试用dbms_backup_restore包

54xw

本来想试一试10g RMAN里的catalog命令，于是我对现在测试库做了一个全库备份并将我将控制文件备份到文件：

 

RMAN> backup database plus archivelog delete input;

SYS@test SQL> alter database backup controlfile to trace;

Database altered.

 

我将所有控制文件，数据文件和重做日志全部删除。我假定我的备份集中没有控制文件，我尝试通过手工创建控制文件，然后使用catalog将备份集catalog到新建的日志文件中去。可是失败了，因为没有数据文件，无法手工创建控制文件：

 

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Aug 9 16:19:37 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@test SQL> CREATE CONTROLFILE REUSE DATABASE "TEST" RESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 '/u01/app/oracle/oradata/test/redo01.log'  SIZE 50M,
  9    GROUP 2 '/u01/app/oracle/oradata/test/redo02.log'  SIZE 50M,
 10    GROUP 3 '/u01/app/oracle/oradata/test/redo03.log'  SIZE 50M
 11  -- STANDBY LOGFILE
 12  DATAFILE
 13    '/u01/app/oracle/oradata/test/system01.dbf',
 14    '/u01/app/oracle/oradata/test/undotbs01.dbf',
 15    '/u01/app/oracle/oradata/test/sysaux01.dbf',
 16    '/u01/app/oracle/oradata/test/users01.dbf',
 17    '/u01/app/oracle/oradata/test/test01.dbf',
 18    '/u01/app/oracle/oradata/test/test02.dbf',
 19    '/u01/app/oracle/product/10.2.0/dbs/t1_01.ora',
 20    '/u01/app/oracle/product/10.2.0/dbs/undots02.ora'
 21  CHARACTER SET AL32UTF8
 22  ;
CREATE CONTROLFILE REUSE DATABASE "TEST" RESETLOGS  ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01565: error in identifying file
'/u01/app/oracle/oradata/test/system01.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

 

看来必须先将数据文件恢复回来，在这种情况下，我尝试使用dbms_backup_restore包，先将数据文件恢复回来：

 

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Aug 9 16:20:11 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@test SQL> declare
  2  devtype varchar2(256);
  3  done boolean;
  4  begin
  5  devtype:=sys.dbms_backup_restore.deviceallocate(type=>'',ident=>'t1');
  6  sys.dbms_backup_restore.restoresetdatafile;
  7  sys.dbms_backup_restore.restoredatafileto(dfnumber=>01,toname=>'/u01/app/oracle/oradata/test/system01.dbf');
  8  sys.dbms_backup_restore.restoredatafileto(dfnumber=>03,toname=>'/u01/app/oracle/oradata/test/sysaux01.dbf');
  9  sys.dbms_backup_restore.restoredatafileto(dfnumber=>06,toname=>'/u01/app/oracle/oradata/test/test02.dbf');
 10  sys.dbms_backup_restore.restoredatafileto(dfnumber=>07,toname=>'/u01/app/oracle/product/10.2.0/dbs/t1_01.ora');
 11  sys.dbms_backup_restore.restorebackuppiece(done=>done,handle=>'/u01/rman_back/TEST/TEST_20110809_47mjio1k_1_1',params=>null);
 12  sys.dbms_backup_restore.devicedeallocate;
 13  end;
 14  /
PL/SQL procedure successfully completed.

SYS@test SQL> declare
  2  devtype varchar2(256);
  3  done boolean;
  4  begin
  5  devtype:=sys.dbms_backup_restore.deviceallocate(type=>'',ident=>'t1');
  6  sys.dbms_backup_restore.restoresetdatafile;
  7  sys.dbms_backup_restore.restoredatafileto(dfnumber=>04,toname=>'/u01/app/oracle/oradata/test/users01.dbf');
sys.dbms_backup_restore.restoredatafileto(dfnumber=>02,toname=>'/u01/app/oracle/oradata/test/undotbs01.dbf');
sys.dbms_backup_restore.restoredatafileto(dfnumber=>05,toname=>'/u01/app/oracle/oradata/test/test01.  8  dbf');
sys.dbms_backup_restore.restoredatafileto(dfnumber=>08,toname=>'/u01/app/oracle/product/10.2.0/dbs/undots02.ora');
sys.dbms_backup_restore.rest  9  orebackuppiece(done=>done,handle=>'/u01/rman_back/TEST/TEST_20110809_46mjio1k_1_1',params=>null);
sys.dbms_backup_restore.devicedealloca 10  te;
end;
/ 11   12   13   14

