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

Drop Primary logfiles on Physical Standby Database

So we migrated a Physical Standby Database from RAW devices to ASM, after the move I still had to move the Online Redolog files:

SQL>select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                             IS_

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

         1         ONLINE  /dev/vx/rdsk/redovmdg/casprd_REDO1  NO
         1         ONLINE  /dev/vx/rdsk/redovmdg/casprd_REDO1b NO
         2         ONLINE  /dev/vx/rdsk/redovmdg/casprd_REDO3  NO
         2         ONLINE  /dev/vx/rdsk/redovmdg/casprd_REDO3b NO
         3         ONLINE  /dev/vx/rdsk/redovmdg/casprd_REDO5  NO
         3         ONLINE  /dev/vx/rdsk/redovmdg/casprd_REDO5b NO

First step was to change STANDBY_FILE_MANAGEMENT from AUTO to MANUAL:

SQL>alter system set standby_file_management='MANUAL';

System altered.

But simply dropping a  logfile group was not so straight forward:

SQL>ALTER DATABASE DROP logfile group 1;

ALTER DATABASE DROP logfile group 1

*

ERROR at line 1:

ORA-01624: log 1 needed for crash recovery of instance castc (thread 1)

ORA-00312: online log 1 thread 1: '/dev/vx/rdsk/redovmdg/casprd_REDO1'

ORA-00312: online log 1 thread 1: '/dev/vx/rdsk/redovmdg/casprd_REDO1b'

Check the STATUS of the Online Redo log Group:

SQL>SELECT GROUP#, STATUS FROM V$LOG;

    GROUP# STATUS

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

         1 CLEARING
         2 CLEARING
         3 CURRENT

If Status is CLEARING or CURRENT then you cannot drop Online Redo log Group.The STATUS need to be CLEARED, UNUSED or INACTIVE

SQL>ALTER DATABASE CLEAR LOGFILE GROUP 1;

Database altered.

SQL>SELECT GROUP#, STATUS FROM V$LOG;

    GROUP# STATUS

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

         1 UNUSED
         2 CLEARING
         3 CURRENT

SQL>ALTER DATABASE DROP LOGFILE GROUP 1; 

.

SQL>alter database add logfile group 1 ('+PCASDG1','+PCASDG1');

Repeat for all groups and check:

SQL>select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                IS_

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

         1         ONLINE  +PCASDG1/castc/onlinelog/group_1.1148.826447293 NO
         1         ONLINE  +PCASDG1/castc/onlinelog/group_1.1149.826447293 NO
         2         ONLINE  +PCASDG1/castc/onlinelog/group_2.1153.826447427 NO
         2         ONLINE  +PCASDG1/castc/onlinelog/group_2.1152.826447431 NO
         3         ONLINE  +PCASDG1/castc/onlinelog/group_3.1180.826447635 NO
         3         ONLINE  +PCASDG1/castc/onlinelog/group_3.1181.826447641 NO