Manually upgrade the Database 10g2 (10.2.0.4) to Oracle database 11g2 on Redhat Linux 5

1. Run utlrp.sql to recompile any remaining stored PL/SQL and Java code.

SQL> @utlrp.sql

2. Check dba_registry

column comp_name format a40
set pagesize 50
set line 2000
select trim(comp_name) comp_name , version , status from dba_registry;

3. Verify that all expected packages and classes are valid:

SQL> select count(*) from dba_objects where status <>’VALID’;
SQL>select owner,object_type,count(*) from dba_objects where status <>’VALID’ group by owner,object_type order by 1,2;

4. Backup the database.


5. Analyze the existing instance using the

SQL>@$ORACLE_11G_HOME/rdbms/admin/utlu112i.sql script

6. Shut down the instance:

SQL> SHUTDOWN IMMEDIATE

7. If your operating system is Linux or UNIX, then make the following checks:

a. The oratab file points to your Oracle Database 11g Rel. 2 Oracle home.
b. Your ORACLE_SID is set correctly
c. The following environment variables point to the Oracle Database 11g2 directories:
– ORACLE_HOME
– PATH
d. Any scripts that clients use to set the ORACLE_HOME value must point to the New Oracle home.
Note: If you are upgrading a cluster database, then perform these checks on all nodes in which this cluster database has instances configured.

8. Log in to the system as the owner of the Oracle Database 11g Release 2 (11.2)

Oracle home directory. At a system prompt, change to the $ORACLE_HOME/rdbms/admin directory.

9. Copy init or spfile file to 11g ORACLE_HOME.


10. Connect to the database instance as a user with SYSDBA privileges.

Start the instance by issuing the following command:

SQL> STARTUP UPGRADE

11. Set the system to spool results to a log file for later verification of success:

SQL> SPOOL upgrade.log

12. Run the catupgrd.sql script:

SQL> @catupgrd.sql This scripts runs the desired upgrade scripts and Shuts Down the database.


13. Restart the instance to reinitialize the system parameters for normal operation.

SQL> STARTUP

14. Run utlu112s.sql to display the results of the upgrade:

SQL> @$ORACLE_HOME/rdbms/admin/utlu112s.sql

15. (Optional ) Run catuppst.sql, located in the ORACLE_HOME/rdbms/admin directory, to
perform upgrade actions that do not require the database to be in UPGRADE mode:

SQL> @rdbms/admin/catuppst.sql

16. Run utlrp.sql to recompile any remaining stored PL/SQL and Java code.

SQL> @utlrp.sql

17. Verify that all expected packages and classes are valid:

SQL> SELECT count(*) FROM dba_invalid_objects;
SQL> SELECT distinct object_name FROM dba_invalid_objects;

18. Exit SQL* Plus

Recovering a Standby database from a missing archives log

Recover Gaps in Standby Database from primary Using incremental backups

A Physical Standby database synchs with Primary by continuous apply of archivelogs from a Primary Database. In case of an archivelog gone missing or corrupt you have to rebuild the standby database from scratch but in 10g, an incremental backup created with BACKUP INCREMENTAL... FROM SCN can be used to refresh the standby database with changes at the primary database since the last SCN at Standby and then managed recovery can resume i.e. Compensate for the missing archivelogs

Step 1: On the standby database find out the current SCN.

standby > select current_scn from v$database;
CURRENT_SCN
-----------
2757142742

Step 2: On the primary database, create an incremental backup from the above SCN

#rman target /
RMAN> BACKUP as compressed backupset device type disk INCREMENTAL FROM SCN 2757142742 DATABASE FORMAT '/oracle/backups/test1/rmanbackup/manual/forstandby_%U' tag 'FOR STANDBY';

Step 3: Cancel managed recovery at the standby database

STDBY>alter database recover managed standby database cancel;
Media recovery complete.

scp the backup files to standby server to /oracle/backups/priv2/rmanbackup/manual/ folder.

Step 4: Catalog the Incremental Backup Files at the Standby Database

#rman target /
RMAN> CATALOG START WITH '/oracle/backups/priv1/rmanbackup/manual/';

