Use of Dbms_backup_restore Packages

Yesterday to do an experiment, the results of the database has been broken, when trying to recover when actually reported RMAN-06026 error. Recall that the _allow_resetlogs_corruption parameter was used in an attempt to recover, and after Resetlogs, Oracle uses the current control file to not allow recovery from this historical backup set.
Because I didn't use catalog, I tried to use Dbms_backup_restore for recovery.
1. Error message
We see that although list backup can display a backup set, it cannot be recovered, and the error is rman-06026,rman-06026.

[Oracle@jumper oradata]$ rman target/recovery manager:release 9.2.0.4.0-production Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved. Connected to target Database:conner (dbid=3152029224) rman> restore database; Starting restore at 11-jun-05 using target database controlfile instead of recovery catalog allocated Channel:ora_disk_1 Channel ora_disk_1:sid=11 Devtype=disk RMAN-00571: =========================================================== RMAN-00569: =============== ERROR message STACK follows =============== RMAN-00571: ================================== ========================= rman-03002:failure of Restore command at 06/11/2005 01:19:01 rman-06026:some targets not found -Aborting restore Rman-06023:no Backup or copy of DataFile 3 found to restore Rman-06023:no Backup or copy of DataFile 2 found to restore Rman-06023:no Backup or copy of DataFile 1 found to restore rman> list backup; List of Backup Sets =================== BS Key TypeLV Size Device Type Elapsed Time completion time------------------------------------------------------------- Full 1G DISK 00:03:20 09-jun-05 BP key:13 status:available tag:tag2005 0609t173346 Piece Name:/opt/oracle/product/9.2.0/dbs/0ggmiabq_1_1 SPFILE included:modification time:08-jun-05 List of datafiles in Backup set File LV Type Ckp SCN Ckp time Name--------------------------------- 1 full 240560269 09-jun-05/opt/oracle/oradata/conner/system01.dbf 2 full 240560269 09-jun-05/opt/oracle/ ORADATA/CONNER/UNDOTBS01.DBF 3 Full 240560269 09-jun-05/opt/oracle/oradata/conner/users01.dbf RMAN> exit R Ecovery Manager complete.

2. Use Dbms_backup_restore for recovery
Dbms_backup_restore is a very powerful package that can be used under database Nomount to read various files from a backup set.
This example uses the following script:

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=> '/opt/oracle/oradata/conner/system01.dbf ') ); Sys.dbms_backup_restore.restoreDatafileTo (dfnumber=>02,toname=> '/opt/oracle/oradata/conner/undotbs01.dbf '); Sys.dbms_backup_restore.restoreDatafileTo (dfnumber=>03,toname=> '/opt/oracle/oradata/conner/users01.dbf ') ; Sys.dbms_backup_restore.restoreBackupPiece (done=>done,handle=> '/opt/oracle/product/9.2.0/dbs/0ggmiabq_1_ 1 ', params=>null); Sys.dbms_backup_restore.deviceDeallocate; End; /

3. Implementation of recovery

