| 订阅 | 在线投稿
分享
 
 
 

隐含参数_disable_logging对数据库的负作用(1)

来源:互联网网民  宽屏版  评论
2006-04-20 05:58:27

不管是归档数据库,还是非归档数据库,只要设置了_disable_logging=true参数,那么在数据库的启动过程中都会因为ORA-07445 core dump [kcrfwcint()+1625]而异常终止的。隐含参数_disable_logging对数据库的负作用

本文作者: Lunar (lunar.zhang@gmail.com )

摘要:“有oracle dba的地方就有江湖,有江湖的地方就有 voodoo dba和voodoo design(请允许我借用一下kamus的经典词汇)”,不久前,就听说有这样一个voodoo design,大致是说:“如果由于大量的事务操作,产生了大量的日志,那么可以尝试设置隐含参数‘_disable_logging’,从而制止数据库产生日志。。。”。

实际上,我们知道,象这样的Oracle隐含参数只应该在测试环境或者在Oracle Support的建议和支持下使用。如果枉自将其设置到数据库当中,很可能会给你的系统带来意想不到的破坏作用。

下面我们将分别在归档模式数据库和非归档模式数据库下测试这个参数,并最终得出相应的结论。

注意:本文中所有的操作和测试都是在测试环境中进行的,请勿在实际的生产环境等重要数据库上尝试。

1. ORA-07445: exception encountered: core dump [kcrfwcint()+1625]

不论是归档数据库,还是非归档数据库,只要是设置了_disable_logging=true参数,数据库就会在启动过程中报错:

ORA-07445: exception encountered: core dump [kcrfwcint()+1625] [SIGFPE] [Integer divide by zero] [0x828739D] [] []

首先,我们来看看关于_disable_logging这个参数的定义:

sys@TSMISC01> select ksppinm,ksppdesc from x$ksppi where ksppinm like '%logging';

KSPPINM KSPPDESC

----------------------------- ----------------------

_disable_logging Disable logging

Elapsed: 00:00:00.00

sys@TSMISC01>

在metalink上我们还可以看到这样的建议和忠告:“

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 **”

接下来,在测试数据库上测试这个参数,用实例再次证明其强大的破坏作用。

现在看看数据库当前的设置情况:

sys@TSMISC01> archive log list

Database log mode No Archive Mode

Automatic archival Enabled

Archive destination /oracle/oradata/TSMISC01/archive

Oldest online log sequence 143

Current log sequence 145

sys@TSMISC01>

这是一个非归档数据库。

然后关闭数据库,并重新启动:

sys@TSMISC01> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

sys@TSMISC01> startup mount

ORACLE instance started.

Total System Global Area 403772836 bytes

Fixed Size 452004 bytes

Variable Size 369098752 bytes

Database Buffers 33554432 bytes

Redo Buffers 667648 bytes

Database mounted.

sys@TSMISC01> alter database open;

alter database open

*

ERROR at line 1:

ORA-03113: end-of-file on communication channel

Elapsed: 00:00:01.95

sys@TSMISC01>

sys@TSMISC02>

此时,数据库出现03113错误,并在启动过程中异常终止。

现看看数据库的资源(信号量和共享内存段)是否已经在操作系统上释放:

[oracle@ts02 shell]$ ipcs

------ Shared Memory Segments --------

key shmid owner perms bytes nattch status

------ Semaphore Arrays --------

key semid owner perms nsems

------ Message Queues --------

key msqid owner perms used-bytes messages

[oracle@ts02 shell]$

可见已经没有数据库的共享内存和信号量。

这时检查一下alert.log,看看有什么重要信息:

检查alert.log:

Wed Apr 12 09:13:48 2006

Shutting down instance: further logons disabled

Shutting down instance (immediate)

License high water mark = 3

。。。 。。。

Starting up ORACLE RDBMS Version: 9.2.0.6.0.

System parameters with non-default values:

processes = 150

timed_statistics = TRUE

shared_pool_size = 167772160

。。。 。。。

_disable_logging = TRUE 这就是问题的所在-----设置这个参数会导致数据库不能启动

。。。 。。。

PMON started with pid=2

DBW0 started with pid=3

LGWR started with pid=4

CKPT started with pid=5

SMON started with pid=6

RECO started with pid=7

CJQ0 started with pid=8

。。。 。。。

Wed Apr 12 09:14:39 2006

Errors in file /oracle/admin/TSMISC01/bdump/tsmisc01_lgwr_10890.trc:

ORA-07445: exception encountered: core dump [kcrfwcint()+1625] [SIGFPE] [Integer divide by zero] [0x828739D] [] []

Wed Apr 12 09:14:40 2006

