News
Perth, Australia
+ (61) 417713124
prosolutions@gavinsoorma.com

11g Release 2 Rolling Upgrade using Transient Logical Standby database

  • Posted by Gavin Soorma
  • On March 22, 2010
  • 0 Comments
  • 11g release 2, keep identity, logical standby, rolling upgrade, transient

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
  • Start Redo Apply on the converted physical database
  • 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
    
    
     2

    0 Comments

    Leave Reply

    Your email address will not be published. Required fields are marked *