如何調用dbms_backup_restore恢復數據庫

本篇文章給大家分享的是有關如何調用dbms_backup_restore恢復數據庫，小編覺得挺實用的，因此分享給大家學習，希望大家閱讀完這篇文章后可以有所收獲，話不多說，跟著小編一起來看看吧。

一、查看RMAN備份參數

[oracle@oraserver ~]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Mon Aug 15 13:21:54 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: MYDATA (DBID=305115346)

RMAN> show all;

using target database control file instead of recovery catalog

RMAN configuration parameters are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

CONFIGURE BACKUP OPTIMIZATION OFF; # default

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default

CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/orahome/oracle/product/10.2.0/db_1/dbs/snapcf_mydata.f'; # default

RMAN> list backup;

二、執行RMAN備份

1、全庫備份

RMAN> backup database;

Starting backup at 15-AUG-11

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=154 devtype=DISK

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00001 name=/oradata/mydata/mydata/system01.dbf

input datafile fno=00003 name=/oradata/mydata/mydata/sysaux01.dbf

input datafile fno=00002 name=/oradata/mydata/mydata/undotbs01.dbf

input datafile fno=00005 name=/oradata/mydata/mydata/example01.dbf

input datafile fno=00004 name=/oradata/mydata/mydata/users01.dbf

channel ORA_DISK_1: starting piece 1 at 15-AUG-11

channel ORA_DISK_1: finished piece 1 at 15-AUG-11

piece handle=/orahome/flash_recovery_area/MYDATA/backupset/2011_08_15/o1_mf_nnndf_TAG20110815T132445_74kcdxvn_.bkp tag=TAG20110815T132445 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

including current control file in backupset

including current SPFILE in backupset

channel ORA_DISK_1: starting piece 1 at 15-AUG-11

channel ORA_DISK_1: finished piece 1 at 15-AUG-11

piece handle=/orahome/flash_recovery_area/MYDATA/backupset/2011_08_15/o1_mf_ncsnf_TAG20110815T132445_74kcgdj8_.bkp tag=TAG20110815T132445 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04

Finished backup at 15-AUG-11

2、備份歸檔日志

RMAN> backup archivelog all delete input;

Starting backup at 15-AUG-11

current log archived

using channel ORA_DISK_1

channel ORA_DISK_1: starting archive log backupset

channel ORA_DISK_1: specifying archive log(s) in backup set

input archive log thread=1 sequence=3 recid=1 stamp=758254922

input archive log thread=1 sequence=4 recid=2 stamp=758346126

input archive log thread=1 sequence=5 recid=3 stamp=758459109

input archive log thread=1 sequence=6 recid=4 stamp=758570310

input archive log thread=1 sequence=7 recid=5 stamp=758661373

input archive log thread=1 sequence=8 recid=6 stamp=758729176

input archive log thread=1 sequence=9 recid=7 stamp=758733084

input archive log thread=1 sequence=10 recid=8 stamp=758761235

input archive log thread=1 sequence=11 recid=9 stamp=758844014

input archive log thread=1 sequence=12 recid=10 stamp=758930411

input archive log thread=1 sequence=13 recid=11 stamp=759002811

input archive log thread=1 sequence=14 recid=12 stamp=759046328

input archive log thread=1 sequence=15 recid=13 stamp=759146966

input archive log thread=1 sequence=16 recid=14 stamp=759246991

channel ORA_DISK_1: starting piece 1 at 15-AUG-11

channel ORA_DISK_1: finished piece 1 at 15-AUG-11

piece handle=/orahome/flash_recovery_area/MYDATA/backupset/2011_08_15/o1_mf_annnn_TAG20110815T135631_74kf8k2h_.bkp tag=TAG20110815T135631 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:36

channel ORA_DISK_1: deleting archive log(s)

archive log filename=/orahome/flash_recovery_area/MYDATA/archivelog/2011_08_04/o1_mf_1_3_73m4g9ht_.arc recid=1 stamp=758254922

archive log filename=/orahome/flash_recovery_area/MYDATA/archivelog/2011_08_05/o1_mf_1_4_73oxjffh_.arc recid=2 stamp=758346126

archive log filename=/orahome/flash_recovery_area/MYDATA/archivelog/2011_08_06/o1_mf_1_5_73scv41h_.arc recid=3 stamp=758459109

archive log filename=/orahome/flash_recovery_area/MYDATA/archivelog/2011_08_07/o1_mf_1_6_73wrg5ov_.arc recid=4 stamp=758570310

archive log filename=/orahome/flash_recovery_area/MYDATA/archivelog/2011_08_08/o1_mf_1_7_73zkcwh4_.arc recid=5 stamp=758661373

archive log filename=/orahome/flash_recovery_area/MYDATA/archivelog/2011_08_09/o1_mf_1_8_741mlq6o_.arc recid=6 stamp=758729176

archive log filename=/orahome/flash_recovery_area/MYDATA/archivelog/2011_08_09/o1_mf_1_9_741qdvq6_.arc recid=7 stamp=758733084

