丢失控制文件恢复实验记录--4(在线日志文件没有损坏，归档日志丢失，直接重建控制文件(跟踪控制文件trace是旧的情况))
一、实验说明：
本文转载于Luocs的丢失控制文件恢复实验记录--4，此处属于转载+模拟。该实验其实跟 《丢失控制文件恢复实验记录--2(当前的控制文件损坏，使用旧控制文件进行恢复(旧控制文件之后新增了表空间的情况))》类似，就是少了利用备份的旧控制文件恢复而已。
操作系统：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 43452 106 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 53451 120 121 SQL> select count(*) from luocs.t1; 122 123 COUNT(*) 124 ---------- 125 19998 126 127 SQL> select max(sequence#) from v$archived_log; 128 129 MAX(SEQUENCE#) 130 -------------- 131 10 132 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 11 154 155 SQL> select count(*) from luocs.t1; 156 157 COUNT(*) 158 ---------- 159 29997 160 161 SQL> select count(*) from test.t1; 162 163 COUNT(*) 164 ---------- 165 63450 166 ----模拟丢失控制文件，丢失归档文件----
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/bak 172 total 6572 173 -rw-r----- 1 oracle oinstall 2340864 Jan 12 08:44 o1_mf_1_10_8h1dgmdr_.arc 174 -rw-r----- 1 oracle oinstall 2326528 Jan 12 08:45 o1_mf_1_11_8h1djno1_.arc 175 -rw-r----- 1 oracle oinstall 2017280 Jan 12 08:42 o1_mf_1_2_8h1dcff7_.arc 176 -rw-r----- 1 oracle oinstall 1024 Jan 12 08:42 o1_mf_1_3_8h1dckqv_.arc 177 -rw-r----- 1 oracle oinstall 2560 Jan 12 08:42 o1_mf_1_4_8h1dcopp_.arc 178 -rw-r----- 1 oracle oinstall 1024 Jan 12 08:42 o1_mf_1_5_8h1dcpm6_.arc 179 -rw-r----- 1 oracle oinstall 1024 Jan 12 08:42 o1_mf_1_6_8h1dcqxs_.arc 180 -rw-r----- 1 oracle oinstall 1024 Jan 12 08:42 o1_mf_1_7_8h1dcrt3_.arc 181 -rw-r----- 1 oracle oinstall 1024 Jan 12 08:42 o1_mf_1_8_8h1dcsq9_.arc 182 -rw-r----- 1 oracle oinstall 1024 Jan 12 08:42 o1_mf_1_9_8h1dd31j_.arc 183 ----启动报错----
184 SQL> startup 185 ORACLE instance started. 186 187 Total System Global Area 330600448 bytes 188 Fixed Size 1336344 bytes 189 Variable Size 247466984 bytes 190 Database Buffers 75497472 bytes 191 Redo Buffers 6299648 bytes 192 ORA-00205: error in identifying control file, check alert log for more info 193 194 ----直接重建控制文件----
195 SQL>CREATE CONTROLFILE REUSE DATABASE "YFT" NORESETLOGS ARCHIVELOG 196 MAXLOGFILES 16 197 MAXLOGMEMBERS 3 198 MAXDATAFILES 100 199 MAXINSTANCES 8 200 MAXLOGHISTORY 292 201 LOGFILE 202 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 512 205 '/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 AL32UTF8 212 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 STATUS 220 ---------- ----------------------------------------- ------- 221 1 /u01/app/oracle/oradata/yft/system01.dbf SYSTEM 222 2 /u01/app/oracle/oradata/yft/sysaux01.dbf RECOVER 223 3 /u01/app/oracle/oradata/yft/undotbs01.dbf RECOVER 224 4 /u01/app/oracle/oradata/yft/users01.dbf RECOVER 225 5 /u01/app/oracle/oradata/yft/example01.dbf RECOVER 226 6 /u01/app/oracle/oradata/yft/jack01.dbf RECOVER 227 228 6 rows selected. 229 ----恢复数据库，报错找不到10号日志文件----
230 RMAN> recover database; 231 232 Starting recover at 12-JAN-13 233 using target database control file instead of recovery catalog 234 allocated channel: ORA_DISK_1 235 channel ORA_DISK_1: SID=22 device type=DISK 236 237 starting media recovery 238 239 unable to find archived log 240 archived log thread=1 sequence=10 241 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:40 245 RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 10 and starting SCN of 879342 246 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 STATUS 252 ---------- -------------------------------------- --------------- 253 10 /u01/app/oracle/oradata/yft/redo01.log INACTIVE 254 11 /u01/app/oracle/oradata/yft/redo02.log INACTIVE 255 12 /u01/app/oracle/oradata/yft/redo03.log CURRENT 256 ----发现上面正好有需要的10号文件，执行基于控制文件恢复一下----
257 SQL> recover database using backup controlfile; 258 ORA-00279: change 879342 generated at 01/12/2013 08:42:42 needed for thread 1 259 ORA-00289: suggestion : 260 /u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_12/o1_mf_1_10_%u_.arc 261 ORA-00280: change 879342 for thread 1 is in sequence #10 262 263 264 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} 265 /u01/app/oracle/oradata/yft/redo01.log 266 ORA-00279: change 879375 generated at 01/12/2013 08:44:03 needed for thread 1 267 ORA-00289: suggestion : 268 /u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_12/o1_mf_1_11_%u_.arc 269 ORA-00280: change 879375 for thread 1 is in sequence #11 270 ORA-00278: log file '/u01/app/oracle/oradata/yft/redo01.log' no longer needed 271 for this recovery 272 273 274 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} 275 /u01/app/oracle/oradata/yft/redo02.log 276 ORA-00279: change 879451 generated at 01/12/2013 08:45:08 needed for thread 1 277 ORA-00289: suggestion : 278 /u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_12/o1_mf_1_12_%u_.arc 279 ORA-00280: change 879451 for thread 1 is in sequence #12 280 ORA-00278: log file '/u01/app/oracle/oradata/yft/redo02.log' no longer needed 281 for this recovery 282 283 284 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} 285 /u01/app/oracle/oradata/yft/redo03.log 286 Log applied. 287 Media recovery complete. ----再查看数据文件的状态，在这里依然看不到我们后创建的数据文件----
288 SQL> select file#,name,status from v$datafile; 289 290 FILE# NAME STATUS 291 ---------- ---------------------------------------- --------------- 292 1 /u01/app/oracle/oradata/yft/system01.dbf SYSTEM 293 2 /u01/app/oracle/oradata/yft/sysaux01.dbf ONLINE 294 3 /u01/app/oracle/oradata/yft/undotbs01.dbf ONLINE 295 4 /u01/app/oracle/oradata/yft/users01.dbf ONLINE 296 5 /u01/app/oracle/oradata/yft/example01.dbf ONLINE 297 6 /u01/app/oracle/oradata/yft/jack01.dbf ONLINE 298 299 6 rows selected. 300 ----启动数据库----
301 SQL> alter database open; 302 alter database open 303 * 304 ERROR at line 1: 305 ORA-01589: must use RESETLOGS or NORESETLOGS option for database open 306 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 STATUS 316 ---------- -------------------------------------------------------- --------------- 317 1 /u01/app/oracle/oradata/yft/system01.dbf SYSTEM 318 2 /u01/app/oracle/oradata/yft/sysaux01.dbf ONLINE 319 3 /u01/app/oracle/oradata/yft/undotbs01.dbf ONLINE 320 4 /u01/app/oracle/oradata/yft/users01.dbf ONLINE 321 5 /u01/app/oracle/oradata/yft/example01.dbf ONLINE 322 6 /u01/app/oracle/oradata/yft/jack01.dbf ONLINE 323 7 /u01/app/oracle/product/11.2.0/db_1/dbs/MISSING00007 RECOVER 324 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> startup 344 ORACLE instance started. 345 346 Total System Global Area 330600448 bytes 347 Fixed Size 1336344 bytes 348 Variable Size 247466984 bytes 349 Database Buffers 75497472 bytes 350 Redo Buffers 6299648 bytes 351 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 bytes 366 Fixed Size 1336344 bytes 367 Variable Size 247466984 bytes 368 Database Buffers 75497472 bytes 369 Redo Buffers 6299648 bytes 370 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 880126 377 1 2 /u01/app/oracle/oradata/yft/sysaux01.dbf ONLINE 880126 378 2 3 /u01/app/oracle/oradata/yft/undotbs01.dbf ONLINE 880126 379 4 4 /u01/app/oracle/oradata/yft/users01.dbf ONLINE 880126 380 6 5 /u01/app/oracle/oradata/yft/example01.dbf ONLINE 880126 381 7 6 /u01/app/oracle/oradata/yft/jack01.dbf ONLINE 880126 382 8 7 /u01/app/oracle/oradata/yft/luocs01.dbf RECOVER 0 383 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 880130 401 1 2 /u01/app/oracle/oradata/yft/sysaux01.dbf ONLINE 880130 402 2 3 /u01/app/oracle/oradata/yft/undotbs01.dbf ONLINE 880130 403 4 4 /u01/app/oracle/oradata/yft/users01.dbf ONLINE 880130 404 6 5 /u01/app/oracle/oradata/yft/example01.dbf ONLINE 880130 405 7 6 /u01/app/oracle/oradata/yft/jack01.dbf ONLINE 880130 406 8 7 /u01/app/oracle/oradata/yft/luocs01.dbf ONLINE 880130 407 408 7 rows selected. 409 ----查看一下数据有没有丢失----
410 SQL> select count(*) from luocs.t1; 411 412 COUNT(*) 413 ---------- 414 9999 415 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;
