Tuesday, November 26, 2013

Recover after a failed switchover


A failed swithcover attempt left both databases with a DATABASE_ROLE=PHYSICAL_STANDBY
Steps I followed to recover from the failure and to change the old PRIMARY back to PRIMARY was as follows:

SQL>shutdown immediate
ORA-01092: ORACLE instance terminated. Disconnection forced

Login to sqlplus again as sysdba and do a startup mount

SQL>startup mount
ORACLE instance started.

Total System Global Area 6.8947E+10 bytes
Fixed Size                  2189520 bytes
Variable Size            2.0670E+10 bytes
Database Buffers         4.8050E+10 bytes
Redo Buffers              225488896 bytes
Database mounted.

SQL>SELECT DATABASE_ROLE, status,OPEN_MODE,DB_UNIQUE_NAME, PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS
FROM V$DATABASE, V$INSTANCE
/


DATABASE_ROLE    STATUS       OPEN_MODE            DB_UNIQUE PROTECTION_MODE      PROTECTION_LEVEL     SWITCHOVER_STATUS
---------------- ------------ -------------------- --------- -------------------- -------------------- --------------------
PHYSICAL STANDBY MOUNTED      MOUNTED              casprd    MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  RECOVERY NEEDED


Cancel the MRP process. You may receive “ORA-16136: Managed Standby Recovery not active” if the MRP process is not running.
                                              0
SQL>alter database recover managed standby database cancel;
alter database recover managed standby database cancel
*
ERROR at line 1:
ORA-16136: Managed Standby Recovery not active

Terminate the current switchover to Standby that never completed fully.  
SQL>alter database recover managed standby database finish;

Now switchover to Primary.
SQL>alter database commit to switchover to primary with session shutdown;

Open the database.
SQL>alter database open;

 Confirm DATABASE_ROLE
SQL>SELECT DATABASE_ROLE, status,OPEN_MODE,DB_UNIQUE_NAME, PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUs
FROM V$DATABASE, V$INSTANCE
/


DATABASE_ROLE    STATUS       OPEN_MODE            DB_UNIQUE PROTECTION_MODE      PROTECTION_LEVEL     SWITCHOVER_STATUS
---------------- ------------ -------------------- --------- -------------------- -------------------- --------------------
PRIMARY          OPEN         READ WRITE           casprd    MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  TO STANDBY