PL/SQL procedure successfully completed.

 

注意：使用这个脚本必须知道每一个数文件与备份集的关系，不然会出错，错误代码忘记拷贝了。可能通过备份时的日志查看一一对应的关系。所以再次证明了保存好备份日志也是一件非常必要的事。

 

实验做到这里，本来打算手工重建控制文件的，然后使用catalog命令。但我并没有这样做，而是干脆把使用dbms_backup_restore包的实验做到底了。

 

恢复控制文件：

 

SYS@test SQL> declare
  2  devtype varchar2(256);
  3  done boolean;
  4  begin
  5  devtype:=sys.dbms_backup_restore.deviceallocate(type=>'',ident=>'t1');
  6  sys.dbms_backup_restore.restoresetdatafile;
  7  sys.dbms_backup_restore.restoreControlfileTo (cfname=>'/u01/app/oracle/oradata/test/control01.ctl');
  8  sys.dbms_backup_restore.restorebackuppiece(done=>done,handle=>'/u01/rman_back/TEST/c-2012049283-20110809-00',params=>null);
sys.dbms_backup_restore.devicedeallocate;
end;
/
  9   10   11
PL/SQL procedure successfully completed.

 

恢复归档日志：

 

SYS@test SQL> declare
  2  devtype varchar2(256);
  3  done boolean;
  4  begin
  5  devtype:=sys.dbms_backup_restore.deviceallocate(type=>'',ident=>'t1');
  6  sys.dbms_backup_restore.restoresetarchivedlog(destination=>'/u01/app/oracle/flash_recovery_area/TEST/archivelog/');
  7  sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>133);
  8  sys.dbms_backup_restore.restorebackuppiece(done=>done,handle=>'/u01/rman_back/TEST/TEST_20110809_48mjio41_1_1',params=>null);
  9  sys.dbms_backup_restore.devicedeallocate;
 10  end;
 11  /

PL/SQL procedure successfully completed.

 

 

这样RESTORE的工作已经完成了，可以尝试mount数据库并恢复：

 

SYS@test SQL> alter database mount;

Database altered.

SYS@test SQL> recover database using backup controlfile until cancel
ORA-00279: change 11544987 generated at 08/09/2011 15:41:40 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/TEST/archivelog/2011_08_09/o1_mf_1_133_%u_.a
rc
ORA-00280: change 11544987 for thread 1 is in sequence #133

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.

SYS@test SQL> alter database open resetlogs;

Database altered.

这样整个使用dbms_backup_restore来做数据库恢复的过程就完成了。然而个人感觉真正的实战中应该不会这样用，因为1）既然备份集里面用控制文件的备份，为何不直接使用retore controlfile from “...”？2）使用dbms_backup_restore来恢复数据文件可比restore database / datafile 来的麻烦多了。

 

但当没有任何控制文件的备份时，dbms_backup_restore还是很有用的。思路应该是通过dbms_backup_restore恢复数据文件，然后手工方式创建控制文件，使用catalog将备份集catalog进控制文件，这样用于恢复数据库的archivelog的备份也可catalog进来。假如archivelog也没有，那么只能设定参数，允许在不一致的状态打开数据库了。有机会还是要找时间测试一下。

 

下面将catalog的几个尝用命令摘抄如下：

（摘自http://www.ningoo.net/html/2007/oracle10g_rman_catalog_command.html）

扫描备份片

CATALOG BACKUPPIECE ‘?/oradata/01dmsbj4_1_1.bcp’;

扫描归档日志

CATALOG ARCHIVELOG ‘?/oradata/archive1_30.dbf’, ‘?/oradata/archive1_31.dbf’;

扫描数据文件拷贝，并且将其做为增量备份的level 0

CATALOG DATAFILECOPY ‘?/oradata/users01.bak’ LEVEL 0;

扫描控制文件拷贝

CATALOG CONTROLFILECOPY ‘controlfilecopy.ctl’

扫描整个目录，如果备份片或者归档日志文件太多，可以放到一个目录中，一次性扫描就行

CATALOG START WITH ‘/tmp/arch_logs’;

扫描闪回恢复区

CATALOG RECOVERY AREA NOPROMPT;