Upgrade an Oracle 12c database using transient logical standby

Upgrading an Oracle 12c database using transient logical standby database.

An Oracle Data Guard standby database keeps itself synchronized with the primary using the redo logs received from it. It can take over the primary’s role when the usual primary goes down or needs to be down for maintenance. How does one upgrade to a higher version in such a HA configuration ?

Upgrading using the transient logical standby.

Reference : http://www.oracle.com/au/products/database/maa-wp-11g-transientlogicalrollingu-1-131927.pdf

Let us apply this technique to and upgrade an Oracle 12c database.

Consider the case of an Oracle 12.1.0.1 database being upgraded to Oracle 12.1.0.2. To start with we have an Oracle container database named DB1, which is in a data guard configuration that uses a physical standby database. The database version is 12.1.0.1 and it is using asynchronous redo transport.

SQL> select * from v$dataguard_config;

DB_UNIQUE_NAME  PARENT_DBUN          DEST_ROLE         CURRENT_SCN     CON_ID

--------------- -------------------- ----------------- ----------- ----------

DB1PR           NONE                 PRIMARY DATABASE       411484          0

DB1SB           DB1PR                PHYSICAL STANDBY       390255          0

1. First a guaranteed restore point is created on primary. This restore point will be needed later on, when the primary site gets upgraded.

SQL> create restore point before_upgrade guarantee flashback database;

Restore point created.

2. Stop managed recovery on the standby.

SQL> recover managed standby database cancel;

Media recovery complete.

SQL>

3. Start a log miner build on primary (Redo transport remains on)

SQL> execute dbms_logstdby.build();

PL/SQL procedure successfully completed.

SQL>

4. Convert the standby database to a logical standby.

SQL> alter database recover to logical standby keep identity;

Database altered.

5. Start SQL apply on the new logical standby

6. Wait for log miner build redo to be applied on primary (until v$logstdby_state.state

should becomes IDLE).

SQL> select session_id, state from v$logstdby_state;

SESSION_ID STATE

---------- ----------------------------------------------------------------

         1 IDLE

7. Defer redo transport to the standby site from  the primary

SQL> alter system set log_archive_dest_state_2='DEFER';

System altered.

8. On the standby stop the SQL Apply process.

SQL> alter database stop logical standby apply;

Database altered.

9. Shut down the logical standby database

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

10. On the standby site, set the ORACLE_HOME and PATH to point to the new Oracle home software. Its assumed here the the instance being upgraded is registered (for instance, in /etc/oratab) under the old Oracle home.

11. On the standby site, Start up the dbua from the new Oracle home.

12.  Select the Upgrade Oracle Database option and click next on the OUI screen that comes up.

13. The list of databases registered are shown, per Oracle home (values are read from the /etc/oraortab file). Pick the DB1SB database and click next.

14.  Set the recompilation and audit file destination options as desired in the GUI.  The upgrade process may take 1-2 hours for the upgrade to complete.

  

15.  Start log transport on primary.

SQL> alter system set log_archive_dest_state_2='ENABLE'; 

System altered.

16. Start SQL apply on the upgraded standby, wait for it to get synchronized with primary.

SQL> alter database start logical standby apply immediate;

Database altered.

17.  Query v$database.current_scn and verify that standby apply process has applied logs past that scn.

Primary:

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

     440984

Standby:

SQL> select applied_scn, latest_scn, mining_scn from v$logstdby_progress;

APPLIED_SCN LATEST_SCN MINING_SCN

----------- ---------- ----------

     441019     441024     441020

SQL> /

18.  At this point, the standby has caught up to the primary (It falls behind when it was being upgraded because redo transport and sql apply was off)

19.  Perform a switchover so that the upgraded standby can become the primary.

Checking status on primary:

SQL> select database_role, switchover_status from v$database;

DATABASE_ROLE    SWITCHOVER_STATUS

---------------- --------------------

PRIMARY          TO STANDBY

Initiating the switchover from primary:

SQL> alter database commit to switchover to logical standby;

Database altered.

Completing the switchover on standby:

Checking its status:

SQL> select database_role, switchover_status from v$database;

DATABASE_ROLE    SWITCHOVER_STATUS

---------------- --------------------

LOGICAL STANDBY  TO PRIMARY

Completing the switchover:

SQL> alter database start logical standby apply immediate;

Database altered.

         

20.  The original primary (DB1PR) is now a logical standby and running on older version. Flash the primary back to the starting point of the upgrade operation and shut it down.

SQL> shut immediate;

startup mount;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

ORACLE instance started.

Total System Global Area  835104768 bytes

Fixed Size                  2293880 bytes

Variable Size             230690696 bytes

Database Buffers          595591168 bytes

Redo Buffers                6529024 bytes

Database mounted.

SQL>

SQL> flashback database to restore point before_upgrade;

Flashback complete.

SQL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL>

21. The next step is to mount the original primary (DB1PR) up from the new oracle home. Set ORACLE_HOME and PATH accordingly first.

SQL> startup mount

ORACLE instance started.

Total System Global Area  838860800 bytes

Fixed Size                  2929936 bytes

Variable Size             234883824 bytes

Database Buffers          591396864 bytes

Redo Buffers                9650176 bytes

Database mounted.

22.  Convert the original primary (DB1PR) to a physical standby

SQL> alter database convert to physical standby;

Database altered.

SQL>

23.  Shut down, mount and start Managed Recovery process on the original primary (DB1PR). It will get upgraded when it applies the redo from the former standby (DB1SB)

SQL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

startup mount

ORACLE instance shut down.

SQL> ORACLE instance started.

Total System Global Area  838860800 bytes

Fixed Size                  2929936 bytes

Variable Size             234883824 bytes

Database Buffers          591396864 bytes

Redo Buffers                9650176 bytes

Database mounted.

SQL> recover managed standby database disconnect;

Media recovery complete.

SQL>

Checking archive log sequence on former standby (DB1SB)

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     40

Next log sequence to archive   41

Current log sequence           41

    

24.  Monitor the progress of the catch-up by checking the alert logs on the former primary (DB1PR)

Media Recovery Log /u01/app/oracle/oradata/arc3/1_37_894717897.dbf

Mon Nov 02 15:52:45 2015

Media Recovery Log /u01/app/oracle/oradata/arc3/1_38_894717897.dbf

Mon Nov 02 15:52:47 2015

Media Recovery Log /u01/app/oracle/oradata/arc3/1_39_894717897.dbf

Mon Nov 02 15:52:47 2015

Media Recovery Log /u01/app/oracle/oradata/arc3/1_40_894717897.dbf

Resize operation completed for file# 2, old size 266816K, new size 267840K

Media Recovery Waiting for thread 1 sequence 41 (in transit)

Mon Nov 02 15:52:48 2015

Recovery of Online Redo Log: Thread 1 Group 4 Seq 41 Reading mem 0

  Mem# 0: /u01/app/oracle/oradata/db1pr/sby2.ora

25.  After the standby is caught up, it has been fully upgraded. Next steps is to drop the restore point created to clean up the flashback logs.

SQL> drop restore point before_upgrade;

Restore point dropped.

26.  Optionally you can execute another switchover to restore the site roles or plan to upgrade the COMPATIBLE parameter.