[Oracle@jumper conner]$ Sqlplus "/As SYSDBA" Sql*plus:release 9.2.0.4.0-production on Sat June 01:24:34-Copyr Ight (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to a idle instance. sql> startup Nomount; ORACLE instance started. Total System Global area 101782828 bytes Fixed size 451884 bytes Variable Size 37748736 by TEs Database buffers 62914560 bytes Redo buffers 667648 bytes sql> DECLARE 2 devtype var CHAR2 (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=> '/opt/oracle/oradata/conner/system01.dbf '); 8 Sys.dbms_backup_restore.restoreDatafileTo (dfnumber=>02,toname=> '/opt/oracle/oradata/conner/ Undotbs01.dbf '); 9 Sys.dbms_backup_restore.restoreDatafileTo (Dfnumber=>03,toname=> '/opt/oracle/oradata/conner/users01.dbf '); Sys.dbms_backup_restore.restoreBackupPiece (done=>done,handle=> '/opt/oracle/product/9.2.0/dbs/0ggmiabq_ 1_1 ', params=>null); One sys.dbms_backup_restore.deviceDeallocate; End; 13/pl/sql procedure successfully completed. Sql>

At this point, the file is read from the backup set.
4. Recovery control File
Because of the effect, there is no backup control files, so had to rebuild the control file.

Sql> ALTER DATABASE mount; Database altered. sql> ALTER DATABASE backup Controlfile to trace; Database altered.

To locate the trace file, edit and execute the Rebuild control file part:

[Oracle@jumper oracle]$ Sqlplus "/As SYSDBA" Sql*plus:release 9.2.0.4.0-production on Sat June 01:30:50-Copyri Ght (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to:oracle9i Enterprise Edition release 9.2.0.4.0-production and the partitioning option Jserver release 9.2. 0.4.0-production sql> shutdown Immediate; Ora-01109:database not open database dismounted. ORACLE instance shut down. sql> startup Nomount; ORACLE instance started. Total System Global area 101782828 bytes Fixed size 451884 bytes Variable Size 37748736 by TEs Database buffers 62914560 bytes Redo buffers 667648 bytes sql> set echo on sql> @ctl S ql> sql> CREATE controlfile reuse DATABASE "CONNER" Resetlogs archivelog 2--SET STANDBY to maximize Performa NCE 3 maxlogfiles 5 4 maxlogmembers 3 5 maxdatafiles 6 maxinstances 1 7 maxloghisto RY 1361 8 LOGFILE 9 Group 1 '/opt/oracle/oradata/conner/redo01.log ' SIZE 10M, Group 2 '/opt/oracle/oradata/conner/redo02.log ' Size 10M, GROUP 3 '/opt/oracle/oradata/conner/redo03.log ' size 10M-STANDBY LOGFILE datafile 14 ' /opt/oracle/oradata/conner/system01.dbf ', '/opt/oracle/oradata/conner/undotbs01.dbf ', '/opt/oracle/oradata/c ONNER/USERS01.DBF ' CHARACTER SET ZHS16GBK 18; Control file created.

5. Implementation of recovery

sql> Recover database; Ora-00283:recovery session canceled due to errors ora-01610:recovery using the BACKUP controlfile option must is done sql> Recover database using Backup controlfile until cancel; Ora-00279:change 240560269 generated at 06/09/2005 17:33:48 needed for thread 1 ora-00289:suggestion:/opt/oracle/orada TA/CONNER/ARCHIVE/1_7.DBF ora-00280:change 240560269 for thread 1 was in sequence #7 specify log: {=suggested | filename | AUTO | CANCEL Auto Ora-00279:change 240600632 generated at 06/10/2005 10:42:26 for thread 1 needed: ora-00289:suggestion T/ORACLE/ORADATA/CONNER/ARCHIVE/1_8.DBF ora-00280:change 240600632 for thread 1 was in sequence #8 ora-00278:log file '/O PT/ORACLE/ORADATA/CONNER/ARCHIVE/1_7.DBF ' no longer needed for this recovery specify log: {=suggested | AUTO | CANCEL Auto Ora-00279:change 240620884 generated at 06/10/2005 10:45:42 for thread 1 needed: ora-00289:suggestion /ORACLE/ORADATA/CONNER/ARCHIVE/1_9.DBF ORA-00280:change 240620884 for thread 1 was in sequence #9 ora-00278:log file '/opt/oracle/oradata/conner/archive/1_8.dbf ' no Longer needed for this recovery ora-00283:recovery sessions canceled due to errors error code, ora-00600:internal Ents: [3020], [4242465], [1], [9], [a], [272], [], [] Ora-10567:redo are inconsistent with data blocks (file# 1, block# 4 8161) ora-10564:tablespace SYSTEM ora-01110:data file 1: '/opt/oracle/oradata/conner/system01.dbf ' Ora-10560:block typ E ' DATA SEGMENT header-unlimited ' ora-01112:media recovery not started sql> the database using Backup control file until cancel; Ora-00279:change 240620949 generated at 06/10/2005 10:45:44 needed for thread 1 ora-00289:suggestion:/opt/oracle/orada TA/CONNER/ARCHIVE/1_9.DBF ora-00280:change 240620949 for thread 1 was in sequence #9 specify log: {=suggested | filename | AUTO | Cancel} Cancel Media recovery cancelled. sql> ALTER DATABASE open resetlogs; Database altered. Sql> Select Name from V$datafile; NAME------------------------------------------------------------/opt/oracle/oradata/conner/system01.dbf/opt/ ORACLE/ORADATA/CONNER/UNDOTBS01.DBF/OPT/ORACLE/ORADATA/CONNER/USERS01.DBF sql>

This completes the recovery.