Use dbms_backup_restore to restore the database

Use dbms_backup_restore to restore the database test environment: OEL + 11.2.0.1 Instance name: orcl2DBID: 1336959433 scenario: Oracle database storage is broken, data files and control files are all lost, only the backup set of data files, there are no control files in the backup set. (Of course, oracle10g and 11g are automatically backing up data files.) to back up the database, perform full backup [oracle @ DBA2 backup] $ rman target/Recovery Manager: release 11.2.0.1.0-Production on Sun Sep 22 00:05:52 2013 Copyright (c) 1982,200 9, Oracle and/or its affiliates. all rights reserved. connected to target database: ORCL2 (DBID = 1336959433) RMAN> backup database format'/backup/orcl _ % U' plus archivelog; Starting backup at 22-SEP-13current log archiv Edusing target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID = 38 device type = DISKchannel ORA_DISK_1: starting archived log backup setchannel ORA_DISK_1: specifying archived log) in backup setinput archived log thread = 1 sequence = 1 RECID = 16 STAMP = 826760691 input archived log thread = 1 sequence = 2 RECID = 9 STAMP = 826760691 input archived log thread = 1 Sequence = 3 RECID = 8 STAMP = 826760691 input archived log thread = 1 sequence = 4 RECID = 10 STAMP = 826760691 input archived log thread = 1 sequence = 5 RECID = 11 STAMP = 826760691 input archived log thread = 1 sequence = 6 RECID = 17 STAMP = 826760691 input archived log thread = 1 sequence = 7 RECID = 19 STAMP = 826760691 input archived log thread = 1 sequence = 8 RECID = 18 STAMP = 826760691 input archived log thread = 1 sequence = 9 RECID = 13 ST AMP = 826760691 input archived log thread = 1 sequence = 10 RECID = 12 STAMP = 826760691 input archived log thread = 1 sequence = 11 RECID = 15 STAMP = 826760691 input archived log thread = 1 sequence = 12 RECID = 14 STAMP = 826760691 input archived log thread = 1 sequence = 13 RECID = 21 STAMP = 826760691 input archived log thread = 1 sequence = 14 RECID = 22 STAMP = 826760691 input archived log thread = 1 sequence = 15 RECID = 20 STAMP = 826760691indium Ut archived log thread = 1 sequence = 16 RECID = 7 STAMP = 826760691 input archived log thread = 1 sequence = 17 RECID = 4 STAMP = 826760691 input archived log thread = 1 sequence = 18 RECID = 5 STAMP = 826760691 input archived log thread = 1 sequence = 19 RECID = 1 STAMP = 826760691 input archived log thread = 1 sequence = 20 RECID = 2 STAMP = 826760691 channel ORA_DISK_1: starting piece 1 at 22-SEP-13channel ORA_DISK_1: finished piece 1 22-SEP-13piece handle =/u/oracle/flash_recovery_area/ORCL2/backupset/2013_09_22/o1_mf_annnn_TAG20130922T000600_93vjz8nh _. bkp tag = TAG20130922T000600 comment = NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00: 01: 35 channel ORA_DISK_1: starting archived log backup setchannel ORA_DISK_1: specifying archived log (s) in backup setinput archived log thread = 1 sequence = 1 RECID = 6 STAMP = 826760691indium Ut archived log thread = 1 sequence = 2 RECID = 3 STAMP = 826760691 channel ORA_DISK_1: starting piece 1 at 22-SEP-13channel ORA_DISK_1: finished piece 1 at 22-SEP-13piece handle =/u/oracle/flash_recovery_area/ORCL2/backupset/2013_09_22/o1_mf_annnn_TAG20130922T000600_93vk288l _. bkp tag = TAG20130922T000600 comment = NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00: 00: 01 channel ORA_DISK_1: starting Rchived log backup setchannel ORA_DISK_1: specifying archived log (s) in backup setinput archived log thread = 1 sequence = 1 RECID = 23 STAMP = 826761959 channel ORA_DISK_1: starting piece 1 at 22-SEP-13channel ORA_DISK_1: finished piece 1 at 22-SEP-13piece handle =/u/oracle/flash_recovery_area/ORCL2/backupset/2013_09_22/o1_mf_annnn_TAG20130922T000600_93vk29cw _. bkp tag = TAG20130922T000600 comment = NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00: 00: 01 Finished backup at 22-SEP-13 Starting backup at large channel ORA_DISK_1channel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile (s) in backup setinput datafile file number = 00001 name =/oradata/orcl2/system01.dbfinput datafile file number = 00002 name =/oradata/orcl2/undotbs01.dbfinput datafile file num Ber = 00003 name =/oradata/orcl2/sysaux01.dbfinput datafile file number = 00005 name =/oradata/orcl2/seven. dbfinput datafile file number = 00004 name =/oradata/orcl2/users01.dbfchannel ORA_DISK_1: starting piece 1 at 22-SEP-13channel ORA_DISK_1: finished piece 1 at 22-SEP-13piece handle =/backup/orcl_04okeoqa_1_1 tag = TAG20130922T000738 comment = NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:02: 05 channel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile (s) in backup interval current control file in backup setincluding current SPFILE in backup setchannel ORA_DISK_1: starting piece 1 at least ORA_DISK_1: finished piece 1 at 22-SEP-13piece handle =/backup/orcl_05okeou7_1_1 tag = TAG20130922T000738 comment = NONEchannel ORA_DISK_1: backup set com Plete, elapsed time: 00: 00: 01 Finished backup at 22-SEP-13 Starting backup at your log archivedusing channel parameter ORA_DISK_1: starting archived log backup setchannel ORA_DISK_1: specifying archived log (s) in backup setinput archived log thread = 1 sequence = 2 RECID = 24 STAMP = 826762186 channel ORA_DISK_1: starting piece 1 at 22-SEP-13channel ORA_DISK_1: finished piece 1 at 22-SEP -13 piece handle =/u/oracle/flash_recovery_area/ORCL2/backupset/2013_09_22/o1_mf_annnn_TAG20130922T000946_93vk6b6o _. bkp tag = TAG20130922T000946 comment = NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00: 00: 01 Finished backup at 22-SEP-13 RMAN> View the backup file: [oracle @ DBA2 backup] $ ls-rw-r ----- 1 oracle oinstall 1182695424 Sep 22 orcl_04okeoqa_1_1-rw-r ----- 1 oracle oinstall 10158080 Sep 22 9 orcl_05okeou7_1_1 orcl_04okeoqa_1_1 is the backup set of the data file, and orcl_05okeou7_1_1 is the backup set of the control file and parameter file. Delete data files and control files in Manufacturing Experiment scenarios: [oracle @ DBA2 oradata] $ cd/oradata/orcl2/[oracle @ DBA2 orcl2] $ lscontrol01.ctl redo02.log seven. dbf system01.dbf temp02.dbf users01.dbfredo01. log redo03.log sysaux01.dbf temp01.dbf restart [oracle @ DBA2 orcl2] $ rm * [oracle @ DBA2 orcl2] $ ls [oracle @ DBA2 orcl2] $ cd/u/oracle/flash_recovery_area/orcl2 /[ oracle @ DBA2 orcl2] $ lscontrol02.ctl [oracle @ DBA2 orcl2] $ rm * [oracle @ DBA2 orcl2] $ count Data Files and control files are now all deleted, and the database will crash. If you start to restore the existing parameter files, use the previous parameter files. If not, manually modify init. ora file, first start the instance to the nomount state; [oracle @ DBA2 dbs] $ SQL SQL * Plus: Release 11.2.0.1.0 Production on Sun Sep 22 00:18:50 2013 Copyright (c) 1982,200 9, oracle. all rights reserved. connected to an idle instance. SQL> startup nomount pfile = '/u/oracle/product/11.2/dbs/initorcl2.ora' ORACLE instance started. total System Global Area 418484224 bytes Fixed Size 1336932 bytesVariable Size 327158172 bytesDatabase Buffers 83886080 bytesRedo Buffers 6103040 bytesSQL> If the backup set contains control files, you can restore the control file Before restoring the data file; the restore control file uses dbms_backup_restore to construct the control file from the backup set: 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 => '/oradata/orcl2/control01.ctl'); 8 sys. dbms_backup_restore.restorebackuppiece (done => done, handle => '/backup/orcl_05okeou7_1_1', params => null); 9 sys. dbms_backup_restore.devicedeallocate; 10 end; 11/PL/SQL procedure successfully completed. SQL> the control file is constructed in the path. [Oracle @ DBA2 orcl2] $ ls-al-rw-r ----- 1 oracle oinstall 10076160 Sep 22 control01.ctl can now start the instance to the mount state. SQL> alter database mount; Database altered. view the file # and absolute path of the data file: SQL> select file #, name from v $ datafile; FILE # NAME ---------- kernel 1/oradata/orcl2/system01.dbf 2/oradata/orcl2/kernel 3/oradata/orcl2/kernel 4/oradata/orcl2/users01.dbf 5/oradata/orcl2/seven. dbf OK! The control file is restored successfully and the file # and name of the data file are known. After restoring the data file, we know the file # and name of the data file, or use dbms_backup_restore to restore the data file from the backup set: 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, 8 toname => '/oradata/orcl2/system01.dbf'); 9 sys. dbms_backup_restore.restoreDatafileTo (dfnumber => 02, 10 toname => '/oradata/orcl2/undotbs01.dbf'); 11 sys. dbms_backup_restore.restoreDatafileTo (dfnumber => 03, 12 toname => '/oradata/orcl2/sysaux01.dbf'); 13 sys. dbms_backup_restore.restoreDatafileTo (dfnumber => 04, 14 toname => '/oradata/orcl2/users01.dbf'); 15 sys. dbms_backup_restore.restoreDatafileTo (dfnumber => 05, 16 toname => '/oradata/orcl2/seven. dbf'); 17 sys. dbms_backup_restore.restoreBackupPiece (done => d One, 18 handle => '/backup/orcl_04okeoqa_1_1', params => null); 19 sys. dbms_backup_restore.deviceDeallocate; 20 end; 21/PL/SQL procedure successfully completed. check whether the data file has been restored to the specified directory in the data file path: [oracle @ DBA2 orcl2] $ ls-altotal 1480808drwxrwxr-x 2 oracle oinstall 4096 Sep 22. drwxrwxr-x 4 oracle oinstall 4096 Sep 21 .. -rw-r ----- 1 oracle oinstall 10076160 Sep 22 control01.ctl-rw-r ----- 1 ora Cle oinstall 52436992 Sep 22 seven. dbf-rw-r ----- 1 oracle oinstall 94380032 Sep 22 sysaux01.dbf-rw-r ----- 1 oracle oinstall 723525632 Sep 22 system01.dbf-rw-r ----- 1 oracle oinstall 629153792 Sep 22 undotbs01.dbf -rw-r ----- 1 oracle oinstall 5251072 Sep 22 users01.dbf indeed, the data file has been restored to the specified directory (there is no temporary tablespace because the temp temporary tablespace is not backed up during rman backup ); the backup set has no control file to restore the data file using dbms_backup_restore to restore the data file from the backup set. Of course I do not know the file # and name of the data file here. Generally, the name will be known, but file # cannot be determined. Generally, system is 01, undotbs is 02, and sysaux is 03, the users number is 04, and others can only be guessed or tried one by one. Of course, it is good to save the information before. 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, 8 toname => '/oradata/orcl2/system01.dbf'); 9 sys. dbms_backup_restore.restoreDatafileTo (dfnumber => 02, 10 toname => '/oradata/orcl2/undotbs01.dbf'); 11 sys. dbms_backu P_restore.restoreDatafileTo (dfnumber => 03, 12 toname => '/oradata/orcl2/sysaux01.dbf'); 13 sys. dbms_backup_restore.restoreDatafileTo (dfnumber => 04, 14 toname => '/oradata/orcl2/users01.dbf'); 15 sys. dbms_backup_restore.restoreDatafileTo (dfnumber => 05, 16 toname => '/oradata/orcl2/seven. dbf'); 17 sys. dbms_backup_restore.restoreBackupPiece (done => done, 18 handle => '/backup/orcl_04okeoqa_1_1', params => null); 19 Sys. dbms_backup_restore.deviceDeallocate; 20 end; 21/PL/SQL procedure successfully completed. check whether the data file has been restored to the specified directory in the data file path: [oracle @ DBA2 orcl2] $ ls-altotal 1480808drwxrwxr-x 2 oracle oinstall 4096 Sep 22. drwxrwxr-x 4 oracle oinstall 4096 Sep 21 .. -rw-r ----- 1 oracle oinstall 10076160 Sep 22 control01.ctl-rw-r ----- 1 oracle oinstall 52436992 Sep 22 seven. dbf-rw-r ----- 1 oracl E oinstall 94380032 Sep 22 sysaux01.dbf-rw-r ----- 1 oracle oinstall 723525632 Sep 22 system01.dbf-rw-r ----- 1 oracle oinstall 629153792 Sep 22 undotbs01.dbf-rw-r ----- 1 oracle oinstall 5251072 Sep 22 users01.dbf is true, the data file has been restored to the specified directory (no temporary tablespace, because the temp temporary tablespace is not backed up during rman backup). To manually create a control file, create a control file. This step is very simple, the premise is that you must know which data files are available: create controlfile reuse database "ORCL2" RESETLOGS ARCHIVELOG MAXLOGF ILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 logfile group 1'/oradata/orcl2/redo01.log 'size 50 m blocksize 512, GROUP 2'/oradata/orcl2/redo02.log 'SIZE 50 m blocksize 512, GROUP 3 '/oradata/orcl2/redo03.log 'size 50 m blocksize 512 datafile'/oradata/orcl2/system01.dbf','/oradata/orcl2/sysaux01.dbf ', '/oradata/orcl2/undotbs01.dbf','/oradata/orcl2/users01.dbf', '/oradat A/orcl2/seven. dbf 'character SET AL32UTF8; SQL> @/home/oracle/controlfile.txt Control file created. SQL> after the control file is restored, the database is started to the mount state and Incomplete recovery is performed. Incomplete recovery now execute Incomplete recovery: SQL> recover database using backup controlfile until cancel; ORA-00279: change 1246394 generated at 09/22/2013 00:07:38 needed for thread 1ORA-00289: suggestion: /u/oracle/flash_recovery_area/ORCL2/archivelog/2013_09_22/o1_mf_1_2_93vk6b0w _. arcORA-00280: change 1246394 for thread 1 is in sequence #2 Specify log: {<RET> = suggested | filename | AUTO | CANCEL} cancelMedia recovery canceled. SQL> after the restoration is completed, the instance is opened. Because the redo file is lost and the execution is incomplete, the resetlogs parameter must be added: SQL> alter database open resetlogs; Database altered. SQL> check whether the redo file is rebuilt? [Oracle @ DBA2 orcl2] $ ls-altotal 1636644drwxrwxr-x 2 oracle oinstall 4096 Sep 22. drwxrwxr-x 4 oracle oinstall 4096 Sep 21 .. -rw-r ----- 1 oracle oinstall 10076160 Sep 22 control01.ctl-rw-r ----- 1 oracle oinstall 52429312 Sep 22 58 redo01.log-rw-r ----- 1 oracle oinstall 52429312 Sep 22 redo02.log- rw-r ----- 1 oracle oinstall 52429312 Sep 22 redo03.log-rw-r ----- 1 orac Le oinstall 52436992 Sep 22 seven. dbf-rw-r ----- 1 oracle oinstall 94380032 Sep 22 sysaux01.dbf-rw-r ----- 1 oracle oinstall 723525632 Sep 22 system01.dbf-rw-r ----- 1 oracle oinstall 10493952 Sep 22 temp01.dbf -rw-r ----- 1 oracle oinstall 10493952 Sep 22 temp02.dbf-rw-r ----- 1 oracle oinstall 629153792 Sep 22 undotbs01.dbf-rw-r ----- 1 oracle oinstall 5251072 Sep 22 Users01.dbf is good, the redo file is also rebuilt, and the instance is started. However, in the experiment, only one control file is used. Considering that the control file must be reused, the instance must be closed, the parameter file must be modified, and a control file must be copied.