本文共 9226 字，大约阅读时间需要 30 分钟。
利用dbms_backup_restore恢复数据库
环境信息：
OS: RedHat5.9
ORACLE: 12C
测试场景：Oracle数据库的存储怀了，数据文件和控制文件全部丢失了，只有数据文件的备份集，且备份集中无控制文件
测试步骤：
1. 首先将数据库进行全备：
将控制文件自动备份关掉
ocrl:/home/oracle@oracle1>rmantarget /
RecoveryManager: Release 12.1.0.2.0 - Production on Mon Feb 13 11:37:22 2017
Copyright(c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connectedto target database: OCRL (DBID=864450697)
RMAN>show all;
usingtarget database control file instead of recovery catalog
RMANconfiguration parameters for database with db_unique_name OCRL are:
CONFIGURERETENTION POLICY TO REDUNDANCY 1; # default
CONFIGUREBACKUP OPTIMIZATION OFF;
CONFIGUREDEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURECONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/data/backup/%F';
CONFIGUREDEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGUREDATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGUREARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGUREMAXSETSIZE TO UNLIMITED; # default
CONFIGUREENCRYPTION FOR DATABASE OFF; # default
CONFIGUREENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURECOMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ;# default
CONFIGURERMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGUREARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURESNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/12.1.0/db_1/dbs/snapcf_ocrl.f';# default
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP OFF;
old RMANconfiguration parameters:
CONFIGURECONTROLFILE AUTOBACKUP ON;
new RMANconfiguration parameters:
CONFIGURECONTROLFILE AUTOBACKUP OFF;
new RMANconfiguration parameters are successfully stored
开始全备
RMAN> backup format '/data/backup/%U' database;
Startingbackup at 2017/02/13 11:44:44
usingtarget database control file instead of recovery catalog
allocatedchannel: ORA_DISK_1
channelORA_DISK_1: SID=27 device type=DISK
channel ORA_DISK_1:starting full datafile backup set
channelORA_DISK_1: specifying datafile(s) in backup set
inputdatafile file number=00002 name=/data/oradata/ocrl/datafile/sysaux01.dbf
inputdatafile file number=00001 name=/data/oradata/ocrl/datafile/system01.dbf
inputdatafile file number=00003 name=/data/oradata/ocrl/datafile/undotbs01.dbf
inputdatafile file number=00004 name=/data/oradata/ocrl/datafile/users01.dbf
inputdatafile file number=00005 name=/data/oradata/ocrl/datafile/test.dbf
inputdatafile file number=00006 name=/data/oradata/ocrl/datafile/tstest.dbf
channelORA_DISK_1: starting piece 1 at 2017/02/13 11:44:46
channelORA_DISK_1: finished piece 1 at 2017/02/13 11:45:15
piece handle=/data/backup/6ersfite_1_1 tag=TAG20170213T114446comment=NONE
channelORA_DISK_1: backup set complete, elapsed time: 00:00:29
channelORA_DISK_1: starting full datafile backup set
channelORA_DISK_1: specifying datafile(s) in backup set
includingcurrent control file in backup set
includingcurrent SPFILE in backup set
channelORA_DISK_1: starting piece 1 at 2017/02/13 11:45:17
channelORA_DISK_1: finished piece 1 at 2017/02/13 11:45:18
piece handle=/data/backup/6frsfiub_1_1 tag=TAG20170213T114446comment=NONE
channelORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finishedbackup at 2017/02/13 11:45:18
RMAN> exit
查看备份：
ocrl:/data/backup@oracle1>cd/data/backup/
ocrl:/data/backup@oracle1>ll
total1721972
-rw-r-----1 oracle dba 1750786048 Feb 13 11:45 6ersfite_1_1
-rw-r-----1 oracle dba 10780672 Feb 13 11:456frsfiub_1_1
2.删除控制文件和数据文件
删除数据文件：
ocrl:/data/backup@oracle1>cd/data/oradata/ocrl/datafile/
ocrl:/data/oradata/ocrl/datafile@oracle1>ls
sysaux01.dbf system01.dbf test.dbf tstest.dbf undotbs01.dbf users01.dbf
ocrl:/data/oradata/ocrl/datafile@oracle1>rm-rf *
lsocrl:/data/oradata/ocrl/datafile@oracle1>ls
删除控制文件：
ocrl:/data/oradata/ocrl/datafile@oracle1>cd/data/oradata/ocrl/control/
ocrl:/data/oradata/ocrl/control@oracle1>ls
control01.ctl control02.ctl
ocrl:/data/oradata/ocrl/control@oracle1>rm-rf *
删除redo和tmpfile
ocrl:/data/oradata/ocrl/control@oracle1>cd/data/oradata/ocrl/redo/
ocrl:/data/oradata/ocrl/redo@oracle1>rm-rf *
ocrl:/data/oradata/ocrl/redo@oracle1>cd/data/oradata/ocrl/tempfile/
ocrl:/data/oradata/ocrl/tempfile@oracle1>ls
temp01.dbf
ocrl:/data/oradata/ocrl/tempfile@oracle1>rm-rf *
3.开始恢复
如果之前有参数文件，就用之前的参数文件，如果没有就自己手动修改init.ora文件，先将实例启动到nomount状态；
这里就用之前的参数文件启动
ocrl:/data/oradata/ocrl/tempfile@oracle1>sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0Production on Mon Feb 13 15:06:07 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 754974720 bytes
Fixed Size 2928968 bytes
Variable Size 524291768bytes
Database Buffers 222298112 bytes
Redo Buffers 5455872bytes
SQL>
构造控制文件
使用dbms_backup_restore将控制文件从备份集中构造出来：
SQL> declare
devtype varchar2(256);
done boolean;
begin
devtype:=dbms_backup_restore.DeviceAllocate(type=>'',ident=>'t1');
dbms_backup_restore.RestoreSetDatafile;
sys.dbms_backup_restore.RestoreControlfileTo(cfname=>'/data/oradata/ocrl/control/control01.ctl');
sys.dbms_backup_restore.RestoreBackupPiece(done=>done,handle=>'/data/backup/6frsfiub_1_1',params=>null);
sys.dbms_backup_restore.DeviceDeallocate;
end;
/
PL/SQL procedure successfully completed.
可以在路径下看到控制文件被构造出来了。
ocrl:/u01/app/oracle/product/12.1.0/db_1@oracle1>cd/data/oradata/ocrl/control/
ocrl:/data/oradata/ocrl/control@oracle1>ls
control01.ctl
由于启动的参数文件里面控制文件做了冗余，如下:
SQL> show parameter control
NAME TYPE VALUE
----------------------------------------------- ------------------------------
control_file_record_keep_time integer 7
control_files string /data/oradata/ocrl/control/con
trol01.ctl, /data/oradata/ocrl
/control/control02.ctl
control_management_pack_access string DIAGNOSTIC+TUNING
SQL>
这里先将数据库关闭，然后利用下面的方法从刚才构建的控制文件再copy一份
ocrl:/data/oradata/ocrl/control@oracle1>ls
control01.ctl
ocrl:/data/oradata/ocrl/control@oracle1>cpcontrol01.ctl control02.ctl
这样在启动到mount状态下就不会报错
ocrl:/data/oradata/ocrl/tempfile@oracle1>sqlplus/ as sysdba
SQL*Plus: Release 12.1.0.2.0 Production onMon Feb 13 15:17:59 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 754974720 bytes
Fixed Size 2928968 bytes
Variable Size 524291768 bytes
Database Buffers 222298112 bytes
Redo Buffers 5455872 bytes
SQL> alter database mount;
Database altered.
查看数据文件的file#号和绝对路径：
SQL> col name format a40
SQL> select file#,name from v$datafile;
FILE# NAME
---------- ----------------------------------------
1 /data/oradata/ocrl/datafile/system01.dbf
2 /data/oradata/ocrl/datafile/sysaux01.dbf
3 /data/oradata/ocrl/datafile/undotbs01.dbf
4 /data/oradata/ocrl/datafile/users01.dbf
5 /data/oradata/ocrl/datafile/test.dbf
6 /data/oradata/ocrl/datafile/tstest.dbf
6 rows selected.
还原数据文件
现在知道数据文件的file#和name后，还是利用dbms_backup_restore从备份集中还原数据文件
SQL>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=>'/data/oradata/ocrl/datafile/system01.dbf');
sys.dbms_backup_restore.RestoreDatafileto(dfnumber=>02,toname=>'/data/oradata/ocrl/datafile/sysaux01.dbf');
sys.dbms_backup_restore.RestoreDatafileto(dfnumber=>03,toname=>'/data/oradata/ocrl/datafile/undotbs01.dbf');
sys.dbms_backup_restore.RestoreDatafileto(dfnumber=>04,toname=>'/data/oradata/ocrl/datafile/users01.dbf');
sys.dbms_backup_restore.RestoreDatafileto(dfnumber=>05,toname=>'/data/oradata/ocrl/datafile/test.dbf');
sys.dbms_backup_restore.RestoreDatafileto(dfnumber=>06,toname=>'/data/oradata/ocrl/datafile/tstest.dbf');
sys.dbms_backup_restore.RestoreBackupPiece(done=>done,handle=>'/data/backup/6ersfite_1_1',params=>null);
sys.dbms_backup_restore.DeviceDeallocate;
end;
/
PL/SQLprocedure successfully completed.
查看数据文件路径下是否已经将数据文件还原到指定目录
ocrl:/data/oradata/ocrl/datafile@oracle1>ls
sysaux01.dbf system01.dbf test.dbf tstest.dbf undotbs01.dbf users01.dbf
至此，数据文件已经被还原至指定目录
现在执行不完全恢复
RMAN> recover database;Startingrecover at 2017/02/13 14:38:11
Startingimplicit crosscheck backup at 2017/02/13 14:38:11
usingtarget database control file instead of recovery catalog
allocatedchannel: ORA_DISK_1
channelORA_DISK_1: SID=24 device type=DISK
Crosschecked5 objects
Finishedimplicit crosscheck backup at 2017/02/13 14:38:12
Startingimplicit crosscheck copy at 2017/02/13 14:38:12
usingchannel ORA_DISK_1
Finishedimplicit crosscheck copy at 2017/02/13 14:38:12
searchingfor all files in the recovery area
catalogingfiles...
nofiles cataloged
usingchannel ORA_DISK_1
startingmedia recovery
archivedlog for thread 1 with sequence 2 is already on disk as file/data/oradata/ocrl/redo/redo02.log
archivedlog file name=/data/oradata/ocrl/redo/redo02.log thread=1 sequence=2
mediarecovery complete, elapsed time: 00:00:00
Finishedrecover at 2017/02/13 14:38:15
恢复完成，之后将实例打开，因为redo文件也丢失了，执行不完全恢复，要加resetlogs参数
RMAN> alter database open resetlogs;
Statementprocessed
RMAN>
看看redo文件是否被重建？
ocrl:/data/oradata/ocrl/redo@oracle1>cd/data/oradata/ocrl/redo/
ocrl:/data/oradata/ocrl/redo@oracle1>ls
redo01.log redo02.log redo03.log
ocrl:/data/oradata/ocrl/redo@oracle1>
至此数据库恢复完成。
转载地址：http://pkhji.baihongyu.com/