一次UNDO损坏修复过程

早上发现测试库连不上, 启动时发现UNDO有坏块[@more@]

SQL>startup mount;
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced

查看alert_ora9.log

Thu Jul 12 09:05:59 2007
Errors in file /SERVER/ora9/admin/ora9/udump/ora9_ora_1202.trc:
ORA-01578: ORACLE data block corrupted (file # 2, block # 9)
ORA-01110: data file 2: '/SERVER/ora9/oradata/ora9/undotbs01.dbf'
Thu Jul 12 09:05:59 2007
Error 1578 happened during db open, shutting down database
USER: terminating instance due to error 1578
Instance terminated by USER, pid = 1202
ORA-1092 signalled during: alter database open...

发现是UNDO 第9个数据块损坏,试着从数据库备份中恢复一下!
[oracle@ora2 udump]$ rman target /

Recovery Manager: Release 9.2.0.4.0 - Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

connected to target database: ORA9 (DBID=386448625)

RMAN> restore datafile 2;

Starting restore at 12-JUL-07

using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=12 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00002 to /SERVER/ora9/oradata/ora9/undotbs01.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/SERVER/ora9/bak/ora9_20070428_1.rmn tag=TAG20070428T084223 params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 12-JUL-07

RMAN> recover datafile 2;

Starting recover at 12-JUL-07
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 67 is already on disk as file /SERVER/ora9/arc/1_67.dbf
archive log thread 1 sequence 68 is already on disk as file /SERVER/ora9/arc/1_68.dbf
archive log thread 1 sequence 69 is already on disk as file /SERVER/ora9/arc/1_69.dbf
archive log thread 1 sequence 70 is already on disk as file /SERVER/ora9/arc/1_70.dbf
archive log thread 1 sequence 71 is already on disk as file /SERVER/ora9/arc/1_71.dbf
archive log thread 1 sequence 72 is already on disk as file /SERVER/ora9/arc/1_72.dbf
archive log thread 1 sequence 73 is already on disk as file /SERVER/ora9/arc/1_73.dbf
archive log thread 1 sequence 74 is already on disk as file /SERVER/ora9/arc/1_74.dbf
archive log thread 1 sequence 75 is already on disk as file /SERVER/ora9/arc/1_75.dbf
archive log thread 1 sequence 76 is already on disk as file /SERVER/ora9/arc/1_76.dbf
archive log thread 1 sequence 77 is already on disk as file /SERVER/ora9/arc/1_77.dbf
archive log thread 1 sequence 78 is already on disk as file /SERVER/ora9/arc/1_78.dbf
archive log thread 1 sequence 79 is already on disk as file /SERVER/ora9/arc/1_79.dbf
archive log thread 1 sequence 80 is already on disk as file /SERVER/ora9/arc/1_80.dbf
archive log thread 1 sequence 81 is already on disk as file /SERVER/ora9/arc/1_81.dbf
archive log thread 1 sequence 82 is already on disk as file /SERVER/ora9/arc/1_82.dbf
archive log thread 1 sequence 83 is already on disk as file /SERVER/ora9/arc/1_83.dbf
archive log thread 1 sequence 84 is already on disk as file /SERVER/ora9/arc/1_84.dbf
archive log thread 1 sequence 85 is already on disk as file /SERVER/ora9/arc/1_85.dbf
archive log thread 1 sequence 86 is already on disk as file /SERVER/ora9/arc/1_86.dbf
archive log thread 1 sequence 87 is already on disk as file /SERVER/ora9/arc/1_87.dbf
archive log thread 1 sequence 88 is already on disk as file /SERVER/ora9/arc/1_88.dbf
archive log thread 1 sequence 89 is already on disk as file /SERVER/ora9/arc/1_89.dbf
archive log thread 1 sequence 90 is already on disk as file /SERVER/ora9/arc/1_90.dbf
archive log thread 1 sequence 91 is already on disk as file /SERVER/ora9/arc/1_91.dbf
archive log thread 1 sequence 92 is already on disk as file /SERVER/ora9/arc/1_92.dbf
archive log thread 1 sequence 93 is already on disk as file /SERVER/ora9/arc/1_93.dbf
archive log thread 1 sequence 94 is already on disk as file /SERVER/ora9/arc/1_94.dbf
archive log thread 1 sequence 95 is already on disk as file /SERVER/ora9/arc/1_95.dbf
archive log thread 1 sequence 96 is already on disk as file /SERVER/ora9/arc/1_96.dbf
archive log thread 1 sequence 97 is already on disk as file /SERVER/ora9/arc/1_97.dbf
archive log thread 1 sequence 98 is already on disk as file /SERVER/ora9/arc/1_98.dbf
archive log thread 1 sequence 99 is already on disk as file /SERVER/ora9/arc/1_99.dbf
archive log thread 1 sequence 100 is already on disk as file /SERVER/ora9/arc/1_100.dbf
archive log thread 1 sequence 101 is already on disk as file /SERVER/ora9/arc/1_101.dbf
archive log thread 1 sequence 102 is already on disk as file /SERVER/ora9/arc/1_102.dbf
archive log thread 1 sequence 103 is already on disk as file /SERVER/ora9/arc/1_103.dbf
archive log thread 1 sequence 104 is already on disk as file /SERVER/ora9/arc/1_104.dbf
archive log thread 1 sequence 105 is already on disk as file /SERVER/ora9/arc/1_105.dbf
archive log thread 1 sequence 106 is already on disk as file /SERVER/ora9/arc/1_106.dbf
archive log thread 1 sequence 107 is already on disk as file /SERVER/ora9/arc/1_107.dbf
archive log thread 1 sequence 108 is already on disk as file /SERVER/ora9/arc/1_108.dbf
archive log thread 1 sequence 109 is already on disk as file /SERVER/ora9/arc/1_109.dbf
archive log thread 1 sequence 110 is already on disk as file /SERVER/ora9/arc/1_110.dbf
archive log thread 1 sequence 111 is already on disk as file /SERVER/ora9/arc/1_111.dbf
archive log thread 1 sequence 112 is already on disk as file /SERVER/ora9/arc/1_112.dbf
archive log thread 1 sequence 113 is already on disk as file /SERVER/ora9/arc/1_113.dbf
archive log thread 1 sequence 114 is already on disk as file /SERVER/ora9/arc/1_114.dbf
archive log thread 1 sequence 115 is already on disk as file /SERVER/ora9/arc/1_115.dbf
archive log thread 1 sequence 116 is already on disk as file /SERVER/ora9/arc/1_116.dbf
archive log filename=/SERVER/ora9/arc/1_43.dbf thread=1 sequence=43
archive log filename=/SERVER/ora9/arc/1_44.dbf thread=1 sequence=44
archive log filename=/SERVER/ora9/arc/1_45.dbf thread=1 sequence=45
unable to find archive log
archive log thread=1 sequence=46
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/12/2007 09:29:05
RMAN-06054: media recovery requesting unknown log: thread 1 scn 209052

恢复不成功; 那就用隐含参数了! 跳过这些回滚段. 准备新建一个,并删除坏的.
SQL> shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.

*._corrupted_rollback_segments=(_SYSSMU1$, _SYSSMU2$, _SYSSMU3$, _SYSSMU4$, _SYS
SMU5$, _SYSSMU6$, _SYSSMU7$, _SYSSMU8$, _SYSSMU9$, _SYSSMU10$)
加入到PFILE中

SQL> startup restrict mount;
ORACLE instance started.

Total System Global Area 202445884 bytes
Fixed Size 451644 bytes
Variable Size 167772160 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
SQL> alter database datafile 2 offline drop;

Database altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-16068: redo log file activation identifier mismatch
ORA-00312: online log 1 thread 1: '/SERVER/ora9/oradata/ora9/redo01.log'

REDO又有问题, 于是
SQL>alter database clear unarchived logfile group 1;
清除不成功了. 还是用了隐藏参数;
SQL>alter system set "_allow_resetlogs_corruption"=true ;
system alterd.

SQL>startup restrict mount;

SQL>recover database until cancel;

SQL>alter database open resetlogs;
Database opened;

数据库成功打开. 于是马上新建一个UNDO tablespace,并在PFILE中设为系统的UNDO tablespace;
create undo tablespace undo datafile '/........./undo.dbf' size 200M;

之后删除原来的表空间.
SQL>drop tablespace undotbs1 including contents;
ORA-01548 active rollback segments '_SYSSMU1$' found, terminate dropping tablespace

SQL>alter rollback segments '_SYSSMU1$' offline;
连OFFLINE都不让,提示正在使用...

SQL>SHUTDOWN immediate;
还是想到了用隐含参数:
*._offline_rollback_segments=(_SYSSMU1$, _SYSSMU2$, _SYSSMU3$, _SYSSMU4$, _SYS
SMU5$, _SYSSMU6$, _SYSSMU7$, _SYSSMU8$, _SYSSMU9$, _SYSSMU10$)

SQL>startup

SQL>drop tablespace undotbs1 including contents;
tablesapce undotbs1 droped;

终于删除了.
最后把PFILE里的参数文件去掉.
重新启动.并作备份!

注: 这里用到的隐含参数有: _corrupted_rollback_segments ,_allow_resetlogs_corruption ,

_offline_rollback_segments 我的库还好是测试库,所以我能大胆地使用这些隐含参数,如果是生产库,大家在使用这些参数时一定要小心谨慎. 并在事先做好备份.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/703656/viewspace-926168/，如需转载，请注明出处，否则将追究法律责任。