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

本文介绍了当Oracle数据库遇到UNDO表空间无法启动的问题时,如何进行故障排查并解决。通过查看告警日志和跟踪文件,确定问题为UNDO表空间不存在或类型错误。解决方案包括将UNDO管理改为手动,删除并重建表空间,最后恢复为自动管理,并使用SPFILE启动数据库。
摘要由CSDN通过智能技术生成

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

故障现场:

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

  • 1
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值