Recovery Manager – My Oracle Notes

#######################################################
## RMAN Setup, Backup and Recovery Test ##
#######################################################

–Basic tips for Disk & Tape Backup/Recovery strategy.

--To be able to efficiently perform incremental backup check if "Change Block Tracking" is enable:
 SELECT * FROM V$BLOCK_CHANGE_TRACKING;

–If it doesn’t exist create it:
alter database enable block change tracking using file ‘+FRA1’;

###########################
# RMAN Format Description
###########################

%a Current database activation id

%A Zero-filled activation ID

%c The copy number of the backup piece within a set of duplexed backup pieces.bMaximum value is 256

%d Database name

%D Current day of the month from the Gregorian calendar in format DD

%e Archived log sequence number

%f Absolute file number

%F Combines the DBID, day, month, year, and sequence into a unique and repeatable generated name

%h Archived redo log thread number

%I DBID

%M Month in the Gregorian calendar in the format MM

%n Database name, padded on the right with x characters to a total length of eight characters

%N Tablespace name. Only valid when backing up datafiles as image copies.

%p Piece number within the backup set. This value starts at 1 for each backup set and is incremented by 1
 for each backup piece created. If a PROXY is specified, the %p variable must be included in the FORMAT
 string either explicitly or implicitly within %U.

%r Resetlogs ID

%s Backup set number. This number is a counter in the control file that is incremented for each backup set.
 The counter value starts at 1 and is unique for the lifetime of the control file. If you restore a backup
 control file, then duplicate values can result. CREATE CONTROLFILE initializes the counter at 1.

%S Zero-filled sequence number

%t Backup set time stamp, a 4-byte value derived as the number of seconds elapsed since a fixed reference time.
 The combination of %s and %t can be used to form a unique name for the backup set.

%T Year, month, and day in the Gregorian calendar in the format: YYYYMMDD

%u An 8-character name constituted by compressed representations of the backup set or image copy number and the
 time the backup set or image copy was created

%U A system-generated unique filename (default). %U is different for image copies and backup pieces.
 For a backup piece, %U is a shorthend for %u_%p_%c and guarantees uniqueness in generated backup filenames.
 For an image copy of a datafile, %U means the following: data-D-%d_id-%I_TS-%N_FNO-%f_%u

%Y Year in this format: YYYY

%% Percent (%) character. For example, %%Y translates to the string %Y

################################################################

#####################
## RMAN STEUP
#####################

connect target /
 connect catalog  usr_catalog/xxxxxx@RMAN_Catalog.emilianofusaglia.net

REGISTER DATABASE;

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 30 DAYS;
 CONFIGURE BACKUP OPTIMIZATION ON;
 CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
 CONFIGURE CONTROLFILE AUTOBACKUP ON;
 CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '+FRA1/%F';
 CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;
 CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' RATE 200M PARMS 'ENV=(NB_ORA_POLICY=ora_monthly)' FORMAT 'backup_%d_%T_%U';
 CONFIGURE CHANNEL DEVICE TYPE DISK RATE 200M FORMAT '+BACKUP/%d_%T_%U';
 CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO 'SBT_TAPE';
 CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+FRA1/snapcf_dbrm07.cf';

--Full Database backup
 BACKUP INCREMENTAL LEVEL = 0 DATABASE PLUS ARCHIVELOG NOT BACKED UP 1 TIMES;

--Incremental Database backup
 BACKUP INCREMENTAL LEVEL = 1 DATABASE PLUS ARCHIVELOG NOT BACKED UP 1 TIMES;

--RMAN Catalog Maintenance and Backup Validation
 SHOW ALL;
 run{
 ALLOCATE CHANNEL disk1 DEVICE TYPE DISK;
 allocate channel tape1 type 'SBT_TAPE' PARMS 'ENV=(NB_ORA_CLIENT=lxrm01.emilianofusaglia.net)';
 crosscheck backup completed before "sysdate-30";
 delete noprompt expired backup;
 delete noprompt obsolete;
 }

--Rman basic Backup Report
 SHOW all;
 list backup summary;

################################################################
## RESTORE TESTS
################################################################

–Restore Datafile from TAPE

SQL> startup mount
 ORACLE instance started.

Total System Global Area 4277059584 bytes
 Fixed Size                  2154936 bytes
 Variable Size            2499812936 bytes
 Database Buffers         1728053248 bytes
 Redo Buffers               47038464 bytes
 Database mounted.
 SQL>
 SQL>
 SQL> select * from v$recover_file;

FILE# ONLINE  ONLINE_  ERROR                       CHANGE#    TIME
 ---------- ------- -------  -------------------------- ----------  --------
 9 ONLINE  ONLINE   FILE NOT FOUND                     0

RMAN> connect target /

connected to target database: DBRM07 (DBID=3653795552, not open)

RMAN> connect catalog  usr_catalog/xxxxxx@RMAN_Catalog.emilianofusaglia.net

connected to recovery catalog database

RMAN> restore datafile 9;

