11g Release 2 Rolling Upgrade using Transient Logical Standby Database

This note illustrates how we can perform a rolling upgrade from Oracle 11g Release 1 to Oracle 11g Release 2 using a Transient Logical Standby database. This approach will miminise the downtime required for an upgrade which can potentially run into several hours down to just the time required to perform a switchover which could be a few minutes in most cases.

A rolling upgrade using a Transient Logical Standby database at a very high level will involve three main stages or steps:

1) Temporarily convert a physical standby database to a logical standby database using the new KEEP IDENTITY clause
2) Perform a database upgrade of the logical standby database
3) Return the logical standby database back to its identity or original status as a physical standby database once the upgrade is complete

The assumption here is that ….

1)We already have configured a Physical Standby Database using Data Guard best practices and both Primary and Standby databases are in sync and Redo Transport and Redo Apply are working properly.

2)Data Guard Broker if configured is disabled

3) Standby database is operating in Maximum Availability or Maximum Protection mode

4) Flashback Database is enabled

Steps

Create a Guaranteed Restore Point on original PrimarySQL> create restore point pre_upgrade_1 guarantee flashback database; Restore point created.

Create a guaranteed restore point on original Standby database

We also create a restore point on the standby database in case we encounter any errors while upgrading the database and we would like to have a fallback in place.SQL> recover managed standby database cancel; Media recovery complete. SQL> create restore point pre_upgrade_2 guarantee flashback database; Restore point created. SQL> recover managed standby database using current logfile disconnect; Media recovery complete.

Create the Log Miner dictionary on the original primary database

SQL> exec dbms_logstdby.build; PL/SQL procedure successfully completed.

Convert the original physical standby database to logical standby database

We will use the new 11g KEEP IDENTITY clause which ensures that the logical standby database keeps its DBID which is identical to the DBID of the original primary database.SQL> recover managed standby database cancel; Media recovery complete. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 526131200 bytes Fixed Size 2139784 bytes Variable Size 209717624 bytes Database Buffers 306184192 bytes Redo Buffers 8089600 bytes Database mounted. SQL> alter database recover to logical standby keep identity; Database altered. SQL> alter database open; Database altered.

Start SQL Apply on the new logical standby database and monitor the dictionary build

At this point now, the logical standby database will be receiving the necessary redo information from the original primary database to populate its own Log Miner dictionary. We can monitor the dictionary build by querying the view V$LOGSTDBY_STATE.SQL> alter database start logical standby apply immediate; Database altered. SQL> select state from v$logstdby_state; STATE ---------------------------------------------------------------- LOADING DICTIONARY SQL> / STATE ---------------------------------------------------------------- APPLYING SQL> / STATE ---------------------------------------------------------------- IDLE

Prepare the Logical Standby database for upgrade

We need to stop the SQL Apply and also create another restore point on the logical standby as a fallback measure.On Original Primary SQL> alter system set log_archive_dest_state_2=DEFER scope=memory; System altered. On Logical Standby SQL> alter database stop logical standby apply; Database altered. SQL> create restore point pre_upgrade_3 guarantee flashback database; Restore point created. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down.

Peform the upgrade of the Logical Standby database to 11g Release 2 using DBUA or by using the manual steps.

While the upgrade is in progress, users are still connected to the original 11g Release 1 primary database. Let us make some DDL as well as DML changes on this database.SQL> conn sh/sh Connected. SQL> update customers set cust_city='Perth'; 55500 rows updated. SQL> commit; Commit complete. SQL> create table mycustomers as select * from customers; Table created. SQL> update mycustomers set cust_city='New York'; 55500 rows updated. SQL> commit; Commit complete.

After the upgrade is complete, we will now start SQL Apply on the upgraded Logical Standby database

On Origial Primary SQL> alter system set log_archive_dest_state_2=enable scope=memory; System altered. On Upgraded Logical Standby database SQL> alter database start logical standby apply immediate;

Perform a Switchover to the Upgraded 11g Release 2 standby database on Original Primary SQL>

