探索_disable_logging的损坏作用_结论2

最新推荐文章于 2021-04-03 04:56:04 发布

原创最新推荐文章于 2021-04-03 04:56:04 发布 · 3.1k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#logging #oracle #database #thread #archive #transformation

oracle 专栏收录该内容

137 篇文章

订阅专栏

本文记录了一次因设置Oracle参数_disable_logging为true而导致的数据库实例崩溃案例。详细介绍了错误现象、查阅Metalink确认问题原因及解决步骤。

SQL> alter system set "_disable_logging"=true scope=spfile;

System altered.

Elapsed: 00:00:00.00
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
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.
ORA-03113: end-of-file on communication channel

SQL>

///////////////////////////////////////////////////////////
检查日志：
///////////////////////////////////////////////////////////
Thu Dec 1 09:31:26 2005
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
SCN scheme 2
Using log_archive_dest parameter default value
LICENSE_MAX_USERS = 0
SYS auditing is disabled
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
sga_max_size             = 403772836
large_pool_size          = 16777216
java_pool_size           = 167772160
control_files            = /oracle/oradata/TSMISC02/control01.ctl, /oracle/oradata/TSMISC02/control02.ctl, /oracle/oradata/TSMISC02/control03.ctl
db_block_size            = 8192
db_cache_size            = 33554432
compatible               = 9.2.0.0.0
log_archive_start        = TRUE
log_archive_dest_1       = LOCATION=/oracle/oradata/TSMISC02/archive
log_archive_format       = %t_%s.dbf
_disable_logging         = TRUE
db_file_multiblock_read_count= 16
fast_start_mttr_target   = 300
undo_management          = AUTO
undo_tablespace          = UNDOTBS2
undo_retention           = 10800
O7_DICTIONARY_ACCESSIBILITY= FALSE
remote_login_passwordfile= EXCLUSIVE
db_domain                =
instance_name            = TSMISC02
dispatchers              = (PROTOCOL=TCP) (SERVICE=TSMISC02XDB)
job_queue_processes      = 10
hash_join_enabled        = TRUE
background_dump_dest     = /oracle/admin/TSMISC02/bdump
user_dump_dest           = /oracle/admin/TSMISC02/udump
core_dump_dest           = /oracle/admin/TSMISC02/cdump
sort_area_size           = 524288
db_name                  = TSMISC02
open_cursors             = 300
star_transformation_enabled= FALSE
query_rewrite_enabled    = FALSE
pga_aggregate_target     = 25165824
aq_tm_processes          = 1
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
QMN0 started with pid=9
Thu Dec 1 09:31:27 2005
starting up 1 shared server(s) ...
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
ARCH: STARTING ARCH PROCESSES
ARC0 started with pid=12
ARC0: Archival started
ARC1 started with pid=13
Thu Dec 1 09:31:28 2005
ARCH: STARTING ARCH PROCESSES COMPLETE
Thu Dec 1 09:31:28 2005
ARC1: Archival started
ARC1: Thread not mounted
Thu Dec 1 09:31:28 2005
ARC0: Thread not mounted
Thu Dec 1 09:31:28 2005
ALTER DATABASE   MOUNT
Thu Dec 1 09:31:32 2005
Successful mount of redo thread 1, with mount id 1740736752
Thu Dec 1 09:31:32 2005
Database mounted in Exclusive Mode.
Completed: ALTER DATABASE   MOUNT
Thu Dec 1 09:31:32 2005
ALTER DATABASE OPEN
Thu Dec 1 09:31:32 2005
LGWR: Primary database is in CLUSTER CONSISTENT mode
Thread 1 opened at log sequence 280
Current log# 1 seq# 280 mem# 0: /oracle/oradata/TSMISC02/redo01.log
Successful open of redo thread 1
Thu Dec 1 09:31:32 2005
Errors in file /oracle/admin/TSMISC02/bdump/tsmisc02_lgwr_3103.trc:
ORA-07445: exception encountered: core dump [kcrfwcint()+1625] [SIGFPE] [Integer divide by zero] [0x828739D] [] []
Thu Dec 1 09:31:34 2005
Errors in file /oracle/admin/TSMISC02/bdump/tsmisc02_pmon_3099.trc:
ORA-00470: LGWR process terminated with error
Thu Dec 1 09:31:34 2005
PMON: terminating instance due to error 470
Instance terminated by PMON, pid = 3099
///////////////////////////////////////////////////////////
[oracle@ts01 bdump]$ ll tsmisc02_lgwr_3103.trc
-rw-r-----    1 oracle   oinstall   716212 Dec 1 09:31 tsmisc02_lgwr_3103.trc
[oracle@ts01 bdump]$ ll tsmisc02_pmon_3099.trc
-rw-r-----    1 oracle   oinstall      625 Dec 1 09:31 tsmisc02_pmon_3099.trc
[oracle@ts01 bdump]$

