oracle恢复主键丢失,案例:Oracle重建控制文件丢失undo异常恢复 ORA-01173模拟与恢复...

天萃荷净

重建控制文件丢失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: {=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: {=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: {=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

--逻辑导出数据，导入新库

-----------------温馨提示--------------------

操作有风险，动手需谨慎

Oracle研究中心

http://www.oracleplus.net

本文由大师惜分飞原创分享，转载请尽量保留本站网址

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之案例:Oracle重建控制文件丢失undo异常恢复 ORA-01173模拟与恢复