如何调用dbms_backup_restore恢复数据库
本篇文章给大家分享的是有关如何调用dbms_backup_restore恢复数据库，小编觉得挺实用的，因此分享给大家学习，希望大家阅读完这篇文章后可以有所收获，话不多说，跟着小编一起来看看吧。
创新互联专注于中大型企业的成都网站制作、成都网站建设和网站改版、网站营销服务，追求商业策划与数据分析、创意艺术与技术开发的融合，累计客户千余家，服务满意度达97%。帮助广大客户顺利对接上互联网浪潮，准确优选出符合自己需要的互联网运用，我们将一直专注品牌网站设计和互联网程序开发，在前进的路上，与客户一起成长！
一、查看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: {
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恢复数据库，小编相信有部分知识点可能是我们日常工作会见到或用到的。希望你能通过这篇文章学到更多知识。更多详情敬请关注创新互联行业资讯频道。
本文标题：如何调用dbms_backup_restore恢复数据库
文章源于：http://ybzwz.com/article/pcppps.html