Errors in file /oracle/admin/TSMISC01/bdump/tsmisc01_pmon_10886.trc:

ORA-00470: LGWR process terminated with error

Wed Apr 12 09:14:40 2006

PMON: terminating instance due to error 470

Instance terminated by PMON, pid = 10886

从metalink可以找到这个问题主要是由于bug 3868748引起的:

When attempting to run Oracle with redo logs disabled (ie: with "_disable_logging"=true) the instance crasheswith SIGFPE (integer divided by zero exception) in kcrfwcint.

** NOTE:

Oracle does *NOT* support the use of _disable_logging=true but this parameter is sometimes used for bulk load operations. No customer system should be running with this parameter set as it totally invalidates any backup / recovery and instance crash recovery options.

也就是说,oracle不建议使用_disable_logging=true参数来加速bulk load的操作。那么,解决的方法就是不使用这个参数。

现在,做一个临时的pfile,去掉那个 _disable_logging 参数:

[oracle@ts02 shell]$ tail /home/oracle/temp.ora

*.sort_area_size=524288

*.star_transformation_enabled='FALSE'

*.timed_statistics=TRUE

*.undo_management='AUTO'

*.undo_retention=10800

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='/oracle/admin/TSMISC01/udump'

*._disable_logging=false

[oracle@ts02 shell]$

再重新启动数据库:

[oracle@ts02 shell]$ sqlplus '/ as sysdba'

SQL*Plus: Release 9.2.0.6.0 - Production on Wed Apr 12 09:31:23 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to an idle instance.

sys@TSMISC01> startup pfile=/home/oracle/temp.ora

ORACLE instance started.

Total System Global Area 403772836 bytes

Fixed Size 452004 bytes

Variable Size 369098752 bytes

Database Buffers 33554432 bytes

Redo Buffers 667648 bytes

Database mounted.

Database opened.

sys@TSMISC01>

现在,我们看到数据库起来了。由于篇幅的关系,这里就不对归档模式下的同样操作一一列举了,但是如果你去测试的话,你会发现,不管是归档数据库,还是非归档数据库,只要设置了_disable_logging=true参数,那么在数据库的启动过程中都会因为ORA-07445 core dump [kcrfwcint()+1625]而异常终止的。

 
特别声明:以上内容(如有图片或视频亦包括在内)为网络用户发布,本站仅提供信息存储服务。
 
