sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 运维DBA >

【学习笔记】Oracle _disable_logging参数控制全局禁用日志功能

时间:2016-10-26 19:33   来源:Oracle研究中心   作者:HTZ   点击:

天萃荷净 Oracle研究中心学习笔记:分享一篇关于Oracle数据库测试disable_logging 全局禁用日志功能案例。

下面是mos对_disable_logging的描述
Description:
~~~~~~~~~~~~
If this is set to true, redo records will NOT be generated
** NO RECOVERY IS POSSIBLE IF THE INSTANCE CRASHES or if it is
SHUTDOWN ABORT **
It is mainly used for getting good benchmarking results.
** NEVER EVER SET THIS ON A PRODUCTION INSTANCE **
此参数是个动态参数,可以随时修改,千百万注意不要在生产环境配置。
下面是基本10.2.0.4,linux平台的测试

1,默认参数的配置

oracleplus.net> @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日志,我们手动切换一次日志。
oracleplus.net> @log_file_9i.sql
oracleplus.net> 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

oracleplus.net> set verify on
oracleplus.net> set timing on;
oracleplus.net> create table scott.htz_pw as select * from dba_objects;

Table created.

Elapsed: 00:00:00.99
这些需要注意时间,后面会用户的
oracleplus.net> alter system dump logfile ‘/oracle/app/oracle/oradata/orcl1024/redo01.log’;

System altered.

Elapsed: 00:00:00.22
oracleplus.net> oradebug setmypid
Statement processed.
oracleplus.net> oradebug tracefile_name;
/oracle/app/oracle/admin/orcl1024/udump/orcl1024_ora_7488.trc
oracleplus.net> !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,更改参数值

oracleplus.net> @parameter_hide.sql
Enter Search Parameter (i.e. max|all) : _disable_logging

PARAMETER SESSION_VALUE INSTANCE_VALUE
——————————————- ————–
_disable_logging FALSE FALSE
oracleplus.net> alter system set "_disable_logging"=true;

System altered.

oracleplus.net> set timing on
oracleplus.net> create table scott.htz_pw1 as select * from dba_objects;

Table created.
这里能看到创建的时间从90多下降到27,性能相当的明显
Elapsed: 00:00:00.27

4,dump日志

oracleplus.net> alter system dump logfile ‘/oracle/app/oracle/oradata/orcl1024/redo03.log’;

System altered.

Elapsed: 00:00:00.19
oracleplus.net> oradebug setmypid
Statement processed.
oracleplus.net> oradebug tracefile_name;
/oracle/app/oracle/admin/orcl1024/udump/orcl1024_ora_8423.trc


oracleplus.net> !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=512Oracleoracleplus.net, 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 正常关闭数据库
oracleplus.net> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
oracleplus.net> 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.
oracleplus.net> select count(*) from scott.htz_pw1;

COUNT(*)
———-
50114

Elapsed: 00:00:00.03
数据没有丢失
5.2 异常关闭数据库
oracleplus.net> drop table scott.htz_pw1;

Table dropped.

Elapsed: 00:00:01.67
oracleplus.net> 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.
oracleplus.net> 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一次
oracleplus.net> alter database clear logfile group 3;

Database altered.

Elapsed: 00:00:01.05
oracleplus.net>

oracleplus.net> select count(*) from scott.htz_pw1;

COUNT(*)
———-
433

Elapsed: 00:00:00.01
这里需要是没有报错了,但是表还存在,其实我们的目标是drop这张表。
5.3 手动产生checkpoint后异常关闭
oracleplus.net> drop table scott.htz_pw1;

Table dropped.

Elapsed: 00:00:00.29
oracleplus.net> alter system switch logfile;

System altered.

Elapsed: 00:00:00.01
oracleplus.net> alter system checkpoint;

System altered.

Elapsed: 00:00:00.04
oracleplus.net> 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.
oracleplus.net> 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,将数据完全刷到磁盘后,再关闭数据库,没有遇到报错,不过这种情况在真实环境是不可能存在的。
整个实验结束
本文固定链接: http://www.htz.pw/2014/07/31/_disable_logging-%e5%85%a8%e5%b1%80%e7%a6%81%e7%94%a8%e6%97%a5%e5%bf%97%e5%8a%9f%e8%83%bd.html | 认真就输

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle _disable_logging参数控制全局禁用日志功能

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1071.html

Oracle研究中心

关键词:

参数_disable_logging使用案例

使用disable_logging禁用全局日志功能

如何禁用Oracle数据库全局日志