searching for all files that match the pattern /oracle/backups/priv1/rmanbackup/manual/

List of Files Unknown to the Database
=====================================
.....
Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

Step 5: Apply the Incremental Backup to the Standby Database:

RMAN> recover database noredo;

Step 6: Put the standby database back to managed recovery mode.

STDBY>> recover managed standby database disconnect;
Media recovery complete.

From the alert.log you will notice that the standby database is still looking for the old log files

*************************************************
FAL[client]: Failed to request gap sequence
GAP - thread 1 sequence ....
DBID 23623369570 branch 704397469
FAL[client]: All defined FAL servers have been attempted.

**************************************************
This is because the controlfile has not been updated.
Hence the standby controlfile has to be recreated

Step 7: At Primary .. Create latest control file for standby database

RMAN> backup device type disk current controlfile for standby format '/oracle/backups/test1/rmanbackup/manual/forstandbyctrl.bck';

Step 8: At Standby ... Create a backup (text copy) of control file (to find out the actual location of data files)

SQL> alter database backup controlfile to trace;

Step 9: At Standby …

SQL> shutdown immediate;
SQL> startup nomount;
RMAN> restore standby controlfile from '/oracle/backups/test1/rmanbackup/manual/forstandbyctrl.bck';

Step 10 : At Standby … Create standby Redo log file

For example ( check out for location , group number and size )

SQL>alter database add standby logfile thread 1 group x 'redo log location' size mb;
SQL>alter database add standby logfile thread 1 group 5 '/oracle/oradata01/test1/redo05.log' size 52428800;
SQL>alter database add standby logfile thread 1 group 6 '/oracle/oradata01/test1/redo06.log' size 52428800;

Step 11: At Standby … If there is mismatch in location of data files between primary and standby, need to update control file, follow these steps

SQL>Alter system set standby_file_management=manual;
SQL> alter database recover managed standby database cancel;
SQL>Shutdown immediate;
SQL>Startup mount
SQL>Alter database rename datafile ‘primary data file location’ to ‘standby datafile location’;

For example:

SQL>alter database rename file '/oracle/oradata02/test2/data/app_data05.dbf' to '/oracle/oradata/test1/data/lob/app_data05.dbf';
SQL>alter system set standby_file_management=auto;
SQL>Shutdown immediate
SQL>Startup mount
SQL>recover managed standby database disconnect;

Step 12: Verify the standby alert log file, after couple of switch logfile at primary:

SQL> alter system switch logfile;

ASM Configuration problem for Oracle 11g

If you encounter below error message when you are configuring ASM , check out below document.

Initializing the Oracle ASMLib driver: [FAILED ]

https://docs.google.com/document/edit?id=1PD9UamH_rUbd_Tuj1P2yz5fNs-3DHdnLGd4yEhXek8k&hl=en

Precautions before a shutdown a database

Before a shutdown the Production or any important database, take some basic precaution besides the backup.

Create an init parameter file from current spfile (If database is using spfile)

In case, If database is not mounting / coming up or need to disable some of parameter(s) or database feature to startup :

SQL> create pfile from spfile;

Create backup of a control file in text format

To know the location and data file's name of a database, In case if a database is not mounting or opening after shutdown

SQL> alter database backup controlfile to trace;
Database altered.

Go to udump folder and check for latest trc file to get control file definition:

[oracle@xxdb1 udump]$ ll -ltr tail -2
-rw-r----- 1 oracle oinstall 743 Apr 24 00:15 xxx1_ora_9000.trc
-rw-r----- 1 oracle oinstall 18004 Apr 24 07:23 xxx1_ora_30518.trc

[oracle@xxxdb1 udump]$ vi xxx1_ora_30518.trc

Check the status of the data files

To confirm, All the data files are available and none of them offline

SQL> select TABLESPACE_NAME, ONLINE_STATUS from dba_data_files;
TABLESPACE_NAME ONLINE_
------------------------------ -------
SYSTEM SYSTEM
UNDOTBS1 ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
.....

Review the last 200 lines of alert log file:

For any possible error message or problem

[oracle@xxxdb1 bdump]$ view alert_db_name.log

