一、实验说明：

     本文转载于Luocs的丢失控制文件恢复实验记录--3，此处属于转载+模拟。

     操作系统：rhel 5.4 x32

     数据库：oracle 11g r2

二、实验操作： 

----先清除历史测试数据然后再产生数据：----   1 SQL> drop user luocs cascade;  2   3 User dropped.  4   5 SQL> drop tablespace luocs including contents;  6   7 Tablespace dropped.  8   9 RMAN> delete noprompt backup; 10  11 using target database control file instead of recovery catalog 12 allocated channel: ORA_DISK_1 13 channel ORA_DISK_1: SID=31 device type=DISK 14 specification does not match any backup in the repository 15  16 RMAN> delete noprompt copy; 17  18 released channel: ORA_DISK_1 19 allocated channel: ORA_DISK_1 20 channel ORA_DISK_1: SID=31 device type=DISK 21 specification does not match any datafile copy in the repository 22 specification does not match any control file copy in the repository 23 List of Archived Log Copies for database with db_unique_name YFT 24 ===================================================================== 25  26 Key     Thrd Seq     S Low Time  27 ------- ---- ------- - --------- 28 3       1    6       A 11-JAN-13 29         Name: /u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_11/o1_mf_1_6_8gzbgpp4_.arc 30  31 1       1    7       A 11-JAN-13 32         Name: /u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_11/o1_mf_1_7_8gzbgplr_.arc 33  34 2       1    8       A 11-JAN-13 35         Name: /u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_11/o1_mf_1_8_8gzbgpn0_.arc 36  37 4       1    1       A 11-JAN-13 38         Name: /u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_11/o1_mf_1_1_8gzbvydb_.arc 39  40 5       1    2       A 11-JAN-13 41         Name: /u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_11/o1_mf_1_2_8gzcfv2l_.arc 42  43 deleted archived log 44 archived log file name=/u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_11/o1_mf_1_6_8gzbgpp4_.arc RECID=3 STAMP=804434262 45 deleted archived log 46 archived log file name=/u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_11/o1_mf_1_7_8gzbgplr_.arc RECID=1 STAMP=804434262 47 deleted archived log 48 archived log file name=/u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_11/o1_mf_1_8_8gzbgpn0_.arc RECID=2 STAMP=804434262 49 deleted archived log 50 archived log file name=/u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_11/o1_mf_1_1_8gzbvydb_.arc RECID=4 STAMP=804434686 51 deleted archived log 52 archived log file name=/u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_11/o1_mf_1_2_8gzcfv2l_.arc RECID=5 STAMP=804435259 53 Deleted 5 objects 54   ----备份当前控制文件----  55 RMAN> backup current controlfile; 56  57 Starting backup at 11-JAN-13 58 using channel ORA_DISK_1 59 channel ORA_DISK_1: starting full datafile backup set 60 channel ORA_DISK_1: specifying datafile(s) in backup set 61 including current control file in backup set 62 channel ORA_DISK_1: starting piece 1 at 11-JAN-13 63 channel ORA_DISK_1: finished piece 1 at 11-JAN-13 64 piece handle=/u01/app/oracle/flash_recovery_area/YFT/backupset/2013_01_11/o1_mf_ncnnf_TAG20130111T181929_8gzsslmr_.bkp tag=TAG20130111T181929 comment=NONE 65 channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03 66 Finished backup at 11-JAN-13 67   ----重新产生数据----  68 SQL> create tablespace luocs datafile '/u01/app/oracle/oradata/yft/luocs01.dbf' size 50m; 69  70 Tablespace created. 71  72 SQL> create user luocs identified by oracle default tablespace luocs; 73  74 User created. 75  76 SQL> grant resource,connect to luocs; 77  78 Grant succeeded. 79  80 SQL> create table luocs.t1 as select * from dba_objects where rownum<10000; 81  82 Table created. 83  84 SQL> alter system switch logfile; 85  86 System altered. 87  88 SQL> select count(*) from test.t1; 89  90   COUNT(*) 91 ---------- 92      63451 93  94 SQL> alter system switch logfile; 95  96 System altered. 97  98 SQL> alter system switch logfile; 99 100 System altered.101 102 SQL> insert into luocs.t1 select * from luocs.t1;103 104 9999 rows created.105 106 SQL> commit;107 108 Commit complete.109 110 SQL> alter system switch logfile;111 112 System altered.113 114 SQL> alter system switch logfile;115 116 System altered.117 118 SQL> delete from test.t1 where rownum<20000;119 120 19999 rows deleted.121 122 SQL> delete from luocs.t1 where rownum<5000;123 124 4999 rows deleted.125 126 SQL> select count(*) from test.t1;127 128   COUNT(*)129 ----------130      43452131 132 SQL> select count(*) from luocs.t1;133 134   COUNT(*)135 ----------136      14999137 138 SQL> commit;139 140 Commit complete.141 142 SQL> alter system switch logfile;143 144 System altered.145 146 SQL> alter system switch logfile;147 148 System altered.149   ----因为之前有过resetlogs的操作，所以使用select max(sequence#) from v$archived_logs查询有误---- 150 SQL> archive log list;151 Database log mode           Archive Mode152 Automatic archival           Enabled153 Archive destination           USE_DB_RECOVERY_FILE_DEST154 Oldest online log sequence     6155 Next log sequence to archive   8156 Current log sequence           8  ----模拟丢失控制文件，然后扩展连归档日志也丢失 157 SQL> shutdown abort;158 ORACLE instance shut down.159  ----删除控制文件---- 160 [oracle@yft ~]$ rm /u01/app/oracle/oradata/yft/control01.ctl  ----把全部归档文件全部当做丢失---- 161 [oracle@yft bak]$ mv /u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_11/* /tmp/bak162  ----数据库启动报错---- 163 SQL> startup164 ORACLE instance started.165 166 Total System Global Area  330600448 bytes167 Fixed Size            1336344 bytes168 Variable Size          243272680 bytes169 Database Buffers       79691776 bytes170 Redo Buffers            6299648 bytes171 ORA-00205: error in identifying control file, check alert log for more info172  ----这个时候已经到nomount状态---- 173 RMAN> restore controlfile from '/u01/app/oracle/flash_recovery_area/YFT/backupset/2013_01_11/o1_mf_ncnnf_TAG20130111T181929_8gzsslmr_.bkp';174 175 Starting restore at 11-JAN-13176 using target database control file instead of recovery catalog177 allocated channel: ORA_DISK_1178 channel ORA_DISK_1: SID=20 device type=DISK179 180 channel ORA_DISK_1: restoring control file181 channel ORA_DISK_1: restore complete, elapsed time: 00:00:03182 output file name=/u01/app/oracle/oradata/yft/control01.ctl183 output file name=/u01/app/oracle/flash_recovery_area/yft/control02.ctl184 Finished restore at 11-JAN-13185 186 RMAN> alter database mount;187 188 database mounted189 released channel: ORA_DISK_1190  ----在这里考虑到归档日志文件丢失使用recover database会失败，而且备份的控制文件不是最新的，索性创建一个新的控制文件---- 191 SQL> alter database backup controlfile to trace;192 193 Database altered.194  ----导出查找刚才的trace文件路径---- 195 SQL> select value from v$diag_info where NAME = 'Default Trace File';196 197 VALUE198 --------------------------------------------------------------------------------199 /u01/app/oracle/diag/rdbms/yft/yft/trace/yft_ora_3964.trc200 201 [oracle@yft bak]$ sed -n '/CREATE CONTROLFILE.*NORESETLOGS/,/;/p' /u01/app/oracle/diag/rdbms/yft/yft/trace/yft_ora_3964.trc202 CREATE CONTROLFILE REUSE DATABASE "YFT" NORESETLOGS  ARCHIVELOG203     MAXLOGFILES 16204     MAXLOGMEMBERS 3205     MAXDATAFILES 100206     MAXINSTANCES 8207     MAXLOGHISTORY 292208 LOGFILE209   GROUP 1 '/u01/app/oracle/oradata/yft/redo01.log'  SIZE 50M BLOCKSIZE 512,210   GROUP 2 '/u01/app/oracle/oradata/yft/redo02.log'  SIZE 50M BLOCKSIZE 512,211   GROUP 3 '/u01/app/oracle/oradata/yft/redo03.log'  SIZE 50M BLOCKSIZE 512212 -- STANDBY LOGFILE213 DATAFILE214   '/u01/app/oracle/oradata/yft/system01.dbf',215   '/u01/app/oracle/oradata/yft/sysaux01.dbf',216   '/u01/app/oracle/oradata/yft/undotbs01.dbf',217   '/u01/app/oracle/oradata/yft/users01.dbf',218   '/u01/app/oracle/oradata/yft/example01.dbf',219   '/u01/app/oracle/oradata/yft/jack01.dbf'220 CHARACTER SET AL32UTF8221 ;222   ----查看当前redo log的状态，这个是从旧的控制文件中读出来的---- 223 SQL> col member for a45;224 SQL> col status for a15;225 SQL> select b.sequence#, a.member, b.status from v$logfile a, v$log b where a.group#=b.group#;226 227  SEQUENCE# MEMBER                     STATUS228 ---------- --------------------------------------------- ---------------229      1 /u01/app/oracle/oradata/yft/redo01.log     CURRENT230      0 /u01/app/oracle/oradata/yft/redo02.log     UNUSED231      0 /u01/app/oracle/oradata/yft/redo03.log     UNUSED232   ----创建控制文件需要启动到nomount状态---- 233 SQL> startup force nomount;234 ORACLE instance started.235 236 Total System Global Area  330600448 bytes237 Fixed Size            1336344 bytes238 Variable Size          243272680 bytes239 Database Buffers       79691776 bytes240 Redo Buffers            6299648 bytes241   ----重建新的控制文件---- 242 SQL>CREATE CONTROLFILE REUSE DATABASE "YFT" NORESETLOGS  ARCHIVELOG243     MAXLOGFILES 16244     MAXLOGMEMBERS 3245     MAXDATAFILES 100246     MAXINSTANCES 8247     MAXLOGHISTORY 292248 LOGFILE249   GROUP 1 '/u01/app/oracle/oradata/yft/redo01.log'  SIZE 50M BLOCKSIZE 512,250   GROUP 2 '/u01/app/oracle/oradata/yft/redo02.log'  SIZE 50M BLOCKSIZE 512,251   GROUP 3 '/u01/app/oracle/oradata/yft/redo03.log'  SIZE 50M BLOCKSIZE 512252 DATAFILE253   '/u01/app/oracle/oradata/yft/system01.dbf',254   '/u01/app/oracle/oradata/yft/sysaux01.dbf',255   '/u01/app/oracle/oradata/yft/undotbs01.dbf',256   '/u01/app/oracle/oradata/yft/users01.dbf',257   '/u01/app/oracle/oradata/yft/example01.dbf',258   '/u01/app/oracle/oradata/yft/jack01.dbf'259 CHARACTER SET AL32UTF8260 19  ;261 262 Control file created.263   ----现在redo的sequence#才是我们模拟故障前的---- 264 SQL> select b.sequence#, a.member, b.status from v$logfile a, v$log b where a.group#=b.group#;265 266  SEQUENCE# MEMBER                     STATUS267 ---------- --------------------------------------------- ---------------268      7 /u01/app/oracle/oradata/yft/redo01.log     INACTIVE269      8 /u01/app/oracle/oradata/yft/redo02.log     CURRENT270      6 /u01/app/oracle/oradata/yft/redo03.log     INACTIVE271  ----数据文件都需要恢复---- 272 SQL> select file#, name, status from v$datafile;273 274      FILE# NAME                      STATUS275 -------- --------------------------------------------- ---------------276      1 /u01/app/oracle/oradata/yft/system01.dbf      SYSTEM277      2 /u01/app/oracle/oradata/yft/sysaux01.dbf      RECOVER278      3 /u01/app/oracle/oradata/yft/undotbs01.dbf     RECOVER279      4 /u01/app/oracle/oradata/yft/users01.dbf       RECOVER280      5 /u01/app/oracle/oradata/yft/example01.dbf     RECOVER281      6 /u01/app/oracle/oradata/yft/jack01.dbf        RECOVER282 283 6 rows selected.284  ----基于控制文件恢复一下，在这里需要的归档日志正好是上面的redo日志---- 285 SQL> recover database using backup controlfile;286 ORA-00279: change 854294 generated at 01/11/2013 18:23:14 needed for thread 1287 ORA-00289: suggestion :288 /u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_11/o1_mf_1_6_%u_.arc289 ORA-00280: change 854294 for thread 1 is in sequence #6290 291 292 Specify log: {
     
      =suggested | filename | AUTO | CANCEL}293 /u01/app/oracle/oradata/yft/redo03.log294 ORA-00279: change 854684 generated at 01/11/2013 18:24:17 needed for thread 1295 ORA-00289: suggestion :296 /u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_11/o1_mf_1_7_%u_.arc297 ORA-00280: change 854684 for thread 1 is in sequence #7298 ORA-00278: log file '/u01/app/oracle/oradata/yft/redo03.log' no longer needed299 for this recovery300 301 302 Specify log: {
      
       =suggested | filename | AUTO | CANCEL}303 /u01/app/oracle/oradata/yft/redo01.log304 ORA-00279: change 854688 generated at 01/11/2013 18:24:21 needed for thread 1305 ORA-00289: suggestion :306 /u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_11/o1_mf_1_8_%u_.arc307 ORA-00280: change 854688 for thread 1 is in sequence #8308 ORA-00278: log file '/u01/app/oracle/oradata/yft/redo01.log' no longer needed309 for this recovery310 311 312 Specify log: {
       
        =suggested | filename | AUTO | CANCEL}313 /u01/app/oracle/oradata/yft/redo02.log314 Log applied.315 Media recovery complete.  ----现在数据文件都已经online了，但是还缺少我们创建的luocs01.dbf数据文件---- 316 SQL> select file#, name, status from v$datafile;317 318      FILE# NAME                                      STATUS319 ---------- ---------------------------------------- ---------------320      1 /u01/app/oracle/oradata/yft/system01.dbf      SYSTEM321      2 /u01/app/oracle/oradata/yft/sysaux01.dbf      ONLINE322      3 /u01/app/oracle/oradata/yft/undotbs01.dbf     ONLINE323      4 /u01/app/oracle/oradata/yft/users01.dbf       ONLINE324      5 /u01/app/oracle/oradata/yft/example01.dbf     ONLINE325      6 /u01/app/oracle/oradata/yft/jack01.dbf        ONLINE326 327 6 rows selected.  ----先resetlogs打开数据库---- 328 SQL> alter database open resetlogs;329 330 Database altered.331   ----这时候因为应用了当前的日志，而且resetlogs打开数据库，所以再$ORACLE_HOME/dbs目录下生成了一个数据文件，但这个却用不了---- 332 SQL> select file#, name, status from v$datafile;333 334      FILE# NAME                                                STATUS335 ---------- ----------------------------------------------- ---------------336      1 /u01/app/oracle/oradata/yft/system01.dbf                SYSTEM337      2 /u01/app/oracle/oradata/yft/sysaux01.dbf                ONLINE338      3 /u01/app/oracle/oradata/yft/undotbs01.dbf               ONLINE339      4 /u01/app/oracle/oradata/yft/users01.dbf                 ONLINE340      5 /u01/app/oracle/oradata/yft/example01.dbf               ONLINE341      6 /u01/app/oracle/oradata/yft/jack01.dbf                  ONLINE342      7 /u01/app/oracle/product/11.2.0/db_1/dbs/MISSING00007    RECOVER343 344 7 rows selected.345  ----先将7号数据文件offline---- 346 SQL> alter database datafile 7 offline;347 348 Database altered.349  ----将新生成的数据文件重名一下---- 351SQL> alter database rename file '/u01/app/oracle/product/11.2.0/db_1/dbs/MISSING00007' to '/u01/app/oracle/oradata/yft/luocs01.dbf';
       
      
     

