Time for action – switchover with a physical standby using SQL*Plus

Perform the following steps using the SQL*Plus connection for both the databases:

  1. We have to check whether the primary database is ready for switchover to standby or not. Check the switchover status from the primary database by issuing the following command and verify that the status is either TO STANDBY or SESSIONS ACTIVE:
    SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO STANDBY

    The previous output shows that the primary database is ready to switch to the standby database role. The SESSIONS ACTIVE status indicates that some user sessions are still connected to the database. Such a case does not pose an obstacle for switchover. When output is SESSIONS ACTIVE, you have to perform switchover using the keyword WITH SESSION SHUTDOWN. This is so that those sessions will be terminated during the switchover.

  2. Perform the switchover command from the primary database.
    SQL> alter database commit to switchover to physical standby with session shutdown;
Database altered.
  3. This step covers what actually happens during switchover in detail. We need to monitor the alert logfile of both the primary and standby databases in parallel. The different types of logfiles are as follows:
    • The switchover-related log from the primary alert logfile:
      Wed Oct 10 16:12:26 2012
alter database commit to switchover to physical standby with session shutdown
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY  [Process Id: 23631] (TURKEY)
    • Prior to switchover, the current log sequence number is 335. After performing switchover, all the transactions will be written to the online redo logfiles and the log switch will be forced on the primary database.
      Wed Oct 10 16:12:30 2012
Archived Log entry 764 added for thread 1 sequence 336 ID 0x4e7c64e3 dest 1:
......
Waiting for potential switchover target to become synchronized...
Wed Oct 10 16:12:47 2012
Active, synchronized Physical Standby  switchover target has been
    • The MRP status on the standby database alert log:
      Wed Oct 10 16:12:47 2012
Media Recovery Log /u02/app/oracle/flash_recovery_area/INDIA_UN/archivelog/2012_10_10/o1_mf_1_337_87bn9793_.arc
Media Recovery Waiting for thread 1 sequence 338
    • The log sequence 337 is also switched and applied on standby. Now all the processes will be terminated and the redo thread of each respective thread will be closed; no further log switches can be performed. At the end, EOR will be generated as follows:
      ARCH: End-Of-Redo Branch archival of thread 1 sequence 338
Archived Log entry 767 added for thread 1 sequence 338 ID 0x4e7c64e3 dest 1:
.......
Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/turkey_un/TURKEY/trace/TURKEY_ora_23631.trc
Archivelog for thread 1 sequence 338 required for standby recovery
Switchover: Primary controlfile converted to standby controlfile succesfully.
    • When EOR is generated, you can view the status of the sequence 338 from the primary database, as shown in the following code:
      SQL> select thread#,sequence#,END_OF_REDO,END_OF_REDO_TYPE from v$archived_log;
   THREAD#  SEQUENCE# END END_OF_RED
---------- ---------- --- ----------
         1        337 NO
         1        337 NO
         1        338 YES SWITCHOVER
    • The sequence 338 including EOR will be applied on the standby database (INDIA) as shown in the following code:
      Resetting standby activation ID 1316775139 (0x4e7c64e3)
Media Recovery End-Of-Redo indicator encountered
Media Recovery Applied until change 3085369
MRP0: Media Recovery Complete: End-Of-REDO (INDIA)
MRP0: Background Media Recovery process shutdown (INDIA)
    • After performing recovery, the switchover process will be completed on the old primary database (TURKEY) as shown in the following code:
      Wed Oct 10 16:12:58 2012
Switchover: Complete - Database shutdown required (TURKEY)
Completed: alter database commit to switchover to physical standby with session shutdown
    • During switchover command execution on the primary database, if you monitor the switchover status of the standby database closely, you can capture it as shown in the following code:
      SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
NOT ALLOWED

SQL> /
SWITCHOVER_STATUS
--------------------
SWITCHOVER PENDING

SQL> /
SWITCHOVER_STATUS
--------------------
TO PRIMARY
  4. Perform switchover from the standby database. By default the switchover status of the standby database will be NOT ALLOWED. After processing switchover from the primary database, during recovery the status will be changed to SWITCHOVER PENDING. Once End-of-Redo is applied on standby, the database will be ready to become primary as shown in the following code:
    SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
TO PRIMARY
  5. Run the SWITCHOVER command on the standby database as shown in the following code:
    SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
Database altered.

    On the alert logfile you will see the following:

    Wed Oct 10 18:01:15 2012
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN
ALTER DATABASE SWITCHOVER TO PRIMARY (INDIA)
Maximum wait for role transition is 15 minutes.
.............
SwitchOver after complete recovery through change 3085369
...............
Standby became primary SCN: 3085367
Switchover: Complete - Database mounted as primary
  6. Now, from the new primary database (INDIA), you can check at what SCN the standby database role been changed, as shown in the following code:
    SQL> select CURRENT_SCN,STANDBY_BECAME_PRIMARY_SCN from v$database;
CURRENT_SCN STANDBY_BECAME_PRIMARY_SCN
----------- --------------------------
    3156173                    3085367
  7. Change the open mode of the new primary to READ-WRITE. After successful switchover from standby to the primary database, the instance status will be MOUNTED as shown in the following code:
    SQL> select db_unique_name,database_role,open_mode from v$database;
DB_UNIQUE_NAME       DATABASE_ROLE    OPEN_MODE
-------------------- ---------------- --------------------
INDIA_UN             PRIMARY          MOUNTED

    Open the database with the following statement:

    SQL> alter database open;
Database altered.
  8. Restart the new standby database and start Redo Apply. After switchover, the new standby instance will be in the NOMOUNT status.
    SQL> select status from v$instance;
STATUS
------------
STARTED

Now perform a clean shutdown with SHUTDOWN IMMEDIATE and then start up the new standby database in the READ ONLY mode if Active Data Guard will be used. Then start Redo Apply on the standby database (TURKEY)

SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
SQL> select db_unique_name,open_mode from v$database;
DB_UNIQUE_NAME  OPEN_MODE
--------------- --------------------
turkey_un       READ ONLY WITH APPLY

If you have multiple standby databases in the Data Guard configuration, start Redo Apply on each standby database.

Tip

After starting Redo Apply on another standby database, whenever EOR is applied on the standby database, the MRP process will be terminated immediately. Then you have to start Redo Apply again.

Performing switchover with a physical standby database using broker

Switchover can also be performed using the Data Guard broker. Managing switchover with the broker is very simple. In SQL*Plus, we have to manage commands from both the primary and standby databases. When using the broker, the SWITCHOVER command is executed from either the primary or the standby database.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.148.107.254