1. check current redo log file setting , we got 3 groups with 1 member.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIV
---------- ---------- ---------- ---------- ---------- ------
STATUS FIRST_CHANGE# FIRST_TIME
-------------------------------- ------------- ------------
1 1 4 33554432 1 YES
INACTIVE 554664 21-JUN-12
2 1 5 33554432 1 NO
CURRENT 559639 21-JUN-12
3 1 3 33554432 1 YES
INACTIVE 544750 21-JUN-12
SQL> desc v$logfile;
Name Null? Type
----------------------------------------- -------- ----------------------------
GROUP# NUMBER
STATUS VARCHAR2(7)
TYPE VARCHAR2(7)
MEMBER VARCHAR2(257)
IS_RECOVERY_DEST_FILE VARCHAR2(3)
2.check redo log filename
SQL> col member format a40
SQL> select * from v$logfile order by group#;
GROUP# STATUS TYPE
---------- -------------- --------------
MEMBER IS_REC
---------------------------------------- ------
1 ONLINE
/webflow/redo/redo01.log NO
2 ONLINE
/webflow/redo/redo02.log NO
3 ONLINE
/webflow/redo/redo03.log NO
3.add member to current redo log group, the file size will auto the same with current one.
SQL> alter database add logfile member '/webflow/redo/redo01b.log' to group 1;
Database altered.
SQL> alter database add logfile member '/webflow/redo/redo02b.log' to group 2;
Database altered.
SQL> alter database add logfile member '/webflow/redo/redo03b.log' to group 3;
Database altered.
3.1 check current 3 groups has 2 members
SQL> select * from v$logfile order by group#;
GROUP# STATUS TYPE
---------- -------------- --------------
MEMBER IS_REC
---------------------------------------- ------
1 ONLINE
/webflow/redo/redo01.log NO
1 INVALID ONLINE
/webflow/redo/redo01b.log NO
2 INVALID ONLINE
/webflow/redo/redo02b.log NO
GROUP# STATUS TYPE
---------- -------------- --------------
MEMBER IS_REC
---------------------------------------- ------
2 ONLINE
/webflow/redo/redo02.log NO
3 INVALID ONLINE
/webflow/redo/redo03b.log NO
3 ONLINE
/webflow/redo/redo03.log NO
6 rows selected.
4. add new group redo log to database
SQL> SQL>
SQL> alter database add logfile group 4
2 ('/webflow/redo/redo04.log', '/webflow/redo/redo04b.log') size 32m;
Database altered.
SQL> alter database add logfile group 5
2 ('/webflow/redo/redo05.log', '/webflow/redo/redo05b.log') size 32m;
Database altered.
4.1 check current redo had 5 group
SQL> select * from v$logfile order by group#;
GROUP# STATUS TYPE
---------- -------------- --------------
MEMBER IS_REC
---------------------------------------- ------
1 ONLINE
/webflow/redo/redo01.log NO
1 INVALID ONLINE
/webflow/redo/redo01b.log NO
2 ONLINE
/webflow/redo/redo02.log NO
GROUP# STATUS TYPE
---------- -------------- --------------
MEMBER IS_REC
---------------------------------------- ------
2 INVALID ONLINE
/webflow/redo/redo02b.log NO
3 INVALID ONLINE
/webflow/redo/redo03b.log NO
3 ONLINE
/webflow/redo/redo03.log NO
GROUP# STATUS TYPE
---------- -------------- --------------
MEMBER IS_REC
---------------------------------------- ------
4 ONLINE
/webflow/redo/redo04.log NO
4 ONLINE
/webflow/redo/redo04b.log NO
5 ONLINE
/webflow/redo/redo05b.log NO
GROUP# STATUS TYPE
---------- -------------- --------------
MEMBER IS_REC
---------------------------------------- ------
5 ONLINE
/webflow/redo/redo05.log NO
10 rows selected.
4.2 check all redo log file status
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIV
---------- ---------- ---------- ---------- ---------- ------
STATUS FIRST_CHANGE# FIRST_TIME
-------------------------------- ------------- ------------
1 1 4 33554432 2 YES
INACTIVE 554664 21-JUN-12
2 1 5 33554432 2 NO
CURRENT 559639 21-JUN-12
3 1 3 33554432 2 YES
INACTIVE 544750 21-JUN-12
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIV
---------- ---------- ---------- ---------- ---------- ------
STATUS FIRST_CHANGE# FIRST_TIME
-------------------------------- ------------- ------------
4 1 0 33554432 2 YES
UNUSED 0
5 1 0 33554432 2 YES
UNUSED 0
5.update new redo log file status.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
5.1 check again, the status must be ACTIVE/CURRENT/INACTIVE
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIV
---------- ---------- ---------- ---------- ---------- ------
STATUS FIRST_CHANGE# FIRST_TIME
-------------------------------- ------------- ------------
1 1 9 33554432 2 YES
ACTIVE 561361 21-JUN-12
2 1 10 33554432 2 NO
CURRENT 561366 21-JUN-12
3 1 8 33554432 2 YES
INACTIVE 561359 21-JUN-12
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIV
---------- ---------- ---------- ---------- ---------- ------
STATUS FIRST_CHANGE# FIRST_TIME
-------------------------------- ------------- ------------
4 1 6 33554432 2 YES
INACTIVE 561355 21-JUN-12
5 1 7 33554432 2 YES
INACTIVE 561357 21-JUN-12
SQL>
留言列表
