Tag: v$log

Here is a newly created database redo log information:

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
———- ———- ———- ———- ———- — —————- ————- ——————-
1 1 17 52428800 1 NO INACTIVE 996238 06/09/2007 22:01:59
2 1 18 52428800 1 NO INACTIVE 1006432 06/09/2007 22:13:32
3 1 19 52428800 1 NO CURRENT 1036439 07/09/2007 09:56:44

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER
———- ——- ——- ————————————————-
3 ONLINE /database/data/redo03.log
2 ONLINE /database/data/redo02.log
1 ONLINE /database/data/redo01.log

Here is how i changed this to five 200M redo logs:

SQL> alter database add logfile group 4 (‘/database/data/redo04.log’) size 200M;
SQL> alter database add logfile group 5 (‘/database/data/redo05.log’) size 200M;

while running following sql commands, if you hit an error like this:

ORA-01623: log 3 is current log for instance RPTDB (thread 1) – cannot drop
ORA-00312: online log 3 thread 1: ‘/database/data/redo03.log’

you should run ” alter system switch logfile;” until current log is 4 or 5. Then execute “alter system checkpoint;”

SQL> alter database drop logfile group 1;
SQL> alter database drop logfile group 2;
SQL> alter database drop logfile group 3;

then move (or maybe drop) old redo logs

mv /database/data/redo01.log /database/data/redo01_old.log
mv /database/data/redo02.log /database/data/redo02_old.log
mv /database/data/redo03.log /database/data/redo03_old.log

finally

SQL> alter database add logfile group 1 (‘/database/data/redo01.log’) size 200M;
SQL> alter database add logfile group 2 (‘/database/data/redo02.log’) size 200M;
SQL> alter database add logfile group 3 (‘/database/data/redo03.log’) size 200M;