Translate

Saturday, June 29, 2013

The Engineering of the Intelligent Backup (II)


An Open Call to Study and Research 
“the Backup Fatigue Syndrome”

During my recent NYOUG presentation at St. John University, I refer to the relevance of data file size sorting in relation to backup performance and backup performance tuning degradation  when significantly larger files are left at the end of the backup, which I will hereby refer to as “the backup fatigue syndrome.”  This brief article is a summary of informal statistics on this problem, which I have never formalized for truthful statistical research.  It looks like this sydrome is associated with various factors such as, but not limited to, logic inherent to OS-level I/O, logic associated with generic backupset-driven technology, and inefficiency of large storage devices, in general. The combination of such factors, among others, is particularly critical to the appearance of the backup fatigue syndrome.

For the past twenty years or so, I have observed various scenarios where a backup operation took a bit longer or much longer than expected, to be more precise. These scenarios involved not only various instances of Oracle database backup but also OS-level backups, involving Windows, Linux, MacOS, and Solaris, and network-driven backups; and different media type, such as essentially tape or drive.  These observations lead me to the systematic testing of verifying that the order by size in which files or backup pieces are used to create the corresponding backup sets has a significant impact in the backup duration. 

This could simply mean that based a backup operation is not necessarily commutative by content (data file) size and that the order (by size) in which datafiles are backed up does result in different duration; in particular, when significantly large files (huge in comparison to the rest of datafiles in the backup) are placed at the end of the backupset or last in the order which files are backed up. This means that an abstraction would not allow me to define an Abelian Group, as a mathematical analogy; or more exactly, a class or abstract object that defines such a group, as the order by size does have an impact.  Comparing mathematical concepts is quite practical here, although it may appear inappropriate to most people.  

The purpose of this note is to actually entice a comprehensive research on this topic.  Explicitly, it is important to determine the following:

1.     The proportion, if any, at which backup performance latency occurs and results in exponential degradation when only a serial channel is used; and  when nearly exponential degradation occurs when parallelism is used.

2.     A method to determine such as proportion of small file to large files, and the proportion between the number of small files, and the number of larger files; such as, for instance, in Oracle, the number of SMALLFILE tablespace datafiles in comparison to the number of BIGFILE tablespace datafiles. The proportion could be established via an actual mathematical ratio or a statistical index or regression model, or as a stochastic model with controlled probability via a Bayes or Markovian or Levy process.

3.     The smoothing of the degradation impact as various level of parallelism are used or increased. In Oracle RMAN technology, this explicitly relates to the actual number of channels used from the number established.


4.     The average ratio from the rather small number large files to the number large number of small files, which does not cause for the backup fatigue syndrome to occur. Otherwise said, if there is a specific generalized proportion that one can used without the degradation to occur.

5.     A (formula-based) method to determine a factor or index to properly estimate the appropriate or custom level of parallelism for each backup operation, as needed.



Based on the following analysis derived from a good number of observations while running RMAN and OS-level backups where signficantly larger files were left a the end of the backup or backup set, it is possible to postulate the “Backup Fatigue Syndrome”, as follows:

Postulate I

When a significant large file is left at the end of a backup, having already backed up a comparatively very large number of much smaller files first, there will be a significant degradation in the backup performance, and the backup duration will appear to increase exponentially.

Postulate II

Based on consistent replicated observations, if the order of data files based on their size is an important factor in attaining optimal backup performance, then there exists an optimal backup order by size for any backup operation, such that the backup duration is optimized.

The following observations are made in relation to the parallel degee used, namely:

·      For serial backups (parallel degree 1), based on a good number of observations, the decreasing sort by size of data files should produce an optimal duration for backup operations, under similar environmental factors such as processor, memory, operating system, and storage, and various others.

·      For parallel backups (parallel degree 2 or greater), based on a good number of observations, the average of decreasing backupsets as parallelized (sorted by size of data files) should produce an optimal duration for backup operations, under similar environmental factors such as processor, memory, operating system, OS clustering, database clustering (RAC, HARD or other similar) , and storage, and various others.

Based on these postulates, it is important to consider expanding RMAN backup capabilities, as well as third party capabilities, to take advantage from these important observations driving the backup fatigue syndrome postulates.

Indeed, I believe that the backup fatigue syndrome exists as a combination of factors, in particular, how backup pieces and backupsets are aggregated into a full backup, and various technology factors, such as large storage system management involving file system based, library-based, and raw devices.

Although I have a good number of sufficient observations, with perceived backup fatigue, which repeated in similar observed scenarios, I currently do not have enough resources to conduct a more official and formal research on this matter using the appropriate tools, statistical methods, and experiment design; so I would like to invite the storage networking community to participate with me in a conjoint research on the backup fatigue syndrome, which could improve the logistic associated with backup technology in the era of big data and truly big data files.

Some of the expectation that can be derived from these perspectives is to provide a sorting by size capability inherit within RMAN rather than writing a script to attain these goals when a disparity of on the number of small files compared to a small number of huge files occurs.

Posted by at No comments:

Wednesday, June 19, 2013

The Engineering of the Intelligent Backup


The following is an excerpt of the paper accompanying my recent NYOUG presentation on the same topic.  You are welcome to download the pdf version of this paper at www.adnresearch.com/documents.php.

Tagging, Encrypting, and Encoding 
with RMAN
  
Anthony D. Noriega, MSCS, MBA, BSSE, OCP
President and Lead Database Consultant at ADN Research

1.  Introduction 

This document introduces a general dynamic model to backup and recovery (BR) and disaster recovery (DR) operations applicable to Oracle standalone and the high-availability cluster environments, involving full, incremental, and cumulative incremental backups, with extension to database cloning for high-availability, backup tagging. and encoding for further granularity, such as but not limited to, block-level recovery, backup and restore scripting, and backup validation among others.
In principle, the focus is solely on establishing a dynamic backup capable of dynamically implementing and executing full database backup, incremental level 1, and cumulative incremental level 1 backups, with custom autodrop job instantiation, and will extend its coverage to cloning for high-availability, disaster recovery topics such as RMAN database backup tagging, and various other topics of high-value for a complete corporate backup and recovery paradigm.
In most corporate environments today, the current backup policy uses fundamental features features, but it in many instances, it does not provide a unified model to fully utilize resources and capabilities.
This model will reveal the significance of private encoding, tagging, and encryption with RMAN, not only to ensure the confidentiality of the backup, but also to commit to the highest level of data privacy and security.

2.  Justification: Weaknesses of Existing Backup Policies

The following is a good-will analysis regarding the weaknesses of the current backup policies bases on a SWAT general perspective.

2.1    Security

Current backup policies lack the fundamental of database security and privacy, and were not implemented to attain regulatory compliance in their respective industry.

2.2    Guaranteed Restore Point

In at least a few scenarios, backup restores can fail due to the lack of proper synchronization and database activity at backup time, so recovery procedures may be needed ipso facto.

2.3    Integrity and Unity

In many scenarios, lack of backup integrity, as the backup depends on the availability of the entire set of archived logs in the file system for a complete or point-in-time recovery. If archived redo logs were not available in disk or tape, due to a disk crash or a tape loss or destruction, then full or point‑in‑time recovery of any nature would not be possible. This is more significant when a corporation’s current policy utilizes the recovery catalog in the same database instance, a model historically not recommeded by Oracle, but still practised.

2.4    Lags in Backup Timeframe

Corporate statistics and random qualitative samples could show that backup models have either potential or neglected lagging timeframes due to its imperfect integrity, which include scenarios such as, in the commonest cases, tape loss or destruction and disk drive or volume damage.

2.5    Timestamps on backup sets and backup pieces

Lack of proper timestamps, in principle, prevent to identify if a backup piece belongs to a specific backup set, and if so, whether it was actually performed at a specific time.  Therefore, searching for the appropriate file can only be accomplished by looking at a directory in the file system, being restricted to a folder-based component control.  However, if a file is misplaced for any reason, it would not be simple to identify such a file and restore it easily to the appropriate location (folder), the only place where it might be functional

2.6    Required Regulatory Compliance

A backup model may need to be encrypted and secured in accordance with an explicit regulatory compliance, such as Mastercard Act, HIPAA, SOX, or various others, meaning that anyone who has access to the backup could invade the company’s privacy and confidentiality and restore it somewhere else without any control at all.

2.7    Backup Encryption for Regulatory Compliance

Certain regulatory compliance acts require that all data be encrypted for data privacy and integrity.  Many related backup models do not apply this regulation in the fear that encryption might cause more trouble than helping data privacy and security.

2.8    Password Security

Many existing backup models using a trivial password for target and catalog backup users, violating cloud security, corporate security, and industry and government regulations.

2.9    Backup Reliability

While mant backup models are reliable, integration with third party vendors may cause for their reliability to decrease. Similarly, in some instances, backup reliability is affected by channel bandwidth, process concurrency, and inconsistent settings for parameters such as job_queue_processes, db_recovery_file_dest_size, and various others.

2.10  Integration with other Oracle technologies

Many existing backup models do not integrate with other Oracle technologies such as Oracle Flashbackup, restore points, Oracle Transparent Data Encryption (TDE), block change tracking, and other SQL and PL/SQL supplied resources.

2.11  Compression Resources and Capabilities

Most corporate backup models do not use RMAN native compression compression at any level or backup granularity, and limit themselves to OS-level compression after backup completion in many real world scenarios. This feature has been rather underutilized in practicality without justification.

2.12  Justification for a new improved policy and standard

A backup policy using the engineering model presented here will introduce custom scripts easy to deploy and use, exhibiting a variety of encoding, tagging, and encryption capabilities, regarded as powerfully useful to robust IT management, corporate governance, and regulatory compliance.

In addition to this, this backup policy can correct granular errors in an existing tiered backup model, which guarantees the assurance of a perfect restore in the event of a disaster due to various points of isolated fault tolerance.  This is mission critical to a fast and successful recovery with optimal MTTR value.

Furthermore, the new policy will emphasize improved reliability, data security, privacy, and confidentiality, for improved regulatory compliance. This new policy will be progressively integrated with new technologies, such as, Flashback, database restore points, advanced encryption standards with Oracle TDE, and advanced compression , among others, in the berth of a new Oracle database release. But most of all it engineers an open model capable to produce an optimal backup model with unlimited accessibility control.

3.   Backup Specifications

3.1    Usability

A backup policy utilizing tagging, encoding, and encryption should also allow for easy administration of any backup by the average DBA. However, it should be difficult enough for a common user to understand or use, and encoded enough to encompass corporate confidentiality and privacy. These usability and relevant user interface concerns are applicable to both a command line interface (CLI), and in some instances have access to Oracle Enterprise Manager.


3.2    Functionality

The backup will provide a variety of functionality including, but not limited to:

Further backup granularity by database component, file type, backup level and type, and specific functionality requirements are discussed in the next numeral.

3.3    Backup Specific Requirements

3.3.1   Prepared Scripts Ready to Run

The following scripts should be provided as part of a custom corporate solution, namely:
  • Full incremental level 0 RMAN backup
  • Incremental Level 1 RMAN backup
  • Cumulative incremental level 1 RMAN backup
  • Full backup validation
  • Incremental cumulative backup validation
  • Full and cumulative incremental backup validation
  • Full backup restore
  • DBPIT/SCN (Database point in time recovery) restore
  • DBPIT/SCN (Database point in time recovery) restore with validation
  • TSPIT/SCN (Tablespace point in time recovery) restore
  • DBFPIT/SCN (Tablespace point in time recovery) restore
  • Block PIT/SCN (Block recovery) restore.

