当前位置: 首页 > BASIC > 正文

_disable_logging 全局禁用日志功能

本地测试文件disable_logging参数测试.txt

 

下面是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.4linux平台的测试

 

欢迎大家加入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

4dump日志

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 >

 

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,将数据完全刷到磁盘后,再关闭数据库,没有遇到报错,不过这种情况在真实环境是不可能存在的。

整个实验结束

本文固定链接: 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 | 认真就输

该日志由 huangtingzhong 于2014年07月31日发表在 BASIC 分类下, 你可以发表评论,并在保留原文地址及作者的情况下引用到你的网站或博客。
原创文章转载请注明: _disable_logging 全局禁用日志功能 | 认真就输
关键字: ,