首页
登录 | 注册

Oracle使用备份控制文件恢复数据库

有备份的control file,但之后我做了ddl操作,导致当前的controlfile比备份的新,然后control file全部丢失,怎么用备份的controlfile来恢复数据库?

步骤1:检查当前redo和archive文件

[email protected]>selectgroup#,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

 

[email protected]>select namefrom v$archived_log where nameis 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 controlfileto trace;

生成一个trace文件,可以用于恢复控制文件,它是一个文本文件。

步骤4

alter database backup controlfileto '/backup/pancontrol.bak';

备份当前的控制文件。

步骤5

创建一个表空间,然后在此表空间里建表,加数据,目的是让controlfile改变成新的。

create tablespace ts_test02 datafile'/u01/oradata/pan/ts_test02_01.dbf' size10m;

sqlplus scott/tiger

create table tb_test02 (idint) tablespace ts_test02;

insert into tb_test02 values(1);

insert into tb_test02 values(2);

insert into tb_test02 values(3);

commit;

现在这些改变在哪个日志文件里呢?

[email protected]>selectgroup#,sequence#,archived,statusfrom 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转储。

[[email protected] pan]$ cp/backup/pancontrol.bak control01.ctl

[[email protected] pan]$ cp/backup/pancontrol.bak control02.ctl

步骤9

起库到mount,若到open会出错:

[email protected]>alter databaseopen;

alter databaseopen

*

ERRORat line1:

ORA-01589: mustuse RESETLOGS or NORESETLOGSoption for databaseopen

步骤10

尝试使用备份的controlfile来恢复数据库。

recover database using backup controlfile;

结果:

[email protected]>recover databaseusing backup controlfile;

ORA-00279: change443470 generated at06/23/201310:23:49 neededfor thread 1

ORA-00289: suggestion: /u01/flash_recovery_area/PAN/archivelog/2013_06_23/o1_mf_1_4_%u_.arc

ORA-00280: change443470 for thread1 is in sequence #4

Specify log:{<RET>=suggested| filename | AUTO| CANCEL}

 

看下这个目录下有没有这个归档日志,发现没有。也就是说数据库会先找你的归档日志来恢复,但我们知道以前的那些DDL操作被记录到1号联机日志里了,所以我要用1号redo日志来做恢复。

 

继续:

recover database using backup controlfile;

ORA-00279: change443470 generated at06/23/201310:23:49 neededfor thread 1

ORA-00289: suggestion: /u01/flash_recovery_area/PAN/archivelog/2013_06_23/o1_mf_1_4_%u_.arc

ORA-00280: change443470 for thread1 is in sequence #4

Specifylog:{<RET>=suggested| filename | AUTO| CANCEL}

/u01/oradata/pan/redo01.log

ORA-00283: recoverysession canceled due to errors

ORA-01244: unnamed datafile(s) added to control fileby media recovery

ORA-01110:data file9:'/u01/oradata/pan/ts_test02_01.dbf'

ORA-01112: media recoverynot started

又出错了!它说没有这个文件,因为日志里记录着ts_test02_01.dbf,但控制文件里却没有,好的,现在我查看下控制文件里那个文件叫什么。

[email protected]>selectfile#,status,namefrom 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”,我把它先重命名成正确的。

[email protected]>alter databaserename file'/u01/oracle/dbs/UNNAMED00009'

2 to'/u01/oradata/pan/ts_test02_01.dbf';

现在再一次恢复,使用1号redo日志。

recover database using backup controlfile;

ORA-00279: change443687 generated at06/23/201310:27:38 neededfor thread 1

ORA-00289: suggestion: /u01/flash_recovery_area/PAN/archivelog/2013_06_23/o1_mf_1_4_%u_.arc

ORA-00280: change443687 for thread1 is in sequence #4

Specify log:{<RET>=suggested| filename | AUTO| CANCEL}

/u01/oradata/pan/redo01.log

Log applied.

Media recovery complete.

果��,恢复成功!

步骤11

alter databaseopen resetlogs;

步骤12 冷备



2019 monjeep.com webmaster#monjeep.com
10 q. 0.010 s.
京ICP备10005923号