丢失控制文件，有旧的备份控制文件，之后有drop表空间和create表空间的操作恢复。...

向右看齐 2021-12-17 16:59 180阅读 0赞

**一、实验说明：**

本实验是基于： [《丢失控制文件恢复实验记录--1(当前的控制文件损坏，使用旧控制文件进行恢复(旧控制文件之后DROP了表空间的情况))》 ][--1_DROP_]和 [《丢失控制文件恢复实验记录--2(当前的控制文件损坏，使用旧控制文件进行恢复(旧控制文件之后新增了表空间的情况))》][--2]，把这两个实验的情况综合以后做一个恢复实验。

操作系统：rhel 5.4 x32

数据库:oracle 11g r2

**二、操作内容：**

----首先做一个当前控制文件的备份----
      1 RMAN> backup current controlfile;
      2 
      3 Starting backup at 15-JAN-13
      4 using target database control file instead of recovery catalog
      5 allocated channel: ORA_DISK_1
      6 channel ORA_DISK_1: SID=31 device type=DISK
      7 channel ORA_DISK_1: starting full datafile backup set
      8 channel ORA_DISK_1: specifying datafile(s) in backup set
      9 including current control file in backup set
     10 channel ORA_DISK_1: starting piece 1 at 15-JAN-13
     11 channel ORA_DISK_1: finished piece 1 at 15-JAN-13
     12 piece handle=/u01/app/oracle/flash_recovery_area/YFT/backupset/2013_01_15/o1_mf_ncnnf_TAG20130115T172740_8hb88g4w_.bkp tag=TAG20130115T172740 comment=NONE
     13 channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
     14 Finished backup at 15-JAN-13
     15 
      ----产生一些数据，其中先创建表空间echo，再删除表空间jack，最后创建表空间jacks----
     16 SQL> create tablespace echo datafile '/u01/app/oracle/oradata/echo01.dbf' size 50m;
     17 
     18 Tablespace created.
     19 
     20 SQL> alter system switch logfile;
     21 
     22 System altered.
     23 
     24 SQL> drop tablespace jack including contents and datafiles;
     25 
     26 Tablespace dropped.
     27 
     28 SQL> create table echo_part tablespace echo as select * from dba_objects where rownum<1000;
     29 
     30 Table created.
     31 
     32 SQL> alter system switch logfile;
     33 
     34 System altered.
     35 
     36 SQL> alter system switch logfile;
     37 
     38 System altered.
     39 
     40 SQL> create tablespace jacks datafile '/u01/app/oracle/oradata/jacks01.dbf' size 50m;
     41 
     42 Tablespace created.
     43 
     44 SQL> alter system switch logfile;
     45 
     46 System altered.
     47 
     48 SQL> delete from jack_part where rownum<500;
     49 
     50 499 rows deleted.
     51 
     52 SQL> commit;
     53 
     54 Commit complete.
     55 
     56 SQL> alter system switch logfile;
     57 
     58 System altered.
     59 
     60 SQL> alter system switch logfile;
     61 
     62 System altered.
     63 
     64 SQL> alter system switch logfile;
     65 
     66 System altered.
     67 
     68 SQL> select count(*) from echo_part;
     69 
     70   COUNT(*)
     71 ----------
     72        999
     73 
     74 SQL> select count(*) from jack_part;
     75 
     76   COUNT(*)
     77 ----------
     78       1500
     79 
      ----模拟控制文件丢失----
     80 SQL> shutdown abort;
     81 ORACLE instance shut down.
     82 
     83 [oracle@yft yft]$ rm /u01/app/oracle/oradata/yft/control01.ctl 
     84 [oracle@yft yft]$ rm /u01/app/oracle/flash_recovery_area/yft/control02.ctl 
     85 
      ----启动时报错----
     86 SQL> startup
     87 ORACLE instance started.
     88 
     89 Total System Global Area  330600448 bytes
     90 Fixed Size            1336344 bytes
     91 Variable Size          272632808 bytes
     92 Database Buffers       50331648 bytes
     93 Redo Buffers            6299648 bytes
     94 ORA-00205: error in identifying control file, check alert log for more info
     95 
      ----alert日志里面也报错----
     96 ALTER DATABASE   MOUNT
     97 ORA-00210: cannot open the specified control file
     98 ORA-00202: control file: '/u01/app/oracle/flash_recovery_area/yft/control02.ctl'
     99 ORA-27037: unable to obtain file status
    100 Linux Error: 2: No such file or directory
    101 Additional information: 3
    102 ORA-00210: cannot open the specified control file
    103 ORA-00202: control file: '/u01/app/oracle/oradata/yft/control01.ctl'
    104 ORA-27037: unable to obtain file status
    105 Linux Error: 2: No such file or directory
    106 Additional information: 3
    107 ORA-205 signalled during: ALTER DATABASE   MOUNT...
    108 
     ----恢复一下旧的控制文件----
    109 RMAN> restore controlfile from '/u01/app/oracle/flash_recovery_area/YFT/backupset/2013_01_15/o1_mf_ncnnf_TAG20130115T172740_8hb88g4w_.bkp';
    110 
    111 Starting restore at 15-JAN-13
    112 using target database control file instead of recovery catalog
    113 allocated channel: ORA_DISK_1
    114 channel ORA_DISK_1: SID=20 device type=DISK
    115 
    116 channel ORA_DISK_1: restoring control file
    117 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
    118 output file name=/u01/app/oracle/oradata/yft/control01.ctl
    119 output file name=/u01/app/oracle/flash_recovery_area/yft/control02.ctl
    120 Finished restore at 15-JAN-13
    121 
     ----将数据库至于mount状态----
    122 RMAN> alter database mount;
    123 
    124 database mounted
    125 released channel: ORA_DISK_1
    126 
     ----查看数据文件信息，因为这时用的是旧的控制文件，所以jack还存在的，后创建的表空间是看不见的----
    127 SQL> select file#, name, status from v$datafile;
    128 
    129      FILE# NAME                                            STATUS
    130 ---------- ------------------------------------------ ---------------
    131      1 /u01/app/oracle/oradata/yft/system01.dbf            SYSTEM
    132      2 /u01/app/oracle/oradata/yft/sysaux01.dbf            ONLINE
    133      3 /u01/app/oracle/oradata/yft/undotbs01.dbf           ONLINE
    134      4 /u01/app/oracle/oradata/yft/users01.dbf             ONLINE
    135      5 /u01/app/oracle/oradata/yft/example01.dbf           ONLINE
    136      6 /u01/app/oracle/oradata/yft/jack01.dbf              ONLINE
    137 
    138 6 rows selected.
    139 
     ----这时候恢复会报错----
    140 RMAN> recover database;
    141 
    142 Starting recover at 15-JAN-13
    143 Starting implicit crosscheck backup at 15-JAN-13
    144 allocated channel: ORA_DISK_1
    145 channel ORA_DISK_1: SID=20 device type=DISK
    146 Finished implicit crosscheck backup at 15-JAN-13
    147 
    148 Starting implicit crosscheck copy at 15-JAN-13
    149 using channel ORA_DISK_1
    150 Finished implicit crosscheck copy at 15-JAN-13
    151 
    152 searching for all files in the recovery area
    153 cataloging files...
    154 cataloging done
    155 
    156 List of Cataloged Files
    157 =======================
    158 File Name: /u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_15/o1_mf_1_3_8hb8cpok_.arc
    159 File Name: /u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_15/o1_mf_1_7_8hb8fzoy_.arc
    160 File Name: /u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_15/o1_mf_1_6_8hb8fwpt_.arc
    161 File Name: /u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_15/o1_mf_1_4_8hb8dl3l_.arc
    162 File Name: /u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_15/o1_mf_1_2_8hb8cjnn_.arc
    163 File Name: /u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_15/o1_mf_1_5_8hb8fplg_.arc
    164 File Name: /u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_15/o1_mf_1_1_8hb89qfn_.arc
    165 File Name: /u01/app/oracle/flash_recovery_area/YFT/backupset/2013_01_15/o1_mf_ncnnf_TAG20130115T172740_8hb88g4w_.bkp
    166 File Name: /u01/app/oracle/flash_recovery_area/YFT/backupset/2013_01_15/o1_mf_ncnnf_TAG20130115T120908_8h9om64f_.bkp
    167 File Name: /u01/app/oracle/flash_recovery_area/YFT/backupset/2013_01_15/o1_mf_ncnnf_TAG20130115T165728_8hb6hwyn_.bkp
    168 File Name: /u01/app/oracle/flash_recovery_area/YFT/backupset/2013_01_15/o1_mf_ncnnf_TAG20130115T113519_8h9mmrr6_.bkp
    169 
    170 using channel ORA_DISK_1
    171 RMAN-00571: ===========================================================
    172 RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    173 RMAN-00571: ===========================================================
    174 RMAN-03002: failure of recover command at 01/15/2013 17:32:40
    175 RMAN-06094: datafile 6 must be restored
    176 
     ----查看一下有多少归档日志----
    177 [oracle@yft yft]$ ll /u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_15/
    178 total 964
    179 -rw-r----- 1 oracle oinstall 529408 Jan 15 17:28 o1_mf_1_1_8hb89qfn_.arc
    180 -rw-r----- 1 oracle oinstall 152576 Jan 15 17:29 o1_mf_1_2_8hb8cjnn_.arc
    181 -rw-r----- 1 oracle oinstall   1536 Jan 15 17:29 o1_mf_1_3_8hb8cpok_.arc
    182 -rw-r----- 1 oracle oinstall  73728 Jan 15 17:29 o1_mf_1_4_8hb8dl3l_.arc
    183 -rw-r----- 1 oracle oinstall 193024 Jan 15 17:30 o1_mf_1_5_8hb8fplg_.arc
    184 -rw-r----- 1 oracle oinstall   1536 Jan 15 17:30 o1_mf_1_6_8hb8fwpt_.arc
    185 -rw-r----- 1 oracle oinstall   2048 Jan 15 17:30 o1_mf_1_7_8hb8fzoy_.arc
    186 
      ---基于控制文件恢复，这时不能锁定6号文件，所以需要将6号文件offline----
    187 SQL> recover database using backup controlfile;
    188 ORA-00283: recovery session canceled due to errors
    189 ORA-01110: data file 6: '/u01/app/oracle/oradata/yft/jack01.dbf'
    190 ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
    191 ORA-01110: data file 6: '/u01/app/oracle/oradata/yft/jack01.dbf'
    192 
    193 
    194 SQL> alter database datafile 6 offline;
    195 
    196 Database altered.
    197 
     ----基于控制文件恢复，将归档日志一个一个恢复，当出现有报错时，将数据文件置于offline-----
    198 SQL> recover database using backup controlfile;
    199 ORA-00279: change 3794952 generated at 01/15/2013 17:19:33 needed for thread 1
    200 ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_15/o1_mf_1_1_8hb89qfn_.arc
    201 ORA-00280: change 3794952 for thread 1 is in sequence #1
    202 
    203 
    204 Specify log: {
        <RET>=suggested | filename | AUTO | CANCEL}
    205 /u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_15/o1_mf_1_1_8hb89qfn_.arc
    206 ORA-00283: recovery session canceled due to errors
    207 ORA-01244: unnamed datafile(s) added to control file by media recovery
    208 ORA-01110: data file 7: '/u01/app/oracle/oradata/echo01.dbf'
    209 
    210 
    211 ORA-01112: media recovery not started
    212 
    213 
    214 SQL> /u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_15/o1_mf_1_1_8hb89qfn_.arc
    215 SP2-0734: unknown command beginning "/u01/app/o..." - rest of line ignored.
    216 SQL> recover database using backup controlfile;
    217 ORA-00283: recovery session canceled due to errors
    218 ORA-01111: name for data file 7 is unknown - rename to correct file
    219 ORA-01110: data file 7: '/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00007'
    220 ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
    221 ORA-01111: name for data file 7 is unknown - rename to correct file
    222 ORA-01110: data file 7: '/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00007'
    223 
    224 
    225 SQL> select file#, name, status from v$datafile;
    226 
    227      FILE# NAME                                                   STATUS
    228 ---------- ---------------------------------------------------- ---------------
    229      1 /u01/app/oracle/oradata/yft/system01.dbf                   SYSTEM
    230      2 /u01/app/oracle/oradata/yft/sysaux01.dbf                   ONLINE
    231      3 /u01/app/oracle/oradata/yft/undotbs01.dbf                  ONLINE
    232      4 /u01/app/oracle/oradata/yft/users01.dbf                    ONLINE
    233      5 /u01/app/oracle/oradata/yft/example01.dbf                  ONLINE
    234      6 /u01/app/oracle/oradata/yft/jack01.dbf                     RECOVER
    235      7 /u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00007       RECOVER
    236 
    237 7 rows selected.
    238 
    239 SQL> alter database datafile 7 offline;
    240 
    241 Database altered.
    242 
    243 SQL> recover database using backup controlfile;
    244 ORA-00279: change 3794981 generated at 01/15/2013 17:28:11 needed for thread 1
    245 ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_15/o1_mf_1_1_8hb89qfn_.arc
    246 ORA-00280: change 3794981 for thread 1 is in sequence #1
    247 
    248 
    249 Specify log: {
        <RET>=suggested | filename | AUTO | CANCEL}
    250 /u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_15/o1_mf_1_1_8hb89qfn_.arc
    251 ORA-00279: change 3795249 generated at 01/15/2013 17:28:23 needed for thread 1
    252 ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_15/o1_mf_1_2_8hb8cjnn_.arc
    253 ORA-00280: change 3795249 for thread 1 is in sequence #2
    254 ORA-00278: log file '/u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_15/o1_mf_1_1_8hb89qfn_.arc' no longer
    255 needed for this recovery
    256 
    257 
    258 Specify log: {
        <RET>=suggested | filename | AUTO | CANCEL}
    259 /u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_15/o1_mf_1_2_8hb8cjnn_.arc
    260 ORA-00279: change 3795309 generated at 01/15/2013 17:29:20 needed for thread 1
    261 ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_15/o1_mf_1_3_8hb8cpok_.arc
    262 ORA-00280: change 3795309 for thread 1 is in sequence #3
    263 ORA-00278: log file '/u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_15/o1_mf_1_2_8hb8cjnn_.arc' no longer
    264 needed for this recovery
    265 
    266 
    267 Specify log: {
        <RET>=suggested | filename | AUTO | CANCEL}
    268 /u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_15/o1_mf_1_3_8hb8cpok_.arc
    269 ORA-00279: change 3795314 generated at 01/15/2013 17:29:26 needed for thread 1
    270 ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_15/o1_mf_1_4_8hb8dl3l_.arc
    271 ORA-00280: change 3795314 for thread 1 is in sequence #4
    272 ORA-00278: log file '/u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_15/o1_mf_1_3_8hb8cpok_.arc' no longer
    273 needed for this recovery
    274 
    275 
    276 Specify log: {
        <RET>=suggested | filename | AUTO | CANCEL}
    277 /u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_15/o1_mf_1_4_8hb8dl3l_.arc
    278 ORA-00283: recovery session canceled due to errors
    279 ORA-01244: unnamed datafile(s) added to control file by media recovery
    280 ORA-01110: data file 6: '/u01/app/oracle/oradata/jacks01.dbf'
    281 
    282 
    283 ORA-01112: media recovery not started
    284 
    285 
    286 SQL> select file#, name, status from v$datafile;
    287 
    288      FILE# NAME                             STATUS
    289 ---------- ------------------------------------------------------------ ---------------
    290      1 /u01/app/oracle/oradata/yft/system01.dbf            SYSTEM
    291      2 /u01/app/oracle/oradata/yft/sysaux01.dbf            ONLINE
    292      3 /u01/app/oracle/oradata/yft/undotbs01.dbf            ONLINE
    293      4 /u01/app/oracle/oradata/yft/users01.dbf            ONLINE
    294      5 /u01/app/oracle/oradata/yft/example01.dbf            ONLINE
    295      6 /u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00006     RECOVER
    296      7 /u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00007     RECOVER
    297 
    298 7 rows selected.
    299 
    300 SQL> alter database datafile 7 offline;
    301 
    302 Database altered.
    303 
    304 SQL> recover database using backup controlfile;
    305 ORA-00283: recovery session canceled due to errors
    306 ORA-01111: name for data file 6 is unknown - rename to correct file
    307 ORA-01110: data file 6: '/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00006'
    308 ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
    309 ORA-01111: name for data file 6 is unknown - rename to correct file
    310 ORA-01110: data file 6: '/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00006'
    311 
    312 
    313 SQL> alter database datafile 6 offline;
    314 
    315 Database altered.
    316 
    317 SQL> recover database using backup controlfile;
    318 ORA-00279: change 3795326 generated at 01/15/2013 17:29:35 needed for thread 1
    319 ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_15/o1_mf_1_4_8hb8dl3l_.arc
    320 ORA-00280: change 3795326 for thread 1 is in sequence #4
    321 
    322 
    323 Specify log: {
        <RET>=suggested | filename | AUTO | CANCEL}
    324 /u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_15/o1_mf_1_4_8hb8dl3l_.arc
    325 ORA-00279: change 3795595 generated at 01/15/2013 17:29:54 needed for thread 1
    326 ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_15/o1_mf_1_5_8hb8fplg_.arc
    327 ORA-00280: change 3795595 for thread 1 is in sequence #5
    328 ORA-00278: log file '/u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_15/o1_mf_1_4_8hb8dl3l_.arc' no longer
    329 needed for this recovery
    330 
    331 
    332 Specify log: {
        <RET>=suggested | filename | AUTO | CANCEL}
    333 /u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_15/o1_mf_1_5_8hb8fplg_.arc
    334 ORA-00279: change 3795623 generated at 01/15/2013 17:30:30 needed for thread 1
    335 ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_15/o1_mf_1_6_8hb8fwpt_.arc
    336 ORA-00280: change 3795623 for thread 1 is in sequence #6
    337 ORA-00278: log file '/u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_15/o1_mf_1_5_8hb8fplg_.arc' no longer
    338 needed for this recovery
    339 
    340 
    341 Specify log: {
        <RET>=suggested | filename | AUTO | CANCEL}
    342 /u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_15/o1_mf_1_6_8hb8fwpt_.arc
    343 ORA-00279: change 3795628 generated at 01/15/2013 17:30:36 needed for thread 1
    344 ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_15/o1_mf_1_7_8hb8fzoy_.arc
    345 ORA-00280: change 3795628 for thread 1 is in sequence #7
    346 ORA-00278: log file '/u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_15/o1_mf_1_6_8hb8fwpt_.arc' no longer
    347 needed for this recovery
    348 
    349 
    350 Specify log: {
        <RET>=suggested | filename | AUTO | CANCEL}
    351 /u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_15/o1_mf_1_7_8hb8fzoy_.arc
    352 ORA-00279: change 3795632 generated at 01/15/2013 17:30:39 needed for thread 1
    353 ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_15/o1_mf_1_8_%u_.arc
    354 ORA-00280: change 3795632 for thread 1 is in sequence #8
    355 ORA-00278: log file '/u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_15/o1_mf_1_7_8hb8fzoy_.arc' no longer
    356 needed for this recovery
    357 
    358 
    359 Specify log: {
        <RET>=suggested | filename | AUTO | CANCEL}
    360 /u01/app/oracle/oradata/yft/redo02.log
    361 Log applied.
    362 Media recovery complete.
    363 
     ----因为从上面已经看到8号日志文件是不存的，就说明该文件是当前日志文件，可以使用select b.sequence#, a.member, b.status from v$logfile a, v$log b where a.group#=b.group#;
         查询日志信息。----
     ----而且从alert日志从可以看到在abort之前的当前日志redo02.log----
    364 Thread 1 advanced to log sequence 8 (LGWR switch)
    365   Current log# 2 seq# 8 mem# 0: /u01/app/oracle/oradata/yft/redo02.log
    366 Tue Jan 15 17:30:39 2013
    367 Archived Log entry 27 added for thread 1 sequence 7 ID 0xb047497f dest 1:
    368 Tue Jan 15 17:31:03 2013
    369 Shutting down instance (abort)
    370 
     ----resetlogs打开数据库----
    371 SQL> alter database open resetlogs;
    372 
    373 Database altered.
    374 
    375 SQL> select file#, name, status from v$datafile;
    376 
    377      FILE# NAME                                                 STATUS
    378 ---------- ------------------------------------------------- ---------------
    379      1 /u01/app/oracle/oradata/yft/system01.dbf                 SYSTEM
    380      2 /u01/app/oracle/oradata/yft/sysaux01.dbf                 ONLINE
    381      3 /u01/app/oracle/oradata/yft/undotbs01.dbf                ONLINE
    382      4 /u01/app/oracle/oradata/yft/users01.dbf                  ONLINE
    383      5 /u01/app/oracle/oradata/yft/example01.dbf                ONLINE
    384      6 /u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00006     OFFLINE
    385      7 /u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00007     OFFLINE
    386 
    387 7 rows selected.
    388 
     ----想要将jacks的表空间online表错----
    389 SQL> alter tablespace jacks online;
    390 alter tablespace jacks online
    391 *
    392 ERROR at line 1:
    393 ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
    394 ORA-01110: data file 6: '/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00006'
    395 
    396 
    397 SQL> alter database datafile 6 online;
    398 alter database datafile 6 online
    399 *
    400 ERROR at line 1:
    401 ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
    402 ORA-01110: data file 6: '/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00006'
    403 
      ----将open以后出现的$ORACLE_HOME/dbs路径下的文件重命名一下----
    404 SQL> alter database rename file '/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00006' to '/u01/app/oracle/oradata/jacks01.dbf';
    405 
    406 Database altered.
    407 
    408 SQL> alter database rename file '/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00007' to '/u01/app/oracle/oradata/echo01.dbf';
    409 
    410 Database altered.
    411 
      ----online时还是报错就需要执行下面的步骤----
    412 SQL> alter tablespace jacks online;
    413 alter tablespace jacks online
    414 *
    415 ERROR at line 1:
    416 ORA-01190: control file or data file 6 is from before the last RESETLOGS
    417 ORA-01110: data file 6: '/u01/app/oracle/oradata/jacks01.dbf'
    418 
    419 
    420 SQL> select file#, name, status from v$datafile;
    421 
    422      FILE# NAME                                            STATUS
    423 ---------- --------------------------------------------- ---------------
    424      1 /u01/app/oracle/oradata/yft/system01.dbf            SYSTEM
    425      2 /u01/app/oracle/oradata/yft/sysaux01.dbf            ONLINE
    426      3 /u01/app/oracle/oradata/yft/undotbs01.dbf           ONLINE
    427      4 /u01/app/oracle/oradata/yft/users01.dbf             ONLINE
    428      5 /u01/app/oracle/oradata/yft/example01.dbf           ONLINE
    429      6 /u01/app/oracle/oradata/jacks01.dbf                 OFFLINE
    430      7 /u01/app/oracle/oradata/echo01.dbf                  OFFLINE
    431 
    432 7 rows selected.
    433 
      ----以下步骤是解决上面的报错的方法----
    434 SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;
    435 
    436 System altered.
    437 
    438 SQL> shutdown immediate;
    439 Database closed.
    440 Database dismounted.
    441 ORACLE instance shut down.
    442 SQL> startup
    443 ORACLE instance started.
    444 
    445 Total System Global Area  330600448 bytes
    446 Fixed Size            1336344 bytes
    447 Variable Size          272632808 bytes
    448 Database Buffers       50331648 bytes
    449 Redo Buffers            6299648 bytes
    450 Database mounted.
    451 Database opened.
    452 SQL> alter session set events 'immediate trace name adjust_scn level 1';
    453 
    454 Session altered.
    455 
    456 SQL> shutdown immediate;
    457 Database closed.
    458 Database dismounted.
    459 ORACLE instance shut down.
    460 SQL> startup mount;
    461 ORACLE instance started.
    462 
    463 Total System Global Area  330600448 bytes
    464 Fixed Size            1336344 bytes
    465 Variable Size          272632808 bytes
    466 Database Buffers       50331648 bytes
    467 Redo Buffers            6299648 bytes
    468 Database mounted.
    469 SQL> select ts#,file#,name,status,checkpoint_change# from v$datafile;
    470 
    471        TS#    FILE# NAME                                              STATUS       CHECKPOINT_CHANGE#
    472 ---------- ---------- -------------------------------------------- --------------- ------------------
    473      0        1 /u01/app/oracle/oradata/yft/system01.dbf               SYSTEM              3796246
    474      1        2 /u01/app/oracle/oradata/yft/sysaux01.dbf               ONLINE              3796246
    475      2        3 /u01/app/oracle/oradata/yft/undotbs01.dbf              ONLINE              3796246
    476      4        4 /u01/app/oracle/oradata/yft/users01.dbf                ONLINE              3796246
    477      6        5 /u01/app/oracle/oradata/yft/example01.dbf              ONLINE              3796246
    478     10        6 /u01/app/oracle/oradata/jacks01.dbf                    OFFLINE             3795324
    479      9        7 /u01/app/oracle/oradata/echo01.dbf                     OFFLINE             3794979
    480 
    481 7 rows selected.
    482 
    483 SQL> recover until cancel;
    484 Media recovery complete.
    485 SQL> alter database datafile 2,3,4,5,6,7 online;
    486 
    487 Database altered.
    488 
    489 SQL> alter database open resetlogs;
    490 
    491 Database altered.
    492 
    493 SQL> select ts#,file#,name,status,checkpoint_change# from v$datafile;
    494 
    495        TS#    FILE# NAME                                               STATUS       CHECKPOINT_CHANGE#
    496 ---------- ---------- --------------------------------------------- --------------- ------------------
    497      0        1 /u01/app/oracle/oradata/yft/system01.dbf               SYSTEM              3796250
    498      1        2 /u01/app/oracle/oradata/yft/sysaux01.dbf               ONLINE              3796250
    499      2        3 /u01/app/oracle/oradata/yft/undotbs01.dbf              ONLINE              3796250
    500      4        4 /u01/app/oracle/oradata/yft/users01.dbf                ONLINE              3796250
    501      6        5 /u01/app/oracle/oradata/yft/example01.dbf              ONLINE              3796250
    502     10        6 /u01/app/oracle/oradata/jacks01.dbf                    ONLINE              3796250
    503      9        7 /u01/app/oracle/oradata/echo01.dbf                     ONLINE              3796250
    504 
    505 7 rows selected.
    506 
    507 SQL> select count(*) from echo_part;
    508 
    509   COUNT(*)
    510 ----------
    511        999
    512 
    513 SQL> select count(*) from jack_part;
    514 
    515   COUNT(*)
    516 ----------
    517       1500