----现在将luocs置成online的还是报错，还是需要一些别的操作---- 352 SQL> alter tablespace luocs online;353 alter tablespace luocs online354 *355 ERROR at line 1:356 ORA-01190: control file or data file 7 is from before the last RESETLOGS357 ORA-01110: data file 7: '/u01/app/oracle/oradata/yft/luocs01.dbf'358 359  ----首先需要设置一下_allow_resetlogs_corruption参数---- 360 SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;361 362 System altered.363 364 SQL> shutdown immediate;365 Database closed.366 Database dismounted.367 ORACLE instance shut down.368 SQL> startup369 ORACLE instance started.370 371 Total System Global Area  330600448 bytes372 Fixed Size            1336344 bytes373 Variable Size          243272680 bytes374 Database Buffers       79691776 bytes375 Redo Buffers            6299648 bytes376 Database mounted.377 Database opened. ----由于v$datafile中checkpoint_change#仍然大于v$recover_file中的change#，决定用adjust_scn来调整scn---- 378 SQL> alter session set events 'immediate trace name adjust_scn level 1';379 380 Session altered.381 382 SQL> shutdown immediate;383 Database closed.384 Database dismounted.385 ORACLE instance shut down.386 SQL> startup mount;387 ORACLE instance started.388 389 Total System Global Area  330600448 bytes390 Fixed Size            1336344 bytes391 Variable Size          243272680 bytes392 Database Buffers       79691776 bytes393 Redo Buffers            6299648 bytes394 Database mounted.395 SQL> select ts#,file#,name,status,checkpoint_change# from v$datafile;396 397     TS#    FILE#   NAME                                              STATUS       CHECKPOINT_CHANGE#398 -------- -------- --------------------------------------------- ---------------    ----------------399      0        1 /u01/app/oracle/oradata/yft/system01.dbf             SYSTEM               855383400      1        2 /u01/app/oracle/oradata/yft/sysaux01.dbf             ONLINE               855383401      2        3 /u01/app/oracle/oradata/yft/undotbs01.dbf            ONLINE               855383402      4        4 /u01/app/oracle/oradata/yft/users01.dbf              ONLINE               855383403      6        5 /u01/app/oracle/oradata/yft/example01.dbf            ONLINE               855383404      7        6 /u01/app/oracle/oradata/yft/jack01.dbf               ONLINE               855383405      8        7 /u01/app/oracle/oradata/yft/luocs01.dbf              RECOVER                0406 407 7 rows selected.408   ----这一步很重要，虽然不做这个操作也能打开数据库，但是我们要用resetlogs来打开数据库，否则仍然将其他数据文件联机的时候仍然会报ORA-01189。 409 SQL> recover until cancel;410 Media recovery complete.411   ----将数据文件置于online---- 412 SQL> alter database datafile 2,3,4,5,6,7 online;413 414 Database altered.415 416 SQL> alter database open resetlogs;417 418 Database altered.419   ----打开数据库以后查看v$datafile，文件都已经是online，而且checkpoint_change#都变成一样的---- 420 SQL> select ts#,file#,name,status,checkpoint_change# from v$datafile;421 422     TS#    FILE# NAME                                                STATUS      CHECKPOINT_CHANGE#423 -------- ------- ---------------------------------------------- ---------------  ------------------424      0        1 /u01/app/oracle/oradata/yft/system01.dbf             SYSTEM               855387425      1        2 /u01/app/oracle/oradata/yft/sysaux01.dbf             ONLINE               855387426      2        3 /u01/app/oracle/oradata/yft/undotbs01.dbf            ONLINE               855387427      4        4 /u01/app/oracle/oradata/yft/users01.dbf              ONLINE               855387428      6        5 /u01/app/oracle/oradata/yft/example01.dbf            ONLINE               855387429      7        6 /u01/app/oracle/oradata/yft/jack01.dbf               ONLINE               855387430      8        7 /u01/app/oracle/oradata/yft/luocs01.dbf              ONLINE               855387431 432 7 rows selected.433   ----查看一下数据有没有丢失---- 434 SQL> select count(*) from luocs.t1;435 436   COUNT(*)437 ----------438      18968439 440 SQL> select count(*) from test.t1;441 442   COUNT(*)443 ----------444      43452 至此，我们得知：在线日志没有损坏，控制文件丢失，归档日志丢失，利用备份旧的控制文件恢复controlfile，再重建新的控制文件，无法恢复所有数据。最后还需要添加临时表空间的数据文件 ，[oracle@yft ~]$ rm /u01/app/oracle/oradata/yft/temp01.dbf SQL>alter tablespace temp add tempfile '/u01/app/oracle/oradata/yft/temp01.dbf' size 200m;