SOS——数据库卡死问题

数据库HANG住了，检查警告日志。

Thu Feb 25 09:06:44 2010
Shutting down instance: further logons disabled
Shutting down instance (normal)
License high water  = 28
Thu Feb 25 09:14:17 2010
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.4.0.
System parameters with non-default values:
  processes                = 200
  timed_statistics         = TRUE
  shared_pool_size         = 285212672
  large_pool_size          = 369098752
  java_pool_size           = 369098752
  control_files            = /oracle/products/oradata/shosgora/control01.ctl, /oracle/products/oradata/shosgora/control02.ctl, /oracle/products/oradata/shosgora/control03.ctl
  db_block_size            = 8192
  db_cache_size            = 654311424
  compatible               = 9.2.0.0.0
  db_file_multiblock_read_count= 16
  fast_start_mttr_target   = 300
  undo_management          = AUTO
  undo_tablespace          = UNDOTBS1
  undo_retention           = 10800
  remote_login_passwordfile= EXCLUSIVE
  db_domain                =
  instance_name            = shosgora
  dispatchers              = (PROTOCOL=TCP) (SERVICE=shosgoraXDB)
  utl_file_dir             = /oracle/dictionary
  job_queue_processes      = 10
  hash_join_enabled        = TRUE
  background_dump_dest     = /oracle/products/admin/shosgora/bdump
  user_dump_dest           = /oracle/products/admin/shosgora/udump
  core_dump_dest           = /oracle/products/admin/shosgora/cdump
  sort_area_size           = 524288
  db_name                  = shosgora
  open_cursors             = 300
  star_transformation_enabled= FALSE
  query_rewrite_enabled    = FALSE
  pga_aggregate_target     = 268435456
  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 Feb 25 09:14:17 2010
starting up 1 shared server(s) ...
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Thu Feb 25 09:14:18 2010
ALTER DATABASE   MOUNT
Thu Feb 25 09:14:22 2010
Successful mount of redo thread 1, with mount id 1851545130.
Thu Feb 25 09:14:22 2010
Database mounted in Exclusive Mode.
Completed: ALTER DATABASE   MOUNT
Thu Feb 25 09:14:22 2010
ALTER DATABASE OPEN
Thu Feb 25 09:14:22 2010
Beginning crash recovery of 1 threads
Thu Feb 25 09:14:22 2010
Started first pass scan
Thu Feb 25 09:14:22 2010
Completed first pass scan
0 redo blocks read, 0 data blocks need recovery
Thu Feb 25 09:14:22 2010
Started recovery at
Thread 1: logseq 2667, block 204799, scn 0.416302437
Recovery of Online Redo Log: Thread 1 Group 3 Seq 2667 Reading mem 0
  Mem# 0 errs 0: /oracle/products/oradata/shosgora/redo03.log
Thu Feb 25 09:14:22 2010
Completed redo application
Thu Feb 25 09:14:22 2010
Ended recovery at
Thread 1: logseq 2667, block 204799, scn 0.416322438
0 data blocks read, 0 data blocks written, 0 redo blocks read
Crash recovery completed successfully
Thu Feb 25 09:14:22 2010
LGWR: Primary database is in CLUSTER CONSISTENT mode
Thu Feb 25 09:14:22 2010
ARCH: Evaluating archive   log 1 thread 1 sequence 2665
ARCH: Beginning to archive log 1 thread 1 sequence 2665
Creating archive destination LOG_ARCHIVE_DEST_1: '/oracle/products/9i/dbs/arch1_2665.dbf'
ARCH: Completed archiving  log 1 thread 1 sequence 2665
Thu Feb 25 09:14:24 2010
LGWR: Primary database is in CLUSTER CONSISTENT mode
Thread 1 advanced to log sequence 2668
Thread 1 opened at log sequence 2668
  Current log# 1 seq# 2668 mem# 0: /oracle/products/oradata/shosgora/redo01.log
