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.
- INIT_PLAN – Identifies database in the configuration and initializes plan parameters to default
- SET_PARAMETER – Used to adjust any parameter generated by init_plan if required.
- BUILD_PLAN – Validates plan parameters and create or modify a rolling_plan
- START_PLAN – Starts Plan execution. It converts standby to temporary logical standby, it is in this phase where the upgrade should occur.
- SWITCHOVER – After maintenance is completed on Oracle logical standby transient Database, this procedure executes switchover to logical standby. After the switchover, the original standby will be a new primary open read/write, and the original primary will be mounted as a logical standby database.
- FINISH_PLAN – converts logical standby to physical standby and synchronization between primary and standby is performed.
The other 2 procedures are used for rollback purposes and to remove any plan configuration
- ROLLBACK_PLAN – restores the entire configuration to the way it was before start_plan. This can happen only if the switchover has not been executed.
- DESTROY_PLAN – this procedure destroys an existing upgrade_plan.
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:.
- Nested Tables
- Identity Columns
- ROWID
- BFILE
- Temporal Validity columns
- PKREF
- PKOID
- SDO_RDF_TRIPLE_S
Unsupported partitioning/table organizations methods:
Tables that are partitioned with the following methods are not supported by Logical Standby:
- Reference partitioning
- System partitioning
Using DBMS_ROLLING
Steps to be performed
-
Init_plan
- execute Init_plan procedure on the primary database.
SQL> exec DBMS_ROLLING.INIT_PLAN(FUTURE_PRIMARY=>'test');
PL/SQL procedure successfully completed.
- Check status from dba_rolling_status and do not continue until status is ready
SQL> SELECT REVISION, STATUS, PHASE FROM DBA_ROLLING_STATUS;
REVISION STATUS PHASE
---------- ------------ --------------
0 READY BUILD PENDING
-
Change parameters if required
- In this case, change apply lag to fall between 60 seconds before switching over to the future primary
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');
-
Build Plan:
- Note: the standby database should be mounted and the parameter cluster_database set to false (even if your standby is RAC), otherwise build_plan may raise an error. Follows an example of starting the build_plan phase on a RAC standby DB making those adjustments.
- Build plan process
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
-
Convert to TLS (Oracle logical standby transient Database) and upgrade standby
- Execute START_PLAN to convert the physical standby DB to a Logical standby DB
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
- Upgrade TLS standby to 19c.
-
Switchover
- Make sure logical standby is in sync with the primary
SQL> select * from dba_rolling_events;
SQL> SELECT INSTID, TARGET, PHASE, DESCRIPTION FROM DBA_ROLLING_PLAN ORDER BY 1;
- Make sure logical apply is running
- On the current primary database execute the switchover
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
- During this process, you may see it raises errors and switchover is not completed. Try again and use the below views to troubleshoot issues if any
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;
-
Finish_plan
- If everything goes well, we are ready to finish the plan, which means the new logical standby will be converted to physical standby and upgraded to 19c.
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;
Notes:
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.
No Comments Yet
Let us know what you think