联系：手机/微信(+86 17813235971) QQ(107644445)
标题：重建控制文件丢失undo异常恢复—ORA-01173模拟与恢复
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
数据库异常关闭，使用resetlogs方式重建控制文件，不包含undo表空间相关数据库，然后尝试resetlogs打开数据库，非常容易出现ORA-01173: data dictionary indicates missing data file from system tablespace之类的错误，本文通过模拟该错误，并简单提供解决方法
模拟ORA-01173错误
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> shutdown abort;
ORACLE instance shut down.
--除掉undo表空间相关数据库然后使用resetlogs模式重建控制文件
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 175775744 bytes
Fixed Size 1343668 bytes
Variable Size 117444428 bytes
Database Buffers 50331648 bytes
Redo Buffers 6656000 bytes
SQL> @/tmp/ctl.sql
Control file created.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/oracle/oradata/ora11g/system01.dbf'
SQL> recover database until cancel;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
SQL> recover database until cancel using backup controlfile;
ORA-00279: change 12696930512547 generated at 08/26/2013 14:43:13 needed for
thread 1
ORA-00289: suggestion : /u01/oracle/oradata/ora11g/archivelog/1_8_824475918.dbf
ORA-00280: change 12696930512547 for thread 1 is in sequence #8
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel;
ORA-00308: cannot open archived log 'cancel;'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/oracle/oradata/ora11g/system01.dbf'
ORA-01112: media recovery not started
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/oracle/oradata/ora11g/system01.dbf'
SQL> recover database until cancel using backup controlfile;
ORA-00279: change 12696930512547 generated at 08/26/2013 14:43:13 needed for
thread 1
ORA-00289: suggestion : /u01/oracle/oradata/ora11g/archivelog/1_8_824475918.dbf
ORA-00280: change 12696930512547 for thread 1 is in sequence #8
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/oracle/oradata/ora11g/redo02.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-01173: data dictionary indicates missing data file from system tablespace
Process ID: 15053
Session ID: 125 Serial number: 3
alert日志
Mon Aug 26 15:22:20 2013 alter database open resetlogs RESETLOGS after complete recovery through change 12696930514503 Archived Log entry 1 added for thread 1 sequence 7 ID 0xfb91eb36 dest 1: Archived Log entry 2 added for thread 1 sequence 8 ID 0xfb91eb36 dest 1: Archived Log entry 3 added for thread 1 sequence 6 ID 0xfb91eb36 dest 1: Clearing online redo logfile 1 /u01/oracle/oradata/ora11g/redo01.log Clearing online log 1 of thread 1 sequence number 7 Clearing online redo logfile 1 complete Clearing online redo logfile 2 /u01/oracle/oradata/ora11g/redo02.log Clearing online log 2 of thread 1 sequence number 8 Clearing online redo logfile 2 complete Clearing online redo logfile 3 /u01/oracle/oradata/ora11g/redo03.log Clearing online log 3 of thread 1 sequence number 6 Clearing online redo logfile 3 complete Resetting resetlogs activation ID 4220644150 (0xfb91eb36) Online log /u01/oracle/oradata/ora11g/redo01.log: Thread 1 Group 1 was previously cleared Online log /u01/oracle/oradata/ora11g/redo02.log: Thread 1 Group 2 was previously cleared Online log /u01/oracle/oradata/ora11g/redo03.log: Thread 1 Group 3 was previously cleared Mon Aug 26 15:22:26 2013 Setting recovery target incarnation to 2 Using SCN growth rate of 16384 per second Mon Aug 26 15:22:26 2013 Assigning activation ID 4220652407 (0xfb920b77) LGWR: STARTING ARCH PROCESSES Mon Aug 26 15:22:26 2013 ARC0 started with pid=20, OS id=15131 ARC0: Archival started LGWR: STARTING ARCH PROCESSES COMPLETE ARC0: STARTING ARCH PROCESSES Mon Aug 26 15:22:27 2013 ARC1 started with pid=21, OS id=15135 Mon Aug 26 15:22:27 2013 ARC2 started with pid=22, OS id=15139 Thread 1 opened at log sequence 1 Current log# 1 seq# 1 mem# 0: /u01/oracle/oradata/ora11g/redo01.log Successful open of redo thread 1 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set Mon Aug 26 15:22:27 2013 SMON: enabling cache recovery Mon Aug 26 15:22:27 2013 ARC3 started with pid=23, OS id=15143 ARC1: Archival started ARC2: Archival started ARC2: Becoming the 'no FAL' ARCH ARC2: Becoming the 'no SRL' ARCH ARC1: Becoming the heartbeat ARCH Errors in file /u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_15053.trc: ORA-01173: data dictionary indicates missing data file from system tablespace Errors in file /u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_15053.trc: ORA-01173: data dictionary indicates missing data file from system tablespace Error 1173 happened during db open, shutting down database USER (ospid: 15053): terminating the instance due to error 1173 Instance terminated by USER, pid = 15053 ORA-1092 signalled during: alter database open resetlogs... opiodr aborting process unknown ospid (15053) as a result of ORA-1092 Mon Aug 26 15:22:29 2013 ORA-1092 : opitsk aborting process
解决方案
解决方案设置相关异常回滚段,然后屏蔽掉，数据库即可正常起来
--参数文件修改(使用event或者工具，系统命令等) undo_management='manual' _corrupted_rollback_segments --重启数据库使得_corrupted_rollback_segments生效 shutdown immediate; startup --尝试open数据库 recover database; alter database open; --新建undo create undo tablespace undo_new datafile '' size 100m autoextend on next 10m maxsize 30G; --删除老undo drop tablespace old_undo --修改参数 shutdonw immediate undo_management='auto' undo_tablespace='unod_new' --启动数据库 startup --逻辑导出数据，导入新库