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