Physical Standby Switchover_status Showing Not Allowed

We try to make some switchover test on our RAC db(2 nodes setup) which has Physical Standby. Do not forget, before start your switchover test, you need to close one of the member of the rac instance.

After close one of instance We checked below query result;

Step 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;

Step 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#;

Step 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

In that step, Query returned ” NOT ALLOWED “… So We started to investigate why SWITCHOVER_STATUS shows NOT ALLOWED.

In Oracle documentation explain SWITCHOVER_STATUS column of v$database can have the following values:

NOT ALLOWED – Either this is a standby database and the primary database has not been switched first, or this is a primary database and there are no standby databases.

SESSIONS ACTIVE – Indicates that there are active SQL sessions attached to the primary or standby database that need to be disconnected before the switchover operation is permitted.

SWITCHOVER PENDING – This is a standby database and the primary database switchover request has been received but not processed.

SWITCHOVER LATENT – The switchover was in pending mode, but did not complete and went back to the primary database.

TO PRIMARY – This is a standby database, with no active sessions, that is allowed to switch over to a primary database.

TO STANDBY – This is a primary database, with no active sessions, that is allowed to switch over to a standby database.

RECOVERY NEEDED – This is a standby database that has not received the switchover request.

We check the synchronization status between primary and physical standby. They are no gap and any issues on sync. Physical standby has applied the lastly generated archived redo log sequence. But We still keep to get same result from query… v$database switchover_status shows “not allowed”

While We make search at metalink We found this note:  Physical Standby Switchover_status Showing Not Allowed. (Doc ID 1392763.1) 
From note:
It is expected to see this status in physical standby.When we are certain that Primary and target standby are in sync,We can then proceed with switchover exercise regardless of “not allowed” status in Physical stand

Switchover always originates from Primary database. On the request of switchover sql statement “alter database commit to switchover to physical standby with session shutdown”, Primary will generate special marker called EOR (end-of-redo) that is placed in the header of online redo log sequence. So this online redo log sequence will be archived locally and sent to all standby databases.

Only upon receiving and applying EOR (end-of-redo), v$database.switchover_status will change from “not allowed” to “to primary” or “sessions active”.

So this is expected value for $database.switchover_status cloumn. We keep going switchover test and it goes so smooth.

You can find How to  Performing Switchover on Oracle Standby Database steps in here

 

Advertisements