undo表空间损坏 数据库打不开

测试过程:
1.创建表
SQL> conn test1/test1
Connected.
SQL> create table t712 (id int);

Table created.

SQL> insert into t712 select * from t4;

1 row created.

SQL> commit;

Commit complete.

SQL> insert into t712 select * from t4;

1 row created.

SQL> insert into t712 select * from t4;

1 row created.

SQL> conn / as sysdba
Connected.
SQL> create table

SQL> create table tt712( id int);

Table created.

SQL> insert into tt712 values (1);

1 row created.

SQL>
  1. shutdown abort 关闭oracle数据库。
  2. 删除undo表空间数据文件 undotbs01.dbf
  3. 启动数据库报 ORA-01092错误
    L> startup;
    ORACLE instance started.

Total System Global Area 599785472 bytes
Fixed Size 2098112 bytes
Variable Size 171969600 bytes
Database Buffers 419430400 bytes
Redo Buffers 6287360 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced

6.警告日志如下
ARC1: Becoming the heartbeat ARCH
Sat Jul 12 02:02:47 CST 2014
Thread 1 opened at log sequence 117
Current log# 3 seq# 117 mem# 0: /u01/app/oracle/oradata/test/redo03.log
Successful open of redo thread 1
Sat Jul 12 02:02:47 CST 2014
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Sat Jul 12 02:02:48 CST 2014
SMON: enabling cache recovery
Sat Jul 12 02:02:48 CST 2014
Errors in file /u01/app/oracle/admin/test/udump/test_ora_12801.trc:
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/u01/app/oracle/oradata/test/undotbs01.dbf'
Sat Jul 12 02:02:48 CST 2014
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Instance terminated by USER, pid = 12801
ORA-1092 signalled during: ALTER DATABASE OPEN…………

问题:
怎么排错,
试过忘了网上N种方法,还是启动不起来,希望大侠给解答一下,谢谢。
2014-07-12 14:09 添加评论 分享
已邀请:
0

macleanliu

赞同来自:

简单来说 你可以 查看 /u01/app/oracle/admin/test/udump/test_ora_12801.trc:

了解引起ORA-704的具体语句 同时定位到数据块,手工修改数据块使其无需访问UNDO ,基本原理是这样。
0

hlyxs - 努力,为了更好的生活。

赞同来自:

谢谢刘大, 数据库修改这块,是需要利用BBED这种方法吗,目前我还没达到那种水平,
我把文件给贴出来了,您给看一下吧?
[oracle@noss01 bdump]$ more ../udump/test_ora_12801.trc
/u01/app/oracle/admin/test/udump/test_ora_12801.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1
System name: Linux
Node name: noss01
Release: 2.6.18-194.el5
Version: #1 SMP Tue Mar 16 21:52:39 EDT 2010
Machine: x86_64
Instance name: test
Redo thread mounted by this instance: 1
Oracle process number: 15
Unix process pid: 12801, image: oracle@noss01 (TNS V1-V3)

*** ACTION NAME:() 2014-07-12 02:02:46.977
*** MODULE NAME:(sqlplus@noss01 (TNS V1-V3)) 2014-07-12 02:02:46.977
*** SERVICE NAME:() 2014-07-12 02:02:46.977
*** SESSION ID:(209.3) 2014-07-12 02:02:46.977
Thread 1 checkpoint: logseq 116, block 2, scn 1863156
cache-low rba: logseq 114, block 3
on-disk rba: logseq 114, block 3, scn 1823147
start recovery at logseq 116, block 2, scn 1863156
----- Redo read statistics for thread 1 -----
Read rate (ASYNC): 0Kb in 0.05s => 0.01 Mb/sec
Total physical reads: 4096Kb
Longest record: 0Kb, moves: 0/1 (0%)
Longest LWN: 0Kb, moves: 0/1 (0%), moved: 0Mb

Last redo scn: 0x0000.001c6df5 (1863157)

----- 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