Open the alert log file as tail -f alert_db_name.log

Make a practice to view what is going with a database, When are you performing some major changes or activities on a database:

[oracle@xxxdb1 ~]$ tail -f /oracle/admin/xxx1/bdump/alert_xxx1.log

In Data Guard / Standby setup

Before a shutdown of a database and after open of a database:

open an alert log as tail -f alert_db_name.log at primary and a standby database:

[oracle@xxxdb1 ~]$ tail -f /oracle/admin/xxx1/bdump/alert_xxx1.log

[oracle@xxxdb2 ~]$ tail -f /oracle/admin/xxx2/bdump/alert_xxx2.log

Switch an archive log manually, checked archive has shipped and media recovery process is applying it.

SQL> alter system switch logfile;

---content from Standby database alert_db_name.log --------
Fri Apr 23 23:41:47 2010
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[24]: Assigned to RFS process 14252
RFS[24]: Identified database type as 'physical standby'
Fri Apr 23 23:43:07 2010
Primary database is in MAXIMUM PERFORMANCE mode
RFS[23]: Successfully opened standby log 8: '/oracle/oradata/xxx2/redo/stby_redo08a.log'
Fri Apr 23 23:43:12 2010
Media Recovery Log /oracle/backups/xxx2/stby_archive/1_12529_704597469.arc
Media Recovery Waiting for thread 1 sequence 12530 (in transit)
Fri Apr 23 23:43:19 2010
Primary database is in MAXIMUM PERFORMANCE mode
RFS[23]: Successfully opened standby log 8: '/oracle/oradata/xxx2/redo/stby_redo08a.log'
Fri Apr 23 23:43:23 2010
Media Recovery Log /oracle/backups/xxx2/stby_archive/1_12530_704597469.arc
Media Recovery Waiting for thread 1 sequence 12531 (in transit)

Managing Archive Gaps Manually on Standby Database Setup

To determine if there is an archive gap on your physical standby database

SQL> SELECT * FROM V$ARCHIVE_GAP;
THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
----------- ------------- --------------
1 12499 12511


In this case, Standby database is waiting for archive log 12499

SQL> select process,sequence#,status from v$managed_standby;

PROCESS SEQUENCE# STATUS
--------- ---------- ------------
ARCH 12509 CLOSING
ARCH 12510 CLOSING
ARCH 12504 CLOSING
ARCH 12508 CLOSING
MRP0 12499 WAIT_FOR_GAP
RFS 12511 IDLE

6 rows selected.

But if you check, archives have been either shipped already or even if you copy these on to Standby server (MRP is still waiting for 12499)

[oracle@xxxdb2 ~]$ cd /oracle/backups/xxx2/stby_archive
[oracle@xxxdb2 stby_archive]$ ll -l 1_12499*
-rw-r----- 1 oracle oinstall 85923328 Apr 23 19:01 1_12499_704597469.arc
[oracle@xxxdb2 stby_archive]$ ll -l 1_1250* head -5
-rw-r----- 1 oracle oinstall 85897728 Apr 23 19:38 1_12500_704597469.arc
-rw-r----- 1 oracle oinstall 83760640 Apr 23 19:39 1_12501_704597469.arc
-rw-r----- 1 oracle oinstall 1121792 Apr 23 19:39 1_12502_704597469.arc
-rw-r----- 1 oracle oinstall 21140480 Apr 23 19:39 1_12503_704597469.arc
-rw-r----- 1 oracle oinstall 246272 Apr 23 19:31 1_12504_704597469.arc
[oracle@xxxdb2 stby_archive]$


Below SQL is also confirmed for same:


SQL> column dest format a20
SQL> select substr(DEST_NAME,1,20) dest ,archived_thread#,archived_seq#,applied_thread#,applied_seq# from v$archive_dest_status;


DEST ARCHIVED_THREAD# ARCHIVED_SEQ# APPLIED_THREAD# APPLIED_SEQ#
----------------------- ---------------- ------------- --------------- ------------
LOG_ARCHIVE_DEST_1 0 0 0 0
LOG_ARCHIVE_DEST_2 1 12509 0 0
….
LOG_ARCHIVE_DEST_10 0 0 0 0
STANDBY_ARCHIVE_DEST 1 12508 1 12498