Successful open of redo thread 1.
Thu Feb 25 09:14:25 2010
SMON: enabling cache recovery
Thu Feb 25 09:14:25 2010
Undo Segment 1 Onlined
Undo Segment 2 Onlined
Undo Segment 3 Onlined
Undo Segment 4 Onlined
Undo Segment 5 Onlined
Undo Segment 6 Onlined
Undo Segment 7 Onlined
Undo Segment 8 Onlined
Undo Segment 9 Onlined
Undo Segment 10 Onlined
Successfully onlined Undo Tablespace 1.
Thu Feb 25 09:14:25 2010
SMON: enabling tx recovery
Thu Feb 25 09:14:28 2010
Database Characterset is AL32UTF8
replication_dependency_tracking turned off (no async multimaster replication found)
Completed: ALTER DATABASE OPEN
Shutting down instance: further logons disabled
Shutting down instance (normal)
License high water  = 3
Waiting for dispatcher 'D000' to shutdown
Thu Feb 25 09:20:19 2010
Waiting for shared server 'S000' to die
Thu Feb 25 09:20:48 2010
All dispatchers and shared servers shutdown
Thu Feb 25 09:20:48 2010
ALTER DATABASE CLOSE NORMAL
Thu Feb 25 09:20:48 2010
SMON: disabling tx recovery
SMON: disabling cache recovery
Thu Feb 25 09:20:48 2010
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Thread 1 closed at log sequence 2668
Successful close of redo thread 1.
Thu Feb 25 09:20:48 2010
Completed: ALTER DATABASE CLOSE NORMAL
Thu Feb 25 09:20:48 2010
ALTER DATABASE DISMOUNT
Completed: ALTER DATABASE DISMOUNT
ARCH: Archiving is disabled
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
ARCH: Archiving is disabled
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Thu Feb 25 09:24:13 2010
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.4.0.
System parameters with non-default values:
  processes                = 200
  timed_statistics         = TRUE
  shared_pool_size         = 285212672
  large_pool_size          = 369098752
  java_pool_size           = 369098752
  control_files            = /oracle/products/oradata/shosgora/control01.ctl, /oracle/products/oradata/shosgora/control02.ctl, /oracle/products/oradata/shosgora/control03.ctl
  db_block_size            = 8192
  db_cache_size            = 654311424
  compatible               = 9.2.0.0.0
  db_file_multiblock_read_count= 16
  fast_start_mttr_target   = 300
  undo_management          = AUTO
  undo_tablespace          = UNDOTBS1
  undo_retention           = 10800
  remote_login_passwordfile= EXCLUSIVE
  db_domain                =
  instance_name            = shosgora
  dispatchers              = (PROTOCOL=TCP) (SERVICE=shosgoraXDB)
  utl_file_dir             = /oracle/dictionary
  job_queue_processes      = 10
  hash_join_enabled        = TRUE
  background_dump_dest     = /oracle/products/admin/shosgora/bdump
  user_dump_dest           = /oracle/products/admin/shosgora/udump
  core_dump_dest           = /oracle/products/admin/shosgora/cdump
  sort_area_size           = 524288
  db_name                  = shosgora
  open_cursors             = 300
  star_transformation_enabled= FALSE
  query_rewrite_enabled    = FALSE
  pga_aggregate_target     = 268435456
  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 Feb 25 09:24:14 2010
starting up 1 shared server(s) ...
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Thu Feb 25 09:24:14 2010
ALTER DATABASE   MOUNT
Thu Feb 25 09:24:18 2010
Successful mount of redo thread 1, with mount id 1851551102.
Thu Feb 25 09:24:18 2010
Database mounted in Exclusive Mode.
Completed: ALTER DATABASE   MOUNT
Thu Feb 25 09:24:18 2010
ALTER DATABASE OPEN
Thu Feb 25 09:24:18 2010
LGWR: Primary database is in CLUSTER CONSISTENT mode
Thread 1 opened at log sequence 2668
  Current log# 1 seq# 2668 mem# 0: /oracle/products/oradata/shosgora/redo01.log
