ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 重建undo表空间,恢复数据库

重建undo表空间,恢复数据库

原创 Linux操作系统 作者:hxmflying 时间:2008-08-18 12:03:57 0 删除 编辑
服务器宕机后,数据库无法启动法

故障现场:
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/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 蜀,不要哭泣!
请登录后发表评论 登录
全部评论

注册时间:2008-04-29

  • 博文量
    23
  • 访问量
    69714