11 rows selected.



You may find similar entry or error in standby database's alert log file:

Media Recovery Waiting for thread 1 sequence 12499
Fetching gap sequence in thread 1, gap sequence 12499-12503
Fri Apr 23 21:56:44 2010
FAL[client]: Failed to request gap sequence
GAP - thread 1 sequence 12499-12503
DBID 2467369570 branch 704597469
FAL[client]: All defined FAL servers have been attempted.


Some time, due to the cancelation of Recovery Process at standby causes a partial apply of archive log. To fix this problem


To find out archive log at Primary database Server:

SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE THREAD#=1 AND DEST_ID=1 AND SEQUENCE# between 12499 and 12503;

Copy the missing archives to Standby Server (optional).

Register them using the ALTER DATABASE REGISTER LOGFILE sQL on your physical standby database (to re-appy them):

SQL> ALTER DATABASE REGISTER LOGFILE '/oracle/backups/xxx2/stby_archive/1_12499_704597469.arc';
Database altered.

SQL> ALTER DATABASE REGISTER LOGFILE '/oracle/backups/xxx2/stby_archive/1_12500_704597469.arc';
Database altered.

SQL> ALTER DATABASE REGISTER LOGFILE '/oracle/backups/xxx2/stby_archive/1_12501_704597469.arc';
Database altered.

SQL> ALTER DATABASE REGISTER LOGFILE '/oracle/backups/xxx2/stby_archive/1_12502_704597469.arc';
Database altered.

SQL> ALTER DATABASE REGISTER LOGFILE '/oracle/backups/xxx2/stby_archive/1_12503_704597469.arc';
Database altered.

MRP automatically start and apply the archive one by one.

Encountered file error when copying listeners from home

If you encountered same or similar error message during of Oracle database creation on RAC
Environment:

“Encountered file error when copying listeners from home=/u01/app/11.2.0/grid”

Follow below steps to fix the problem

$ echo $TNS_ADMIN
If set, unset and run dbua
$ unset TNS_ADMIN
$ echo
$TNS_ADMIN
$ dbua

Check out Metalink [ID 763578.1] for detail.

Oracle database's fixed_date feature usage script

Some time, there is requirement to advance the database’s date to test month end or advance date related batch process. One way to change the date at O/s level but it may difficult if there are more than databases in Database Server box.

Below script is used fixed_date feature to set advance date and increments / synchronies the time after every 2 minutes through script.

Create a folder (for example dt) and create below script files.

Create or Edit instance_list_node2 to include database(s) for fix /Advance date

# cat instance_list_node2
orcl
demodb
testdb

Main script to get changed date and time of database(s).

# cat clock_change.ksh

#!/usr/bin/ksh
############################################
# never-ending loop to change database clock
############################################
export ORACLE_HOME=/oracle/oradata01/as10g # change the Oracle home name
export JOBDIR=/home/oracle/scripts/dt # change the folder name
export NODENUM=2
export LISTFILE=/home/oracle/scripts/dt/instance_list_node${NODENUM} # change the folder name
cd $JOBDIR

# start with q resync of hours and minutes...
CHANGE_TYPE=1
exec < ${LISTFILE}
while read line
do
export ORACLE_SID=${line}
TYPE=RESYNC
${ORACLE_HOME}/bin/sqlplus / as sysdba @${JOBDIR}/resync_clock_change_sql_generator.sql
TIME=`date '+%H:%M:%S'`
sed -e "s/\*/${TIME}/g" ${JOBDIR}/resync_clock_change_model.sql > ${JOBDIR}/resync_clock_change.sql
${ORACLE_HOME}/bin/sqlplus / as sysdba @${JOBDIR}/resync_clock_change.sql

