使用备份控制文件恢复数据库
使用备份控制文件恢复数据库
有备份的control file,但之后我做了ddl操作,导致当前的control file比备份的新,然后control file全部丢失,怎么用备份的control file来恢复数据库?
步骤1:检查当前redo和archive文件 sys@PAN>select group#,sequence#,archived,status,first_change# from v$log; GROUP# SEQUENCE# ARC STATUS FIRST_CHANGE# ---------- ---------- --- ---------------- ------------- 1 4 NO CURRENT 443342 2 2 YES INACTIVE 443335 3 3 YES INACTIVE 443338 sys@PAN>select name from v$archived_log where name is not null; NAME -------------------------------------------------------------------------------- /u01/flash_recovery_area/PAN/archivelog/2013_06_23/o1_mf_1_1_8wg0nhky_.arc /u01/flash_recovery_area/PAN/archivelog/2013_06_23/o1_mf_1_2_8wg0njog_.arc /u01/flash_recovery_area/PAN/archivelog/2013_06_23/o1_mf_1_3_8wg0nlhz_.arc 步骤2:冷备 步骤3: alter database backup controlfile to trace; 生成一个trace文件,可以用于恢复控制文件,它是一个文本文件。 步骤4: alter database backup controlfile to '/backup/pancontrol.bak'; 备份当前的控制文件。 步骤5: 创建一个表空间,然后在此表空间里建表,加数据,目的是让controlfile改变成新的。 create tablespace ts_test02 datafile '/u01/oradata/pan/ts_test02_01.dbf' size 10m; sqlplus scott/tiger create table tb_test02 (id int) tablespace ts_test02; insert into tb_test02 values(1); insert into tb_test02 values(2); insert into tb_test02 values(3); commit; 现在这些改变在哪个日志文件里呢? sys@PAN>select group#,sequence#,archived,status from v$log; GROUP# SEQUENCE# ARC STATUS ---------- ---------- --- ---------------- 1 4 NO CURRENT 2 2 YES INACTIVE 3 3 YES INACTIVE Elapsed: 00:00:00.01 理论上在1号日志中,一会要用这个做恢复。 步骤6 删除当前controlfile 步骤7 shutdown abort 步骤8 用备份controlfile转储。 [oracle@oracle11g pan]$ cp /backup/pancontrol.bak control01.ctl [oracle@oracle11g pan]$ cp /backup/pancontrol.bak control02.ctl 步骤9 起库到mount,若到open会出错: sys@PAN>alter database open; alter database open * ERRORat line 1: ORA-01589: must use RESETLOGS or NORESETLOGS option for database open 步骤10 尝试使用备份的controlfile来恢复数据库。 recover database using backup controlfile; 结果: sys@PAN>recover database using backup controlfile; ORA-00279: change 443470 generated at 06/23/2013 10:23:49 needed for thread 1 ORA-00289: suggestion : /u01/flash_recovery_area/PAN/archivelog/2013_06_23/o1_mf_1_4_%u_.arc ORA-00280: change 443470 for thread 1 is in sequence #4 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} 看下这个目录下有没有这个归档日志,发现没有。也就是说数据库会先找你的归档日志来恢复,但我们知道以前的那些DDL操作被记录到1号联机日志里了,所以我要用1号redo日志来做恢复。 继续: recover database using backup controlfile; ORA-00279: change 443470 generated at 06/23/2013 10:23:49 needed for thread 1 ORA-00289: suggestion : /u01/flash_recovery_area/PAN/archivelog/2013_06_23/o1_mf_1_4_%u_.arc ORA-00280: change 443470 for thread 1 is in sequence #4 Specifylog: {<RET>=suggested | filename | AUTO | CANCEL} /u01/oradata/pan/redo01.log ORA-00283: recovery session canceled due to errors ORA-01244: unnamed datafile(s) added to control file by media recovery ORA-01110: data file 9: '/u01/oradata/pan/ts_test02_01.dbf' ORA-01112: media recovery not started 又出错了!它说没有这个文件,因为日志里记录着ts_test02_01.dbf,但控制文件里却没有,好的,现在我查看下控制文件里那个文件叫什么。 sys@PAN>select file#,status,name from v$datafile; FILE# STATUS NAME ---------- --------------------------------------------------------------------------------------- 1 SYSTEM /u01/oradata/pan/system01.dbf 2 ONLINE /u01/oradata/pan/sysaux01.dbf 3 ONLINE /u01/oradata/pan/lxtbs01.dbf 4 ONLINE /u01/oradata/pan/users01.dbf 5 ONLINE /u01/oradata/pan/undotbs02.dbf 6 ONLINE /u01/oradata/pan/ts_test01.dbf 7 ONLINE /u01/oradata/pan/ts_readonly01.dbf 8 ONLINE /u01/oradata/pan/app2_01.dbf 9 RECOVER /u01/oracle/dbs/UNNAMED00009 9 rows selected. 现在明白了,控制文件里的这个文件名为“/u01/oracle/dbs/UNNAMED00009”,我把它先重命名成正确的。 sys@PAN>alter database rename file '/u01/oracle/dbs/UNNAMED00009' 2 to '/u01/oradata/pan/ts_test02_01.dbf'; 现在再一次恢复,使用1号redo日志。 recover database using backup controlfile; ORA-00279: change 443687 generated at 06/23/2013 10:27:38 needed for thread 1 ORA-00289: suggestion : /u01/flash_recovery_area/PAN/archivelog/2013_06_23/o1_mf_1_4_%u_.arc ORA-00280: change 443687 for thread 1 is in sequence #4 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} /u01/oradata/pan/redo01.log Log applied. Media recovery complete. 果然,恢复成功! 步骤11 alter database open resetlogs; 步骤12 冷备