3.3.2   Shells

Written and executed as Bash, C, Korn, or Perl shells in Linux and Unix enviroments; batch, Powershell, or Perl script in Windows; or Oracle PL/SQL utilizing DBMS_SCHEDULER supplied package blocks used to call or invoke each one of the scripts previously described, usually as an executable.

3.3.3   Policies

  • DBA Managers will schedule the appropriate dates and times to run both the full (incremental level 0), incremental level 1 or cumulative incremental level 1 backups.
  • Backups will belong to a special consumer group category, which will permit a special authentication for batch jobs, without displaying any password.
  • Backups scripts can either be stored or run from a secured or encrypted directory.
  • Backups should be preferably encrypted, but IT governance rules could be implemented to keep or provide support for unencrypted backups for business reasons.

3.3.4   Schedule

  • Full backup after midnight on a week-end day; the recommended schedule is Saturday at 00:00:00 hours local database time
  • Cumulative incremental level 3 different weekdays, after midnight. For instance, recommended hours are week days, Monday, Wednesday and Friday at 00:00:00 hours local database time
  • Specific days of the week and exact times are to be specified by DBA managers.

3.3.5   Invocation Method

The preferred method is to utilize a shell script to start the backup, but it can also be driven through a stored script or procedure or via other type of batch job.  A shell call is made to run an executable or a local or stored RMAN script or a combination of both.        

3.3.6   Backup Types Used

o   Full backup (incremental level 0)
The full backup will run once a week over the week-end, as recommended by lead DBA management or as required by the circumstances.
o   Incremental level 1
o   Cumulative incremental level 1
This backup type will run twice during the weekdays, between full-time backups.

3.3.7   Backup Actions and Contents

The backup model must provide consistency and integrity in all and each one of the following database components, namely:
o   Data Files
o   Control Files
o   Archived Redo Log (Archivelog) Files and supplemental redo logs used with block change tracking
o   Spfile
The backup model should also enforce the business rules associated with guranteed restored points.

3.3.8   Special Features


The following features would properly characterize a good backup model, namely:

3.3.8.1   Compactness: Cohesion, Coupling, and Unity (Integrity)

The backup should have several levels of fault tolerance, but it should be compact.

3.3.8.2   Easy to Customize

Backup should only involve a few parameters, which are easy to administer by the average DBA.

3.3.8.3   Easy to deploy

Backup policy should provide a variety of simple resources to deploy in the entire corporate environment in a seamless fashion. It is recommended that the core code of the backup be stored in an RMAN script.

3.3.8.4   Easy to maintain throughout the company

Mandatory backup maintenance based on resilient periodic schedule, validating and verifying the backup to guarantee its data quality and integrity.

3.3.8.5   RMAN Scripting Usage

Both the full backup (incremental level 0) and the cumulative incremental level 1 backup will be driven by customized RMAN scripts easy to deploy onto the entire corporation.  Store scripting should be in place for daily use, but a copy of an equivalent non-stored script should be available at all times.  Similarly, a restore script is required to render:
3.3.8.5.1    Flexibility
o   Provide full backup restore completion, including restore of full backup (incremental level 0) and incremental level 1 or cumulative incremental level 1 backups.
o   Provide database point in time recovery
o   Provide tablespace point in time support 
3.3.8.5.2    Custom Media Formatting
A custom path to default directories or SBT tape will be provided.  When backing up strictly to drives, a tertiary backup to tape will be provided at the OS-level.  It is recommended to keep at least a full month of consecutive tape backups, with some redundancy.  This means that archived logs will be backed up both within the RMAN backup sets, and directly to tape at the OS-level through a conventional system backup or via a customize tape backup.

3.3.8.6   Backup Integrity

The current policy does not explicitly backup the archived redo logs within the RMAN backup sets, which constitutes a single point of failure to the backup consistency and unity, and all of the physical and logical locations, and business perspectives. An improvement can be made by customizing the backup to include tagged, encoded, and individual backup sets for each component.

3.3.8.7   Database Security and Data Privacy for Regulatory Compliance 

These special security, privacy, and compliance features include, but are not limited to:
3.3.8.7.1    Business Intelligence Implementation
3.3.6.7.1.1    Timestamps
Every backup piece should be properly marked with a timestamp, upon start and completion; as well every backup set should have a similar timestamp format upon completion of the backup set. This will enable consistency with specific SCNs and flashback technologies. This is attained through OS shells such as Bash and Korn shells in Linux and Unix or through a DOS console batch file, in either scenario with the following command:

set NLS_DATE_FORMAT=DD-MON-YYYY HH24:MI:SS (without any quotation marks)
In fact, this command is placed just before starting the shell invoking the RMAN script. Thus, each backup piece start and end will be marked with a consistent visible timestamp, which highly improves the DBA’s ability to analyze and use RMAN logs, and utilize them in future B/R and D/R database operations.
3.3.6.7.1.2    Backup Integrity and Fault-Tolerance
For administrative reasons, at the media level, the backup should be compact and fault tolerance, being self contained in one media type, with other backup layers tolerance levels, independently from whether the backup policy is strictly a tiered one or not. For instance, if the tape containing the archived logs is lost or destroyed, in the event of a disk drive failure affecting those logs, the RMAN backup should contain the required archived logs as close to or to the point of failure, and all along the recovery window of time.
3.3.6.7.1.3    Tagging and Encoding
Tagging should facilitate the backup type, the backup date and time, and searching for a backup set within the backup, a backup piece within the backup set, and a database file block within a backup piece. Tagging can be customized by the corporation needs, as well, and can be modified to meet those new requirements, for any attainable business intelligence purpose.
3.3.6.7.1.4    Logging RMAN Operations
Every RMAN backup as well as other RMAN operations such as —but not limited to—backup restore, database cloning, database and tablespace point in time recovery, must have a log of every event that takes place.
3.3.6.7.1.5    Backup Restore Operations
An optimal restore policy must:
o   Provide capabilities for successful full, point in time, SCN-based, or until cancel restores.
o   Utilize at least one RMAN stored script, whenever possible
o   Use tagging
o   Utilize company private encoding for backup pieces
o   Utilize an explicit configuration format
o   Provide an easy integration with company historic backup formats
o   Backup the archived redo log
o   Crosscheck backup components frequently
o   Supply a full incremental level 0 shell component
o   Supply a  cumulative incremental level 1 shell component
o   Supply a  incremental level 1 shell component
o   Provide a unique shell encompassing the three previously mentioned shells
o   Make smart usage of RMAN custom parameters such as FILESPERSET, TAG, and MAXSETSIZE, among others.
o   Provide encryption support for various encryption algorithms through Oracle TDE in conjunction with encryption wallets and related encryption methods.
o   Render easiness and flexibility to accept calls.
3.3.6.7.1.6    Encryption
The backup policy should support encryption in conjunction with an encryption method and algorithm selected by DBA to be used for that database instance. The backup should support encryption algorithms (e.g., MD5 and SHA1, among) provided with the Oracle database, and various encryption standards, such as DES, 3DES, and IPSEC, among others, and it should not conflict with the encryption provided with the Oracle Transparent Data Encryption (TDE) wallet through any method used.
3.3.6.7.1.7    Compression
Backup compression at various levels —from BASIC to LOW, to MEDIUM, to HIGH— should be available, in accordance to the company’s dynamic needs. It is recommended to license high level compression with Oracle Corporation.
3.3.6.7.1.8    Other Recommendations
o   Could be synchronized with Oracle FLASHBACK technologies and database restore points
o   Should print exact timestamp when a backup piece is starts to being backed up
o   Could use block change tracking.

4.    Basic Practices

4.1    Proposed Backup Scheduling 

BACKUP TYPE
LEVEL
WEEK DAY(S)
START TIME
ESTIMATED DURATION
FULL
INCREMENTAL LEVEL 0
SATURDAY
MIDNIGHT
4 HOURS
CUMULATIVE
INCREMENTAL LEVEL 1
MONDAY, WEDNESDAY, FRIDAY
MIDNIGHT
4 HOURS
Table 1.  Proposed backup schedules.
When scheduling a Oracle database RMAN backup, Lead DBA should follow a few basic principles, namely:
  • Ensure that the backup schedule does not conflict with any other processes, including non database operations, such, webcasts, videoconferences, and other processes affecting network bandwidth or constraining SAN or NAS or other high availability interconnects, thus, affecting RMAN channels
  • Ensure that a properly time-stamped log of all backup process actions takes places accordingly
  • Ensure that both database and OS accounts have the appropriate privileges and credentials to run the backup job.

4.2    Supplied Shells, PL/SQL and RMAN Scripts

The following RMAN, PL/SQL and RMAN scripts are provided in accordance to the following business process model, as described in the following diagram:

The diagram summarizes the core RMAN backup process as follows:

o   Creating RMAN directories (a one time action)
o   Creating the proposed  SP_RMANLINE procedure, main procedure used to run the backup within the PKG_UTL_RMAN extended package in compact mode.
o   Using the SP_CALLRMANLINE stored procedure or setting equivalent PL/SQL block to call the SP_RMANLINE stored procedure.
o   Running the grantall.sql SQL script in order to grant the appropriate privileges to directories and procedures and other required objects.
o   OSCommand PL/SQL stored function or procedure and Oscommand Java stored procedure to run OS-level commands, more practical in Linux and Unix environments, where the file mode must be stated. This is not required for Windows, since a batch file is an executable by default.
Although the diagram does not include a comprehensive structure of the PKG_UTL_RMAN package, it is hereby included below:
The backup’s proposed original package headers (constructors) are presented, as follows:
CREATE OR REPLACE PKG_UTL_RMAN IS­
    FUNCTION  sf_setOSconfig();            --    Sets on which operating system platform
                                             backup will run on
   FUNCTION  sf_generateTag();            --     Generates custom rman tag
   FUNCTION  sf_setCustomLineParam();     --     Customizes parameter portion of
    FUNCTION OsCommand_Run();                    -- Java-based PL/SQL external stored function
   PROCEDURE OsCommand_Exec();                   -- Java-based PL/SQL external stored procedure
    PROCEDURE sp_custom_rmanline1();       --    Customized version of RMAN backup
                                             (sp_rmanline procedure)
   PROCEDURE sp_call_custom_rmanline1();  --     entry point for default rman backup
   PROCEDURE sp_setPrivs();               --     Privilege setting for custom backup
   PROCEDURE sp_setCredentials();         --     Sets OS level credentials for
   
    PROCEDURE sp_changePwd();              --    Admin password changing and user
                                                 management
   PROCEDURE sp_changePrivs();            --     Changes DBa privileges in general
   PROCEDURE sp_addPrivDBA();             --     Adds a privileged DBA to use the RMAN
                                                 backup utility
   PROCEDURE sp_validatebkp()             --     Validates a backup or restore.
    PROCEDURE prc_master_restore()            -- Custom backup restore with many options.    
   PROCEDURE sp_rmanline1();              --     Default RMAN backup
   PROCEDURE sp_callrmanline();          --      entry point for default rman backup
END PKG_UTL_RMAN;
Exhibit 1.  Proposed RMAN Backup Utility (Package Headers/Constructors skeletal)
On a daily basis, it is expected for the SP_RMANLINE() procedure to run the backup after being invoked by SP_CALLRMANLINE().  Once the backup is deployed, there should be no DBA interaction, other than optionally monitoring the running jobs and consistently reviewing the RMAN logs.