不管是归档数据库,还是非归档数据库,只要设置了_disable_logging=true参数,那么在数据库的启动过程中都会因为ORA-07445 core dump [kcrfwcint()+1625]而异常终止的。隐含参数_disable_logging对数据库的负作用 本文作者: Lunar ([url=mailto:lunar.zhang@gmail.com]lunar.zhang@gmail.com[/url] ) 摘要:“有oracle dba的地方就有江湖,有江湖的地方就有 voodoo dba和voodoo design(请允许我借用一下kamus的经典词汇)”,不久前,就听说有这样一个voodoo design,大致是说:“如果由于大量的事务操作,产生了大量的日志,那么可以尝试设置隐含参数‘_disable_logging’,从而制止数据库产生日志。。。”。 实际上,我们知道,象这样的Oracle隐含参数只应该在测试环境或者在Oracle Support的建议和支持下使用。如果枉自将其设置到数据库当中,很可能会给你的系统带来意想不到的破坏作用。 下面我们将分别在归档模式数据库和非归档模式数据库下测试这个参数,并最终得出相应的结论。 注意:本文中所有的操作和测试都是在测试环境中进行的,请勿在实际的生产环境等重要数据库上尝试。 1. ORA-07445: exception encountered: core dump [kcrfwcint()+1625] 不论是归档数据库,还是非归档数据库,只要是设置了_disable_logging=true参数,数据库就会在启动过程中报错: ORA-07445: exception encountered: core dump [kcrfwcint()+1625] [SIGFPE] [Integer divide by zero] [0x828739D] [] [] 首先,我们来看看关于_disable_logging这个参数的定义: sys@TSMISC01> select ksppinm,ksppdesc from x$ksppi where ksppinm like '%logging'; KSPPINM KSPPDESC ----------------------------- ---------------------- _disable_logging Disable logging Elapsed: 00:00:00.00 sys@TSMISC01> 在metalink上我们还可以看到这样的建议和忠告:“ 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 **” 接下来,在测试数据库上测试这个参数,用实例再次证明其强大的破坏作用。 现在看看数据库当前的设置情况: sys@TSMISC01> archive log list Database log mode No Archive Mode Automatic archival Enabled Archive destination /oracle/oradata/TSMISC01/archive Oldest online log sequence 143 Current log sequence 145 sys@TSMISC01> 这是一个非归档数据库。 然后关闭数据库,并重新启动: sys@TSMISC01> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. sys@TSMISC01> startup mount ORACLE instance started. Total System Global Area 403772836 bytes Fixed Size 452004 bytes Variable Size 369098752 bytes Database Buffers 33554432 bytes Redo Buffers 667648 bytes Database mounted. sys@TSMISC01> alter database open; alter database open * ERROR at line 1: ORA-03113: end-of-file on communication channel Elapsed: 00:00:01.95 sys@TSMISC01> sys@TSMISC02> 此时,数据库出现03113错误,并在启动过程中异常终止。 现看看数据库的资源(信号量和共享内存段)是否已经在操作系统上释放: [oracle@ts02 shell]$ ipcs ------ Shared Memory Segments -------- key shmid owner perms bytes nattch status ------ Semaphore Arrays -------- key semid owner perms nsems ------ Message Queues -------- key msqid owner perms used-bytes messages [oracle@ts02 shell]$ 可见已经没有数据库的共享内存和信号量。 这时检查一下alert.log,看看有什么重要信息: 检查alert.log: Wed Apr 12 09:13:48 2006 Shutting down instance: further logons disabled Shutting down instance (immediate) License high water mark = 3 。。。 。。。 Starting up ORACLE RDBMS Version: 9.2.0.6.0. System parameters with non-default values: processes = 150 timed_statistics = TRUE shared_pool_size = 167772160 。。。 。。。 _disable_logging = TRUE 这就是问题的所在-----设置这个参数会导致数据库不能启动 。。。 。。。 PMON started with pid=2 DBW0 started with pid=3 LGWR started with pid=4 CKPT started with pid=5 SMON started with pid=6 RECO started with pid=7 CJQ0 started with pid=8 。。。 。。。 Wed Apr 12 09:14:39 2006 Errors in file /oracle/admin/TSMISC01/bdump/tsmisc01_lgwr_10890.trc: ORA-07445: exception encountered: core dump [kcrfwcint()+1625] [SIGFPE] [Integer divide by zero] [0x828739D] [] [] Wed Apr 12 09:14:40 2006 Errors in file /oracle/admin/TSMISC01/bdump/tsmisc01_pmon_10886.trc: ORA-00470: LGWR process terminated with error Wed Apr 12 09:14:40 2006 PMON: terminating instance due to error 470 Instance terminated by PMON, pid = 10886 从metalink可以找到这个问题主要是由于bug 3868748引起的: When attempting to run Oracle with redo logs disabled (ie: with "_disable_logging"=true) the instance crasheswith SIGFPE (integer divided by zero exception) in kcrfwcint. ** NOTE: Oracle does *NOT* support the use of _disable_logging=true but this parameter is sometimes used for bulk load operations. No customer system should be running with this parameter set as it totally invalidates any backup / recovery and instance crash recovery options. 也就是说,oracle不建议使用_disable_logging=true参数来加速bulk load的操作。那么,解决的方法就是不使用这个参数。 现在,做一个临时的pfile,去掉那个 _disable_logging 参数: [oracle@ts02 shell]$ tail /home/oracle/temp.ora *.sort_area_size=524288 *.star_transformation_enabled='FALSE' *.timed_statistics=TRUE *.undo_management='AUTO' *.undo_retention=10800 *.undo_tablespace='UNDOTBS1' *.user_dump_dest='/oracle/admin/TSMISC01/udump' *._disable_logging=false [oracle@ts02 shell]$ 再重新启动数据库: [oracle@ts02 shell]$ sqlplus '/ as sysdba' SQL*Plus: Release 9.2.0.6.0 - Production on Wed Apr 12 09:31:23 2006 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to an idle instance. sys@TSMISC01> startup pfile=/home/oracle/temp.ora ORACLE instance started. Total System Global Area 403772836 bytes Fixed Size 452004 bytes Variable Size 369098752 bytes Database Buffers 33554432 bytes Redo Buffers 667648 bytes Database mounted. Database opened. sys@TSMISC01> 现在,我们看到数据库起来了。由于篇幅的关系,这里就不对归档模式下的同样操作一一列举了,但是如果你去测试的话,你会发现,不管是归档数据库,还是非归档数据库,只要设置了_disable_logging=true参数,那么在数据库的启动过程中都会因为ORA-07445 core dump [kcrfwcint()+1625]而异常终止的。
󰈣󰈤
 
 
 
>>返回首页<<
 
 热帖排行
 
 
 
静静地坐在废墟上,四周的荒凉一望无际,忽然觉得,凄凉也很美
©2005- 王朝网络 版权所有