archive log filename=/orahome/flash_recovery_area/MYDATA/archivelog/2011_08_09/o1_mf_1_10_742lwl6y_.arc recid=8 stamp=758761235

archive log filename=/orahome/flash_recovery_area/MYDATA/archivelog/2011_08_10/o1_mf_1_11_7453qfps_.arc recid=9 stamp=758844014

archive log filename=/orahome/flash_recovery_area/MYDATA/archivelog/2011_08_11/o1_mf_1_12_747r39d5_.arc recid=10 stamp=758930411

archive log filename=/orahome/flash_recovery_area/MYDATA/archivelog/2011_08_12/o1_mf_1_13_749ysv2t_.arc recid=11 stamp=759002811

archive log filename=/orahome/flash_recovery_area/MYDATA/archivelog/2011_08_13/o1_mf_1_14_74c99q5d_.arc recid=12 stamp=759046328

archive log filename=/orahome/flash_recovery_area/MYDATA/archivelog/2011_08_14/o1_mf_1_15_74gclo7p_.arc recid=13 stamp=759146966

archive log filename=/orahome/flash_recovery_area/MYDATA/archivelog/2011_08_15/o1_mf_1_16_74kf8g2z_.arc recid=14 stamp=759246991

Finished backup at 15-AUG-11

RMAN>

3、查看controlfile備份

RMAN> list backup of controlfile;

List of Backup Sets

===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

2       Full    6.80M      DISK        00:00:01     15-AUG-11

        BP Key: 2   Status: AVAILABLE  Compressed: NO  Tag: TAG20110815T132445

        Piece Name: /orahome/flash_recovery_area/MYDATA/backupset/2011_08_15/o1_mf_ncsnf_TAG20110815T132445_74kcgdj8_.bkp

  Control File Included: Ckp SCN: 1223929      Ckp time: 15-AUG-11

RMAN> exit

Recovery Manager complete.

二、通過視圖查看數據庫數據文件、控制文件、日志文件路徑信息

1、查看數據文件

[oracle@oraserver ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 15 14:02:09 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

查看數據文件

SQL>  select name from v$datafile;

NAME

--------------------------------------------------------------------------------

/oradata/mydata/mydata/system01.dbf

/oradata/mydata/mydata/undotbs01.dbf

/oradata/mydata/mydata/sysaux01.dbf

/oradata/mydata/mydata/users01.dbf

/oradata/mydata/mydata/example01.dbf

2、查看日志文件

SQL> col member for a40

SQL> select * from v$logfile;

GROUP#  STATUS   TYPE    MEMBER                              IS_RECOVERY_DEST_FILE

------ -------- ------- ----------------------------------- ----------------------

     3          ONLINE  /oradata/mydata/mydata/redo03.log     NO

     2          ONLINE  /oradata/mydata/mydata/redo02.log     NO

     1          ONLINE  /oradata/mydata/mydata/redo01.log     NO

3、查看控制文件

SQL> col name for a40

SQL> select * from v$controlfile;

STATUS     NAME                                   IS_RECOVERY_DEST_FILE   BLOCK_SIZE FILE_SIZE_BLKS

-------- -------------------------------------- ------------------------- ---------- --------------

         /oradata/mydata/mydata/control01.ctl   NO                         16384            430

         /oradata/mydata/mydata/control02.ctl   NO                         16384            430

         /oradata/mydata/mydata/control03.ctl   NO                         16384            430

三、刪除所有數據文件、控制文件、日志文件

[oracle@oraserver mydata]$ pwd

/oradata/mydata/mydata

[oracle@oraserver mydata]$ ll

總用量 1257640

-rw-r-----  1 oracle oinstall   7061504  8月 15 14:19 control01.ctl

-rw-r-----  1 oracle oinstall   7061504  8月 15 14:19 control02.ctl

-rw-r-----  1 oracle oinstall   7061504  8月 15 14:19 control03.ctl

-rw-r-----  1 oracle oinstall 104865792  8月 15 14:01 example01.dbf

-rw-r-----  1 oracle oinstall  52429312  8月 15 14:19 redo01.log

-rw-r-----  1 oracle oinstall  52429312  8月 14 10:09 redo02.log

-rw-r-----  1 oracle oinstall  52429312  8月 15 13:56 redo03.log

-rw-r-----  1 oracle oinstall 325066752  8月 15 14:19 sysaux01.dbf

-rw-r-----  1 oracle oinstall 513810432  8月 15 14:19 system01.dbf

-rw-r-----  1 oracle oinstall  20979712  8月 13 06:00 temp01.dbf

-rw-r-----  1 oracle oinstall 157294592  8月 15 14:18 undotbs01.dbf

-rw-r-----  1 oracle oinstall   5251072  8月 15 14:01 users01.dbf

[oracle@oraserver mydata]$ rm -rf *

[oracle@oraserver mydata]$ ls

[oracle@oraserver mydata]$

用dbms_backup_restore包恢復數據庫

[oracle@oraserver mydata]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 15 14:24:57 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> shutdown abort

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area  603979776 bytes

Fixed Size                  1220796 bytes

Variable Size             180358980 bytes

Database Buffers          415236096 bytes

Redo Buffers                7163904 bytes

ORA-00205: error in identifying control file, check alert log for more info

丟失控制文件，數據庫啟動到nomount狀態。

1、恢復控制文件

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/mydata/mydata/control01.ctl');

  8  sys.dbms_backup_restore.restorebackuppiece(done=>done,handle=>'/orahome/flash_recovery_area/MYDATA/backupset/2011_08_15/o1_mf_ncsnf_TAG20110815T132445_74kcgdj8_.bkp');

  9  sys.dbms_backup_restore.devicedeallocate;

 10  end;

 11  /

PL/SQL procedure successfully completed.

2、恢復數據文件

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=>'/oradata/mydata/mydata/system01.dbf');

  8  sys.dbms_backup_restore.restoredatafileto(dfnumber=>02,toname=>'/oradata/mydata/mydata/undotbs01.dbf');

  9  sys.dbms_backup_restore.restoredatafileto(dfnumber=>03,toname=>'/oradata/mydata/mydata/sysaux01.dbf');

 10  sys.dbms_backup_restore.restoredatafileto(dfnumber=>04,toname=>'/oradata/mydata/mydata/users01.dbf');

 11  sys.dbms_backup_restore.restoredatafileto(dfnumber=>05,toname=>'/oradata/mydata/mydata/example01.dbf');  

 12  sys.dbms_backup_restore.restorebackuppiece(done=>done,handle=>'/orahome/flash_recovery_area/MYDATA/backupset/2011_08_15/o1_mf_nnndf_TAG20110815T132445_74kcdxvn_.bkp');

 13  sys.dbms_backup_restore.devicedeallocate;

 14  end;

 15  /

