非正常关闭数据库(数据库归档) 回滚段表空间损坏处理失败如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