Starting restore at 07.04.11
 starting full resync of recovery catalog
 full resync complete
 allocated channel: ORA_DISK_1
 channel ORA_DISK_1: SID=1393 instance=DBRM071 device type=DISK
 allocated channel: ORA_DISK_2
 channel ORA_DISK_2: SID=1422 instance=DBRM071 device type=DISK
 allocated channel: ORA_DISK_3
 channel ORA_DISK_3: SID=1451 instance=DBRM071 device type=DISK
 allocated channel: ORA_DISK_4
 channel ORA_DISK_4: SID=1480 instance=DBRM071 device type=DISK
 allocated channel: ORA_DISK_5
 channel ORA_DISK_5: SID=1509 instance=DBRM071 device type=DISK
 allocated channel: ORA_SBT_TAPE_1
 channel ORA_SBT_TAPE_1: SID=1538 instance=DBRM071 device type=SBT_TAPE
 channel ORA_SBT_TAPE_1: Veritas NetBackup for Oracle - Release 6.5 (2010120208)

channel ORA_SBT_TAPE_1: starting datafile backup set restore
 channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
 channel ORA_SBT_TAPE_1: restoring datafile 00009 to +DATA1/dbtr07/datafile/indxtbs.562.717369571
 channel ORA_SBT_TAPE_1: reading from backup piece 13ld31mb_1_2
 channel ORA_SBT_TAPE_1: piece handle=13ld30mb_1_2 tag=TAG20110406T121258
 channel ORA_SBT_TAPE_1: restored backup piece 1
 channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:04:16
 Finished restore at 07.04.11
 starting full resync of recovery catalog
 full resync complete

RMAN> recover datafile 9;

Starting recover at 07.04.11
 using channel ORA_DISK_1
 using channel ORA_DISK_2
 using channel ORA_DISK_3
 using channel ORA_DISK_4
 using channel ORA_DISK_5
 using channel ORA_SBT_TAPE_1
 channel ORA_SBT_TAPE_1: starting incremental datafile backup set restore
 channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
 destination for restore of datafile 00009: +DATA1/dbtr07/datafile/indxtbs.562.717369571
 channel ORA_SBT_TAPE_1: reading from backup piece 1eld30qo_1_2
 channel ORA_SBT_TAPE_1: piece handle=1eld30qo_1_2 tag=TAG20110406T121520
 channel ORA_SBT_TAPE_1: restored backup piece 1
 channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:04:25

starting media recovery

archived log for thread 1 with sequence 162 is already on disk as file +FRA1/dbtr07/archivelog/2011_04_06/thread_1_seq_162.16655.718385527
 archived log for thread 1 with sequence 163 is already on disk as file +FRA1/dbtr07/archivelog/2011_04_06/thread_1_seq_163.8452.718385527
 archived log for thread 2 with sequence 141 is already on disk as file +FRA1/dbtr07/archivelog/2011_04_06/thread_2_seq_141.4677.718385531
 archived log for thread 3 with sequence 132 is already on disk as file +FRA1/dbtr07/archivelog/2011_04_06/thread_3_seq_132.5466.718385525
 archived log for thread 3 with sequence 133 is already on disk as file +FRA1/dbtr07/archivelog/2011_04_06/thread_3_seq_133.1479.718385525
 channel ORA_SBT_TAPE_1: starting archived log restore to default destination
 channel ORA_SBT_TAPE_1: restoring archived log
 archived log thread=3 sequence=131
 channel ORA_SBT_TAPE_1: reading from backup piece 1hld30r8_1_2
 channel ORA_SBT_TAPE_1: piece handle=1hld30r8_1_2 tag=TAG20100507T121535
 channel ORA_SBT_TAPE_1: restored backup piece 1
 channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:02:15
 archived log file name=+FRA1/dbtr07/archivelog/2011_04_06/thread_3_seq_131.16592.718387087 thread=3 sequence=131
 channel ORA_SBT_TAPE_1: starting archived log restore to default destination
 channel ORA_SBT_TAPE_1: restoring archived log
 archived log thread=1 sequence=161
 channel ORA_SBT_TAPE_1: reading from backup piece 1ild30r8_1_2
 channel ORA_SBT_TAPE_1: piece handle=1ild30r8_1_2 tag=TAG20100507T121535
 channel ORA_SBT_TAPE_1: restored backup piece 1
 channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:01:58
 archived log file name=+FRA1/dbtr07/archivelog/2011_04_06/thread_1_seq_161.13854.718387211 thread=1 sequence=161
 channel ORA_SBT_TAPE_1: starting archived log restore to default destination
 channel ORA_SBT_TAPE_1: restoring archived log
 archived log thread=2 sequence=140
 channel ORA_SBT_TAPE_1: reading from backup piece 1jld30r8_1_2
 channel ORA_SBT_TAPE_1: piece handle=1jld30r8_1_2 tag=TAG20100507T121535
 channel ORA_SBT_TAPE_1: restored backup piece 1
 channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:01:55
 archived log file name=+FRA1/dbtr07/archivelog/2011_04_06/thread_2_seq_140.1819.718387319 thread=2 sequence=140
 channel default: deleting archived log(s)
 archived log file name=+FRA1/dbtr07/archivelog/2011_04_06/thread_1_seq_161.13854.718387211 RECID=824 STAMP=718387211
 channel default: deleting archived log(s)
 archived log file name=+FRA1/dbtr07/archivelog/2011_04_06/thread_2_seq_140.1819.718387319 RECID=825 STAMP=718387319
 channel default: deleting archived log(s)
 archived log file name=+FRA1/dbtr07/archivelog/2011_04_06/thread_3_seq_131.16592.718387087 RECID=823 STAMP=718387086
 media recovery complete, elapsed time: 00:00:02
 Finished recover at 07.04.11

My Oracle Notes

Tag: Recovery Manager

RMAN Backup Recovery Test

Menu