丢失控制文件，有旧的备份控制文件，之后有drop表空间和create表空间的操作恢复。
一、实验说明：
本实验是基于： 《丢失控制文件恢复实验记录--1(当前的控制文件损坏，使用旧控制文件进行恢复(旧控制文件之后DROP了表空间的情况))》 和 《丢失控制文件恢复实验记录--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;来增加临时表空间。
