Adding and Dropping Redo log files in Oracle Database.
Currently redo log groups are having 50MB size in each group,
Task is to add new redo log groups with 250MB size and dropping 50MB groups..
Current Redo log details
SQL> select group#,sequence#,bytes,archived,status from v$log;
GROUP# SEQUENCE# BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
1 91693 52428800 YES INACTIVE
2 91694 52428800 NO CURRENT
3 91692 52428800 YES INACTIVE
We have 3 groups , let us add 4th,5th and 6th group.
SQL> select MEMBER from v$logfile;
MEMBER
--------------------------------------------------------------------------------
+DATA/ORCLP01/ONLINELOG/group_3.267.1001242535
+FRA/ORCLP01/ONLINELOG/group_3.346.1001242537
+DATA/ORCLP01/ONLINELOG/group_2.260.1001242533
+FRA/ORCLP01/ONLINELOG/group_2.345.1001242533
+DATA/ORCLP01/ONLINELOG/group_1.262.1001242531
+FRA/ORCLP01/ONLINELOG/group_1.344.1001242531
+DATA/ORCLP01/ONLINELOG/group_4.269.1113141311
7 rows selected.
SQL> alter database add logfile group 4 '+DATA' size 250M;
Database altered.
SQL> alter database add logfile group 5 '+DATA' size 250M;
Database altered.
SQL> alter database add logfile group 6 '+DATA' size 250M;
Database altered.
SQL>
Adding Second member in the group
SQL> ALTER DATABASE ADD LOGFILE MEMBER '+DATA' TO GROUP 4;
Database altered.
SQL> ALTER DATABASE ADD LOGFILE MEMBER '+DATA' TO GROUP 5;
Database altered.
SQL> ALTER DATABASE ADD LOGFILE MEMBER '+DATA' TO GROUP 6;
Database altered.
SQL> select group#,sequence#,bytes,archived,status from v$log;
GROUP# SEQUENCE# BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
1 91693 52428800 YES INACTIVE
2 91694 52428800 NO CURRENT
3 91692 52428800 YES INACTIVE
4 0 262144000 YES UNUSED
5 0 262144000 YES UNUSED
6 0 262144000 YES UNUSED
6 rows selected.
Verify the redo logfile status before drop
SQL> alter system switch logfile;
System altered.
SQL> select group#,sequence#,bytes,archived,status from v$log;
GROUP# SEQUENCE# BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
1 91693 52428800 YES INACTIVE
2 91694 52428800 YES ACTIVE
3 91692 52428800 YES INACTIVE
4 91695 262144000 NO CURRENT
5 0 262144000 YES UNUSED
6 0 262144000 YES UNUSED
6 rows selected.
SQL> alter system switch logfile;
System altered.
SQL> select group#,sequence#,bytes,archived,status from v$log;
GROUP# SEQUENCE# BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
1 91693 52428800 YES INACTIVE
2 91694 52428800 YES ACTIVE
3 91692 52428800 YES INACTIVE
4 91695 262144000 YES ACTIVE
5 91696 262144000 NO CURRENT
6 0 262144000 YES UNUSED
6 rows selected.
SQL> alter system switch logfile;
System altered.
SQL> select group#,sequence#,bytes,archived,status from v$log
2 ;
GROUP# SEQUENCE# BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
1 91693 52428800 YES INACTIVE
2 91694 52428800 YES ACTIVE
3 91692 52428800 YES INACTIVE
4 91695 262144000 YES ACTIVE
5 91696 262144000 YES ACTIVE
6 91697 262144000 NO CURRENT
6 rows selected.
SQL> alter system archive log group 2;
alter system archive log group 2
*
ERROR at line 1:
ORA-16013: log 2 sequence# 91694 does not need archiving
SQL> alter system archive log group 3;
alter system archive log group 3
*
ERROR at line 1:
ORA-16013: log 3 sequence# 91692 does not need archiving
SQL> select group#,sequence#,bytes,archived,status from v$log
2 ;
GROUP# SEQUENCE# BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
1 91693 52428800 YES INACTIVE
2 91694 52428800 YES ACTIVE
3 91692 52428800 YES INACTIVE
4 91695 262144000 YES ACTIVE
5 91696 262144000 YES ACTIVE
6 91697 262144000 NO CURRENT
6 rows selected.
Dropping Redo log groups 1,2 and 3
Note: Make sure the status of group to be deleted/dropped should be INACTIVE
and should not be ACTIVE and CURRENT.
DROP LOGFILE GROUP 1
SQL> ALTER DATABASE DROP LOGFILE GROUP 1;
Database altered.
SQL> select group#,sequence#,bytes,archived,status from v$log;
GROUP# SEQUENCE# BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
2 91694 52428800 YES ACTIVE
3 91692 52428800 YES INACTIVE
4 91695 262144000 YES ACTIVE
5 91696 262144000 YES ACTIVE
6 91697 262144000 NO CURRENT
DROP LOGFILE GROUP 3
SQL> ALTER DATABASE DROP LOGFILE GROUP 3;
Database altered.
SQL> select group#,sequence#,bytes,archived,status from v$log;
GROUP# SEQUENCE# BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
2 91694 52428800 YES ACTIVE
4 91695 262144000 YES ACTIVE
5 91696 262144000 YES ACTIVE
6 91697 262144000 NO CURRENT
SQL> alter system switch logfile;
System altered.
SQL> select group#,sequence#,bytes,archived,status from v$log;
GROUP# SEQUENCE# BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
2 91698 52428800 NO CURRENT
4 91695 262144000 YES ACTIVE
5 91696 262144000 YES ACTIVE
6 91697 262144000 YES ACTIVE
SQL> alter system switch logfile;
System altered.
SQL> select group#,sequence#,bytes,archived,status from v$log;
GROUP# SEQUENCE# BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
2 91698 52428800 YES ACTIVE
4 91699 262144000 NO CURRENT
5 91696 262144000 YES INACTIVE
6 91697 262144000 YES ACTIVE
SQL> alter system switch logfile;
System altered.
SQL> select group#,sequence#,bytes,archived,status from v$log;
GROUP# SEQUENCE# BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
2 91698 52428800 YES ACTIVE
4 91699 262144000 YES ACTIVE
5 91700 262144000 NO CURRENT
6 91697 262144000 YES ACTIVE
SQL> alter system archive log group 5;
alter system archive log group 5
*
ERROR at line 1:
ORA-00259: log 5 of open instance ORCLP01 (thread 1) is the current log,
cannot archive
SQL> select group#,sequence#,bytes,archived,status from v$log;
GROUP# SEQUENCE# BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
2 91698 52428800 YES ACTIVE
4 91699 262144000 YES ACTIVE
5 91700 262144000 NO CURRENT
6 91697 262144000 YES ACTIVE
SQL> alter system switch logfile;
System altered.
SQL> select group#,sequence#,bytes,archived,status from v$log;
GROUP# SEQUENCE# BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
2 91698 52428800 YES INACTIVE
4 91699 262144000 YES ACTIVE
5 91700 262144000 YES ACTIVE
6 91701 262144000 NO CURRENT
Drop the redo log group 2
SQL> ALTER DATABASE DROP LOGFILE GROUP 2;
Database altered.
SQL> select group#,sequence#,bytes,archived,status from v$log;
GROUP# SEQUENCE# BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
4 91699 262144000 YES ACTIVE
5 91700 262144000 YES ACTIVE
6 91701 262144000 NO CURRENT
SQL>
See also
Import Running longer time due to huge log switch
http://oracle.com
