Skip to content

Insight and analysis of technology and business strategy

How to Minimize downtime for 19c upgrade using Oracle logical standby transient Database

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:.

  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

    • 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.
  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.

Top Categories

  • There are no suggestions because the search field is empty.

Tell us how we can help!

dba-cloud-services
Upcoming-Events-banner