Exhibit 2.  Backup Workflow

Exhibit 3. Backup Model Architecture

5.    Tagging Strategies

The tagging strategies and techniques for RMAN can involve, namely:
·      Using Oracle default tagging, which utilizes a smart timestamp.
·      Utilizing custom tagging supporting corporate backup management and data warehousing integration when managing backup data across platforms.
·      Applying tags specific to territories, timeframes, business units, goal seeking records and various others.

Also, tags can be customized dynamically using a programmatic interface passing actual parameters to RMAN substitution variables.

The following is an RMAN script using substitution variables, to be invoked by a shell with matching parameters:

set encryption &1;
run  {
         allocate  channel cH1 type disk;
         sql 'alter system archive log current';
         sql 'begin create_restore_point; end;';
         crosscheck archivelog all;
         release   channel cH1;
         allocate  channel cH1 type disk;
         allocate  channel cH2 type disk;
         allocate  channel cH3 type disk;
         allocate  channel cH4 type disk;
         set limit channel cH1 kbytes 16577216;
         set limit channel cH2 kbytes 16577216;
         set limit channel cH3 kbytes 16577216;
         set limit channel cH4 kbytes 16577216;
         BACKUP AS &2 BACKUPSET
            INCREMENTAL LEVEL &3 &4
            FORMAT '/home/oracle/app/oracle/flash_recovery_area/orcl/backupset/%T_%d_dbbkp_L0_%s%t%U'  tag = &7
         FILESPERSET &6
           (database);
         BACKUP AS &2 BACKUPSET
            INCREMENTAL LEVEL &3 &4
            FORMAT '/home/oracle/app/oracle/flash_recovery_area/orcl/backupset/%T_%d_ctlbkp_L0_%s%t%U' tag = &7
            (current controlfile);
            sql 'alter system archive log current';
          BACKUP AS &2 BACKUPSET
            INCREMENTAL LEVEL &3 &4
            FORMAT '/home/oracle/app/oracle/flash_recovery_area/orcl/backupset/%T_%d_arcbkp_L0_%s%t%U' tag = &7
             (archivelog all);
         backup spfile;
         sql 'alter database backup controlfile to trace';
         release channel cH1;
         release channel cH2;
         release channel cH3;
         release channel cH4;
       }
         resync catalog;
         list backupset of database;
         list backupset of controlfile;
         list backupset of archivelog all;
         list restore point all;
         report schema;
         validate check logical skip inaccessible database;
         restore validate database;
         restore validate archivelog all;
         report obsolete;
         delete noprompt obsolete;
         exit;

Exhibit 4. RMAN versatile script using substitution variables

Equivalently, the RMAN command line matching this script could look as follows:

${ORACLE_HOME}/bin/rman target sys/${SYSPWD}@adnibsx catalog rman/${RMANPWD}@adnibmsx @${RMAN_SCRIPT_DIR}/r_backup.rman  USING  'ON'  ' '   1  ' '  'adnibmsx' 1 'adnibmsx_INL1_201305011653'  LOG=${RMAN_LOG_DIR}/adnibmsx_r_backup.rman_L1_201305011653.log

Exhibit 5. RMAN call line in OS-shell.

6.    Encoding Strategies

Encoding strategies can be quite simple or extremely complex.  This backup model customizing corporate encoding through the usage of a function returning a string to be used anywhere in the backup, such as for instance:
·      Create a custom tag
·      Create a portion of the backup piece name
·      Create a naming convention for a backupset
·      Create  a naming convention for any other RMAN backup component.

The following function is an example of simple encoding:


FUNCTION sf_simple_encoding (string1  IN VARCHAR2, string2 IN VARCHAR2, string3 IN VARCHAR2) RETURN VARCHAR2 IS
v_custom_tag VARCHAR2(4000);
BEGIN

        SELECT RTRIM(string1)||RTRIM(string2)||RTRIM(string3) AS custom_encode
            INTO v_custom_tag
            FROM dual;

       RETURN v_custom_tag;

EXCEPTION WHEN OTHERS
THEN
raise_application_error(-20119,'Invalid values used.');
DBMS_OUTPUT.put_line(SQLERRM);
RETURN NULL;
END sf_simple_encoding;

Exhibit 6. Simple Encoding Function.

Then testing the function would produce the string to be used a tag name or other naming convention within the backup, passed as an actual parameter to the RMAN backup script.

SQL> get /tmp/tstfun.sql
1    begin
2    2     dbms_output.put_line(sf_simple_encoding('CERN','LAB1','TST321'));
3* end;
SQL> /
CERNLAB1TST321

Besides, complex functions can be implemented to customize an important aspect of the backup model, and this is not limited to a custom tagging strategy, but rather an extended corporate backup model can be easily implemented, which is more consistent with the convolution of big data and data warehousing environments, as well as smart storage hardware capabilities.

7.    Other Major Considerations 

7.1    RMAN Configuration Settings


The following is a recommended array of RMAN configuration settings useful to any environment, in general, but subject to any required adjustment due to regulatory compliance, business or resource constraints, capacity planning or performance tuning or any other de facto reasons.

RMAN> show all;

RMAN configuration parameters for database with db_unique_name ADNIBMSX are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO COMPRESSED BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT   'C:\app\oracle\flash_recovery_area\ADNIBMSX\BACKUPSET\%Y_%M_%D\ADNIBMSX_%
CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT   'C:\app\oracle\flash_recovery_area\ADNIBMSX\BACKUPSET\%Y_%M_%D\ADNIBMSX_%
CONFIGURE CHANNEL 3 DEVICE TYPE DISK FORMAT   'C:\app\oracle\flash_recovery_area\ADNIBMSX\BACKUPSET\%Y_%M_%D\ADNIBMSX_%
CONFIGURE CHANNEL 4 DEVICE TYPE DISK FORMAT   'C:\app\oracle\flash_recovery_area\ADNIBMSX\BACKUPSET\%Y_%M_%D\ADNIBMSX_%
CONFIGURE MAXSETSIZE TO UNLIMITED;
CONFIGURE ENCRYPTION FOR DATABASE ON;
CONFIGURE ENCRYPTION ALGORITHM 'AES128';
CONFIGURE COMPRESSION ALGORITHM 'HIGH' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE;
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'C:\APP\ORACLE\PRODUCT\11.2.0\DBHOME_4\DATABASE\SNCFADNIBMSX.ORA';

Exhibit 7.  RMAN Configuration Settings

7.2    Encryption

For the past few months, the Oracle DBA team has successfully tested the implementation of the Oracle Transparent Data Encryption (TDE) in conjunction with an encrypted backup and secure backup policies, using a file-based encryption wallet.  At the present time, the overall business process is reliable enough to be put into production. Cross-training among DBAs to attain full understanding and application of the backup process may be required for a thorough knowledge of the existing policy and the new changes to be applied.

7.2    Compression 

The current backup policy already uses Oracle Basic compression.  The new policy recommends using Oracle Advanced Compression with a HIGH compression setting rather than any other compression level.

7.3    Database Architecture

For many database environments, the current size of the database log files is only 50MB, which is already somewhat small for the size of essentially any database.  The fact that the archived redo logs may reside in different NAS or SAN location represents an additional issue for backup performance optimization as the involved propagation delay represents a major concern.

The minimum recommended size for the a log file size in each environment, is as follows:

ENVIRONMENT
MINIMUM
AVERAGE
MAXIMUM
DEVELOPMENT
100MB
150MB
200MB
SIT
200MB
300MB
400MB
UAT
300MB
500MB
800MB
PRODUCTION
500MB
800MB
2GB[1] 
Table 2. Sample Recommended database log files size.

7.4    Other Concerns

For most corporations, the backup policy should work in accordance with the implementation of capacity planning operating procedures and the investment and allocation of the appropriate resources for coordinated deployment and maintenance plans.


[1] Production log file sizing is strictly measured by the actual database size and daily production workload.

APPENDIX


A.       Sample Source Code


PROCEDURE  SP_RMANLINE(
                        ip_rmantuser         VARCHAR2,       
                        ip_rmantuserpwd      VARCHAR2,             
                        ip_rmancuser         VARCHAR2,             
                        ip_rmancuserpwd      VARCHAR2,             
                        ip_target            VARCHAR2,             
                        ip_rcvcat            VARCHAR2,
                        ip_rman_script_dir        VARCHAR2      DEFAULT 'RMAN_SCRIPT_DIR',
                        ip_rman_batch_dir         VARCHAR2      DEFAULT 'RMAN_BATCH_DIR',
                        ip_Fname                  VARCHAR2      DEFAULT 'r_backup.rman',
                        ip_log_dir                VARCHAR2      DEFAULT 'RMAN_LOG_DIR',
                        ip_lvl                    NUMBER        DEFAULT 0,
                        ip_Cum_Opt                VARCHAR2,
                        ip_Oracle_bin_dir         VARCHAR2      DEFAULT 'ORACLE_BIN_DIR',
                        ip_Comp_Flag VARCHAR2  DEFAULT 'C',
                        ip_Encr_Flag  VARCHAR2  DEFAULT 'on',
                        ip_Filesps    NUMBER     DEFAULT 1
                      ) IS           

crlf            CONSTANT VARCHAR2(10) := CHR(13)||CHR(10);

v_exit          VARCHAR2(30) := 'exit'||CHR(13)||CHR(10);

v_user          VARCHAR2(30);

v_rmanline        VARCHAR2(4000);     

v_Fname           VARCHAR2(4000);

v_dFname          VARCHAR2(4000);

v_Path_Dir        VARCHAR2(4000);

v_Bin_Dir         VARCHAR2(4000);

v_logPath_Dir   VARCHAR2(4000);

v_ts            VARCHAR2(20);

v_ts2           VARCHAR2(20);

v_set_nls_date  VARCHAR2(128)  := 'set NLS_DATE_FORMAT=DD-MON-RRRR HH24:MI:SS'||CRLF;

v_PATH          VARCHAR2(4000);

v_rmanbkp_name  VARCHAR2(30);

v_clean_rmanbkp_name  VARCHAR2(30);

v_batch_file    VARCHAR2(128);

v_dbatch_file   VARCHAR2(128);


RMANfh          utl_file.file_type;

dRMANfh         utl_file.file_type;

PROCEDURE prcGetTimestamps(ip_Ts1 IN OUT VARCHAR2, ip_Ts2 IN OUT VARCHAR2) IS
BEGIN
 SELECT TO_CHAR(SYSDATE,'YYYYMMDDHH24MI')   AS cts,
        TO_CHAR(SYSDATE,'RRRRMMDDHH24MI')   AS cts2
   INTO ip_Ts1,
        ip_Ts2
   FROM dual;
EXCEPTION
WHEN OTHERS THEN 
   null;
END prcGetTimestamps;

-- Returns the actual directory path...
FUNCTION getPathDir (ipDir IN VARCHAR2) RETURN VARCHAR2 IS

lvPathDir       VARCHAR2(4000);
BEGIN
  SELECT directory_path
    INTO lvPathDir
    FROM  sys.dba_directories
  WHERE directory_name = ipDir;
  RETURN lvPathDir;
EXCEPTION
WHEN NO_DATA_FOUND THEN
  DBMS_OUTPUT.put_line('Invalid File system path or directory name.');
  RETURN NULL;