select switchover_status from v$database; SWITCHOVER_STATUS -------------------- TO STANDBY SQL> alter database commit to switchover to logical standby; Database altered. On upgraded Logical Standby database SQL> select switchover_status from v$database; SWITCHOVER_STATUS -------------------- TO PRIMARY SQL> alter database commit to switchover to logical primary; Database altered.

Perform some checks

While the logical standby database was being upgraded to 11g Release 2, users were connected to the original 11g Release 1 primary database. Let us see if those changes we made have been propagated to the standby site – which now after the switchover has become the new Primary database.SQL> conn sh/sh Connected. SQL> select distinct cust_city from customers; CUST_CITY ------------------------------ Perth SQL> select distinct cust_city from mycustomers; CUST_CITY ------------------------------ New York


Make some changes on the upgraded 11g Release 2 databaseSQL> conn sh/sh Connected. SQL> update mycustomers set cust_city='Tokyo'; 55500 rows updated. SQL> update customers set cust_city='Hong Kong'; 55500 rows updated. SQL> commit; Commit complete.

Re-transformation into Physical Standby database

The former primary database is running at a lower Oracle version (11g Release 1) as a transient logical standby database. It cannot receive and apply any redo from the new primary database until it has been converted back into a physical standby database.On New Primary (11g Release 2) SQL> conn / as sysdba Connected. SQL> alter system set log_archive_dest_state_2=defer scope=memory; On New Logical Standy database (11g Release 1) SQL> select database_role from v$database; DATABASE_ROLE ---------------- LOGICAL STANDBY SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production PL/SQL Release 11.1.0.6.0 - Production CORE 11.1.0.6.0 Production TNS for IBM/AIX RISC System/6000: Version 11.1.0.6.0 - Production NLSRTL Version 11.1.0.6.0 - Production SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 526131200 bytes Fixed Size 2139784 bytes Variable Size 260049272 bytes Database Buffers 255852544 bytes Redo Buffers 8089600 bytes Database mounted. SQL> flashback database to restore point pre_upgrade_1; Flashback complete. SQL> shutdown immediate; ORA-01109: database not open Database dismounted. ORACLE instance shut down.

Start the new logical standby database in the 11g Release 2 Oracle Home and convert it to a physical standby database.

We need to do some prep work before we can start the instance in the 11g Release 2 Oracle home. Copy the password file and the init.ora file from the 11g R1 locations to the 11g R2 locations.

dba1:/u01/oracle/product/11.2/dbs> cp /u02/oradata/product/dbs/orapwdba1 .

dba1:/u01/oracle/product/11.2/dbs> cp /u02/oradata/product/dbs/spfiledba1.ora .SQL> startup mount ORACLE instance started. Total System Global Area 521936896 bytes Fixed Size 2208368 bytes Variable Size 159387024 bytes Database Buffers 352321536 bytes Redo Buffers 8019968 bytes Database mounted. SQL> alter database convert to physical standby; Database altered. SQL> shutdown immediate; ORA-01507: database not mounted ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 521936896 bytes Fixed Size 2208368 bytes Variable Size 159387024 bytes Database Buffers 352321536 bytes Redo Buffers 8019968 bytes Database mounted.

At this stage we will get an error if we try and open the database as the database is of a lower version and has not been upgraded as yet.SQL> alter database open; alter database open * ERROR at line 1: ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00704: bootstrap process failure ORA-39700: database must be opened with UPGRADE option Process ID: 393392 Session ID: 191 Serial number: 3

Start Redo Apply on the converted physical databaseOn New Primary SQL>

alter system set log_archive_dest_state_2=enable scope=memory; System altered. On New Physical Standby database SQL> recover managed standby database using current logfile disconnect; Media recovery complete.

Monitor Redo Apply

When the redo apply starts, it will register a new incarnation received from the primary database. Until that happens, we will see from the alert log that the Redo Apply loops every ten seconds or so waiting for the incarnation to be registered.Fri Mar 19 10:38:51 2010 Managed Standby Recovery starting Real Time Apply MRP0: Background Media Recovery waiting for new incarnation during transient logical upgrade procedure Errors in file /u01/ofsad2/diag/rdbms/dba1_devu026/dba1/trace/dba1_pr00_368854.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 /u01/ofsad2/diag/rdbms/dba1_devu026/dba1/trace/dba1_pr00_368854.trc: ORA-19906: recovery target incarnation changed during recovery

