本地测试文件disable_logging参数测试.txt
下面是mos对_disable_logging的描述
|
Description: It is mainly used for getting good benchmarking results. ** NEVER EVER SET THIS ON A PRODUCTION INSTANCE ** 此参数是个动态参数，可以随时修改，千百万注意不要在生产环境配置。 |
下面是基本10.2.0.4，linux平台的测试
欢迎大家加入ORACLE超级群：17115662 免费解决各种ORACLE问题,以后BLOG将迁移到http://www.htz.pw
1，默认参数的配置
|
www.htz.pw > @parameter_hide.sql
+————————————————————————+ | display hide parameter value | +————————————————————————+
Enter Search Parameter (i.e. max|all) : _disable_logging
PARAMETER SESSION_VALUE INSTANCE_VALUE DESCRIPTION —————————————- ——————– ——————– —————– _disable_logging FALSE FALSE Disable logging |
2，创建测试表并dump日志
在创建表之前，为了方面dump日志，我们手动切换一次日志。
|
www.htz.pw > @log_file_9i.sql www.htz.pw > set echo off STATUS TYPE MEMBER ———- ———————————————————- CURRENT ONLINE /oracle/app/oracle/oradata/orcl1024/redo01.log INACTIVE ONLINE /oracle/app/oracle/oradata/orcl1024/redo02.log ACTIVE ONLINE /oracle/app/oracle/oradata/orcl1024/redo03.log
www.htz.pw > set verify on www.htz.pw > set timing on; www.htz.pw > create table scott.htz_pw as select * from dba_objects;
Table created.
Elapsed: 00:00:00.99 这些需要注意时间，后面会用户的 www.htz.pw > alter system dump logfile ‘/oracle/app/oracle/oradata/orcl1024/redo01.log’;
System altered.
Elapsed: 00:00:00.22 www.htz.pw > oradebug setmypid Statement processed. www.htz.pw > oradebug tracefile_name; /oracle/app/oracle/admin/orcl1024/udump/orcl1024_ora_7488.trc www.htz.pw > !wc -l /oracle/app/oracle/admin/orcl1024/udump/orcl1024_ora_7488.trc 8361 /oracle/app/oracle/admin/orcl1024/udump/orcl1024_ora_7488.trc 这里可以看见生成了8361条日志 |
dump日志内容，这里只贴了一部分
|
REDO RECORD – Thread:1 RBA: 0x00000a.00000002.0010 LEN: 0x0288 VLD: 0x05 SCN: 0x0000.002886f1 SUBSCN: 1 07/29/2014 23:50:52 CHANGE #1 TYP:0 CLS:21 AFN:2 DBA:0x00800029 OBJ:4294967295 SCN:0x0000.002886a6 SEQ: 1 OP:5.2 ktudh redo: slt: 0x0018 sqn: 0x0000019e flg: 0x0412 siz: 244 fbi: 0 uba: 0x0080025a.015f.2e pxid: 0x0000.000.00000000 CHANGE #2 TYP:0 CLS:22 AFN:2 DBA:0x0080025a OBJ:4294967295 SCN:0x0000.002886a5 SEQ: 6 OP:5.1 ktudb redo: siz: 244 spc: 2232 flg: 0x0012 seq: 0x015f rec: 0x2e xid: 0x0003.018.0000019e ktubl redo: slt: 24 rci: 0 opc: 11.1 objn: 18 objd: 18 tsn: 0 Undo type: Regular undo Begin trans Last buffer split: No Temp Object: No Tablespace Undo: No 0x00000000 prev ctl uba: 0x008000c0.015e.38 prev ctl max cmt scn: 0x0000.00282bf2 prev tx cmt scn: 0x0000.00282bfb txn start scn: 0xffff.ffffffff logon user: 0 prev brb: 8388799 prev bcl: 0 KDO undo record: KTB Redo op: 0x04 ver: 0x01 op: L itl: xid: 0x0005.00c.0000019c uba: 0x008001fc.0177.23 flg: C— lkc: 0 scn: 0x0000.0027a9e1 KDO Op code: URP row dependencies Disabled xtype: XA flags: 0x00000000 bdba: 0x0040007a hdba: 0x00400079 itli: 1 ispac: 0 maxfr: 4863 tabn: 0 slot: 26(0x1a) flag: 0x2c lock: 0 ckix: 0 ncol: 17 nnew: 11 size: 0 col 0: [ 2] c1 02 col 1: [ 4] c3 06 18 4e col 6: [ 1] 80 col 7: [ 7] 78 6c 03 0c 01 28 31 col 8: [ 7] 78 72 07 15 0b 24 03 col 9: [ 7] 78 6c 03 0c 01 28 31 col 10: [ 1] 80 col 13: [ 1] 80 col 14: *NULL* col 15: [ 1] 80 col 16: [ 4] c3 07 38 24 CHANGE #3 TYP:2 CLS: 1 AFN:1 DBA:0x0040007a OBJ:18 SCN:0x0000.0027a9e1 SEQ: 1 OP:11.5 KTB Redo op: 0x11 ver: 0x01 op: F xid: 0x0003.018.0000019e uba: 0x0080025a.015f.2e Block cleanout record, scn: 0x0000.002886f1 ver: 0x01 opt: 0x02, entries follow… itli: 1 flg: 2 scn: 0x0000.0027a9e1 KDO Op code: URP row dependencies Disabled xtype: XA flags: 0x00000000 bdba: 0x0040007a hdba: 0x00400079 itli: 1 ispac: 0 maxfr: 4863 tabn: 0 slot: 26(0x1a) flag: 0x2c lock: 1 ckix: 0 ncol: 17 nnew: 11 size: 0 col 0: [ 2] c1 02 col 1: [ 4] c3 06 18 58 col 6: [ 1] 80 col 7: [ 7] 78 6c 03 0c 01 28 31 col 8: [ 7] 78 72 07 1d 18 33 35 col 9: [ 7] 78 6c 03 0c 01 28 31 col 10: [ 1] 80 col 13: [ 1] 80 col 14: *NULL* col 15: [ 1] 80 col 16: [ 4] c3 07 38 24 关于dump出现来的日志每一个字段代表什么意思，可以见DSI，里面有详细的说明的 |
3，更改参数值
|
www.htz.pw > @parameter_hide.sql Enter Search Parameter (i.e. max|all) : _disable_logging
PARAMETER SESSION_VALUE INSTANCE_VALUE ——————————————- ————– _disable_logging FALSE FALSE www.htz.pw > alter system set "_disable_logging"=true;
System altered.
www.htz.pw > set timing on www.htz.pw > create table scott.htz_pw1 as select * from dba_objects;
Table created. 这里能看到创建的时间从90多下降到27，性能相当的明显 Elapsed: 00:00:00.27 |
4，dump日志
|
www.htz.pw > alter system dump logfile ‘/oracle/app/oracle/oradata/orcl1024/redo03.log’;
System altered.
Elapsed: 00:00:00.19 www.htz.pw > oradebug setmypid Statement processed. www.htz.pw > oradebug tracefile_name; /oracle/app/oracle/admin/orcl1024/udump/orcl1024_ora_8423.trc
www.htz.pw > !wc -l /oracle/app/oracle/admin/orcl1024/udump/orcl1024_ora_8423.trc 55 /oracle/app/oracle/admin/orcl1024/udump/orcl1024_ora_8423.trc 这里看到dump的内容中，只有55行 |
下面是具体的内容
|
DUMP OF REDO FROM FILE ‘/oracle/app/oracle/oradata/orcl1024/redo03.log’ Opcodes *.* RBAs: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff SCNs: scn: 0x0000.00000000 thru scn: 0xffff.ffffffff Times: creation thru eternity FILE HEADER: Compatibility Vsn = 169870080=0xa200300 Db ID=3178845726=0xbd79561e, Db Name=’ORCL1024′ Activation ID=3187136800=0xbdf7d920 Control Seq=3726=0xe8e, File size=102400=0x19000 File Number=3, Blksiz=512, File Type=2 LOG descrip:"Thread 0001, Seq# 0000000012, SCN 0x000000288821-0xffffffffffff" thread: 1 nab: 0xffffffff seq: 0x0000000c hws: 0x1 eot: 1 dis: 0 resetlogs count: 0x32dd5d49 scn: 0x0000.0023e8b1 (2353329) resetlogs terminal rcv count: 0x0 scn: 0x0000.00000000 prev resetlogs count: 0x32dd5cfc scn: 0x0000.0023e8af (2353327) prev resetlogs terminal rcv count: 0x0 scn: 0x0000.00000000 Low scn: 0x0000.00288821 (2656289) 07/29/2014 23:55:46 Next scn: 0xffff.ffffffff 01/01/1988 00:00:00 Enabled scn: 0x0000.0023e8b1 (2353329) 07/19/2014 22:42:17 Thread closed scn: 0x0000.00288821 (2656289) 07/29/2014 23:55:46 Disk cksum: 0xa421 Calc cksum: 0xa421 Terminal recovery stop scn: 0x0000.00000000 Terminal recovery 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 Thread internal enable indicator: thr: 0, seq: 0 scn: 0x0000.00000000 END OF REDO DUMP —– Redo read statistics for thread 1 —– Read rate (SYNC): 0Kb in 0.19s => 0.00 Mb/sec Total physical reads: 8192Kb ———————————————- 这里可以看到，没有产生任何的日志内容 |
5，在禁用日志后，关闭数据库测试
5.1 正常关闭数据库
|
www.htz.pw > shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. www.htz.pw > startup ORACLE instance started.
Total System Global Area 167772160 bytes Fixed Size 2082432 bytes Variable Size 100665728 bytes Database Buffers 54525952 bytes Redo Buffers 10498048 bytes Database mounted. Database opened. www.htz.pw > select count(*) from scott.htz_pw1;
COUNT(*) ———- 50114
Elapsed: 00:00:00.03 数据没有丢失 |
5.2 异常关闭数据库
|
www.htz.pw > drop table scott.htz_pw1;
Table dropped.
Elapsed: 00:00:01.67 www.htz.pw > startup force; ORACLE instance started.
Total System Global Area 167772160 bytes Fixed Size 2082432 bytes Variable Size 100665728 bytes Database Buffers 54525952 bytes Redo Buffers 10498048 bytes Database mounted. Database opened. www.htz.pw > select count(*) from scott.htz_pw1; select count(*) from scott.htz_pw1 * ERROR at line 1: ORA-00600: internal error code, arguments: [4097], [], [], [], [], [], [], []
强制关闭数据库报下面的错误 Elapsed: 00:00:02.15 这里看到异常关闭的时候，是有问题的，报了4097错误，但是原来没有报UNDO段块与那一个块报错 |
下面看看后台日志报错信息
|
Completed: ALTER DATABASE OPEN Tue Jul 29 23:59:44 2014 Errors in file /oracle/app/oracle/admin/orcl1024/udump/orcl1024_ora_8984.trc: ORA-00354: corrupt redo log block header ORA-00353: log corruption near block 2 change 2656289 time 07/29/2014 23:55:46 ORA-00334: archived log: ‘/oracle/app/oracle/oradata/orcl1024/redo03.log’ Tue Jul 29 23:59:44 2014 Errors in file /oracle/app/oracle/admin/orcl1024/udump/orcl1024_ora_8984.trc: ORA-00600: internal error code, arguments: [4097], [], [], [], [], [], [], [] Tue Jul 29 23:59:46 2014 Errors in file /oracle/app/oracle/admin/orcl1024/udump/orcl1024_ora_8984.trc: ORA-00354: corrupt redo log block header ORA-00353: log corruption near block 2 change 2656289 time 07/29/2014 23:55:46 ORA-00334: archived log: ‘/oracle/app/oracle/oradata/orcl1024/redo03.log’ ORA-00600: internal error code, arguments: [4097], [], [], [], [], [], [], []
trace文件中的部分内容 Read rate (SYNC): 0Kb in 0.01s => 0.00 Mb/sec Total physical reads: 326Kb ———————————————- Error 354 occurred while dumping log ORA-00354: corrupt redo log block header ORA-00353: log corruption near block 2 change 2656289 time 07/29/2014 23:55:46 ORA-00334: archived log: ‘/oracle/app/oracle/oradata/orcl1024/redo03.log’ *** 2014-07-29 23:59:44.747 ksedmp: internal or fatal error ORA-00600: internal error code, arguments: [4097], [], [], [], [], [], [], [] Current SQL statement for this session: select count(*) from scott.htz_pw1 这里可以看到，是由于redo中坏块，导致的报错，查询当前日志组状态，发现报错日志非当前的，所以手动clear一次 www.htz.pw > alter database clear logfile group 3;
Database altered.
Elapsed: 00:00:01.05
www.htz.pw > select count(*) from scott.htz_pw1;
COUNT(*) ———- 433
Elapsed: 00:00:00.01 这里需要是没有报错了，但是表还存在，其实我们的目标是drop这张表。 |
5.3 手动产生checkpoint后异常关闭
|
www.htz.pw > drop table scott.htz_pw1;
Table dropped.
Elapsed: 00:00:00.29 www.htz.pw > alter system switch logfile;
System altered.
Elapsed: 00:00:00.01 www.htz.pw > alter system checkpoint;
System altered.
Elapsed: 00:00:00.04 www.htz.pw > startup force; ORACLE instance started.
Total System Global Area 167772160 bytes Fixed Size 2082432 bytes Variable Size 100665728 bytes Database Buffers 54525952 bytes Redo Buffers 10498048 bytes Database mounted. Database opened. www.htz.pw > select count(*) from scott.htz_pw1; select count(*) from scott.htz_pw1 * ERROR at line 1: ORA-00942: table or view does not exist
Elapsed: 00:00:00.01 这里通过手动checkpoint，将数据完全刷到磁盘后，再关闭数据库，没有遇到报错，不过这种情况在真实环境是不可能存在的。 |
整个实验结束
_disable_logging 全局禁用日志功能：等您坐沙发呢！