oracle回滚段恢复数据,非正常关闭数据库(数据库归档) 回滚段表空间损坏处理失败...

非正常关闭数据库(数据库归档) 回滚段表空间损坏处理失败如shutdown abort或crash

1.生成测试数据

SQL> conn test/test@jumper

已连接。

SQL> truncate table test;

表已截掉。

SQL> insert into test values(1);

已创建 1 行。

2.非正常关闭数据库，删除回滚段表空间

再打开一窗口

SQL> shutdown abort

ORACLE 例程已经关闭。

SQL> host del D

racleoradatajumperUNDOTBS01.DBF

3.创建启动参数文件

SQL> startup

ORACLE 例程已经启动。

Total System Global Area 135338868 bytes

Fixed Size 453492 bytes

Variable Size 109051904 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

数据库装载完毕。

ORA-01157: 无法标识/锁定数据文件 2 - 请参阅 DBWR 跟踪文件

ORA-01110: 数据文件 2: 'D:ORACLEORADATAJUMPERUNDOTBS01.DBF'

SQL> create pfile from spfile;

文件已创建。

SQL> shutdown immediate

ORA-01109: 数据库未打开

已经卸载数据库。

ORACLE 例程已经关闭。

4.修改启动参数文件D

racleora92databaseinitjumper.ora，moun数据库

原来参数:

undo_management='AUTO'

undo_tablespace='UNDOTBS1'

修改为:

undo_management='manual'

undo_tablespace='system'

_CORRUPTED_ROLLBACK_SEGMENTS=(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,_SYSSMU4$,_SYSSMU5$,_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$)

SQL> startup mount pfile=D

racleora92databaseinitjumper.ora

ORACLE 例程已经启动。

Total System Global Area 135338868 bytes

Fixed Size 453492 bytes

Variable Size 109051904 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

数据库装载完毕。

5.确认undo表空间丢失

SQL> select file# from v$recover_file;

FILE#

----------

2

SQL> select name from v$datafile where file#=2;

NAME

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

D:ORACLEORADATAJUMPERUNDOTBS01.DBF

6.打开数据库

SQL> alter database datafile 'D:ORACLEORADATAJUMPERUNDOTBS01.DBF' offline drop;

数据库已更改。

SQL> recover database;

完成介质恢复。

SQL> alter database open;

alter database open

*

ERROR 位于第 1 行:

ORA-01092: ORACLE 例程终止。强行断开连接

查看alter.log文件报以下错误

ALTER DATABASE RECOVER database

Sun Dec 25 14:12:17 2005

Media Recovery Start

Starting datafile 1 recovery in thread 1 sequence 28

Datafile 1: 'D:ORACLEORADATAJUMPERSYSTEM01.DBF'

Starting datafile 3 recovery in thread 1 sequence 28

Datafile 3: 'D:ORACLEORADATAJUMPERINDX01.DBF'

Starting datafile 4 recovery in thread 1 sequence 28

Datafile 4: 'D:ORACLEORADATAJUMPERTOOLS01.DBF'

Starting datafile 5 recovery in thread 1 sequence 28

Datafile 5: 'D:ORACLEORADATAJUMPERUSERS01.DBF'

Media Recovery Log

Recovery of Online Redo Log: Thread 1 Group 1 Seq 28 Reading mem 0

Mem# 0 errs 0: D:ORACLEORADATAJUMPERREDO01.LOG

Media Recovery Complete

Completed: ALTER DATABASE RECOVER database

Sun Dec 25 14:12:27 2005

alter database open

Sun Dec 25 14:12:28 2005

Beginning crash recovery of 1 threads

Sun Dec 25 14:12:28 2005

Started first pass scan

Sun Dec 25 14:12:28 2005

Completed first pass scan

138 redo blocks read, 0 data blocks need recovery

Sun Dec 25 14:12:29 2005

Started recovery at

Thread 1: logseq 28, block 2, scn 0.139909

