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 Primary

SQL> 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 database

SQL> 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.

Retransformation 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

On 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