＝＝＝＝＝＝＝redo log file＝＝＝＝＝＝＝bash-2.05b$ sqlplus "/as sysdba"SQL*Plus: Release 9.2.0.4.0 - Production on Fri Apr 17 16:22:45 2009Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.Connected to:Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit ProductionWith the OLAP optionJServer Release 9.2.0.4.0 - ProductionSQL> select thread#,status,instance from v$thread; THREAD# STATUS INSTANCE---------- ------ ---------------- 1 CLOSED xx1 2 OPEN xx2 SQL> col member format a50SQL> set pagesize 30SQL> select THREAD#,SEQUENCE#,BYTES,ARCHIVED,STATUS from v$log ; # THREAD# SEQUENCE# BYTES ARC STATUS---------- ---------- ---------- ---------- --- ---------------- 1 1 57 251658240 YES INACTIVE 2 1 58 251658240 YES INACTIVE 3 1 59 251658240 YES INACTIVE 4 1 60 251658240 NO CURRENT 5 2 178 251658240 NO CURRENT 6 2 175 251658240 YES INACTIVE 7 2 177 251658240 YES INACTIVE 8 2 176 251658240 YES INACTIVE UNUSED - Online redo log has never been written to. This is the state of a redo log that was just added, or just after a RESETLOGS, when it is not the current redo log.CURRENT - Current redo log. This implies that the redo log is active. The redo log could be open or closed.ACTIVE - Log is active but is not the current log. It is needed for crash recovery. It may be in use for block recovery. It may or may not be archived.CLEARING - Log is being re-created as an empty log after an ALTER DATABASE CLEAR LOGFILE statement. After the log is cleared, the status changes to UNUSED.CLEARING_CURRENT - Current log is being cleared of a closed thread. The log can stay in this status if there is some failure in the switch such as an I/O error writing the new log header.INACTIVE - Log is no longer needed for instance recovery. It may be in use for media recovery. It might or might not be archived.CLEARING_CURRENT日志状态不常见可以通过下面的办法监控日志的每一个状态：创建一个监控表：create table test as (select STATUS from v$log where 1 = 0) ;设置一个开关：create table stop(x int);truncate table test;truncate table stop;监控：declare l_n number; begin loop insert into test ( select STATUS from v$log ); commit; select count(*) into l_n from stop; exit when l_n > 0; end loop; end;/打开另一个session21 添加一个logfile2 切换一次3 ALTER DATABASE CLEAR LOGFILE GROUP 6;insert into stop values(1);commit;检查日志状态：select distinct STATUS from test;通过这种办法可以更好的理解日志的每一种状态SQL> alter database add logfile thread 2 group 7 'D:\LOG_DIR\REDO7A.LOG' size 10M;数据库已更改。SQL> alter database add logfile thread 2 group 8 'D:\LOG_DIR\REDO7B.LOG' size 10M;数据库已更改。SQL> alter database enable public thread 2;数据库已更改。SQL> select thread#,status,instance from v$thread; THREAD# STATUS INSTANCE---------- ------ -------------------------------------------------------------------------------- 1 OPEN train 2 CLOSED UNNAMED_INSTANCE_2 SQL> select * from v$logfile; # STATUS TYPE MEMBER---------- ------- ------- -------------------------------------------------- 1 ONLINE /dev/rredo1_11_n 1 ONLINE /dev/rredo1_12_n 2 ONLINE /dev/rredo1_21_n 2 ONLINE /dev/rredo1_22_n 5 ONLINE /dev/rredo1_51_n 5 ONLINE /dev/rredo1_52_n 6 STALE ONLINE /dev/rredo1_61_n 6 STALE ONLINE /dev/rredo1_62_n 7 STALE ONLINE /dev/rredo1_71_n 7 STALE ONLINE /dev/rredo1_72_n 8 STALE ONLINE /dev/rredo1_81_n 8 STALE ONLINE /dev/rredo1_82_n 3 ONLINE /dev/rredo1_31_n 3 ONLINE /dev/rredo1_32_n 4 ONLINE /dev/rredo1_41_n 4 ONLINE /dev/rredo1_42_nINVALID - File is inaccessibleSTALE - File's contents are incompleteDELETED - File is no longer usednull - File is in use-------------------------------------------------SQL> alter system switch logfile;System altered.SQL> /System altered.SQL> /System altered.SQL> select #,THREAD#,SEQUENCE#,BYTES,ARCHIVED,STATUS from v$log; # THREAD# SEQUENCE# BYTES ARC STATUS---------- ---------- ---------- ---------- --- ---------------- 1 1 61 251658240 NO CURRENT 2 1 58 251658240 YES INACTIVE 3 1 59 251658240 YES INACTIVE 4 1 60 251658240 YES INACTIVE 5 2 178 251658240 YES INACTIVE 6 2 179 251658240 YES INACTIVE 7 2 181 251658240 NO CURRENT 8 2 180 251658240 YES INACTIVE8 rows selected.-- drop logfile SQL> alter database drop logfile group 5;Database altered.SQL> select #,THREAD#,SEQUENCE#,BYTES,ARCHIVED,STATUS from v$log; # THREAD# SEQUENCE# BYTES ARC STATUS---------- ---------- ---------- ---------- --- ---------------- 1 1 61 251658240 NO CURRENT 2 1 58 251658240 YES INACTIVE 3 1 59 251658240 YES INACTIVE 4 1 60 251658240 YES INACTIVE 6 2 179 251658240 YES INACTIVE 7 2 181 251658240 NO CURRENT 8 2 180 251658240 YES INACTIVE7 rows selected.-- add logfile SQL> alter database add logfile group 5 ('/dev/rredo1_51_n','/dev/rredo1_52_n') size 251658240 reuse;Database altered.SQL> select #,THREAD#,SEQUENCE#,BYTES,ARCHIVED,STATUS from v$log where THREAD#=2; # THREAD# SEQUENCE# BYTES ARC STATUS---------- ---------- ---------- ---------- --- ---------------- 5 2 0 251658240 YES UNUSED 6 2 179 251658240 YES INACTIVE 7 2 181 251658240 NO CURRENT 8 2 180 251658240 YES INACTIVESQL> select * from v$logfile; # STATUS TYPE MEMBER---------- ------- ------- -------------------------------------------------- 1 ONLINE /dev/rredo1_11_n 1 ONLINE /dev/rredo1_12_n 2 ONLINE /dev/rredo1_21_n 2 ONLINE /dev/rredo1_22_n 5 ONLINE /dev/rredo1_51_n 5 ONLINE /dev/rredo1_52_n 6 ONLINE /dev/rredo1_61_n 6 ONLINE /dev/rredo1_62_n 7 ONLINE /dev/rredo1_71_n 7 ONLINE /dev/rredo1_72_n 8 ONLINE /dev/rredo1_81_n 8 ONLINE /dev/rredo1_82_n 3 ONLINE /dev/rredo1_31_n 3 ONLINE /dev/rredo1_32_n 4 ONLINE /dev/rredo1_41_n 4 ONLINE /dev/rredo1_42_n16 rows selected.-- drop logfile member SQL> alter database drop logfile member '/dev/rredo1_61_n' ;Database altered.SQL> select THREAD#,SEQUENCE#,MEMBERS,BYTES,ARCHIVED,STATUS from v$log where THREAD#=2; # THREAD# SEQUENCE# MEMBERS BYTES ARC STATUS---------- ---------- ---------- ---------- ---------- --- ---------------- 5 2 0 2 251658240 YES UNUSED 6 2 179 1 251658240 YES INACTIVE 7 2 181 2 251658240 NO CURRENT 8 2 180 2 251658240 YES INACTIVESQL> select * from v$logfile; # STATUS TYPE MEMBER---------- ------- ------- -------------------------------------------------- 1 ONLINE /dev/rredo1_11_n 1 ONLINE /dev/rredo1_12_n 2 ONLINE /dev/rredo1_21_n 2 ONLINE /dev/rredo1_22_n 5 ONLINE /dev/rredo1_51_n 5 ONLINE /dev/rredo1_52_n 6 ONLINE /dev/rredo1_62_n 7 ONLINE /dev/rredo1_71_n 7 ONLINE /dev/rredo1_72_n 8 ONLINE /dev/rredo1_81_n 8 ONLINE /dev/rredo1_82_n 3 ONLINE /dev/rredo1_31_n 3 ONLINE /dev/rredo1_32_n 4 ONLINE /dev/rredo1_41_n 4 ONLINE /dev/rredo1_42_n15 rows selected.-- add logfile member SQL> alter database add logfile member '/dev/rredo1_61_n' to group 6;Database altered.SQL> select #,THREAD#,SEQUENCE#,MEMBERS,BYTES,ARCHIVED,STATUS from v$log where THREAD#=2; # THREAD# SEQUENCE# MEMBERS BYTES ARC STATUS---------- ---------- ---------- ---------- ---------- --- ---------------- 5 2 0 2 251658240 YES UNUSED 6 2 179 2 251658240 YES INACTIVE 7 2 181 2 251658240 NO CURRENT 8 2 180 2 251658240 YES INACTIVESQL> select * from v$logfile; # STATUS TYPE MEMBER---------- ------- ------- -------------------------------------------------- 1 ONLINE /dev/rredo1_11_n 1 ONLINE /dev/rredo1_12_n 2 ONLINE /dev/rredo1_21_n 2 ONLINE /dev/rredo1_22_n 5 ONLINE /dev/rredo1_51_n 5 ONLINE /dev/rredo1_52_n 6 INVALID ONLINE /dev/rredo1_61_n 6 ONLINE /dev/rredo1_62_n 7 ONLINE /dev/rredo1_71_n 7 ONLINE /dev/rredo1_72_n 8 ONLINE /dev/rredo1_81_n 8 ONLINE /dev/rredo1_82_n 3 ONLINE /dev/rredo1_31_n 3 ONLINE /dev/rredo1_32_n 4 ONLINE /dev/rredo1_41_n 4 ONLINE /dev/rredo1_42_n16 rows selected.SQL> alter system switch logfile;System altered.SQL> /System altered.SQL> /System altered.SQL> /System altered.SQL> /System altered.SQL> select * from v$logfile; # STATUS TYPE MEMBER---------- ------- ------- -------------------------------------------------- 1 ONLINE /dev/rredo1_11_n 1 ONLINE /dev/rredo1_12_n 2 ONLINE /dev/rredo1_21_n 2 ONLINE /dev/rredo1_22_n 5 ONLINE /dev/rredo1_51_n 5 ONLINE /dev/rredo1_52_n 6 ONLINE /dev/rredo1_61_n 6 ONLINE /dev/rredo1_62_n 7 ONLINE /dev/rredo1_71_n 7 ONLINE /dev/rredo1_72_n 8 ONLINE /dev/rredo1_81_n 8 ONLINE /dev/rredo1_82_n 3 ONLINE /dev/rredo1_31_n 3 ONLINE /dev/rredo1_32_n 4 ONLINE /dev/rredo1_41_n 4 ONLINE /dev/rredo1_42_n16 rows selected.SQL> select #,THREAD#,SEQUENCE#,MEMBERS,BYTES,ARCHIVED,STATUS from v$log where THREAD#=2; # THREAD# SEQUENCE# MEMBERS BYTES ARC STATUS---------- ---------- ---------- ---------- ---------- --- ---------------- 5 2 186 2 251658240 NO CURRENT 6 2 183 2 251658240 YES INACTIVE 7 2 185 2 251658240 YES INACTIVE 8 2 184 2 251658240 YES INACTIVE SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.bash-2.05b$ lspv -Lhdisk0 0052db8d1d6c6d1a rootvg hdisk1 0052db8d20ff9621 rootvg hdisk2 0052db8d2103e69d datavg hdisk3 0052db8d2106f554 datavg bash-2.05b$ lspv hdisk2 PHYSICAL VOLUME: hdisk2 VOLUME : datavgPV IDENTIFIER: 0052db8d2103e69d VG IDENTIFIER 0052db8d00004c00000000fe2103f6b6PV STATE: active STALE PARTITIONS: 0 ALLOCATABLE: yesPP SIZE: 256 megabyte(s) LOGICAL VOLUMES: 29TOTAL PPs: 546 (139776 megabytes) VG DESCRIPTORS: 2FREE PPs: 58 (14848 megabytes) HOT SPARE: noUSED PPs: 488 (124928 megabytes) FREE DISTRIBUTION: 00..00..00..00..58 USED DISTRIBUTION: 110..109..109..109..51 bash-2.05b$ lspv hdisk3PHYSICAL VOLUME: hdisk3 VOLUME : datavgPV IDENTIFIER: 0052db8d2106f554 VG IDENTIFIER 0052db8d00004c00000000fe2103f6b6PV STATE: active STALE PARTITIONS: 0 ALLOCATABLE: yesPP SIZE: 256 megabyte(s) LOGICAL VOLUMES: 40TOTAL PPs: 546 (139776 megabytes) VG DESCRIPTORS: 1FREE PPs: 235 (60160 megabytes) HOT SPARE: noUSED PPs: 311 (79616 megabytes) FREE DISTRIBUTION: 04..00..13..109..109 USED DISTRIBUTION: 106..109..96..00..00 bash-2.05b$ mklv -y 'redo1_83_n' -t 'raw' datavg 1 hdisk3redo1_83_nbash-2.05b$ lslv redo1_83_nLOGICAL VOLUME: redo1_83_n VOLUME : datavgLV IDENTIFIER: 0052db8d00004c00000000fe2103f6b6.68 PERMISSION: read/writeVG STATE: active/complete LV STATE: closed/syncdTYPE: raw WRITE VERIFY: offMAX LPs: 512 PP SIZE: 256 megabyte(s)COPIES: 1 SCHED POLICY: parallelLPs: 1 PPs: 1STALE PPs: 0 BB POLICY: relocatableINTER-POLICY: minimum RELOCATABLE: yesINTRA-POLICY: middle UPPER BOUND: 32MOUNT POINT: N/A LABEL: NoneMIRROR WRITE CONSISTENCY: on/ACTIVE EACH LP COPY ON A SEPARATE PV ?: yes bash-2.05b$ dd if=/dev/rredo1_82_n of=/dev/rredo1_83_n bs=1024k256+0 records in.256+0 records out.SQL> startup mountORACLE instance started.Total System Global Area 1779928496 bytesFixed Size 744880 bytesVariable Size 1107296256 bytesDatabase Buffers 671088640 bytesRedo Buffers 798720 bytesDatabase mounted.SQL> alter database rename file '/dev/rredo1_82_n' to '/dev/rredo1_83_n' ;Database altered.SQL> alter database open;Database altered.SQL> col member format a50SQL> set pagesize 30SQL> select * from v$logfile; # STATUS TYPE MEMBER---------- ------- ------- -------------------------------------------------- 1 ONLINE /dev/rredo1_11_n 1 ONLINE /dev/rredo1_12_n 2 ONLINE /dev/rredo1_21_n 2 ONLINE /dev/rredo1_22_n 5 ONLINE /dev/rredo1_51_n 5 ONLINE /dev/rredo1_52_n 6 ONLINE /dev/rredo1_61_n 6 ONLINE /dev/rredo1_62_n 7 ONLINE /dev/rredo1_71_n 7 ONLINE /dev/rredo1_72_n 8 ONLINE /dev/rredo1_81_n 8 ONLINE /dev/rredo1_83_n 3 ONLINE /dev/rredo1_31_n 3 ONLINE /dev/rredo1_32_n 4 ONLINE /dev/rredo1_41_n 4 ONLINE /dev/rredo1_42_n16 rows selected.SQL> select #,THREAD#,SEQUENCE#,MEMBERS,BYTES,ARCHIVED,STATUS from v$log where THREAD#=2; # THREAD# SEQUENCE# MEMBERS BYTES ARC STATUS---------- ---------- ---------- ---------- ---------- --- ---------------- 5 2 186 2 251658240 NO CURRENT 6 2 183 2 251658240 YES INACTIVE 7 2 185 2 251658240 YES INACTIVE 8 2 184 2 251658240 YES INACTIVESQL> alter system switch logfile;System altered.SQL> /System altered.SQL> /System altered.SQL> /System altered.SQL> /System altered.SQL> select #,THREAD#,SEQUENCE#,MEMBERS,BYTES,ARCHIVED,STATUS from v$log where THREAD#=2; # THREAD# SEQUENCE# MEMBERS BYTES ARC STATUS---------- ---------- ---------- ---------- ---------- --- ---------------- 5 2 190 2 251658240 YES INACTIVE 6 2 191 2 251658240 NO CURRENT 7 2 189 2 251658240 YES INACTIVE 8 2 188 2 251658240 YES INACTIVE
