Saturday, June 27, 2020

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;



No comments:

Post a Comment