WHEN OTHERS THEN
  RAISE;
  DBMS_OUTPUT.put_line(SQLERRM);
END getPathDir;

FUNCTION getOracleBinDir  RETURN VARCHAR2 IS


lvOraHomeDir    VARCHAR2(4000);
lvOraBinDir     VARCHAR2(4000);
 begin
  sys.dbms_system.get_env('ORACLE_HOME', lvOraHomeDir);
  lvOraBinDir := lvOraHomeDir||'\bin\';
  RETURN lvOraBinDir;
EXCEPTION
WHEN NO_DATA_FOUND THEN
  DBMS_OUTPUT.put_line('Invalid File system path or directory name.');
  RETURN NULL;
WHEN OTHERS THEN
  RAISE;
  DBMS_OUTPUT.put_line(SQLERRM);
END getOracleBinDir;

-- Returns the actual directory path...
FUNCTION setPathDir (ipBinDir IN VARCHAR2) RETURN VARCHAR2 IS

v_PathDir       VARCHAR2(4000);
BEGIN
v_PathDir   := 'set PATH='||ipBinDir||CRLF;
RETURN v_PathDir;
EXCEPTION
WHEN NO_DATA_FOUND THEN
  DBMS_OUTPUT.put_line('Invalid File system path or directory name.');
  RETURN NULL;
WHEN OTHERS THEN
  RAISE;
  DBMS_OUTPUT.put_line(SQLERRM);
END setPathDir;


-- Generates the RMAN Backup command line
FUNCTION genRmanLine RETURN VARCHAR2 IS
v_rmanline_fun    VARCHAR2(4000);
BEGIN
SELECT    v_Bin_Dir
        ||'rman target'
        ||' '        
        ||ip_rmantuser
        ||'/'               
        ||ip_rmantuserpwd      
        ||DECODE(ip_target,NULL,NULL,'@'||ip_target)
        ||' ' 
        ||DECODE(ip_rcvcat,NULL,'NOCATALOG','CATALOG')  
        ||' ' 
        ||DECODE(ip_rcvcat,NULL,NULL,ip_rmancuser)  
        ||DECODE(ip_rcvcat,NULL,NULL,'/')
        ||DECODE(ip_rcvcat,NULL,NULL,ip_rmancuserpwd )      
        ||DECODE(ip_rcvcat,NULL,NULL,'@'||ip_rcvcat)  
        ||' '                         
        ||'@'
        ||v_Path_Dir
        ||NVL(ip_Fname,'r_backup.rman')
        ||' '
        ||' USING '
        ||' '
        ||CHR(39)
        ||ip_Encr_Flag
        ||CHR(39)
        ||' '  
        ||DECODE( UPPER(ip_Comp_Flag) , 'C' , CHR(39)||'COMPRESSED'||CHR(39), CHR(39)||' '||CHR(39) )   
        ||'  '            
        ||TO_CHAR(ip_lvl,'9')
        ||' ' 
        ||DECODE( ip_Cum_Opt,'C', CHR(39)||'CUMULATIVE'||CHR(39), CHR(39)||' '||CHR(39) )
        ||'  '
        ||CHR(39)
        ||instance_name
        ||CHR(39)
        ||' '
        ||LTRIM(TO_CHAR( DECODE ( ip_Filesps, null, 1, ip_Filesps ),'99999' ))
        ||' '
        ||CHR(39)
        ||instance_name 
        ||DECODE( ip_lvl,1, DECODE( ip_Cum_Opt , 'C', '_CIL1_' , '_INL1_' ), 0, '_FLL0_', '_FLL0_')
        ||v_ts
        ||CHR(39)                 
        ||' '      
        ||' LOG='
        ||v_logPath_Dir
        ||instance_name
        ||'_'
        ||NVL(ip_Fname,'_r_backup')
        ||DECODE( ip_lvl, 0,'_L0_','_L1_')
        ||v_ts
        ||'.log' 
 INTO   v_rmanline_fun   
 FROM   sys.V$instance;
RETURN v_rmanline_fun; 
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20102,'Invalid option.');
END genRmanLine;


PROCEDURE CloseFile(ipFHandle IN OUT utl_file.file_type) IS             
                       
vFHandle utl_file.file_type;                   
                       
BEGIN                  
     -- closing files                          
                       
vFHandle := ipFHandle;                         
                        
if utl_file.is_open(vFHandle) then             
                       
   utl_file.fclose(vFHandle);                  
                       
end if;                
                       
dbms_output.put_line('File closed');            
                       
EXCEPTION              
                       
WHEN OTHERS THEN       
                       
     DBMS_OUTPUT.PUT_LINE(SQLERRM);            
                       
END CloseFile;

-- Opens file for reading or writing...
PROCEDURE OpenFile(ipFileHandle IN OUT UTL_FILE.FILE_TYPE, ipMode IN VARCHAR2, ipDir IN VARCHAR2, ipFname IN VARCHAR2, ipBfrSz IN INTEGER) IS          
       
BEGIN                          
       ipFileHandle := utl_file.fopen(ipDir,ipFname, ipMode,ipBfrSz);          
       
EXCEPTION                      
WHEN UTL_FILE.INVALID_MODE THEN                        
     RAISE;                    
     DBMS_OUTPUT.PUT_LINE('Invalid Mode used.');       
     CloseFile(ipFHandle => ipFileHandle);             
WHEN UTL_FILE.INVALID_PATH THEN                        
     RAISE;                    
     DBMS_OUTPUT.PUT_LINE('Invalid path being used.');                         
     CloseFile(ipFHandle => ipFileHandle);             
WHEN UTL_FILE.ACCESS_DENIED THEN                       
     RAISE;                    
     DBMS_OUTPUT.PUT_LINE('Access denied.');           
     CloseFile(ipFHandle => ipFileHandle);             
WHEN UTL_FILE.CHARSETMISMATCH THEN                     
     RAISE;                    
     DBMS_OUTPUT.PUT_LINE('Invalid character set error');                      
     CloseFile(ipFHandle => ipFileHandle);             
WHEN UTL_FILE.INTERNAL_ERROR THEN                      
     RAISE;                    
     DBMS_OUTPUT.PUT_LINE('Internal Write error occured.');                    
     CloseFile(ipFHandle => ipFileHandle);             
WHEN UTL_FILE.INVALID_MAXLINESIZE  THEN                
     RAISE;                    
     DBMS_OUTPUT.PUT_LINE('Buffer overflow');          
     CloseFile(ipFHandle => ipFileHandle);             
WHEN UTL_FILE.READ_ERROR THEN                          
     RAISE;                    
     DBMS_OUTPUT.PUT_LINE('OS Read error occured.');                           
     CloseFile(ipFHandle => ipFileHandle);             
WHEN UTL_FILE.WRITE_ERROR THEN                         
     RAISE;                    
     DBMS_OUTPUT.PUT_LINE('OS Write error occured.');                          
     CloseFile(ipFHandle => ipFileHandle);             
WHEN UTL_FILE.INVALID_FILENAME THEN                    
     RAISE;                    
     DBMS_OUTPUT.PUT_LINE('Invalid file name used.');                          
     CloseFile(ipFHandle => ipFileHandle);              
WHEN UTL_FILE.INVALID_FILEHANDLE THEN                  
     RAISE;                    
     DBMS_OUTPUT.PUT_LINE('Invalid handle found.');                            
     CloseFile(ipFHandle => ipFileHandle);             
WHEN UTL_FILE.INVALID_OPERATION THEN                   
     RAISE;                    
     DBMS_OUTPUT.PUT_LINE('Invalid operation attempted.');                     
     CloseFile(ipFHandle => ipFileHandle);             
WHEN OTHERS THEN               
     RAISE;                     
     DBMS_OUTPUT.PUT_LINE(SQLERRM);                    
     CloseFile(ipFHandle => ipFileHandle);             
END OpenFile;                  

-- Procedure to open the output file

PROCEDURE OpenOutPutFile IS                    
BEGIN                   
     RMANfh := utl_file.fopen(ip_rman_script_dir,ip_Fname,'a',1024);        
EXCEPTION              
WHEN OTHERS THEN       
    DBMS_OUTPUT.put_line(SQLERRM);             
END OpenOutPutFile;    
                          
-- Procedure to close the file


PROCEDURE DeleteFile (ipDir IN VARCHAR2, ipFileName IN VARCHAR2) IS                    
                       

BEGIN    
    DBMS_OUTPUT.put_line('Batch Directory is... => '||getPathDir( NVL( ipDir,ip_rman_batch_dir) ) );  
    DBMS_OUTPUT.put_line('File Name is file... => '||ipFileName);
    DBMS_OUTPUT.put_line('Deleting file... => '||getPathDir( NVL( ipDir,ip_rman_batch_dir) )||ipFileName);
    utl_file.fremove(location => getPathDir( NVL( ipDir,ip_rman_batch_dir) ) , filename => ipFileName );                                                               
                       
EXCEPTION              
                       
WHEN UTL_FILE.INVALID_MODE THEN                
     DBMS_OUTPUT.PUT_LINE('Invalid Mode used.');                       
WHEN UTL_FILE.INVALID_PATH THEN                
     DBMS_OUTPUT.PUT_LINE('Invalid path being used.');                 
WHEN UTL_FILE.ACCESS_DENIED THEN               
     DBMS_OUTPUT.PUT_LINE('Access denied.');   
WHEN UTL_FILE.INVALID_OFFSET THEN              
     DBMS_OUTPUT.PUT_LINE('Invalid offset occurred.');                 
WHEN UTL_FILE.CHARSETMISMATCH THEN             
     DBMS_OUTPUT.PUT_LINE('Invalid character set error');              
WHEN UTL_FILE.INTERNAL_ERROR THEN              
     DBMS_OUTPUT.PUT_LINE('Internal Write error occured.');            
WHEN UTL_FILE.INVALID_FILENAME THEN            
     DBMS_OUTPUT.PUT_LINE('Invalid file name used.');                  
WHEN UTL_FILE.INVALID_OPERATION THEN           
     DBMS_OUTPUT.PUT_LINE('Invalid operation attempted.');             
WHEN OTHERS THEN       
  raise;               
  DBMS_OUTPUT.PUT_LINE(SQLERRM);
              
END DeleteFile;
 
-- Procedure to write to the output file

PROCEDURE WriteFile (ipFHandle IN utl_file.file_type, ipBfr IN VARCHAR2) IS                    
                       
BEGIN                  
                       
    utl_file.putf(ipFHandle,'%s',ipBfr);                          
                        
    utl_file.fflush(ipFHandle);                
                       
EXCEPTION              
                       
WHEN UTL_FILE.INVALID_MODE THEN                
     DBMS_OUTPUT.PUT_LINE('Invalid Mode used.');                       
WHEN UTL_FILE.INVALID_PATH THEN                
     DBMS_OUTPUT.PUT_LINE('Invalid path being used.');                 
WHEN UTL_FILE.ACCESS_DENIED THEN               
     DBMS_OUTPUT.PUT_LINE('Access denied.');   
WHEN UTL_FILE.INVALID_OFFSET THEN               
     DBMS_OUTPUT.PUT_LINE('Invalid offset occurred.');                 
WHEN UTL_FILE.CHARSETMISMATCH THEN             
     DBMS_OUTPUT.PUT_LINE('Invalid character set error');              
WHEN UTL_FILE.INTERNAL_ERROR THEN              
     DBMS_OUTPUT.PUT_LINE('Internal Write error occured.');            
