11GR2 Oracle Data Guard Switchover Scenarios

Oracle High Availability 11GR2 Oracle Data Guard Switchover Scenarios

11GR2 Oracle Data Guard Switchover Scenarios

16 September 2021 Mehmet Dervişoğlu
High Availability

This article explains Oracle 11G R2 Oracle Data Guard physical standby database switchover scenarios. We have explained already the setup of the physical standby database for Oracle 11G R2 Data Guard. Please click here for this article.

Step 1:

SELECT OPEN_MODE FROM V$DATABASE;

OPEN_MODE

--------------------

READ ONLY

SELECT OPEN_MODE FROM V$DATABASE;

OPEN_MODE

--------------------

READ ONLY

Step 2:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

1	ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Step 3:

SELECT open_mode FROM V$DATABASE;

OPEN_MODE

--------------------

READ ONLY WITH APPLY

SELECT open_mode FROM V$DATABASE;

OPEN_MODE

--------------------

READ ONLY WITH APPLY

Scenario 1:

How to switch over the Primary database to a Standby database and the Standby database to a primary (adb -> standby, sdb->primary)

Step 1:

Check to see if the Primary database is ready to be switched over, needs to be set to “Session active” or “to primary”.

SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS

-----------------

TO STANDBY

SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS

-----------------

TO STANDBY

Step 2:

In the Primary DB

ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;

1	ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;

Step 3:

Closing the Primary DB (to be switched to standby mode), and reopening it in mount mode.

SHUTDOWN ABORT; STARTUP MOUNT;

1	SHUTDOWN ABORT; STARTUP MOUNT;

Step 4:

The Standby (to be set to Primary) database’s status should either be TO PRIMARY or SESSIONS ACTIVE.

SELECT SWITCHOVER_STATUS FROM V$DATABASE;SWITCHOVER_STATUS

-----------------

TO_PRIMARY

SELECT SWITCHOVER_STATUS FROM V$DATABASE;SWITCHOVER_STATUS

-----------------

TO_PRIMARY

Step 5:

To switch the standby db over to a primary (while in the Standby db).

 ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN

1	ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN

Step 6:

Opening the (new) Primary DB.

 ALTER DATABASE OPEN;

1	ALTER DATABASE OPEN;

Step 7:

Set the new Standby log so that it applies the redologs

 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

1	ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Step 8:

The new Standby DB

alter database open

1	alter database open

If the following error appears:

– ORA-10456: cannot open standby database; media recovery session may be in progress

alter database recover managed standby database cancel;

alter database open ;

alter database recover managed standby database cancel;

alter database open ;

To change it to its old state, use the following command:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

1	ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

If the Primary database has become inaccessible, the Standby database should be made the Primary DB. In order to change the faulty Primary DB back to a Standby database, it’s necessary to redo all the steps from the beginning (Setup tab onwards).

Scenario 2:

Step 1:

If the faulty Primary DB can be opened in mount mode, send any archives that haven’t been sent to the standby DB (target_db_name standby db unique name).

Use the following:

ALTER SYSTEM FLUSH REDO TO target_db_name;

1	ALTER SYSTEM FLUSH REDO TO target_db_name;

Checking the updated archived files

SELECT UNIQUE THREAD# AS THREAD, MAX(SEQUENCE#) OVER (PARTITION BY thread#)

AS LAST from V$ARCHIVED_LOG;

SELECT UNIQUE THREAD# AS THREAD, MAX(SEQUENCE#) OVER (PARTITION BY thread#)

AS LAST from V$ARCHIVED_LOG;

Step 2:

If the Primary server is accessible and if there are any archived logs in the Standby db (that’s going to be switched over to the Primary), they should be copied to the new Standby-to-be server.

Run the following command afterward to save them:

ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filespec1';

1	ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filespec1';

Run the following:

SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

1	SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

The output:

THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------

1 90 92

THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#

---------- ------------- --------------

1 90 92

Step 3:

If there are missing archived logs as can be seen in the example, they should be copied to the Standby DB about to be switched Over into a Primary DB and saved using the following line:

ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filespec1';

1	ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filespec1';

Step 4:

Cancel the redo apply in the Primary DB (which is about to switch over into Standby)

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

So far no errors have been encountered then proceed to Step 5.

If there’s an error, continue saving the archivelogs. If we still get error outputs after saving the archivelogs, continue to save the data.

ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;[/crayon]

Step 5:

Check to see if the Standby DB is ready to be switched over to a Primary DB.

 SELECT SWITCHOVER_STATUS FROM V$DATABASE;

1	SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS

—————–

TO PRIMARY

Step 6:

Switching the Standby DB to Primary

ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

alter database open;

ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

alter database open;

Step 7:

The other Standby DBs in the environment

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

1	ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

To switch the original Primary server (adb.sysdba.org) back into a Primary server: retrace the steps shown during setup to StandBY mode and then switch over to Primary.

It may be necessary to update “LOG_ARCHIVE_DEST”.

Useful resources for further reading:

http://docs.oracle.com/cd/E11882_01/backup.112/e10642.pdf

http://www.troug.org/?page_id=42 (Uğur İnal)

http://gavinsoorma.com/2010/03/create-standby-database-using-11g-duplicate-from-active-database/

Written By

sysdba.org