Oracle RMAN恢复测试的方法教程

本篇内容主要讲解"Oracle RMAN恢复测试的方法教程"，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习"Oracle RMAN恢复测试的方法教程"吧!

1 启动归档

[oracle@chendb ~]$ sqlplus / as sysdbaSQL> alter system set log_archive_dest_1='location=/arch';SQL> alter system set log_archive_format = "chendb_%t_%s_%r.arc" scope=spfile;SQL> shutdown immediate;SQL> startup mount;SQL> alter database archivelog;SQL> alter database open;SQL> archive log list;

2 创建测试数据

SQL> conn cjc/***SQL>create table t1(id number,ttime varchar2(100));CREATE OR REPLACE PROCEDURE test_pro isbegin  for i in 1 .. 10 loop    insert into t1 values (i, to_char(sysdate, 'yyyy/mm/dd hh34:mi:ss'));    commit;  end loop;end;/

3 创建0级备份脚本

[oracle@cjcos01 script]$ vim /rmanbak/script/0_rmanbak.sh###0_rmanbak.sh begin### export NLS_DATE_FORMAT='yyyy-mm-dd hh34:mi:ss'export ORACLE_SID=chendbrman target / log=/rmanbak/db/0_rmanbak.log <
执行0级备份
[oracle@cjcos01 script]$ chmod a+x 0_rmanbak.sh [oracle@cjcos01 script]$ ./0_rmanbak.sh
执行备份期间，生成测试数据，切换归档日志
SQL> conn cjc/***SQL> exec test_pro();SQL> create table t2 as select * from t1;SQL> alter system switch logfile;
4 创建1级备份
[oracle@cjcos01 script]$ cat 1_rmanbak.sh ###1_rmanbak.sh begin### export NLS_DATE_FORMAT='yyyy-mm-dd hh34:mi:ss'export ORACLE_SID=chendb rman target / log=/rmanbak/db/1_rmanbak.log <
执行1级备份
[oracle@cjcos01 script]$ chmod a+x 1_rmanbak.sh [oracle@cjcos01 script]$ ./1_rmanbak.sh
执行备份期间，生成测试数据，切换归档日志
SQL> conn cjc/***SQL> exec test_pro();SQL> create table t3 as select * from t2;SQL> alter system switch logfile;
5 执行归档备份
[oracle@cjcos01 script]$ cat arch_rmanbak.sh ###arch_rmanbak.sh begin### export NLS_DATE_FORMAT='yyyy-mm-dd hh34:mi:ss'export ORACLE_SID=chendbrman target / log=/rmanbak/db/arch_rmanbak.log <
执行归档备份
[oracle@cjcos01 script]$ ./arch_rmanbak.shSQL> create table t5 as select *from t1;
查看备份信息
SQL> alter session set nls_date_format='yyyymmdd hh34:mi:ss';col tag for a20set lin 200 pages 200col handle for a50col SIZE_BYTES_DISPLAY for a10 select BS.RECID,BS.START_TIME,BS.PIECES,BS.INCREMENTAL_LEVEL,BS.BACKUP_TYPE,BP.PIECE#,BP.HANDLE,BP.TAG,BP.STATUS,ROUND(BP.bytes/1024/1024/1024,2) gb  from v$backup_set bs,v$backup_piece bp   where   bs.recid=bp.recid;       RECID START_TIME PIECES INCREMENTAL_LEVEL BPIECE# HANDLE  TAG       S GB---------- ----------------- ---------- ----------------- - ---------- -------------------------------------------------- -------------------- - ---------- 1 20200801 14:31:54      1 0 D     1 /rmanbak/db/lev0_CHENDB_20200801_02v6p8eq_1_1.bak  0_RMANBAK_CJCDB      A.41 2 20200801 14:31:54      1 0 D     1 /rmanbak/db/lev0_CHENDB_20200801_01v6p8eq_1_1.bak  0_RMANBAK_CJCDB      A.73 3 20200801 14:33:30      1 0 D     1 /rmanbak/db/lev0_CHENDB_20200801_04v6p8hq_1_1.bak  0_RMANBAK_CJCDB      A  0 4 20200801 14:33:23      1 0 D     1 /rmanbak/db/lev0_CHENDB_20200801_03v6p8hj_1_1.bak  0_RMANBAK_CJCDB      A.01 5 20200801 15:07:32      1 1 I     1 /rmanbak/db/lev1_CHENDB_20200801_06v6pahk_1_1.bak  1_RMANBAK_CJCDB      A  0 6 20200801 15:07:32      1 1 I     1 /rmanbak/db/lev1_CHENDB_20200801_05v6pahk_1_1.bak  1_RMANBAK_CJCDB      A  0 7 20200801 15:08:49      1 1 I     1 /rmanbak/db/lev1_CHENDB_20200801_08v6pak1_1_1.bak  1_RMANBAK_CJCDB      A  0 8 20200801 15:08:48      1 1 I     1 /rmanbak/db/lev1_CHENDB_20200801_07v6pak0_1_1.bak  1_RMANBAK_CJCDB      A.01 9 20200801 15:13:26      1   L     1 /rmanbak/db/arch_CHENDB_20200801_09v6pasm_1_1.bak  ARCH_CJCDB       A.5110 20200801 15:13:26      1   L     1 /rmanbak/db/arch_CHENDB_20200801_0av6pasm_1_1.bak  ARCH_CJCDB       A.4511 20200801 15:14:12      1   L     1 /rmanbak/db/arch_CHENDB_20200801_0bv6pau4_1_1.bak  ARCH_CJCDB       A  012 20200801 15:14:14      1   D     1 /rmandata/db/ctl_CHENDB_20200801_0cv6pau6_1_1.ctl  TAG20200801T151414   A.0112 rows selected
BACKUP_TYPE:
Type of files that are in this backup.
If the backup contains archived redo logs, the value is L.
If this is a datafile full backup, the value is D.
If this is an incremental backup, the value is I.
查看备份信息
[oracle@cjcos01 script]$ export NLS_DATE_FORMAT='YYYYMMDD HH24:MI:SS'[oracle@cjcos01 script]$ rman target /Recovery Manager: Release 11.2.0.4.0 - Production on Sat Aug 1 15:24:20 2020Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.connected to target database: CHENDB (DBID=1831901477)
RMAN> list backup summary;using target database control file instead of recovery catalogList of Backups===============Key     TY LV S Device Type Completion Time   #Pieces #Copies Compressed Tag------- -- -- - ----------- ----------------- ------- ------- ---------- ---1       B  0  A DISK        20200801 14:33:06 1       1       NO         0_RMANBAK_CJCDB2       B  0  A DISK        20200801 14:33:26 1       1       NO         0_RMANBAK_CJCDB3       B  0  A DISK        20200801 14:33:30 1       1       NO         0_RMANBAK_CJCDB4       B  0  A DISK        20200801 14:33:31 1       1       NO         0_RMANBAK_CJCDB5       B  1  A DISK        20200801 15:08:41 1       1       NO         1_RMANBAK_CJCDB6       B  1  A DISK        20200801 15:08:48 1       1       NO         1_RMANBAK_CJCDB7       B  1  A DISK        20200801 15:08:49 1       1       NO         1_RMANBAK_CJCDB8       B  1  A DISK        20200801 15:08:50 1       1       NO         1_RMANBAK_CJCDB9       B  A  A DISK        20200801 15:14:07 1       1       NO         ARCH_CJCDB10      B  A  A DISK        20200801 15:14:08 1       1       NO         ARCH_CJCDB11      B  A  A DISK        20200801 15:14:13 1       1       NO         ARCH_CJCDB12      B  F  A DISK        20200801 15:14:15 1       1       NO         TAG20200801T151414
######恢复######
场景一：使用0级备份恢复数据库
手动将增量和归档备份设置失效，测试只有0级备份进行恢复数据库
---生成unavailable脚本
SQL> set pagesize 2000SQL> set heading offSQL> spool /home/oracle/unavailable.logSQL> select 'change backupset '||BS.RECID||' unavailable;'from v$backup_set bs,v$backup_piece bp where bs.recid=bp.recid and BS.BACKUP_TYPE!='D' SQL>spool off'CHANGEBACKUPSET'||BS.RECID||'UNAVAILABLE;'----------------------------------------------------------------------change backupset 5 unavailable;change backupset 6 unavailable;change backupset 7 unavailable;change backupset 8 unavailable;change backupset 9 unavailable;change backupset 10 unavailable;change backupset 11 unavailable;RMAN> change backupset 5 unavailable;change backupset 6 unavailable;change backupset 7 unavailable;change backupset 8 unavailable;change backupset 9 unavailable;change backupset 10 unavailable;change backupset 11 unavailable;
查看状态，5-12备份集已经失效
RMAN> list backup summary;List of Backups===============Key     TY LV S Device Type Completion Time   #Pieces #Copies Compressed Tag------- -- -- - ----------- ----------------- ------- ------- ---------- ---1       B  0  A DISK        20200801 14:33:06 1       1       NO         0_RMANBAK_CJCDB2       B  0  A DISK        20200801 14:33:26 1       1       NO         0_RMANBAK_CJCDB3       B  0  A DISK        20200801 14:33:30 1       1       NO         0_RMANBAK_CJCDB4       B  0  A DISK        20200801 14:33:31 1       1       NO         0_RMANBAK_CJCDB5       B  1  U DISK        20200801 15:08:41 1       1       NO         1_RMANBAK_CJCDB6       B  1  U DISK        20200801 15:08:48 1       1       NO         1_RMANBAK_CJCDB7       B  1  U DISK        20200801 15:08:49 1       1       NO         1_RMANBAK_CJCDB8       B  1  U DISK        20200801 15:08:50 1       1       NO         1_RMANBAK_CJCDB9       B  A  U DISK        20200801 15:14:07 1       1       NO         ARCH_CJCDB10      B  A  U DISK        20200801 15:14:08 1       1       NO         ARCH_CJCDB11      B  A  U DISK        20200801 15:14:13 1       1       NO         ARCH_CJCDB12      B  F  A DISK        20200801 15:14:15 1       1       NO         TAG20200801T151414
0级恢复
SQL> shutdown immediate
冷备
[oracle@cjcos01 oradata]$ pwd/u01/app/oracle11/oradata[oracle@cjcos01 oradata]$ cp chendb chendb_0801bak -rSQL> startup mount
开始0级恢复
默认执行完全恢复，归档备份已经设置失效，所有会提示找不到归档
RMAN>run{allocate channel d1 type disk;restore database;recover database;release channel d1;}
恢复日志如下：
using target database control file instead of recovery catalogallocated channel: d1channel d1: SID=20 device type=DISKStarting restore at 20200801 15:30:43channel d1: starting datafile backup set restorechannel d1: specifying datafile(s) to restore from backup setchannel d1: restoring datafile 00002 to /u01/app/oracle11/oradata/chendb/sysaux01.dbfchannel d1: restoring datafile 00004 to /u01/app/oracle11/oradata/chendb/users01.dbfchannel d1: restoring datafile 00006 to /u01/app/oracle11/oradata/chendb/cjctbs01.dbfchannel d1: reading from backup piece /rmanbak/db/lev0_CHENDB_20200801_02v6p8eq_1_1.bakchannel d1: piece handle=/rmanbak/db/lev0_CHENDB_20200801_02v6p8eq_1_1.bak tag=0_RMANBAK_CJCDBchannel d1: restored backup piece 1channel d1: restore complete, elapsed time: 00:01:05channel d1: starting datafile backup set restorechannel d1: specifying datafile(s) to restore from backup setchannel d1: restoring datafile 00001 to /u01/app/oracle11/oradata/chendb/system01.dbfchannel d1: restoring datafile 00003 to /u01/app/oracle11/oradata/chendb/undotbs01.dbfchannel d1: restoring datafile 00005 to /u01/app/oracle11/oradata/chendb/example01.dbfchannel d1: reading from backup piece /rmanbak/db/lev0_CHENDB_20200801_01v6p8eq_1_1.bakchannel d1: piece handle=/rmanbak/db/lev0_CHENDB_20200801_01v6p8eq_1_1.bak tag=0_RMANBAK_CJCDBchannel d1: restored backup piece 1channel d1: restore complete, elapsed time: 00:01:15Finished restore at 20200801 15:33:04Starting recover at 20200801 15:33:05starting media recoveryreleased channel: d1RMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of recover command at 08/01/2020 15:33:06RMAN-06053: unable to perform media recovery because of missing logRMAN-06025: no backup of archived log for thread 1 with sequence 39 and starting SCN of 2280954 found to restoreRMAN-06025: no backup of archived log for thread 1 with sequence 38 and starting SCN of 2280793 found to restoreRMAN-06025: no backup of archived log for thread 1 with sequence 37 and starting SCN of 2280705 found to restoreRMAN-06025: no backup of archived log for thread 1 with sequence 36 and starting SCN of 2278640 found to restoreRMAN-06025: no backup of archived log for thread 1 with sequence 35 and starting SCN of 2278619 found to restoreRMAN-06025: no backup of archived log for thread 1 with sequence 34 and starting SCN of 2277662 found to restore
查看控制文件记录的数据文件scn
SQL> select file#,checkpoint_change# from v$datafile;     FILE# CHECKPOINT_CHANGE#---------- ------------------ 1      2281863 2      2281863 3      2281863 4      2281863 5      2281863 6      22818636 rows selected.
查看数据文件头记录的SCN
SQL> select file#,checkpoint_change# from v$datafile_header;     FILE# CHECKPOINT_CHANGE#---------- ------------------ 1      2278590 2      2278591 3      2278590 4      2278591 5      2278590 6      22785916 rows selected.
0级别恢复，执行不完全恢复，恢复到数据文件头最大的scn
RMAN>run{allocate channel d1 type disk;restore database until scn 2278591;recover database until scn 2278591;release channel d1;}released channel: d1RMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of recover command at 08/01/2020 15:41:12RMAN-06053: unable to perform media recovery because of missing logRMAN-06025: no backup of archived log for thread 1 with sequence 34 and starting SCN of 2277662 found to restore
提示恢复需要34号归档文件，查看34号归档所在备份集
RMAN> list backupset of archivelog sequence 34;List of Backup Sets===================BS Key  Size       Device Type Elapsed Time Completion Time  ------- ---------- ----------- ------------ -----------------10      463.58M    DISK        00:00:42     20200801 15:14:08        BP Key: 10   Status: AVAILABLE  Compressed: NO  Tag: ARCH_CJCDB        Piece Name: /rmanbak/db/arch_CHENDB_20200801_0av6pasm_1_1.bak  List of Archived Logs in backup set 10  Thrd Seq     Low SCN    Low Time          Next SCN   Next Time  ---- ------- ---------- ----------------- ---------- ---------  1    34      2277662    20200801 14:16:55 2278619    20200801 14:32:15
手动将10号备份置为有效
RMAN> change backupset 10 available;using channel ORA_DISK_1changed backup piece availablebackup piece handle=/rmanbak/db/arch_CHENDB_20200801_0av6pasm_1_1.bak RECID=10 STAMP=1047309207Changed 1 objects to AVAILABLE status
解压出34号归档文件
RMAN> restore archivelog sequence 34;Starting restore at 20200801 15:46:11using channel ORA_DISK_1channel ORA_DISK_1: starting archived log restore to default destinationchannel ORA_DISK_1: restoring archived logarchived log thread=1 sequence=34channel ORA_DISK_1: reading from backup piece /rmanbak/db/arch_CHENDB_20200801_0av6pasm_1_1.bakchannel ORA_DISK_1: piece handle=/rmanbak/db/arch_CHENDB_20200801_0av6pasm_1_1.bak tag=ARCH_CJCDBchannel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:00:07Finished restore at 20200801 15:46:18[oracle@cjcos01 db]$ ls /arch/chendb_1_34_1042210216.arc
再次执行恢复
此次使用了0级备份+34号归档 完成了恢复
RMAN>run{allocate channel d1 type disk;restore database until scn 2278591;recover database until scn 2278591;release channel d1;}released channel: ORA_DISK_1allocated channel: d1channel d1: SID=20 device type=DISKStarting restore at 20200801 15:47:12skipping datafile 1; already restored to file /u01/app/oracle11/oradata/chendb/system01.dbfskipping datafile 3; already restored to file /u01/app/oracle11/oradata/chendb/undotbs01.dbfskipping datafile 5; already restored to file /u01/app/oracle11/oradata/chendb/example01.dbfskipping datafile 2; already restored to file /u01/app/oracle11/oradata/chendb/sysaux01.dbfskipping datafile 4; already restored to file /u01/app/oracle11/oradata/chendb/users01.dbfskipping datafile 6; already restored to file /u01/app/oracle11/oradata/chendb/cjctbs01.dbfrestore not done; all files read only, offline, or already restoredFinished restore at 20200801 15:47:12Starting recover at 20200801 15:47:12starting media recoveryarchived log for thread 1 with sequence 34 is already on disk as file /arch/chendb_1_34_1042210216.arcarchived log file name=/arch/chendb_1_34_1042210216.arc thread=1 sequence=34media recovery complete, elapsed time: 00:00:00Finished recover at 20200801 15:47:12released channel: d1
恢复成功
查看当前数据文件scn
SQL> select file#,checkpoint_change# from v$datafile_header;     FILE# CHECKPOINT_CHANGE#---------- ------------------ 1      2278591 2      2278591 3      2278591 4      2278591 5      2278591 6      22785916 rows selected.
场景二：使用0级+1级备份 恢复数据库
将增量备份设置为有效
RMAN> change backupset 5 available;change backupset 6 available;change backupset 7 available;change backupset 8 available;
查看备份状态
RMAN> list backup summary;List of Backups===============Key     TY LV S Device Type Completion Time   #Pieces #Copies Compressed Tag------- -- -- - ----------- ----------------- ------- ------- ---------- ---1       B  0  A DISK        20200801 14:33:06 1       1       NO         0_RMANBAK_CJCDB2       B  0  A DISK        20200801 14:33:26 1       1       NO         0_RMANBAK_CJCDB3       B  0  A DISK        20200801 14:33:30 1       1       NO         0_RMANBAK_CJCDB4       B  0  A DISK        20200801 14:33:31 1       1       NO         0_RMANBAK_CJCDB5       B  1  A DISK        20200801 15:08:41 1       1       NO         1_RMANBAK_CJCDB6       B  1  A DISK        20200801 15:08:48 1       1       NO         1_RMANBAK_CJCDB7       B  1  A DISK        20200801 15:08:49 1       1       NO         1_RMANBAK_CJCDB8       B  1  A DISK        20200801 15:08:50 1       1       NO         1_RMANBAK_CJCDB9       B  A  U DISK        20200801 15:14:07 1       1       NO         ARCH_CJCDB10      B  A  U DISK        20200801 15:14:08 1       1       NO         ARCH_CJCDB11      B  A  U DISK        20200801 15:14:13 1       1       NO         ARCH_CJCDB12      B  F  A DISK        20200801 15:14:15 1       1       NO         TAG20200801T151414
删除解压的归档
[oracle@cjcos01 db]$ rm -rf /arch/chendb_1_34_1042210216.arc
恢复冷备份
SQL> shutdown immediate[oracle@cjcos01 oradata]$ rm -rf chendb/*[oracle@cjcos01 oradata]$ cp chendb_0801bak/* chendb/ -rSQL> startup mount
开始0+1级恢复，默认执行完全恢复
归档备份已经设置失效，所有会提示找不到归档
RMAN>run{allocate channel d1 type disk;restore database;recover database;release channel d1;}allocated channel: d1channel d1: SID=20 device type=DISKStarting restore at 20200801 16:15:57channel d1: starting datafile backup set restorechannel d1: specifying datafile(s) to restore from backup setchannel d1: restoring datafile 00002 to /u01/app/oracle11/oradata/chendb/sysaux01.dbfchannel d1: restoring datafile 00004 to /u01/app/oracle11/oradata/chendb/users01.dbfchannel d1: restoring datafile 00006 to /u01/app/oracle11/oradata/chendb/cjctbs01.dbfchannel d1: reading from backup piece /rmanbak/db/lev0_CHENDB_20200801_02v6p8eq_1_1.bakchannel d1: piece handle=/rmanbak/db/lev0_CHENDB_20200801_02v6p8eq_1_1.bak tag=0_RMANBAK_CJCDBchannel d1: restored backup piece 1channel d1: restore complete, elapsed time: 00:00:25channel d1: starting datafile backup set restorechannel d1: specifying datafile(s) to restore from backup setchannel d1: restoring datafile 00001 to /u01/app/oracle11/oradata/chendb/system01.dbfchannel d1: restoring datafile 00003 to /u01/app/oracle11/oradata/chendb/undotbs01.dbfchannel d1: restoring datafile 00005 to /u01/app/oracle11/oradata/chendb/example01.dbfchannel d1: reading from backup piece /rmanbak/db/lev0_CHENDB_20200801_01v6p8eq_1_1.bakchannel d1: piece handle=/rmanbak/db/lev0_CHENDB_20200801_01v6p8eq_1_1.bak tag=0_RMANBAK_CJCDBchannel d1: restored backup piece 1channel d1: restore complete, elapsed time: 00:00:35Finished restore at 20200801 16:16:58Starting recover at 20200801 16:16:58channel d1: starting incremental datafile backup set restorechannel d1: specifying datafile(s) to restore from backup setdestination for restore of datafile 00002: /u01/app/oracle11/oradata/chendb/sysaux01.dbfdestination for restore of datafile 00004: /u01/app/oracle11/oradata/chendb/users01.dbfdestination for restore of datafile 00006: /u01/app/oracle11/oradata/chendb/cjctbs01.dbfchannel d1: reading from backup piece /rmanbak/db/lev1_CHENDB_20200801_06v6pahk_1_1.bakchannel d1: piece handle=/rmanbak/db/lev1_CHENDB_20200801_06v6pahk_1_1.bak tag=1_RMANBAK_CJCDBchannel d1: restored backup piece 1channel d1: restore complete, elapsed time: 00:00:01channel d1: starting incremental datafile backup set restorechannel d1: specifying datafile(s) to restore from backup setdestination for restore of datafile 00001: /u01/app/oracle11/oradata/chendb/system01.dbfdestination for restore of datafile 00003: /u01/app/oracle11/oradata/chendb/undotbs01.dbfdestination for restore of datafile 00005: /u01/app/oracle11/oradata/chendb/example01.dbfchannel d1: reading from backup piece /rmanbak/db/lev1_CHENDB_20200801_05v6pahk_1_1.bakchannel d1: piece handle=/rmanbak/db/lev1_CHENDB_20200801_05v6pahk_1_1.bak tag=1_RMANBAK_CJCDBchannel d1: restored backup piece 1channel d1: restore complete, elapsed time: 00:00:01starting media recoveryreleased channel: d1RMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of recover command at 08/01/2020 16:17:00RMAN-06053: unable to perform media recovery because of missing logRMAN-06025: no backup of archived log for thread 1 with sequence 39 and starting SCN of 2280954 found to restoreRMAN-06025: no backup of archived log for thread 1 with sequence 38 and starting SCN of 2280793 found to restoreRMAN-06025: no backup of archived log for thread 1 with sequence 37 and starting SCN of 2280705 found to restoreRMAN-06025: no backup of archived log for thread 1 with sequence 36 and starting SCN of 2278640 found to restore
查看数据文件头scn
SQL> select file#,checkpoint_change# from v$datafile_header;     FILE# CHECKPOINT_CHANGE#---------- ------------------ 1      2280683 2      2280684 3      2280683 4      2280684 5      2280683 6      22806846 rows selected.
执行不完全恢复
RMAN>run{allocate channel d1 type disk;###restore database until scn 2280684;recover database until scn 2280684;release channel d1;}allocated channel: d1channel d1: SID=20 device type=DISKStarting recover at 20200801 16:18:27starting media recoveryreleased channel: d1RMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of recover command at 08/01/2020 16:18:28RMAN-06053: unable to perform media recovery because of missing logRMAN-06025: no backup of archived log for thread 1 with sequence 36 and starting SCN of 2278640 found to restore
提示需要36号归档文件
查看sequence 36
RMAN> list backupset of archivelog sequence 36;List of Backup Sets===================BS Key  Size       Device Type Elapsed Time Completion Time  ------- ---------- ----------- ------------ -----------------10      463.58M    DISK        00:00:42     20200801 15:14:08        BP Key: 10   Status: UNAVAILABLE  Compressed: NO  Tag: ARCH_CJCDB        Piece Name: /rmanbak/db/arch_CHENDB_20200801_0av6pasm_1_1.bak  List of Archived Logs in backup set 10  Thrd Seq     Low SCN    Low Time          Next SCN   Next Time  ---- ------- ---------- ----------------- ---------- ---------  1    36      2278640    20200801 14:33:01 2280705    20200801 15:07:56
需要备份片10
RMAN> change backupset 10 available;
解压归档
RMAN> restore archivelog sequence 36;[oracle@cjcos01 chendb]$ ls /arch/chendb_1_36_1042210216.arc
恢复数据库
RMAN>run{allocate channel d1 type disk;restore database until scn 2280684;recover database until scn 2280684;release channel d1;}allocated channel: d1channel d1: SID=20 device type=DISKStarting restore at 20200801 16:53:20channel d1: starting datafile backup set restorechannel d1: specifying datafile(s) to restore from backup setchannel d1: restoring datafile 00002 to /u01/app/oracle11/oradata/chendb/sysaux01.dbfchannel d1: restoring datafile 00004 to /u01/app/oracle11/oradata/chendb/users01.dbfchannel d1: restoring datafile 00006 to /u01/app/oracle11/oradata/chendb/cjctbs01.dbfchannel d1: reading from backup piece /rmanbak/db/lev0_CHENDB_20200801_02v6p8eq_1_1.bakchannel d1: piece handle=/rmanbak/db/lev0_CHENDB_20200801_02v6p8eq_1_1.bak tag=0_RMANBAK_CJCDBchannel d1: restored backup piece 1channel d1: restore complete, elapsed time: 00:00:25channel d1: starting datafile backup set restorechannel d1: specifying datafile(s) to restore from backup setchannel d1: restoring datafile 00001 to /u01/app/oracle11/oradata/chendb/system01.dbfchannel d1: restoring datafile 00003 to /u01/app/oracle11/oradata/chendb/undotbs01.dbfchannel d1: restoring datafile 00005 to /u01/app/oracle11/oradata/chendb/example01.dbfchannel d1: reading from backup piece /rmanbak/db/lev0_CHENDB_20200801_01v6p8eq_1_1.bakchannel d1: piece handle=/rmanbak/db/lev0_CHENDB_20200801_01v6p8eq_1_1.bak tag=0_RMANBAK_CJCDBchannel d1: restored backup piece 1channel d1: restore complete, elapsed time: 00:00:35Finished restore at 20200801 16:54:21Starting recover at 20200801 16:54:21channel d1: starting incremental datafile backup set restorechannel d1: specifying datafile(s) to restore from backup setdestination for restore of datafile 00002: /u01/app/oracle11/oradata/chendb/sysaux01.dbfdestination for restore of datafile 00004: /u01/app/oracle11/oradata/chendb/users01.dbfdestination for restore of datafile 00006: /u01/app/oracle11/oradata/chendb/cjctbs01.dbfchannel d1: reading from backup piece /rmanbak/db/lev1_CHENDB_20200801_06v6pahk_1_1.bakchannel d1: piece handle=/rmanbak/db/lev1_CHENDB_20200801_06v6pahk_1_1.bak tag=1_RMANBAK_CJCDBchannel d1: restored backup piece 1channel d1: restore complete, elapsed time: 00:00:03channel d1: starting incremental datafile backup set restorechannel d1: specifying datafile(s) to restore from backup setdestination for restore of datafile 00001: /u01/app/oracle11/oradata/chendb/system01.dbfdestination for restore of datafile 00003: /u01/app/oracle11/oradata/chendb/undotbs01.dbfdestination for restore of datafile 00005: /u01/app/oracle11/oradata/chendb/example01.dbfchannel d1: reading from backup piece /rmanbak/db/lev1_CHENDB_20200801_05v6pahk_1_1.bakchannel d1: piece handle=/rmanbak/db/lev1_CHENDB_20200801_05v6pahk_1_1.bak tag=1_RMANBAK_CJCDBchannel d1: restored backup piece 1channel d1: restore complete, elapsed time: 00:00:01starting media recoveryarchived log for thread 1 with sequence 36 is already on disk as file /arch/chendb_1_36_1042210216.arcarchived log file name=/arch/chendb_1_36_1042210216.arc thread=1 sequence=36media recovery complete, elapsed time: 00:00:01Finished recover at 20200801 16:54:26released channel: d1
恢复成功
SQL> select file#,checkpoint_change# from v$datafile_header;     FILE# CHECKPOINT_CHANGE#---------- ------------------ 1      2280684 2      2280684 3      2280684 4      2280684 5      2280684 6      22806846 rows selected.
场景三：使用0级+归档备份恢复数据库
设置增量备份失效
RMAN> change backupset 5 unavailable;change backupset 6 unavailable;change backupset 7 unavailable;change backupset 8 unavailable;change backupset 9 available;change backupset 10 available;change backupset 11 available;
查看备份状态
RMAN> list backupset summary;List of Backups===============Key     TY LV S Device Type Completion Time   #Pieces #Copies Compressed Tag------- -- -- - ----------- ----------------- ------- ------- ---------- ---1       B  0  A DISK        20200801 14:33:06 1       1       NO         0_RMANBAK_CJCDB2       B  0  A DISK        20200801 14:33:26 1       1       NO         0_RMANBAK_CJCDB3       B  0  A DISK        20200801 14:33:30 1       1       NO         0_RMANBAK_CJCDB4       B  0  A DISK        20200801 14:33:31 1       1       NO         0_RMANBAK_CJCDB5       B  1  U DISK        20200801 15:08:41 1       1       NO         1_RMANBAK_CJCDB6       B  1  U DISK        20200801 15:08:48 1       1       NO         1_RMANBAK_CJCDB7       B  1  U DISK        20200801 15:08:49 1       1       NO         1_RMANBAK_CJCDB8       B  1  U DISK        20200801 15:08:50 1       1       NO         1_RMANBAK_CJCDB9       B  A  A DISK        20200801 15:14:07 1       1       NO         ARCH_CJCDB10      B  A  A DISK        20200801 15:14:08 1       1       NO         ARCH_CJCDB11      B  A  A DISK        20200801 15:14:13 1       1       NO         ARCH_CJCDB12      B  F  A DISK        20200801 15:14:15 1       1       NO         TAG20200801T151414
执行完全恢复
RMAN>run{allocate channel d1 type disk;restore database;recover database;release channel d1;}released channel: ORA_DISK_1allocated channel: d1channel d1: SID=20 device type=DISKStarting restore at 20200801 17:12:20channel d1: starting datafile backup set restorechannel d1: specifying datafile(s) to restore from backup setchannel d1: restoring datafile 00002 to /u01/app/oracle11/oradata/chendb/sysaux01.dbfchannel d1: restoring datafile 00004 to /u01/app/oracle11/oradata/chendb/users01.dbfchannel d1: restoring datafile 00006 to /u01/app/oracle11/oradata/chendb/cjctbs01.dbfchannel d1: reading from backup piece /rmanbak/db/lev0_CHENDB_20200801_02v6p8eq_1_1.bakchannel d1: piece handle=/rmanbak/db/lev0_CHENDB_20200801_02v6p8eq_1_1.bak tag=0_RMANBAK_CJCDBchannel d1: restored backup piece 1channel d1: restore complete, elapsed time: 00:00:25channel d1: starting datafile backup set restorechannel d1: specifying datafile(s) to restore from backup setchannel d1: restoring datafile 00001 to /u01/app/oracle11/oradata/chendb/system01.dbfchannel d1: restoring datafile 00003 to /u01/app/oracle11/oradata/chendb/undotbs01.dbfchannel d1: restoring datafile 00005 to /u01/app/oracle11/oradata/chendb/example01.dbfchannel d1: reading from backup piece /rmanbak/db/lev0_CHENDB_20200801_01v6p8eq_1_1.bakchannel d1: piece handle=/rmanbak/db/lev0_CHENDB_20200801_01v6p8eq_1_1.bak tag=0_RMANBAK_CJCDBchannel d1: restored backup piece 1channel d1: restore complete, elapsed time: 00:00:35Finished restore at 20200801 17:13:20Starting recover at 20200801 17:13:20starting media recoveryarchived log for thread 1 with sequence 36 is already on disk as file /arch/chendb_1_36_1042210216.arcchannel d1: starting archived log restore to default destinationchannel d1: restoring archived logarchived log thread=1 sequence=34channel d1: restoring archived logarchived log thread=1 sequence=35channel d1: reading from backup piece /rmanbak/db/arch_CHENDB_20200801_0av6pasm_1_1.bakchannel d1: piece handle=/rmanbak/db/arch_CHENDB_20200801_0av6pasm_1_1.bak tag=ARCH_CJCDBchannel d1: restored backup piece 1channel d1: restore complete, elapsed time: 00:00:15archived log file name=/arch/chendb_1_34_1042210216.arc thread=1 sequence=34archived log file name=/arch/chendb_1_35_1042210216.arc thread=1 sequence=35archived log file name=/arch/chendb_1_36_1042210216.arc thread=1 sequence=36channel d1: starting archived log restore to default destinationchannel d1: restoring archived logarchived log thread=1 sequence=37channel d1: restoring archived logarchived log thread=1 sequence=38channel d1: restoring archived logarchived log thread=1 sequence=39channel d1: reading from backup piece /rmanbak/db/arch_CHENDB_20200801_0bv6pau4_1_1.bakchannel d1: piece handle=/rmanbak/db/arch_CHENDB_20200801_0bv6pau4_1_1.bak tag=ARCH_CJCDBchannel d1: restored backup piece 1channel d1: restore complete, elapsed time: 00:00:01archived log file name=/arch/chendb_1_37_1042210216.arc thread=1 sequence=37media recovery complete, elapsed time: 00:00:01Finished recover at 20200801 17:13:44released channel: d1
恢复成功
场景四：完全恢复
将所有备份设置为可用
RMAN>change backupset 5 available;change backupset 6 available;change backupset 7 available;change backupset 8 available;
查看备份状态
RMAN> list backupset summary;List of Backups===============Key     TY LV S Device Type Completion Time   #Pieces #Copies Compressed Tag------- -- -- - ----------- ----------------- ------- ------- ---------- ---1       B  0  A DISK        20200801 14:33:06 1       1       NO         0_RMANBAK_CJCDB2       B  0  A DISK        20200801 14:33:26 1       1       NO         0_RMANBAK_CJCDB3       B  0  A DISK        20200801 14:33:30 1       1       NO         0_RMANBAK_CJCDB4       B  0  A DISK        20200801 14:33:31 1       1       NO         0_RMANBAK_CJCDB5       B  1  A DISK        20200801 15:08:41 1       1       NO         1_RMANBAK_CJCDB6       B  1  A DISK        20200801 15:08:48 1       1       NO         1_RMANBAK_CJCDB7       B  1  A DISK        20200801 15:08:49 1       1       NO         1_RMANBAK_CJCDB8       B  1  A DISK        20200801 15:08:50 1       1       NO         1_RMANBAK_CJCDB9       B  A  A DISK        20200801 15:14:07 1       1       NO         ARCH_CJCDB10      B  A  A DISK        20200801 15:14:08 1       1       NO         ARCH_CJCDB11      B  A  A DISK        20200801 15:14:13 1       1       NO         ARCH_CJCDB12      B  F  A DISK        20200801 15:14:15 1       1       NO         TAG20200801T151414released channel: ORA_DISK_1allocated channel: d1channel d1: SID=20 device type=DISKStarting restore at 20200801 17:25:24channel d1: starting datafile backup set restorechannel d1: specifying datafile(s) to restore from backup setchannel d1: restoring datafile 00002 to /u01/app/oracle11/oradata/chendb/sysaux01.dbfchannel d1: restoring datafile 00004 to /u01/app/oracle11/oradata/chendb/users01.dbfchannel d1: restoring datafile 00006 to /u01/app/oracle11/oradata/chendb/cjctbs01.dbfchannel d1: reading from backup piece /rmanbak/db/lev0_CHENDB_20200801_02v6p8eq_1_1.bakchannel d1: piece handle=/rmanbak/db/lev0_CHENDB_20200801_02v6p8eq_1_1.bak tag=0_RMANBAK_CJCDBchannel d1: restored backup piece 1channel d1: restore complete, elapsed time: 00:00:25channel d1: starting datafile backup set restorechannel d1: specifying datafile(s) to restore from backup setchannel d1: restoring datafile 00001 to /u01/app/oracle11/oradata/chendb/system01.dbfchannel d1: restoring datafile 00003 to /u01/app/oracle11/oradata/chendb/undotbs01.dbfchannel d1: restoring datafile 00005 to /u01/app/oracle11/oradata/chendb/example01.dbfchannel d1: reading from backup piece /rmanbak/db/lev0_CHENDB_20200801_01v6p8eq_1_1.bakchannel d1: piece handle=/rmanbak/db/lev0_CHENDB_20200801_01v6p8eq_1_1.bak tag=0_RMANBAK_CJCDBchannel d1: restored backup piece 1channel d1: restore complete, elapsed time: 00:00:35Finished restore at 20200801 17:26:24Starting recover at 20200801 17:26:24channel d1: starting incremental datafile backup set restorechannel d1: specifying datafile(s) to restore from backup setdestination for restore of datafile 00002: /u01/app/oracle11/oradata/chendb/sysaux01.dbfdestination for restore of datafile 00004: /u01/app/oracle11/oradata/chendb/users01.dbfdestination for restore of datafile 00006: /u01/app/oracle11/oradata/chendb/cjctbs01.dbfchannel d1: reading from backup piece /rmanbak/db/lev1_CHENDB_20200801_06v6pahk_1_1.bakchannel d1: piece handle=/rmanbak/db/lev1_CHENDB_20200801_06v6pahk_1_1.bak tag=1_RMANBAK_CJCDBchannel d1: restored backup piece 1channel d1: restore complete, elapsed time: 00:00:01channel d1: starting incremental datafile backup set restorechannel d1: specifying datafile(s) to restore from backup setdestination for restore of datafile 00001: /u01/app/oracle11/oradata/chendb/system01.dbfdestination for restore of datafile 00003: /u01/app/oracle11/oradata/chendb/undotbs01.dbfdestination for restore of datafile 00005: /u01/app/oracle11/oradata/chendb/example01.dbfchannel d1: reading from backup piece /rmanbak/db/lev1_CHENDB_20200801_05v6pahk_1_1.bakchannel d1: piece handle=/rmanbak/db/lev1_CHENDB_20200801_05v6pahk_1_1.bak tag=1_RMANBAK_CJCDBchannel d1: restored backup piece 1channel d1: restore complete, elapsed time: 00:00:01starting media recoveryarchived log for thread 1 with sequence 36 is already on disk as file /arch/chendb_1_36_1042210216.arcarchived log for thread 1 with sequence 37 is already on disk as file /arch/chendb_1_37_1042210216.arcarchived log for thread 1 with sequence 38 is already on disk as file /arch/chendb_1_38_1042210216.arcarchived log for thread 1 with sequence 39 is already on disk as file /arch/chendb_1_39_1042210216.arcarchived log file name=/arch/chendb_1_36_1042210216.arc thread=1 sequence=36archived log file name=/arch/chendb_1_37_1042210216.arc thread=1 sequence=37media recovery complete, elapsed time: 00:00:01Finished recover at 20200801 17:26:28released channel: d1
恢复完成，启动数据库
SQL> alter database open;
查看数据
SQL> conn cjc/cjcSQL> select *from tab;TNAME       TABTYPECLUSTERID------------------------------ ------- ----------T1       TABLET2       TABLET3       TABLET5       TABLET6       TABLE6 rows selected.
场景五：异地恢复常用命令
1 恢复参数文件
RMAN> startup nomountRMAN> restore spfile to '/rmandata/spfilecjcdb01.ora' from '/0712/rman/db/lev0_CJCDB01_20200711_04v52r5k_1_1.bak';SQL> shutdown immedaiteSQL> create pfile='/rmandata/initcjcdb01.ora' from spfile='/rmandata/spfilecjcdb01.ora';---修改initcjcdb01.ora参数SQL> startup nomount pfile='/rmandata/initcjcdb01.ora'
2 恢复控制文件
RMAN> list backupset of controlfile;run {allocate channel c1 type disk;restore controlfile from '/0712/rman/db/ctl_CJCDB01_20200712_1vv54j7m_1_1.ctl';release channel c1;}
3 查看文件路径
select name from v$datafileunion allselect name from v$tempfileunion allselect name from v$controlfileunion all select member from v$logfile;
4 生成转换文件位置的语句
SQL>set line 150set page 100set heading off select 'set newname for datafile ' || '''' || name || '''' || ' to ' ||       chr(39) ||       replace(name, '+DATA/cjcdb/datafile/', '/rmandata/db/') ||       ''';'  from (select name          from v$datafile        union all        select name          from v$tempfile        union all        select name          from v$controlfile        union all        select member from v$logfile);
###set newname for datafile '+DATA/cjcdb/datafile/system.256.1039343535' to '/rmandata/db/system.256.1039343535';set newname for datafile '+DATA/cjcdb/datafile/sysaux.257.1039343537' to '/rmandata/db/sysaux.257.1039343537';set newname for datafile '+DATA/cjcdb/datafile/undotbs1.258.1039343539' to '/rmandata/db/undotbs1.258.1039343539';set newname for datafile '+DATA/cjcdb/datafile/users.259.1039343539' to '/rmandata/db/users.259.1039343539';set newname for datafile '+DATA/cjcdb/datafile/undotbs2.264.1039343919' to '/rmandata/db/undotbs2.264.1039343919';set newname for datafile '+DATA/cjcdb/datafile/cjctbs.268.1043622893' to '/rmandata/db/cjctbs.268.1043622893';set newname for datafile '+DATA/cjcdb/datafile/chentbs.267.1046799153' to '/rmandata/db/chentbs.267.1046799153';set newname for datafile '+DATA/cjcdb/datafile/chentbs01.272.1046799329' to '/rmandata/db/chentbs01.272.1046799329';set newname for datafile '+DATA/cjcdb/tempfile/temp.263.1039343731' to '+DATA/cjcdb/tempfile/temp.263.1039343731';set newname for datafile '/rmandata/control01.ctl' to '/rmandata/control01.ctl';set newname for datafile '+DATA/cjcdb/onlinelog/group_2.262.1039343705' to '+DATA/cjcdb/onlinelog/group_2.262.1039343705';set newname for datafile '+DATA/cjcdb/onlinelog/group_1.261.1039343703' to '+DATA/cjcdb/onlinelog/group_1.261.1039343703';set newname for datafile '+DATA/cjcdb/onlinelog/group_3.265.1039344119' to '+DATA/cjcdb/onlinelog/group_3.265.1039344119';set newname for datafile '+DATA/cjcdb/onlinelog/group_4.266.1039344121' to '+DATA/cjcdb/onlinelog/group_4.266.1039344121';###
5 修改文件路径
如果Restore执行后执行了SWITCH DATAFILE ALL;语句，可以不用执行下面的操作。
SQL>set line 150set page 100set heading off select 'alter database rename file ' || '''' || name || '''' || ' to ' ||       chr(39) ||       replace(name, '+DATA/cjcdb/datafile/', '/rmandata/db/') ||       ''';'  from (select name          from v$datafile        union all        select name          from v$tempfile        union all        select name          from v$controlfile        union all        select member from v$logfile);###alter database rename file '+DATA/cjcdb/datafile/system.256.1039343535' to '/rmandata/db/system.256.1039343535';alter database rename file '+DATA/cjcdb/datafile/sysaux.257.1039343537' to '/rmandata/db/sysaux.257.1039343537';alter database rename file '+DATA/cjcdb/datafile/undotbs1.258.1039343539' to '/rmandata/db/undotbs1.258.1039343539';alter database rename file '+DATA/cjcdb/datafile/users.259.1039343539' to '/rmandata/db/users.259.1039343539';alter database rename file '+DATA/cjcdb/datafile/undotbs2.264.1039343919' to '/rmandata/db/undotbs2.264.1039343919';alter database rename file '+DATA/cjcdb/datafile/cjctbs.268.1043622893' to '/rmandata/db/cjctbs.268.1043622893';alter database rename file '+DATA/cjcdb/datafile/chentbs.267.1046799153' to '/rmandata/db/chentbs.267.1046799153';alter database rename file '+DATA/cjcdb/datafile/chentbs01.272.1046799329' to '/rmandata/db/chentbs01.272.1046799329';alter database rename file '+DATA/cjcdb/tempfile/temp.263.1039343731' to '+DATA/cjcdb/tempfile/temp.263.1039343731';alter database rename file '/rmandata/control01.ctl' to '/rmandata/control01.ctl';alter database rename file '+DATA/cjcdb/onlinelog/group_2.262.1039343705' to '+DATA/cjcdb/onlinelog/group_2.262.1039343705';alter database rename file '+DATA/cjcdb/onlinelog/group_1.261.1039343703' to '+DATA/cjcdb/onlinelog/group_1.261.1039343703';alter database rename file '+DATA/cjcdb/onlinelog/group_3.265.1039344119' to '+DATA/cjcdb/onlinelog/group_3.265.1039344119';alter database rename file '+DATA/cjcdb/onlinelog/group_4.266.1039344121' to '+DATA/cjcdb/onlinelog/group_4.266.1039344121';###
6 异机恢复示例(RAC备份恢复到单机)：
RMAN>run{allocate channel d1 type disk;set newname for datafile '+DATA/cjcdb/datafile/system.256.1039343535' to '/rmandata/db/system.256.1039343535';set newname for datafile '+DATA/cjcdb/datafile/sysaux.257.1039343537' to '/rmandata/db/sysaux.257.1039343537';set newname for datafile '+DATA/cjcdb/datafile/undotbs1.258.1039343539' to '/rmandata/db/undotbs1.258.1039343539';set newname for datafile '+DATA/cjcdb/datafile/users.259.1039343539' to '/rmandata/db/users.259.1039343539';set newname for datafile '+DATA/cjcdb/datafile/undotbs2.264.1039343919' to '/rmandata/db/undotbs2.264.1039343919';set newname for datafile '+DATA/cjcdb/datafile/cjctbs.268.1043622893' to '/rmandata/db/cjctbs.268.1043622893';set newname for datafile '+DATA/cjcdb/datafile/chentbs.267.1046799153' to '/rmandata/db/chentbs.267.1046799153';set newname for datafile '+DATA/cjcdb/datafile/chentbs01.272.1046799329' to '/rmandata/db/chentbs01.272.1046799329';set newname for datafile '+DATA/cjcdb/tempfile/temp.263.1039343731' to '+DATA/cjcdb/tempfile/temp.263.1039343731';set newname for datafile '/rmandata/control01.ctl' to '/rmandata/control01.ctl';set newname for datafile '+DATA/cjcdb/onlinelog/group_2.262.1039343705' to '+DATA/cjcdb/onlinelog/group_2.262.1039343705';set newname for datafile '+DATA/cjcdb/onlinelog/group_1.261.1039343703' to '+DATA/cjcdb/onlinelog/group_1.261.1039343703';set newname for datafile '+DATA/cjcdb/onlinelog/group_3.265.1039344119' to '+DATA/cjcdb/onlinelog/group_3.265.1039344119';set newname for datafile '+DATA/cjcdb/onlinelog/group_4.266.1039344121' to '+DATA/cjcdb/onlinelog/group_4.266.1039344121';restore database until time "to_date('2020-07-31 22:17:47','yyyy-mm-dd hh34:mi:ss')";SWITCH DATAFILE ALL;recover database until time "to_date('2020-07-31 22:17:47','yyyy-mm-dd hh34:mi:ss')";release channel d1;}
7 在某些特定场景下：
先执行了
restore database until time xxx;recover database until time xxx;
recover结束后没有启动数据库，数据库处于mouont状态。
这时如果想将数据库recover到更新的时间，不需要在执行
restore database until time xxx;
数据量大时，restore database耗时，耗空间。
只需要执行
recover database until time xxx+time;
但是如果在这个时间段内生成了新的数据文件，不能直接进行recover。
需要单独restore新增的数据文件，或通过下面的命令生成一个新的文件。
alter database create datafile '+DATA/cjcdb/datafile/cjc_data_5.dbf' as '/oradata/cjcdb/datafile/cjc_data_5.dbf';
然后可以进行执行recover。
到此，相信大家对"Oracle RMAN恢复测试的方法教程"有了更深的了解，不妨来实际操作一番吧！这里是网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！