Oracle has a documented process for using Oracle logical standby transient Database (TLS) to minimize downtime during database upgrades. I would like to share my experience in the real world using this process to get an Oracle database migrated. I have to say I recommend this option only if downtime is really, really tight, as it will involve downtime for 2 switchovers to keep the primary running on the same host as it was when the process started. Only one switchover will be required if you keep your primary running on the server where the standby database was running.
In my experience upgrading databases to 19c is a process that may take between 45 min to 2 hours to have databases upgraded. Using another nice tool from Oracle “autoupgrade” simplifies this process a lot, but we are not discussing this tool at this time. so let’s focus on the transient logical standby process using the dbms_rolling procedure.
To use this process, we need at least a primary and a physical standby database. In the end, we will have both databases, primary and standby upgraded to the newest version. The package DBMS_ROLLING will take care of all the process, including switchover, logical transient standby conversion, and convert it back to physical standby. To do this rolling upgrade requires a license for Oracle Active Dataguard.
Primary and standby databases are divided into trailing and leading groups. Target Primary is called a Trailing Group Master (TGM), Target Standby is called a Leading group Master (LGM), and the rest of the standby databases can either be in the trailing or the Leading Groups, depending on their upgrade schedule i.e. in a configuration with multiple standby databases only one standby must be LGM to protect LGM configure Physical standby database. Leading Group must be upgraded first. Database on a trailing group must be started in a new home and like the trailing group master, will be upgraded once the process has been completed for the leading group.
DBMS_ROLLING consists of eight procedures, all needed to complete the process.
The other 2 procedures are used for rollback purposes and to remove any plan configuration
Some data types are not supported by Logical Standby, so we must first validate that our tables do not have any of these unsupported datatypes:.
Unsupported partitioning/table organizations methods:
Tables that are partitioned with the following methods are not supported by Logical Standby:
Steps to be performed
SQL> exec DBMS_ROLLING.INIT_PLAN(FUTURE_PRIMARY=>'test'); PL/SQL procedure successfully completed.
SQL> SELECT REVISION, STATUS, PHASE FROM DBA_ROLLING_STATUS;
REVISION STATUS PHASE
---------- ------------ --------------
0 READY BUILD PENDING
SQL> select scope, name, curval from dba_rolling_parameters order by scope, name;
SQL> exec DBMS_ROLLING.SET_PARAMETER('SWITCH_LGM_LAG_WAIT', '1');
SQL> exec DBMS_ROLLING.SET_PARAMETER('SWITCH_LGM_LAG_TIME', '60');
On standby: SQL> show parameter cluster NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ cluster_database boolean TRUE cluster_database_instances integer 2 cluster_interconnects string SQL> alter system set cluster_database=false scope=spfile; System altered. SQL>shutdown immediate; SQL> startup mount; ORACLE instance started. Total System Global Area 1577058304 bytes Fixed Size 2924832 bytes Variable Size 553651936 bytes Database Buffers 1006632960 bytes Redo Buffers 13848576 bytes Database mounted. SQL> show parameter cluster NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ cluster_database boolean FALSE cluster_database_instances integer 1 cluster_interconnects string SQL> alter database recover managed standby database disconnect from session; SQL> exec dbms_rolling.build_plan; PL/SQL procedure successfully completed. SQL> SELECT INSTID, TARGET, PHASE, DESCRIPTION FROM DBA_ROLLING_PLAN ORDER BY 1; INSTID TARGET PHASE DESCRIPTION ---------- ---------- ---------- ----------------------------------------------------------------- 1 test START Verify database is a primary 2 test START Verify MAXIMUM PROTECTION is disabled 3 test_stby START Verify database is a physical standby ... 65 test FINISH Drop guaranteed restore point DBMSRU_INITIAL 66 test_stby FINISH Drop guaranteed restore point DBMSRU_INITIAL 66 rows selected. SQL> SELECT REVISION, STATUS, PHASE FROM DBA_ROLLING_STATUS; REVISION STATUS PHASE ---------- ------------ --------------- 1 READY START PENDING
SQL> exec DBMS_ROLLING.START_PLAN;
PL/SQL procedure successfully completed.
SQL> SELECT REVISION, STATUS, PHASE FROM DBA_ROLLING_STATUS;
REVISION STATUS PHASE
---------- ------------ ---------------
2 READY SWITCH PENDING
LOGICAL RUNNING NOT STARTED
SQL> SELECT NAME,OPEN_MODE,DATABASE_ROLE FROM V$DATABASE;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
test_stby READ WRITE LOGICAL STANDBY
SQL> Execute DBMS_ROLLING.DESTROY_PLAN;
SQL> select * from dba_rolling_events; SQL> SELECT INSTID, TARGET, PHASE, DESCRIPTION FROM DBA_ROLLING_PLAN ORDER BY 1;
SQL > execute dbms_rolling.switchover; PL/SQL procedure successfully completed. SQL > select db_unique_name,database_role,open_mode from v$database; DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE ------------------------------ ---------------- -------------------- test_stby PRIMARY READ WRITE
column event_time format a35 column message format a65 set lines 150 select * from dba_rolling_events; set lines 250 column target format a15 column phase format a65 column description format a50 SELECT INSTID, TARGET, PHASE, DESCRIPTION FROM DBA_ROLLING_PLAN ORDER BY 1;
Former primary: SQL> alter system set cluster_database=false scope=spfile; System altered. SQL> shutdown immediate; $. oraenv <<< test1 $ echo $ORACLE_HOME #### Make sure this is 19c SQL> startup mount; ###On new Primary upgraded to 19c: SYS >execute dbms_rolling.finish_plan; PL/SQL procedure successfully completed. SYS> select db_unique_name,database_role,open_mode from v$database; DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE ------------------------------ ---------------- -------------------- test_stby PRIMARY READ WRITE SYS> select db_unique_name,database_role,open_mode from v$database; DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE ------------------------------ ---------------- -------------------- test PHYSICAL STANDBY MOUNTED ### If needed perform a switchover to restore original primary running as status prior upgrade. SQL> ALTER DATABASE SWITCHOVER TO test VERIFY; SQL> ALTER DATABASE SWITCHOVER TO test; SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
During the process you may find some odd errors, and also troubleshooting could be very hard due to errors not being as informative as they should. My suggestions: ensure your standby configuration is correct, all parameters are set correctly like log_archive_config, log_archive_dest, etc. also if the load of transactions is very heavy during switchover, it may fail. Give it a try 2 or 3 times, eventually switchover will happen.
In summary, this is a good approach to be followed if your maintenance window is very tight. Still, if you have a larger maintenance window (one hour or more), my suggestion is to use the standard process to upgrade the database just to save the time of preparing and testing this approach.
In a later post will share some of the errors faced during this process.
Ready to optimize your Oracle Database for the future?