WHEN UTL_FILE.INVALID_MAXLINESIZE  THEN        
     DBMS_OUTPUT.PUT_LINE('Buffer overflow');                          
WHEN UTL_FILE.WRITE_ERROR THEN                 
     DBMS_OUTPUT.PUT_LINE('OS Write error occured.');                  
WHEN UTL_FILE.INVALID_FILENAME THEN            
     DBMS_OUTPUT.PUT_LINE('Invalid file name used.');                  
WHEN UTL_FILE.INVALID_FILEHANDLE THEN          
     DBMS_OUTPUT.PUT_LINE('Invalid handle found.');                    
WHEN UTL_FILE.INVALID_OPERATION THEN           
     DBMS_OUTPUT.PUT_LINE('Invalid operation attempted.');             
WHEN OTHERS THEN       
  raise;               
  DBMS_OUTPUT.PUT_LINE(SQLERRM);                
END WriteFile;


-- generates and runs the rman backup job...
PROCEDURE sub_create_bkp (ip_jname IN VARCHAR2, ip_jtype IN VARCHAR2, ip_jaction IN VARCHAR2) IS

lv_start_time TIMESTAMP;

lv_bkp_time   TIMESTAMP;

BEGIN
 
   SELECT  current_timestamp + INTERVAL '0 0:00:30.000' DAY TO SECOND(3) AS vs
     INTO  lv_start_time
     FROM  dual;

   SELECT  current_timestamp + TO_DSINTERVAL('0 0:01:00.000') AS tsbkp
     INTO  lv_bkp_time
     FROM  dual;

   sys.dbms_scheduler.create_job(
                                  job_name    =>  ip_jname,
                                  job_type    =>  ip_jtype,
                                  job_action  =>  ip_jaction,
                                  start_date  =>  lv_start_time,
                                  job_class   =>  'DEFAULT_JOB_CLASS',
                                  comments    =>  'Generates and executes an Oracle backup job',
                                  auto_drop   =>  TRUE,
                                  enabled     =>  FALSE
                                );

                                 sys.dbms_scheduler.set_attribute( name => ip_jname, attribute => 'job_priority', value => 1 );

                                 sys.dbms_scheduler.set_attribute( name => ip_jname,
                                                                   attribute => 'logging_level',
                                                                   value => DBMS_SCHEDULER.LOGGING_FULL
                                                                 );
   sys.dbms_scheduler.enable( ip_jname );

   sys.dbms_lock.sleep(60);

EXCEPTION
WHEN OTHERS THEN
   DBMS_OUTPUT.put_line(SQLERRM);
END sub_create_bkp;

PROCEDURE sub_cleanup_job (ip_jname IN VARCHAR2, ip_jtype IN VARCHAR2, ip_jaction IN VARCHAR2) IS

lv_start_time TIMESTAMP;
lv_bkp_time   TIMESTAMP;
BEGIN
 
   SELECT  current_timestamp + INTERVAL '0 0:00:20.000' DAY TO SECOND(3) AS vs
     INTO  lv_start_time
     FROM  dual;

   SELECT  current_timestamp + TO_DSINTERVAL('0 0:00:40.000') AS tsbkp
     INTO  lv_bkp_time
     FROM  dual;

   sys.dbms_scheduler.create_job(
                                  job_name      =>  ip_jname,
                                  job_type      =>  ip_jtype,
                                  job_action    =>  ip_jaction,
                                  start_date    =>  lv_start_time,
                                  job_class     =>  'DEFAULT_JOB_CLASS',
                                  comments      =>  'Cleans up/removes rman backup executable file.',
                                  auto_drop     =>  TRUE,
                                  enabled       =>  FALSE
                                );
                                 sys.dbms_scheduler.set_attribute( name => ip_jname, attribute => 'job_priority', value => 3 );
   sys.dbms_scheduler.enable( ip_jname );
   sys.dbms_lock.sleep(2);
EXCEPTION
WHEN OTHERS THEN

   BEGIN

    DeleteFile ( ipDir => ip_rman_batch_dir, ipFileName => v_Fname );

   EXCEPTION

   WHEN OTHERS THEN

      DBMS_OUTPUT.put_line('Could not erase rman file in use. Verify that manual deletion is performed.');

      RAISE_APPLICATION_ERROR(-20116,'RMAN batch file with security information was not permanently deleted.');

      DBMS_OUTPUT.put_line(SQLERRM);

   END;

END sub_cleanup_job;

PROCEDURE sub_disable_bkpjob (ip_jname IN VARCHAR2) IS

BEGIN

   sys.dbms_scheduler.disable ( name => ip_jname);  

EXCEPTION
WHEN OTHERS THEN
   DBMS_OUTPUT.put_line(SQLERRM);
END sub_disable_bkpjob;

PROCEDURE sub_drop_bkpjob (ip_jname IN VARCHAR2) IS

BEGIN

   sys.dbms_scheduler.drop_job ( job_name => ip_jname);
  
EXCEPTION
WHEN OTHERS THEN
   DBMS_OUTPUT.put_line(SQLERRM);
END sub_drop_bkpjob;

FUNCTION funGetJobStatus(ip_jname IN VARCHAR2 ) RETURN BOOLEAN IS

  lv_enabled SYS.dba_scheduler_jobs.enabled%TYPE;

BEGIN
   SELECT enabled
     INTO lv_enabled
     FROM SYS.dba_scheduler_jobs
   WHERE job_name = ip_jname;
   IF lv_enabled = 'TRUE' THEN
      RETURN TRUE;
   ELSE
      RETURN FALSE;
   END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
   raise_application_error(-20101,'Job was not previously created.');
   RETURN NULL;
WHEN OTHERS THEN
   DBMS_OUTPUT.put_line(SQLERRM);
   RETURN FALSE;
END funGetJobStatus;


FUNCTION JobIsRunning (ip_owner IN VARCHAR2, ip_jname IN VARCHAR2 ) RETURN BOOLEAN IS

v_resp_jname VARCHAR2(128);

BEGIN
   SELECT job_name
     INTO v_resp_jname
     FROM sys.dba_scheduler_running_jobs
    WHERE job_name = ip_jname
      AND owner = NVL(ip_owner,'SYS');

  IF v_resp_jname IS NOT NULL THEN
     RETURN TRUE;
  ELSE
     RETURN FALSE;
  END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
     RETURN FALSE;
WHEN TOO_MANY_ROWS THEN
     RETURN TRUE;
WHEN OTHERS THEN
  RAISE_APPLICATION_ERROR(-20117,'Job status is unknown.');
  DBMS_OUTPUT.put_line(SQLERRM);
  RETURN NULL;
END JobIsRunning;

PROCEDURE sub_sleep_until_done(ip_owner IN VARCHAR2,ip_jname IN VARCHAR2) IS
BEGIN
     sys.dbms_lock.sleep(60);
WHILE JobIsRunning ( ip_owner => NVL(ip_owner,'SYS') , ip_jname => ip_jname ) LOOP
    BEGIN
     sys.dbms_lock.sleep(3);
    EXCEPTION
    WHEN OTHERS THEN null;
    END;
END LOOP;
END sub_sleep_until_done;


PROCEDURE sub_run_bkp (ip_jname IN VARCHAR2 ) IS
BEGIN
   CASE
   WHEN ( NOT funGetJobStatus (ip_jname) ) THEN


        sys.dbms_scheduler.enable( ip_jname );

        sys.dbms_scheduler.run_job( ip_jname );

   WHEN funGetJobStatus (ip_jname) THEN

        sys.dbms_scheduler.run_job( ip_jname );

   ELSE

        DBMS_OUTPUT.put_line('Job probably does not exist.');

   END CASE;    

END sub_run_bkp;

PROCEDURE sub_write_cmd_line IS

BEGIN
    -- writes rman command line
    OpenFile(ipFileHandle => RMANfh ,   ipMode => 'a',   ipDir => NVL(ip_rman_batch_dir,'RMAN_BATCH_DIR'),    ipFname => v_Fname,    ipBfrSz => 32767);   

    WriteFile ( ipFHandle => RMANfh, ipBfr => v_set_nls_date );

    WriteFile ( ipFHandle => RMANfh, ipBfr => setPathDir ( ipBinDir  => v_Bin_Dir ) );

    v_Bin_Dir := 'set PATH=echo %PATH%'||CRLF;

    WriteFile ( ipFHandle => RMANfh, ipBfr => setPathDir ( ipBinDir  => v_Bin_Dir ) );

    WriteFile ( ipFHandle => RMANfh , ipBfr => v_rmanline );

    WriteFile ( ipFHandle => RMANfh , ipBfr => v_exit);

    CloseFile(ipFHandle => RMANfh);

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.put_line(SQLERRM);

END sub_write_cmd_line;


PROCEDURE sub_remove_rbat_file (ipDir IN VARCHAR2, ipFname IN VARCHAR2) IS

    v_CmdLine1 VARCHAR2(4000);

    v_CmdLine2 VARCHAR2(4000);

BEGIN

  --  v_dFname  := 'del_'||SUBSTR(NVL(ip_Fname,'r_backup'),1,14)||v_ts||'.bat';

    v_CmdLine1 := 'set PATH=echo %PATH%'||crlf;

    v_CmdLine2 := 'del /q '||getPathDir( NVL( ipDir,ip_rman_batch_dir) )||v_dFname||crlf;

    DBMS_OUTPUT.put_line('Batch Directory is... => '||getPathDir( NVL( ipDir,ip_rman_batch_dir) ) );
 
    DBMS_OUTPUT.put_line('File Name is file... => '||ipFname);

    DBMS_OUTPUT.put_line('Background job is deleting file... => '||getPathDir( NVL( ipDir,ip_rman_batch_dir) )||ipFname);

    OpenFile(ipFileHandle => dRMANfh , ipMode => 'a',   ipDir => NVL(ip_rman_batch_dir,'RMAN_BATCH_DIR'), ipFname => v_dFname, ipBfrSz => 32767);   

    WriteFile ( ipFHandle => dRMANfh,  ipBfr => v_CmdLine1 );

    WriteFile ( ipFHandle => dRMANfh,  ipBfr => v_CmdLine2 );

    WriteFile ( ipFHandle => dRMANfh , ipBfr => v_exit);

    CloseFile(ipFHandle => dRMANfh );


EXCEPTION
WHEN OTHERS
THEN
  null;
END sub_remove_rbat_file;

-- Main Program and entry point...
BEGIN

    SELECT user
      INTO v_user
      FROM sys.dual;
        
    v_Path_Dir := getPathDir (ipDir => ip_rman_script_dir); 

    v_Bin_Dir  := NVL( getPathDir (ipDir => ip_Oracle_bin_dir), getOracleBinDir() );

    v_logPath_Dir := getPathDir (ipDir => ip_log_dir);

    prcGetTimestamps(ip_Ts1 => v_ts, ip_Ts2 => v_ts2);

    v_rmanline := genRmanLine()||crlf;

    DBMS_OUTPUT.put_line(v_rmanline); 

    v_Fname  := SUBSTR(NVL(ip_Fname,'r_backup.rman'),1,14)||v_ts||'.bat';

    v_dFname  := SUBSTR(NVL('del_'||ip_Fname,'r_backup.rman'),1,14)||v_ts||'.bat';
    -- writes rman command line

    sub_write_cmd_line();

    dbms_output.put_line(v_rmanline);                         

    v_rmanbkp_name := NVL(ip_target,NVL(ip_rcvcat,'ORCL'))||'_RMANBKP_'||v_ts2;

    v_batch_file := getPathDir (ipDir => ip_rman_batch_dir)||v_Fname;

    sub_create_bkp (ip_jname => v_rmanbkp_name, ip_jtype => 'EXECUTABLE', ip_jaction => v_batch_file) ;

    sub_sleep_until_done(ip_owner => NVL(v_user,'SYS'),ip_jname => v_rmanbkp_name );

    sub_remove_rbat_file ( ipDir => ip_rman_batch_dir, ipFname => v_Fname );

    v_clean_rmanbkp_name := NVL(ip_target,NVL(ip_rcvcat,'ORCL'))||'_rmbat_'||v_ts2;

    v_dbatch_file := getPathDir (ipDir => ip_rman_batch_dir)||v_dFname;

    sub_cleanup_job (ip_jname => v_clean_rmanbkp_name , ip_jtype => 'EXECUTABLE', ip_jaction => v_dbatch_file );

    sub_sleep_until_done(ip_owner => NVL(v_user,'SYS'),ip_jname => v_clean_rmanbkp_name );

end SP_RMANLINE;

-- calling main procedure

PROCEDURE SP_CALLRMANLINE IS
begin
SP_RMANLINE(
              ip_rmantuser => 'SYS',
              ip_rmantuserpwd => '%WINPWD_T%',            
              ip_rmancuser => 'RMAN',
              ip_rmancuserpwd => '%WINPWD_C%',
              ip_target => 'ADNIBMSX',
              ip_rcvcat => 'ADNIBMSX',
              ip_rman_script_dir => 'RMAN_SCRIPT_DIR',
              ip_rman_batch_dir => 'RMAN_BATCH_DIR',
              ip_Fname => 'r_backup.rman',
              ip_log_dir => 'RMAN_LOG_DIR',
              ip_lvl => 1,
              ip_Cum_Opt => 'C',
              ip_Oracle_bin_dir => 'ORACLE_BIN_DIR',
              ip_Comp_Flag  => 'C',
              ip_Encr_Flag  => 'ON',   
              ip_Filesps => 1
            );
exception
when others then
dbms_output.put_line(sqlerrm);
end;                           


B.      Sample Log Files (Excerpts)

B.1    Full or Incremental Level 0

Recovery Manager: Release 11.2.0.3.0 - Production on Wed May 1 16:25:57 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ADNIBMSX (DBID=2634802274)
connected to recovery catalog database

RMAN> # Will allow for encrpted rman backups without introducing any keys, once the wallet is opned...
2> # should pass tag as a partial parameter for the tag name.
3>
4> set encryption on;
5>
6> run  {
7>          allocate  channel cH1 type disk;
8>          sql 'alter system archive log current';
9>          crosscheck archivelog all;
10>          release   channel cH1;
11>          allocate  channel cH1 type disk;
12>          allocate  channel cH2 type disk;
13>          allocate  channel cH3 type disk;
14>          allocate  channel cH4 type disk;
15>          set limit channel cH1 kbytes 16577216;
16>          set limit channel cH2 kbytes 16577216;
17>          set limit channel cH3 kbytes 16577216;
18>          set limit channel cH4 kbytes 16577216;
19>          BACKUP AS COMPRESSED BACKUPSET
20>             INCREMENTAL LEVEL 0
21>             FORMAT 'C:\app\oracle\flash_recovery_area\adnibmsx\BACKUPSET\%T_%s_%d_L0_bkp_Db_%s%t%U'  tag = adnibmsx_FLL0_201305011625
22>          FILESPERSET 1
23>            (database);
24>          BACKUP AS COMPRESSED BACKUPSET
25>             INCREMENTAL LEVEL 0
26>             FORMAT 'C:\app\oracle\flash_recovery_area\adnibmsx\BACKUPSET\%T_%s_%d_L0_bkp_Ctl_%s%t%U' tag = adnibmsx_FLL0_201305011625
27>             (current controlfile);
28>             sql 'alter system archive log current';
29>           BACKUP AS COMPRESSED BACKUPSET
30>             INCREMENTAL LEVEL 0
31>             FORMAT 'C:\app\oracle\flash_recovery_area\adnibmsx\BACKUPSET\%T_%s_%d_L0_bkp_Arc_%s%t%U' tag = adnibmsx_FLL0_201305011625
32>              (archivelog all);
33>          backup spfile;
34>          sql 'alter database backup controlfile to trace';
35>          release channel cH1;
36>          release channel cH2;
37>          release channel cH3;
38>          release channel cH4;
39>        }
40>          resync catalog;
41>          list backupset of database;
42>          list backupset of controlfile;
43>          list backupset of archivelog all;
44>          list restore point all;
45>          report schema;
46>          validate check logical skip inaccessible database;
47>          restore validate database;
48>          restore validate archivelog all;
49>          report obsolete;
50>          delete noprompt obsolete;
51>          exit;
executing command: SET encryption

allocated channel: cH1
channel cH1: SID=536 device type=DISK

sql statement: alter system archive log current

validation succeeded for archived log
archived log file name=C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\ARCHIVELOG\2013_04_30\O1_MF_1_466_8QZYK67Y_.ARC RECID=462 STAMP=814108007
validation succeeded for archived log
archived log file name=C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\ARCHIVELOG\2013_04_30\O1_MF_1_467_8QZYKBGQ_.ARC RECID=463 STAMP=814108010
validation succeeded for archived log
archived log file name=C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\ARCHIVELOG\2013_04_30\O1_MF_1_468_8R05TK4H_.ARC RECID=464 STAMP=814115495
validation succeeded for archived log
archived log file name=C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\ARCHIVELOG\2013_04_30\O1_MF_1_469_8R067B98_.ARC RECID=465 STAMP=814115897
validation succeeded for archived log
archived log file name=C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\ARCHIVELOG\2013_04_30\O1_MF_1_470_8R06D8FP_.ARC RECID=466 STAMP=814116077
validation succeeded for archived log
archived log file name=C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\ARCHIVELOG\2013_05_01\O1_MF_1_471_8R2CDMXZ_.ARC RECID=467 STAMP=814273130
validation succeeded for archived log
archived log file name=C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\ARCHIVELOG\2013_05_01\O1_MF_1_472_8R2CY3XS_.ARC RECID=468 STAMP=814273668
validation succeeded for archived log
archived log file name=C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\ARCHIVELOG\2013_05_01\O1_MF_1_473_8R2CY5JN_.ARC RECID=469 STAMP=814273669
validation succeeded for archived log
archived log file name=C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\ARCHIVELOG\2013_05_01\O1_MF_1_474_8R2YLR7Q_.ARC RECID=470 STAMP=814292762
Crosschecked 9 objects


released channel: cH1

allocated channel: cH1
channel cH1: SID=536 device type=DISK

allocated channel: cH2
channel cH2: SID=37 device type=DISK

allocated channel: cH3
channel cH3: SID=537 device type=DISK

allocated channel: cH4
channel cH4: SID=28 device type=DISK





Starting backup at 01-MAY-2013 16:26:06
channel cH1: starting compressed incremental level 0 datafile backup set
channel cH1: specifying datafile(s) in backup set
input datafile file number=00009 name=C:\APP\ORACLE\ORADATA\ADNIBMSX\MGMT.DBF
channel cH1: starting piece 1 at 01-MAY-2013 16:26:07
channel cH2: starting compressed incremental level 0 datafile backup set
channel cH2: specifying datafile(s) in backup set
input datafile file number=00001 name=C:\APP\ORACLE\ORADATA\ADNIBMSX\SYSTEM01.DBF
channel cH2: starting piece 1 at 01-MAY-2013 16:26:08
channel cH3: starting compressed incremental level 0 datafile backup set
channel cH3: specifying datafile(s) in backup set
input datafile file number=00003 name=C:\APP\ORACLE\ORADATA\ADNIBMSX\UNDOTBS01.DBF
channel cH3: starting piece 1 at 01-MAY-2013 16:26:08
channel cH4: starting compressed incremental level 0 datafile backup set
channel cH4: specifying datafile(s) in backup set
input datafile file number=00005 name=C:\APP\ORACLE\ORADATA\ADNIBMSX\EXAMPLE01.DBF
channel cH4: starting piece 1 at 01-MAY-2013 16:26:09
channel cH1: finished piece 1 at 01-MAY-2013 16:30:04
piece handle=C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\BACKUPSET\20130501_1359_ADNIBMSX_L0_BKP_DB_1359814292767AFO8I7OV_1_1 tag=ADNIBMSX_FLL0_201305011625 comment=NONE
channel cH1: backup set complete, elapsed time: 00:03:57
channel cH1: starting compressed incremental level 0 datafile backup set
channel cH1: specifying datafile(s) in backup set
input datafile file number=00002 name=C:\APP\ORACLE\ORADATA\ADNIBMSX\SYSAUX01.DBF
channel cH1: starting piece 1 at 01-MAY-2013 16:30:06
channel cH4: finished piece 1 at 01-MAY-2013 16:30:07
piece handle=C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\BACKUPSET\20130501_1362_ADNIBMSX_L0_BKP_DB_1362814292768AIO8I7P0_1_1 tag=ADNIBMSX_FLL0_201305011625 comment=NONE
channel cH4: backup set complete, elapsed time: 00:03:58
channel cH4: starting compressed incremental level 0 datafile backup set
channel cH4: specifying datafile(s) in backup set
input datafile file number=00010 name=C:\APP\ORACLE\ORADATA\ADNIBMSX\MGMT_AD4J.DBF
channel cH4: starting piece 1 at 01-MAY-2013 16:30:09
channel cH4: finished piece 1 at 01-MAY-2013 16:31:14
piece handle=C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\BACKUPSET\20130501_1364_ADNIBMSX_L0_BKP_DB_1364814293007AKO8I80F_1_1 tag=ADNIBMSX_FLL0_201305011625 comment=NONE
channel cH4: backup set complete, elapsed time: 00:01:05
channel cH4: starting compressed incremental level 0 datafile backup set
channel cH4: specifying datafile(s) in backup set
input datafile file number=00007 name=C:\APP\ORACLE\ORADATA\ADNIBMSX\HZNENCR01.DBF
channel cH4: starting piece 1 at 01-MAY-2013 16:31:17
channel cH4: finished piece 1 at 01-MAY-2013 16:31:52
piece handle=C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\BACKUPSET\20130501_1365_ADNIBMSX_L0_BKP_DB_1365814293075ALO8I82J_1_1 tag=ADNIBMSX_FLL0_201305011625 comment=NONE
channel cH4: backup set complete, elapsed time: 00:00:35
channel cH4: starting compressed incremental level 0 datafile backup set
channel cH4: specifying datafile(s) in backup set
input datafile file number=00006 name=C:\APP\ORACLE\ORADATA\ADNIBMSX\RMANTS01.DBF
channel cH4: starting piece 1 at 01-MAY-2013 16:31:55
channel cH2: finished piece 1 at 01-MAY-2013 16:33:12
piece handle=C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\BACKUPSET\20130501_1360_ADNIBMSX_L0_BKP_DB_1360814292767AGO8I7OV_1_1 tag=ADNIBMSX_FLL0_201305011625 comment=NONE
channel cH2: backup set complete, elapsed time: 00:07:04
channel cH2: starting compressed incremental level 0 datafile backup set
channel cH2: specifying datafile(s) in backup set
input datafile file number=00011 name=C:\APP\ORACLE\ORADATA\ADNIBMSX\MGMT_ECM_DEPOT1.DBF
channel cH2: starting piece 1 at 01-MAY-2013 16:33:14
channel cH4: finished piece 1 at 01-MAY-2013 16:33:17
piece handle=C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\BACKUPSET\20130501_1366_ADNIBMSX_L0_BKP_DB_1366814293113AMO8I83P_1_1 tag=ADNIBMSX_FLL0_201305011625 comment=NONE
channel cH4: backup set complete, elapsed time: 00:01:22
channel cH4: starting compressed incremental level 0 datafile backup set
channel cH4: specifying datafile(s) in backup set
input datafile file number=00004 name=C:\APP\ORACLE\ORADATA\ADNIBMSX\USERS01.DBF
channel cH4: starting piece 1 at 01-MAY-2013 16:33:21
channel cH2: finished piece 1 at 01-MAY-2013 16:33:46
piece handle=C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\BACKUPSET\20130501_1367_ADNIBMSX_L0_BKP_DB_1367814293193ANO8I869_1_1 tag=ADNIBMSX_FLL0_201305011625 comment=NONE
channel cH2: backup set complete, elapsed time: 00:00:32
channel cH2: starting compressed incremental level 0 datafile backup set
channel cH2: specifying datafile(s) in backup set
input datafile file number=00008 name=C:\APP\ORACLE\ORADATA\ADNIBMSX\DATA01.DBF
channel cH2: starting piece 1 at 01-MAY-2013 16:33:49
channel cH4: finished piece 1 at 01-MAY-2013 16:33:49
piece handle=C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\BACKUPSET\20130501_1368_ADNIBMSX_L0_BKP_DB_1368814293198AOO8I86E_1_1 tag=ADNIBMSX_FLL0_201305011625 comment=NONE
channel cH4: backup set complete, elapsed time: 00:00:28
channel cH1: finished piece 1 at 01-MAY-2013 16:33:56
piece handle=C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\BACKUPSET\20130501_1363_ADNIBMSX_L0_BKP_DB_1363814293005AJO8I80D_1_1 tag=ADNIBMSX_FLL0_201305011625 comment=NONE
channel cH1: backup set complete, elapsed time: 00:03:50
channel cH2: finished piece 1 at 01-MAY-2013 16:33:56
piece handle=C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\BACKUPSET\20130501_1369_ADNIBMSX_L0_BKP_DB_1369814293228APO8I87C_1_1 tag=ADNIBMSX_FLL0_201305011625 comment=NONE
channel cH2: backup set complete, elapsed time: 00:00:07
channel cH3: finished piece 1 at 01-MAY-2013 16:34:44
piece handle=C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\BACKUPSET\20130501_1361_ADNIBMSX_L0_BKP_DB_1361814292768AHO8I7P0_1_1 tag=ADNIBMSX_FLL0_201305011625 comment=NONE
channel cH3: backup set complete, elapsed time: 00:08:36
Finished backup at 01-MAY-2013 16:34:44

