1. It is assume there is an existing physical standby data guard configuration and switchover between the database is working properly. Steps on how to created the data guard (11gR2 RAC Data guard) is not shown here.
2. 11gR2 database software upgrades could be done in-place as well as out of place (which is the recommended way). In this case out of place upgrade is used and to save time install the 11gR2 database software on the new location both on primary and standby database servers and apply the latest patches. Specially with Jan 2012 PSU this could lead to issues (step 11).
3. Copy the listener.ora and tnsnames.ora file from the existing 11gR1 Oracle Home to 11gR2 Oracle home and modify the Oracle Home value in listener.ora to point to 11gR2 home.
4. There are several constraints when using this method of upgrade. More available on the white papers and metalink notes mentioned at the end of the post. These are ...
1. Dataguard broker must be not be configured for the database. If DG Broker is setup then disable it. ALTER SYSTEM SET DG_BROKER_START=FALSE SCOPE=BOTH;5. Enable flashback logging on both primary and standby databases. This requires database to be shutdown cleanly and then in mount mode. This could incur down time and if flash back is not enabled then to create the restore point database need to be shutdown and mounted in 11gR1. Once mounted enable flashback with
2. Protection mode must be either maximum performance or maximum availability.
3. Log archive destination to standby database must have the optional option.
4. Compatible parameter value must be same on both primary and standby before the upgrade.
5. Prepare to switchover clause cannot be used for the initial switchover.
6. Since this method uses logical standby , identify the unsupported data types. See if Extended Datatype Support (EDS) could be used which allows SQL Apply to replicate changes to tables that contain some data types not natively supported.
SQL> alter database flashback on;6. Backup both primary DB and standby DB which would be used to recover if the upgrade fails.
7. The primary database is denoted with DBP and standby database with DBS. Data guard configuration is not using DG Broker and there are no stream replications running. If stream replication is used then stop capture process.
8. Both databases are running on maximum performance mode
SQL> SELECT PROTECTION_MODE FROM V$DATABASE; PROTECTION_MODE -------------------- MAXIMUM PERFORMANCECurrent log_archive_dest on primary database is
NAME TYPE VALUE
-------------------- ----------- ------------------------------
log_archive_dest_1 string service=DBSTNS LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5
reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=dbsand on standbySQL> show parameter log_archive_dest_1
NAME TYPE VALUE
--------------------- ----------- ------------------------------
log_archive_dest_1 string service=DBPTNS LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5
reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=dbpUpgrade log_archive_dest on each database to include the OPTIONAL clause.On primary alter system set log_archive_dest_1= 'service=DBSTNS LGWR ASYNC NOAFFIRM OPTIONAL max_failure=10
max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=dbs' scope=both;On standbyalter system set log_archive_dest_1= 'service=DBPTNS LGWR ASYNC NOAFFIRM OPTIONAL max_failure=10
max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=dbp' scope=both;9. Create a guarantee restore point on primary DB.SQL> create restore point before_upgrade_1 guarantee flashback database; Restore point created.Optionally restore point could be created in standby DB as well allowing it to be flash backed in case of failures during upgrade.
10. Next step is the main step in the upgrade, that is converting the physical standby to logical standby with keep identity clause. Cancel log apply on standby
SQL> alter database recover managed standby database cancel;Create the log miner dictionary in primary DB
SQL> EXECUTE DBMS_LOGSTDBY.BUILD; PL/SQL procedure successfully completed.Convert the physical standby to logical standby with by running the following on the standby DB
SQL> alter database recover to logical standby keep identity; SQL> alter database open; SQL> alter database start logical standby apply immediate; SQL> execute dbms_logstdby.apply_set( 'log_auto_delete', 'false'); PL/SQL procedure successfully completed.Check state and wait until state becomes idle
SQL> SELECT SESSION_ID, STATE FROM V$LOGSTDBY_STATE;
SESSION_ID STATE
---------- -------------------
1 LOADING DICTIONARY
SQL> /
SESSION_ID STATE
---------- ------------------
1 IDLE11. To carry out the upgrade defer log shipping on primarySQL> alter system set log_archive_dest_state_1='defer' scope=memoryOn standby
SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;Create a restore point on standby to be used in case of upgrade failures
SQL> CREATE RESTORE POINT before_upgrade_standby_1 GUARANTEE FLASHBACK DATABASE;12. Set the oracle home to new version (11gR2) and upgrade the logical standby (DBS). Set environment variables to 11gR2 home
export ORACLE_HOME=/opt/app/oracle/product/11.2.0/db_1 export PATH=$ORACLE_HOME/bin:$PATHTo confirm it's the 11gR2 binaries that are used
which sqlplus /opt/app/oracle/product/11.2.0/db_1/bin/sqlplusStop the listener running out of 11gR1 home and start the listener on 11gR2.
Upgrade the logical standby db from 11.1. to 11.2 either using DBUA or manually with scripts. While upgrade is happening primary is open for use and there's no down time.
When running DBUA following warning may appear, make sure there's enough space for log archive location(s).
Although it is the standby that is upgrade DB Name will show the primary DB name. This is expected as in standby only db unique name is different between primary and standby.
13. Once the upgrade is finished enable log archive destination on primary DB
SQL> alter system set log_archive_dest_state_1='enable' scope=memory;and start SQL apply on standby
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;14. This is only step where a down time happens ( not counting the downtime needed for enabling flashback logging and according to oracle this is less than 1 minute). Carry out a switchover by making the newly upgraded logical standby (DBS) the new primary.
On current primary (DBP)
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE; SWITCHOVER_STATUS -------------------- TO STANDBY SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY; Database altered.On current standby (DBS)
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE; SWITCHOVER_STATUS -------------------- TO PRIMARY SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL PRIMARY; Database altered.15. At this point there's no disaster recovery as the old primary which is new standby(DBP) is running in a lower version (11.1). To enable disaster recovery this must be flash backed and converted to physical standby.
On new primary (DBS) defer the log archive destination
SQL> alter system set log_archive_dest_state_1='defer' scope=memory;On new standby (old primary DBP)
SQL> shutdown immediate; SQL> startup mount SQL> FLASHBACK DATABASE TO RESTORE POINT before_upgrade_1; Flashback complete.16. Convert the new standby (DBP) to physical standby by mounting on the new oracle home (11gR2). Copy the listener.ora and tnsnames.ora files to new oracle home (if not done already) and start the listener from new home. Also copy the spfile and orapwd to new oracle home. Then run the convert command
shutdown immediate; startup mount; SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY; Database altered.Above command will unmount the DB therefore mount it and start the redo apply.
shutdown immeidate; startup mount; SQL> RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT; Media recovery complete.On the new primary (DBS) enable the log_archive_dest
SQL> alter system set log_archive_dest_state_1='enable' scope=memory;when redo apply starts it will register a new incarnation on the new physical standby (DBP) from the primary database (DBS). Following could be seen from the standby db (DBP) alert log.
Wed Mar 07 13:13:53 2012 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT Attempt to start background Managed Standby Recovery process (dbp) Wed Mar 07 13:13:53 2012 MRP0 started with pid=31, OS id=13047 MRP0: Background Managed Standby Recovery process started (dbp) started logmerger process Wed Mar 07 13:13:58 2012 Managed Standby Recovery starting Real Time Apply MRP0: Background Media Recovery waiting for new incarnation during transient logical upgrade procedure Errors in file /opt/app/oracle/diag/rdbms/dbp/dbp/trace/dbp_pr00_13049.trc: ORA-19906: recovery target incarnation changed during recovery Managed Standby Recovery not using Real Time Apply Slave exiting with ORA-19906 exception Errors in file /opt/app/oracle/diag/rdbms/dbp/dbp/trace/dbp_pr00_13049.trc: ORA-19906: recovery target incarnation changed during recovery Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT Recovery Slave PR00 previously exited with exception 19906 Wed Mar 07 13:14:09 2012 started logmerger process Wed Mar 07 13:14:09 2012 Managed Standby Recovery starting Real Time Apply MRP0: Background Media Recovery waiting for new incarnation during transient logical upgrade procedure Errors in file /opt/app/oracle/diag/rdbms/dbp/dbp/trace/dbp_pr00_13051.trc: ORA-19906: recovery target incarnation changed during recovery Managed Standby Recovery not using Real Time Apply Slave exiting with ORA-19906 exception Errors in file /opt/app/oracle/diag/rdbms/dbp/dbp/trace/dbp_pr00_13051.trc: ORA-19906: recovery target incarnation changed during recovery Recovery Slave PR00 previously exited with exception 19906 Wed Mar 07 13:14:12 2012 Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST Wed Mar 07 13:14:13 2012 RFS[1]: Assigned to RFS process 13055 ... ... RFS[1]: New Archival REDO Branch(resetlogs_id): 777303881 Prior: 776883134 RFS[1]: Archival Activation ID: 0x6c441934 Current: 0x0 RFS[1]: Effect of primary database OPEN RESETLOGS RFS[1]: Managed Standby Recovery process is active RFS[1]: Incarnation entry added for Branch(resetlogs_id): 777303881 (dbp) Wed Mar 07 13:14:19 2012 Setting recovery target incarnation to 2 Archived Log entry 78 added for thread 1 sequence 1 rlc 777303881 ID 0x6c441934 dest 1: Wed Mar 07 13:14:19 2012 RFS[4]: Assigned to RFS process 13063 RFS[4]: Opened log for thread 1 sequence 3 dbid 1815985854 branch 777303881 Archived Log entry 79 added for thread 1 sequence 3 rlc 777303881 ID 0x6c441934 dest 1: Wed Mar 07 13:14:20 2012 RFS[5]: Assigned to RFS process 13065 RFS[5]: Opened log for thread 1 sequence 4 dbid 1815985854 branch 777303881 Wed Mar 07 13:14:20 2012 RFS[6]: Assigned to RFS process 13067 RFS[6]: Opened log for thread 1 sequence 5 dbid 1815985854 branch 777303881 ... ... Clearing online redo logfile 1 complete Clearing online redo logfile 2 /opt/app/oracle/oradata/DBP/onlinelog/o1_mf_2_7odd8b6c_.log Clearing online log 2 of thread 1 sequence number 70 Clearing online redo logfile 2 complete Clearing online redo logfile 3 /opt/app/oracle/oradata/DBP/onlinelog/o1_mf_3_7odd8bn4_.log Clearing online log 3 of thread 1 sequence number 60 Clearing online redo logfile 3 complete Media Recovery Log /opt/app/oracle/flash_recovery_area/DBP/archivelog/2012_03_07/o1_mf_1_61_7ogncljf_.arc Media Recovery Log /opt/app/oracle/flash_recovery_area/DBP/archivelog/2012_03_07/o1_mf_1_62_7ogncndv_.arc RFS[6]: Opened log for thread 1 sequence 7 dbid 1815985854 branch 777303881 Media Recovery Log /opt/app/oracle/flash_recovery_area/DBP/archivelog/2012_03_07/o1_mf_1_63_7ogncpqb_.arc Media Recovery Log /opt/app/oracle/flash_recovery_area/DBP/archivelog/2012_03_07/o1_mf_1_1_7ogqs53q_.arc Wed Mar 07 13:14:32 2012 Media Recovery Log /opt/app/oracle/flash_recovery_area/DBP/archivelog/2012_03_07/o1_mf_1_2_7ogqs6r2_.arc Wed Mar 07 13:14:32 2012 Archived Log entry 83 added for thread 1 sequence 6 rlc 777303881 ID 0x6c441934 dest 1:17. Do a switchover so the database configuraiton is as before the upgrade (DBP is primary and DBS standby). This would add another down time, if the systems are identical the upgrade process could be concluded here incuring only one down time.
On new primary (DBS)
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE; SWITCHOVER_STATUS -------------------- TO STANDBY SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY; Database altered.On new standby (DBP)
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE; SWITCHOVER_STATUS -------------------- TO PRIMARY SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY; Database altered.Start redo apply on DBS
shutdown immediate; startup mount SQL> alter database RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;18. Drop the restore points to save some space
SQL> SELECT NAME FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE ='YES'; NAME ----------------- BEFORE_UPGRADE_1On standby
SQL> drop restore point BEFORE_UPGRADE_1; SQL> SELECT NAME FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE ='YES'; NAME ------------------------ BEFORE_UPGRADE_STANDBY_1 SQL> drop restore point BEFORE_UPGRADE_STANDBY_1;19. If DG Broker was enable before configure again.
20. If all is in order then upgrade the compatible parameter value. This conclude the upgrade processes.
Useful metalink notes
Extended Datatype Support (EDS) for SQL Apply [ID 559353.1]
Oracle11g Data Guard: Database Rolling Upgrade Shell Script [ID 949322.1]
White Papers
1. Database Rolling Upgrade Using Transient Logical Standby:
Oracle Data Guard 11g Oracle Maximum Availability Architecture White Paper January 2012
2. Database Rolling Upgrades Made Easy by Using a Data Guard Physical Standby Database
Oracle Maximum Availability Architecture White Paper October 2011
Best Practices for Zero Risk, Zero Downtime Database Maintenance