Successful open of redo thread 1.
Thu Feb 25 09:24:18 2010
SMON: enabling cache recovery
Thu Feb 25 09:24:18 2010
Undo Segment 1 Onlined
Undo Segment 2 Onlined
Undo Segment 3 Onlined
Undo Segment 4 Onlined
Undo Segment 5 Onlined
Undo Segment 6 Onlined
Undo Segment 7 Onlined
Undo Segment 8 Onlined
Undo Segment 9 Onlined
Undo Segment 10 Onlined
Successfully onlined Undo Tablespace 1.
Thu Feb 25 09:24:18 2010
SMON: enabling tx recovery
Thu Feb 25 09:24:18 2010
Database Characterset is AL32UTF8
replication_dependency_tracking turned off (no async multimaster replication found)
Completed: ALTER DATABASE OPEN

Fri Feb 26 06:32:30 2010
ORACLE Instance shosgora - Can not allocate log, archival required
Fri Feb 26 06:32:30 2010
ARCH: Connecting to console port...
Thread 1 cannot allocate new log, sequence 2669
All online logs needed archiving
  Current log# 1 seq# 2668 mem# 0: /oracle/products/oradata/shosgora/redo01.log
Fri Feb 26 08:54:30 2010
Starting ORACLE instance (normal)
Shutting down instance: further logons disabled
Shutting down instance (normal)
License high water  = 28
Fri Feb 26 08:59:42 2010
Active process 3361 user 'oracle' program '[email protected] (QMN0)'
Active process 9566 user 'oracle' program '[email protected] (TNS V1-V3)'
SHUTDOWN: waiting for logins to complete.
Fri Feb 26 09:09:59 2010
Shutting down instance (abort)
License high water  = 28
Instance terminated by USER, pid = 10604
Fri Feb 26 09:10:10 2010
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.4.0.
System parameters with non-default values:
  processes                = 200
  timed_statistics         = TRUE
  shared_pool_size         = 285212672
  large_pool_size          = 369098752
  java_pool_size           = 369098752
  control_files            = /oracle/products/oradata/shosgora/control01.ctl, /oracle/products/oradata/shosgora/control02.ctl, /oracle/products/oradata/shosgora/control03.ctl
  db_block_size            = 8192
  db_cache_size            = 654311424
  compatible               = 9.2.0.0.0
  db_file_multiblock_read_count= 16
  fast_start_mttr_target   = 300
  undo_management          = AUTO
  undo_tablespace          = UNDOTBS1
  undo_retention           = 10800
  remote_login_passwordfile= EXCLUSIVE
  db_domain                =
  instance_name            = shosgora
  dispatchers              = (PROTOCOL=TCP) (SERVICE=shosgoraXDB)
  utl_file_dir             = /oracle/dictionary
  job_queue_processes      = 10
  hash_join_enabled        = TRUE
  background_dump_dest     = /oracle/products/admin/shosgora/bdump
  user_dump_dest           = /oracle/products/admin/shosgora/udump
  core_dump_dest           = /oracle/products/admin/shosgora/cdump
  sort_area_size           = 524288
  db_name                  = shosgora
  open_cursors             = 300
  star_transformation_enabled= FALSE
  query_rewrite_enabled    = FALSE
  pga_aggregate_target     = 268435456
  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
Fri Feb 26 09:10:11 2010
starting up 1 shared server(s) ...
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Fri Feb 26 09:10:11 2010
ALTER DATABASE   MOUNT
Fri Feb 26 09:10:15 2010
Successful mount of redo thread 1, with mount id 1851662515.
Fri Feb 26 09:10:15 2010
Database mounted in Exclusive Mode.
Completed: ALTER DATABASE   MOUNT
Fri Feb 26 09:10:15 2010
ALTER DATABASE OPEN
Fri Feb 26 09:10:15 2010
Beginning crash recovery of 1 threads
Fri Feb 26 09:10:15 2010
Started first pass scan
Fri Feb 26 09:10:15 2010
Completed first pass scan
0 redo blocks read, 0 data blocks need recovery
Fri Feb 26 09:10:15 2010
Started recovery at
Thread 1: logseq 2668, block 204799, scn 0.416669420
Recovery of Online Redo Log: Thread 1 Group 1 Seq 2668 Reading mem 0
  Mem# 0 errs 0: /oracle/products/oradata/shosgora/redo01.log
Fri Feb 26 09:10:15 2010
Completed redo application
Fri Feb 26 09:10:15 2010
Ended recovery at
Thread 1: logseq 2668, block 204799, scn 0.416689421
0 data blocks read, 0 data blocks written, 0 redo blocks read
Crash recovery completed successfully
Fri Feb 26 09:10:16 2010
LGWR: Primary database is in CLUSTER CONSISTENT mode
Fri Feb 26 09:10:16 2010
ARCH: Evaluating archive   log 2 thread 1 sequence 2666
ARCH: Beginning to archive log 2 thread 1 sequence 2666
Creating archive destination LOG_ARCHIVE_DEST_1: '/oracle/products/9i/dbs/arch1_2666.dbf'
ARCH: Completed archiving  log 2 thread 1 sequence 2666
Fri Feb 26 09:10:18 2010
LGWR: Primary database is in CLUSTER CONSISTENT mode
Thread 1 advanced to log sequence 2669
Thread 1 opened at log sequence 2669
  Current log# 2 seq# 2669 mem# 0: /oracle/products/oradata/shosgora/redo02.log
Successful open of redo thread 1.
Fri Feb 26 09:10:18 2010
SMON: enabling cache recovery
Fri Feb 26 09:10:18 2010
Undo Segment 1 Onlined
Undo Segment 2 Onlined
Undo Segment 3 Onlined
Undo Segment 4 Onlined
Undo Segment 5 Onlined
Undo Segment 6 Onlined
Undo Segment 7 Onlined
Undo Segment 8 Onlined
Undo Segment 9 Onlined
Undo Segment 10 Onlined
Successfully onlined Undo Tablespace 1.
Fri Feb 26 09:10:18 2010
SMON: enabling tx recovery
Fri Feb 26 09:10:18 2010
Database Characterset is AL32UTF8
replication_dependency_tracking turned off (no async multimaster replication found)
Completed: ALTER DATABASE OPEN

查看有没有错误信息grep ORA- logfile_name

ORA-01013: user requested cancel of current operation
ORA-01013: user requested cancel of current operation
ORA-01013: user requested cancel of current operation
ORA-01013: user requested cancel of current operation
ORA-01013: user requested cancel of current operation
ORA-000060: Deadlock detected. More info in file /oracle/products/admin/shosgora/udump/shosgora_ora_13967.trc.
ORA-000060: Deadlock detected. More info in file /oracle/products/admin/shosgora/udump/shosgora_ora_13981.trc.
ORA-000060: Deadlock detected. More info in file /oracle/products/admin/shosgora/udump/shosgora_ora_19054.trc.
ORA-000060: Deadlock detected. More info in file /oracle/products/admin/shosgora/udump/shosgora_ora_13260.trc.
ORA-000060: Deadlock detected. More info in file /oracle/products/admin/shosgora/udump/shosgora_ora_20276.trc.
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP
ORA-00600: internal error code, arguments: [12235], [], [], [], [], [], [], []
ORA-000060: Deadlock detected. More info in file /oracle/products/admin/shosgora/udump/shosgora_ora_31745.trc.
ORA-000060: Deadlock detected. More info in file /oracle/products/admin/shosgora/udump/shosgora_ora_9088.trc.
ORA-000060: Deadlock detected. More info in file /oracle/products/admin/shosgora/udump/shosgora_ora_29879.trc.
ORA-000060: Deadlock detected. More info in file /oracle/products/admin/shosgora/udump/shosgora_ora_1214.trc.
ORA-000060: Deadlock detected. More info in file /oracle/products/admin/shosgora/udump/shosgora_ora_23133.trc.

无法连接有没有什么错误提示的，还是停住了，检查一下listener的日志

调试易

SOS——数据库卡死问题

解决方案 »