丢失控制文件恢复实验记录--3(当前的控制文件损坏，归档日志文件损坏且备份的控制文件是旧的情况恢复数据库)

一、实验说明：

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

操作系统：rhel 5.4 x32

数据库：oracle 11g r2

二、实验操作：

  ----先清除历史测试数据然后再产生数据：----
  1 SQL> drop user luocs cascade;
  2 
  3 User dropped.
  4 
  5 SQL> drop tablespace luocs including contents;
  6 
  7 Tablespace dropped.
  8 
  9 RMAN> delete noprompt backup;
 10 
 11 using target database control file instead of recovery catalog
 12 allocated channel: ORA_DISK_1
 13 channel ORA_DISK_1: SID=31 device type=DISK
 14 specification does not match any backup in the repository
 15 
 16 RMAN> delete noprompt copy;
 17 
 18 released channel: ORA_DISK_1
 19 allocated channel: ORA_DISK_1
 20 channel ORA_DISK_1: SID=31 device type=DISK
 21 specification does not match any datafile copy in the repository
 22 specification does not match any control file copy in the repository
 23 List of Archived Log Copies for database with db_unique_name YFT
 24 =====================================================================
 25 
 26 Key     Thrd Seq     S Low Time 
 27 ------- ---- ------- - ---------
 28 3       1    6       A 11-JAN-13
 29         Name: /u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_11/o1_mf_1_6_8gzbgpp4_.arc
 30 
 31 1       1    7       A 11-JAN-13
 32         Name: /u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_11/o1_mf_1_7_8gzbgplr_.arc
 33 
 34 2       1    8       A 11-JAN-13
 35         Name: /u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_11/o1_mf_1_8_8gzbgpn0_.arc
 36 
 37 4       1    1       A 11-JAN-13
 38         Name: /u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_11/o1_mf_1_1_8gzbvydb_.arc
 39 
 40 5       1    2       A 11-JAN-13
 41         Name: /u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_11/o1_mf_1_2_8gzcfv2l_.arc
 42 
 43 deleted archived log
 44 archived log file name=/u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_11/o1_mf_1_6_8gzbgpp4_.arc RECID=3 STAMP=804434262
 45 deleted archived log
 46 archived log file name=/u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_11/o1_mf_1_7_8gzbgplr_.arc RECID=1 STAMP=804434262
 47 deleted archived log
 48 archived log file name=/u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_11/o1_mf_1_8_8gzbgpn0_.arc RECID=2 STAMP=804434262
 49 deleted archived log
 50 archived log file name=/u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_11/o1_mf_1_1_8gzbvydb_.arc RECID=4 STAMP=804434686
 51 deleted archived log
 52 archived log file name=/u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_11/o1_mf_1_2_8gzcfv2l_.arc RECID=5 STAMP=804435259
 53 Deleted 5 objects
 54 
  ----备份当前控制文件----
 55 RMAN> backup current controlfile;
 56 
 57 Starting backup at 11-JAN-13
 58 using channel ORA_DISK_1
 59 channel ORA_DISK_1: starting full datafile backup set
 60 channel ORA_DISK_1: specifying datafile(s) in backup set
 61 including current control file in backup set
 62 channel ORA_DISK_1: starting piece 1 at 11-JAN-13
 63 channel ORA_DISK_1: finished piece 1 at 11-JAN-13
 64 piece handle=/u01/app/oracle/flash_recovery_area/YFT/backupset/2013_01_11/o1_mf_ncnnf_TAG20130111T181929_8gzsslmr_.bkp tag=TAG20130111T181929 comment=NONE
 65 channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
 66 Finished backup at 11-JAN-13
 67 
  ----重新产生数据----
 68 SQL> create tablespace luocs datafile '/u01/app/oracle/oradata/yft/luocs01.dbf' size 50m;
 69 
 70 Tablespace created.
 71 
 72 SQL> create user luocs identified by oracle default tablespace luocs;
 73 
 74 User created.
 75 
 76 SQL> grant resource,connect to luocs;
 77 
 78 Grant succeeded.
 79 
 80 SQL> create table luocs.t1 as select * from dba_objects where rownum<10000;
 81 
 82 Table created.
 83 
 84 SQL> alter system switch logfile;
 85 
 86 System altered.
 87 
 88 SQL> select count(*) from test.t1;
 89 
 90   COUNT(*)
 91 ----------
 92      63451
 93 
 94 SQL> alter system switch logfile;
 95 
 96 System altered.
 97 
 98 SQL> alter system switch logfile;
 99 
