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
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
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
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
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