Shulou(Shulou.com)05/31 Report--

This article focuses on the "Oracle RMAN recovery testing method tutorial", interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Next, let the editor take you to learn the "Oracle RMAN recovery testing method tutorial"!

1 start archiving

[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 > archivelog list

2 create test data

SQL > conn cjc/***SQL > create table T1 (id number,ttime varchar2); 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 create a level 0 backup script

[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 exec test_pro (); SQL > create table T2 as select * from T1 position SQL > alter system switch logfile

4 create a level 1 backup

[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 exec test_pro (); SQL > create table T3 as select * from T2 alternative SQL > alter system switch logfile

5 perform an archive backup

[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 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 04 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 06 20200801 15:07:32 1 1 I 1 / rmanbak/db/lev1_CHENDB_20200801_05v6pahk_1_1.bak 1_RMANBAK_CJCDB A 07 20200801 15:08:49 1 1 I 1 / rmanbak/db/lev1_CHENDB_20200801_08v6pak1_1_1.bak 1_RMANBAK_CJCDB A 08 20200801 15:08:48 11 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.

View backup information

[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 BA A DISK 20200801 15:14:07 11 NO ARCH_CJCDB10 BA A DISK 20200801 15:14:08 11 NO ARCH_CJCDB11 BA A DISK 20200801 15:14:13 11 NO ARCH_CJCDB12 B F A DISK 20200801 15:14:15 1 1 NO TAG20200801T151414

# restore #

Scenario 1: restore the database using a level 0 backup

Manually invalidate the incremental and archive backup settings and test that only a level 0 backup is used to restore the database

-generate unavailable script

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.BACKUPPROTYPERTYPERATION' 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

Check status. 5-12 backup set has expired.

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 BA U DISK 20200801 15:14:07 11 NO ARCH_CJCDB10 BA U DISK 20200801 15:14:08 11 NO ARCH_CJCDB11 BA U DISK 20200801 15:14:13 11 NO ARCH_CJCDB12 B F A DISK 20200801 15:14:15 11 NO TAG20200801T151414

Level 0 recovery

SQL > shutdown immediate

Cold standby

[oracle@cjcos01 oradata] $pwd/u01/app/oracle11/oradata [oracle@cjcos01 oradata] $cp chendb chendb_0801bak-rSQL > startup mount

Start level 0 recovery

Full recovery is performed by default, archive backup has been set up invalid, and all will prompt that the archive cannot be found.

RMAN > run {allocate channel D1 type disk;restore database;recover database;release channel D1;}

The recovery log is as follows:

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 08Greater 012020 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

View the data file scn recorded by the control file

SQL > select file#,checkpoint_change# from data rows selected; FILE# CHECKPOINT_CHANGE#--1 2281863 2 2281863 3 2281863 4 2281863 5 22818636 22818636.

View the SCN of the header record of the data file

SQL > select file#,checkpoint_change# from data head; FILE# CHECKPOINT_CHANGE#--1 2278590 2 2278591 3 2278590 4 2278591 5 2278590 6 22785916 rows selected.

Level 0 restore, perform incomplete recovery, restore to the scn with the largest data file header

RMAN > run {allocate channel D1 type disk;restore database until scn 2278591 * recover database until scn 2278591 * and starting SCN of 2277662 found to restore

Prompt that recovery requires archive No. 34. Check the backup set where Archive No. 34 is located.

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

Manually set backup No. 10 to be valid

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

Extract the file No. 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

Perform the restore again

This time, level 0 backup + No. 34 archive was used to complete the recovery.

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

Recovery succeeded

View the current data file scn

SQL > select file#,checkpoint_change# from data head; FILE# CHECKPOINT_CHANGE#--1 2278591 2 2278591 3 2278591 4 2278591 5 22785916 22785916 rows selected.

Scenario 2: restore the database using level 0 + level 1 backup

Set incremental backup to valid

RMAN > change backupset 5 available;change backupset 6 available;change backupset 7 available;change backupset 8 available

View backup status

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 BA U DISK 20200801 15:14:07 11 NO ARCH_CJCDB10 BA U DISK 20200801 15:14:08 11 NO ARCH_CJCDB11 BA U DISK 20200801 15:14:13 11 NO ARCH_CJCDB12 B F A DISK 20200801 15:14:15 11 NO TAG20200801T151414

Delete unzipped archive

[oracle@cjcos01 db] $rm-rf / arch/chendb_1_34_1042210216.arc

Restore cold backup

SQL > shutdown immediate [oracle@cjcos01 oradata] $rm-rf chendb/* [oracle@cjcos01 oradata] $cp chendb_0801bak/* chendb/-rSQL > startup mount

Start level 01st restore, and perform full restore by default

Archive backup has been set up invalid, all will prompt that the archive cannot be found

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 with sequence 01 and starting SCN of 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

View data file header scn

SQL > select file#,checkpoint_change# from data head; FILE# CHECKPOINT_CHANGE#--1 2280683 2 2280684 3 2280683 4 2280684 5 2280683 6 22806846 rows selected.

Perform an incomplete recovery

RMAN > run {allocate channel D1 type disk;###restore database until scn 2280684 Taiwan recover database until scn 2280684 16:18:27starting media recoveryreleased channel 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 08Comp012020 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

Prompt requires archival document No. 36

View 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

Backup slice 10 is required

RMAN > change backupset 10 available

Decompression and archiving

RMAN > restore archivelog sequence 36; [oracle@cjcos01 chendb] $ls / arch/chendb_1_36_1042210216.arc

Restore the database

RMAN > run {allocate channel D1 type disk;restore database until scn 2280684 from recover database until scn 2280684 from 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

Recovery succeeded

SQL > select file#,checkpoint_change# from data head; FILE# CHECKPOINT_CHANGE#--1 2280684 2 2280684 3 2280684 4 2280684 5 22806846 rows selected.

Scenario 3: restore the database using level 0 + archive backup

Setting incremental backup invalidation

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

View backup status

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 BA A DISK 20200801 15:14:07 11 NO ARCH_CJCDB10 BA A DISK 20200801 15:14:08 11 NO ARCH_CJCDB11 BA A DISK 20200801 15:14:13 11 NO ARCH_CJCDB12 B F A DISK 20200801 15:14:15 11 NO TAG20200801T151414

Perform a full recovery

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

Recovery succeeded

Scenario 4: full recovery

Make all backups available

RMAN > change backupset 5 available;change backupset 6 available;change backupset 7 available;change backupset 8 available

View backup status

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 BA A DISK 20200801 15:14:07 11 NO ARCH_CJCDB10 BA A DISK 20200801 15:14:08 11 NO ARCH_CJCDB11 BA A DISK 20200801 15:14:13 11 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

Restore complete, start the database

SQL > alter database open

View data

SQL > conn cjc/cjcSQL > select * from tab;TNAME TABTYPECLUSTERID-- T1 TABLET2 TABLET3 TABLET5 TABLET6 TABLE6 rows selected.

Scenario 5: restore common commands off-site

1 restore parameter file

RMAN > startup nomountRMAN > restore spfile to'/ rmandata/spfilecjcdb01.ora' from'/ 0712 Universe rman hand dbLeft 0CJCDB01 "20200711" 04v52r5KOK1 "1.bakery SQL > shutdown immedaiteSQL > create pfile='/rmandata/initcjcdb01.ora' from spfile='/rmandata/spfilecjcdb01.ora';--- modify initcjcdb01.ora parameter SQL > startup nomount pfile='/rmandata/initcjcdb01.ora'

2 restore control files

RMAN > list backupset of controlfile;run {allocate channel C1 type disk;restore controlfile from'/ 0712 release channel c1;}

3 View file path

Select name from v$datafileunion allselect name from v$tempfileunion allselect name from v$controlfileunion allselect member from v$logfile

4 generate a statement to convert the location of the file

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 modify the file path

If the SWITCH DATAFILE ALL; statement is executed after Restore execution, you don't have to do the following.

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 example of off-machine recovery (RAC backup and restore to stand-alone):

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"; SWITCH DATAFILE ALL Recover database until time "to_date ('2020-07-31 22 hh34:mi:ss' 17 hh34:mi:ss')"; release channel D1;}

7 in some specific scenarios:

Execute it first.

Restore database until time xxx;recover database until time xxx

The database is not started after the end of recover, and the database is in mouont state.

At this point, if you want to recover the database to the update time, you do not need to execute

Restore database until time xxx

When the amount of data is large, restore database consumes time and space.

You just need to execute

Recover database until time xxx+time

However, if a new data file is generated within this period of time, recover cannot be done directly.

You need to add a new data file to restore separately, or generate a new file with the following command.

Alter database create datafile'+ DATA/cjcdb/datafile/cjc_data_5.dbf' as'/ oradata/cjcdb/datafile/cjc_data_5.dbf'

You can then execute the recover.

At this point, I believe that you have a deeper understanding of the "Oracle RMAN recovery testing method tutorial". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.