Oracle利用dbms_backup_restore进行恢复

2009年10月27日　作者： 大头刚　

需要搭建一个oracle测试环境,把上周末的一个全备拿出来做恢复。把oracle安装到原始库相同目录，把备份COPY到原始库的备份目录,包括数据文件备份、控制文件备份、归档日志备份。用dbms_backup_restore进行恢复。

一、利用dbms_backup_restore来restore控制文件。

sql>startup nomount;
DECLARE
devtype VARCHAR2(256);
done BOOLEAN;
BEGIN
devtype:=sys.dbms_backup_restore.deviceallocate(TYPE=>'',ident=>'t1');
sys.dbms_backup_restore.restoresetdatafile;
sys.dbms_backup_restore.restorecontrolfileto(cfname=>'/u01/app/oracle/oradata/orcl/trol.ctl');
sys.dbms_backup_restore.restorebackuppiece(done=>done,handle=>'/u01/backup/rman_backup/data/orcl_trol_m1ksjgqr_1_1_20091024.dbf',params=>null);
sys.dbms_backup_restore.devicedeallocate;
END;
/

注意把密码文件里的控制文件路径相应修改。

二、利用dbms_backup_restore来restore数据文件

DECLARE
    devtype VARCHAR2(256);
     done BOOLEAN;
     BEGIN
    devtype:=sys.dbms_backup_restore.deviceallocate(TYPE=>'',ident=>'t1');
    sys.dbms_backup_restore.restoresetdatafile;
  sys.dbms_backup_restore.restoredatafileto(dfnumber=>01,toname=>'/u01/app/oracle/oradata/orcl/system01.dbf');
  sys.dbms_backup_restore.restoredatafileto(dfnumber=>02,toname=>'/u01/app/oracle/oradata/orcl/undotbs01.dbf');
  sys.dbms_backup_restore.restoredatafileto(dfnumber=>03,toname=>'/u01/app/oracle/oradata/orcl/sysaux01.dbf');
  sys.dbms_backup_restore.restoredatafileto(dfnumber=>04,toname=>'/u01/app/oracle/oradata/orcl/users01.dbf');
  sys.dbms_backup_restore.restoredatafileto(dfnumber=>05,toname=>'/u01/app/oracle/oradata/orcl/mytest_data.dbf');
  sys.dbms_backup_restore.restoredatafileto(dfnumber=>06,toname=>'/u01/app/oracle/oradata/orcl/mytest_index.dbf');
  sys.dbms_backup_restore.restoredatafileto(dfnumber=>07,toname=>'/u01/app/oracle/oradata/orcl/perfstat.dbf');
  sys.dbms_backup_restore.restoredatafileto(dfnumber=>08,toname=>'/u01/app/oracle/oradata/orcl/tools01.dbf');
  sys.dbms_backup_restore.restoredatafileto(dfnumber=>09,toname=>'/u01/app/oracle/oradata/orcl/test01.dbf');
  sys.dbms_backup_restore.restoredatafileto(dfnumber=>10,toname=>'/u01/app/oracle/oradata/orcl/test2.dbf');
  sys.dbms_backup_restore.restoredatafileto(dfnumber=>11,toname=>'/u01/app/oracle/oradata/orcl/mytest_data02.dbf');
  sys.dbms_backup_restore.restoredatafileto(dfnumber=>12,toname=>'/u01/app/oracle/oradata/orcl/mytest_data03.dbf');
  sys.dbms_backup_restore.restoredatafileto(dfnumber=>13,toname=>'/u01/app/oracle/oradata/orcl/mytest_data04.dbf');
  sys.dbms_backup_restore.restoredatafileto(dfnumber=>14,toname=>'/u01/app/oracle/oradata/orcl/mytest_index02.dbf');
  sys.dbms_backup_restore.restoredatafileto(dfnumber=>15,toname=>'/u01/app/oracle/oradata/orcl/mytest_data05.dbf');
  sys.dbms_backup_restore.restoredatafileto(dfnumber=>16,toname=>'/u01/app/oracle/oradata/orcl/mytest_data06.dbf');
  sys.dbms_backup_restore.restoredatafileto(dfnumber=>17,toname=>'/u01/app/oracle/oradata/orcl/mytest_data07.dbf');
  sys.dbms_backup_restore.restoredatafileto(dfnumber=>18,toname=>'/u01/app/oracle/oradata/orcl/mytest_data08.dbf');
  sys.dbms_backup_restore.restoredatafileto(dfnumber=>19,toname=>'/u01/app/oracle/oradata/orcl/mytest_data09.dbf');
  sys.dbms_backup_restore.restoredatafileto(dfnumber=>20,toname=>'/u01/app/oracle/oradata/orcl/mytest_data10.dbf');
  sys.dbms_backup_restore.restoredatafileto(dfnumber=>21,toname=>'/u01/app/oracle/oradata/orcl/mytest_data11.dbf');
  sys.dbms_backup_restore.restoredatafileto(dfnumber=>22,toname=>'/u01/app/oracle/oradata/orcl/mytest_data12.dbf');
  sys.dbms_backup_restore.restoredatafileto(dfnumber=>23,toname=>'/u01/app/oracle/oradata/orcl/mytest_data13.dbf');
  sys.dbms_backup_restore.restoredatafileto(dfnumber=>24,toname=>'/u01/app/oracle/oradata/orcl/mytest_data14.dbf');
  sys.dbms_backup_restore.restoredatafileto(dfnumber=>25,toname=>'/u01/app/oracle/oradata/orcl/mytest_data15.dbf');
  sys.dbms_backup_restore.restoredatafileto(dfnumber=>26,toname=>'/u01/app/oracle/oradata/orcl/mytest_data16.dbf');
