公司生产的ORACLE数据库宕机几天宕机了三次，严得影响生产
下面是宕机后启动数据库的ALERT.LOG
Thu Sep 13 10:51:28 2012
ORACLE V9.2.0.8.0 - Production vsnsta=0
vsnsql=12 vsnxtr=3
Windows 2000 Version 5.2 Service Pack 2, CPU type 586
Thu Sep 13 10:51:28 2012
Starting ORACLE instance (normal)
Disable cache advisory with old cache parameters
Thu Sep 13 10:51:29 2012
Window memory size 1073741824
Thu Sep 13 10:51:29 2012
Minimum working set window size : 4096
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.8.0.
System parameters with non-default values:
processes = 800
timed_statistics = TRUE
shared_pool_size = 629145600
large_pool_size = 67108864
java_pool_size = 33554432
pre_page_sga = TRUE
use_indirect_data_buffers= TRUE
control_files = F:\OraData\PRDNG\CONTROL01.CTL, D:\OraData\PRDNG\CONTROL02.CTL, G:\OraData\PRDNG\CONTROL03.CTL
db_block_buffers = 1310720
db_block_size = 8192
_db_block_lru_latches = 64
compatible = 9.2.0.0.0
log_archive_start = TRUE
log_archive_dest_1 = LOCATION=F:\OraData\PRDNG\archive MANDATORY REOPEN
log_archive_format = arch_%t_%s.ARC
db_file_multiblock_read_count= 16
fast_start_mttr_target = 60
undo_management = AUTO
undo_tablespace = UNDOTBS
undo_retention = 10800
remote_login_passwordfile= EXCLUSIVE
db_domain =
job_queue_processes = 20
parallel_max_servers = 32
hash_join_enabled = TRUE
hash_area_size = 10485760
background_dump_dest = C:\oracle\admin\PRDNG\bdump
user_dump_dest = C:\oracle\admin\PRDNG\udump
core_dump_dest = C:\oracle\admin\PRDNG\cdump
sort_area_size = 524288
db_name = prdng
open_cursors = 1000
star_transformation_enabled= FALSE
query_rewrite_enabled = FALSE
pga_aggregate_target = 536870912
aq_tm_processes = 1
PMON started with pid=2, OS id=7620
DBW0 started with pid=3, OS id=7808
DBW1 started with pid=4, OS id=6792
LGWR started with pid=5, OS id=9220
CKPT started with pid=6, OS id=2652
SMON started with pid=7, OS id=6256
RECO started with pid=8, OS id=9272
CJQ0 started with pid=9, OS id=8336
QMN0 started with pid=10, OS id=9344
Thu Sep 13 10:51:34 2012
ARCH: STARTING ARCH PROCESSES
ARC0 started with pid=11, OS id=5656
ARC0: Archival started
ARC1 started with pid=12, OS id=3908
Thu Sep 13 10:51:34 2012
ARC1: Archival started
Thu Sep 13 10:51:35 2012
ARCH: STARTING ARCH PROCESSES COMPLETE
Thu Sep 13 10:51:35 2012
ARC0: Becoming the 'no FAL' ARCH
ARC0: Becoming the 'no FAL' ARCHARC0: Thread not mounted
Thu Sep 13 10:51:36 2012
ARC1: Becoming the heartbeat ARCH
ARC1: Becoming the heartbeat ARCHARC1: Thread not mounted
Thu Sep 13 10:51:58 2012
ALTER DATABASE MOUNT
Thu Sep 13 10:52:02 2012
Successful mount of redo thread 1, with mount id 2184128782
Thu Sep 13 10:52:02 2012
Database mounted in Exclusive Mode.
Completed: ALTER DATABASE MOUNT
Thu Sep 13 10:52:43 2012
ALTER DATABASE OPEN
-------------------------------------------------------------------------
下面是跟踪文件Dump file c:\oracle\admin\prdng\udump\prdng_ora_5436.trc
Thu Sep 13 10:19:31 2012
ORACLE V9.2.0.8.0 - Production vsnsta=0
vsnsql=12 vsnxtr=3
Windows 2000 Version 5.2 Service Pack 2, CPU type 586
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
Windows 2000 Version 5.2 Service Pack 2, CPU type 586
Instance name: prdngRedo thread mounted by this instance: 1Oracle process number: 212Windows thread id: 5436, image: ORACLE.EXE
*** 2012-09-13 10:19:31.046
*** SESSION ID:(213.63) 2012-09-13 10:19:31.015
opitsk: network error occurred while two-task session with mso trying to send break; error code = 12152
Dump file c:\oracle\admin\prdng\udump\prdng_ora_5436.trc
Thu Sep 13 10:46:27 2012
ORACLE V9.2.0.8.0 - Production vsnsta=0
vsnsql=12 vsnxtr=3
Windows 2000 Version 5.2 Service Pack 2, CPU type 586
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
Windows 2000 Version 5.2 Service Pack 2, CPU type 586
Instance name: prdngRedo thread mounted by this instance: 1Oracle process number: 200Windows thread id: 5436, image: ORACLE.EXE
*** 2012-09-13 10:46:27.921
*** SESSION ID:(401.40814) 2012-09-13 10:46:27.890
opitsk: network error occurred while two-task session with mso trying to send break; error code = 12152
下面是宕机后启动数据库的ALERT.LOG
Thu Sep 13 10:51:28 2012
ORACLE V9.2.0.8.0 - Production vsnsta=0
vsnsql=12 vsnxtr=3
Windows 2000 Version 5.2 Service Pack 2, CPU type 586
Thu Sep 13 10:51:28 2012
Starting ORACLE instance (normal)
Disable cache advisory with old cache parameters
Thu Sep 13 10:51:29 2012
Window memory size 1073741824
Thu Sep 13 10:51:29 2012
Minimum working set window size : 4096
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.8.0.
System parameters with non-default values:
processes = 800
timed_statistics = TRUE
shared_pool_size = 629145600
large_pool_size = 67108864
java_pool_size = 33554432
pre_page_sga = TRUE
use_indirect_data_buffers= TRUE
control_files = F:\OraData\PRDNG\CONTROL01.CTL, D:\OraData\PRDNG\CONTROL02.CTL, G:\OraData\PRDNG\CONTROL03.CTL
db_block_buffers = 1310720
db_block_size = 8192
_db_block_lru_latches = 64
compatible = 9.2.0.0.0
log_archive_start = TRUE
log_archive_dest_1 = LOCATION=F:\OraData\PRDNG\archive MANDATORY REOPEN
log_archive_format = arch_%t_%s.ARC
db_file_multiblock_read_count= 16
fast_start_mttr_target = 60
undo_management = AUTO
undo_tablespace = UNDOTBS
undo_retention = 10800
remote_login_passwordfile= EXCLUSIVE
db_domain =
job_queue_processes = 20
parallel_max_servers = 32
hash_join_enabled = TRUE
hash_area_size = 10485760
background_dump_dest = C:\oracle\admin\PRDNG\bdump
user_dump_dest = C:\oracle\admin\PRDNG\udump
core_dump_dest = C:\oracle\admin\PRDNG\cdump
sort_area_size = 524288
db_name = prdng
open_cursors = 1000
star_transformation_enabled= FALSE
query_rewrite_enabled = FALSE
pga_aggregate_target = 536870912
aq_tm_processes = 1
PMON started with pid=2, OS id=7620
DBW0 started with pid=3, OS id=7808
DBW1 started with pid=4, OS id=6792
LGWR started with pid=5, OS id=9220
CKPT started with pid=6, OS id=2652
SMON started with pid=7, OS id=6256
RECO started with pid=8, OS id=9272
CJQ0 started with pid=9, OS id=8336
QMN0 started with pid=10, OS id=9344
Thu Sep 13 10:51:34 2012
ARCH: STARTING ARCH PROCESSES
ARC0 started with pid=11, OS id=5656
ARC0: Archival started
ARC1 started with pid=12, OS id=3908
Thu Sep 13 10:51:34 2012
ARC1: Archival started
Thu Sep 13 10:51:35 2012
ARCH: STARTING ARCH PROCESSES COMPLETE
Thu Sep 13 10:51:35 2012
ARC0: Becoming the 'no FAL' ARCH
ARC0: Becoming the 'no FAL' ARCHARC0: Thread not mounted
Thu Sep 13 10:51:36 2012
ARC1: Becoming the heartbeat ARCH
ARC1: Becoming the heartbeat ARCHARC1: Thread not mounted
Thu Sep 13 10:51:58 2012
ALTER DATABASE MOUNT
Thu Sep 13 10:52:02 2012
Successful mount of redo thread 1, with mount id 2184128782
Thu Sep 13 10:52:02 2012
Database mounted in Exclusive Mode.
Completed: ALTER DATABASE MOUNT
Thu Sep 13 10:52:43 2012
ALTER DATABASE OPEN
-------------------------------------------------------------------------
下面是跟踪文件Dump file c:\oracle\admin\prdng\udump\prdng_ora_5436.trc
Thu Sep 13 10:19:31 2012
ORACLE V9.2.0.8.0 - Production vsnsta=0
vsnsql=12 vsnxtr=3
Windows 2000 Version 5.2 Service Pack 2, CPU type 586
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
Windows 2000 Version 5.2 Service Pack 2, CPU type 586
Instance name: prdngRedo thread mounted by this instance: 1Oracle process number: 212Windows thread id: 5436, image: ORACLE.EXE
*** 2012-09-13 10:19:31.046
*** SESSION ID:(213.63) 2012-09-13 10:19:31.015
opitsk: network error occurred while two-task session with mso trying to send break; error code = 12152
Dump file c:\oracle\admin\prdng\udump\prdng_ora_5436.trc
Thu Sep 13 10:46:27 2012
ORACLE V9.2.0.8.0 - Production vsnsta=0
vsnsql=12 vsnxtr=3
Windows 2000 Version 5.2 Service Pack 2, CPU type 586
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
Windows 2000 Version 5.2 Service Pack 2, CPU type 586
Instance name: prdngRedo thread mounted by this instance: 1Oracle process number: 200Windows thread id: 5436, image: ORACLE.EXE
*** 2012-09-13 10:46:27.921
*** SESSION ID:(401.40814) 2012-09-13 10:46:27.890
opitsk: network error occurred while two-task session with mso trying to send break; error code = 12152
--metalink上看到的，希望你能用上
Applies to:
Oracle Server - Enterprise Edition - Version 9.2.0.1 and later
Generic Windows
***Checked for relevance on 25-Apr-2012***Symptoms
When the Oracle service is restarted, there is no information about a database shutdown.
However, there is an entry in the alert log for a subsequent database startup. ORA_SID_AUTOSTART and ORA_SID_SHUTDOWN registry settings are set to TRUE. Eg: C:\> NET STOP "OracleServiceAPU01"
The OracleServiceAPU01 service is stopping....
The OracleServiceAPU01 service was stopped successfully.
C:\>time /t
10:33 Alert Log shows:Wed Jun 20 10:28:30 2007
Database Characterset is WE8MSWIN1252
replication_dependency_tracking turned off (no async multimaster replication found)
Completed: alter database open
Dump file d:\oracle\admin\apu01\bdump\alert_apu01.log
Wed Jun 20 10:32:44 2007
ORACLE V9.2.0.6.0 - Production vsnsta=0
vsnsql=12 vsnxtr=3
Windows 2000 Version 5.2 Service Pack 1, CPU type 586
Wed Jun 20 10:32:44 2007
Starting ORACLE instance (normal)
.
.>>>>>>>>>>>>>> There is no information of shutdown but we see the re-start entry.
.
Wed Jun 20 10:32:53 2007
alter database open
Wed Jun 20 10:32:53 2007
Beginning crash recovery of 1 threads >>>>>>>>>>>>> we find crash recovery happening.
Wed Jun 20 10:32:53 2007
Started redo scan
Wed Jun 20 10:32:53 2007
Completed redo scanCause
Shutdown is not getting enough time to complete as the registry setting for ORA_SID_SHUTDOWN_TIMEOUT is not sufficient.
This parameter sets the maximum time to wait before the service for a particular SID stops. If this time is exceeded, the services gets terminated and hence we find a instance crash happening with no entries for a proper shutdown. Hence, a subsequent startup performs an instance crash recovery.Solution
Increase the value for the registry parameter ORA_SID_SHUTDOWN_TIMEOUT.
Default value for the ORA_SID_SHUTDOWN_TIMEOUT parameter (for older Oracle versions) is 30.
The default value for Oracle 10.2 and 11.2 is 90.Edit the value for the ORA_SID_SHUTDOWN_TIMEOUT parameter to a higher value and restart the server (OS) for the changes to take effect.The above registry setting can be found in the location like HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0
For 10g and higher, the registry entries are found in entries like HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb11g_home1