Recovery of Online Redo Log: Thread 1 Group 1 Seq 28 Reading mem 0

Mem# 0 errs 0: D:ORACLEORADATAJUMPERREDO01.LOG

Sun Dec 25 14:12:29 2005

Ended recovery at

Thread 1: logseq 28, block 140, scn 0.160187

0 data blocks read, 0 data blocks written, 138 redo blocks read

Crash recovery completed successfully

Sun Dec 25 14:12:29 2005

LGWR: Primary database is in CLUSTER CONSISTENT mode

Thread 1 advanced to log sequence 29

Thread 1 opened at log sequence 29

Current log# 2 seq# 29 mem# 0: D:ORACLEORADATAJUMPERREDO02.LOG

Successful open of redo thread 1.

Sun Dec 25 14:12:29 2005

SMON: enabling cache recovery

Sun Dec 25 14:12:30 2005

ARC0: Evaluating archive log 1 thread 1 sequence 28

ARC0: Beginning to archive log 1 thread 1 sequence 28

Creating archive destination LOG_ARCHIVE_DEST_1: 'D:ORACLEORADATAJUMPERARCHIVE1_28.DBF'

ARC0: Completed archiving log 1 thread 1 sequence 28

Sun Dec 25 14:12:30 2005

Errors in file d

racleadminjumperudumpjumper_ora_3360.trc:

ORA-00704: 引导程序进程失败

ORA-00604: 递归 SQL 层 2 出现错误

ORA-00376: 此时无法读取文件 2

ORA-01110: 数据文件 2: 'D:ORACLEORADATAJUMPERUNDOTBS01.DBF'

Sun Dec 25 14:24:06 2005

Error 704 happened during db open, shutting down database

USER: terminating instance due to error 704

Instance terminated by USER, pid = 3796

ORA-1092 signalled during: alter database open...

对应的jumper_ora_3360.trc错误如下:

Dump file d

racleadminjumperudumpjumper_ora_3360.trc

Sun Dec 25 14:12:19 2005

ORACLE V9.2.0.1.0 - Production vsnsta=0

vsnsql=12 vsnxtr=3

Windows 2000 Version 5.1 Service Pack 2, CPU type 586

Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.1.0 - Production

Windows 2000 Version 5.1 Service Pack 2, CPU type 586

Instance name: jumper

Redo thread mounted by this instance: 1

Oracle process number: 10

Windows thread id: 3360, image: ORACLE.EXE

*** SESSION ID

9.3) 2005-12-25 14:12:19.000

----- Redo read statistics for thread 1 -----

Read rate (ASYNC) = 69Kb/sec => 138 blocks in 1s

Read buffer = 8192Kb (16384 blocks)

Longest record = 0Kb

Record moves = 0/223 (0%)

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

----- Redo read statistics for thread 1 -----

Read rate (ASYNC) = 69Kb/sec => 138 blocks in 1s

Read buffer = 8192Kb (16384 blocks)

Longest record = 0Kb

Record moves = 0/223 (0%)

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

*** 2005-12-25 14:12:29.000

KCRA: start recovery claims for 0 data blocks

*** 2005-12-25 14:12:29.000

KCRA: buffers claimed = 0/0, eliminated = 0

ORA-00704: 引导程序进程失败

ORA-00604: 递归 SQL 层 2 出现错误

ORA-00376: 此时无法读取文件 2

ORA-01110: 数据文件 2: 'D:ORACLEORADATAJUMPERUNDOTBS01.DBF'

怪了，归档模式恢复不成功，而非归档模式恢复成功?

从后台日志来看，归档模式下用序列为28的日志作了介质恢复，然后将此日志归档后，报了启动错误

是不是归档进程后，数据库仍然去读老的回滚表空间,造成打开数据库不成功？而非归档就成功启动了。

原因在那里？

其它测试过程见

http://lovexueer.itpub.net/post/5072/49241