100 System altered.
101 
102 SQL> insert into luocs.t1 select * from luocs.t1;
103 
104 9999 rows created.
105 
106 SQL> commit;
107 
108 Commit complete.
109 
110 SQL> alter system switch logfile;
111 
112 System altered.
113 
114 SQL> alter system switch logfile;
115 
116 System altered.
117 
118 SQL> delete from test.t1 where rownum<20000;
119 
120 19999 rows deleted.
121 
122 SQL> delete from luocs.t1 where rownum<5000;
123 
124 4999 rows deleted.
125 
126 SQL> select count(*) from test.t1;
127 
128   COUNT(*)
129 ----------
130      43452
131 
132 SQL> select count(*) from luocs.t1;
133 
134   COUNT(*)
135 ----------
136      14999
137 
138 SQL> commit;
139 
140 Commit complete.
141 
142 SQL> alter system switch logfile;
143 
144 System altered.
145 
146 SQL> alter system switch logfile;
147 
148 System altered.
149 
  ----因为之前有过resetlogs的操作，所以使用select max(sequence#) from v$archived_logs查询有误----
150 SQL> archive log list;
151 Database log mode           Archive Mode
152 Automatic archival           Enabled
153 Archive destination           USE_DB_RECOVERY_FILE_DEST
154 Oldest online log sequence     6
155 Next log sequence to archive   8
156 Current log sequence           8
  ----模拟丢失控制文件，然后扩展连归档日志也丢失
157 SQL> shutdown abort;
158 ORACLE instance shut down.
159 
 ----删除控制文件----
160 [oracle@yft ~]$ rm /u01/app/oracle/oradata/yft/control01.ctl 
 ----把全部归档文件全部当做丢失----
161 [oracle@yft bak]$ mv /u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_11/* /tmp/bak
162 
 ----数据库启动报错----
163 SQL> startup
164 ORACLE instance started.
165 
166 Total System Global Area  330600448 bytes
167 Fixed Size            1336344 bytes
168 Variable Size          243272680 bytes
169 Database Buffers       79691776 bytes
170 Redo Buffers            6299648 bytes
171 ORA-00205: error in identifying control file, check alert log for more info
172 
 ----这个时候已经到nomount状态----
173 RMAN> restore controlfile from '/u01/app/oracle/flash_recovery_area/YFT/backupset/2013_01_11/o1_mf_ncnnf_TAG20130111T181929_8gzsslmr_.bkp';
174 
175 Starting restore at 11-JAN-13
176 using target database control file instead of recovery catalog
177 allocated channel: ORA_DISK_1
178 channel ORA_DISK_1: SID=20 device type=DISK
179 
180 channel ORA_DISK_1: restoring control file
181 channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
182 output file name=/u01/app/oracle/oradata/yft/control01.ctl
183 output file name=/u01/app/oracle/flash_recovery_area/yft/control02.ctl
184 Finished restore at 11-JAN-13
185 
186 RMAN> alter database mount;
187 
188 database mounted
189 released channel: ORA_DISK_1
190 
 ----在这里考虑到归档日志文件丢失使用recover database会失败，而且备份的控制文件不是最新的，索性创建一个新的控制文件---- 
191 SQL> alter database backup controlfile to trace;
192 
193 Database altered.
194 
 ----导出查找刚才的trace文件路径----
195 SQL> select value from v$diag_info where NAME = 'Default Trace File';
196 
197 VALUE
198 --------------------------------------------------------------------------------
199 /u01/app/oracle/diag/rdbms/yft/yft/trace/yft_ora_3964.trc
200 
201 [oracle@yft bak]$ sed -n '/CREATE CONTROLFILE.*NORESETLOGS/,/;/p' /u01/app/oracle/diag/rdbms/yft/yft/trace/yft_ora_3964.trc
202 CREATE CONTROLFILE REUSE DATABASE "YFT" NORESETLOGS  ARCHIVELOG
203     MAXLOGFILES 16
204     MAXLOGMEMBERS 3
205     MAXDATAFILES 100
206     MAXINSTANCES 8
207     MAXLOGHISTORY 292
208 LOGFILE
209   GROUP 1 '/u01/app/oracle/oradata/yft/redo01.log'  SIZE 50M BLOCKSIZE 512,
210   GROUP 2 '/u01/app/oracle/oradata/yft/redo02.log'  SIZE 50M BLOCKSIZE 512,
211   GROUP 3 '/u01/app/oracle/oradata/yft/redo03.log'  SIZE 50M BLOCKSIZE 512
212 -- STANDBY LOGFILE
213 DATAFILE
214   '/u01/app/oracle/oradata/yft/system01.dbf',
215   '/u01/app/oracle/oradata/yft/sysaux01.dbf',
216   '/u01/app/oracle/oradata/yft/undotbs01.dbf',
217   '/u01/app/oracle/oradata/yft/users01.dbf',
218   '/u01/app/oracle/oradata/yft/example01.dbf',
219   '/u01/app/oracle/oradata/yft/jack01.dbf'
220 CHARACTER SET AL32UTF8
221 ;
222 
  ----查看当前redo log的状态，这个是从旧的控制文件中读出来的----
223 SQL> col member for a45;
224 SQL> col status for a15;
225 SQL> select b.sequence#, a.member, b.status from v$logfile a, v$log b where a.group#=b.group#;
226 
227  SEQUENCE# MEMBER                     STATUS
228 ---------- --------------------------------------------- ---------------
229      1 /u01/app/oracle/oradata/yft/redo01.log     CURRENT
230      0 /u01/app/oracle/oradata/yft/redo02.log     UNUSED
231      0 /u01/app/oracle/oradata/yft/redo03.log     UNUSED
232 
  ----创建控制文件需要启动到nomount状态----
233 SQL> startup force nomount;
234 ORACLE instance started.
235 
236 Total System Global Area  330600448 bytes
237 Fixed Size            1336344 bytes
238 Variable Size          243272680 bytes
239 Database Buffers       79691776 bytes
240 Redo Buffers            6299648 bytes
241 
  ----重建新的控制文件----
242 SQL>CREATE CONTROLFILE REUSE DATABASE "YFT" NORESETLOGS  ARCHIVELOG
243     MAXLOGFILES 16
244     MAXLOGMEMBERS 3
245     MAXDATAFILES 100
246     MAXINSTANCES 8
247     MAXLOGHISTORY 292
248 LOGFILE
249   GROUP 1 '/u01/app/oracle/oradata/yft/redo01.log'  SIZE 50M BLOCKSIZE 512,
250   GROUP 2 '/u01/app/oracle/oradata/yft/redo02.log'  SIZE 50M BLOCKSIZE 512,
251   GROUP 3 '/u01/app/oracle/oradata/yft/redo03.log'  SIZE 50M BLOCKSIZE 512
252 DATAFILE
253   '/u01/app/oracle/oradata/yft/system01.dbf',
254   '/u01/app/oracle/oradata/yft/sysaux01.dbf',
255   '/u01/app/oracle/oradata/yft/undotbs01.dbf',
256   '/u01/app/oracle/oradata/yft/users01.dbf',
257   '/u01/app/oracle/oradata/yft/example01.dbf',
258   '/u01/app/oracle/oradata/yft/jack01.dbf'
259 CHARACTER SET AL32UTF8
260 19  ;
261 
262 Control file created.
263 
  ----现在redo的sequence#才是我们模拟故障前的----
264 SQL> select b.sequence#, a.member, b.status from v$logfile a, v$log b where a.group#=b.group#;
265 
266  SEQUENCE# MEMBER                     STATUS
267 ---------- --------------------------------------------- ---------------
268      7 /u01/app/oracle/oradata/yft/redo01.log     INACTIVE
269      8 /u01/app/oracle/oradata/yft/redo02.log     CURRENT
270      6 /u01/app/oracle/oradata/yft/redo03.log     INACTIVE
271 
 ----数据文件都需要恢复----
272 SQL> select file#, name, status from v$datafile;
273 
274      FILE# NAME                      STATUS
275 -------- --------------------------------------------- ---------------
276      1 /u01/app/oracle/oradata/yft/system01.dbf      SYSTEM
277      2 /u01/app/oracle/oradata/yft/sysaux01.dbf      RECOVER
278      3 /u01/app/oracle/oradata/yft/undotbs01.dbf     RECOVER
279      4 /u01/app/oracle/oradata/yft/users01.dbf       RECOVER
280      5 /u01/app/oracle/oradata/yft/example01.dbf     RECOVER
281      6 /u01/app/oracle/oradata/yft/jack01.dbf        RECOVER
282 
283 6 rows selected.
284 
 ----基于控制文件恢复一下，在这里需要的归档日志正好是上面的redo日志----
285 SQL> recover database using backup controlfile;
286 ORA-00279: change 854294 generated at 01/11/2013 18:23:14 needed for thread 1
287 ORA-00289: suggestion :
288 /u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_11/o1_mf_1_6_%u_.arc
289 ORA-00280: change 854294 for thread 1 is in sequence #6
290 
291 
292 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
293 /u01/app/oracle/oradata/yft/redo03.log
294 ORA-00279: change 854684 generated at 01/11/2013 18:24:17 needed for thread 1
295 ORA-00289: suggestion :
296 /u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_11/o1_mf_1_7_%u_.arc
297 ORA-00280: change 854684 for thread 1 is in sequence #7
298 ORA-00278: log file '/u01/app/oracle/oradata/yft/redo03.log' no longer needed
299 for this recovery
300 
301 
302 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
303 /u01/app/oracle/oradata/yft/redo01.log
304 ORA-00279: change 854688 generated at 01/11/2013 18:24:21 needed for thread 1
305 ORA-00289: suggestion :
306 /u01/app/oracle/flash_recovery_area/YFT/archivelog/2013_01_11/o1_mf_1_8_%u_.arc
307 ORA-00280: change 854688 for thread 1 is in sequence #8
308 ORA-00278: log file '/u01/app/oracle/oradata/yft/redo01.log' no longer needed
309 for this recovery
310 
311 
312 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
313 /u01/app/oracle/oradata/yft/redo02.log
314 Log applied.
315 Media recovery complete.
  ----现在数据文件都已经online了，但是还缺少我们创建的luocs01.dbf数据文件----
316 SQL> select file#, name, status from v$datafile;
317 
318      FILE# NAME                                      STATUS
319 ---------- ---------------------------------------- ---------------
320      1 /u01/app/oracle/oradata/yft/system01.dbf      SYSTEM
321      2 /u01/app/oracle/oradata/yft/sysaux01.dbf      ONLINE
322      3 /u01/app/oracle/oradata/yft/undotbs01.dbf     ONLINE
323      4 /u01/app/oracle/oradata/yft/users01.dbf       ONLINE
324      5 /u01/app/oracle/oradata/yft/example01.dbf     ONLINE
325      6 /u01/app/oracle/oradata/yft/jack01.dbf        ONLINE
326 
327 6 rows selected.
  ----先resetlogs打开数据库----
328 SQL> alter database open resetlogs;
329 
330 Database altered.
331 
  ----这时候因为应用了当前的日志，而且resetlogs打开数据库，所以再$ORACLE_HOME/dbs目录下生成了一个数据文件，但这个却用不了----
332 SQL> select file#, name, status from v$datafile;
333 
334      FILE# NAME                                                STATUS
335 ---------- ----------------------------------------------- ---------------
336      1 /u01/app/oracle/oradata/yft/system01.dbf                SYSTEM
337      2 /u01/app/oracle/oradata/yft/sysaux01.dbf                ONLINE
338      3 /u01/app/oracle/oradata/yft/undotbs01.dbf               ONLINE
339      4 /u01/app/oracle/oradata/yft/users01.dbf                 ONLINE
340      5 /u01/app/oracle/oradata/yft/example01.dbf               ONLINE
341      6 /u01/app/oracle/oradata/yft/jack01.dbf                  ONLINE
342      7 /u01/app/oracle/product/11.2.0/db_1/dbs/MISSING00007    RECOVER
343 
344 7 rows selected.
345 
 ----先将7号数据文件offline----
346 SQL> alter database datafile 7 offline;
347 
348 Database altered.
349 
 ----将新生成的数据文件重名一下----
351SQL> alter database rename file '/u01/app/oracle/product/11.2.0/db_1/dbs/MISSING00007' to '/u01/app/oracle/oradata/yft/luocs01.dbf';

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

posted @ 2013-01-11 12:03 I’m Me! 阅读(1604) 评论(0) 收藏举报

刷新页面返回顶部

I’m Me!

Work hard,Learn hard!

丢失控制文件恢复实验记录--3(当前的控制文件损坏，归档日志文件损坏且备份的控制文件是旧的情况恢复数据库)

公告