- 要点:对于RMAN的不完全恢复，有如下步骤：
1）加载数据到mount状态(建议恢复前先做备份)
2）为高并发分配多个通道
3）还原所有(所需)的数据文件
4）使用until time,until sequence,until scn来恢复数据库
5）使用resetlogs打开数据库
6）全备数据库
SQL> conn scott/Sina.2015@study Connected. SQL> show user; USER is "SCOTT" SQL> create table tb1(id int,name varchar2(10)); Table created. SQL> insert into tb1 select 1,'Test' from dual; 1 row created. SQL> conn sys / as sysdba; Enter password: Connected. SQL> SELECT a.GROUP#,a.ARCHIVED,a.STATUS,b.TYPE,b.MEMBER FROM V$LOG a,V$LOGFILE b 2 where a.GROUP#=b.GROUP# 3 ; GROUP# ARC STATUS TYPE MEMBER ---------- --- ---------------- ------- -------------- 3 NO CURRENT ONLINE /data2/orcl/redo03.log 2 YES INACTIVE ONLINE /data2/orcl/redo02.log 1 YES INACTIVE ONLINE /data2/orcl/redo01.log
SQL> host strings /data2/orcl/redo03.log | grep Test SQL> commit; --注意commit是将日志缓冲内容写入到日志文件 SQL> host strings /data2/orcl/redo03.log | grep Test --提交后联机日志文件中才有新增的记录 Test Oracle Real Application Testing Oracle Real Application Testing perl-Test-HarnessW_TPackage_0:3.17-127.el6 perl-Test-Harness perl-Test-SimplePackage perl-Test-Simple perl-Test-Harness perl-Test-Harness perl-Test-SimpleRed Hat, Inc. perl-Test-SimplePackage SQL> host ls /data2/orcl/recover_log/archive_log 1_10_892917066.dbf 1_15_892917066.dbf 1_2_892917066.dbf 1_4_892917066.dbf 1_9_892917066.dbf 1_11_892917066.dbf 1_16_892917066.dbf 1_34_892641223.dbf 1_5_892917066.dbf 1_12_892917066.dbf 1_17_892917066.dbf 1_35_892641223.dbf 1_6_892917066.dbf 1_13_892917066.dbf 1_18_892917066.dbf 1_36_892641223.dbf 1_7_892917066.dbf 1_14_892917066.dbf 1_1_892917066.dbf 1_3_892917066.dbf 1_8_892917066.dbf
RMAN> run{ 2> allocate channel ch1 type disk; allocate channel ch2 type disk; backup as compressed backupset database plus archivelog delete input 3> 4> 5> format '/data2/backup/wh_lg_%T_%U' 6> tag='Wholebak_Pluslog'; 7> release channel ch1; 8> release channel ch2;} released channel: ORA_DISK_1 released channel: ORA_DISK_2 released channel: ORA_DISK_3 allocated channel: ch1 channel ch1: SID=131 device type=DISK allocated channel: ch2 channel ch2: SID=199 device type=DISK Starting backup at 15-OCT-15 current log archived channel ch1: starting compressed archived log backup set channel ch1: specifying archived log(s) in backup set input archived log thread=1 sequence=34 RECID=27 STAMP=892917066 input archived log thread=1 sequence=35 RECID=28 STAMP=892917067 input archived log thread=1 sequence=36 RECID=29 STAMP=892917068 channel ch1: starting piece 1 at 15-OCT-15 channel ch2: starting compressed archived log backup set channel ch2: specifying archived log(s) in backup set input archived log thread=1 sequence=1 RECID=30 STAMP=892918266 input archived log thread=1 sequence=2 RECID=31 STAMP=892918267 input archived log thread=1 sequence=3 RECID=32 STAMP=892936841 input archived log thread=1 sequence=4 RECID=33 STAMP=892944057 input archived log thread=1 sequence=5 RECID=34 STAMP=892967601 input archived log thread=1 sequence=6 RECID=35 STAMP=892994416 input archived log thread=1 sequence=7 RECID=36 STAMP=892998810 input archived log thread=1 sequence=8 RECID=37 STAMP=893021371 input archived log thread=1 sequence=9 RECID=38 STAMP=893023737 input archived log thread=1 sequence=10 RECID=39 STAMP=893044828 channel ch2: starting piece 1 at 15-OCT-15 channel ch1: finished piece 1 at 15-OCT-15 piece handle=/data2/backup/wh_lg_20151015_29qjpa57_1_1 tag=WHOLEBAK_PLUSLOG comment=NONE channel ch1: backup set complete, elapsed time: 00:00:04 channel ch1: deleting archived log(s) archived log file name=/data2/orcl/recover_log/archive_log/1_34_892641223.dbf RECID=27 STAMP=892917066 archived log file name=/data2/orcl/recover_log/archive_log/1_35_892641223.dbf RECID=28 STAMP=892917067 archived log file name=/data2/orcl/recover_log/archive_log/1_36_892641223.dbf RECID=29 STAMP=892917068 channel ch1: starting compressed archived log backup set channel ch1: specifying archived log(s) in backup set input archived log thread=1 sequence=11 RECID=40 STAMP=893072256 input archived log thread=1 sequence=12 RECID=41 STAMP=893088234 input archived log thread=1 sequence=13 RECID=42 STAMP=893095263 input archived log thread=1 sequence=14 RECID=43 STAMP=893109634 input archived log thread=1 sequence=15 RECID=44 STAMP=893116838 input archived log thread=1 sequence=16 RECID=45 STAMP=893142174 input archived log thread=1 sequence=17 RECID=46 STAMP=893151673 input archived log thread=1 sequence=18 RECID=47 STAMP=893167412 input archived log thread=1 sequence=19 RECID=48 STAMP=893167783 channel ch1: starting piece 1 at 15-OCT-15 channel ch1: finished piece 1 at 15-OCT-15 piece handle=/data2/backup/wh_lg_20151015_2bqjpa5b_1_1 tag=WHOLEBAK_PLUSLOG comment=NONE channel ch1: backup set complete, elapsed time: 00:00:25 channel ch1: deleting archived log(s) archived log file name=/data2/orcl/recover_log/archive_log/1_11_892917066.dbf RECID=40 STAMP=893072256 archived log file name=/data2/orcl/recover_log/archive_log/1_12_892917066.dbf RECID=41 STAMP=893088234 archived log file name=/data2/orcl/recover_log/archive_log/1_13_892917066.dbf RECID=42 STAMP=893095263 archived log file name=/data2/orcl/recover_log/archive_log/1_14_892917066.dbf RECID=43 STAMP=893109634 archived log file name=/data2/orcl/recover_log/archive_log/1_15_892917066.dbf RECID=44 STAMP=893116838 archived log file name=/data2/orcl/recover_log/archive_log/1_16_892917066.dbf RECID=45 STAMP=893142174 archived log file name=/data2/orcl/recover_log/archive_log/1_17_892917066.dbf RECID=46 STAMP=893151673 archived log file name=/data2/orcl/recover_log/archive_log/1_18_892917066.dbf RECID=47 STAMP=893167412 archived log file name=/data2/orcl/recover_log/archive_log/1_19_892917066.dbf RECID=48 STAMP=893167783 channel ch2: finished piece 1 at 15-OCT-15 piece handle=/data2/backup/wh_lg_20151015_2aqjpa57_1_1 tag=WHOLEBAK_PLUSLOG comment=NONE channel ch2: backup set complete, elapsed time: 00:00:29 channel ch2: deleting archived log(s) archived log file name=/data2/orcl/recover_log/archive_log/1_1_892917066.dbf RECID=30 STAMP=892918266 archived log file name=/data2/orcl/recover_log/archive_log/1_2_892917066.dbf RECID=31 STAMP=892918267 archived log file name=/data2/orcl/recover_log/archive_log/1_3_892917066.dbf RECID=32 STAMP=892936841 archived log file name=/data2/orcl/recover_log/archive_log/1_4_892917066.dbf RECID=33 STAMP=892944057 archived log file name=/data2/orcl/recover_log/archive_log/1_5_892917066.dbf RECID=34 STAMP=892967601 archived log file name=/data2/orcl/recover_log/archive_log/1_6_892917066.dbf RECID=35 STAMP=892994416 archived log file name=/data2/orcl/recover_log/archive_log/1_7_892917066.dbf RECID=36 STAMP=892998810 archived log file name=/data2/orcl/recover_log/archive_log/1_8_892917066.dbf RECID=37 STAMP=893021371 archived log file name=/data2/orcl/recover_log/archive_log/1_9_892917066.dbf RECID=38 STAMP=893023737 archived log file name=/data2/orcl/recover_log/archive_log/1_10_892917066.dbf RECID=39 STAMP=893044828 Finished backup at 15-OCT-15 Starting backup at 15-OCT-15 channel ch1: starting compressed full datafile backup set channel ch1: specifying datafile(s) in backup set input datafile file number=00001 name=/data2/orcl/system01.dbf input datafile file number=00003 name=/data2/orcl/undotbs01.dbf channel ch1: starting piece 1 at 15-OCT-15 channel ch2: starting compressed full datafile backup set channel ch2: specifying datafile(s) in backup set input datafile file number=00002 name=/data2/orcl/sysaux01.dbf input datafile file number=00004 name=/data2/orcl/user01.dbf channel ch2: starting piece 1 at 15-OCT-15 channel ch2: finished piece 1 at 15-OCT-15 piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2015_10_15/o1_mf_nnndf_TAG20151015T141014_c1yjt962_.bkp tag=TAG20151015T141014 comment=NONE channel ch2: backup set complete, elapsed time: 00:00:46 channel ch1: finished piece 1 at 15-OCT-15 piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2015_10_15/o1_mf_nnndf_TAG20151015T141014_c1yjt92v_.bkp tag=TAG20151015T141014 comment=NONE channel ch1: backup set complete, elapsed time: 00:00:57 Finished backup at 15-OCT-15 Starting backup at 15-OCT-15 current log archived channel ch1: starting compressed archived log backup set channel ch1: specifying archived log(s) in backup set input archived log thread=1 sequence=20 RECID=49 STAMP=893167872 channel ch1: starting piece 1 at 15-OCT-15 channel ch1: finished piece 1 at 15-OCT-15 piece handle=/data2/backup/wh_lg_20151015_2eqjpa81_1_1 tag=WHOLEBAK_PLUSLOG comment=NONE channel ch1: backup set complete, elapsed time: 00:00:03 channel ch1: deleting archived log(s) archived log file name=/data2/orcl/recover_log/archive_log/1_20_892917066.dbf RECID=49 STAMP=893167872 Finished backup at 15-OCT-15 Starting Control File and SPFILE Autobackup at 15-OCT-15 piece handle=/u01/app/oracle/fast_recovery_area/ORCL/autobackup/2015_10_15/o1_mf_s_893167876_c1yjw646_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 15-OCT-15 released channel: ch1 released channel: ch2
SQL> drop table scott.tb1; Table dropped.
RMAN> run{ 2> allocate channel ch1 type disk; 3> allocate channel ch2 type disk; 4> set until time "to_date('2015-10-15 14:12:10','yyyy-mm-dd hh24:mi:ss')"; 5> restore database; 6> recover database; 7> alter database open resetlogs; 8> release channel ch1; 9> release channel ch2;} allocated channel: ch1 channel ch1: SID=131 device type=DISK allocated channel: ch2 channel ch2: SID=199 device type=DISK executing command: SET until clause Starting restore at 15-OCT-15 channel ch1: starting datafile backup set restore channel ch1: specifying datafile(s) to restore from backup set channel ch1: restoring datafile 00002 to /data2/orcl/sysaux01.dbf channel ch1: restoring datafile 00004 to /data2/orcl/user01.dbf channel ch1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORCL/backupset/2015_10_15/o1_mf_nnndf_TAG20151015T141014_c1yjt962_.bkp channel ch2: starting datafile backup set restore channel ch2: specifying datafile(s) to restore from backup set channel ch2: restoring datafile 00001 to /data2/orcl/system01.dbf channel ch2: restoring datafile 00003 to /data2/orcl/undotbs01.dbf channel ch2: reading from backup piece /u01/app/oracle/fast_recovery_area/ORCL/backupset/2015_10_15/o1_mf_nnndf_TAG20151015T141014_c1yjt92v_.bkp released channel: ch1 released channel: ch2 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of restore command at 10/15/2015 14:19:14 ORA-19870: error while restoring backup piece /u01/app/oracle/fast_recovery_area/ORCL/backupset/2015_10_15/o1_mf_nnndf_TAG20151015T141014_c1yjt962_.bkp ORA-19573: cannot obtain exclusive enqueue for datafile 2
接2.2部分: RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of restore command at 10/15/2015 14:19:14 ORA-19870: error while restoring backup piece /u01/app/oracle/fast_recovery_area/ORCL/backupset/2015_10_15/o1_mf_nnndf_TAG20151015T141014_c1yjt962_.bkp ORA-19573: cannot obtain exclusive enqueue for datafile 2 Cause: The file access enqueue could not be obtained for a file specified in a backup, copy or restore operation. If the enqueue type shown is SHARED, then the file is the input file for a backup or copy. If the type is EXCLUSIVE, then the file is the output file for a datafile copy or restore which is attempting to overwrite the currently active version of that file. In this case, the file must be offline or the database must be closed. If the type is read-only, then you are attempting to back up or copy this file while the database is in NOARCHIVELOG mode. 问题解决：不完全恢复时，数据库状态需要切换到 mount状态
RMAN> startup nomount force; Oracle instance started Total System Global Area 1937457152 bytes Fixed Size 2229584 bytes Variable Size 1241516720 bytes Database Buffers 687865856 bytes Redo Buffers 5844992 bytes RMAN> alter database mount; database mounted RMAN> run{ 2> allocate channel ch1 type disk; 3> allocate channel ch2 type disk; 4> set until time "to_date('2015-10-15 14:12:10','yyyy-mm-dd hh24:mi:ss')"; 5> restore database; 6> recover database; 7> alter database open resetlogs; 8> release channel ch1; 9> release channel ch2;} allocated channel: ch1 channel ch1: SID=129 device type=DISK allocated channel: ch2 channel ch2: SID=193 device type=DISK executing command: SET until clause Starting restore at 15-OCT-15 channel ch1: starting datafile backup set restore channel ch1: specifying datafile(s) to restore from backup set channel ch1: restoring datafile 00002 to /data2/orcl/sysaux01.dbf channel ch1: restoring datafile 00004 to /data2/orcl/user01.dbf channel ch1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORCL/backupset/2015_10_15/o1_mf_nnndf_TAG20151015T141014_c1yjt962_.bkp channel ch2: starting datafile backup set restore channel ch2: specifying datafile(s) to restore from backup set channel ch2: restoring datafile 00001 to /data2/orcl/system01.dbf channel ch2: restoring datafile 00003 to /data2/orcl/undotbs01.dbf channel ch2: reading from backup piece /u01/app/oracle/fast_recovery_area/ORCL/backupset/2015_10_15/o1_mf_nnndf_TAG20151015T141014_c1yjt92v_.bkp channel ch1: piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2015_10_15/o1_mf_nnndf_TAG20151015T141014_c1yjt962_.bkp tag=TAG20151015T141014 channel ch1: restored backup piece 1 channel ch1: restore complete, elapsed time: 00:00:45 channel ch2: piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2015_10_15/o1_mf_nnndf_TAG20151015T141014_c1yjt92v_.bkp tag=TAG20151015T141014 channel ch2: restored backup piece 1 channel ch2: restore complete, elapsed time: 00:01:05 Finished restore at 15-OCT-15 Starting recover at 15-OCT-15 starting media recovery media recovery complete, elapsed time: 00:00:03 Finished recover at 15-OCT-15 database opened released channel: ch1 released channel: ch2 SQL> conn scott/Sina.2015@study Connected. SQL> select * from tb1; ID NAME ---------- ---------- 1 Test
另:rman debug trace 诊 rman问题
rman target / debug trace=/tmp/rman_debug