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
- 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.
- Stop managed recovery on the standby.
SQL>
recover managed standby database cancel;
Media
recovery complete.
SQL>
- Start a log miner build on primary (Redo
transport remains on)
SQL>
execute dbms_logstdby.build();
PL/SQL
procedure successfully completed.
SQL>
- Convert the standby database to a logical
standby.
SQL>
alter database recover to logical standby keep identity;
Database
altered.
- Start SQL apply on the new logical standby
- 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
- Defer redo transport to the standby site
from the primary
SQL>
alter system set log_archive_dest_state_2='DEFER';
System
altered.
- On the standby stop the SQL Apply process.
SQL>
alter database stop logical standby apply;
Database
altered.
- Shut down the logical standby database
SQL>
shutdown immediate;
Database
closed.
Database
dismounted.
ORACLE
instance shut down.
SQL>
- 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.
- On the standby site, Start up the dbua from the
new Oracle home.
- Select
the Upgrade Oracle Database option and click next on the OUI screen that comes
up.
- 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.
- 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.
- Start log transport on primary.
SQL> alter system set log_archive_dest_state_2='ENABLE';
System altered.
- 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.
- 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> /
- 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)
- 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.
- 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>
- 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.
- Convert
the original primary (DB1PR) to a physical standby
SQL> alter database convert to physical standby;
Database altered.
SQL>
- 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
- 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
- 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.
- Optionally
you can execute another switchover to restore the site roles or plan to
upgrade the COMPATIBLE parameter.
