服务器宕机后,数据库无法启动法
故障现场:
SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup mount pfile='/oraapp/oracle/admin/TEST/pfile/initTEST.ora';
ORACLE instance started.
Total System Global Area 370218244 bytes
Fixed Size 451844 bytes
Variable Size 251658240 bytes
Database Buffers 117440512 bytes
Redo Buffers 667648 bytes
Database mounted.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
查看告警日志:
Mon Aug 18 10:35:27 2008
SMON: enabling cache recovery
Mon Aug 18 10:35:27 2008
Errors in file /oraapp/oracle/admin/TEST/udump/test_ora_4096.trc:
ORA-30012: undo tablespace 'UNDOTBS' does not exist or of wrong type
Mon Aug 18 10:35:27 2008
Error 30012 happened during db open, shutting down database
USER: terminating instance due to error 30012
Instance terminated by USER, pid = 4096
ORA-1092 signalled during: alter database open...
查看跟踪文件:
[oracle@oracle bdump]$ cat /oraapp/oracle/admin/TEST/udump/test_ora_4096.trc
/oraapp/oracle/admin/TEST/udump/test_ora_4096.trc
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
ORACLE_HOME = /oraapp/oracle/92
System name: Linux
Node name: oracle
Release: 2.6.9-5.EL
Version: #1 Wed Jan 5 19:22:18 EST 2005
Machine: i686
Instance name: TEST
Redo thread mounted by this instance: 1
Oracle process number: 14
Unix process pid: 4096, image: oracle@oracle (TNS V1-V3)
*** SESSION ID:(11.3) 2008-08-18 10:35:27.213
Thread 1 recovery from rba:0x0008d3.00000002.0010 scn:0x0000.02128819
----- Redo read statistics for thread 1 -----
Read rate (ASYNC): 0Kb in 0.04s => 0.00 Mb/sec
Longest record: 0Kb, moves: 0/1 (0%)
----------------------------------------------
----- Recovery Hash Table Statistics ---------
Hash table buckets = 32768
Longest hash chain = 0
Average hash chain = 0/0 = 0.0
Max compares per lookup = 0
Avg compares per lookup = 0/0 = 0.0
----------------------------------------------
*** 2008-08-18 10:35:27.258
KCRA: start recovery claims for 0 data blocks
*** 2008-08-18 10:35:27.258
KCRA: buffers claimed = 0/0, eliminated = 0
----- Recovery Hash Table Statistics ---------
Hash table buckets = 32768
Longest hash chain = 0
Average hash chain = 0/0 = 0.0
Max compares per lookup = 0
Avg compares per lookup = 0/0 = 0.0
----------------------------------------------
ORA-30012: undo tablespace 'UNDOTBS' does not exist or of wrong type
估计是UNDO表空间不能恢复
解决方法:将其改为手动管理UNDO段,数据库启动到MOUNT状态,移去以前表空间,打开数据库,
重建表空间,改为自动管理undo表空间,用spfile启动。
1) 修改initTEST.ora
中参数undo_management=AUTO改为undo_management='MANUAL'
2) 启动到到mount状态
SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup mount pfile='/oraapp/oracle/admin/TEST/pfile/initTEST.ora';
ORACLE instance started.
Total System Global Area 370218244 bytes
Fixed Size 451844 bytes
Variable Size 251658240 bytes
Database Buffers 117440512 bytes
Redo Buffers 667648 bytes
Database mounted.
3)删除undo表空间
DROP TABLESPACE UNDOTBS INCLUDING CONTENTS AND DATAFILES;
4)打开数据库
SQL> alter database open;
Database altered.
5)创建UNDO表空间
SQL> CREATE UNDO TABLESPACE UNDOTBS DATAFILE '/oraapp/oracle/oradata/TEST/TEST_undo0' SIZE 200M;
Tablespace created.
6) 修改initTEST.ora中参数undo_management='MANUAL'改为undo_management=AUTO
7) spfile启动
SQL> create spfile from pfile='/oraapp/oracle/admin/TEST/pfile/initTEST.ora';
File created.
SQL>
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area 571545076 bytes
Fixed Size 452084 bytes
Variable Size 352321536 bytes
Database Buffers 218103808 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/14019112/viewspace-429739/,如需转载,请注明出处,否则将追究法律责任。