学习数据库无法正常启动，修改Undo表空修复法

先转一篇文章，因为是它教会了我怎么处理：

----------------------------------------------------------------------------------------------------------------

服务器宕机后，数据库无法启动法

故障现场：
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. 

-----------------------------------------------------------------------------------------------

以上是转来的。

我正好也遇到这个问题了，上网看了以上文章，用以下方法解决了。（oracle 9i的环境）

1.启动到到mount状态。

2.创建参数文件。create pfile from spfile

3.关闭数据库,shutdown immediate。

3.刚创建的pfile在$ORACLE_HOME/dbs里。名字叫“init数据库名.ora”，如上面文章的initTEST.ora，是因为它的数据库名就叫TEST。修改这个文件，把undo_management=AUTO改为undo_management='MANUAL'    。要注意这个文件里有一行undo_tablespace='XXXXXXX'。这里指定了undo表空的名字，这个名字要和自己的undo表空名字一样，不然还是会启动失败。

4.用pfile启动数据库到mount 状态  startup mount pfile = '/oracle/app/TEST/initTEST.ora'。

5.启动到open状态。alter database open。上面转的文章里没说要把数据open也可以，但我试了不行，就把数据库改到open状态，居然成功open了。

6.删除undo表空间 
DROP TABLESPACE UNDOTBS INCLUDING CONTENTS AND DATAFILES;

7.创建UNDO表空间

 CREATE UNDO TABLESPACE UNDOTBS DATAFILE '/oraapp/oracle/oradata/TEST/TEST_undo0' SIZE 200M;

9.关闭数据库。shutdown immediate

10.修改initTEST.ora中的undo_management='AUTO'。

11.再次用该文件启动数据库。

 startup pfile = '/oracle/app/TEST/initTEST.ora'。

12.创建spfile。

create spfile from pfile

13。关闭数据库。shutdown immediate;

14。最后正常启动数据库。startup。