if [ $? -eq 0 ]
then
echo ${ORACLE_SID} $TYPE" clock update ran " `date` >> ${JOBDIR}/clock_change.log
else
echo " " >> ${JOBDIR}/clock_change.log
echo " " >> ${JOBDIR}/clock_change.log
echo ${ORACLE_SID} $TYPE" CLOCK UPDATE FAILED " `date` >> ${JOBDIR}/clock_change.log
echo " " >> ${JOBDIR}/clock_change.log
echo " " >> ${JOBDIR}/clock_change.log
fi
done
sleep 120

#
# This is an infinite loop...
#
while [ 1 -eq 1 ]
do
CHANGE_TYPE=0
HR=`date '+%H'`
exec < ${LISTFILE}
while read line
do
export ORACLE_SID=${line}

if [ ${HR} -eq 01 ]
then
CHANGE_TYPE=1
fi
if [ ${HR} -eq 07 ]
then
CHANGE_TYPE=1
fi
if [ ${HR} -eq 13 ]
then
CHANGE_TYPE=1
fi
if [ ${HR} -eq 19 ]
then
CHANGE_TYPE=1
fi

if [ ${CHANGE_TYPE} -eq 1 ]
then
TYPE=RESYNC
${ORACLE_HOME}/bin/sqlplus / as sysdba @${JOBDIR}/resync_clock_change_sql_generator.sql
TIME=`date '+%H:%M:%S'`
sed -e "s/\*/${TIME}/g" ${JOBDIR}/resync_clock_change_model.sql > ${JOBDIR}/resync_clock_change.sql
${ORACLE_HOME}/bin/sqlplus / as sysdba @${JOBDIR}/resync_clock_change.sql
else
CHANGE_TYPE=2
TYPE=INCREMENTAL
${ORACLE_HOME}/bin/sqlplus / as sysdba @${JOBDIR}/clock_change1.sql
fi

if [ ${?} -eq 0 ]
then
echo ${ORACLE_SID} ${TYPE}" clock update ran " `date` >> ${JOBDIR}/clock_change.log
else
echo " " >> ${JOBDIR}/clock_change.log
echo " " >> ${JOBDIR}/clock_change.log
echo ${ORACLE_SID} $TYPE" CLOCK UPDATE FAILED " `date` >> ${JOBDIR}/clock_change.log
echo " " >> ${JOBDIR}/clock_change.log
echo " " >> ${JOBDIR}/clock_change.log
fi
done
sleep 120
done


To get new date after 24 hrs.

# cat resync_clock_change_sql_generator.sql
set feedback off
set heading off
spool /home/oracle/scripts/dt/resync_clock_change_model.sql # change the folder name
select 'alter system set FIXED_DATE = '''to_char(sysdate,'YYYY-MM-DD-*')''' ;' from dual;
select 'exit;' from dual;
spool off
exit;


To get changed new time

# cat clock_change1.sql
set feedback off
set heading off
spool /home/oracle/scripts/dt/clock_change2.sql # change the folder name
select 'alter system set FIXED_DATE = '''to_char(sysdate + 2/1440,'YYYY-MM-DD-HH24:MI:SS')''' ;' from dual;
select 'exit;' from dual;
spool off
@/home/oracle/scripts/dt/clock_change2.sql ; # change the folder name
exit;


# Intermediate file

# cat resync_clock_change.sql
alter system set FIXED_DATE = '2012-01-12-19:59:18' ;
exit;

# Intermediate file

# cat clock_change2.sql
alter system set FIXED_DATE = '2012-01-12-23:11:18' ;
exit;

To implement the script:


chmod 777 clock_change.ksh

SQL> shutdown immediate;
SQL> startup mount
SQL>alter system set FIXED_DATE='2011-12-31-10:00:00'; # any advance /back date

To start script (infinite loop)

#nohup ./clock_change.ksh &

To Verify the date and time of database. This script will change the time after every 2 mins and date will be changed after 24 hrs.

SQL>alter session set nls_date_format='YYYY-MON-DD HH24:MI:SS';

SQL> select to_char(sysdate, 'DD-MM-YYYY HH24:MI:SS') from dual;

TO_CHAR(SYSDATE,'DD
-------------------
31-12-2011 10:00:00

To stop the script for particular database

Remove the database name from file instance_list_node2

To stop the script

#ps -ef grep clock_change
#kill -9 process_id