注意：這里每個dbf文件的dfnumber必須與未出現故障時，每個dbf文件的number一致，可通過select file#,name from v$datafile中查詢。

若不一致，恢復后打開數據庫會報錯，如：

SQL>

SQL> alter database mount;

Database altered.

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01122: database file 1 failed verification check

ORA-01110: data file 1: '/oradata/mydata/mydata/system01.dbf'

ORA-01251: Unknown File Header Version read for file number 1

SQL> host ls /oradata/mydata/mydata

control01.ctl  example01.dbf  sysaux01.dbf  system01.dbf  undotbs01.dbf  users01.dbf

[oracle@oraserver mydata]$ cp control01.ctl control02.ctl

[oracle@oraserver mydata]$ cp control01.ctl control03.ctl

SQL> startup nomount

ORA-01081: cannot start already-running ORACLE - shut it down first

SQL> startup mount

ORA-01081: cannot start already-running ORACLE - shut it down first

SQL> shutdown immediate

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup nomount

ORACLE instance started.

Total System Global Area  603979776 bytes

Fixed Size                  1220796 bytes

Variable Size             180358980 bytes

Database Buffers          415236096 bytes

Redo Buffers                7163904 bytes

SQL> alter database mount;

Database altered.

declare

devtype varchar2(256);

done boolean;

begin

devtype:=sys.dbms_backup_restore.deviceallocate(type=>'',ident=>'t1');

sys.dbms_backup_restore.restoresetarchivedlog(destination=>'/orahome/flash_recovery_area/MYDATA/archivelog');

sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>3);

sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>4);

sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>5);

sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>6);

sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>7);

sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>8);

sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>9);

sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>10);

sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>11);

sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>12);

sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>13);

sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>14);

sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>15);

sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>16);

sys.dbms_backup_restore.restorebackuppiece(done=>done,handle=>'/orahome/flash_recovery_area/MYDATA/backupset/2011_08_15/o1_mf_nnndf_TAG20110815T132445_74kcdxvn_.bkp');

sys.dbms_backup_restore.devicedeallocate;

end;

/

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

使用controlfile恢復數據庫

SQL> recover database until cancel using backup controlfile;

ORA-00279: change 1223908 generated at 08/15/2011 13:24:45 needed for thread 1

ORA-00289: suggestion :

/orahome/flash_recovery_area/MYDATA/archivelog/2011_08_15/o1_mf_1_16_%u_.arc

ORA-00280: change 1223908 for thread 1 is in sequence #16

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

auto

ORA-00279: change 1225044 generated at 08/15/2011 13:56:30 needed for thread 1

ORA-00289: suggestion :

/orahome/flash_recovery_area/MYDATA/archivelog/2011_08_15/o1_mf_1_17_%u_.arc

ORA-00280: change 1225044 for thread 1 is in sequence #17

ORA-00278: log file

'/orahome/flash_recovery_area/MYDATA/archivelog/1_16_758221587.dbf' no longer

needed for this recovery

ORA-00308: cannot open archived log

'/orahome/flash_recovery_area/MYDATA/archivelog/2011_08_15/o1_mf_1_17_%u_.arc'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

resetlog打開數據庫

SQL> alter database open resetlogs;

Database altered.

SQL>

以上就是如何調用dbms_backup_restore恢復數據庫，小編相信有部分知識點可能是我們日常工作會見到或用到的。希望你能通過這篇文章學到更多知識。更多詳情敬請關注億速云行業資訊頻道。