丢失控制文件恢复实验记录--4(在线日志文件没有损坏，归档日志丢失，直接重建控制文件(跟踪控制文件trace是旧的情况))...-白红宇

丢失控制文件恢复实验记录--4(在线日志文件没有损坏，归档日志丢失，直接重建控制文件(跟踪控制文件trace是旧的情况))...
阅读量：4325 次
发布时间：2019-06-06
本文共 15456 字，大约阅读时间需要 51 分钟。
一、实验说明：
本文转载于Luocs的丢失控制文件恢复实验记录--4，此处属于转载+模拟。该实验其实跟类似，就是少了利用备份的旧控制文件恢复而已。
操作系统：rhel 5.4 x32
数据库：oracle 11g r2
二、实验操作：
----先导出一个跟踪文件trace----   1 SQL> alter database backup controlfile to trace;  2   3 Database altered.  4   5 SQL> select value from v$diag_info where NAME = 'Default Trace File';  6   7 VALUE  8 --------------------------------------------------------------------------------  9 /u01/app/oracle/diag/rdbms/yft/yft/trace/yft_ora_3844.trc 10   ----查看trace内容----  11 [oracle@yft ~]$ sed -n '/CREATE CONTROLFILE.*NORESETLOGS/,/;/p' /u01/app/oracle/diag/rdbms/yft/yft/trace/yft_ora_3844.trc 12 CREATE CONTROLFILE REUSE DATABASE "YFT" NORESETLOGS  ARCHIVELOG 13     MAXLOGFILES 16 14     MAXLOGMEMBERS 3 15     MAXDATAFILES 100 16     MAXINSTANCES 8 17     MAXLOGHISTORY 292 18 LOGFILE 19   GROUP 1 '/u01/app/oracle/oradata/yft/redo01.log'  SIZE 50M BLOCKSIZE 512, 20   GROUP 2 '/u01/app/oracle/oradata/yft/redo02.log'  SIZE 50M BLOCKSIZE 512, 21   GROUP 3 '/u01/app/oracle/oradata/yft/redo03.log'  SIZE 50M BLOCKSIZE 512 22 -- STANDBY LOGFILE 23 DATAFILE 24   '/u01/app/oracle/oradata/yft/system01.dbf', 25   '/u01/app/oracle/oradata/yft/sysaux01.dbf', 26   '/u01/app/oracle/oradata/yft/undotbs01.dbf', 27   '/u01/app/oracle/oradata/yft/users01.dbf', 28   '/u01/app/oracle/oradata/yft/example01.dbf', 29   '/u01/app/oracle/oradata/yft/jack01.dbf' 30 CHARACTER SET AL32UTF8 31 ; 32   ----产生一些数据----  33 SQL> create tablespace luocs datafile '/u01/app/oracle/oradata/yft/luocs01.dbf' size 50m; 34  35 Tablespace created. 36  37 SQL> create user luocs identified by oracle default tablespace luocs; 38  39 User created. 40  41 SQL> grant resource,connect to luocs; 42  43 Grant succeeded. 44  45 SQL> create table luocs.t1 as select * from dba_objects where rownum<10000; 46  47 Table created. 48  49 SQL> alter system switch logfile; 50  51 System altered. 52  53 SQL> alter system switch logfile; 54  55 System altered. 56  57 SQL> alter system switch logfile; 58  59 System altered. 60  61 SQL> alter system switch logfile; 62  63 System altered. 64  65 SQL> alter system switch logfile; 66  67 System altered. 68  69 SQL> alter system switch logfile; 70  71 System altered. 72  73 SQL> alter system switch logfile; 74  75 System altered. 76  77 SQL> select max(sequence#) from v$archived_log; 78  79 MAX(SEQUENCE#) 80 -------------- 81          8 82  83 SQL> alter system switch logfile; 84  85 System altered. 86  87 SQL> select max(sequence#) from v$archived_log; 88  89 MAX(SEQUENCE#) 90 -------------- 91          9 92  93 SQL> insert into luocs.t1 select * from luocs.t1; 94  95 9999 rows created. 96  97 SQL> commit; 98  99 Commit complete.100 101 SQL> select count(*) from test.t1;102 103   COUNT(*)104 ----------105      43452106 107 SQL> insert into test.t1 select * from test.t1 where rownum<10000;108 109 9999 rows created.110 111 SQL> alter system switch logfile;112 113 System altered.114 115 SQL> select count(*) from test.t1;116 117   COUNT(*)118 ----------119      53451120 121 SQL> select count(*) from luocs.t1;122 123   COUNT(*)124 ----------125      19998126 127 SQL> select max(sequence#) from v$archived_log;128 129 MAX(SEQUENCE#)130 --------------131         10132 133 SQL> insert into luocs.t1 select * from luocs.t1 where rownum<10000;134 135 9999 rows created.136 137 SQL> insert into test.t1 select * from test.t1 where rownum<10000;138 139 9999 rows created.140 141 SQL> commit;142 143 Commit complete.144 145 SQL> alter system switch logfile;146 147 System altered.148 149 SQL> select max(sequence#) from v$archived_log;150 151 MAX(SEQUENCE#)152 --------------153         11154 155 SQL> select count(*) from luocs.t1;156 157   COUNT(*)158 ----------159      29997160 161 SQL> select count(*) from test.t1;162 163   COUNT(*)164 ----------165      63450166  ----模拟丢失控制文件，丢失归档文件---- 167 SQL> shutdown abort;168 ORACLE instance shut down.169 170 [oracle@yft ~]$ mv /u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_12/* /tmp/bak/171 [oracle@yft ~]$ ll /tmp/bak172 total 6572173 -rw-r----- 1 oracle oinstall 2340864 Jan 12 08:44 o1_mf_1_10_8h1dgmdr_.arc174 -rw-r----- 1 oracle oinstall 2326528 Jan 12 08:45 o1_mf_1_11_8h1djno1_.arc175 -rw-r----- 1 oracle oinstall 2017280 Jan 12 08:42 o1_mf_1_2_8h1dcff7_.arc176 -rw-r----- 1 oracle oinstall    1024 Jan 12 08:42 o1_mf_1_3_8h1dckqv_.arc177 -rw-r----- 1 oracle oinstall    2560 Jan 12 08:42 o1_mf_1_4_8h1dcopp_.arc178 -rw-r----- 1 oracle oinstall    1024 Jan 12 08:42 o1_mf_1_5_8h1dcpm6_.arc179 -rw-r----- 1 oracle oinstall    1024 Jan 12 08:42 o1_mf_1_6_8h1dcqxs_.arc180 -rw-r----- 1 oracle oinstall    1024 Jan 12 08:42 o1_mf_1_7_8h1dcrt3_.arc181 -rw-r----- 1 oracle oinstall    1024 Jan 12 08:42 o1_mf_1_8_8h1dcsq9_.arc182 -rw-r----- 1 oracle oinstall    1024 Jan 12 08:42 o1_mf_1_9_8h1dd31j_.arc183  ----启动报错---- 184 SQL> startup185 ORACLE instance started.186 187 Total System Global Area  330600448 bytes188 Fixed Size            1336344 bytes189 Variable Size          247466984 bytes190 Database Buffers       75497472 bytes191 Redo Buffers            6299648 bytes192 ORA-00205: error in identifying control file, check alert log for more info193 194  ----直接重建控制文件---- 195 SQL>CREATE CONTROLFILE REUSE DATABASE "YFT" NORESETLOGS  ARCHIVELOG196     MAXLOGFILES 16197     MAXLOGMEMBERS 3198     MAXDATAFILES 100199     MAXINSTANCES 8200     MAXLOGHISTORY 292201 LOGFILE202   GROUP 1 '/u01/app/oracle/oradata/yft/redo01.log'  SIZE 50M BLOCKSIZE 512,203   GROUP 2 '/u01/app/oracle/oradata/yft/redo02.log'  SIZE 50M BLOCKSIZE 512,204  10    GROUP 3 '/u01/app/oracle/oradata/yft/redo03.log'  SIZE 50M BLOCKSIZE 512205   '/u01/app/oracle/oradata/yft/system01.dbf',206   '/u01/app/oracle/oradata/yft/sysaux01.dbf',207   '/u01/app/oracle/oradata/yft/undotbs01.dbf',208   '/u01/app/oracle/oradata/yft/users01.dbf',209   '/u01/app/oracle/oradata/yft/example01.dbf',210   '/u01/app/oracle/oradata/yft/jack01.dbf'211 CHARACTER SET AL32UTF8212 19  ;213 214 Control file created.215  ----因为拿的是旧的控制文件来创建的，所以看不到后来添加的数据文件信息---- 216 SQL> col name for a45;217 SQL> select file#,name,status from v$datafile;218 219      FILE# NAME                                      STATUS220 ---------- ----------------------------------------- -------221      1 /u01/app/oracle/oradata/yft/system01.dbf      SYSTEM222      2 /u01/app/oracle/oradata/yft/sysaux01.dbf      RECOVER223      3 /u01/app/oracle/oradata/yft/undotbs01.dbf     RECOVER224      4 /u01/app/oracle/oradata/yft/users01.dbf       RECOVER225      5 /u01/app/oracle/oradata/yft/example01.dbf     RECOVER226      6 /u01/app/oracle/oradata/yft/jack01.dbf        RECOVER227 228 6 rows selected.229  ----恢复数据库，报错找不到10号日志文件---- 230 RMAN> recover database;231 232 Starting recover at 12-JAN-13233 using target database control file instead of recovery catalog234 allocated channel: ORA_DISK_1235 channel ORA_DISK_1: SID=22 device type=DISK236 237 starting media recovery238 239 unable to find archived log240 archived log thread=1 sequence=10241 RMAN-00571: ===========================================================242 RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============243 RMAN-00571: ===========================================================244 RMAN-03002: failure of recover command at 01/12/2013 08:48:40245 RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 10 and starting SCN of 879342246 247 SQL> col member for a45;248 SQL> col status for a15;249 SQL> select b.sequence#, a.member, b.status from v$logfile a, v$log b where a.group#=b.group#;250  ----查看当前日志文件信息：---- 251  SEQUENCE# MEMBER                                 STATUS252 ---------- -------------------------------------- ---------------253     10 /u01/app/oracle/oradata/yft/redo01.log     INACTIVE254     11 /u01/app/oracle/oradata/yft/redo02.log     INACTIVE255     12 /u01/app/oracle/oradata/yft/redo03.log     CURRENT256  ----发现上面正好有需要的10号文件，执行基于控制文件恢复一下---- 257 SQL> recover database using backup controlfile;258 ORA-00279: change 879342 generated at 01/12/2013 08:42:42 needed for thread 1259 ORA-00289: suggestion :260 /u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_12/o1_mf_1_10_%u_.arc261 ORA-00280: change 879342 for thread 1 is in sequence #10262 263 264 Specify log: {
     
      =suggested | filename | AUTO | CANCEL}265 /u01/app/oracle/oradata/yft/redo01.log266 ORA-00279: change 879375 generated at 01/12/2013 08:44:03 needed for thread 1267 ORA-00289: suggestion :268 /u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_12/o1_mf_1_11_%u_.arc269 ORA-00280: change 879375 for thread 1 is in sequence #11270 ORA-00278: log file '/u01/app/oracle/oradata/yft/redo01.log' no longer needed271 for this recovery272 273 274 Specify log: {
      
       =suggested | filename | AUTO | CANCEL}275 /u01/app/oracle/oradata/yft/redo02.log276 ORA-00279: change 879451 generated at 01/12/2013 08:45:08 needed for thread 1277 ORA-00289: suggestion :278 /u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_12/o1_mf_1_12_%u_.arc279 ORA-00280: change 879451 for thread 1 is in sequence #12280 ORA-00278: log file '/u01/app/oracle/oradata/yft/redo02.log' no longer needed281 for this recovery282 283 284 Specify log: {
       
        =suggested | filename | AUTO | CANCEL}285 /u01/app/oracle/oradata/yft/redo03.log286 Log applied.287 Media recovery complete. ----再查看数据文件的状态，在这里依然看不到我们后创建的数据文件---- 288 SQL> select file#,name,status from v$datafile;289 290      FILE# NAME                                      STATUS291 ---------- ---------------------------------------- ---------------292      1 /u01/app/oracle/oradata/yft/system01.dbf      SYSTEM293      2 /u01/app/oracle/oradata/yft/sysaux01.dbf      ONLINE294      3 /u01/app/oracle/oradata/yft/undotbs01.dbf     ONLINE295      4 /u01/app/oracle/oradata/yft/users01.dbf       ONLINE296      5 /u01/app/oracle/oradata/yft/example01.dbf     ONLINE297      6 /u01/app/oracle/oradata/yft/jack01.dbf        ONLINE298 299 6 rows selected.300  ----启动数据库---- 301 SQL> alter database open;302 alter database open303 *304 ERROR at line 1:305 ORA-01589: must use RESETLOGS or NORESETLOGS option for database open306 307 308 SQL> alter database open resetlogs;309 310 Database altered.311  ----查看数据文件信息，再$ORACLE_HOME/dbs目录下生成了一个数据文件，但这个却用不了---- 312 SQL> col name for a60;313 SQL> select file#,name,status from v$datafile;314 315      FILE# NAME                                                     STATUS316 ---------- -------------------------------------------------------- ---------------317      1 /u01/app/oracle/oradata/yft/system01.dbf                     SYSTEM318      2 /u01/app/oracle/oradata/yft/sysaux01.dbf                     ONLINE319      3 /u01/app/oracle/oradata/yft/undotbs01.dbf                    ONLINE320      4 /u01/app/oracle/oradata/yft/users01.dbf                      ONLINE321      5 /u01/app/oracle/oradata/yft/example01.dbf                    ONLINE322      6 /u01/app/oracle/oradata/yft/jack01.dbf                       ONLINE323      7 /u01/app/oracle/product/11.2.0/db_1/dbs/MISSING00007         RECOVER324 325 7 rows selected.326  ----先将7号数据文件offline---- 327 SQL> alter database datafile 7 offline;328 329 Database altered.330  ----将新生成的数据文件重名一下---- 331 SQL> alter database rename file '/u01/app/oracle/product/11.2.0/db_1/dbs/MISSING00007' to '/u01/app/oracle/oradata/yft/luocs01.dbf';332 333 Database altered.334  ----首先需要设置一下_allow_resetlogs_corruption参数---- 335 SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;336 337 System altered.338 339 SQL> shutdown immediate;340 Database closed.341 Database dismounted.342 ORACLE instance shut down.343 SQL> startup344 ORACLE instance started.345 346 Total System Global Area  330600448 bytes347 Fixed Size            1336344 bytes348 Variable Size          247466984 bytes349 Database Buffers       75497472 bytes350 Redo Buffers            6299648 bytes351 Database mounted.352 Database opened.353  ----由于v$datafile中checkpoint_change#仍然大于v$recover_file中的change#，决定用adjust_scn来调整scn---- 354 SQL> alter session set events 'immediate trace name adjust_scn level 1';355 356 Session altered.357 358 SQL> shutdown immediate;359 Database closed.360 Database dismounted.361 ORACLE instance shut down.362 SQL> startup mount;363 ORACLE instance started.364 365 Total System Global Area  330600448 bytes366 Fixed Size            1336344 bytes367 Variable Size          247466984 bytes368 Database Buffers       75497472 bytes369 Redo Buffers            6299648 bytes370 Database mounted.371 372 SQL> select ts#,file#,name,status,checkpoint_change# from v$datafile;373 374        TS#    FILE# NAME                                              STATUS       CHECKPOINT_CHANGE#375 ---------- ---------- ------------------------------------------- --------------- ------------------376      0        1 /u01/app/oracle/oradata/yft/system01.dbf               SYSTEM               880126377      1        2 /u01/app/oracle/oradata/yft/sysaux01.dbf               ONLINE               880126378      2        3 /u01/app/oracle/oradata/yft/undotbs01.dbf              ONLINE               880126379      4        4 /u01/app/oracle/oradata/yft/users01.dbf                ONLINE               880126380      6        5 /u01/app/oracle/oradata/yft/example01.dbf              ONLINE               880126381      7        6 /u01/app/oracle/oradata/yft/jack01.dbf                 ONLINE               880126382      8        7 /u01/app/oracle/oradata/yft/luocs01.dbf                RECOVER                0383 384 7 rows selected.385  ----这一步很重要，虽然不做这个操作也能打开数据库，但是我们要用resetlogs来打开数据库，否则仍然将其他数据文件联机的时候仍然会报ORA-01189。 386 SQL> recover until cancel;387 Media recovery complete.  ----将数据文件置于online---- 388 SQL> alter database datafile 2,3,4,5,6,7 online;389 390 Database altered.391 392 SQL> alter database open resetlogs;393 394 Database altered.395  ----打开数据库以后查看v$datafile，文件都已经是online，而且checkpoint_change#都变成一样的---- 396 SQL> select ts#,file#,name,status,checkpoint_change# from v$datafile;397 398        TS#    FILE# NAME                                               STATUS       CHECKPOINT_CHANGE#399 ---------- ---------- ---------------------------------------------- --------------- ------------------400      0        1 /u01/app/oracle/oradata/yft/system01.dbf               SYSTEM               880130401      1        2 /u01/app/oracle/oradata/yft/sysaux01.dbf               ONLINE               880130402      2        3 /u01/app/oracle/oradata/yft/undotbs01.dbf              ONLINE               880130403      4        4 /u01/app/oracle/oradata/yft/users01.dbf                ONLINE               880130404      6        5 /u01/app/oracle/oradata/yft/example01.dbf              ONLINE               880130405      7        6 /u01/app/oracle/oradata/yft/jack01.dbf                 ONLINE               880130406      8        7 /u01/app/oracle/oradata/yft/luocs01.dbf                ONLINE               880130407 408 7 rows selected.409  ----查看一下数据有没有丢失---- 410 SQL> select count(*) from luocs.t1;411 412   COUNT(*)413 ----------414       9999415 416 SQL> select count(*) from test.t1;417 418   COUNT(*)419 ----------420      63450 至此，我们得知：在线日志没有损坏，控制文件丢失，归档日志丢失，利用旧的trace恢复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;
转载于:https://www.cnblogs.com/Richardzhu/articles/2857155.html
你可能感兴趣的文章