[oracle@ts01 bdump]$ cat tsmisc02_pmon_3099.trc
/oracle/admin/TSMISC02/bdump/tsmisc02_pmon_3099.trc
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
ORACLE_HOME = /oracle/product/920
System name:    Linux
Node name:      ts01
Release:        2.4.21-4.EL
Version:        #1 Fri Oct 3 18:13:58 EDT 2003
Machine:        i686
Instance name: TSMISC02
Redo thread mounted by this instance: 1
Oracle process number: 2
Unix process pid: 3099, image: oracle@ts01 (PMON)

*** SESSION ID:(1.1) 2005-12-01 09:31:34.198
error 470 detected in background process
ORA-00470: LGWR process terminated with error
[oracle@ts01 bdump]$
///////////////////////////////////////////////////////////

SQL> shutdown immediate;
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
[oracle@ts01 oracle]$ 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@ts01 oracle]$ ps -ef | grep oracle
root      2620 2482 0 08:10 pts/0    00:00:00 su - oracle
oracle    2621 2620 0 08:10 pts/0    00:00:00 -bash
root      2666 2528 0 08:25 pts/1    00:00:00 su - oracle
oracle    2667 2666 0 08:25 pts/1    00:00:05 -bash
oracle    3095 2667 0 08:56 pts/1    00:00:00 tail -f alert_TSMISC02.log
root      3172 3126 0 09:33 pts/2    00:00:00 su - oracle
oracle    3173 3172 0 09:33 pts/2    00:00:00 -bash
oracle    3233 2621 0 09:57 pts/0    00:00:00 ps -ef
oracle    3234 2621 0 09:57 pts/0    00:00:00 grep oracle
[oracle@ts01 oracle]$

///////////////////////////////////////////////////////////
察看metalink:
///////////////////////////////////////////////////////////

Bookmark Go to End

Doc ID: Note:342195.1
Subject: ORA-7445 (kcrfwcint)
Type: REFERENCE
Status: PUBLISHED
Content Type: TEXT/X-HTML
Creation Date: 23-NOV-2005
Last Revision Date: 25-NOV-2005

Note: For additional ORA-7445 related information please read Note 211909.1

PURPOSE:

This article provides information on one or more bugs that are
known to result in the given ORA-7445 error.

SUGGESTIONS:

If the Known Issues section below does not help in terms of identifying
a solution, please submit the trace files and alert.log to Oracle
Support Services for further analysis.

Known Issues:
Bug# 3868748   See Note 3868748.8
      Instance crashes when trying to use "_disable_logging"=true
      Fixed: 10.2.0.1
///////////////////////////////////////////////////////////

///////////////////////////////////////////////////////////
修正方法：
不要使用 _disable_logging=TRUE

修改pfile，将 _disable_logging 改为fase：
*._disable_logging=false

///////////////////////////////////////////////////////////
[oracle@ts01 oracle]$ cat a.ora| grep _disable_logging
*._disable_logging=false
#*._disable_logging=TRUE
[oracle@ts01 oracle]$
[oracle@ts01 oracle]$ sqlplus '/ as sysdba'

SQL*Plus: Release 9.2.0.6.0 - Production on Mon Dec 12 13:01:03 2005

Connected to an idle instance.

SQL> startup pfile=a.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.
SQL>
///////////////////////////////////////////////////////////

