-
UNDO出现数据库坏块处理方案
-
设置manual模式使用system回滚段。
- 使用system回滚段
(1)用spfile 创建pfile,然后修改参数:
#*.undo_tablespace='UNDOTBS1'
#*.undo_management='AUTO'
#*.undo_tablespace
#*.undo_retention
undo_management='MANUAL'
rollback_segments='SYSTEM'
(2)用修改之后的pfile,重启DB
SQL> STARTUP MOUNT pfile='F:/initorcl.ora' ;
(3)删除原来的表空间,创建新的UNDO 表空间
SQL> drop tablespace undotbs;
SQL> create undo tablespace undotbs1 datafile '/u01/oradata/undotbs1.dbf' size 10M;
(4)关闭数据库,修改pfile参数,然后用新的pfile创建spfile,在正常启动数据库。
*.undo_tablespace='UNDOTBS1'
*.undo_management='AUTO'
#undo_management='MANUAL'
#rollback_segments='SYSTEM'
offline问题回滚段,重建undo,切换UNDO
SQL> shutdown immediate
ORA-00376: file 15 cannot be read at this time
ORA-01110: data file 15: '/rman/oradata/mat/undo2.dbf'
-- 修改参数文件pfile加上如下两个参数:
$ vi initmat.ora
*._allow_resetlogs_corruption=true
*._corrupted_rollback_segments=(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,_SYSSMU4$,_SYSSMU5$,_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$)
-- 保存后用pfile启动
SQL> startup pfile=initmat.ora
-- 重新创建undo表空间
create undo tablespace undo1 datafile '/data/mat/undo1.dbf' size 100m
/
-- 切换undo表空间
alter system set undo_tablespace=undo1 scope=both;
drop tablespace UNDO2 including contents and datafiles;
-- 再次查询需要恢复的文件,已经不存在了
SQL> select * from v$recover_file;
no rows selected
-- 再次关闭数据库,并重启,沒有发现报错。
SQL> shutdown immediate
-- 把原来的参数进行修改,去掉隐含参数,再次启动
SQL> startup pfile=initmat.ora
SQL> create spfile from pfile;
SQL> shutdown immediate
SQL> startup
操作手册2
-
可以看到,损坏的文件2是undotbs01.dbf,
查看资料,undotbs损坏或丢失时可以采用隐含参数临时启动数据库,然后进行修复。修改init文件,加入
*._allow_resetlogs_corruption=true
(注:允许在数据库文件SCN不一致的情况下启动数据库)
*._corrupted_rollback_segments=(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,_SYSSMU4$,_SYSSMU5$,_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$)
(注:允许在rollback segments损坏的情况下启动数据库)SQL> shutdown abort
ORACLE instance shut down.
SQL> startup pfile='d:/oracle92/ora92/database/inittest.ora'
ORACLE instance started.Total System Global Area 93395628 bytes
Fixed Size 453292 bytes
Variable Size 75497472 bytes
Database Buffers 16777216 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.启动成功,查看下当前的rollback segments
SQL> select segment_name,status from dba_rollback_segs;SEGMENT_NAME STATUS
------------------------------ ----------------
SYSTEM ONLINE
_SYSSMU1$ NEEDS RECOVERY
_SYSSMU2$ NEEDS RECOVERY
_SYSSMU3$ NEEDS RECOVERY
_SYSSMU4$ NEEDS RECOVERY
_SYSSMU5$ NEEDS RECOVERY
_SYSSMU6$ NEEDS RECOVERY
_SYSSMU7$ NEEDS RECOVERY
_SYSSMU8$ NEEDS RECOVERY
_SYSSMU9$ NEEDS RECOVERY
_SYSSMU10$ NEEDS RECOVERYSEGMENT_NAME STATUS
------------------------------ ----------------
_SYSSMU11$ ONLINE12 rows selected.
新建一重做表空间undo
SQL> create undo tablespace undo datafile 'D:/oracle92/oradata/test/undo01.dbf' size 50M reuse autoe
xtend on;Tablespace created.
SQL> select segment_name,status from dba_rollback_segs;
SEGMENT_NAME STATUS
------------------------------ ----------------
SYSTEM ONLINE
_SYSSMU1$ OFFLINE
_SYSSMU2$ NEEDS RECOVERY
_SYSSMU3$ NEEDS RECOVERY
_SYSSMU4$ NEEDS RECOVERY
_SYSSMU5$ NEEDS RECOVERY
_SYSSMU6$ NEEDS RECOVERY
_SYSSMU7$ NEEDS RECOVERY
_SYSSMU8$ NEEDS RECOVERY
_SYSSMU9$ NEEDS RECOVERY
_SYSSMU10$ NEEDS RECOVERYSEGMENT_NAME STATUS
------------------------------ ----------------
_SYSSMU11$ ONLINE
_SYSSMU12$ OFFLINE
_SYSSMU13$ OFFLINE
_SYSSMU14$ OFFLINE
_SYSSMU15$ OFFLINE
_SYSSMU16$ OFFLINE
_SYSSMU17$ OFFLINE
_SYSSMU18$ OFFLINE
_SYSSMU19$ OFFLINE
_SYSSMU20$ OFFLINE
_SYSSMU21$ OFFLINE22 rows selected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.修改init文件
*.undo_tablespace=undoSQL> startup pfile='d:/oracle92/ora92/database/inittest.ora'
ORACLE instance started.Total System Global Area 93395628 bytes
Fixed Size 453292 bytes
Variable Size 75497472 bytes
Database Buffers 16777216 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.删除损坏的undotbs1表空间:
SQL> alter tablespace undotbs1 offline normal;Tablespace altered.
SQL> drop tablespace UNDOTBS1 including contents and datafiles;
Tablespace dropped.
SQL> select * from v$recover_file;
no rows selected
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.修改init文件,注释参数
#*._allow_resetlogs_corruption=true
#*._corrupted_rollback_segments=(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,_SYSSMU4$,_SYSSMU5$,_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$)SQL> startup pfile='d:/oracle92/ora92/database/inittest.ora'
ORACLE instance started.Total System Global Area 93395628 bytes
Fixed Size 453292 bytes
Variable Size 75497472 bytes
Database Buffers 16777216 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL>
至此数据库已经成功修复。**********************************************************************************
需要提醒的是,在删除损坏的重做表空间时,一定要先offline,
否则注释掉隐含参数后就会出现下面的情况。SQL> startup pfile='d:/oracle92/ora92/database/inittest.ora'
ORACLE instance started.Total System Global Area 93395628 bytes
Fixed Size 453292 bytes
Variable Size 75497472 bytes
Database Buffers 16777216 bytes
Redo Buffers 667648 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced查看alert log,本例中会发现下面的信息,oracle标记刚才删除的
重做表空间需要恢复,这时就无法去掉隐含参数了。
......
drop tablespace UNDOTBS1 including contents and datafiles
SMON: about to recover undo segment 6
SMON: mark undo segment 6 as needs recovery
SMON: about to recover undo segment 6
SMON: mark undo segment 6 as needs recovery
SMON: about to recover undo segment 6
SMON: mark undo segment 6 as needs recovery