Oracle DBA Tips and Techniques

By Arjun Raja

Drop and Recreate Online Redolog Files

Posted by Arjun Raja on July 29, 2009

Method to drop and recreate online redolog files with 2 members to each group.

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: