RMAN on Multitenant DB – Awareness of the Backup Optimization Behavior

Recovery Manager (RMAN) is one of the most popular Oracle databases components with unique Backup/Recovery features. It is fully integrated with the Multitenant Architecture allowing to implement Manage Many-Databases-as-One strategy.

RMAN permits to customize and save several database parameters used during the backup and recovery operations. Such parameters define for example the backup retention policy, the default device type,  how many archivelog copy should be stored, if the backup-sets should be compressed and/or encrypted and so on…

 

Below an example of RMAN setup with the highlight of the parameter CONFIGURE BACKUP OPTIMIZATION ON discussed on the next sections.

RMAN> show all;

RMAN configuration parameters for database with db_unique_name CEFUPRD are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 8 DAYS;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/BACKUP/Databases/CEFUPRD/%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/BACKUP/Databases/CEFUPRD/%d_%T_%U';
CONFIGURE MAXSETSIZE TO UNLIMITED;
CONFIGURE ENCRYPTION FOR DATABASE OFF;
CONFIGURE ENCRYPTION ALGORITHM 'AES128';
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE;
CONFIGURE RMAN OUTPUT TO KEEP FOR 10 DAYS;
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY BACKED UP 1 TIMES TO DISK;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/fast_recovery_area/rcocefuprd/cefuprd/snapcf_cefuprd.f';

RMAN>

 

 

Effects of RMAN Backup Optimization ON/OFF

In a Multitenant environment is more important than ever to understand the effects of the parameter CONFIGURE BACKUP OPTIMIZATION which can be set to ON or OFF.

Behavion when set ON

If RMAN determines that a file is identical and it has been backed up, then it is a candidate to be skipped. RMAN must do further checking to determine whether to skip the file, however, because both the retention policy and the backup duplexing feature are factors in the algorithm that determines whether RMAN has sufficient backups on the specified device type. (Definition from Oracle Backup Recovery User’s Guide).

Behavion when set OFF

The RMAN backup always includes all files no matter if they are identical and already backed up within the backup retention window.

 

 

What happens by migrating from Non-CDB to PDB?

Assuming that we have just migrated a non-CDB database to PDB and our pluggable database has 4 tablespaces all open read/write.  The container uses the same RMAN setup included on the top of this page, with CONFIGURE BACKUP OPTIMIZATION ON.

Dispite having a FULL database backup every night, only 1 backup every 8 days will be complete and consistent, because the RMAN backup optimization algorithm will detect the SEED PDB datafiles unchanged and it will skip those files. Therefore if we restore the CDB using the backup-sets generated by one FULL database backup, with no access to the rest of backup-sets inside the retention window, there are great probabilities that the restore will fail.

 

Extract of the CDB backup log which shows that the PDB$SEED datafiles have been skipped because already backed up 1 time during the last 8 days.

RMAN> BACKUP AS COMPRESSED BACKUPSET INCREMENTAL LEVEL = 0 DATABASE PLUS ARCHIVELOG NOT BACKED UP 1 TIMES; 

Starting backup at May 15 2018 00:35:07
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=9 instance=clgbprd1 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=168 instance=clgbprd1 device type=DISK
skipping archived logs of thread 1 from sequence 39516 to 39931; already backed up
skipping archived logs of thread 2 from sequence 34457 to 34749; already backed up
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=2 sequence=34774 RECID=148645 STAMP=976088413
input archived log thread=2 sequence=34775 RECID=148649 STAMP=976088467
input archived log thread=1 sequence=39944 RECID=148655 STAMP=976088552
input archived log thread=2 sequence=34776 RECID=148651 STAMP=976088509
input archived log thread=2 sequence=34777 RECID=148653 STAMP=976088551
input archived log thread=2 sequence=34778 RECID=148657 STAMP=976088700
input archived log thread=1 sequence=39945 RECID=148662 STAMP=976088937
input archived log thread=2 sequence=34779 RECID=148659 STAMP=976088838

...
Starting backup at May 15 2018 00:50:02
using channel ORA_DISK_1
using channel ORA_DISK_2
skipping datafile 2; already backed up 1 time(s)
skipping datafile 4; already backed up 1 time(s)
channel ORA_DISK_1: starting compressed incremental level 0 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
...

 

Using only the backup-sets above to restore the CDB means that Oracle has to recreate the two skipped datafiles (number 2 and 4) applying the archived logs generated during the initial CDB provisioning.

To note that the full backup starts including archived log from the following sequence:

  • For the Thread 1 – Sequence  39944
  • For the Thread 2 – Sequence  34774

But when Oracle initiates the Media Recovery, it complains because the archived log Thread 1 – Sequence 1 is unavailable:

RMAN> run {
allocate auxiliary channel dsk1 type disk ;
2> allocate auxiliary channel dsk2 type disk ;
allocate auxiliary channel dsk3 type disk ;
allocate auxiliary channel dsk4 type disk ;
3> allocate auxiliary channel dsk5 type disk ;
4> allocate auxiliary channel dsk6 type disk ;
5> duplicate database to 'CEFUAUX' noopen backup location '/BACKUP/Databases/CEFUPRD/backup_20180515_only' nofilenamecheck;
}6> 7> 8> 9>

allocated channel: dsk1
channel dsk1: SID=322 device type=DISK

allocated channel: dsk2
channel dsk2: SID=471 device type=DISK

allocated channel: dsk3
channel dsk3: SID=9 device type=DISK

allocated channel: dsk4
channel dsk4: SID=166 device type=DISK

allocated channel: dsk5
channel dsk5: SID=323 device type=DISK

allocated channel: dsk6
channel dsk6: SID=478 device type=DISK

Starting Duplicate Db at May 15 2018 09:29:15

....

contents of Memory Script:
{
 set until scn 2372623043;
 recover
 clone database
 delete archivelog
 ;
}
executing Memory Script

executing command: SET until clause

Starting recover at May 15 2018 11:24:39

starting media recovery

unable to find archived log
archived log thread=1 sequence=1
Oracle instance started

 

I hope this example helped to understand that while migrating from non-CDB to Multitenant, many Administration tasks should be carefully reviewed due to major architecture changes.

 


 

Advertisements

Duplicate database from active database

— Source database     = TRAC
— Duplicate database = TDUP10

##############################
## PREREQUISITES for Cloning
##############################

## Create the diag structure
 mkdir -p <diag_directory_DB_Name>
## Generate the PFILE from the Original DB and copy to the target host:
 alter system create pfile'/tmp/pfile.ora' from spfile;
 scp /tmp/pfile.ora lclus01:/tmp
## Adjust the following pfile parameters:
 DB_NAME
 LOG_FILE_NAME_CONVERT
 DB_FILE_NAME_CONVERT
 DB_CREATE_FILE_DEST
##Statup nomount and create the spfile:
 alter system create spfile='+DATA/TDUP10/spfile_TDUP10.ora' from pfile'/tmp/pfile.ora';
#############################################################
##############################
 Network Configuration
 ##############################
##Listener Static Entry:
 SID_LIST_LISTENER =
 (SID_LIST =
 (SID_DESC =
 (SID_NAME = PLSExtProc)
 (ORACLE_HOME = /u01/oracle/product/11.2.0.1)
 (PROGRAM = extproc)
 )
 (SID_DESC =
 (global_dbname = TDUP10.emilianofusaglia.net)
 (ORACLE_HOME = /u01/oracle/product/11.2.0.1)
 (sid_name = TDUP11)
 )
 )
##TNS entry
 TDUP10.emilianofusaglia.net =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = loraclu-scan.emilianofusaglia.net)(PORT = 1526))
 (CONNECT_DATA =
 (SERVER = DEDICATED)
 (SERVICE_NAME = TDUP10.emilianofusaglia.net)
 )
 )
#############################################################
##############################
 ##RMAN Duplicate
 ##############################
#rman
 connect target /
 connect auxiliary sys/xxxxx@TDUP10.emilianofusaglia.net
 duplicate target database
 to TDUP10
 from active database
 DB_FILE_NAME_CONVERT 'TRAC','TDUP10'
 skip tablespace POR_READONLY;
##Register the database into the Grid Infrastructure
 srvctl add database -d TDUP10 -o $ORACLE_HOME
 srvctl add instance -d TDUP10 -i TDUP11 -n lclus01
 srvctl add instance -d TDUP10 -i TDUP12 -n lclus02
##Create the password file on each node using the utility orapwd.
 #cd $ORACLE_HOME/dbs
 #orapwd file=<password_file_name> password=<sys_password> entries=n
## Add oratab entry
 <DB_NAME>:<ORACLE_HOME>:N

Data Guard Broker

###################################################
##      How to configure DATA GUARD BROKER    ##
###################################################

# Create the data guard broker files

--Primary
alter system set dg_broker_config_file1 = '+DATA1/TEFOXTR/DATAGUARDCONFIG/brokerconfig01.dat';
alter system set dg_broker_config_file2 = '+FRA1/TEFOXTR/DATAGUARDCONFIG/brokerconfig02.dat';

--Standby
alter system set dg_broker_config_file1 = '+DATA1/TEFOXZH/DATAGUARDCONFIG/brokerconfig01.dat';
alter system set dg_broker_config_file2 = '+FRA1/TEFOXZH/DATAGUARDCONFIG/brokerconfig02.dat';


--Start the broker on both databases
alter system set dg_broker_start=true;


--From the primary database connect to the broker and create the configuration
#dgmgrl

DGMGRL>
connect sys/xxxxxxx@TEFOXTR.emilianofusaglia.net

--Create the configuration for primary database
create configuration 'BRKTEFOX' as primary database is 'TEFOXTR' connect identifier is TEFOXTR_DGBHA.emilianofusaglia.net;

--Add standby database
add database 'TEFOXZH' as connect identifier is TEFOXZH_DGBHA.emilianofusaglia.net;

--Setup the properties
edit database 'TEFOXTR' set property 'LogXptMode' = 'SYNC';
edit database 'TEFOXZH' set property 'LogXptMode' = 'SYNC';
--or
edit database 'TEFOXTR' set property 'LogXptMode' = 'ASYNC';
edit database 'TEFOXZH' set property 'LogXptMode' = 'ASYNC';

edit configuration set protection mode as maxprotection;
--or
edit configuration set protection mode as maxavailability;
--or
edit configuration set protection mode as maxperformance;

edit database 'TEFOXTR' set property 'NetTimeout' = '20';
edit database 'TEFOXZH' set property 'NetTimeout' = '20';

edit database 'TEFOXTR' set property 'Binding' = 'MANDATORY';
edit database 'TEFOXZH' set property 'Binding' = 'MANDATORY';

enable configuration;

-- Switchover command:
DGMGRL> SWITCHOVER to "TEFOXZH";

--Stop Recovery
edit database 'TEFOXZH' set state = 'APPLY-OFF';
--Start Recovery
edit database 'TEFOXZH' set state = 'APPLY-ON';
edit database 'TEFOXZH' set state = 'APPLY-ON' WITH APPLY INSTANCE ='TEFOXZH1';

--Enable ArchiveLog Tracing on Primary and Standby for Troubleshooting
edit instance 'TEFOXTR1' on database 'TEFOXTR' set property 'LogArchiveTrace' = '1';    
edit instance 'TEFOXZH1' on database 'TEFOXZH' set property 'LogArchiveTrace' = '6345';

Oracle 11gR1 Data Guard Setup

############################################################
## DATA GUARD IMPLEMENTATION on 11g R1 RAC ##
############################################################

--Primary DB: USA10
--Standby DB: EURO10

############################
--From the Primary Database:
alter database force logging;

alter database add standby logfile  size 1G;
alter database add standby logfile  size 1G;
alter database add standby logfile  size 1G;
alter database add standby logfile  size 1G;
alter database add standby logfile  size 1G;
alter database add standby logfile  size 1G;
alter database add standby logfile  size 1G;
alter database add standby logfile  size 1G;
alter database add standby logfile  size 1G;

select * from v$standby_log;

 alter system set parallel_execution_message_size=8192 scope=spfile;
 alter system set fast_start_mttr_target=3600;
 
############################
--From the Standby Site:

--Dump the pfile and change the following parameters for the Standby:
*.control_files='+DATA1/EURO10/CONTROLFILE/CURRENT01.CTR','+FRA1/EURO10/CONTROLFILE/CURRENT02.CTR'
*.db_file_name_convert='/USA10/','/EURO10/'
*.log_file_name_convert='/USA10/','/EURO10/'
*.db_unique_name='EURO10'
--AS this is a single instance RAC
_disable_interface_checking = TRUE


LISTENER =
  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = lneuron01.emilianofusaglia.net)(PORT = 1526))
  )

alter system set local_listener='LISTENER' scope=spfile sid='EURO11';
---
mkdir -p /u01/oracle/admin/EURO10/adump
mkdir -p /u01/oracle/diag/rdbms/euro10/EURO11/cdump


######################################################################
 ################## #Network Congiguration ########################
######################################################################


##########################################
## Static Listener Entries:
##########################################

--Primary Cluster Node 1
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/oracle/product/11.1.0.7)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (global_dbname = USA10_DGMGRL.emilianofusaglia.net)
      (ORACLE_HOME = /u01/oracle/product/11.1.0.7)
      (sid_name = USA11)
    )
    (SID_DESC =
      (global_dbname = USA10_DGB.emilianofusaglia.net)
      (ORACLE_HOME = /u01/oracle/product/11.1.0.7)
      (sid_name = USA11)
    )
    (SID_DESC =
      (global_dbname = USA10.emilianofusaglia.net)
      (ORACLE_HOME = /u01/oracle/product/11.1.0.7)
      (sid_name = USA11)
    )
  )

--Primary Cluster Node 2
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/oracle/product/11.1.0.7)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (global_dbname = USA10_DGMGRL.emilianofusaglia.net)
      (ORACLE_HOME = /u01/oracle/product/11.1.0.7)
      (sid_name = USA12)
    )
    (SID_DESC =
      (global_dbname = USA10_DGB.emilianofusaglia.net)
      (ORACLE_HOME = /u01/oracle/product/11.1.0.7)
      (sid_name = USA12)
    )
    (SID_DESC =
      (global_dbname = USA10.emilianofusaglia.net)
      (ORACLE_HOME = /u01/oracle/product/11.1.0.7)
      (sid_name = USA12)
    )
  )

--Primary Cluster Node 3
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/oracle/product/11.1.0.7)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (global_dbname = USA10_DGMGRL.emilianofusaglia.net)
      (ORACLE_HOME = /u01/oracle/product/11.1.0.7)
      (sid_name = USA13)
    )
    (SID_DESC =
      (global_dbname = USA10_DGB.emilianofusaglia.net)
      (ORACLE_HOME = /u01/oracle/product/11.1.0.7)
      (sid_name = USA13)
    )
    (SID_DESC =
      (global_dbname = USA10.emilianofusaglia.net)
      (ORACLE_HOME = /u01/oracle/product/11.1.0.7)
      (sid_name = USA13)
    )
  )

--Standby Cluster Node 1
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/oracle/product/11.1.0.7)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (global_dbname = EURO10_DGMGRL.emilianofusaglia.net)
      (ORACLE_HOME = /u01/oracle/product/11.1.0.7)
      (sid_name = EURO11)
    )
    (SID_DESC =
      (global_dbname = EURO10_DGB.emilianofusaglia.net)
      (ORACLE_HOME = /u01/oracle/product/11.1.0.7)
      (sid_name = EURO11)
    )
    (SID_DESC =
      (global_dbname = EURO10.emilianofusaglia.net)
      (ORACLE_HOME = /u01/oracle/product/11.1.0.7)
      (sid_name = EURO11)
    )
  )


##########################################
##TNS Entries Primary & Standby Cluster
##########################################
EURO11.emilianofusaglia.net =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = lneuron01-vip.emilianofusaglia.net)(PORT = 1526))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = EURO10.emilianofusaglia.net)
      (INSTANCE_NAME = EURO11)
    )
  )

EURO10.emilianofusaglia.net =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = lneuron01-vip.emilianofusaglia.net)(PORT = 1526))
      (FAILOVER = on)
      (LOAD_BALANCE = on)
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = EURO10.emilianofusaglia.net)
    )
  )

USA10.emilianofusaglia.net =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = lnusan01-vip.emilianofusaglia.net)(PORT = 1526))
      (ADDRESS = (PROTOCOL = TCP)(HOST = lnusan02-vip.emilianofusaglia.net)(PORT = 1526))
      (ADDRESS = (PROTOCOL = TCP)(HOST = lnusan03-vip.emilianofusaglia.net)(PORT = 1526))
      (FAILOVER = on)
      (LOAD_BALANCE = on)
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = USA10.emilianofusaglia.net)
    )
  )


##########################################
## Standby Controlfile Setup ##
##########################################

alter database create standby controlfile as '/u01/oracle/emiliano/USA10.stby.ctl';

# scp  /u01/oracle/emiliano/USA10.stby.ctl lneuron01:/tmp


### Execute the following steps:
Startup nomount pfile='/u01/oracle/emiliano/PFILES/EURO10_for_GD.ora';
Create the spfile on ASM
Startup nomount exclusive;


## Do not RESTORE the Controlfile automatically DONE by the CLONE Procedure
## Which update also Contro_file parameter into SPFIE!!!!
rman target /
RMAN> restore controlfile from '/u01/oracle/emiliano/USA10.stby.ctl';

---
##########################################
## Duplicate the Database
##########################################

##From Primary DB Restore the DB to the Standby Side:
rman
connect target sys/xxxxxxx@USA10                             ----PS No domain for Target
connect auxiliary sys/xxxxxxx@EURO10.emilianofusaglia.net      ----PS Use domain for Auxiliary

run {
   allocate channel p1 type disk;
   allocate auxiliary channel s1 type disk;
   duplicate target database for standby from active database
   dorecover;
    }

--or

run {
   allocate channel p1 type disk;
   allocate channel p2 type disk;
   allocate channel p3 type disk;
   allocate channel p4 type disk;
   allocate auxiliary channel s1 type disk;
   allocate auxiliary channel s2 type disk;
   allocate auxiliary channel s3 type disk;
   allocate auxiliary channel s4 type disk;
   duplicate target database for standby from active database
   dorecover;
    }

##########################################
## Register the Standby Database to CRS
##########################################

srvctl add database -d EURO10 -o $ORACLE_HOME
srvctl add instance -d EURO10 -i EURO11 -n lneuron01

##########################################
## Data Guard Broker Configuration
##########################################

--Primary
alter system set dg_broker_config_file1 = '+DATA1/USA10/DATAGUARDCONFIG/brokerconfig01.dat';
alter system set dg_broker_config_file2 = '+DATA1/USA10/DATAGUARDCONFIG/brokerconfig02.dat';

--Standby
alter system set dg_broker_config_file1 = '+DATA1/EURO10/DATAGUARDCONFIG/brokerconfig01.dat';
alter system set dg_broker_config_file2 = '+DATA1/EURO10/DATAGUARDCONFIG/brokerconfig02.dat';

--on both databases
alter system set dg_broker_start=true;

--From a primary node connect to the broker and create the configuration

#dgmgrl

DGMGRL> connect sys/xxxxxxxx@USA10.emilianofusaglia.net
Connected.

create configuration 'CONFDG10' as primary database is 'USA10' connect identifier is USA10.emilianofusaglia.net;
   
add database 'EURO10' as connect identifier is EURO10.emilianofusaglia.net;

edit database 'USA10' set property 'LogXptMode' = 'SYNC';
edit database 'EURO10' set property 'LogXptMode' = 'SYNC';

edit database 'USA10' set property 'LogXptMode' = 'ASYNC';
edit database 'EURO10' set property 'LogXptMode' = 'ASYNC';

--edit configuration set protection mode as maxavailability;
edit configuration set protection mode as maxprotection;
--edit configuration set protection mode as maxavailability;
--edit configuration set protection mode as maxperformance;
enable configuration;

edit database 'USA10' set property 'NetTimeout' = '20';
edit database 'EURO10' set property 'NetTimeout' = '20';

exit;

DGMGRL> SWITCHOVER to "EURO10";

--Stop Recovery
edit database 'EURO10' set state = 'APPLY-OFF';
--Start Recovery
edit database 'EURO10' set state = 'APPLY-ON';

--Enabling ArchiveLog Tracing on Primary and Standby Good for Troubleshooting!!
edit instance 'USA11' on database 'USA10' set property 'LogArchiveTrace' = '1';    
edit instance 'USA12' on database 'USA10' set property 'LogArchiveTrace' = '1';    
edit instance 'USA13' on database 'USA10' set property 'LogArchiveTrace' = '1';    

edit instance 'EURO11' on database 'EURO10' set property 'LogArchiveTrace' = '6345';    

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

RMAN Backup Recovery Test

#######################################################
##                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