A colleague requested assistance with an issue he encountered while attempting to perform a switchover from a physical standby to a primary. The environment was two sets of 4 node RACs each running Oracle 11.2.0.4. Both sets of RACs had been started as single node databases before the switchover had been attempted.
The status on the standby (being switched to a primary) was as follows:
SELECT database_role, switchover_status FROM v$database; DATABASE_ROLE SWITCHOVER_STATUS PHYSICAL STANDBY NOT ALLOWED
The alert log indicated that not all logs had been applied to this standby before it was converted to a primary:
Database not available for switchover End-Of-REDO archived log file has not been recovered Incomplete recovery SCN:0:115789224 archive SCN:0:115809474 Database not available for switchover End-Of-REDO archived log file has not been recovered Incomplete recovery SCN:0:115789224 archive SCN:0:115809474 Switchover: Media recovery required - standby not in limbo ORA-16139 signalled during: ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN...
At this point we brought up the database as a standby with the below commands:
ALTER DATABASE MOUNT STANDBY DATABASE; alter database recover managed standby database finish force;
This caused the last archive logs from the primary to be applied and the following to be displayed in the alert log:
Attempt to do a Terminal Recovery (BOSTON1) Media Recovery Start: Managed Standby Recovery (BOSTON1) started logmerger process Wed Jul 25 22:24:53 2018 Managed Standby Recovery not using Real Time Apply Parallel Media Recovery started with 64 slaves Wed Jul 25 22:24:54 2018 Media Recovery Log +DG/BOSTON/1_4912_948659911.arc Identified End-Of-Redo (switchover) for thread 1 sequence 4912 at SCN 0x0.6e71cc2 Resetting standby activation ID 2219145217 (0x84457801) Media Recovery End-Of-Redo indicator encountered Media Recovery Applied through change 115809474 Media Recovery Complete:lter database recover managed standby database finish force End-Of-REDO (BOSTON1) Attempt to set limbo arscn 0:115809474 irscn 0:115809474 Completed: alter database recover managed standby database finish force
After the above, we issued:
alter database commit to switchover to primary with session shutdown;
And the alert log indicated that it was successful:
alter database commit to switchover to primary with session shutdown ALTER DATABASE SWITCHOVER TO PRIMARY (BOSTON1) Maximum wait for role transition is 15 minutes. Backup controlfile written to trace file /opt/oracle/product/diag/rdbms/BOSTON/BOSTON1/trace/BOSTON1_ora_45373.trc SwitchOver after complete recovery through change 115809474 Online log +DG/BOSTON/redo01.log: Thread 1 Group 1 was previously cleared Online log +DG/BOSTON/redo02.log: Thread 1 Group 2 was previously cleared Online log +DG/BOSTON/redo03.log: Thread 2 Group 3 was previously cleared Online log +DG/BOSTON/redo04.log: Thread 2 Group 4 was previously cleared Online log +DG/BOSTON/redo05.log: Thread 3 Group 5 was previously cleared Online log +DG/BOSTON/redo06.log: Thread 3 Group 6 was previously cleared Online log +DG/BOSTON/redo07.log: Thread 4 Group 7 was previously cleared Online log +DG/BOSTON/redo08.log: Thread 4 Group 8 was previously cleared Standby became primary SCN: 115809472 Switchover: Complete - Database mounted as primary Completed: alter database commit to switchover to primary with session shutdown
At this point the database was in the below mode and switchover status:
SELECT open_mode, database_role, switchover_status FROM v$database; OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS MOUNTED PRIMARY NOT ALLOWED
We then enabled the log_archive_dest_state_2 as this was now the primary and issued the below commands:
shutdown immediate; startup;
The database was now in the below mode and switchover status:
SELECT open_mode, database_role, switchover_status FROM v$database; OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS READ WRITE PRIMARY RESOLVABLE GAP
After a couple of minutes, the status changed to:
SELECT open_mode, database_role, switchover_status FROM v$database; OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS READ WRITE PRIMARY SESSIONS ACTIVE
The standby status was as follows:
SELECT open_mode, database_role, switchover_status FROM v$database; OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS MOUNTED PHYSICAL STANDBY NOT ALLOWED
This is an expected status as per “Physical Standby Switchover_status Showing Not Allowed. (Doc ID 1392763.1)”. After the command to switch roles is issued on the primary, the primary will generate a 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”.
We cut a few logs on the new primary and checked to ensure that they were applied on the standby. We then shut down the standby and brought it up as a 4 node RAC. Then we shut down the primary and brought it up as a 4 node RAC.