Starting backup at 01-MAY-2013 16:34:47
channel cH1: starting compressed incremental level 0 datafile backup set
channel cH1: specifying datafile(s) in backup set
including current control file in backup set
channel cH1: starting piece 1 at 01-MAY-2013 16:34:52
channel cH1: finished piece 1 at 01-MAY-2013 16:34:53
piece handle=C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\BACKUPSET\20130501_1370_ADNIBMSX_L0_BKP_CTL_1370814293288AQO8I898_1_1 tag=ADNIBMSX_FLL0_201305011625 comment=NONE
channel cH1: backup set complete, elapsed time: 00:00:01
Finished backup at 01-MAY-2013 16:34:53

…
channel ORA_DISK_4: validation complete, elapsed time: 00:00:14
channel ORA_DISK_1: piece handle=C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\BACKUPSET\20130430_1323_ADNIBMSX_L0_BKP_ARC_13238141080149BO8CJBE_1_1 tag=ADNIBMSX_FLL0_201304301254
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:15
channel ORA_DISK_2: piece handle=C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\BACKUPSET\20130430_1324_ADNIBMSX_L0_BKP_ARC_13248141080159CO8CJBF_1_1 tag=ADNIBMSX_FLL0_201304301254
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: validation complete, elapsed time: 00:00:15
Finished restore at 01-MAY-2013 16:46:50

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 2
Report of obsolete backups and copies
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set           11543  30-APR-2013 12:58:42
  Backup Piece       11556  30-APR-2013 12:58:42 C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\BACKUPSET\20130430_1312_ADNIBMSX_L0_BKP_DB_131281410735890O8CIMU_1_1
Backup Set           11544  30-APR-2013 13:00:26
  Backup Piece       11557  30-APR-2013 13:00:26 C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\BACKUPSET\20130430_1310_ADNIBMSX_L0_BKP_DB_13108141073578UO8CIMT_1_1
Backup Set           11545  30-APR-2013 13:01:58
  Backup Piece       11558  30-APR-2013 13:01:58 C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\BACKUPSET\20130430_1315_ADNIBMSX_L0_BKP_DB_131581410764393O8CIVR_1_1
Backup Set           11546  30-APR-2013 13:02:50
  Backup Piece       11559  30-APR-2013 13:02:50 C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\BACKUPSET\20130430_1316_ADNIBMSX_L0_BKP_DB_131681410772594O8CJ2D_1_1
Backup Set           11547  30-APR-2013 13:03:49
  Backup Piece       11560  30-APR-2013 13:03:49 C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\BACKUPSET\20130430_1311_ADNIBMSX_L0_BKP_DB_13118141073578VO8CIMT_1_1
Backup Set           11548  30-APR-2013 13:04:14
  Backup Piece       11561  30-APR-2013 13:04:14 C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\BACKUPSET\20130430_1317_ADNIBMSX_L0_BKP_DB_131781410778295O8CJ46_1_1
Backup Set           11549  30-APR-2013 13:04:14
  Backup Piece       11562  30-APR-2013 13:04:14 C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\BACKUPSET\20130430_1318_ADNIBMSX_L0_BKP_DB_131881410783996O8CJ5V_1_1
Backup Set           11550  30-APR-2013 13:04:33
  Backup Piece       11563  30-APR-2013 13:04:33 C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\BACKUPSET\20130430_1319_ADNIBMSX_L0_BKP_DB_131981410786597O8CJ6P_1_1
Backup Set           11551  30-APR-2013 13:04:36
  Backup Piece       11564  30-APR-2013 13:04:36 C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\BACKUPSET\20130430_1320_ADNIBMSX_L0_BKP_DB_132081410786698O8CJ6Q_1_1
Backup Set           11552  30-APR-2013 13:05:25
  Backup Piece       11565  30-APR-2013 13:05:25 C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\BACKUPSET\20130430_1313_ADNIBMSX_L0_BKP_DB_131381410735891O8CIMU_1_1
Archive Log          11621  30-APR-2013 13:06:47 C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\ARCHIVELOG\2013_04_30\O1_MF_1_466_8QZYK67Y_.ARC
Backup Set           11553  30-APR-2013 13:05:39
  Backup Piece       11566  30-APR-2013 13:05:39 C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\BACKUPSET\20130430_1314_ADNIBMSX_L0_BKP_DB_131481410755992O8CIT7_1_1
Archive Log          11629  30-APR-2013 13:06:50 C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\ARCHIVELOG\2013_04_30\O1_MF_1_467_8QZYKBGQ_.ARC
Archive Log          11758  30-APR-2013 15:11:35 C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\ARCHIVELOG\2013_04_30\O1_MF_1_468_8R05TK4H_.ARC
Archive Log          11759  30-APR-2013 15:18:17 C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\ARCHIVELOG\2013_04_30\O1_MF_1_469_8R067B98_.ARC
Archive Log          11760  30-APR-2013 15:21:17 C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\ARCHIVELOG\2013_04_30\O1_MF_1_470_8R06D8FP_.ARC
Archive Log          11764  01-MAY-2013 10:58:50 C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\ARCHIVELOG\2013_05_01\O1_MF_1_471_8R2CDMXZ_.ARC
Backup Set           11824  01-MAY-2013 11:07:45
  Backup Piece       11826  01-MAY-2013 11:07:45 C:\APP\ORACLE\PRODUCT\11.2.0\DBHOME_4\DATABASE\C-2634802274-20130501-00
Backup Set           11884  01-MAY-2013 11:09:06
  Backup Piece       11887  01-MAY-2013 11:09:06 C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\BACKUPSET\2013_05_01\O1_MF_NNSNF_TAG20130501T110905_8R2D0LGM_.BKP
Backup Set           11897  01-MAY-2013 11:09:13
  Backup Piece       11899  01-MAY-2013 11:09:13 C:\APP\ORACLE\PRODUCT\11.2.0\DBHOME_4\DATABASE\C-2634802274-20130501-01
Backup Set           12059  01-MAY-2013 16:34:52
  Backup Piece       12061  01-MAY-2013 16:34:52 C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\BACKUPSET\20130501_1370_ADNIBMSX_L0_BKP_CTL_1370814293288AQO8I898_1_1

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 2
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
Deleting the following obsolete backups and copies:
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set           11543  30-APR-2013 12:58:42
  Backup Piece       11556  30-APR-2013 12:58:42 C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\BACKUPSET\20130430_1312_ADNIBMSX_L0_BKP_DB_131281410735890O8CIMU_1_1
Backup Set           11544  30-APR-2013 13:00:26
  Backup Piece       11557  30-APR-2013 13:00:26 C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\BACKUPSET\20130430_1310_ADNIBMSX_L0_BKP_DB_13108141073578UO8CIMT_1_1
Backup Set           11545  30-APR-2013 13:01:58
  Backup Piece       11558  30-APR-2013 13:01:58 C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\BACKUPSET\20130430_1315_ADNIBMSX_L0_BKP_DB_131581410764393O8CIVR_1_1
Backup Set           11546  30-APR-2013 13:02:50
  Backup Piece       11559  30-APR-2013 13:02:50 C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\BACKUPSET\20130430_1316_ADNIBMSX_L0_BKP_DB_131681410772594O8CJ2D_1_1
Backup Set           11547  30-APR-2013 13:03:49
  Backup Piece       11560  30-APR-2013 13:03:49 C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\BACKUPSET\20130430_1311_ADNIBMSX_L0_BKP_DB_13118141073578VO8CIMT_1_1
Backup Set           11548  30-APR-2013 13:04:14
  Backup Piece       11561  30-APR-2013 13:04:14 C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\BACKUPSET\20130430_1317_ADNIBMSX_L0_BKP_DB_131781410778295O8CJ46_1_1
Backup Set           11549  30-APR-2013 13:04:14
  Backup Piece       11562  30-APR-2013 13:04:14 C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\BACKUPSET\20130430_1318_ADNIBMSX_L0_BKP_DB_131881410783996O8CJ5V_1_1
Backup Set           11550  30-APR-2013 13:04:33
  Backup Piece       11563  30-APR-2013 13:04:33 C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\BACKUPSET\20130430_1319_ADNIBMSX_L0_BKP_DB_131981410786597O8CJ6P_1_1
Backup Set           11551  30-APR-2013 13:04:36
  Backup Piece       11564  30-APR-2013 13:04:36 C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\BACKUPSET\20130430_1320_ADNIBMSX_L0_BKP_DB_132081410786698O8CJ6Q_1_1
