Switchover and Switchback simplied in Oracle 12c

    Oct 13, 2017 8:51:00 AM by Syed Jaffar Hussain

    Business continuity (Disaster Recovery) has become a very critical factor for every business, especially in the financial sectors. Most of the banks are tending to have their regular DR test to meet the central bank regulation on DR testing capabilities.

    Very recently, there was a request from one of the clients to perform a reverse replication and rollback (i.,e switchover & switchback) between the HO and DR for one of the business critical databases. Similar activities performed with easy on pre 12c databases. However, this was my first experience with Oracle 12c. After spending a bit of time to explore whats new in 12c Switchover, it was amazing to learn how 12c simplified the procedure. So, I decided to write a post on my experience.

    This post demonstrates how Switchover and Switchback procedure is simplified in Oracle 12c.

    The following is used in the scenario:

    • 2 instances Oracle 12c RAC primary database ( IMMPRD)
    • Single instance Oracle 12c RAC Standby database ( IMMSDB)

    Look at the current status of the both databases:

    -- Primary

    IMMPRD> select status,instance_name,database_role from v$database,v$instance;
    STATUS       INSTANCE_NAME    DATABASE_ROLE
    ------------ ---------------- ----------------
    OPEN         IMMPRD1           PRIMARY

     

    -- Standby

    IMMSDB> select status,instance_name,database_role from v$database,v$instance;
    STATUS       INSTANCE_NAME    DATABASE_ROLE
    ------------ ---------------- ----------------
    OPEN         IMMSDB1           PHYSICAL STANDBY

    Before getting into the real action, validate the following to avoid any failures during the course of role transition:

    • Ensure log_archive_dest_2 is configured on PRIMARY and STANDBY databases
    • Media Recovery Process (MRP) is active on STANDBY and in sync with PRIMARY database
    • Create STANDBY REDO logs on PRIMARY, if not exists
    • FAL_CLIENT & FAL_SERVER parameters set on both databases
    • Verify TEMP tablespaces on STANDBY, add them if required, as TEMPFFILES created after STANDBY creation won’t be propagated to STANDBY site.

     

    Pre-Switchover in 12c

    For a smooth role transition, it is important to have everything in-place and in sync. Pre-Oracle 12c, a set of commands used on PRIMARY and STANDBY to validate the readiness of the systems. However, with Oracle 12c, this is simplified with the ALTER DATABASE SWITCHOVER VERIFY command. The command performs the following set of actions:

    • Verifies minimum Oracle version, i.e, Oracle 12.1
    • PRIMRY DB REDO SHIPPING
    • Verify MRP status on Standby database

    Let’s run the command on the primary database to validate if the environments are ready for the role transition.

    IMMPRD>  alter database switchover to IMMSDB verify;
     alter database switchover to IMSDB verify
    *
    ERROR at line 1:
    ORA-16475: succeeded with warnings, check alert log for more details

    When the command is executed, an ORA-16475 error was encountered. For more details, lets walk through the PRIMARY and STANDBY databases alert.log file, and pay attention to the SWITCHOVER VERIFY WARNING.

    --primary database alert.log

    Fri Oct 13 11:16:00 2017
    SWITCHOVER VERIFY: Send VERIFY request to switchover target IMSDB
    SWITCHOVER VERIFY COMPLETE
    SWITCHOVER VERIFY WARNING: switchover target has no standby database defined in LOG_ARCHIVE_DEST_n parameter.
    If the switchover target is converted to a primary database, the new primary database will not be protected.

    ORA-16475 signalled during:  alter database switchover to IMSDB verify...

    The LOG_ARCHIVE_DEST_2 parameter was not set on the STANDBY database and the VERIFY command produced the warning. After setting the parameter on the STANDBY, the verify command was re-ran, and it went well this time.

    IMMPRD> alter database switchover to IMMSDB verify;
    Database altered.
    PRIMARY database alert.log confirms no WARINGS
    alter database switchover to IMMSDB verify
    Fri Oct 13 08:49:20 2017
    SWITCHOVER VERIFY: Send VERIFY request to switchover target IMMSDB
    SWITCHOVER VERIFY COMPLETE
    Completed: alter database switchover to IMMSDB verify

     

    Switchover in 12c 

    After successful validation and confirmation about the DBs readiness for the role transition, execute the actual switchover command on the primary database. (advised to view the alert.log files of PRIMARY and STANDBY instances).

    IMMPRD> alter database switchover to IMMSDB;

     

    Database altered.

    Let’s walk through the PRIMARY and STANDBY database alert.log files to review what Oracle has internally done.

    --primary database alert.log

    alter database switchover to IMMSDB
    Fri Oct 13 08:50:21 2017
    Starting switchover [Process ID: 302592]
    Fri Oct 13 08:50:21 2017
    ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY [Process Id: 302592] (IMMPRD1)
    Waiting for target standby to receive all redo
    Fri Oct 13 08:50:21 2017
    Waiting for all non-current ORLs to be archived...
    Fri Oct 13 08:50:21 2017
    All non-current ORLs have been archived.
    Fri Oct 13 08:50:21 2017
    Waiting for all FAL entries to be archived...
    Fri Oct 13 08:50:21 2017
    All FAL entries have been archived.
    Fri Oct 13 08:50:21 2017
    Waiting for dest_id 2 to become synchronized...
    Fri Oct 13 08:50:22 2017
    Active, synchronized Physical Standby switchover target has been identified
    Preventing updates and queries at the Primary
    Generating and shipping final logs to target standby
    Switchover End-Of-Redo Log thread 1 sequence 24469 has been fixed
    Switchover End-Of-Redo Log thread 2 sequence 23801 has been fixed
    Switchover: Primary highest seen SCN set to 0x960.0x8bcd0f48
    ARCH: Noswitch archival of thread 2, sequence 23801
    ARCH: End-Of-Redo Branch archival of thread 2 sequence 23801
    ARCH: LGWR is scheduled to archive destination LOG_ARCHIVE_DEST_2 after log switch
    ARCH: Standby redo logfile selected for thread 2 sequence 23801 for destination LOG_ARCHIVE_DEST_2
    ARCH: Noswitch archival of thread 1, sequence 24469
    ARCH: End-Of-Redo Branch archival of thread 1 sequence 24469
    ARCH: LGWR is scheduled to archive destination LOG_ARCHIVE_DEST_2 after log switch
    ARCH: Standby redo logfile selected for thread 1 sequence 24469 for destination LOG_ARCHIVE_DEST_2
    ARCH: Archiving is disabled due to current logfile archival
    Primary will check for some target standby to have received all redo
    Waiting for target standby to apply all redo
    Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/imprd/IMPRD1/trace/IMPRD1_ora_302592.trc
    Converting the primary database to a new standby database
    Clearing standby activation ID 627850507 (0x256c3d0b)
    The primary database controlfile was created using the
    'MAXLOGFILES 192' clause.
    There is space for up to 186 standby redo logfiles
    Use the following SQL commands on the standby database to create
    standby redo logfiles that match the primary database:
    ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 104857600;
    ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 104857600;
    ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 104857600;
    ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 104857600;
    ALTER DATABASE ADD STANDBY LOGFILE 'srl5.f' SIZE 104857600;
    ALTER DATABASE ADD STANDBY LOGFILE 'srl6.f' SIZE 104857600;
    ALTER DATABASE ADD STANDBY LOGFILE 'srl7.f' SIZE 104857600;
    Archivelog for thread 1 sequence 24469 required for standby recovery
    Archivelog for thread 2 sequence 23801 required for standby recovery
    Switchover: Primary controlfile converted to standby controlfile succesfully.
    Switchover complete. Database shutdown required
    USER (ospid: 302592): terminating the instance
    Fri Oct 13 08:50:44 2017
    Instance terminated by USER, pid = 302592
    Completed: alter database switchover to IMMSDB
    Shutting down instance (abort)

    --standby database alert.log

    SWITCHOVER: received request 'ALTER DTABASE COMMIT TO SWITCHOVER  TO PRIMARY' from primary database.

    Fri Oct 13 08:50:32 2017
    ALTER DATABASE SWITCHOVER TO PRIMARY (IMMSDB1)
    Maximum wait for role transition is 15 minutes.
    Switchover: Media recovery is still active
    Role Change: Canceling MRP - no more redo to apply

    SMON: disabling cache recovery
    Fri Oct 13 08:50:41 2017
    Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/imsdb/IMMSDB1/trace/IMMSDB1_rmi_120912.trc
    SwitchOver after complete recovery through change 10310266982216
    Online logfile pre-clearing operation disabled by switchover
    Online log +DATAC1/IMMSDB/ONLINELOG/group_1.3018.922980623: Thread 1 Group 1 was previously cleared
    Standby became primary SCN: 10310266982214
    Switchover: Complete - Database mounted as primary
    SWITCHOVER: completed request from primary database.
    Fri Oct 13 08:51:11 2017

    At this point-in-time, the new PRIMARY database is in MOUNT state, so you need to OPEN the database.

    IMMSDB> alter database open

    And startup the STANDBY database and enable MRP: (below is the active standby database command)

    IMMPRD> startup
    IMMPRD> recover managed standby database using current logfile disconnect from session;

    Post Switchover, run through the following:

    IMMSDB> alter system switch logfile;
    IMMSDB> select dest_id,error,status from v$archive_dest where dest_id=2;
    IMMSDB> select max(sequence#),thread# from v$log_history group by thread#;
    IMMSDB> select max(sequence#)  from v$archived_log where applied='YES' and dest_id=2;

    On Standby database

    IMMPRD> select thread#,sequence#,process,status from gv$managed_standby;
    -- in 12.2, use gv$dataguard_status instead of gv$managed_standby view
    IMMPRD> select max(sequence#),thread# from v$archived_log group by thread#;

    You can also enable the trace on primary and standby before performing the role transition to analyze any failures during the procedure. Use the below procedure on the PRIMARY database to enable the tracing:

    SQL> alter system set log_archive_trace=8191;  -- enabling trace
    SQL> alter system set log_archive_trace=0;      -- disabling trace

     

    Switchback

    To revert (switch back) to the previous situation, perform the same action. Remember, now, your primary is your previous STANDBY and standby is previous PRIMARY. References:
    12c Data guard Switchover Best Practices using SQLPLUS (Doc ID 1578787.1) 

     

    Tags: Oracle

    Syed Jaffar Hussain

    Written by Syed Jaffar Hussain

    An Oracle Database Expert for over 15 years from his 20 years of Information Technology (IT) career. Over the past 15 years of Oracle journey, he involved with several local and large scaled international banks where he implemented and managed highly complex cluster and non-cluster environments with over 100’s of business critical databases. Recognizing his efforts and contribution towards the Oracle community, Oracle awarded him the prestigious ‘Best DBA of the year, 2011’ and Oracle ACE Director status. He also acquired industry best Oracle credentials, Oracle Certified Master (OCM), Oracle RAC Expert, OCP DBA 8i,9i,10g & 11g in addition to ITIL Expertise. Syed is an active Oracle speaker, regularly presents technical sessions and webinars on various Oracle database technologies at many Oracle events. You can visit his technical blog, http://jaffardba.blogspot.com where he discuss and writes the workaround/solution about the issues confronted from his day-to-day activities. Apart from being the part of the core Technical Review committee member for a few Oracle technology oriented books, he also co-authored an Oracle 11g R1/R2 Real Application Cluster Essentials and Oracle Expert RAC books. His blog can be found at http://jaffardba.blogspot.com/