DROP AND RECREATE ONLINE REDOLOG FILES

Firstly ORACLE will never allow you to drop the current ONLINE redolog file –

Ex :

SQL> select GROUP#,THREAD#,STATUS from v$log;
GROUP# THREAD# STATUS
———- ———- —————-
1 1 CURRENT
2 1 UNUSED
3 1 INACTIVE
4 1 INACTIVE

SQL> alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-01623: log 1 is current log for instance test (thread 1) – cannot drop
ORA-00312: online log 1 thread 1: ‘/u03/oradata/testdb/redo01.log’

Now to drop and recreate online redolog files in the same directory –

SQL> select  member from v$logfile;
MEMBER
——————————————————————————–
/u50/oradata/test/redo4a.log
/u51/oradata/test/redo4b.log
/u50/oradata/test/redo3a.log
/u51/oradata/test/redo3b.log
/u50/oradata/test/redo2a.log
/u51/oradata/test/redo2b.log
/u50/oradata/test/redo1a.log
/u51/oradata/test/redo1b.log

8 rows selected.

Intention is to drop and recreate these online logs with a different size.

SQL> select GROUP#,THREAD#,STATUS from v$log;
GROUP# THREAD# STATUS
———- ———- —————-
1 1 INACTIVE
2 1 INACTIVE
3 1 CURRENT
4 1 INACTIVE

Begin dropping the redolog groups with STATUS=’INACTIVE’ – As mentioned earlier Oracle will not allow you to drop a current online redolog file.

sql > alter database drop logfile group 1;
cd /u50/oradata/test

rm redo1a.log

cd /u51/oradata/test

rm redo1b.log

Recreate group with both members –

sql > alter database add logfile group 1('/u50/oradata/test/log1a.ora','/u51/oradata/test/log1b.ora')size 2048m;
Do the same for the other three groups –

To change the status of a log group from CURRENT to INACTIVE, simply switch a logfile with this command-

sql> alter system switch logfile;
Run the command 2 or 3 times if the group status does not change.

To check status of group remember the command is

sql > select GROUP#,THREAD#,STATUS from v$log;
Also make sure the logfile is physically removed from the disk with the rm command – if you don’t do this and try to recreate a logfile an error will appear similar to this –

Ex: You dropped log group 3 but did not remove the physical file redo03.log from disk /u03/oradata/test and tried to recreate the group.

SQL> alter database add logfile group3 '/u03/oradata/test/redo03.log' size 50m;
alter database add logfile ‘/u03/oradata/test/redo03.log’ size 50m
*
ERROR at line 1:
ORA-01577: cannot add log file ‘/u03/oradata/test/redo03.log’ – file already
part of database