=======
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 2009
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the OLAP option
JServer Release 9.2.0.4.0 - Production
SQL> select thread#,status,instance from v$thread;
THREAD# STATUS INSTANCE
---------- ------ ----------------
1 CLOSED xx1
2 OPEN xx2
SQL> col member format a50
SQL> set pagesize 30
SQL> 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;
/
打开另一个session2
1 添加一个logfile
2 切换一次
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_n
INVALID - File is inaccessible
STALE - File's contents are incomplete
DELETED - File is no longer used
null - 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 INACTIVE
8 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 INACTIVE
7 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 INACTIVE
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_n
16 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 INACTIVE
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_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_n
15 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 INACTIVE
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 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_n
16 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_n
16 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 immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
bash-2.05b$ lspv -L
hdisk0 0052db8d1d6c6d1a rootvg
hdisk1 0052db8d20ff9621 rootvg
hdisk2 0052db8d2103e69d datavg
hdisk3 0052db8d2106f554 datavg
bash-2.05b$ lspv hdisk2
PHYSICAL VOLUME: hdisk2 VOLUME : datavg
PV IDENTIFIER: 0052db8d2103e69d VG IDENTIFIER 0052db8d00004c00000000fe2103f6b6
PV STATE: active
STALE PARTITIONS: 0 ALLOCATABLE: yes
PP SIZE: 256 megabyte(s) LOGICAL VOLUMES: 29
TOTAL PPs: 546 (139776 megabytes) VG DESCRIPTORS: 2
FREE PPs: 58 (14848 megabytes) HOT SPARE: no
USED PPs: 488 (124928 megabytes)
FREE DISTRIBUTION: 00..00..00..00..58
USED DISTRIBUTION: 110..109..109..109..51
bash-2.05b$ lspv hdisk3
PHYSICAL VOLUME: hdisk3 VOLUME : datavg
PV IDENTIFIER: 0052db8d2106f554 VG IDENTIFIER 0052db8d00004c00000000fe2103f6b6
PV STATE: active
STALE PARTITIONS: 0 ALLOCATABLE: yes
PP SIZE: 256 megabyte(s) LOGICAL VOLUMES: 40
TOTAL PPs: 546 (139776 megabytes) VG DESCRIPTORS: 1
FREE PPs: 235 (60160 megabytes) HOT SPARE: no
USED 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 hdisk3
redo1_83_n
bash-2.05b$ lslv redo1_83_n
LOGICAL VOLUME: redo1_83_n VOLUME : datavg
LV IDENTIFIER: 0052db8d00004c00000000fe2103f6b6.68 PERMISSION: read/write
VG STATE: active/complete LV STATE: closed/syncd
TYPE: raw WRITE VERIFY: off
MAX LPs: 512 PP SIZE: 256 megabyte(s)
COPIES: 1 SCHED POLICY: parallel
LPs: 1 PPs: 1
STALE PPs: 0 BB POLICY: relocatable
INTER-POLICY: minimum RELOCATABLE: yes
INTRA-POLICY: middle UPPER BOUND: 32
MOUNT POINT: N/A LABEL: None
MIRROR 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=1024k
256+0 records in.
256+0 records out.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1779928496 bytes
Fixed Size 744880 bytes
Variable Size 1107296256 bytes
Database Buffers 671088640 bytes
Redo Buffers 798720 bytes
Database 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 a50
SQL> set pagesize 30
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_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_n
16 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> 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
在线日志文件学习
最新推荐文章于 2022-11-22 15:09:08 发布