| 導購 | 订阅 | 在线投稿
分享
 
 
 

隱含參數_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- 王朝網路 版權所有