Then we will see …..RFS[1]: Identified database type as 'physical standby': Client is LGWR SYNC pid 1134740 Primary database is in MAXIMUM AVAILABILITY mode Changing standby controlfile to RESYNCHRONIZATION level Standby controlfile consistent with primary RFS[1]: Selected log 4 for thread 1 sequence 111 dbid 2023850869 branch 713959355 Fri Mar 19 10:39:12 2010 RFS[2]: Assigned to RFS process 1736934 RFS[2]: Identified database type as 'physical standby': Client is ARCH pid 1990894 RFS[2]: Selected log 5 for thread 1 sequence 110 dbid 2023850869 branch 713959355 RFS[2]: New Archival REDO Branch(resetlogs_id): 713959355 Prior: 713439030 RFS[2]: Archival Activation ID: 0x78aa01e2 Current: 0x0 RFS[2]: Effect of primary database OPEN RESETLOGS RFS[2]: Managed Standby Recovery process is active RFS[2]: Incarnation entry added for Branch(resetlogs_id): 713959355 (dba1) Fri Mar 19 10:39:13 2010 Setting recovery target incarnation to 3

Then we will see the the media recovery in progress. This will apply all the redo which contains the changes related to the upgrade of the database to 11g Release 2, so that once the media recovery is complete, we will see that the physical standby database has indeed been upgraded to 11g Release 2.Fri Mar 19 11:01:06 2010 Managed Standby Recovery starting Real Time Apply Parallel Media Recovery started with 4 slaves RFS[3]: Identified database type as 'physical standby': Client is ARCH pid 1990894 Fri Mar 19 11:01:22 2010 RFS[4]: Assigned to RFS process 1835206 RFS[4]: Identified database type as 'physical standby': Client is ARCH pid 794694 RFS[3]: Opened log for thread 1 sequence 7 dbid 2023850869 branch 713959355 RFS[4]: Opened log for thread 1 sequence 8 dbid 2023850869 branch 713959355 Archived Log entry 79 added for thread 1 sequence 7 rlc 713959355 ID 0x78aa01e2 dest 2: Archived Log entry 80 added for thread 1 sequence 8 rlc 713959355 ID 0x78aa01e2 dest 2: ...... ...... RFS[44]: Assigned to RFS process 561242 RFS[44]: Identified database type as 'physical standby': Client is ARCH pid 794694 RFS[44]: Opened log for thread 1 sequence 99 dbid 2023850869 branch 713959355 Archived Log entry 119 added for thread 1 sequence 99 rlc 713959355 ID 0x78aa01e2 dest 2: Archived Log entry 120 added for thread 1 sequence 97 rlc 713959355 ID 0x78aa01e2 dest 2: RFS[44]: Opened log for thread 1 sequence 100 dbid 2023850869 branch 713959355 Archived Log entry 121 added for thread 1 sequence 100 rlc 713959355 ID 0x78aa01e2 dest 2: ...... ...... Media Recovery Log /u02/oradata/dba1/arch/arch1_10_713959355.log Fri Mar 19 11:42:53 2010 Media Recovery Log /u02/oradata/dba1/arch/arch1_11_713959355.log Media Recovery Log /u02/oradata/dba1/arch/arch1_12_713959355.log Fri Mar 19 11:43:09 2010 Media Recovery Log /u02/oradata/dba1/arch/arch1_13_713959355.log Media Recovery Log /u02/oradata/dba1/arch/arch1_14_713959355.log ...... ...... Media Recovery Log /u02/oradata/dba1/arch/arch1_24_713959355.log Media Recovery Log /u02/oradata/dba1/arch/arch1_25_713959355.log Fri Mar 19 11:45:02 2010 Media Recovery Log /u02/oradata/dba1/arch/arch1_26_713959355.log Media Recovery Log /u02/oradata/dba1/arch/arch1_27_713959355.log Fri Mar 19 11:45:19 2010 Media Recovery Log /u02/oradata/dba1/arch/arch1_28_713959355.log ..... ..... Media Recovery Log /u02/oradata/dba1/arch/arch1_104_713959355.log Media Recovery Log /u02/oradata/dba1/arch/arch1_105_713959355.log Media Recovery Log /u02/oradata/dba1/arch/arch1_106_713959355.log Media Recovery Log /u02/oradata/dba1/arch/arch1_107_713959355.log Media Recovery Log /u02/oradata/dba1/arch/arch1_108_713959355.log Media Recovery Log /u02/oradata/dba1/arch/arch1_109_713959355.log Media Recovery Log /u02/oradata/dba1/arch/arch1_110_713959355.log Fri Mar 19 13:19:50 2010 Media Recovery Log /u02/oradata/dba1/arch/arch1_111_713959355.log Media Recovery Log /u02/oradata/dba1/arch/arch1_112_713959355.log Media Recovery Log /u02/oradata/dba1/arch/arch1_113_713959355.log Media Recovery Log /u02/oradata/dba1/arch/arch1_114_713959355.log Media Recovery Waiting for thread 1 sequence 115 (in transit) Recovery of Online Redo Log: Thread 1 Group 6 Seq 115 Reading mem 0 Mem# 0: /u02/oradata/dba1/flash_recovery_area/DBA1/onlinelog/o1_mf_6_5sm961z3_.log
SQL> @?/rdbms/admin/utlu112s.sql . Oracle Database 11.2 Post-Upgrade Status Tool 03-19-2010 13:28:43 . Component Status Version HH:MM:SS . Oracle Server . VALID 11.2.0.1.0 00:44:46 JServer JAVA Virtual Machine . VALID 11.2.0.1.0 00:10:56 Oracle Workspace Manager . VALID 11.2.0.1.0 00:01:28 OLAP Analytic Workspace . VALID 11.2.0.1.0 00:01:18 OLAP Catalog . VALID 11.2.0.1.0 00:02:36 Oracle OLAP API . VALID 11.2.0.1.0 00:01:28 Oracle Enterprise Manager . VALID 11.2.0.1.0 00:38:10 Oracle XDK . VALID 11.2.0.1.0 00:01:31 Oracle Text . VALID 11.2.0.1.0 00:01:22 Oracle XML Database . VALID 11.2.0.1.0 00:06:22 Oracle Database Java Packages . VALID 11.2.0.1.0 00:01:13 Oracle Multimedia . VALID 11.2.0.1.0 00:15:21 Spatial . VALID 11.2.0.1.0 00:09:56 Oracle Expression Filter . VALID 11.2.0.1.0 00:00:31 Oracle Rules Manager . VALID 11.2.0.1.0 00:00:24 Oracle Application Express . VALID 3.2.1.00.10 00:26:39 Gathering Statistics . 00:17:09 Total Upgrade Time: 03:02:12

