In this post, I want to share Switchover process on Dataguard Setup.

Db version is 11.2  2 node RAC and standby db is standalone

Operation system is  AIX 7.1

Data Guard Physical Standby Switchover Best Practices using SQL*Plus

1)Verify Managed Recovery is running on the standby

The following query at the standby verifies that managed recovery is running:
SQL> SELECT PROCESS FROM V$MANAGED_STANDBY WHERE PROCESS LIKE ‘MRP%’;

If managed standby recovery is not running or not started with real-time apply, restart managed recovery with real-time apply enabled:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

2)Verify there are no large Gaps
Identify the current sequence number for each thread on the primary database
SQL> SELECT THREAD#, SEQUENCE# FROM V$THREAD;

Verify the target physical standby database has applied up to, but not including the logs from the primary query.
On the standby the following query should be within 1 or 2 of the primary query result.

SQL> SELECT THREAD#, MAX(SEQUENCE#) FROM V$ARCHIVED_LOG
WHERE APPLIED = ‘YES’
AND RESETLOGS_CHANGE# = (SELECT RESETLOGS_CHANGE#
FROM V$DATABASE_INCARNATION WHERE STATUS = ‘CURRENT’)
GROUP BY THREAD#;

3)Verify that the primary database can be switched to the standby role
Query the SWITCHOVER_STATUS column of the V$DATABASE view on the primary database:
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
—————–
TO STANDBY

4)If The Primary is a RAC, then shutdown all secondary primary instances
Only one instance enough , shut down others

5)Switchover the primary to a standby database

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY WITH SESSION SHUTDOWN;

6)Verify that the standby database can be switched to the primary role

Query the SWITCHOVER_STATUS column of the V$DATABASE view on the standby database:

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
—————–
TO PRIMARY

A value of TO PRIMARY or SESSIONS ACTIVE indicates that the standby database is ready to be switched to the primary role.
If neither of these values is returned, verify that redo apply is active and that redo transport is configured and working properly.
Continue to query this column until the value returned is either TO PRIMARY or SESSIONS ACTIVE.

7)Switchover the standby database to a primary

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

8)Open the new primary database
SQL> ALTER DATABASE OPEN;

9)Restart the new standby
If the new standby database (former primary database) was not shutdown since switching it to standby, bring it to the mount state and start managed recovery.
This can be done in parallel to the new primary open.

SQL> SHUTDOWN ABORT;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Advertisements