*** 2014-07-12 02:02:47.032
KCRA: start recovery claims for 0 data blocks
*** 2014-07-12 02:02:47.032
KCRA: blocks processed = 0/0, claimed = 0, eliminated = 0
*** 2014-07-12 02:02:47.033
Recovery of Online Redo Log: Thread 1 Group 2 Seq 116 Reading mem 0
*** 2014-07-12 02:02:47.033
Completed redo application
*** 2014-07-12 02:02:47.033
Completed recovery checkpoint
----- 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-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/u01/app/oracle/oradata/test/undotbs01.dbf'
[oracle@noss01 bdump]$

刘大,网上很多人说,可以利用原来undotbs01所有回滚段类似如下,启动,
我试了一下,根本不行,有个问题,因为库已经打不开了,我根据就不知道
原来有那些回滚段,
有人说利用
[oracle@noss01 bdump]$ strings /u01/app/oracle/oradata/test/system01.dbf | grep _SYSSMU | cut -d $ -f 1 | sort -u
and substr(drs.segment_name,1,7) != '_SYSSMU'
KD' and substr(drs.segment_name,1,7) != ''_SYSSMU'' ' );
_SYSSMU1
_SYSSMU1
_SYSSMU10
_SYSSMU11
_SYSSMU12
_SYSSMU13
_SYSSMU14
_SYSSMU15
_SYSSMU16
_SYSSMU17
_SYSSMU18
_SYSSMU19
_SYSSMU2
_SYSSMU2
_SYSSMU20
_SYSSMU3
_SYSSMU3
_SYSSMU4
_SYSSMU4
_SYSSMU5
_SYSSMU5
_SYSSMU6
_SYSSMU6
_SYSSMU7
_SYSSMU7
_SYSSMU8
_SYSSMU8
_SYSSMU8
_SYSSMU9
_SYSSMU9
SYSTEM _SYSSMU9
这种方法能看出原来undo 表空间使用的回滚段吗?
因为dba_rollback_segs 视图已经无法访问了,
很多人利用的是_corrupted_rollback_segments指定了类似的回滚段,
类似如下
.undo_management='MANUAL'
*.rollback_segments='SYSTEM'

*.undo_tablespace='UNDOTBS1'

.user_dump_dest='/u01/app/oracle/admin/test/udump'
*._offline_rollback_segments='_SYSSMU1$','_SYSSMU2$','_SYSSMU3$','_SYSSMU4$','_SYSSMU5$','_SYSSMU6$','_SYSSMU7$','_SYSSMU8$','_SYSSMU9$','_SYSSMU10$','_SYSSMU11$','_SY
SSMU12$','_SYSSMU13$','_SYSSMU14$','_SYSSMU15$','_SYSSMU16$','_SYSSMU17$','_SYSSMU18$','_SYSSMU19$','_SYSSM20$'
*._allow_resetlogs_corruption=true
*._corrupted_rollback_segments='_SYSSMU1$','_SYSSMU2$','_SYSSMU3$','_SYSSMU4$','_SYSSMU5$','_SYSSMU6$','_SYSSMU7$','_SYSSMU8$','_SYSSMU9$','_SYSSMU10$','_SYSSMU11$','_
SYSSMU12$','_SYSSMU13$','_SYSSMU14$','_SYSSMU15$','_SYSSMU16$','_SYSSMU17$','_SYSSMU18$','_SYSSMU19$','_SYSSM20$'

我试了一下,怎么都不行
也不明白 那个参数到底有什么用,还有一个 offline_rollback_segements;参数
这种方法是不是错误的,还是我写的参数文件不对,有个很大的疑问,有什么办法知道,
数据库使用了那些回滚段,在数据库无法打开的情况下 那个
strings /u01/app/oracle/oradata/test/system01.dbf | grep _SYSSMU | cut -d $ -f 1 | sort -u
这种方法是不是不靠谱?
请刘大多多指导,谢谢了。
0

macleanliu

赞同来自:

【数据恢复】解决ORA-600[4xxx]错误并打开数据库
http://www.askmaclean.com/arch ... .html

要回复问题请先登录注册

退出全屏模式 全屏模式 回复