Backup Set           11552  30-APR-2013 13:05:25
  Backup Piece       11565  30-APR-2013 13:05:25 C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\BACKUPSET\20130430_1313_ADNIBMSX_L0_BKP_DB_131381410735891O8CIMU_1_1
Archive Log          11621  30-APR-2013 13:06:47 C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\ARCHIVELOG\2013_04_30\O1_MF_1_466_8QZYK67Y_.ARC
Backup Set           11553  30-APR-2013 13:05:39
  Backup Piece       11566  30-APR-2013 13:05:39 C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\BACKUPSET\20130430_1314_ADNIBMSX_L0_BKP_DB_131481410755992O8CIT7_1_1
Archive Log          11629  30-APR-2013 13:06:50 C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\ARCHIVELOG\2013_04_30\O1_MF_1_467_8QZYKBGQ_.ARC
Archive Log          11758  30-APR-2013 15:11:35 C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\ARCHIVELOG\2013_04_30\O1_MF_1_468_8R05TK4H_.ARC
Archive Log          11759  30-APR-2013 15:18:17 C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\ARCHIVELOG\2013_04_30\O1_MF_1_469_8R067B98_.ARC
Archive Log          11760  30-APR-2013 15:21:17 C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\ARCHIVELOG\2013_04_30\O1_MF_1_470_8R06D8FP_.ARC
Archive Log          11764  01-MAY-2013 10:58:50 C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\ARCHIVELOG\2013_05_01\O1_MF_1_471_8R2CDMXZ_.ARC
Backup Set           11824  01-MAY-2013 11:07:45
  Backup Piece       11826  01-MAY-2013 11:07:45 C:\APP\ORACLE\PRODUCT\11.2.0\DBHOME_4\DATABASE\C-2634802274-20130501-00
Backup Set           11884  01-MAY-2013 11:09:06
  Backup Piece       11887  01-MAY-2013 11:09:06 C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\BACKUPSET\2013_05_01\O1_MF_NNSNF_TAG20130501T110905_8R2D0LGM_.BKP
Backup Set           11897  01-MAY-2013 11:09:13
  Backup Piece       11899  01-MAY-2013 11:09:13 C:\APP\ORACLE\PRODUCT\11.2.0\DBHOME_4\DATABASE\C-2634802274-20130501-01
Backup Set           12059  01-MAY-2013 16:34:52
  Backup Piece       12061  01-MAY-2013 16:34:52 C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\BACKUPSET\20130501_1370_ADNIBMSX_L0_BKP_CTL_1370814293288AQO8I898_1_1
deleted backup piece
backup piece handle=C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\BACKUPSET\20130430_1312_ADNIBMSX_L0_BKP_DB_131281410735890O8CIMU_1_1 RECID=944 STAMP=814107362
deleted backup piece
backup piece handle=C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\BACKUPSET\20130430_1310_ADNIBMSX_L0_BKP_DB_13108141073578UO8CIMT_1_1 RECID=945 STAMP=814107362
deleted backup piece
backup piece handle=C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\BACKUPSET\20130430_1315_ADNIBMSX_L0_BKP_DB_131581410764393O8CIVR_1_1 RECID=946 STAMP=814107658
deleted backup piece
backup piece handle=C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\BACKUPSET\20130430_1316_ADNIBMSX_L0_BKP_DB_131681410772594O8CJ2D_1_1 RECID=947 STAMP=814107736
deleted backup piece
backup piece handle=C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\BACKUPSET\20130430_1311_ADNIBMSX_L0_BKP_DB_13118141073578VO8CIMT_1_1 RECID=948 STAMP=814107362
deleted backup piece
backup piece handle=C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\BACKUPSET\20130430_1317_ADNIBMSX_L0_BKP_DB_131781410778295O8CJ46_1_1 RECID=949 STAMP=814107837
deleted backup piece
backup piece handle=C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\BACKUPSET\20130430_1318_ADNIBMSX_L0_BKP_DB_131881410783996O8CJ5V_1_1 RECID=950 STAMP=814107841
deleted backup piece
backup piece handle=C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\BACKUPSET\20130430_1319_ADNIBMSX_L0_BKP_DB_131981410786597O8CJ6P_1_1 RECID=951 STAMP=814107869
deleted backup piece
backup piece handle=C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\BACKUPSET\20130430_1320_ADNIBMSX_L0_BKP_DB_132081410786698O8CJ6Q_1_1 RECID=952 STAMP=814107873
deleted backup piece
backup piece handle=C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\BACKUPSET\20130430_1313_ADNIBMSX_L0_BKP_DB_131381410735891O8CIMU_1_1 RECID=953 STAMP=814107363
deleted archived log
archived log file name=C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\ARCHIVELOG\2013_04_30\O1_MF_1_466_8QZYK67Y_.ARC RECID=462 STAMP=814108007
deleted backup piece
backup piece handle=C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\BACKUPSET\20130430_1314_ADNIBMSX_L0_BKP_DB_131481410755992O8CIT7_1_1 RECID=954 STAMP=814107645
deleted archived log
archived log file name=C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\ARCHIVELOG\2013_04_30\O1_MF_1_467_8QZYKBGQ_.ARC RECID=463 STAMP=814108010
deleted archived log
archived log file name=C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\ARCHIVELOG\2013_04_30\O1_MF_1_468_8R05TK4H_.ARC RECID=464 STAMP=814115495
deleted archived log
archived log file name=C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\ARCHIVELOG\2013_04_30\O1_MF_1_469_8R067B98_.ARC RECID=465 STAMP=814115897
deleted archived log
archived log file name=C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\ARCHIVELOG\2013_04_30\O1_MF_1_470_8R06D8FP_.ARC RECID=466 STAMP=814116077
deleted archived log
archived log file name=C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\ARCHIVELOG\2013_05_01\O1_MF_1_471_8R2CDMXZ_.ARC RECID=467 STAMP=814273130
deleted backup piece
backup piece handle=C:\APP\ORACLE\PRODUCT\11.2.0\DBHOME_4\DATABASE\C-2634802274-20130501-00 RECID=975 STAMP=814273664
deleted backup piece
backup piece handle=C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\BACKUPSET\2013_05_01\O1_MF_NNSNF_TAG20130501T110905_8R2D0LGM_.BKP RECID=979 STAMP=814273746
deleted backup piece
backup piece handle=C:\APP\ORACLE\PRODUCT\11.2.0\DBHOME_4\DATABASE\C-2634802274-20130501-01 RECID=980 STAMP=814273749
deleted backup piece
backup piece handle=C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\BACKUPSET\20130501_1370_ADNIBMSX_L0_BKP_CTL_1370814293288AQO8I898_1_1 RECID=992 STAMP=814293292
Deleted 21 objects


Recovery Manager complete.

B.2    Incremental Level 1

Recovery Manager: Release 11.2.0.3.0 - Production on Thu May 2 11:03:13 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ADNIBMSX (DBID=2634802274)
connected to recovery catalog database

RMAN> # Will allow for encrpted rman backups without introducing any keys, once the wallet is opned...
2> # should pass tag as a partial parameter for the tag name.
3>
4> set encryption on;
5>
6> run  {
7>          allocate  channel cH1 type disk;
8>          sql 'alter system archive log current';
9>          crosscheck archivelog all;
10>          release   channel cH1;
11>          allocate  channel cH1 type disk;
12>          allocate  channel cH2 type disk;
13>          allocate  channel cH3 type disk;
14>          allocate  channel cH4 type disk;
15>          set limit channel cH1 kbytes 16577216;
16>          set limit channel cH2 kbytes 16577216;
17>          set limit channel cH3 kbytes 16577216;
18>          set limit channel cH4 kbytes 16577216;
19>          BACKUP AS COMPRESSED BACKUPSET
20>             INCREMENTAL LEVEL 1
21>             FORMAT 'C:\app\oracle\flash_recovery_area\adnibmsx\BACKUPSET\%T_%s_%d_L1_bkp_Db_%s%t%U'  tag = adnibmsx_INL1_201305021102
22>          FILESPERSET 1
23>            (database);
24>          BACKUP AS COMPRESSED BACKUPSET
25>             INCREMENTAL LEVEL 1
26>             FORMAT 'C:\app\oracle\flash_recovery_area\adnibmsx\BACKUPSET\%T_%s_%d_L1_bkp_Ctl_%s%t%U' tag = adnibmsx_INL1_201305021102
27>             (current controlfile);
28>             sql 'alter system archive log current';
29>           BACKUP AS COMPRESSED BACKUPSET
30>             INCREMENTAL LEVEL 1
31>             FORMAT 'C:\app\oracle\flash_recovery_area\adnibmsx\BACKUPSET\%T_%s_%d_L1_bkp_Arc_%s%t%U' tag = adnibmsx_INL1_201305021102
32>              (archivelog all);
33>          backup spfile;
34>          sql 'alter database backup controlfile to trace';
35>          release channel cH1;
36>          release channel cH2;
37>          release channel cH3;
38>          release channel cH4;
39>        }
40>          resync catalog;
41>          list backupset of database;
42>          list backupset of controlfile;
43>          list backupset of archivelog all;
44>          list restore point all;
45>          report schema;
46>          validate check logical skip inaccessible database;
47>          restore validate database;
48>          restore validate archivelog all;
49>          report obsolete;
50>          delete noprompt obsolete;
51>          exit;
executing command: SET encryption
starting full resync of recovery catalog
full resync complete

allocated channel: cH1
channel cH1: SID=537 device type=DISK

sql statement: alter system archive log current

validation succeeded for archived log
archived log file name=C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\ARCHIVELOG\2013_05_01\O1_MF_1_472_8R2CY3XS_.ARC RECID=468 STAMP=814273668
validation succeeded for archived log
archived log file name=C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\ARCHIVELOG\2013_05_01\O1_MF_1_473_8R2CY5JN_.ARC RECID=469 STAMP=814273669
validation succeeded for archived log
archived log file name=C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\ARCHIVELOG\2013_05_01\O1_MF_1_474_8R2YLR7Q_.ARC RECID=470 STAMP=814292762
validation succeeded for archived log
archived log file name=C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\ARCHIVELOG\2013_05_01\O1_MF_1_475_8R2Z3MF4_.ARC RECID=471 STAMP=814293299
validation succeeded for archived log
archived log file name=C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\ARCHIVELOG\2013_05_01\O1_MF_1_476_8R2Z3O05_.ARC RECID=472 STAMP=814293301
validation succeeded for archived log
archived log file name=C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\ARCHIVELOG\2013_05_01\O1_MF_1_477_8R307R17_.ARC RECID=473 STAMP=814294456
validation succeeded for archived log
archived log file name=C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\ARCHIVELOG\2013_05_01\O1_MF_1_478_8R309GBF_.ARC RECID=474 STAMP=814294511
validation succeeded for archived log
archived log file name=C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\ARCHIVELOG\2013_05_01\O1_MF_1_479_8R309KDR_.ARC RECID=475 STAMP=814294513
validation succeeded for archived log
archived log file name=C:\APP\ORACLE\FLASH_RECOVERY_AREA\ADNIBMSX\ARCHIVELOG\2013_05_02\O1_MF_1_480_8R501S3W_.ARC RECID=476 STAMP=814359807
Crosschecked 9 objects


released channel: cH1



Posted by at No comments:
Subscribe to: Posts (Atom)