Switchback Steps

We will now perform a second switchover (note, this step is optional if we wish to continue running the database from the original standby site). This will return the new primary database back to its original role as the physical standby database and the new standby database back to its original role of Primary database.On new Standby SQL> select switchover_status from v$database; SWITCHOVER_STATUS -------------------- TO PRIMARY SQL> alter database commit to switchover to primary; Database altered. SQL> shutdown immediate; ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 521936896 bytes Fixed Size 2208368 bytes Variable Size 159387024 bytes Database Buffers 352321536 bytes Redo Buffers 8019968 bytes Database mounted. Database opened. On current Primary SQL> select switchover_status from v$database; SWITCHOVER_STATUS -------------------- TO STANDBY SQL> alter database commit to switchover to standby with session shutdown; Database altered. SQL> shutdown immediate; ORA-01507: database not mounted ORACLE instance shut down. SQL> startup; ORACLE instance started. Total System Global Area 521936896 bytes Fixed Size 2208368 bytes Variable Size 159387024 bytes Database Buffers 352321536 bytes Redo Buffers 8019968 bytes Database mounted. Database opened. SQL> recover managed standby database using current logfile disconnect; Media recovery complete. Test all changes made from 11g Release 2 database before switchback have been propagated SQL> conn sh/sh Connected. SQL> select distinct cust_city from customers; CUST_CITY ------------------------------ Hong Kong SQL> select distinct cust_city from mycustomers; CUST_CITY ------------------------------ Tokyo