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

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.

4> set encryption on;

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

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.

4> set encryption on;

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

Translate

Wednesday, June 19, 2013

The Engineering of the Intelligent Backup

1. Introduction

2. Justification: Weaknesses of Existing Backup Policies

2.1 Security

2.2 Guaranteed Restore Point

2.3 Integrity and Unity

2.4 Lags in Backup Timeframe

2.5 Timestamps on backup sets and backup pieces

2.6 Required Regulatory Compliance

2.7 Backup Encryption for Regulatory Compliance

2.8 Password Security

2.9 Backup Reliability

2.10 Integration with other Oracle technologies

2.11 Compression Resources and Capabilities

2.12 Justification for a new improved policy and standard

3. Backup Specifications

3.1 Usability

3.2 Functionality

3.3 Backup Specific Requirements

3.3.1 Prepared Scripts Ready to Run

3.3.2 Shells

3.3.3 Policies

3.3.4 Schedule

3.3.5 Invocation Method

3.3.6 Backup Types Used

3.3.7 Backup Actions and Contents

3.3.8 Special Features

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

3.3.8.2 Easy to Customize

3.3.8.3 Easy to deploy

3.3.8.4 Easy to maintain throughout the company

3.3.8.5 RMAN Scripting Usage

3.3.8.5.1 Flexibility

3.3.8.5.2 Custom Media Formatting

3.3.8.6 Backup Integrity

3.3.8.7 Database Security and Data Privacy for Regulatory Compliance

3.3.8.7.1 Business Intelligence Implementation

3.3.6.7.1.1 Timestamps

3.3.6.7.1.2 Backup Integrity and Fault-Tolerance

3.3.6.7.1.3 Tagging and Encoding

3.3.6.7.1.4 Logging RMAN Operations

3.3.6.7.1.5 Backup Restore Operations

3.3.6.7.1.6 Encryption

3.3.6.7.1.7 Compression

3.3.6.7.1.8 Other Recommendations

4. Basic Practices

4.1 Proposed Backup Scheduling

4.2 Supplied Shells, PL/SQL and RMAN Scripts

5. Tagging Strategies

6. Encoding Strategies

7. Other Major Considerations

7.1 RMAN Configuration Settings

7.2 Encryption

7.2 Compression

7.3 Database Architecture

7.4 Other Concerns

APPENDIX

A. Sample Source Code

B. Sample Log Files (Excerpts)

B.1 Full or Incremental Level 0

B.2 Incremental Level 1

No comments: