login: root Password: Last login: Thu Apr 1 11:39:16 from 10.123.7.162 Sun Microsystems Inc. SunOS 5.8 Generic Patch October 2001 You have new mail. # su - oracle bash-2.03$ cd $ORACLE_BASE/admin/*/bdump bash-2.03$ vi *.log
"alert_gzhs.log" 7438 lines, 283262 characters Sat Feb 7 20:30:06 2004 Starting ORACLE instance (normal) LICENSE_MAX_SESSION = 0 LICENSE_SESSIONS_WARNING = 0 SCN scheme 3 Using log_archive_dest parameter default value LICENSE_MAX_USERS = 0 SYS auditing is disabled Starting up ORACLE RDBMS Version: 9.2.0.3.0. System parameters with non-default values: processes = 150 timed_statistics = TRUE shared_pool_size = 1157627904 large_pool_size = 16777216 java_pool_size = 637534208 control_files = /u01/oradata/gzhs/control01.ctl, /u02/oradata/gzhs/control02.ctl, /u03/oradata/gzhs/control03.ctl db_block_size = 8192 db_cache_size = 2516582400 compatible = 9.2.0.0.0 log_archive_start = TRUE log_archive_dest_1 = LOCATION=/u06/oradata/gzhs/arch log_archive_format = %t_%s.dbf 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 = gzhs dispatchers = (PROTOCOL=TCP) (SERVICE=gzhsXDB) job_queue_processes = 10 hash_join_enabled = TRUE background_dump_dest = /oracle/admin/gzhs/bdump user_dump_dest = /oracle/admin/gzhs/udump core_dump_dest = /oracle/admin/gzhs/cdump sort_area_size = 524288 db_name = gzhs open_cursors = 300 star_transformation_enabled= FALSE query_rewrite_enabled = FALSE pga_aggregate_target = 838860800 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 "alert_gzhs.log" 7438 lines, 283262 characters USER: terminating instance due to error 30012 Instance terminated by USER, pid = 26433 ORA-1092 signalled during: ALTER DATABASE OPEN... Thu Apr 1 11:11:08 2004 Starting ORACLE instance (normal) LICENSE_MAX_SESSION = 0 LICENSE_SESSIONS_WARNING = 0 SCN scheme 3 Using log_archive_dest parameter default value LICENSE_MAX_USERS = 0 SYS auditing is disabled Starting up ORACLE RDBMS Version: 9.2.0.3.0. System parameters with non-default values: processes = 150 timed_statistics = TRUE shared_pool_size = 1157627904 large_pool_size = 16777216 java_pool_size = 637534208 control_files = /u01/oradata/gzhs/control01.ctl, /u02/oradata/gzhs/control02.ctl, /u03/oradata/gzhs/control03.ctl db_block_size = 8192 db_cache_size = 2516582400 compatible = 9.2.0.0.0 log_archive_start = TRUE log_archive_dest_1 = LOCATION=/u06/oradata/gzhs/arch log_archive_format = %t_%s.dbf 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 = gzhs dispatchers = (PROTOCOL=TCP) (SERVICE=gzhsXDB) job_queue_processes = 10 hash_join_enabled = TRUE background_dump_dest = /oracle/admin/gzhs/bdump user_dump_dest = /oracle/admin/gzhs/udump core_dump_dest = /oracle/admin/gzhs/cdump sort_area_size = 524288 db_name = gzhs open_cursors = 300 star_transformation_enabled= FALSE query_rewrite_enabled = FALSE pga_aggregate_target = 838860800 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 Thu Apr 1 11:11:13 2004 starting up 1 shared server(s) ... QMN0 started with pid=9 Thu Apr 1 11:11:13 2004 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 Apr 1 11:11:13 2004 ARCH: STARTING ARCH PROCESSES COMPLETE Thu Apr 1 11:11:13 2004 ARC0: Thread not mounted Thu Apr 1 11:11:13 2004 ARC1: Archival started ARC1: Thread not mounted Thu Apr 1 11:11:14 2004 ALTER DATABASE MOUNT Thu Apr 1 11:11:18 2004 Successful mount of redo thread 1, with mount id 1088380178. Thu Apr 1 11:11:18 2004 Database mounted in Exclusive Mode. Completed: ALTER DATABASE MOUNT Thu Apr 1 11:11:27 2004 alter database open Thu Apr 1 11:11:27 2004 Beginning crash recovery of 1 threads Thu Apr 1 11:11:27 2004 Started first pass scan Thu Apr 1 11:11:28 2004 Completed first pass scan 1 redo blocks read, 0 data blocks need recovery Thu Apr 1 11:11:28 2004 Started recovery at Thread 1: logseq 177, block 2, scn 0.33104793 Recovery of Online Redo Log: Thread 1 Group 3 Seq 177 Reading mem 0 Mem# 0 errs 0: /u01/oradata/gzhs/redo03.log Thu Apr 1 11:11:28 2004 Completed redo application Thu Apr 1 11:11:28 2004 Ended recovery at Thread 1: logseq 177, block 3, scn 0.33124794 0 data blocks read, 0 data blocks written, 1 redo blocks read Crash recovery completed successfully Thu Apr 1 11:11:28 2004 LGWR: Primary database is in CLUSTER CONSISTENT mode Thread 1 advanced to log sequence 178 Thread 1 opened at log sequence 178 Current log# 1 seq# 178 mem# 0: /u01/oradata/gzhs/redo01.log Successful open of redo thread 1. Thu Apr 1 11:11:28 2004 ARC0: Evaluating archive log 3 thread 1 sequence 177 Thu Apr 1 11:11:28 2004 ARC0: Beginning to archive log 3 thread 1 sequence 177 Creating archive destination LOG_ARCHIVE_DEST_1: '/u06/oradata/gzhs/arch/1_177.dbf' Thu Apr 1 11:11:28 2004 SMON: enabling cache recovery ARC0: Completed archiving log 3 thread 1 sequence 177 Thu Apr 1 11:11:28 2004 Errors in file /oracle/admin/gzhs/udump/gzhs_ora_27781.trc: ORA-30012: /263/267/317/373/261/355/277/325/274/344 'UNDOTBS1' /262/273/264/346/324/332/273/362/300/340/320/315/262/273/325/375/310/ 267 Thu Apr 1 11:11:28 2004 Error 30012 happened during db open, shutting down database USER: terminating instance due to error 30012 Instance terminated by USER, pid = 27781 ORA-1092 signalled during: alter database open... :q
.............
在警报日志末尾显示了数据库在Open状态因为错误而异常终止.
2. 尝试重新启动数据库
bash-2.03$ sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.3.0 - Production on 星期四 4月 1 11:43:52 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
已连接到空闲例程。
SQL> startup ORACLE 例程已经启动。
Total System Global Area 4364148184 bytes Fixed Size 736728 bytes Variable Size 1845493760 bytes Database Buffers 2516582400 bytes Redo Buffers 1335296 bytes 数据库装载完毕。 ORA-01092: ORACLE 例程终止。强行断开连接
bash-2.03$ sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.3.0 - Production on 星期四 4月 1 11:46:20 2004 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
已连接到空闲例程。
SQL>
SQL>
SQL> startup mount;
ORACLE 例程已经启动。
Total System Global Area 4364148184 bytes
Fixed Size 736728 bytes
Variable Size 1845493760 bytes
Database Buffers 2516582400 bytes
Redo Buffers 1335296 bytes
数据库装载完毕。
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/oradata/gzhs/system01.dbf
/u01/oradata/gzhs/cwmlite01.dbf
/u01/oradata/gzhs/drsys01.dbf
/u01/oradata/gzhs/example01.dbf
/u01/oradata/gzhs/indx01.dbf
/u01/oradata/gzhs/odm01.dbf
/u01/oradata/gzhs/tools01.dbf
/u01/oradata/gzhs/users01.dbf
/u01/oradata/gzhs/xdb01.dbf
.........................
/u01/oradata/gzhs/UNDOTBS2.dbf
已选择23行。
SQL>
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 10800
undo_suppress_errors boolean FALSE undo_tablespace string UNDOTBS1
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------ spfile string
......................... .............
发现系统没有使用spfile,而初始化参数设置的undo表空间为UNDOTBS1
5. 检查参数文件
bash-2.03$ cd $ORACLE_HOME/dbs
bash-2.03$ ls
init.ora initgzhs.ora initgzhs.ora.old orapwgzhs
initdw.ora initgzhs.ora.hurray lkGZHS snapcf_gzhs.f
bash-2.03$ vi initgzhs.ora
"initgzhs.ora" [Incomplete last line] 105 lines, 3087 characters
#################################################### # Copyright (c) 1991, 2001, 2002 by Oracle Corporation ####################################################
########################################### # Archive ########################################### log_archive_dest_1='LOCATION=/u06/oradata/gzhs/arch' log_archive_format=%t_%s.dbf
log_archive_start=true
########################################### # Cache and I/O ########################################### db_block_size=8192 db_cache_size=2516582400 db_file_multiblock_read_count=16 ########################################### # Cursors and Library Cache ########################################### open_cursors=300 ......................
########################################### # System Managed Undo and Rollback Segments ########################################### undo_management=AUTO undo_retention=10800 undo_tablespace=UNDOTBS1
:q! .............
这个设置是极其可疑的. 怀疑参数文件和实际数据库设置不符.
6. 再次检查alert文件 查找对于UNDO表空间的操作
第一部分,创建数据库时的信息:
Sat Feb 7 20:30:12 2004
CREATE DATABASE gzhs
MAXINSTANCES 1
MAXLOGHISTORY 1
MAXLOGFILES 5
MAXLOGMEMBERS 3
MAXDATAFILES 100
DATAFILE '/u01/oradata/gzhs/system01.dbf' SIZE 500M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/u01/oradata/gzhs/temp01.dbf' SIZE 1000M REUSE AUTOEXTEND
ON NEXT 250M MAXSIZE UNLIMITED
UNDO TABLESPACE "UNDOTBS1" DATAFILE '/u01/oradata/gzhs/undotbs01.dbf' SIZE 1000M REUSE AUTOEXTEND ON
NEXT 100M MAXSIZE UNLIMITED CHARACTER SET ZHS16GBK NATIONAL CHARACTER SET AL16UTF16 LOGFILE GROUP 1 ('/u01/oradata/gzhs/redo01.log') SIZE 256M, GROUP 2 ('/u01/oradata/gzhs/redo02.log') SIZE 256M, GROUP 3 ('/u01/oradata/gzhs/redo03.log') SIZE 256M .............
########################################### # System Managed Undo and Rollback Segments ########################################### undo_management=AUTO undo_retention=10800 undo_tablespace=UNDOTBS2
....
bash-2.03$ sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.3.0 - Production on 星期四 4月 1 11:55:11 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
连接到: Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.3.0 - Production
SQL> select * from v$version;
BANNER ---------------------------------------------------------------- Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production PL/SQL Release 9.2.0.3.0 - Production CORE 9.2.0.3.0 Production TNS for Solaris: Version 9.2.0.3.0 - Production NLSRTL Version 9.2.0.3.0 - Production
SQL> exit 从Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.3.0 - Production中断开 bash-2.03$