///////////////////////////////////////////////////////////
相关点alert：
///////////////////////////////////////////////////////////
Mon Dec 12 13:01:09 2005
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
SCN scheme 2
Using log_archive_dest parameter default value
LICENSE_MAX_USERS = 0
SYS auditing is disabled
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
sga_max_size             = 403772836
large_pool_size          = 16777216
java_pool_size           = 167772160
control_files            = /oracle/oradata/TSMISC02/control01.ctl, /oracle/oradata/TSMISC02/control02.ctl, /oracle/oradata/TSMISC02/control03.ctl
db_block_size            = 8192
db_cache_size            = 33554432
compatible               = 9.2.0.0.0
log_archive_start        = TRUE
log_archive_dest_1       = LOCATION=/oracle/oradata/TSMISC02/archive
log_archive_format       = %t_%s.dbf
_disable_logging         = FALSE
db_file_multiblock_read_count= 16
fast_start_mttr_target   = 300
undo_management          = AUTO
undo_tablespace          = UNDOTBS2
undo_retention           = 10800
O7_DICTIONARY_ACCESSIBILITY= FALSE
remote_login_passwordfile= EXCLUSIVE
db_domain                =
instance_name            = TSMISC02
dispatchers              = (PROTOCOL=TCP) (SERVICE=TSMISC02XDB)
job_queue_processes      = 10
hash_join_enabled        = TRUE
background_dump_dest     = /oracle/admin/TSMISC02/bdump
user_dump_dest           = /oracle/admin/TSMISC02/udump
core_dump_dest           = /oracle/admin/TSMISC02/cdump
sort_area_size           = 524288
db_name                  = TSMISC02
open_cursors             = 300
star_transformation_enabled= FALSE
query_rewrite_enabled    = FALSE
pga_aggregate_target     = 25165824
aq_tm_processes          = 1
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
Mon Dec 12 13:01:10 2005
starting up 1 shared server(s) ...
QMN0 started with pid=9
Mon Dec 12 13:01:10 2005
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
ARCH: STARTING ARCH PROCESSES
ARC0 started with pid=12
ARC0: Archival started
Mon Dec 12 13:01:11 2005
ARCH: STARTING ARCH PROCESSES COMPLETE
Mon Dec 12 13:01:11 2005
ALTER DATABASE   MOUNT
ARC1 started with pid=13
ARC1: Archival started
ARC1: Thread not mounted
Mon Dec 12 13:01:11 2005
ARC0: Thread not mounted
Mon Dec 12 13:01:15 2005
Successful mount of redo thread 1, with mount id 1741682071
Mon Dec 12 13:01:15 2005
Database mounted in Exclusive Mode.
Completed: ALTER DATABASE   MOUNT
Mon Dec 12 13:01:15 2005
ALTER DATABASE OPEN
Mon Dec 12 13:01:15 2005
Beginning crash recovery of 1 threads
Mon Dec 12 13:01:15 2005
Started redo scan
Mon Dec 12 13:01:15 2005
Completed redo scan
0 redo blocks read, 0 data blocks need recovery
Mon Dec 12 13:01:15 2005
Started recovery at
Thread 1: logseq 282, block 2, scn 0.917167
Mon Dec 12 13:01:15 2005
Recovery of Online Redo Log: Thread 1 Group 3 Seq 282 Reading mem 0
Mem# 0 errs 0: /oracle/oradata/TSMISC02/redo03.log
Mon Dec 12 13:01:15 2005
Completed redo application
Mon Dec 12 13:01:15 2005
Ended recovery at
Thread 1: logseq 282, block 2, scn 0.937168
0 data blocks read, 0 data blocks written, 0 redo blocks read
Crash recovery completed successfully
Mon Dec 12 13:01:15 2005
LGWR: Primary database is in CLUSTER CONSISTENT mode
Thread 1 advanced to log sequence 283
Thread 1 opened at log sequence 283
Current log# 1 seq# 283 mem# 0: /oracle/oradata/TSMISC02/redo01.log
Successful open of redo thread 1
Mon Dec 12 13:01:15 2005
SMON: enabling cache recovery
Mon Dec 12 13:01:15 2005
ARC0: Evaluating archive   log 3 thread 1 sequence 282
ARC0: Beginning to archive log 3 thread 1 sequence 282
Creating archive destination LOG_ARCHIVE_DEST_1: '/oracle/oradata/TSMISC02/archive/1_282.dbf'
ARC0: Completed archiving log 3 thread 1 sequence 282
Mon Dec 12 13:01:15 2005
Successfully onlined Undo Tablespace 11.
Mon Dec 12 13:01:15 2005
SMON: enabling tx recovery
Mon Dec 12 13:01:15 2005
Database Characterset is ZHS16GBK
replication_dependency_tracking turned off (no async multimaster replication found)
Completed: ALTER DATABASE OPEN
///////////////////////////////////////////////////////////

///////////////////////////////////////////////////////////
总结：
///////////////////////////////////////////////////////////
如果设置了 _disable_logging=true，那么数据库会因为下面的错误而crash：
Thu Dec 1 09:31:32 2005
Errors in file /oracle/admin/TSMISC02/bdump/tsmisc02_lgwr_3103.trc:
ORA-07445: exception encountered: core dump [kcrfwcint()+1625] [SIGFPE] [Integer divide by zero] [0x828739D] [] []
Thu Dec 1 09:31:34 2005
Errors in file /oracle/admin/TSMISC02/bdump/tsmisc02_pmon_3099.trc:
ORA-00470: LGWR process terminated with error
Thu Dec 1 09:31:34 2005
PMON: terminating instance due to error 470
Instance terminated by PMON, pid = 3099

修复的办法是将 _disable_logging 的值改为 false。