sys.dbms_backup_restore.restorebackuppiece(done=>done,handle=>'/u01/backup/rman_backup/data/orcl_full_f1ksj53s_1_1_20091024.dbf',params=>null);
  sys.dbms_backup_restore.devicedeallocate;
   END;
/


三、recover database

SQL> recover database using backup controlfile;
ORA-00279: change 3497093061 generated AT 10/24/2009 08:36:48 needed FOR thread
1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/orcl/archivelog/2009_10_27/o1_mf_1_633
1_%u_.arc
ORA-00280: change 3497093061 FOR thread 1 IS IN sequence #6331
Specify LOG: {<RET>=suggested | filename | AUTO | CANCEL}
auto

这里特别需要注意，可以根据提示查出sequence为#6331，利用dbms_backup_restore来restore日志文件。

DECLARE
devtype VARCHAR2(256);
done BOOLEAN;
BEGIN
devtype:=sys.dbms_backup_restore.deviceallocate(TYPE=>'',ident=>'t1');
sys.dbms_backup_restore.restoresetarchivedlog(destination=>'/u01/app/oracle/flash_recovery_area');
sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>6331);
sys.dbms_backup_restore.restorebackuppiece
(done=>done,handle=>'/u01/backup/rman_backup/data/orcl_arch_f3kslt0l_1_1_20091025.dbf',params=>null);
sys.dbms_backup_restore.devicedeallocate;
END;
/

前面用auto,最后使用cancel。

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 3497328686 generated at 10/24/2009 09:57:28 needed for thread
1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/orcl/archivelog/2009_10_27/o1_mf_1_633
2_%u_.arc
ORA-00280: change 3497328686 for thread 1 is in sequence #6332
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.


四、open数据库

SQL> ALTER database OPEN resetlogs;
Database altered.
 
记得重建密码
orapwd file=<fname> password=<password> entries=<users>
标签: , , | 分类 : Oracle










  • Comments (0)
  • Trackbacks (0)
Leave a comment Trackback

No comments yet.

No trackback yet.