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

Wednesday, September 18, 2013

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