April 14, 2006
作者:eygle
在非归档模式下(归档模式会导致日志损坏),使用_disable_logging可以禁止日志的生成,从而可以提高某些特殊操作(如批量加载或Benchmark测试等)的性能.
我们可以来看一下这个禁止是如何完成的.
启动2个Session,一个用于管理,一个用于测试.
1.Session 1 ,使用SYS用户连接数据库,当前_disable_logging未设置,数据库处于正常状态
|
$ sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.4.0 - Production on Fri Apr 14 09:30:42 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production
SQL> col member for a40
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER
---------- ------- ------- ----------------------------------------
1 ONLINE /opt/oracle9/oradata/testora9/redo01.log
2 ONLINE /opt/oracle9/oradata/testora9/redo02.log
3 ONLINE /opt/oracle9/oradata/testora9/redo03.log
SQL> alter system switch logfile;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 148 104857600 1 NO CURRENT 262708457 14-APR-06
2 1 146 104857600 1 NO INACTIVE 262662667 14-APR-06
3 1 147 104857600 1 NO ACTIVE 262675537 14-APR-06
|
2.Session2通过SCOTT用户连接,执行如下更新:
|
SQL> connect scott/tiger
Connected.
SQL> update emp set sal=3000 where empno=7788;
1 row updated.
SQL> commit;
Commit complete.
|
3.在Session转储REDO日志
|
SQL> ALTER SYSTEM DUMP LOGFILE '/opt/oracle9/oradata/testora9/redo01.log';
System altered.
|
4.观察跟踪文件,记录正常状态下的REDO信息
|
$ ls
testora9_ora_16037.trc
$ cat *
/opt/oracle9/admin/testora9/udump/testora9_ora_16037.trc
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production
ORACLE_HOME = /opt/oracle9/product/9.2.0
System name: SunOS
Node name: testdbserver.hurray.com.cn
Release: 5.8
Version: Generic_108528-25
Machine: sun4u
Instance name: testora9
Redo thread mounted by this instance: 1
Oracle process number: 15
Unix process pid: 16037, image: oracle@testdbserver.hurray.com.cn (TNS V1-V3)
*** 2006-04-14 09:32:01.356
*** SESSION ID:(17.3524) 2006-04-14 09:32:01.340
DUMP OF REDO FROM FILE '/opt/oracle9/oradata/testora9/redo01.log'
Opcodes *.*
DBA's: (file # 0, block # 0) thru (file # 65534, block # 4194303)
RBA's: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff
SCN's scn: 0x0000.00000000 thru scn: 0xffff.ffffffff
Times: creation thru eternity
FILE HEADER:
Software vsn=153092096=0x9200000, Compatibility Vsn=153092096=0x9200000
Db ID=1628068176=0x610a5950, Db Name='TESTORA9'
Activation ID=1628067152=0x610a5550
Control Seq=1161=0x489, File size=204800=0x32000
File Number=1, Blksiz=512, File Type=2 LOG
descrip:"Thread 0001, Seq# 0000000148, SCN 0x00000fa89ce9-0xffffffffffff"
thread: 1 nab: 0xffffffff seq: 0x00000094 hws: 0x1 eot: 1 dis: 0
reset logs count: 0x1f435110 scn: 0x0000.00000001
Low scn: 0x0000.0fa89ce9 04/14/2006 09:31:06
Next scn: 0xffff.ffffffff 01/01/1988 00:00:00
Enabled scn: 0x0000.00000001 04/26/2004 15:56:10
Thread closed scn: 0x0000.0fa89ce9 04/14/2006 09:31:06
Log format vsn: 0x8000000 Disk cksum: 0x9930 Calc cksum: 0x9930
Terminal Recovery Stamp scn: 0x0000.00000000 01/01/1988 00:00:00
Most recent redo scn: 0x0000.00000000
Largest LWN: 0 blocks
End-of-redo stream : No
Unprotected mode
Miscellaneous flags: 0x0
REDO RECORD - Thread:1 RBA: 0x000094.00000002.0010 LEN: 0x01b0 VLD: 0x01
SCN: 0x0000.0fa89cf0 SUBSCN: 1 04/14/2006 09:31:30
CHANGE #1 TYP:0 CLS:33 AFN:2 DBA:0x00800089 SCN:0x0000.0fa89cca SEQ: 1 OP:5.2
ktudh redo: slt: 0x000f sqn: 0x00009c89 flg: 0x000a siz: 132 fbi: 0
uba: 0x00800559.0352.01 pxid: 0x0000.000.00000000
CHANGE #2 TYP:1 CLS:34 AFN:2 DBA:0x00800559 SCN:0x0000.0fa89cf0 SEQ: 1 OP:5.1
ktudb redo: siz: 132 spc: 0 flg: 0x000a seq: 0x0352 rec: 0x01
xid: 0x0009.00f.00009c89
ktubl redo: slt: 15 rci: 0 opc: 11.1 objn: 30359 objd: 30359 tsn: 0
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
0x00000000 prev ctl uba: 0x00800558.0352.57
prev ctl max cmt scn: 0x0000.0fa89887 prev tx cmt scn: 0x0000.0fa8988b
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
op: L itl: xid: 0x0005.01e.00009810 uba: 0x00800615.032f.26
flg: C--- lkc: 0 scn: 0x0000.0f92af59
KDO Op code: URP row dependencies Disabled
xtype: XA bdba: 0x0040a482 hdba: 0x0040a481
itli: 1 ispac: 0 maxfr: 4863
tabn: 0 slot: 7(0x7) flag: 0x2c lock: 0 ckix: 0
ncol: 8 nnew: 1 size: 0
col 5: [ 2] c2 29
CHANGE #3 TYP:2 CLS: 1 AFN:1 DBA:0x0040a482 SCN:0x0000.0f92ced1 SEQ: 1 OP:11.5
KTB Redo
op: 0x11 ver: 0x01
op: F xid: 0x0009.00f.00009c89 uba: 0x00800559.0352.01
Block cleanout record, scn: 0x0000.0fa89cf0 ver: 0x01 opt: 0x02, entries follow...
itli: 2 flg: 2 scn: 0x0000.0f92ced1
KDO Op code: URP row dependencies Disabled
xtype: XA bdba: 0x0040a482 hdba: 0x0040a481
itli: 1 ispac: 0 maxfr: 4863
tabn: 0 slot: 7(0x7) flag: 0x2c lock: 1 ckix: 0
ncol: 8 nnew: 1 size: 0
col 5: [ 2] c2 1f
CHANGE #4 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ: 0 OP:5.20
session number = 16
serial number = 651
transaction name =
REDO RECORD - Thread:1 RBA: 0x000094.00000002.01c0 LEN: 0x0054 VLD: 0x01
SCN: 0x0000.0fa89cf1 SUBSCN: 1 04/14/2006 09:31:30
CHANGE #1 TYP:0 CLS:33 AFN:2 DBA:0x00800089 SCN:0x0000.0fa89cf0 SEQ: 1 OP:5.4
ktucm redo: slt: 0x000f sqn: 0x00009c89 srt: 0 sta: 9 flg: 0x2
ktucf redo: uba: 0x00800559.0352.01 ext: 2 spc: 8016 fbi: 0
REDO RECORD - Thread:1 RBA: 0x000094.00000004.0010 LEN: 0x01f4 VLD: 0x01
SCN: 0x0000.0fa89cf2 SUBSCN: 1 04/14/2006 09:31:31
CHANGE #1 TYP:0 CLS:21 AFN:2 DBA:0x00800029 SCN:0x0000.0fa89ccc SEQ: 1 OP:5.2
ktudh redo: slt: 0x001b sqn: 0x00009c9b flg: 0x0012 siz: 108 fbi: 0
uba: 0x0080046b.0345.40 pxid: 0x0000.000.00000000
CHANGE #2 TYP:0 CLS:22 AFN:2 DBA:0x0080046b SCN:0x0000.0fa89ccb SEQ: 1 OP:5.1
ktudb redo: siz: 108 spc: 2908 flg: 0x0012 seq: 0x0345 rec: 0x40
xid: 0x0003.01b.00009c9b
ktubl redo: slt: 27 rci: 0 opc: 11.1 objn: 3643 objd: 3643 tsn: 0
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
0x00000000 prev ctl uba: 0x0080046b.0345.3f
prev ctl max cmt scn: 0x0000.0fa89888 prev tx cmt scn: 0x0000.0fa8988d
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
op: L itl: xid: 0x0002.021.0000a088 uba: 0x0080091e.0714.47
flg: C--- lkc: 0 scn: 0x0000.0fa89ce0
KDO Op code: LKR row dependencies Disabled
xtype: XA bdba: 0x00401f7a hdba: 0x00401f79
itli: 1 ispac: 0 maxfr: 4863
tabn: 0 slot: 0 to: 0
CHANGE #3 TYP:2 CLS: 1 AFN:1 DBA:0x00401f7a SCN:0x0000.0fa89ce3 SEQ: 1 OP:11.4
KTB Redo
op: 0x11 ver: 0x01
op: F xid: 0x0003.01b.00009c9b uba: 0x0080046b.0345.40
Block cleanout record, scn: 0x0000.0fa89cf2 ver: 0x01 opt: 0x02, entries follow...
itli: 1 flg: 2 scn: 0x0000.0fa89ce0
itli: 2 flg: 2 scn: 0x0000.0fa89ce3
KDO Op code: LKR row dependencies Disabled
xtype: XA bdba: 0x00401f7a hdba: 0x00401f79
itli: 1 ispac: 0 maxfr: 4863
tabn: 0 slot: 0 to: 1
CHANGE #4 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ: 0 OP:5.19
session number = 8
serial number = 1
current username = SYS
login username =
client info =
OS username = oracle9
Machine name = testdbserver.hurray.com.cn
OS terminal = UNKNOWN
OS process id = 2053
OS program name = oracle@testdbserver.hurray.com.cn (QMN0)
transaction name =
REDO RECORD - Thread:1 RBA: 0x000094.00000005.0014 LEN: 0x00dc VLD: 0x01
SCN: 0x0000.0fa89cf2 SUBSCN: 1 04/14/2006 09:31:31
CHANGE #1 TYP:0 CLS:22 AFN:2 DBA:0x0080046b SCN:0x0000.0fa89cf2 SEQ: 1 OP:5.1
ktudb redo: siz: 84 spc: 2798 flg: 0x0022 seq: 0x0345 rec: 0x41
xid: 0x0003.01b.00009c9b
ktubu redo: slt: 27 rci: 64 opc: 11.1 objn: 3635 objd: 3635 tsn: 0
Undo type: Regular undo Undo type: Last buffer split: No
Tablespace Undo: No
0x00000000
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
op: L itl: xid: 0x0008.01b.00009c85 uba: 0x008007a3.037e.0b
flg: C--- lkc: 0 scn: 0x0000.0fa89ce3
KDO Op code: LKR row dependencies Disabled
xtype: XA bdba: 0x00401f3a hdba: 0x00401f39
itli: 1 ispac: 0 maxfr: 4863
tabn: 0 slot: 0 to: 0
CHANGE #2 TYP:2 CLS: 1 AFN:1 DBA:0x00401f3a SCN:0x0000.0fa89ce5 SEQ: 1 OP:11.4
KTB Redo
op: 0x01 ver: 0x01
op: F xid: 0x0003.01b.00009c9b uba: 0x0080046b.0345.41
KDO Op code: LKR row dependencies Disabled
xtype: XA bdba: 0x00401f3a hdba: 0x00401f39
itli: 1 ispac: 0 maxfr: 4863
tabn: 0 slot: 0 to: 1
REDO RECORD - Thread:1 RBA: 0x000094.00000005.00f0 LEN: 0x0054 VLD: 0x01
SCN: 0x0000.0fa89cf3 SUBSCN: 1 04/14/2006 09:31:31
CHANGE #1 TYP:0 CLS:21 AFN:2 DBA:0x00800029 SCN:0x0000.0fa89cf2 SEQ: 1 OP:5.4
ktucm redo: slt: 0x001b sqn: 0x00009c9b srt: 0 sta: 9 flg: 0x2
ktucf redo: uba: 0x0080046b.0345.41 ext: 2 spc: 2712 fbi: 0
REDO RECORD - Thread:1 RBA: 0x000094.00000006.0010 LEN: 0x0188 VLD: 0x01
SCN: 0x0000.0fa89cf5 SUBSCN: 1 04/14/2006 09:31:31
CHANGE #1 TYP:0 CLS:27 AFN:2 DBA:0x00800059 SCN:0x0000.0fa89ccd SEQ: 1 OP:5.2
ktudh redo: slt: 0x0020 sqn: 0x00009c72 flg: 0x0012 siz: 108 fbi: 0
uba: 0x0080027b.0352.1d pxid: 0x0000.000.00000000
CHANGE #2 TYP:0 CLS:28 AFN:2 DBA:0x0080027b SCN:0x0000.0fa89ccc SEQ: 1 OP:5.1
ktudb redo: siz: 108 spc: 5718 flg: 0x0012 seq: 0x0352 rec: 0x1d
xid: 0x0006.020.00009c72
ktubl redo: slt: 32 rci: 0 opc: 11.1 objn: 3635 objd: 3635 tsn: 0
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
0x00000000 prev ctl uba: 0x0080027b.0352.1c
prev ctl max cmt scn: 0x0000.0fa8988a prev tx cmt scn: 0x0000.0fa8988e
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
op: L itl: xid: 0x0002.01a.0000a089 uba: 0x0080091e.0714.49
flg: C--- lkc: 0 scn: 0x0000.0fa89ce5
KDO Op code: LKR row dependencies Disabled
xtype: XA bdba: 0x00401f3a hdba: 0x00401f39
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 0 to: 0
CHANGE #3 TYP:2 CLS: 1 AFN:1 DBA:0x00401f3a SCN:0x0000.0fa89cf3 SEQ: 1 OP:11.4
KTB Redo
op: 0x11 ver: 0x01
op: F xid: 0x0006.020.00009c72 uba: 0x0080027b.0352.1d
Block cleanout record, scn: 0x0000.0fa89cf5 ver: 0x01 opt: 0x02, entries follow...
itli: 1 flg: 2 scn: 0x0000.0fa89cf3
itli: 2 flg: 2 scn: 0x0000.0fa89ce5
KDO Op code: LKR row dependencies Disabled
xtype: XA bdba: 0x00401f3a hdba: 0x00401f39
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 0 to: 2
CHANGE #4 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ: 0 OP:5.20
session number = 8
serial number = 1
transaction name =
REDO RECORD - Thread:1 RBA: 0x000094.00000006.0198 LEN: 0x0054 VLD: 0x01
SCN: 0x0000.0fa89cf6 SUBSCN: 1 04/14/2006 09:31:31
CHANGE #1 TYP:0 CLS:27 AFN:2 DBA:0x00800059 SCN:0x0000.0fa89cf5 SEQ: 1 OP:5.4
ktucm redo: slt: 0x0020 sqn: 0x00009c72 srt: 0 sta: 9 flg: 0x2
ktucf redo: uba: 0x0080027b.0352.1d ext: 2 spc: 5608 fbi: 0
REDO RECORD - Thread:1 RBA: 0x000094.00000007.0010 LEN: 0x00d4 VLD: 0x01
SCN: 0x0000.0fa89cf7 SUBSCN: 1 04/14/2006 09:31:31
CHANGE #1 TYP:0 CLS:33 AFN:2 DBA:0x00800089 SCN:0x0000.0fa89cf1 SEQ: 1 OP:5.2
ktudh redo: slt: 0x0027 sqn: 0x00009c88 flg: 0x0011 siz: 52 fbi: 0
uba: 0x00800559.0352.02 pxid: 0x0000.000.00000000
CHANGE #2 TYP:0 CLS:34 AFN:2 DBA:0x00800559 SCN:0x0000.0fa89cf0 SEQ: 2 OP:5.1
ktudb redo: siz: 52 spc: 8016 flg: 0x0012 seq: 0x0352 rec: 0x02
xid: 0x0009.027.00009c88
ktubl redo: slt: 39 rci: 0 opc: 5.7 objn: 0 objd: 0 tsn: 0
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
0x00000000 prev ctl uba: 0x00800559.0352.01
prev ctl max cmt scn: 0x0000.0fa8988b prev tx cmt scn: 0x0000.0fa89894
CHANGE #3 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ: 0 OP:5.20
session number = 8
serial number = 1
transaction name =
REDO RECORD - Thread:1 RBA: 0x000094.00000007.00e4 LEN: 0x0118 VLD: 0x01
SCN: 0x0000.0fa89cf8 SUBSCN: 1 04/14/2006 09:31:31
CHANGE #1 TYP:0 CLS:33 AFN:2 DBA:0x00800089 SCN:0x0000.0fa89cf7 SEQ: 1 OP:5.2
ktudh redo: slt: 0x0027 sqn: 0x00000000 flg: 0x0002 siz: 84 fbi: 0
uba: 0x00800559.0352.03 pxid: 0x0000.000.00000000
CHANGE #2 TYP:0 CLS:34 AFN:2 DBA:0x00800559 SCN:0x0000.0fa89cf7 SEQ: 1 OP:5.1
ktudb redo: siz: 84 spc: 7962 flg: 0x0022 seq: 0x0352 rec: 0x03
xid: 0x0009.027.00009c88
ktubu redo: slt: 39 rci: 0 opc: 11.1 objn: 3643 objd: 3643 tsn: 0
Undo type: Regular undo Undo type: Last buffer split: No
Tablespace Undo: No
0x00000000
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
op: L itl: xid: 0x0008.01b.00009c85 uba: 0x008007a3.037e.0a
flg: C--- lkc: 0 scn: 0x0000.0fa89ce3
KDO Op code: LKR row dependencies Disabled
xtype: XA bdba: 0x00401f7a hdba: 0x00401f79
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 1 to: 0
CHANGE #3 TYP:2 CLS: 1 AFN:1 DBA:0x00401f7a SCN:0x0000.0fa89cf3 SEQ: 1 OP:11.4
KTB Redo
op: 0x01 ver: 0x01
op: F xid: 0x0009.027.00009c88 uba: 0x00800559.0352.03
KDO Op code: LKR row dependencies Disabled
xtype: XA bdba: 0x00401f7a hdba: 0x00401f79
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 1 to: 2
REDO RECORD - Thread:1 RBA: 0x000094.00000008.0010 LEN: 0x00dc VLD: 0x01
SCN: 0x0000.0fa89cf8 SUBSCN: 1 04/14/2006 09:31:31
CHANGE #1 TYP:0 CLS:34 AFN:2 DBA:0x00800559 SCN:0x0000.0fa89cf8 SEQ: 1 OP:5.1
ktudb redo: siz: 84 spc: 7876 flg: 0x0022 seq: 0x0352 rec: 0x04
xid: 0x0009.027.00009c88
ktubu redo: slt: 39 rci: 3 opc: 11.1 objn: 3635 objd: 3635 tsn: 0
Undo type: Regular undo Undo type: Last buffer split: No
Tablespace Undo: No
0x00000000
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
op: L itl: xid: 0x0003.01b.00009c9b uba: 0x0080046b.0345.41
flg: C--- lkc: 0 scn: 0x0000.0fa89cf3
KDO Op code: LKR row dependencies Disabled
xtype: XA bdba: 0x00401f3a hdba: 0x00401f39
itli: 1 ispac: 0 maxfr: 4863
tabn: 0 slot: 1 to: 0
CHANGE #2 TYP:2 CLS: 1 AFN:1 DBA:0x00401f3a SCN:0x0000.0fa89cf6 SEQ: 1 OP:11.4
KTB Redo
op: 0x01 ver: 0x01
op: F xid: 0x0009.027.00009c88 uba: 0x00800559.0352.04
KDO Op code: LKR row dependencies Disabled
xtype: XA bdba: 0x00401f3a hdba: 0x00401f39
itli: 1 ispac: 0 maxfr: 4863
tabn: 0 slot: 1 to: 1
REDO RECORD - Thread:1 RBA: 0x000094.00000008.00ec LEN: 0x0054 VLD: 0x01
SCN: 0x0000.0fa89cf9 SUBSCN: 1 04/14/2006 09:31:31
CHANGE #1 TYP:0 CLS:33 AFN:2 DBA:0x00800089 SCN:0x0000.0fa89cf8 SEQ: 1 OP:5.4
ktucm redo: slt: 0x0027 sqn: 0x00009c88 srt: 0 sta: 9 flg: 0x2
ktucf redo: uba: 0x00800559.0352.04 ext: 2 spc: 7790 fbi: 0
REDO RECORD - Thread:1 RBA: 0x000094.00000008.0140 LEN: 0x0188 VLD: 0x01
SCN: 0x0000.0fa89cfa SUBSCN: 1 04/14/2006 09:31:31
CHANGE #1 TYP:0 CLS:21 AFN:2 DBA:0x00800029 SCN:0x0000.0fa89cf3 SEQ: 1 OP:5.2
ktudh redo: slt: 0x001a sqn: 0x00009c9b flg: 0x0012 siz: 108 fbi: 0
uba: 0x0080046b.0345.42 pxid: 0x0000.000.00000000
CHANGE #2 TYP:0 CLS:22 AFN:2 DBA:0x0080046b SCN:0x0000.0fa89cf2 SEQ: 2 OP:5.1
ktudb redo: siz: 108 spc: 2712 flg: 0x0012 seq: 0x0345 rec: 0x42
xid: 0x0003.01a.00009c9b
ktubl redo: slt: 26 rci: 0 opc: 11.1 objn: 3635 objd: 3635 tsn: 0
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
0x00000000 prev ctl uba: 0x0080046b.0345.40
prev ctl max cmt scn: 0x0000.0fa8988d prev tx cmt scn: 0x0000.0fa898e6
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
op: L itl: xid: 0x0006.020.00009c72 uba: 0x0080027b.0352.1d
flg: C--- lkc: 0 scn: 0x0000.0fa89cf6
KDO Op code: LKR row dependencies Disabled
xtype: XA bdba: 0x00401f3a hdba: 0x00401f39
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 1 to: 0
CHANGE #3 TYP:2 CLS: 1 AFN:1 DBA:0x00401f3a SCN:0x0000.0fa89cf9 SEQ: 1 OP:11.4
KTB Redo
op: 0x11 ver: 0x01
op: F xid: 0x0003.01a.00009c9b uba: 0x0080046b.0345.42
Block cleanout record, scn: 0x0000.0fa89cfa ver: 0x01 opt: 0x02, entries follow...
itli: 1 flg: 2 scn: 0x0000.0fa89cf9
itli: 2 flg: 2 scn: 0x0000.0fa89cf6
KDO Op code: LKR row dependencies Disabled
xtype: XA bdba: 0x00401f3a hdba: 0x00401f39
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 1 to: 2
CHANGE #4 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ: 0 OP:5.20
session number = 8
serial number = 1
transaction name =
REDO RECORD - Thread:1 RBA: 0x000094.00000009.00d8 LEN: 0x0054 VLD: 0x01
SCN: 0x0000.0fa89cfb SUBSCN: 1 04/14/2006 09:31:31
CHANGE #1 TYP:0 CLS:21 AFN:2 DBA:0x00800029 SCN:0x0000.0fa89cfa SEQ: 1 OP:5.4
ktucm redo: slt: 0x001a sqn: 0x00009c9b srt: 0 sta: 9 flg: 0x2
ktucf redo: uba: 0x0080046b.0345.42 ext: 2 spc: 2602 fbi: 0
REDO RECORD - Thread:1 RBA: 0x000094.00000009.012c LEN: 0x00d4 VLD: 0x01
SCN: 0x0000.0fa89cfb SUBSCN: 1 04/14/2006 09:31:31
CHANGE #1 TYP:0 CLS:27 AFN:2 DBA:0x00800059 SCN:0x0000.0fa89cf6 SEQ: 1 OP:5.2
ktudh redo: slt: 0x000c sqn: 0x00009c72 flg: 0x0011 siz: 52 fbi: 0
uba: 0x0080027b.0352.1e pxid: 0x0000.000.00000000
CHANGE #2 TYP:0 CLS:28 AFN:2 DBA:0x0080027b SCN:0x0000.0fa89cf5 SEQ: 1 OP:5.1
ktudb redo: siz: 52 spc: 5608 flg: 0x0012 seq: 0x0352 rec: 0x1e
xid: 0x0006.00c.00009c72
ktubl redo: slt: 12 rci: 0 opc: 5.7 objn: 0 objd: 0 tsn: 0
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
0x00000000 prev ctl uba: 0x0080027b.0352.1d
prev ctl max cmt scn: 0x0000.0fa8988e prev tx cmt scn: 0x0000.0fa89902
CHANGE #3 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ: 0 OP:5.20
session number = 8
serial number = 1
transaction name =
REDO RECORD - Thread:1 RBA: 0x000094.0000000a.0010 LEN: 0x0148 VLD: 0x01
SCN: 0x0000.0fa89cfb SUBSCN: 1 04/14/2006 09:31:31
CHANGE #1 TYP:0 CLS:27 AFN:2 DBA:0x00800059 SCN:0x0000.0fa89cfb SEQ: 1 OP:5.2
ktudh redo: slt: 0x000c sqn: 0x00000000 flg: 0x0002 siz: 84 fbi: 0
uba: 0x0080027b.0352.1f pxid: 0x0000.000.00000000
CHANGE #2 TYP:0 CLS:28 AFN:2 DBA:0x0080027b SCN:0x0000.0fa89cfb SEQ: 1 OP:5.1
ktudb redo: siz: 84 spc: 5554 flg: 0x0022 seq: 0x0352 rec: 0x1f
xid: 0x0006.00c.00009c72
ktubu redo: slt: 12 rci: 0 opc: 11.1 objn: 3643 objd: 3643 tsn: 0
Undo type: Regular undo Undo type: Last buffer split: No
Tablespace Undo: No
0x00000000
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
op: L itl: xid: 0x0003.01b.00009c9b uba: 0x0080046b.0345.40
flg: C--- lkc: 0 scn: 0x0000.0fa89cf3
KDO Op code: LKR row dependencies Disabled
xtype: XA bdba: 0x00401f7a hdba: 0x00401f79
itli: 1 ispac: 0 maxfr: 4863
tabn: 0 slot: 2 to: 0
CHANGE #3 TYP:2 CLS: 1 AFN:1 DBA:0x00401f7a SCN:0x0000.0fa89cf9 SEQ: 1 OP:11.4
KTB Redo
op: 0x11 ver: 0x01
op: F xid: 0x0006.00c.00009c72 uba: 0x0080027b.0352.1f
Block cleanout record, scn: 0x0000.0fa89cfb ver: 0x01 opt: 0x02, entries follow...
itli: 1 flg: 2 scn: 0x0000.0fa89cf3
itli: 2 flg: 2 scn: 0x0000.0fa89cf9
KDO Op code: LKR row dependencies Disabled
xtype: XA bdba: 0x00401f7a hdba: 0x00401f79
itli: 1 ispac: 0 maxfr: 4863
tabn: 0 slot: 2 to: 1
REDO RECORD - Thread:1 RBA: 0x000094.0000000a.0158 LEN: 0x00dc VLD: 0x01
SCN: 0x0000.0fa89cfb SUBSCN: 1 04/14/2006 09:31:31
CHANGE #1 TYP:0 CLS:28 AFN:2 DBA:0x0080027b SCN:0x0000.0fa89cfb SEQ: 2 OP:5.1
ktudb redo: siz: 84 spc: 5468 flg: 0x0022 seq: 0x0352 rec: 0x20
xid: 0x0006.00c.00009c72
ktubu redo: slt: 12 rci: 31 opc: 11.1 objn: 3635 objd: 3635 tsn: 0
Undo type: Regular undo Undo type: Last buffer split: No
Tablespace Undo: No
0x00000000
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
op: L itl: xid: 0x0009.027.00009c88 uba: 0x00800559.0352.04
flg: C--- lkc: 0 scn: 0x0000.0fa89cf9
KDO Op code: LKR row dependencies Disabled
xtype: XA bdba: 0x00401f3a hdba: 0x00401f39
itli: 1 ispac: 0 maxfr: 4863
tabn: 0 slot: 2 to: 0
CHANGE #2 TYP:2 CLS: 1 AFN:1 DBA:0x00401f3a SCN:0x0000.0fa89cfb SEQ: 1 OP:11.4
KTB Redo
op: 0x01 ver: 0x01
op: F xid: 0x0006.00c.00009c72 uba: 0x0080027b.0352.20
KDO Op code: LKR row dependencies Disabled
xtype: XA bdba: 0x00401f3a hdba: 0x00401f39
itli: 1 ispac: 0 maxfr: 4863
tabn: 0 slot: 2 to: 1
REDO RECORD - Thread:1 RBA: 0x000094.0000000b.0044 LEN: 0x0054 VLD: 0x01
SCN: 0x0000.0fa89cfc SUBSCN: 1 04/14/2006 09:31:31
CHANGE #1 TYP:0 CLS:27 AFN:2 DBA:0x00800059 SCN:0x0000.0fa89cfb SEQ: 2 OP:5.4
ktucm redo: slt: 0x000c sqn: 0x00009c72 srt: 0 sta: 9 flg: 0x2
ktucf redo: uba: 0x0080027b.0352.20 ext: 2 spc: 5382 fbi: 0
REDO RECORD - Thread:1 RBA: 0x000094.0000000b.0098 LEN: 0x0188 VLD: 0x01
SCN: 0x0000.0fa89cfd SUBSCN: 1 04/14/2006 09:31:31
CHANGE #1 TYP:0 CLS:33 AFN:2 DBA:0x00800089 SCN:0x0000.0fa89cf9 SEQ: 1 OP:5.2
ktudh redo: slt: 0x000d sqn: 0x00009c89 flg: 0x0012 siz: 108 fbi: 0
uba: 0x00800559.0352.05 pxid: 0x0000.000.00000000
CHANGE #2 TYP:0 CLS:34 AFN:2 DBA:0x00800559 SCN:0x0000.0fa89cf8 SEQ: 2 OP:5.1
ktudb redo: siz: 108 spc: 7790 flg: 0x0012 seq: 0x0352 rec: 0x05
xid: 0x0009.00d.00009c89
ktubl redo: slt: 13 rci: 0 opc: 11.1 objn: 3635 objd: 3635 tsn: 0
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
0x00000000 prev ctl uba: 0x00800559.0352.02
prev ctl max cmt scn: 0x0000.0fa89894 prev tx cmt scn: 0x0000.0fa89905
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
op: L itl: xid: 0x0003.01a.00009c9b uba: 0x0080046b.0345.42
flg: C--- lkc: 0 scn: 0x0000.0fa89cfb
KDO Op code: LKR row dependencies Disabled
xtype: XA bdba: 0x00401f3a hdba: 0x00401f39
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 2 to: 0
CHANGE #3 TYP:2 CLS: 1 AFN:1 DBA:0x00401f3a SCN:0x0000.0fa89cfc SEQ: 1 OP:11.4
KTB Redo
op: 0x11 ver: 0x01
op: F xid: 0x0009.00d.00009c89 uba: 0x00800559.0352.05
Block cleanout record, scn: 0x0000.0fa89cfd ver: 0x01 opt: 0x02, entries follow...
itli: 1 flg: 2 scn: 0x0000.0fa89cfc
itli: 2 flg: 2 scn: 0x0000.0fa89cfb
KDO Op code: LKR row dependencies Disabled
xtype: XA bdba: 0x00401f3a hdba: 0x00401f39
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 2 to: 2
CHANGE #4 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ: 0 OP:5.20
session number = 8
serial number = 1
transaction name =
REDO RECORD - Thread:1 RBA: 0x000094.0000000c.0030 LEN: 0x0054 VLD: 0x01
SCN: 0x0000.0fa89cfe SUBSCN: 1 04/14/2006 09:31:31
CHANGE #1 TYP:0 CLS:33 AFN:2 DBA:0x00800089 SCN:0x0000.0fa89cfd SEQ: 1 OP:5.4
ktucm redo: slt: 0x000d sqn: 0x00009c89 srt: 0 sta: 9 flg: 0x2
ktucf redo: uba: 0x00800559.0352.05 ext: 2 spc: 7680 fbi: 0
REDO RECORD - Thread:1 RBA: 0x000094.0000000d.0010 LEN: 0x00d4 VLD: 0x01
SCN: 0x0000.0fa89cff SUBSCN: 1 04/14/2006 09:31:37
CHANGE #1 TYP:0 CLS:21 AFN:2 DBA:0x00800029 SCN:0x0000.0fa89cfb SEQ: 1 OP:5.2
ktudh redo: slt: 0x0017 sqn: 0x00009c9c flg: 0x0011 siz: 52 fbi: 0
uba: 0x0080046b.0345.43 pxid: 0x0000.000.00000000
CHANGE #2 TYP:0 CLS:22 AFN:2 DBA:0x0080046b SCN:0x0000.0fa89cfa SEQ: 1 OP:5.1
ktudb redo: siz: 52 spc: 2602 flg: 0x0012 seq: 0x0345 rec: 0x43
xid: 0x0003.017.00009c9c
ktubl redo: slt: 23 rci: 0 opc: 5.7 objn: 0 objd: 0 tsn: 0
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
0x00000000 prev ctl uba: 0x0080046b.0345.42
prev ctl max cmt scn: 0x0000.0fa898e6 prev tx cmt scn: 0x0000.0fa89907
CHANGE #3 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ: 0 OP:5.20
session number = 8
serial number = 1
transaction name =
REDO RECORD - Thread:1 RBA: 0x000094.0000000d.00e4 LEN: 0x0118 VLD: 0x01
SCN: 0x0000.0fa89cff SUBSCN: 1 04/14/2006 09:31:37
CHANGE #1 TYP:0 CLS:21 AFN:2 DBA:0x00800029 SCN:0x0000.0fa89cff SEQ: 1 OP:5.2
ktudh redo: slt: 0x0017 sqn: 0x00000000 flg: 0x0002 siz: 84 fbi: 0
uba: 0x0080046b.0345.44 pxid: 0x0000.000.00000000
CHANGE #2 TYP:0 CLS:22 AFN:2 DBA:0x0080046b SCN:0x0000.0fa89cff SEQ: 1 OP:5.1
ktudb redo: siz: 84 spc: 2548 flg: 0x0022 seq: 0x0345 rec: 0x44
xid: 0x0003.017.00009c9c
ktubu redo: slt: 23 rci: 0 opc: 11.1 objn: 3643 objd: 3643 tsn: 0
Undo type: Regular undo Undo type: Last buffer split: No
Tablespace Undo: No
0x00000000
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
op: L itl: xid: 0x0009.027.00009c88 uba: 0x00800559.0352.03
flg: C--- lkc: 0 scn: 0x0000.0fa89cf9
KDO Op code: LKR row dependencies Disabled
xtype: XA bdba: 0x00401f7a hdba: 0x00401f79
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 3 to: 0
CHANGE #3 TYP:2 CLS: 1 AFN:1 DBA:0x00401f7a SCN:0x0000.0fa89cfc SEQ: 1 OP:11.4
KTB Redo
op: 0x01 ver: 0x01
op: F xid: 0x0003.017.00009c9c uba: 0x0080046b.0345.44
KDO Op code: LKR row dependencies Disabled
xtype: XA bdba: 0x00401f7a hdba: 0x00401f79
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 3 to: 2
REDO RECORD - Thread:1 RBA: 0x000094.0000000e.0010 LEN: 0x00dc VLD: 0x01
SCN: 0x0000.0fa89cff SUBSCN: 1 04/14/2006 09:31:37
CHANGE #1 TYP:0 CLS:22 AFN:2 DBA:0x0080046b SCN:0x0000.0fa89cff SEQ: 2 OP:5.1
ktudb redo: siz: 84 spc: 2462 flg: 0x0022 seq: 0x0345 rec: 0x45
xid: 0x0003.017.00009c9c
ktubu redo: slt: 23 rci: 68 opc: 11.1 objn: 3635 objd: 3635 tsn: 0
Undo type: Regular undo Undo type: Last buffer split: No
Tablespace Undo: No
0x00000000
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
op: L itl: xid: 0x0006.00c.00009c72 uba: 0x0080027b.0352.20
flg: C--- lkc: 0 scn: 0x0000.0fa89cfc
KDO Op code: LKR row dependencies Disabled
xtype: XA bdba: 0x00401f3a hdba: 0x00401f39
itli: 1 ispac: 0 maxfr: 4863
tabn: 0 slot: 3 to: 0
CHANGE #2 TYP:2 CLS: 1 AFN:1 DBA:0x00401f3a SCN:0x0000.0fa89cfe SEQ: 1 OP:11.4
KTB Redo
op: 0x01 ver: 0x01
op: F xid: 0x0003.017.00009c9c uba: 0x0080046b.0345.45
KDO Op code: LKR row dependencies Disabled
xtype: XA bdba: 0x00401f3a hdba: 0x00401f39
itli: 1 ispac: 0 maxfr: 4863
tabn: 0 slot: 3 to: 1
REDO RECORD - Thread:1 RBA: 0x000094.0000000e.00ec LEN: 0x0054 VLD: 0x01
SCN: 0x0000.0fa89d00 SUBSCN: 1 04/14/2006 09:31:37
CHANGE #1 TYP:0 CLS:21 AFN:2 DBA:0x00800029 SCN:0x0000.0fa89cff SEQ: 2 OP:5.4
ktucm redo: slt: 0x0017 sqn: 0x00009c9c srt: 0 sta: 9 flg: 0x2
ktucf redo: uba: 0x0080046b.0345.45 ext: 2 spc: 2376 fbi: 0
REDO RECORD - Thread:1 RBA: 0x000094.0000000e.0140 LEN: 0x0188 VLD: 0x01
SCN: 0x0000.0fa89d01 SUBSCN: 1 04/14/2006 09:31:37
CHANGE #1 TYP:0 CLS:27 AFN:2 DBA:0x00800059 SCN:0x0000.0fa89cfc SEQ: 1 OP:5.2
ktudh redo: slt: 0x001f sqn: 0x00009c72 flg: 0x0012 siz: 108 fbi: 0
uba: 0x0080027b.0352.21 pxid: 0x0000.000.00000000
CHANGE #2 TYP:0 CLS:28 AFN:2 DBA:0x0080027b SCN:0x0000.0fa89cfb SEQ: 3 OP:5.1
ktudb redo: siz: 108 spc: 5382 flg: 0x0012 seq: 0x0352 rec: 0x21
xid: 0x0006.01f.00009c72
ktubl redo: slt: 31 rci: 0 opc: 11.1 objn: 3635 objd: 3635 tsn: 0
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
0x00000000 prev ctl uba: 0x0080027b.0352.1e
prev ctl max cmt scn: 0x0000.0fa89902 prev tx cmt scn: 0x0000.0fa89908
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
op: L itl: xid: 0x0009.00d.00009c89 uba: 0x00800559.0352.05
flg: C--- lkc: 0 scn: 0x0000.0fa89cfe
KDO Op code: LKR row dependencies Disabled
xtype: XA bdba: 0x00401f3a hdba: 0x00401f39
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 3 to: 0
CHANGE #3 TYP:2 CLS: 1 AFN:1 DBA:0x00401f3a SCN:0x0000.0fa89d00 SEQ: 1 OP:11.4
KTB Redo
op: 0x11 ver: 0x01
op: F xid: 0x0006.01f.00009c72 uba: 0x0080027b.0352.21
Block cleanout record, scn: 0x0000.0fa89d01 ver: 0x01 opt: 0x02, entries follow...
itli: 1 flg: 2 scn: 0x0000.0fa89d00
itli: 2 flg: 2 scn: 0x0000.0fa89cfe
KDO Op code: LKR row dependencies Disabled
xtype: XA bdba: 0x00401f3a hdba: 0x00401f39
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 3 to: 2
CHANGE #4 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ: 0 OP:5.20
session number = 8
serial number = 1
transaction name =
REDO RECORD - Thread:1 RBA: 0x000094.0000000f.00d8 LEN: 0x0054 VLD: 0x01
SCN: 0x0000.0fa89d02 SUBSCN: 1 04/14/2006 09:31:37
CHANGE #1 TYP:0 CLS:27 AFN:2 DBA:0x00800059 SCN:0x0000.0fa89d01 SEQ: 1 OP:5.4
ktucm redo: slt: 0x001f sqn: 0x00009c72 srt: 0 sta: 9 flg: 0x2
ktucf redo: uba: 0x0080027b.0352.21 ext: 2 spc: 5272 fbi: 0
REDO RECORD - Thread:1 RBA: 0x000094.0000000f.012c LEN: 0x00d4 VLD: 0x01
SCN: 0x0000.0fa89d02 SUBSCN: 1 04/14/2006 09:31:37
CHANGE #1 TYP:0 CLS:33 AFN:2 DBA:0x00800089 SCN:0x0000.0fa89cfe SEQ: 1 OP:5.2
ktudh redo: slt: 0x0028 sqn: 0x00009c88 flg: 0x0011 siz: 52 fbi: 0
uba: 0x00800559.0352.06 pxid: 0x0000.000.00000000
CHANGE #2 TYP:0 CLS:34 AFN:2 DBA:0x00800559 SCN:0x0000.0fa89cfd SEQ: 1 OP:5.1
ktudb redo: siz: 52 spc: 7680 flg: 0x0012 seq: 0x0352 rec: 0x06
xid: 0x0009.028.00009c88
ktubl redo: slt: 40 rci: 0 opc: 5.7 objn: 0 objd: 0 tsn: 0
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
0x00000000 prev ctl uba: 0x00800559.0352.05
prev ctl max cmt scn: 0x0000.0fa89905 prev tx cmt scn: 0x0000.0fa8990a
CHANGE #3 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ: 0 OP:5.20
session number = 8
serial number = 1
transaction name =
REDO RECORD - Thread:1 RBA: 0x000094.00000010.0010 LEN: 0x0054 VLD: 0x01
SCN: 0x0000.0fa89d03 SUBSCN: 1 04/14/2006 09:31:37
CHANGE #1 TYP:0 CLS:33 AFN:2 DBA:0x00800089 SCN:0x0000.0fa89d02 SEQ: 1 OP:5.4
ktucm redo: slt: 0x0028 sqn: 0x00009c88 srt: 0 sta: 9 flg: 0x2
ktucf redo: uba: 0x00800559.0352.06 ext: 2 spc: 7626 fbi: 0
END OF REDO DUMP
----- Redo read statistics for thread 1 -----
Read rate (ASYNC): 7Kb in 0.19s => 0.00 Mb/sec
Longest record: 0Kb, moves: 0/27 (0%)
Change moves: 6/63 (9%), moved: 0Mb
|
5.禁用日志生成,并切换日志
|
SQL> alter system set "_disable_logging"=true;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 148 104857600 1 NO ACTIVE 262708457 14-APR-06
2 1 149 104857600 1 NO CURRENT 262708606 14-APR-06
3 1 147 104857600 1 NO INACTIVE 262675537 14-APR-06
|
6.SCOTT用户再更新
|
SQL> show parameter disable
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_disable_logging boolean TRUE
SQL> update emp set sal=4000 where empno=7788;
1 row updated.
SQL> commit;
Commit complete.
|
7.转储日志
|
SQL> ALTER SYSTEM DUMP LOGFILE '/opt/oracle9/oradata/testora9/redo02.log';
System altered.
|
8.记录此时REDO信息
|
DUMP OF REDO FROM FILE '/opt/oracle9/oradata/testora9/redo02.log'
Opcodes *.*
DBA's: (file # 0, block # 0) thru (file # 65534, block # 4194303)
RBA's: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff
SCN's scn: 0x0000.00000000 thru scn: 0xffff.ffffffff
Times: creation thru eternity
FILE HEADER:
Software vsn=153092096=0x9200000, Compatibility Vsn=153092096=0x9200000
Db ID=1628068176=0x610a5950, Db Name='TESTORA9'
Activation ID=1628067152=0x610a5550
Control Seq=1163=0x48b, File size=204800=0x32000
File Number=2, Blksiz=512, File Type=2 LOG
descrip:"Thread 0001, Seq# 0000000149, SCN 0x00000fa89d7e-0xffffffffffff"
thread: 1 nab: 0xffffffff seq: 0x00000095 hws: 0x1 eot: 1 dis: 0
reset logs count: 0x1f435110 scn: 0x0000.00000001
Low scn: 0x0000.0fa89d7e 04/14/2006 09:33:43
Next scn: 0xffff.ffffffff 01/01/1988 00:00:00
Enabled scn: 0x0000.00000001 04/26/2004 15:56:10
Thread closed scn: 0x0000.0fa89d7e 04/14/2006 09:33:43
Log format vsn: 0x8000000 Disk cksum: 0xcac7 Calc cksum: 0xcac7
Terminal Recovery Stamp scn: 0x0000.00000000 01/01/1988 00:00:00
Most recent redo scn: 0x0000.00000000
Largest LWN: 0 blocks
End-of-redo stream : No
Unprotected mode
Miscellaneous flags: 0x0
END OF REDO DUMP
|
我们看到此时已经没有REDO生成.数据库仅记录少量必要信息而已.
这就是日志禁用的本质所在.
Posted by eygle at 9:04 PM
| Comments (0)
作者:eygle
设置了_disable_logging参数,可以禁用日志的生成,从而提高某些测试的性能.
以下测试,纯属测试目的,其他内容请参考本站其他文章:
http://www.eygle.com/archives/2006/04/more_about_disable_logging.html
禁用日志情况下:
|
SQL> connect / as sysdba
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 286755168 bytes
Fixed Size 731488 bytes
Variable Size 167772160 bytes
Database Buffers 117440512 bytes
Redo Buffers 811008 bytes
Database mounted.
Database opened.
SQL> connect eygle/eygle
Connected.
SQL> show parameter disable
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_disable_logging boolean TRUE
|
测试创建100万数据表:
|
SQL> create table test as select * from dba_objects where 1=0;
Table created.
SQL> set timing on
SQL> begin
2 for i in 1 .. 10000 loop
3 insert into test select * from dba_objects where rownum < 101;
4 commit;
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:40.46
SQL> truncate table test;
Table truncated.
Elapsed: 00:00:52.72
|
大约时间用了40秒.
再看正常日志生成下:
|
SQL> connect / as sysdba
Connected.
SQL> alter system set "_disable_logging"=false;
System altered.
Elapsed: 00:00:00.05
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 286755168 bytes
Fixed Size 731488 bytes
Variable Size 167772160 bytes
Database Buffers 117440512 bytes
Redo Buffers 811008 bytes
Database mounted.
Database opened.
SQL> show parameter disable
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_disable_logging boolean FALSE
SQL> connect eygle/eygle
Connected.
SQL> set timing on
SQL>
SQL> begin
2 for i in 1 .. 10000 loop
3 insert into test select * from dba_objects where rownum < 101;
4 commit;
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
Elapsed: 00:01:54.04
SQL>
SQL> truncate table test;
Table truncated.
Elapsed: 00:01:01.56
|
此时大约用时1分54秒.
两者差距为: 114 / 40 = 2.85 倍.
我们可以看出两者的差距是显著的.不作过多测试了,就此打住.大家有兴趣的可以自己测试一下.
以上测试的环境为:
|
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
PL/SQL Release 9.2.0.4.0 - Production
CORE 9.2.0.3.0 Production
TNS for Solaris: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production
|
Posted by eygle at 4:57 PM
| Comments (2)
作者:eygle
在很久以前,曾经介绍过Oracle的一个内部隐含参数_disable_logging,看到有朋友论述这个参数,今天忍不住做一点补充说明.
1.当然,隐含有风险,设置请谨慎.
2.最初在9.2.0.6 Solaris版本上,设置该参数会触发Bug:3868748 使得数据库无法启动.
从警告日志中,通常可以获得的错误提示是:
ORA-07445: exception encountered:
core dump [kcrfwcint()+1625] [SIGFPE] [Integer divide by zero] [0x828739D] [] [] |
但是好在这个参数是动态的,在存在Bug版本中,我们可以修改数据库当前值来进行测试:
|
SQL> alter system set "_disable_logging"=true scope=memory;
System altered.
|
3.这个Bug的影响范围并非全部,9.2.0.6之下,Oracle9.2.0.5,Oracle9.2.0.4等,都不受这个Bug影响.Oracle声称的修正该Bug的版本是Oracle10gR2(我未验正).
4.这个参数在Oracle9.2.0.4版本的Linux/Solaris上是不存在这个Bug的.在我的环境中经过验证,过程请参考(附注1).
5.在归档模式下,设置该参数会导致日志文件损坏.因为在设置该参数与归档原则违背，归档进程无法识别该日志文件格式，会将该日志文件标记为损坏.所以需要谨慎测试,具体请参考(附注2).
附注1:Solaris Oracle9.2.0.4设置测试过程:
|
$ sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.4.0 - Production on Thu Apr 13 22:51:24 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production
SQL> show parameter disa
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_disable_logging boolean FALSE
SQL> alter system set "_disable_logging"=true scope=both;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 286755168 bytes
Fixed Size 731488 bytes
Variable Size 167772160 bytes
Database Buffers 117440512 bytes
Redo Buffers 811008 bytes
Database mounted.
Database opened.
SQL> show parameter disable
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_disable_logging boolean TRUE
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
PL/SQL Release 9.2.0.4.0 - Production
CORE 9.2.0.3.0 Production
TNS for Solaris: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production
|
附注2:归档模式下,该参数会导致日志文件损坏.
设置该参数后,切换日志:
|
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /opt/oracle/oradata/conner/archive
Oldest online log sequence 20
Next log sequence to archive 23
Current log sequence 23
SQL> alter system switch logfile;
System altered.
SQL> select group#,status from v$Log;
GROUP# STATUS
---------- ----------------
1 INACTIVE
2 ACTIVE
3 INACTIVE
4 CURRENT
SQL> show parameter disable
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_disable_logging boolean TRUE
|
此时可以在警告日志中看到日志损坏的错误信息:
Thu Apr 13 23:33:25 2006
ARC0: Evaluating archive log 2 thread 1 sequence 23
ARC0: Beginning to archive log 2 thread 1 sequence 23
Creating archive destination LOG_ARCHIVE_DEST_1: '/opt/oracle/oradata/conner/archive/1_23.dbf'
ARC0: Log corruption near block 3849 change 0 time ?
ARC0: All Archive destinations made inactive due to error 354
Thu Apr 13 23:33:25 2006
Errors in file /opt/oracle/admin/conner/bdump/conner_arc0_21506.trc:
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 3849 change 0 time 04/13/2006 21:13:03
ORA-00312: online log 2 thread 1: '/opt/oracle/oradata/conner/redo02.log'
ARC0: Archiving not possible: error count exceeded
ARC0: Failed to archive log 2 thread 1 sequence 23
ARCH: Archival stopped, error occurred. Will continue retrying
Thu Apr 13 23:33:26 2006
ORACLE Instance conner - Archival Error
ARCH: Connecting to console port...
Thu Apr 13 23:33:26 2006
ORA-16038: log 2 sequence# 23 cannot be archived
ORA-00354: corrupt redo log block header
ORA-00312: online log 2 thread 1: '/opt/oracle/oradata/conner/redo02.log'
ARCH: Connecting to console port...
ARCH:
Thu Apr 13 23:33:26 2006
ORA-16038: log 2 sequence# 23 cannot be archived
ORA-00354: corrupt redo log block header
ORA-00312: online log 2 thread 1: '/opt/oracle/oradata/conner/redo02.log'
|
Posted by eygle at 4:54 PM
| Comments (0)
