--- title: How to Minimize downtime for 19c upgrade using Oracle logical standby transient Database | Official Pythian®® Blog description: Oracle has a documented process for using Oracle logical standby transient Database (TLS) to minimize downtime during database upgrades. I would like to --- [Blog | Pythian ](https://www.pythian.com/blog) # [How to Minimize downtime for 19c upgrade using Oracle logical standby transient Database | Official Pythian®® Blog](https://www.pythian.com/blog/how-to-minimize-downtime-for-19c-upgrade-using-oracle-logical-standby-transient-database) Written by [Hugo Ramirez](https://www.pythian.com/blog/author/hugo-ramirez) | Oct 5, 2022 4:00:00 AM ## Background 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. ## Context 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. ## Rollback 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:. 1. Nested Tables 2. Identity Columns 3. ROWID 4. BFILE 5. Temporal Validity columns 6. PKREF 7. PKOID 8. SDO_RDF_TRIPLE_S Unsupported partitioning/table organizations methods: Tables that are partitioned with the following methods are not supported by Logical Standby: 1. Reference partitioning 2. System partitioning ## Using DBMS_ROLLING Steps to be performed ### 1. Init_plan 1. - 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 ``` ### 2. 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'); ``` ### 3. 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 ```   ### 4. 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.** - The suggestion is to use the autoupgrade tool, and once this is completed, we continue with the next step, switchover. The upgrade process will not be covered here as it could have many details. You can see an example in the official docs: [https://docs.oracle.com/en/database/oracle/oracle-database/19/upgrd/examples-of-autoupgrade-workflows.html#GUID-1A9651AE-854F-47F4-8BAE-166F0A35FB16](https://docs.oracle.com/en/database/oracle/oracle-database/19/upgrd/examples-of-autoupgrade-workflows.html#GUID-1A9651AE-854F-47F4-8BAE-166F0A35FB16) - If things go wrong: up to this point, we only worked on the standby database. If some errors appear that we cannot resolve or our maintenance window is about to end, we can just call *destroy_plan,* and this will roll back our process. The standby database may need to be rebuilt. ``` SQL> Execute DBMS_ROLLING.DESTROY_PLAN; ``` ### 5. 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; ``` ### 6. 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. ## Oracle Database Consulting Services Ready to optimize your Oracle Database for the future?   [View full post](https://www.pythian.com/blog/how-to-minimize-downtime-for-19c-upgrade-using-oracle-logical-standby-transient-database) ```json { "@context" : "http://schema.org", "@type" : "BlogPosting", "author" : { "@type" : "Person", "name" : "Hugo Ramirez" }, "dateModified" : "2026-02-02T07:56:49.743Z", "datePublished" : "2022-10-05T04:00:00Z", "headline" : "How to Minimize downtime for 19c upgrade using Oracle logical standby transient Database", "image" : { "@type" : "ImageObject", "height" : 2160, "url" : "https://resources.pythian.com/hubfs/Imported_Blog_Media/shutterstock_680379580-1.jpg", "width" : 4096 }, "mainEntityOfPage" : "https://www.pythian.com/blog/how-to-minimize-downtime-for-19c-upgrade-using-oracle-logical-standby-transient-database", "publisher" : { "@type" : "Organization", "logo" : { "@type" : "ImageObject", "height" : 60, "url" : "/hs/hsstatic/content_shared_assets/static-1.4092/img/default-amp-logo.png", "width" : 60 }, "name" : "Pythian Blog" } } ```