SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
———- ———- ———- ———- ———- — —————- ————- ———
1 1 544 52428800 1 YES INACTIVE 1584829 21-MAY-11
2 1 545 52428800 1 YES INACTIVE 1598614 21-MAY-11
3 1 546 52428800 1 NO CURRENT 1609237 21-MAY-11
SQL>select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
———- ——- ——- —————————————- —
3 ONLINE /data02/nsndb/redo03.log NO
2 ONLINE /data02/nsndb/redo02.log NO
1 ONLINE /data02/nsndb/redo01.log NO
SQL>Alter database add logfile group 4 '/data02/nsndb/redo04.log'
size 300M reuse;
SQL>Alter database add logfile group 5 '/data02/nsndb/redo05.log'
size 300M reuse;
SQL>Alter database add logfile group 6 '/data02/nsndb/redo06.log'
size 300M reuse;
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
———- ——- ——- —————————————- —
3 ONLINE /data02/nsndb/redo03.log NO
2 ONLINE /data02/nsndb/redo02.log NO
1 ONLINE /data02/nsndb/redo01.log NO
4 ONLINE /data02/nsndb/redo04.log NO
5 ONLINE /data02/nsndb/redo05.log NO
6 ONLINE /data02/nsndb/redo06.log NO
6 rows selected.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
———- ———- ———- ———- ———- — —————- ————- ———
1 1 544 52428800 1 YES INACTIVE 1584829 21-MAY-11
2 1 545 52428800 1 YES INACTIVE 1598614 21-MAY-11
3 1 546 52428800 1 NO CURRENT 1609237 21-MAY-11
4 1 0 314572800 1 YES UNUSED 0
5 1 0 314572800 1 YES UNUSED 0
6 1 0 314572800 1 YES UNUSED 0
6 rows selected.
–Now switch log file 3 times to make these new files online.
SQL>Alter system switch logfile;
SQL>Alter system switch logfile;
SQL>Alter system switch logfile;
–force checkpoint to write all contents to data files.
SQL>Alter system checkpoint;
–Drop the old log files.
SQL>Alter database drop logfile group 1;
SQL>Alter database drop logfile group 2;
SQL>Alter database drop logfile group 3;
SQL>select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
———- ———- ———- ———- ———- — —————- ————- ———
4 1 3302 314572800 1 NO CURRENT 284686002 25-JUN-12
5 1 3300 314572800 1 YES INACTIVE 284403450 25-JUN-12
6 1 3301 314572800 1 YES INACTIVE 284607896 25-JUN-12