Loading....

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

Please follow and like us:

Last Update: July 29, 2009  

July 29, 2009 88 Arjun Raja
Total 0 Votes:
0

Tell us how can we improve this post?

+ = Verify Human or Spambot ?

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?

, ,

Leave a Comment

Your email address will not be published. Required fields are marked *

*
*

Social media & sharing icons powered by UltimatelySocial
Back To Top

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?