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