mysql undo丢失_重建控制文件丢失undo异常恢复—ORA-600 25025模拟与恢复

在现实的情况中，有些人因为某种原因重建控制文件(丢失undo[有意或者无意])，然后又resetlogs库尝试恢复,这样的操作可能导致各种比较麻烦的恢复,这里模拟ORA-600[25025]异常恢复

模拟ORA-600[25025]错误

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> alter database backup controlfile to trace as '/tmp/ctl';

Database altered.

SQL> create table chf.t_xifenfei_www as select * from dba_objects where 1=0;

Table created.

SQL> insert into chf.t_xifenfei_www select * from dba_objects;

74749 rows created.

--另外一个会话abort

SQL> shutdown abort;

ORACLE instance shut down.

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> !vi /tmp/ctl.sql

CREATE CONTROLFILE REUSE DATABASE "ORA11G" NORESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 '/u01/oracle/oradata/ora11g/redo01.log' SIZE 50M BLOCKSIZE 512,

GROUP 2 '/u01/oracle/oradata/ora11g/redo02.log' SIZE 50M BLOCKSIZE 512,

GROUP 3 '/u01/oracle/oradata/ora11g/redo03.log' SIZE 50M BLOCKSIZE 512

DATAFILE

'/u01/oracle/oradata/ora11g/system01.dbf',

'/u01/oracle/oradata/ora11g/sysaux01.dbf',

-- '/u01/oracle/oradata/ora11g/undo02.dbf',

'/u01/oracle/oradata/ora11g/users01.dbf',

'/u01/oracle/oradata/ora11g/dbfs01.dbf',

'/u01/oracle/oradata/ora11g/tts_xifenfei02.dbf',

'/u01/oracle/oradata/ora11g/tts_xifenfei01.dbf',

'/u01/oracle/oradata/ora11g/system02.dbf',

'/u01/oracle/oradata/ora11g/czum01.dbf',

'/u01/oracle/oradata/ora11g/undotbs02.dbf',

'/u01/oracle/oradata/sp2008',

'/u01/oracle/oradata/sp_2009',

'/u01/oracle/oradata/sp_2010',

'/u01/oracle/oradata/sp_2011',

'/u01/oracle/oradata/sp_2012',

'/u01/oracle/oradata/sp_2013',

'/u01/oracle/oradata/sp_2014',

'/u01/oracle/oradata/sp_2015',

'/u01/oracle/oradata/sp_2016',

'/u01/oracle/oradata/sp_2017',

'/u01/oracle/oradata/sp_2018',

'/u01/oracle/oradata/sp_2019',

'/u01/oracle/oradata/sp_2020',

'/u01/oracle/oradata/sp_2021',

'/u01/oracle/oradata/sp_2022',

'/u01/oracle/oradata/sp_2023',

'/u01/oracle/oradata/sp_2024',

'/u01/oracle/oradata/sp_2025',

'/u0