下面是恢复的另一种方法，是在恢复控制文件以后再重建控制文件，再基于控制文件恢复。

----在abort之前的操作跟上面一样----
      1 SQL> select count(*) from echo_part;
      2 
      3   COUNT(*)
      4 ----------
      5        999
      6 
      7 SQL> select count(*) from jack_part;
      8 
      9   COUNT(*)
     10 ----------
     11       1001
     12 
     13 
      ----进行控制文件恢复----
     14 RMAN> restore controlfile from '/u01/app/oracle/flash_recovery_area/YFT/backupset/2013_01_15/o1_mf_ncnnf_TAG20130115T172740_8hb88g4w_.bkp';
     15 
     16 Starting restore at 15-JAN-13
     17 using target database control file instead of recovery catalog
     18 allocated channel: ORA_DISK_1
     19 channel ORA_DISK_1: SID=20 device type=DISK
     20 
     21 channel ORA_DISK_1: restoring control file
     22 channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
     23 output file name=/u01/app/oracle/oradata/yft/control01.ctl
     24 output file name=/u01/app/oracle/flash_recovery_area/yft/control02.ctl
     25 Finished restore at 15-JAN-13
     26 
     27 RMAN> alter database mount;
     28 
     29 database mounted
     30 released channel: ORA_DISK_1
     31 
     32 
     33 
     34 SQL> select b.sequence#, a.member, b.status from v$logfile a, v$log b where a.group#=b.group#;
     35 
     36  SEQUENCE# MEMBER                     STATUS
     37 ---------- --------------------------------------------- ---------------
     38      1 /u01/app/oracle/oradata/yft/redo01.log     CURRENT
     39      0 /u01/app/oracle/oradata/yft/redo02.log     UNUSED
     40      0 /u01/app/oracle/oradata/yft/redo03.log     UNUSED
      ----重建控制文件----
     41 SQL> alter database backup controlfile to trace;
     42  
     43  Database altered.
     44  
     45 SQL> select value from v$diag_info where NAME = 'Default Trace File';
     46  
     47  VALUE
     48  --------------------------------------------------------------------------------
     49  /u01/app/oracle/diag/rdbms/yft/yft/trace/yft_ora_3964.trc
     50 
     51  [oracle@yft bak]$ sed -n '/CREATE CONTROLFILE.*NORESETLOGS/,/;/p' /u01/app/oracle/diag/rdbms/yft/yft/trace/yft_ora_3964.trc
     52  CREATE CONTROLFILE REUSE DATABASE "YFT" NORESETLOGS  ARCHIVELOG
     53      MAXLOGFILES 16
     54     MAXLOGMEMBERS 3
     55     MAXDATAFILES 100
     56      MAXINSTANCES 8
     57      MAXLOGHISTORY 292
     58 LOGFILE
     59    GROUP 1 '/u01/app/oracle/oradata/yft/redo01.log'  SIZE 50M BLOCKSIZE 512,
     60    GROUP 2 '/u01/app/oracle/oradata/yft/redo02.log'  SIZE 50M BLOCKSIZE 512,
     61    GROUP 3 '/u01/app/oracle/oradata/yft/redo03.log'  SIZE 50M BLOCKSIZE 512
     62  -- STANDBY LOGFILE
     63  DATAFILE
     64    '/u01/app/oracle/oradata/yft/system01.dbf',
     65    '/u01/app/oracle/oradata/yft/sysaux01.dbf',
     66    '/u01/app/oracle/oradata/yft/undotbs01.dbf',
     67    '/u01/app/oracle/oradata/yft/users01.dbf',
     68   '/u01/app/oracle/oradata/yft/example01.dbf',
     69    '/u01/app/oracle/oradata/yft/jack01.dbf'
     70  CHARACTER SET AL32UTF8
     71  ;
     72  
     73 
     74 SQL>CREATE CONTROLFILE REUSE DATABASE "YFT" NORESETLOGS  ARCHIVELOG
     75     MAXLOGFILES 16
     76     MAXLOGMEMBERS 3
     77     MAXDATAFILES 100
     78     MAXINSTANCES 8
     79     MAXLOGHISTORY 292
     80 LOGFILE
     81   GROUP 1 '/u01/app/oracle/oradata/yft/redo01.log'  SIZE 50M BLOCKSIZE 512,
     82   GROUP 2 '/u01/app/oracle/oradata/yft/redo02.log'  SIZE 50M BLOCKSIZE 512,
     83   GROUP 3 '/u01/app/oracle/oradata/yft/redo03.log'  SIZE 50M BLOCKSIZE 512
     84 -- STANDBY LOGFILE
     85 DATAFILE
     86   '/u01/app/oracle/oradata/yft/system01.dbf',
     87   '/u01/app/oracle/oradata/yft/sysaux01.dbf',
     88   '/u01/app/oracle/oradata/yft/undotbs01.dbf',
     89   '/u01/app/oracle/oradata/yft/users01.dbf',
     90  17    '/u01/app/oracle/oradata/yft/example01.dbf'
     91 CHARACTER SET AL32UTF8
     92  19  ;
     93 
     94 Control file created.
     95 
     96 SQL> select b.sequence#, a.member, b.status from v$logfile a, v$log b where a.group#=b.group#;
     97 
     98  SEQUENCE# MEMBER                                 STATUS
     99 ---------- ------------------------------------- ---------------
    100      7 /u01/app/oracle/oradata/yft/redo01.log     INACTIVE
    101      8 /u01/app/oracle/oradata/yft/redo02.log     CURRENT
    102      6 /u01/app/oracle/oradata/yft/redo03.log     INACTIVE
    103 
    104 SQL> recover database using backup controlfile;
    105 ORA-00279: change 3797907 generated at 01/15/2013 17:55:36 needed for thread 1
    106 ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_15/o1_mf_1_8_%u_.arc
    107 ORA-00280: change 3797907 for thread 1 is in sequence #8
    108 
    109 
    110 Specify log: {
        <RET>=suggested | filename | AUTO | CANCEL}
    111 /u01/app/oracle/oradata/yft/redo02.log
    112 Log applied.
    113 Media recovery complete.
    114 SQL> select file#, name, status from v$datafile;
    115 
    116      FILE# NAME                                             STATUS
    117 ---------- --------------------------------------------- ---------------
    118      1 /u01/app/oracle/oradata/yft/system01.dbf             SYSTEM
    119      2 /u01/app/oracle/oradata/yft/sysaux01.dbf             ONLINE
    120      3 /u01/app/oracle/oradata/yft/undotbs01.dbf            ONLINE
    121      4 /u01/app/oracle/oradata/yft/users01.dbf              ONLINE
    122      5 /u01/app/oracle/oradata/yft/example01.dbf            ONLINE
    123 
    124 SQL> alter database open resetlogs;
    125 
    126 Database altered.
    127 
    128 SQL> select file#, name, status from v$datafile;
    129 
    130      FILE# NAME                                                 STATUS
    131 ---------- ------------------------------------------------- ---------------
    132      1 /u01/app/oracle/oradata/yft/system01.dbf                 SYSTEM
    133      2 /u01/app/oracle/oradata/yft/sysaux01.dbf                 ONLINE
    134      3 /u01/app/oracle/oradata/yft/undotbs01.dbf                ONLINE
    135      4 /u01/app/oracle/oradata/yft/users01.dbf                  ONLINE
    136      5 /u01/app/oracle/oradata/yft/example01.dbf                ONLINE
    137      6 /u01/app/oracle/product/11.2.0/db_1/dbs/MISSING00006     RECOVER
    138      7 /u01/app/oracle/product/11.2.0/db_1/dbs/MISSING00007     RECOVER
    139 
    140 7 rows selected.
    141 
    142 SQL> alter tablespace jacks online;
    143 alter tablespace jacks online
    144 *
    145 ERROR at line 1:
    146 ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
    147 ORA-01111: name for data file 6 is unknown - rename to correct file
    148 ORA-01110: data file 6: '/u01/app/oracle/product/11.2.0/db_1/dbs/MISSING00006'
    149 
    150 
    151 SQL> alter database rename file '/u01/app/oracle/product/11.2.0/db_1/dbs/MISSING00006' to '/u01/app/oracle/oradata/jacks01.dbf';
    152 
    153 Database altered.
    154 
    155 SQL> alter database rename file '/u01/app/oracle/product/11.2.0/db_1/dbs/MISSING00007' to '/u01/app/oracle/oradata/echo01.dbf';
    156 
    157 Database altered.
    158 
    159 SQL> alter tablespace jacks online;
    160 alter tablespace jacks online
    161 *
    162 ERROR at line 1:
    163 ORA-01190: control file or data file 6 is from before the last RESETLOGS
    164 ORA-01110: data file 6: '/u01/app/oracle/oradata/jacks01.dbf'
    165 
    166 
    167 SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;
    168 
    169 System altered.
    170 
    171 SQL> shutdown immediate;
    172 Database closed.
    173 Database dismounted.
    174 ORACLE instance shut down.
    175 SQL> startup
    176 ORACLE instance started.
    177 
    178 Total System Global Area  330600448 bytes
    179 Fixed Size            1336344 bytes
    180 Variable Size          272632808 bytes
    181 Database Buffers       50331648 bytes
    182 Redo Buffers            6299648 bytes
    183 Database mounted.
    184 Database opened.
    185 SQL> alter session set events 'immediate trace name adjust_scn level 1';
    186 
    187 Session altered.
    188 
    189 SQL> shutdown immediate;
    190 Database closed.
    191 Database dismounted.
    192 ORACLE instance shut down.
    193 SQL> startup mount;
    194 ORACLE instance started.
    195 
    196 Total System Global Area  330600448 bytes
    197 Fixed Size            1336344 bytes
    198 Variable Size          272632808 bytes
    199 Database Buffers       50331648 bytes
    200 Redo Buffers            6299648 bytes
    201 Database mounted.
    202 SQL> select ts#,file#,name,status,checkpoint_change# from v$datafile;
    203 
    204        TS#    FILE# NAME                                              STATUS       CHECKPOINT_CHANGE#
    205 ---------- ---------- ------------------------------------------- --------------- ------------------
    206      0        1 /u01/app/oracle/oradata/yft/system01.dbf               SYSTEM              3798567
    207      1        2 /u01/app/oracle/oradata/yft/sysaux01.dbf               ONLINE              3798567
    208      2        3 /u01/app/oracle/oradata/yft/undotbs01.dbf              ONLINE              3798567
    209      4        4 /u01/app/oracle/oradata/yft/users01.dbf                ONLINE              3798567
    210      6        5 /u01/app/oracle/oradata/yft/example01.dbf              ONLINE              3798567
    211     10        6 /u01/app/oracle/oradata/jacks01.dbf                    RECOVER                0
    212      9        7 /u01/app/oracle/oradata/echo01.dbf                     RECOVER                0
    213 
    214 7 rows selected.
    215 
    216 SQL> recover until cancel;
    217 Media recovery complete.
    218 SQL> alter database datafile 2,3,4,5,6,7 online;
    219 
    220 Database altered.
    221 
    222 SQL> alter database open resetlogs;
    223 
    224 Database altered.
    225 
    226 SQL> select ts#,file#,name,status,checkpoint_change# from v$datafile;
    227 
    228        TS#    FILE# NAME                                              STATUS       CHECKPOINT_CHANGE#
    229 ---------- ---------- ------------------------------------------ --------------- ------------------
    230      0        1 /u01/app/oracle/oradata/yft/system01.dbf               SYSTEM              3798571
    231      1        2 /u01/app/oracle/oradata/yft/sysaux01.dbf               ONLINE              3798571
    232      2        3 /u01/app/oracle/oradata/yft/undotbs01.dbf              ONLINE              3798571
    233      4        4 /u01/app/oracle/oradata/yft/users01.dbf                ONLINE              3798571
    234      6        5 /u01/app/oracle/oradata/yft/example01.dbf              ONLINE              3798571
    235     10        6 /u01/app/oracle/oradata/jacks01.dbf                    ONLINE              3798571
    236      9        7 /u01/app/oracle/oradata/echo01.dbf                     ONLINE              3798571
    237 
    238 7 rows selected.
    239 
    240 SQL> select count(*) from jack_part;
    241 
    242   COUNT(*)
    243 ----------
    244       1001
    245 
    246 SQL> select count(*) from echo_part;
    247 
    248   COUNT(*)
    249 ----------
    250        999
     最后还是需要alter tablespace temp add tempfile '/u01/app/oracle/oradata/yft/temp02.dbf' size 200m;来增加临时表空间。

转载于:https://www.cnblogs.com/Richardzhu/archive/2013/01/15/2861499.html

[--1_DROP_]: http://www.cnblogs.com/Richardzhu/articles/2854137.html
[--2]: http://www.cnblogs.com/Richardzhu/articles/2855033.html