Recovery Manager (RMAN) updated for RAC

Topics


Quick Backup with Details
run {
allocate channel t1 type disk;
allocate channel t2 type disk;
allocate channel t3 type disk;

# Backup the whole DB
backup database spfile tag="Full_Database" format 'C:\app\RMAN_Backup\backup_%d_set%s_piece%p_%T_%U' ;

# Switch out of the current logfile
alter system archive log current;

# Backup the archived logs
crosscheck archivelog all;
backup archivelog all format 'C:\app\RMAN_Backup\arch_logs_%u';

# Backup a copy of the controlfile that contains records for the other backups just made
backup current controlfile tag="Control_File" format 'C:\app\RMAN_Backup\rman_backup_controlfile_%d_ctl.bckp' reuse;

# Creates a ccontrolfile copy on a specific location
backup as copy current controlfile format 'C:\app\RMAN_Backup\Control_File_copy.ctl';

#Crosschecking will check that the catalog/controlfile matches the physical backups. If a backup is missing, it will set the piece to 'EXPIRED' (missing)
CROSSCHECK BACKUP;

# Delete un-necessary backups. This command deletes backups based on the retention policy.
# Ignores any I/O errors, including those that occur when a backup is missing from disk or tape
DELETE NOPROMPT force OBSOLETE;

# Delete the expired backups.
DELETE NOPROMPT EXPIRED BACKUP;

RELEASE CHANNEL t1;
RELEASE CHANNEL t2;
RELEASE CHANNEL t3;
}


Recovery Manager Major Features
Recovery Manager contains a number features to aid in the backup, restore and recovery operations in large, complex Oracle environments. These include:

Backup file Compression
Recovery Manager only copies data blocks that contain data. A tablespace that is only half filled will be backed up into a file half the size of an OS image copy of the same tablespace.

Incremental Backups
This feature allows backups to be performed that contain only database blocks that have changed since the last full or incremental backup. This can greatly reduce the size of backups. It can also reduce the time required to do a restoration. A typical scenario would be to perform a weekly full backup and daily incrementals.

Change-Aware Incremental Backups

In previous releases of the Oracle database, RMAN had to examine every block in the data file to determine which blocks had been changed when performing an incremental backup.  The time to perform an incremental backup was proportional to the size of the data files.  Therefore,  performing an incremental backup on a very large database could take some time, even if only a few blocks were changed.
You can create a block change tracking file that records the blocks modified since the last backup.  RMAN uses the tracking file to determine which blocks to include in the incremental backup.  RMAN no longer needs to examine the entire data file.  The time to perform an incremental backup is now proportional to the amount of content modified since the last backup. Here are the steps RMAN will perform to do an incremental backup:
1.    Read the Block Change Tracking File to determine which blocks in the data file need to be read.
2.    Scan only the changed blocks in the data file and then back them up.

The size of the block change tracking file is proportional to:
- The database size in bytes: the block change tracking file contains data representing every data file block in the database.  The data is approximately 1/250000 of the total size of the database.
- The number of enabled threads: In a Real Application Cluster (RAC) environment, the instances update different areas of the tracking file without any locking or inter-node block swapping.  You enable block change tracking for the entire database and not for individual instances.
- The number of old backups: The block change tracking file keeps a record of all changes between previous backups, in addition to the modification since the last backup.  It retains the change history for a maximum of eight backups.
The size of the file is calculated by the following formula:
Size of the Block Change Tracking File =    ( (Threads*2) + number of old backups ) * database size in bytes / 250,000

The initial size for the block change tracking file is 10 MB. Using this formula, a 2 TB database with only one thread and having five backups in the RMAN repository will require a block change tracking file of 59 MB.

Enabling, Disabling and Monitoring Block Change Tracking
By default, Oracle will not record block change information.  To enable this feature, you need to issue the following command:
SQL> alter database enable block change tracking USING FILE ‘/u01/oradata/ora1/change_tracking.f’;

To disable this feature, you issue this command:
SQL> alter database disable block change tracking;

To monitor the status of block change tracking, you type:
select file, status, bytes from   v$block_change_tracking;
STATUS    FILE                            BYTES
--------  ----------------------------    ---------------
ENABLED    /dba/backup/01_mf_yzmrr7.chg    10,000,000

If the location needs to be moved, change tracking can be disabled, and a new change tracking file can be created, but this causes the database to lose all change tracking information. Moreover, unfortunately the change tracking file cannot be moved without shutting down the database, moving it with the appropriate ALTER DATABASE RENAME FILE <filename> command, and then restarting the database.
Oracle does recommend that this feature be activated for any database whose disaster recovery plan utilizes incremental backups of differing levels. Oracle also notes that theirs is a small performance hit during normal operations, but that hit should be discounted against the need to avoid scans of datafiles during restoration and recovery operations.


Corrupt Block Detection
When a backup is executed with Recovery Manager, the database blocks are read and corruption is automatically detected. By default, when a corrupt block error is detected by Recovery Manager, an error message will be displayed (or written to a log file if not running interactively), and the backup terminates. This error message will identify the file that the corrupt block is in. By setting the value of maxcorrupt to something greater than zero (the default) and re-executing the backup or copy, more information on the corrupt block will be put in the views V$BACKUP_CORRUPTION or V$COPY_CORRUPTION.

Fractured Block Detection
Another benefit of an Oracle server process being used when executing Recovery Manager is that an Oracle server process can detect when a data block is fractured. A data block is considered fractured when the control information in the header does not match the information stored in the footer. A fractured block can occur during an online backup because the Oracle data blocks are not always the same size as the operating system block size.
Oracle data blocks are referred to as logical blocks while operating system blocks are called physical blocks. In most environments, the logical block contains multiple physical blocks. Therefore, while the operating system is backing up a database, Oracle can be writing into multiple physical blocks at the same time the operating system is reading a subset of the physical blocks written to by Oracle. To resolve this problem, Oracle implemented the hot backup mode , which tells Oracle to copy the entire logical block to the redo log buffer when a data block is modified. When the database is not in hot backup mode, only the before and after image of the modified row is written to the redo buffer. Since the server process can detect fractured blocks and will reread the block if a fracture is detected, the hot backup mode is not necessary using Recovery Manager.

Automatic Parallelization
Backup and restore operations are automatically parallelized. This is supported with both disk and tape backup/restore operations. The parallelization can be implemented by using the allocate channel and filesperset Recovery Manager commands. Allocate channel is used to assign I/O devices, and the filesperset is used to define how many database files will be written to a single backup output file (called a backup set). The total backup time can be reduced by taking advantage of parallelism.

Backup Usability and Restore Validation Reporting
Recovery Manager has several features that allow a DBA to look at their backups and analyze if the backups are OK.
The RMAN commands report and recover will help you to identify the backup status. These commands can be executed interactively, or as scripts.
To find out if a tablespace has a datafile that can't be recovered (this could be due to an unrecoverable operation, such as running SQL*Loader in UNRECOVERABLE mode), enter:
report unrecoverable tablespace "users";

To determine if a database has not been backed up within the last 7 days, enter:
report need backup days 7 database;

Backups can become obsolete for various reasons including: a backup of a database file that no longer exists, or there is a more recent backup of an existing file. A report of obsolete backups can be used to determine what could be deleted from backup media to free up space. To find backups that are obsolete because there are at least 2 more recent backups, enter:
report obsolete redundancy 2;

Recovery Manager has the capability of doing a "dry run" restoration of a database. This can be valuable in determining that all files needed for the restoration are available from backup media. The following is an example of validating restoration for an entire database:
restore database validate;

This command checks for the database files needed for restoration, but not the archive log files that may be needed to recover.

Recovery Catalog for Centralized Operations
The recovery catalog is used to store information regarding backup and restore operations. The catalog is a schema that should reside in a database other than those being backed up (target databases). The recovery catalog can contain information about multiple target databases. The reports described in the previous section were generated from information stored in the recovery catalog. The recovery catalog is also used to store scripts of Recovery Manager commands.

If you have just a few databases, then the recovery catalog is probably not worth the extra effort and hassle. If you have a database environment with many databases in it, you should consider using a recovery catalog. Generally, the added flexibility and centralized enterprise-wide reporting benefits of the recovery catalog outweigh the additional maintenance and administrative requirements that are added with the use of a recovery catalog. One downside to using a recovery catalog, though, is that if the catalog database is down, your backups will all fail unless you have coded your backup scripts to perform a backup without the recovery catalog in cases where the first backup with the recovery catalog fails.
Additionally, a recovery catalog is an essential part of a Data Guard backup environment and Split mirror backups. In these configurations, when you back up the database from the backup host, the recovery catalog is considered the most current information, so it is the brains behind the strategy and becomes a single point of failure if not maintained properly. The bottom line is that you need to decide for yourself whether your environment calls for a recovery catalog.
 

Expanded Image Copying Features

A standard RMAN backup set contains one or more backup pieces, and each of these pieces consists of the data blocks for a particular datafile stored in a special compressed format. When a datafile needs to be restored, therefore, the entire datafile essentially needs to be recreated from the blocks present in the backup piece.
An image copy of a datafile, on the other hand, is much faster to restore because the physical structure of the datafile already exists. Since version 10g Oracle permits image copies to be created at the database, tablespace, or datafile level through the new RMAN directive BACKUP AS COPY. For example, here is a command script to create image copies for all datafiles in the entire database:
RUN {
# Set the default channel configuration. Note the use of the
# %U directive to insure unique file names for the image copies
ALLOCATE CHANNEL dbkp1 DEVICE TYPE DISK FORMAT 'c:\oracle\rmanbkup\U%';
# Create an image copy of all datafiles in the database
BACKUP AS COPY DATABASE;
}

More examples:
RUN {
    # Set the default channel configuration
    ALLOCATE CHANNEL dbkp1 DEVICE TYPE DISK FORMAT 'c:\oracle\rmanbkup\ic_%d_%s_%t_%p';
    # Back up specific datafiles and retain them as an image copies
    BACKUP AS COPY (DATAFILE 2, 6, 9 MAXSETSIZE 25M);
    # Back up a specific tablespace and retain it as an image copy
    BACKUP AS COPY (TABLESPACE example MAXSETSIZE 15M);
    # Back up the whole database and retain it as an image copy
    BACKUP AS COPY DATABASE;
}

Incrementally Updated Backups
As we saw before, it is now much simpler to create image copy backups of the database. Another new feature since Oracle 10g is to incrementally updated backups, allows me to apply incremental database changes to the corresponding image copy backup - also known as rolling forward the datafile image copy -- of any datafile in the database. Since image copy backups are much faster to restore in a media recovery situation, this new feature gives me the option to have updated image copies ready for restoration without having to recreate the image copies on a regular basis.
To utilize this feature, you will use the new BACKUP ... FOR RECOVER OF COPY command to create the incremental level 1 backups to roll forward the changes to the image copy of the datafiles, and use the new RMAN RECOVER COPY OF DATABASE command to apply the incremental backup to the image copies of the datafiles. Note that the TAG directive becomes extremely important to this implementation, as it is used to identify to which image copies the changes are to be rolled forward. Here is a script that illustrates a daily cycle of creation and application of the incrementally updated backups. This would be appropriate for a database that has sufficient disk space for storage of image copies, and has a relatively high need for quick restoration of media:
RUN {
# Roll forward any available changes to image copy files
# from the previous set of incremental Level 1 backups
recover copy of database with tag 'img_cpy_upd';

# Create incremental level 1 backup of all datafiles in the database
# for roll-forward application against image copies
backup incremental level 1
for recover of copy with tag 'img_cpy_upd' database;
}

Though this appears a bit counter-intuitive at first, here is an explanation of what happens during the initial run of this script:
    * The RECOVER command actually has no effect, because it cannot find any incremental backups with a tag of img_cpy_upd.
    * However, the BACKUP command will create a new Incremental Level 0 backup that is labeled with a tag of img_cpy_upd because no backups have been created yet with this tag.

And during the second run of this script:
    * The RECOVER command still will have no effect, because it cannot find any Level 1 incremental backups with a tag of img_cpy_upd.
    * The BACKUP command will create its first Incremental Level 1 backup that is labeled with a tag of img_cpy_upd.

But during the third and subsequent runs of this script:
    * The RECOVER command finds the incremental level 1 image copy backups from the previous night's run tagged as img_cpy_upd, and applies them to the existing datafile image copies.
    * The BACKUP command will create the next Incremental Level 1 backup that is labeled with a tag of img_cpy_upd.

After the third run of this script, RMAN would then choose the following files during a media recovery scenario: the image copy of the database for tag img_cpy_upd from the previous night, the most recent incremental level 1 backup, and all archived redo logs since the image copy was taken. This strategy offers a potentially quick and flexible recovery, since the datafile image copies will be relatively quick to restore, and the incremental level 1 backup plus all archived redo logs can be used to perform either a point-in-time or a complete recovery.
Example: Incrementally-Updated Backups: A Weekly Implementation
RUN {
######
# This script will create image copy backups to which incremental
# changes can be applied on a weekly schedule
######
# Roll forward any available changes to image copy files
# from the previous set of incremental Level 1 backups. Note that
# the roll-forward will not occur until 7 days have elapsed!
recover copy of database with tag 'img_cpy_upd'
until time (sysdate-7);


## Create incremental level 1 backup of all datafiles in the database
# for roll-forward application against weekly image copies
backup incremental level 1
for recover of copy with tag 'img_cpy_upd' database;

}


Database Dropping and Deregistration
Oracle allows a database to be dropped and its entry removed from the RMAN catalog. The following statement drops the entire database and removes the database files:
RMAN> drop database;

The statement below drops the entire database, removes the database files, and deletes all backup copies of the database and the archive log files:
RMAN> drop database including backups;

The two statements above drop the database and delete the database files.  However, they do not unregister the database from the RMAN catalog. The following statement will remove the database information from the RMAN catalog:
RMAN> unregister database grid;


RMAN Architecture
The production database that you are backing up is called the target. A separate database, called the recovery catalog, contains information about datafile and control-file copies, backup sets, archived redo logs, and other key elements needed for recovery.
RMAN performs two main functions:
 - It maintains the RMAN metadata in the control file or the recovery catalog.
 - It communicates with the Oracle database and the operating system in order to create, restore, and recover backup sets and image copies. (RMAN writes image copies to disk only, not to tape.)
RMAN creates several client connections, or channels, between the target database and the backup storage device. RMAN can create backup sets on disk or directly on tape. To use tape storage, RMAN requires a media manager.
If you have more than one database to back up, you can create more than one recovery catalog and have each database serve as the other's recovery catalog. For example, assume there are two production databases, one called "prd1," and a second called "prd2." You can install the recovery catalog for "prd1" in the "prd2" database, and the recovery catalog for the "prd2" database in "prd1." This enables you to avoid the extra space requirements and memory overhead of maintaining a separate recovery catalog database. However, this solution is not practical if the recovery catalog databases for both reside in tablespaces residing on the same physical disk.

To get the most out of RMAN, you should always use a recovery catalog, which contains details of all backup-and-recovery-related operations. RMAN uses the catalog to identify the relationship between backups and database files, automatically deciding which recovery events will minimize the mean time to recover (MTTR). If you run RMAN without using a recovery catalog, it will gather the necessary information from the database's control file but won't support point-in-time recovery. And if you lose all copies of your control file you'll be out of luck. That's why unless you have only one computer running the Oracle software, you should always use a recovery catalog and store it on totally separate hardware from any of your production or target instances - preferably in a separate location. The catalog itself rarely occupies more than 40MB.

IMPORTANT NOTE:
Save the database id displayed in the RMAN output if you are operating RMAN backups in nocatalog mode, since it is required during disaster recovery.
You will see the database id in output from RMAN on connecting to target database like:
connected to target database: INVENTORY (DBID=1670954628)

There are 2 ways to find the DBID:
a. If RMAN Backup text logs are maintained, the DBID can be found when the initial connection is made to the target , or
b. If autobackup is enabled and has the autobackup format set (let’s say the format is '%F'), then the filename of the autobackup has the DBID in it.
For example, 'c-1106578690-20100408-00' is the name of the autobackup file; so the DBID should be '1106578690'.

It's also recommended to save your init.ora file and your tnsnames.ora file in a "safe" place.

Terminology in RMAN
When you issue an RMAN backup command, RMAN creates backup sets, which are logical groupings of physical files. You can see this value in your reports as the "BS KEY" column.
The physical files that RMAN creates on your backup media are called backup pieces. When working with RMAN, you need to understand that the following terms have specific meanings:

a) RMAN Backup - A backup of all or part of your database. This results from issuing an RMAN backup command. A backup consists of one or more backup sets.

b) Backup Set - A logical grouping of backup files -- the backup pieces -- that are created when you issue an RMAN backup command. A backup set is RMAN's name for a collection of files associated with a backup. Backup sets consist of datafiles or archivelogs.  A single backup set cannot contain a combination of archivelogs and datafiles.
Backup sets consist of one or more individual backup files. The individual files contained in a backup set are called backup pieces.
Backup sets can be full or incremental. A full backup is a backup of all of the blocks that make up a datafile or datafiles. Recovery Manager allows you to take full backups of datafiles, datafile copies, tablespaces, archive logs, control files and databases. Incremental backups copy blocks that have been changed since a previous backup. Incremental copes can be taken of datafiles, tablespaces and databases. Recovery Manager also provides cumulative backups. Cumulative backups copy all blocks that have been changed since the most recent incremental backup.

c) Backup piece:  A physical binary file created by RMAN during a backup. Backup pieces are written to your backup medium, whether to disk or tape. They contain blocks from the target database's datafiles, archived redo log files, and control files.
    When RMAN constructs a backup piece from datafiles, there are a several rules that it follows:
        * A datafile cannot span backup sets.
        * A datafile can span backup pieces as long as it stays within one backup set.
        * Datafiles and control files can coexist in the same backup sets.
        * Archived redo log files are never in the same backup set as datafiles or control files.
    RMAN is the only tool that can operate on backup pieces.

d) Image Copy: Is a bit-by-bit copy of database files created on Disk. This is equivalent to cp (on Linux) or copy (on windows).
An image copy is a single datafile that you can use as input to a recovery. The Oracle server process validates the blocks in the file during backup and registers the copy in the recovery catalog.  Image copies do not require the execution of a recovery operation, the datafile can be renamed to the image copy.
As a result, image copies:

e) Parallel Backup and Recovery
Recovery Manager is able to parallelize a single backup, recovery or restore operation, but is unable to process multiple commands in a stored script in parallel.
The RMAN script below uses three channels to back up three data files identified by their file number.  This creates three separate server processes each one sending a data stream to the tape device.
RUN
{
  ALLOCATE CHANNEL t1 DEVICE TYPE sbt;
  ALLOCATE CHANNEL t2 DEVICE TYPE sbt;
  ALLOCATE CHANNEL t3 DEVICE TYPE sbt;
  BACKUP DATAFILE 3,4,5 ;
}

- Hot backup - backup taken while Oracle Database is Up. (Database should be in “Archive Log” mode for hot backup)

- Cold backup - backup taken while Oracle database is down or in mount state (NOT OPEN).

- Full Backup - Backing up entire database is called as full backup

- Incremental Backup - Backup of block level changes to database made after previous incremental/full backup.

- “Level 0" Incremental backup - This backs up all blocks in database. This is equivalent to full backup.

“Level 1" Incremental backup - This backs up database block changes after previous incremental backup.
If there is no level 0 incremental backup and you run level 1 incremental backup, RMAN will automatically make level 0 incremental backup.

Cumulative incremental backup - level 1 incremental backup which includes all blocks changed since most recent level 0 incremental backup.

Differential incremental backup - level 1 incremental backup which includes only block changed since most recent incremental backup.

By default, incremental backups are differential


Connection to RMAN Target

There are several ways to connect to your target DB:

rman target /
rman target=sys/passwd@PROD

rman
connect target sys/mh_flyers@PROD

If you get ORA-01031, you can also perform:
rman target sys
or
rman target sys/passwd
or
rman
connect target sys
or
rman
connect target sys/passwd



Flash Recovery Area (FRA) and Estimate Size

Please READ HERE FIRST!!!!

With Automatic Disk-Based Backup and Recovery, you can create a flash recovery area, which automates management of backup-related files. Choose a location on disk and an upper bound for storage space and set a retention policy that governs how long backup files are needed for recovery, and then the database manages the storage used for backups, archived redo logs, and other recovery-related files for your database within that space.
Files no longer needed are eligible for deletion when RMAN needs to reclaim space for new files. If you do not use a flash recovery area, you must manually manage disk space for your backup-related files and balance the use of space among the different types of files. Oracle recommends that you enable a flash recovery area to simplify your backup management.
Using a flash recovery area simplifies the ongoing administration of your database by automatically naming files, retaining them as long as they are needed for restore and recovery activities, and deleting them when they are no longer needed to restore your database and space is needed for some other backup and recovery-related purpose.
Your long-term backup and recovery administration can be greatly simplified by using a flash recovery area. Use of the flash recovery area is strongly recommended. You may want to set up a flash recovery area as one of the first steps in implemeting your backup strategy.

By configuring the RMAN RETENTION POLICY, the FRA will automatically delete obsolete backups and archive logs that are no longer required based on that configuration.  If you set the RETENTION POLICY to a recovery window of 7 days, then RMAN will retain all backups required to recover the database 7 days in the past. FRA will delete obsolete files once the disk is arriving to the quote defined.

All files that are needed to completely recover a database from a media failure are part of the Flash Recovery Area. Those recovery related files include:
•    Control file: A copy is created in the Flash Recovery Area location at database creation.
•    Archived log files: When the Flash Recovery Area is configured, the archiver background process then creates archived files in the Flash Recovery Area and in other configured LOG_ARCHIVE_DEST_n locations.
•    Flashback logs: the Flash Recovery Area automatically manages Flashback Database logs.
•    Control file autobackups: The default location for control file .
•    Data file copies:  The default location for data file copies created by RMAN is stored in the Flash Recovery Area.
•    RMAN backups: The default location for RMAN to create files during backup & copy operations.  It is also the default location to restore archive logs from tape if they are required during a recover task.

Per default a backuppiece is created in the Flash Recovery Area unless it is overruled by using the FORMAT-clause.  The FORMAT-clause can be specified in the BACKUP command or configured  in the persistent configuration, by using CONFIGURE command.

Like with normal backup, the Controlfile autobackup will be stored in the Flash Recovery Area unless the location is overruled by the FORMAT for the autobackup.
RMAN> configure controlfile autobackup format for device type disk to <....>;

By default RMAN restores the archived redologs in the LOG_ARCHIVE_DEST_n location.  If one of the LOG_ARCHIVE_DEST_n parameters is set to  "LOCATION=USE_DB_RECOVERY_FILE_DEST" then restored archived redo log files will be stored in the Flash Recovery Area.

This can be overrulled by using the RMAN command :
  RMAN> SET ARCHIVELOG DESTINATION <....>;


RMAN File Creation in the Flash Recovery Area
This section describes RMAN commands or implicit actions (such as control file autobackup) that can create files in the flash recovery area, and how to control whether a specific command creates files there or in some other destination. The assumption in all cases is that a flash recovery area has already been configured for your database. The commands are:

-  BACKUP
If you do not specify a FORMAT option to the BACKUP command, and do not configure a FORMAT option for disk backups, RMAN creates backup pieces and image copies in the flash recovery area, with names in Oracle Managed Files name format.

-  Control File Autobackup
RMAN can create control file autobackups in the flash recovery area. Use the RMAN command CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR to clear any configured format option for the control file autobackup location on disk. Control file autobackups will be placed in the flash recovery area when no other destination is configured.

-  RESTORE ARCHIVELOG
Explicitly or implicitly (as in the case of ), set one of the LOG_ARCHIVE_DEST_n) parameters to 'LOCATION=USE_DB_RECOVERY_FILE_DEST'. If you do not specify SET ARCHIVELOG DESTINATION to override this behavior, then restored archived redo log files will be stored in the flash recovery area.

-  RECOVER DATABASE or TABLESPACE, BLOCKRECOVER, and FLASHBACK DATABASE
These commands restore archived redo logs from backup for use during media recovery, as required by the command. RMAN restores any redo log files needed during these operations to the flash recovery area, and delete them once they are applied during media recovery.

To direct the restored archived redo logs to the flash recovery area, set one of the LOG_ARCHIVE_DEST_n parameters to 'LOCATION=USE_DB_RECOVERY_FILE_DEST", and make sure you are not using SET ARCHIVELOG DESTINATION to direct restored archived logs to some other destination.


Sizing FRA
If you want to keep:
–Control file backups and archived logs
Then estimate total size of all archived logs generated between successive backups on the busiest days x 2 (in case of unexpected redo spikes)
–Flashback logs
Then add in {Redo rate x Flashback retention target time x 2}
–Incremental backups
Then add in their estimated sizes
–On-disk image copy
Then add in size of the database minus size of temporary files

FRA File Retention and Deletion
When FRA space needs exceed quota, automatic file deletion occurs in the following order:
1. Flashback logs = Oldest Flashback time can be affected (with exception of guaranteed restore points)
2. RMAN backup pieces/copies and archived redo logs that are:
        • Not needed to maintain RMAN retention policy, or
        • Have been backed up to tape (via DEVICE TYPE SBT) or secondary disk location (via BACKUP RECOVERY AREA TO DESTINATION ‘..’)

If archived log deletion policy is configured as:
– APPLIED ON [ALL] STANDBY = Then archived log must have been applied to mandatory or all standby databases
– SHIPPED TO [ALL] STANDBY = Then archived log must have been transferred to mandatory or all standby databases
– BACKED UP <N> TIMES TO DEVICE TYPE [DISK | SBT] = Then archived log must have been backed up at least <N>times
– If [APPLIEDor SHIPPED] and BACKED UPpolicies are configured, both conditions must be satisfied for an archived log to be considered for deletion.



Installing a Catalog
    One important decision when using RMAN is deciding whether to use a recovery catalog. This section covers the pros and cons of a catalog and then details catalog implementation issues.

What Is the Catalog?
In many respects, RMAN can be thought of as an Oracle database file backup and restoration utility. RMAN must keep track of many things. When was the last backup taken? Which files were backed up? Which backup sets contain which database files? The answers to these questions can be found in the target control files. Optionally, RMAN can be configured to store this information in a separate database known as the catalog. The catalog consists collectively of a database and a database schema. The schema objects hold the RMAN-specific information for each of your target databases.

Catalog advantages
When you use a catalog repository, you get more flexibility and access to all the features of RMAN. The advantages of using a catalog accrue in the following areas:

  •           You can retain backup and recovery metadata for long periods of time.
  •           You can centralize operations.
  •           RMAN becomes more flexible in certain backup and recovery scenarios.

A catalog enables you to recover your control files in the event that they are all corrupted or lost. If you are not using a catalog, and you haven't backed up your control files via an ALTER DATABASE BACKUP CONTROLFILE command, you could find yourself in the unenviable position of not being able to recover your target.
Another good reason to use a catalog is that information pertaining to your RMAN backup and recovery activities can be stored for very long periods of time. If for any reason you had to use a backup set that was several months old, a catalog provides more flexibility to go back further in time than with the NOCATALOG option.
With a catalog, you can manage all of your backup and recovery activities from one repository. The advantage of this is that if you have multiple databases to maintain, you're storing all of your backup and recovery metadata in one place.
Finally, when using the catalog, you have greater flexibility during certain recovery situations. For example, you can use previous incarnations of the database for recovery.

Catalog disadvantages
While the advantages of the catalog are substantial, there are a few potential headaches that you should be aware of:

  •           Upgrades and compatibility can be problematic.
  •           A catalog adds complexity.
  •           A catalog can increase your needs for hardware and DBA resources.

If you're backing up production databases, and you want the most flexibility possible for any given backup and recovery scenario, Oracle recommends that you use a catalog database. However, if you have nonproduction databases for which you want to implement RMAN functionality, but you don't want the overhead of a catalog, you may want to consider just using the target control files.

Using RMAN without a catalog
RMAN can be used out of the box to back up, restore, and recover a database without setting up a catalog. You may decide not to use a catalog because you have limited resources or because you want a quick and easy backup and recovery mechanism for your databases. This section points out the issues you need to be aware of if you choose this route:
If you are using Oracle8i or an earlier version, you may not be able to recover your database if you lose all of your control files. If this happens, you may not be able to recover your database. If you exclusively use the target control files for the repository, you must put into place a mechanism that backs up your control files. (This should be a standard practice anyway.) You should also multiplex your control files to multiple disk drives. If you do not use a catalog, make sure that your control files are backed with:
    ALTER DATABASE BACKUP CONTROLFILE ......

Catalog Issues
The creation of the catalog database is fairly straightforward. It is, after all, just a database, schema, and objects. Here are some issues you should consider:

  •           Where to put the database that will host the catalog
  •           Which database version to use for the catalog
  •           How to size the catalog
  •           How to manage multiple target databases

Physical location of the catalog
You should place the catalog database on a different server than the target database. If you fail to do this, you jeopardize backup and recovery operations, because you make it possible to lose both the catalog and target databases.
The catalog can coexist in a database used for other applications. The advantage to this is that you leverage existing resources. The disadvantage is that the performance and availability of your catalog can be affected by other applications within the same database.

Version of catalog
What version of Oracle should you use to create the catalog database? We suggest that the catalog database be created with the latest version of Oracle in your production environment. This helps to minimize compatibility issues and complexity when you start to back up your target databases.



RMAN Configuration

STEP 1 Modify your Net Connnections and Create the Recovery Catalog (Optional)
Before invoking RMAN, set the NLS_DATE_FORMAT and NLS_LANG environment variables. Much of the RMAN LIST output is date/time related. It is often necessary to have this information displayed as accurately as possible when performing time-based recovery. Example NLS settings:
NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1

NLS_DATE_FORMAT=DD-MON-YYYY HH24:MI:SS           THIS ONE IS VERY IMPORTANT!!!

Your TNSNAMES.ORA file should have an entry for both your catalog and target databases. For this example, we will use two databases, the catalog database (RMAN) and the target database (PROD).

Then, create the catalog (optional)
Connect to the Oracle RMAN instance by using your DBA account, create an account for RMAN, and grant it appropriate rights.
sqlplus "sys@RMAN as sysdba"
create tablespace RMAN datafile '.......' size 150M autoextend on next 10M maxzize 700M
      EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

create user RMAN identified by rman temporary tablespace TEMP default tablespace RMAN quota unlimited on RMAN;
grant recovery_catalog_owner, connect, resource, create session, dba to RMAN;

connect RMAN/RMAN@RMAN

rman catalog=rman/rman@RMAN
rman> create catalog;

To drop the catalog run (no backups performed yet):
rman> drop catalog;


STEP 2 Check Flash Recovery Area for your PROD (optional)
This is where the backups are going to be saved. You can check this parameter by quering query v$recovery_file_dest view. If the space is not big enough, then increase it.
If this parameter is not defined yet, cou can do it by:
alter system db_recovery_file_dest = '/temp/...' scope=both;
alter system db_recovery_file_dest_size = 10G scope=both;
            (ALTER SYSTEM set db_recovery_file_dest_size=60G scope=both sid='*' ;  if using RAC)

STEP 3 Register Your Database (only if you created a catalog)
Now you need to register your target database (PROD) with the catalog you've created (if you did it). This populates the catalog with initial information regarding the configuration of your target database and includes a full synchronization of the catalog with the target database's control file. Call to the rman command file FROM your TARGET DB. For example from the PROD box:
rman target / catalog=rman/rman@RMAN
OR
rman target <qstring> [rcvcat <qstring> | cmdfile <qstring> |
msglog <qstring> | append | trace <qstring>]

TARGET= A connect string containing a userid and password for the database on which Recovery Manager is to operate.
RCVCAT= A connect string that contains a userid and password for the database that contains the recovery catalog (RMAN).
CMDFILE= The name of a file that contains the input commands for RMAN. If this argument is specified, RMAN operates in batch mode; otherwise, RMAN operates in interactive line mode.
MSGLOG= The name of a file where RMAN records commands and output Results. If not specified, RMAN outputs to the screen.
APPEND= This parameters causes the msglog file to be opened in append mode. If this parameter is not specified and a file with the same name as the msglog file already exists, it is overwritten.
TRACE=  A file name where RMAN will dump a trace information. (useful feature for RMAN jobs debugging)

Note: RMAN automatically requests a connection to the target database as SYSDBA. In order to connect to the target database as SYSDBA, you must either:

Be part of the operating system DBA group with respect to the target database. This means that you have the ability to CONNECT INTERNAL to the target database without a password.

-or -

Have a password file setup. This requires the use of the "orapwd" command and the initialization parameter "remote_login_passwordfile".

--As a comment, you can start RMAN from any PROD server using:
rman catalog=rman/rman@RMAN target=sys/passwd@PROD
or
rman
connect target sys/mh_flyers@PROD
connect rcvcat rman/rman@RMAN

Then Register the Database:
rman> register database

Once complete, the DBID, DB_NAME and structure are captured in the recovery catalog. To verify the registration run:
rman> list incarnation;
List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            CUR Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
38      39      PROD     4283267716       YES 19688901   10-MAY-01

NOTE= Take a note of your DB ID!!!!!!


STEP 4
Back Up the Catalog and Synchronize All Databases
If you lose your catalog, you can partially regenerate it from the information in the control file, but you should avoid that predicament by backing it up. You can use RMAN to back up the catalog by creating a recovery-catalog schema in one of your other instances, using that as the catalog for your primary catalog instance. Providing that these two instances do not share any common resources, this role reversal is free from single points of failure.
It's important that your recovery catalog has an up-to-date view of your database. When you execute RMAN operations on the database, RMAN automatically synchronizes the catalog with the target. However, depending on the volatility of your database, you may need to resynchronize the catalog more frequently. Always include catalog resynchronization as part of any structural database change (the addition of files, tablespaces, and so on) and ensure that a resynchronization occurs at intervals less than the init.ora setting for the parameter CONTROL_FILE_RECORD_KEEP_TIME. To perform this action, issue the RESYNC CATALOG command from within RMAN.
 

STEP 5 Setup RMAN Settings
You can always setup the settings from the OEM Console: DB Home Page/Maint Property / Backup and Recovery. This settings are specified only one time.
Use the following command to check your settings once you connect:
rman target / catalog=rman/rman@RMAN
rman> show all;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/9.2.0/dbs/snapcf_ORA920.f'; # default
Recommended  Configuration Parameters
configure default device type to disk;
configure retention policy to redundancy 2;
configure device type disk parallelism 2;
configure channel device type disk format '/orabackup/rman/ORA920/backup_%d_set%s_piece%p_%T_%U' maxpiecesize 5000M;
configure controlfile autobackup on;
configure controlfile autobackup format for device type disk to '/orabackup/rman/ORA920/rman_backup_controlfile_%d_ctl';
configure backup optimization on;

rman> show all;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
#By Default save all the Backups to disk

CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
# Keep at least two copies of each datafile and controlfile backup.
# Note that when configuring a retention policy, RMAN will not cause backups to be automatically deleted.
# The retention policy will, however, mark backups as OBSOLETE that have fallen outside the retention period.
# RMAN commands like "REPORT OBSOLETE" and "DELETE OBSOLETE" will work with these obsolete backups.
# If you have certain backups which must be retained longer than this retention policy,
# you can use the KEEP option with the BACKUP command when creating those backups

CONFIGURE DEVICE TYPE DISK PARALLELISM 2;
# By default put 2 channels per backup:
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/orabackup/rman/ORA920/backup_db_%d_S_%s_P_%p_T_%t' MAXPIECESIZE 5000 M;
# Specifies the location and name of the backup files. We need to specify the format for each channel.
# The format specifier %t is replaced with a 4-byte timestamp, %s with the backup set number, and %p with the backup piece number.
The "%U" ensures that Oracle appends a unique identifier to the backup file name.
The MAXPIECESIZE (optional) attribute sets a maximum file size for each file in the backup set.

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

CONFIGURE CONTROLFILE AUTOBACKUP ON;
# Performs backup of controlfile and spfile to the same backup piece each time a backup or copy is performed (default location is FRA).
NOTE= If you are not going to use a recovery catalog, and you wish to be able to recover your control file after an automated control file backup,
you must know the DBID of the database. You should, as a part of your initial setup and configuration of RMAN, note the DBIDs of the databases that
you will be backing up and save that list somewhere safe.
The DBID of the database is available from the V$DATABASE view in the DBID column and it's also displayed when you start RMAN and connect to a target database.

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/some_location/rman_backup_controlfile_%d_%F_ctl.bckp';
# Performs the Backup of the controlfile on that place instead of the Flash Recovery Area,
# The "%F" in the file name
translates into c-IIIIIIIIII-YYYYMMDD-QQ, where:
* IIIIIIIIII - stands for the DBID. (The DBID is printed in decimal so that it can be easily associated with the target database.
* YYYYMMDD - is a time stamp in the Gregorian calendar of the day the backup is generated.
* QQ - is the sequence in hexadecimal number that starts with 00 and has a maximum of 'FF' (256).


CONFIGURE BACKUP OPTIMIZATION ON;
#If we turn this command ON, then RMAN will not backup already backuped unchanged data files



Other Options:

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/some_location/snapcf_ORA920.f';
# Where to create the snapshot file for resync

CONFIGURE EXCLUDE FOR TABLESPACE tbl_exclude;
# Excludes tbs_exclude from full database backups.
# NOEXCLUDE can be specified with the BACKUP command to override this configuration.

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;

Governs archived redo log deletion policy for the flash recovery area. Possible options :
APPLIED ON STANDBY - enables FRA to delete archivelogs that are applied on mandatory standby. 
NONE        - enables FRA to delete archivelogs that are backed up to tertiary device and that are obsolete based on the configured backup retention policy.
This is the default configuration.
CLEAR       - clears the deletion policy and returns the specified configuration to default value. The default value is NONE.
By default, archived redo log files in a FRA that were backed up to a tertiary device or made obsolete (as defined by the RMAN retention policy) are eligible for deletion. 
Examples of the CONFIGURE ARCHIVELOG DELETION POLICY Command:
When backups of archived redo log files are taken on the standby database:
1. Issue the following command on the primary database:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
2. Issue the following command on the standby database:
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;

When backups of archived redo log files are taken on the primary database:
1. Issue the following command on the standby database:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
2. Issue the following command on the primary database:
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;

Reconfiguring the Deletion Policy After a Role Transition :
After a switchover or failover, you may need to reissue the RMAN CONFIGURE ARCHIVELOG DELETION POLICY command on each database.
If the backup site for archived redo log files remains the same, then do nothing.
Otherwise, you must switch the archivelog deletion policy by issuing the
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY statement on the database where backups are not taken, and issuing the CONFIGURE ARCHIVELOG DELETION POLICY TO NONE statement on the database where backups are taken.

Viewing the Current Deletion Policy
To view the current setting (APPLIED ON STANDBY, CLEAR, NONE) for a database,issue the following query:
SELECT NAME, VALUE
FROM V$RMAN_CONFIGURATION
WHERE NAME LIKE '%ARCHIVELOG DELETION POLICY%';
NAME                          VALUE
----------------------------- --------------
ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY

You can also find the existing configuration using the RMAN SHOW ARCHIVELOG DELETION POLICY command:
RMAN> SHOW ARCHIVELOG DELETION POLICY
RMAN configuration parameters are:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

Using Substitution Variables
RMAN can make use of substitution variables in creating format strings to generate filenames. Without due care, non-unique filenames can be generated and, under certain circumstances, this can cause backup data to be overwritten and therefore lost. So be careful; this is not a bug with Oracle, but rather a usage error.

Format Description
%p specifies the backup piece number within the backup set. This value starts at 1 for each backup set and is incremented by 1 as each backup piece is created.
%s specifies the backup set number. This number is a counter in the control file that is incremented for each backup set. The counter value starts at 1 and is unique for the lifetime of the control file. If you restore a backup control file, then duplicate values can result. Also, CREATE CONTROLFILE initializes the counter back to 1.
%d specifies the database name.
%n specifies the database name, padded on the right with 'x' characters to a total length of 8 characters. For example, if PROD1 is the database name, then PROD1xxx is the padded database name.
%t specifies the backup set timestamp, which is a 4-byte value derived as the number of seconds elapsed since a fixed reference time. The combination of %s and %t can be used to form a unique name for the backup set. Format YYYYMMDD
%u specifies an 8-character name constituted by compressed representations of the backup set number and the time the backup set was created.
%c (Oracle8i or higher) - specifies the copy number of the backup piece within a set of duplexed backup pieces. If you did not issue the set duplex command, then this variable will be 1 for regular backup sets and 0 for proxy copies. If you issued set duplex, the variable identifies the copy number: 1, 2, 3, or 4.
%U (Oracle8i or higher) - specifies a convenient shorthand for %u_%p_%c that guarantees uniqueness in generated backup filenames. If you do not specify a format, RMAN uses %U by default. %U is the default in Oracle8i.



We can always clean these settings to its default values using:
configure ......   clear;

STEP 5.5 Validating Backups (optional)
You can run a test RMAN backup that does not generate any output. The test checks datafiles for physical and logical corruption and that all database files exist and are in the correct locations. For example:
RMAN> backup validate database archivelog all;

or a Full Validation:
run
{
restore database validate;
restore archivelog from time 'sysdate-1' validate;
restore controlfile validate;
restore spfile validate;
}

If you get:
RMAN-06059: expected archived log not found, lost of archived log compromises recoverability

It means that the Archive Log Files doesn't exist (maybe was removed on OS level but not by RMAN).  Your options are either to restore the missing file(s), or to perform a crosscheck. To perform a crosscheck, run the following command from within RMAN:
RMAN> crosscheck archivelog all;
This will give non-existing archivelogs status of 'EXPIRED', these are no longer used for backups.
When an archive log crosscheck is performed, RMAN checks each archive log in turn to make sure that it exists on disk (or tape). Those that are missing are marked as unavailable. If you have got missing logs, this won't bring them back. It will allow you to get past this error and back-up the database though.
It is advisable to perform a full backup of the database at this point. Schedule a crosscheck every once in a while (e.g. weekly) in order to prevent this.
And then run:
RMAN> list expired archivelog all;
to see if it lists any missing archive logs.
And then:
RMAN> delete expired archivelog all;
to remove the expired ones.


The VALIDATE command initiates data integrity checks, logging physical, and optionally logical, block corruptions of database files and backups in the V$DATABASE_BLOCK_CORRUPTION view and the Automatic Diagnostic Repository as one or more failures.
The following code shows some of the possible syntax variations.

# Check for physical corruption of all database files.

VALIDATE DATABASE;

# Check for physical and logical corruption of a tablespace.
VALIDATE CHECK LOGICAL TABLESPACE USERS;

# Check for physical and logical corruption of a datafile.
VALIDATE CHECK LOGICAL DATAFILE 4;

# Check for physical corruption of all archived redo logs files.
VALIDATE ARCHIVELOG ALL;

# Check for physical and logical corruption of the controlfile.
VALIDATE CHECK LOGICAL CURRENT CONTROLFILE;

# Check for physical and logical corruption of a specific backupset.
VALIDATE CHECK LOGICAL BACKUPSET 3;


The BACKUP VALIDATE and RESTORE VALIDATE commands perform the same checks as the VALIDATE command for the files targeted by the backup or restore command, but they don't actually perform the specified backup or restore operation. This allows you to check the integrity of a backup or restore operation before actually performing it.
The following code shows some of the possible syntax variations.

# Check for physical corruption of files to be backed up.
BACKUP VALIDATE DATABASE ARCHIVELOG ALL;

# Check for physical and logical corruption of files to be backed up.
BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;

# Check for physical corruption of files to be restored.
RESTORE VALIDATE DATABASE;

# Check for physical and logical corruption of files to be restored.
RESTORE VALIDATE CHECK LOGICAL DATABASE;


STEP 6 Create Database Backups
Now you are ready to run a Full Backup.

The simplest backup is an offline (Cold) backup, you can:
% rman target system/manager@PROD
RMAN> shutdown immediate
RMAN> startup force dba;
RMAN> shutdown immediate
RMAN> startup mount
RMAN> backup database include current controlfile tag 'before_upgrade';
RMAN> alter database open;

Note the inclusion of the current control file with the backup, and the use of the tag to identify the backup. To use this backup of the database, the control file must be restored from the same backup as the rest of the database. Adding INCLUDE CURRENT CONTROLFILE ensures that a usable backup of the control file is included with the backup and tagged in order to simplify restoring the control file with the rest of the database.


Now for an Online (Hot) Bakup:
export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
% rman target system/manager@PROD

rman> backup spfile;                                                                        #Just to test the backup of this file, it will save it to the default place (Flash Recovery Area or Disk)
rman> backup database plus archivelog tag="Full_Database";    # Will backup to the default place (Flash Recovery Area or Disk) with a tag
rman> backup database format '/tmp/%U';                                     #Backup to a Specific Place, the %U generates a unique name.
rman> backup archivelog all delete input;                                      #Backup All Archive log Files and delete the ones that were backuped from PROD
rman> backup archivelog all delete all input;                                 #Backup All Archive log Files and delete all of them from PROD

Next we perform a complete database backup using a single command, we will put a tag and delete obsolete files:
RMAN> run {
   backup database plus archivelog tag="Full_Database";
   delete noprompt force obsolete;
}

When you run RMAN in command line mode, it sends the output to the terminal.
If you specify the msglog option, then RMAN writes the output to a specified log file instead. Example:

rman target=/ nocatalog msglog Full_RMAN_Backup.txt

In this example, a sample RMAN script is placed into a command file called commandfile.rcv. You can run this file from the operating system command line and write the output into the log file outfile.txt as follows:
rman TARGET / CATALOG rman/cat@catdb CMDFILE commandfile.rcv LOG outfile.txt

The Abbreviated Syntax is:
BACKUP [ FULL | INCREMENTAL LEVEL integer ]
 [ CUMULATIVE ]
 [ FORMAT '/path/filename' ]
 [ TAG tag ]
 {DATABASE | TABLESPACE 'name' , .... |
 DATAFILE 'name' | number
 BACKUPSET ALL | number | time
 CURRENT CONTROLFILE} (choose one)
 [ PLUS ARCHIVELOG [DELETE INPUT] ]


STEP 7 - Test Restore process of your backup (optional)
This will do everything except actually restore the database. This is the best method to determine if your backup is good and usable before being in a situation where it is critical and issues exist.
The following commands can be run any time to check if RMAN is capable of restoring database/tablespace using existing backups.

# Check if database can be restored
RMAN> restore database validate;

or a Full Validation:
run
{
restore database validate;
restore archivelog from time 'sysdate-1' validate;
restore controlfile validate;
restore spfile validate;
}


# Check if tablespace is restorable
RMAN> restore validate tablespace read_only_tablespace_name ;

# Check if controlfile can be restored
RMAN> restore controlfile validate ;

#check if archivelogs for the past two weeks can be restored
RMAN> restore archivelog from time 'sysdate-14' validate;

Some Points to Remember:
- RMAN doesn't automatically delete obsolete files, you need to specify the DELETE OBSOLETE command, but if you are using FRA you don't need to specify the OBSOLETE command.
- The catalog is usefull ONLY to save some scripts, except for that, all the RMAN features work equally well with or without a catalog. The repository is ALWAYS on the controlfile of the TARGET DB.
- A whole backup is a backup of all database files and current control file, perform this one using the BACKUP DATABASE command.
- You can specify SQL commands on RMAN like:
          rman> alter system archive log current;   #switch log files


Other RMAN Commands
#Backup Some tablespaces only:
rman> backup tablespace users, tools;
rman> backup tablespace SYSTEM format '/u01/oracle/db/AKI1/bck/ora_df%t_s%s_s%p';

#Backup a datafile:
rman>backup datafile copy '/tmp/system01.dbf';

#Also backup the Archve log Files:
rman>backup .......  plus archivelog;

#Backup all the archive log Files:
rman> backup archivelog all;

#Backup all the archive log Files and delete them from their location:
rman> backup archivelog all delete all input;

#Backup current control file to specific place:
rman> backup current controlfile to '/.../....' reuse;


Step 7 (optional) -  You can create a Unix Script
That will call the saved script in RMAN
We want to run it every Sunday night as a scheduled job. Let's run it as a cron job. Create a Korn shell script called "rman_backup.ksh". This shell script will run the RMAN script, prod_full_backup.rcv.

#!/bin/ksh
# Shell script to run RMAN script prod_full_backup.rcv
# Logging messages to prod_full_backup.log
# Set environment variables
export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
export ORACLE_SID=PROD

export ORACLE_HOME=/usr/local/OraHome1
# Connect to RMAN and run the backup script (all in one line)
rman target / rcvcat rman/rman@rman cmdfile /home/oracle/script/rman_prod_full_backup.rcv msglog /home/oracle/script/rman_full_backup.log
exit

Change permissions on the shell script appropriately.
chmod 755 rman_backup.ksh

Now, create the RMAN script file, prod_full_backup.rcv. This just runs the previously stored catalog script, prod_full_backup. Here are the contents of prod_full_backup.rcv
run {execute script prod_full_backup;}

Finally, schedule the shell script using the crontab.
[prod-server:oracle] $ crontab -e
00 21 * * 0 /home/oracle/script/rman_backup.ksh

We have scheduled our script to run every Sunday night at 9:00 PM.


Simplest Backup Command

The following is one example of RMAN doing an online database backup. In this example, the backup sets are written to disk.

run {
allocate channel t1 type disk;
allocate channel t2 type disk;
allocate channel t3 type disk;

#backup the whole db
backup tag 'whole_database_open' format '/oracle/backups/BFS/backup_%d_set%s_piece%p_%T_%U' database;

# switch out of the current logfile
alter system archive log current;

#backup the archived logs
crosscheck archivelog all;
backup archivelog all format '/oracle/backups/BFS/arch_logs_%u';

# backup a copy of the controlfile that contains records for the other backups just made
backup current controlfile tag = "Control_File" format '/oracle/backups/BFS/ControlFile_%u' reuse;
CROSSCHECK BACKUP;
DELETE NOPROMPT OBSOLETE;
DELETE NOPROMPT EXPIRED BACKUP;
RELEASE CHANNEL t1;
RELEASE CHANNEL t2;
RELEASE CHANNEL t3;
}



Understanding the Backup Process
Now that RMAN is configured, backing up the database (including backing up all archive redo logs), is as easy as:

RMAN Statements to Perform Full (online) Database Backup
% rman target system/manager@PROD nocatalog msglog rman_full_backup_db_online_ORA920.log
RMAN> backup database plus archivelog delete input;
RMAN> delete noprompt force obsolete;
RMAN> exit

The following table includes all resulting backup sets contained in the control file after performing the above backup routine.

BS
Key
Backup
Type
Device
Type
Controlfile
Included?
SPFILE
Included?
Piece
Number
Handle (Piece) Name Start
Time
End
Time
Elapsed
Seconds
Tag Block
Size
2 Datafile Full DISK     1 /orabackup/rman/ORA920/backup_db_ORA920_S_5_P_1_T_543443342 11/28/04 20:29:02 11/28/04 20:32:44 222 TAG20041128T202902 8,192
          2 /orabackup/rman/ORA920/backup_db_ORA920_S_5_P_2_T_543443342 11/28/04 20:32:47 11/28/04 20:34:45 118 TAG20041128T202902 8,192
3 Archived Logs DISK     1 /orabackup/rman/ORA920/backup_db_ORA920_S_6_P_1_T_543443693 11/28/04 20:34:53 11/28/04 20:34:53 0 TAG20041128T203452 512
4 Datafile Full DISK YES YES 1 /orabackup/rman/ORA920/c-2542143170-20041128-01 11/28/04 20:34:54 11/28/04 20:34:55 1   8,

Now, let's talk see actual steps that were performed by RMAN after running the full (online) database backup statements. I attempted to annotate the RMAN log file (below) with the following steps. Note that the first 6 steps were all part of the backup database plus archivelog delete input; statement. The 7th step includes the delete noprompt force obsolete; statement

  1. The first action performed by RMAN is a log switch (using the alter system srchivelog current command).

  2. Next, all existing archived redo logs (including the one just archived) are backed up to a Archive Log backup set. Note that at the end of the RMAN statements in our example, this archive log backup set (backup_db_ORA920_S_4_P_1_T_543443326) will be removed as it will no longer be needed once the database files are backed up.

  3. RMAN then starts the backup of the actual database files. For this backup set, (BS Key - 2), RMAN creates two backup pieces: backup_db_ORA920_S_5_P_1_T_543443342 and backup_db_ORA920_S_5_P_2_T_543443342.

  4. After the full database backup, RMAN performs another archive redo log switch (using the alter system archivelog current command).

  5. After the archive redo log switch completes, RMAN creates an Archived Log backup set (BS Key - 3) of the remaining archived redo logs (using the backup archivelog all command). This is backup piece backup_db_ORA920_S_6_P_1_T_543443693. Keep in mind that this archived redo log backup set includes the redo logs that occured during the full database backup.

  6. Finally, the autobackup of the control file and SPFILE occurs and creates another datafile backup set (BS Key - 4). This is contained in backup piece c-2542143170-20041128-01. Since a full database backup will always include datafile 1, which belongs to the SYSTEM tablespace, there will always be a backup of the control file and the SPFILE. At this point, all tasks for the backup database plus archivelog delete input; statement have been completed.

  7. The delete noprompt force obsolete; statement is then run to apply the retention policy we have configured. In our example, the first Archive Log backup set (that was performed at the begining on this example) will be removed. This was piece backup_db_ORA920_S_4_P_1_T_543443326.

RMAN Full (online) Database Backup Log File
Recovery Manager: Release 9.2.0.5.0 - Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

connected to target database: ORA920 (DBID=2542143170)
using target database controlfile instead of recovery catalog

RMAN>

Starting backup at 28-NOV-04
current log archived (Step 1)
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=16 devtype=DISK
channel ORA_DISK_1: starting archive log backupset (Step 2)
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=89 recid=1 stamp=543441301
input archive log thread=1 sequence=90 recid=2 stamp=543443042
input archive log thread=1 sequence=91 recid=3 stamp=543443132
input archive log thread=1 sequence=92 recid=4 stamp=543443258
input archive log thread=1 sequence=93 recid=5 stamp=543443325
channel ORA_DISK_1: starting piece 1 at 28-NOV-04
channel ORA_DISK_1: finished piece 1 at 28-NOV-04
piece handle=/orabackup/rman/ORA920/backup_db_ORA920_S_4_P_1_T_543443326 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: deleting archive log(s)
archive log filename=/u06/app/oradata/ORA920/archive/arch_t1_s89.dbf recid=1 stamp=543441301
archive log filename=/u06/app/oradata/ORA920/archive/arch_t1_s90.dbf recid=2 stamp=543443042
archive log filename=/u06/app/oradata/ORA920/archive/arch_t1_s91.dbf recid=3 stamp=543443132
archive log filename=/u06/app/oradata/ORA920/archive/arch_t1_s92.dbf recid=4 stamp=543443258
archive log filename=/u06/app/oradata/ORA920/archive/arch_t1_s93.dbf recid=5 stamp=543443325
Finished backup at 28-NOV-04

Starting backup at 28-NOV-04
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset (Step 3)
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u06/app/oradata/ORA920/system01.dbf
input datafile fno=00010 name=/u06/app/oradata/ORA920/users01.dbf
input datafile fno=00002 name=/u06/app/oradata/ORA920/undotbs01.dbf
input datafile fno=00011 name=/u06/app/oradata/ORA920/perfstat01.dbf
input datafile fno=00007 name=/u06/app/oradata/ORA920/example01.dbf
input datafile fno=00003 name=/u06/app/oradata/ORA920/cwmlite01.dbf
input datafile fno=00004 name=/u06/app/oradata/ORA920/drsys01.dbf
input datafile fno=00005 name=/u06/app/oradata/ORA920/odm01.dbf
input datafile fno=00006 name=/u06/app/oradata/ORA920/xdb01.dbf
input datafile fno=00008 name=/u06/app/oradata/ORA920/indx01.dbf
input datafile fno=00009 name=/u06/app/oradata/ORA920/tools01.dbf
input datafile fno=00012 name=/u06/app/oradata/ORA920/users02.dbf
channel ORA_DISK_1: starting piece 1 at 28-NOV-04
channel ORA_DISK_1: finished piece 1 at 28-NOV-04
piece handle=/orabackup/rman/ORA920/backup_db_ORA920_S_5_P_1_T_543443342 comment=NONE
channel ORA_DISK_1: starting piece 2 at 28-NOV-04
channel ORA_DISK_1: finished piece 2 at 28-NOV-04
piece handle=/orabackup/rman/ORA920/backup_db_ORA920_S_5_P_2_T_543443342 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:05:50
Finished backup at 28-NOV-04

Starting backup at 28-NOV-04
current log archived (Step 4)
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset (Step 5)
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=94 recid=6 stamp=543443692
channel ORA_DISK_1: starting piece 1 at 28-NOV-04
channel ORA_DISK_1: finished piece 1 at 28-NOV-04
piece handle=/orabackup/rman/ORA920/backup_db_ORA920_S_6_P_1_T_543443693 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archive log(s)
archive log filename=/u06/app/oradata/ORA920/archive/arch_t1_s94.dbf recid=6 stamp=543443692
Finished backup at 28-NOV-04

Starting Control File and SPFILE Autobackup at 28-NOV-04 (Step 6)
piece handle=/orabackup/rman/ORA920/c-2542143170-20041128-01 comment=NONE
Finished Control File and SPFILE Autobackup at 28-NOV-04

RMAN>
RMAN retention policy will be applied to the command (Step 7)
RMAN retention policy is set to redundancy 2
using channel ORA_DISK_1
Deleting the following obsolete backups and copies:
Type Key Completion Time Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set 1 28-NOV-04
Backup Piece 1 28-NOV-04 /orabackup/rman/ORA920/backup_db_ORA920_S_4_P_1_T_543443326
deleted backup piece
backup piece handle=/orabackup/rman/ORA920/backup_db_ORA920_S_4_P_1_T_543443326 recid=1 stamp=543443326
Deleted 1 objects
RMAN>
Recovery Manager complete.


Delete specified archivelogs and backups
As part of the regular backup file log maintenance, the recovery window and redundancy commands are usually used within backup scripts to define how long the backups would stay on disk or on tape. For archive log retention, the delete archivelog command is utilized.

DELETE Archive logs
To clean-up older archives the “backup archive log delete input” is very commonly used to manage the archive log retention on disk. This however has a very serious drawback because as soon as the backup is finished all the archive logs are purged. In this scenario if recovery is required then the backup of the archive from tape has to be restored and then they can be applied. This increases the downtime due to the slower recovery rate from tape. Also for environments that extract data from archive logs, immediate purging of these log files could interfere with processes like Golden Gate and Streams, in case these processes are down for some reason and the backup job fires, it will remove the archive logs that will be required on the process start-up.
Thus to retain these archive logs for a longer time the following can used in the backup script to manage the archive logs on disk
DELETE archivelog ALL COMPLETED BEFORE ‘sysdate-1’;
DELETE archivelog ALL BACKED UP 2 TIMES to disk;
DELETE NOPROMPT archivelog UNTIL SEQUENCE = 3790;

Delete Archive log Backups
The following command can be used to manage the backup of the archive log when storage space needs to be released.
DELETE BACKUP OF archivelog UNTIL TIME='sysdate-5';

Obsolete database backups
Backups that fall outside the specified recovery window can be purged uisng the obsolete command
DELETE OBSOLETE RECOVERY WINDOW OF 4 DAYS;


Special Considerations for RAC

- Check that your DB is in archive log mode
- Set up Flash Recovery to save the archive log files and backuped files to an NFS mounted drive or to the cluster file system (much better). The directory for Archive Log Files MUST exist in all the nodes.
- Setup the snapshot control file Location. The snapshot control file is a temporary snapshot control file that RMAN creates to re-synchronize from a read-consistent version of the control file. RMAN only needs a snapshot control file when re-synchronizing with the recovery catalog or when making a backup of the current control file.
In RAC, the snapshot control file is only needed on the nodes on which RMAN performs backups; the snapshot control file does not need to be globally available to all instances in a RAC environment.
You can specify a cluster file system file or a raw device destination for the location of your snapshot control file.
- Verify connectivity to all the boxes
- Connect to RMAN to verify and set the controlfile persistent configuration. The controlfiles are shared between the instances. Also note the target DBID at connection. Perform the following parameter modifications:
rman> show all;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/9.2.0/dbs/snapcf_ORA920.f'; # default
configure retention policy to redundancy 3;       #retain the 3 last backups performed
configure default device type to disk;
configure controlfile autobackup on;
#Performs backup of controlfile and spfile each time a backup is performed
configure snapshot controlfile name to '$ORACLE_HOME/dbs/snapcf_ORACLE_SID'; # Where to create the snapshot file for resync
configure controlfile autobackup format for device type disk to '/backup_oltp/%d_%F_ctl.bckp';
#Performs Backups on that place instead of the Flash Recovery Area, The "%F" in the file name instructs RMAN
#to append the database identifier and backup timestamp to the backup filename.
#The database identifier, or DBID, is a unique integer identifier for the database. Note that the control file is only specified for a single location,
#which requires each node to have that directory in each node.
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/backup_oltp/backup_db_%d_S_%s_P_%p_T_%t';
configure device type disk parallelism 2; #Put 2 because 2 nodes
configure channel 1 device type disk connect
'SYS/password@node1';
configure channel 2 device type disk connect 'SYS/password@node2';


Managing Archived Redo Logs on RAC
When a node generates an archived redo log, Oracle always records the filename of the log in the control file of the target database. If you are using a recovery catalog, then RMAN also records the archived redo log filenames in the recovery catalog when a re-synchronization occurs. The archived redo log naming convention that you use is important because when a node writes to a log with a specific filename on its file system, the file must be readable by any node that needs to access this archived redo log. For example, if node 1 archives a log to /oracle/arc_dest/log_1_100_23452345.arc, then node 2 can only back up this archived redo log only if it can read /oracle/arc_dest/log_1_100_23452345.arc on its own file system.
The backup and recovery strategy that you choose depends on how you configure the archiving destinations for each node. Whether only one node or all nodes perform archived redo log backups, you need to ensure that all archived redo logs are backed up. If you use RMAN parallelism during recovery, then the node that performs recovery must have read access to all archived redo logs in your cluster.
As an example, if the instance associated with redo thread number 1 sets LOG_ARCHIVE_FORMAT to log_%t_%s_%r.arc, then its archived redo log files are named:
log_1_1000_23435343.arc
log_1_1001_23452345.arc
log_1_1002_23452345.arc
The primary consideration is to ensure that all archived redo logs can be read from every node during recovery, and if possible during backups.
I HIGHLY recommend to use a CFS to save the archived redo logs from all the nodes to a single location

- Perform a Backup with differential incremental 0 and then archived logs using delete input option.
rman> backup incremental level 0 format '/shared_directory/%d_LVL0_%T_%u_s%s_p%p'
database PLUS ARCHIVELOG format
'/shared_directory/%d_ARCGLOG_%T_%u_s%s_p%p'
delete input;


- Backupset Maintenance using the configured retention policy
rman>
list backup summary;
list backup of database;
list backup of archivelog all;
list backup of controlfile;
report obsolete;
delete obsolete or delete noprompt obsolete;
report schema;

- Deleting archived Redo Logs after a Successful Backup
Assuming that you have configured the automatic channels, you can use the following example to delete the archived logs that you backed up n times (in this case I'm using a '1' to specify RMAN not to delete the archive logs unless it has a record of them being backed up at least once). The device type can be DISK or SBT:
rman> DELETE ARCHIVELOG ALL BACKED UP 1 TIMES TO DEVICE TYPE disk;
During a delete operation, as long as at least one of the channels allocated has access to the archived log, RMAN will automatically schedule the deletion of the specific log on that channel. For a local archiving scheme, there must be at least one channel allocated that can delete an archived log. For a CFS archiving scheme, assuming that every node writes to the archived logs on the same CFS, the archived log can be deleted by any allocated channel.

If you have not configured automatic channels, then you can manually allocate the maintenance channels as follows and delete the archived logs.
rman> ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE DISK CONNECT 'SYS/oracle@node1';
ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE DISK CONNECT 'SYS/oracle@node2';
DELETE ARCHIVELOG ALL BACKED UP 1 TIMES TO DEVICE TYPE disk;


Nice Script for RAC
backup.sh
#!/bin/csh -x
# rman_backup_as_copy_to_FS
# -------------------
# 29-01-07 Alejandro Vargas
# -------------------
# This script make a backup copy to file system
# This backup can be restored on File system as a regular hot backup
# Or can be restored to ASM by using rman
# --------------------------------------
# This script does:
# 1) Administrative tasks:
# crosscheck
# delete obsolete
# 2) Archive log current on 1st Instance
# 3) Archive log current on 2nd Instance
# 4) Rman backup as copy to file system including controlfile and archivelogs
# 5) Archive log current on 1st Instance
# 6) Archive log current on 2nd Instance
# 7) Rman backup as copy archivelogs not backed up and print backupset list to log
# --------------------------------------
# This script works with 2 nodes only, if you have more than 2 nodes you need to customize it.
#
# This script use aliases and Environment variables set on .cshrc
# to setup the environment to point to the Database:
# setenv DBS_HOME /u01/app01/oracle/product/10gDB
# setenv BASE_PATH /usr/kerberos/sbin:/usr/kerberos/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/usr/X11R6/bin:/root/bin
# alias 10db ‘setenv $ORACLE_HOME $DBS_HOME; setenv PATH $ORACLE_HOME/bin:$BASE_PATH’
# This script do require as parameters the 2 instance names
# It will use them to archive all required logs from instances 1 and 2
# --------------------------------------
set v_inst1=mars1
set v_inst2=mars2
# Rman Backup Location variable
# -------------------
set v_rman_loc=/orabackup/rman
# Step 1: Administrative tasks, crosscheck and delete obsolete
# -------------------
#10db
setenv ORACLE_SID $v_inst1
rman target / nocatalog <<EOF
crosscheck backupset;
crosscheck copy;
crosscheck archivelog all;
delete noprompt expired backup ;
delete noprompt obsolete;
exit
EOF
# This script run from 1st node. We use an external identified DBA user, ops$oracle, to execute
# the archive log current. From the same session we connect as ops$oracle into the 2nd instance
# You need remote_os_authent=TRUE on both instances to connect remotely without password
# Step 2: Archive log current on 1st Instance
# Step 3: Archive log current on 2nd Instance
# --------------------------------------
sqlplus -s sys/pass@$v_inst1 as sysdba << EOF
select instance_name from v\$instance
/
alter system archive log current
/
connect sys/pass@$v_inst2 as sysdba;
select instance_name from v\$instance
/
alter system archive log current
/
exit
EOF
# On step 4 we use 4 channels. This needs to be customized according the number of cpu’s/IO
# channels available. Rman is invoked in nocatalog mode, we need to have configured
# ORACLE_HOME, ORACLE_SID and PATH on the environment, as we did in the previous steps.
# Step 4: Rman backup as copy to file system including controlfile and archivelogs
# --------------------------------------
rman target / nocatalog <<EOF
run {
allocate channel backup_disk1 type disk format ‘$v_rman_loc/%U’;
allocate channel backup_disk2 type disk format ‘$v_rman_loc/%U’;
backup as COPY tag ‘%TAG’ database include current controlfile;
release channel backup_disk1;
release channel backup_disk2;
}
exit
EOF
# Step 5 and 6: Archive log current on 1st and 2nd Instances
# --------------------------------------
sqlplus -s sys/pass@$v_inst1 as sysdba << EOF
select instance_name from v\$instance
/
alter system archive log current
/
connect sys/pass@$v_inst2 as sysdba;
select instance_name from v\$instance
/
alter system archive log current
/
exit
EOF
# Step 7: Rman backup as copy archivelogs not backed up and print backupset list to log
rman target / nocatalog <<EOF
backup as copy archivelog all format ‘$v_rman_loc/%d_AL_%T_%u_s%s_p%p’ ;
list backupset;
exit
EOF
# Redirecting rman output to log will suppress standard output, because of that
# running separately.
rman target / nocatalog log=$v_rman_loc/backupset_info.log <<EOF
list backup summary;
list backupset;
list backup of controlfile;
exit
EOF
# eof backup


Another RAC on Linux with ASM RMAN Backup Script:

#!/bin/ksh
set -x
# Script : backupdb
# Alejandro Vargas
# March 6, 2007
# Backup database and archived logs required to recover it out of ASM to File System
# Backup as copy generate datafile copies that can be used to manually clone the database
# Environment Variables
# ---------------------
export ORACLE_SID=racdbtst1
export ORACLE_BASE=/oradisk/app01/oracle
export ORACLE_HOME=/oradisk/app01/oracle/product/10gDB

# Path Variables

# --------------
export BASE_PATH=/usr/kerberos/sbin:/usr/kerberos/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/usr/X11R6/bin:/root/bin
export PATH=${ORACLE_HOME}/bin:${BASE_PATH}

# Script Variables

# ----------------
# v_logvw = set the name of dynamic view v$log to variable v_logvw to be used inside a command.
# v_scni1 = initial sequence thread1
# v_scnf1 = final sequence thread1
# v_scni2 = initial sequence thread2
# v_scnf2 = final sequence thread2
# v_bdest = backup destination
v_logvw=gv\$log
v_scni1=`echo "select 'xX '||min(sequence#) from $v_logvw where thread#=1;" | sqlplus -s '/as sysdba' | grep "xX" |awk '{print $2}'`
v_scni2=`echo "select 'xX '||min(sequence#) from $v_logvw where thread#=2;" | sqlplus -s '/as sysdba' | grep "xX" |awk '{print $2}'`
v_bdest=/vmasmtest/BACKUP/rman_backups/

# Backup database

# ---------------
rman target / nocatalog <<EOF
run {
alter system archive log current;
allocate channel backup_disk1 type disk format '$v_bdest/%U';
allocate channel backup_disk2 type disk format '$v_bdest/%U';
allocate channel backup_disk3 type disk format '$v_bdest/%U';
allocate channel backup_disk4 type disk format '$v_bdest/%U';
backup as COPY database include current controlfile;
release channel backup_disk1;
release channel backup_disk2;
release channel backup_disk3;
release channel backup_disk4;
alter system archive log current;
}
exit
EOF
v_scnf1=`echo "select 'xX '||max(sequence#) from $v_logvw where thread#=1;" | sqlplus -s '/as sysdba' | grep "xX" |awk '{print $2}'`
v_scnf2=`echo "select 'xX '||max(sequence#) from $v_logvw where thread#=2;" | sqlplus -s '/as sysdba' | grep "xX" |awk '{print $2}'`
rman target / nocatalog <<EOF
run {
allocate channel backup_disk1 type disk format '$v_bdest/%U';
allocate channel backup_disk2 type disk format '$v_bdest/%U';
backup as copy archivelog
sequence between $v_scni1 and $v_scnf1 thread 1;
backup as copy archivelog
sequence between $v_scni2 and $v_scnf2 thread 2;
release channel backup_disk1 ;
release channel backup_disk2 ;
}
exit
EOF

# Clean Catalog

# -------------
rman target / nocatalog <<EOF
crosscheck backupset;
crosscheck copy;
crosscheck archivelog all;
delete noprompt expired backup ;
delete noprompt obsolete;
exit
EOF
rman target / nocatalog log=$v_bdest/backuset_info.log <<EOF
list backupset;
exit
EOF
# Eof backupdb


Backing up Backups
There is a legitimate need to take a copy of a backup set: for example, you backup to disk, because restores and recoveries from disk will be quicker than ones to tape.  But you can’t just keep backing up to disk, because it will run out of room.  Instead, you treat the disk as a staging area… as a new backup to disk is taken, the previous one is moved off onto tape.
The default backup directory is:
         /path1/backup_dir
You need to move the backups to another disk or directory.
         /path2/backup_dir/
To move the backupsets directory structure to restore a backup use:
BACKUP DEVICE TYPE disk BACKUPSET ALL format '/path2/backup_dir/prod9/%d_%U' DELETE INPUT;

To move the backupsets directory to tape and leave them on disk use:
Backup device type sbt backupset all;

This command would typically be run in a scenario where the user wants more recent backups to exist on disk and older backups to exist on tape, but does not need backups to exist on both disk and tape at the same time. Using DELETE INPUT effectively makes this a move operation. If you don't use DELETE INPUT then rman can failover if a error occurs reading the backup backupset.
BACKUP DEVICE TYPE SBT BACKUPSET CREATED BEFORE 'sysdate-7' DELETE INPUT;

…but (assuming a nightly backup, and room on disk for just one night’s backup) you could do this:
backup device type sbt backupset created before ‘sysdate-1’ delete input;
…and the ‘delete input’ bit ensures that the source backup set is removed from the disk, after being archived onto tape.


Unregister a Database From RMAN
First we start up RMAN with a connection to the catalog and the target, making a note of the DBID in the banner
rman catalog=rman/rman@rman target=sys/password@PROD

Next we list and delete any backupsets recorded in the repository:
rman> list backup summary;
rman> delete backup device type sbt;
rman> delete backup device type disk;

Next we connect to the RMAN catalog owner using SQL*Plus and issue the following statement:
SQL> CONNECT rman/rman@dba1
SQL> SELECT db_key, db_id FROM db WHERE db_id = 1487421514;
DB_KEY      DB_ID
---------- ----------
1          1487421514

The resulting key and id can then be used to unregister the database:
SQL> EXECUTE dbms_rcvcat.unregisterdatabase(1, 1487421514);

Deleting data from Catalog
If the backuped files has been deleted from the host, you can perform a "CLEAN" process running the following:
************************************************************************
--Clean ALL INVALID on RMAN (specially if I delete files manually)
************************************************************************
rman> crosscheck backup;
rman> report obsolete;
rman> delete obsolete;
rman> report schema;
rman> report need backup;
rman> list backup;
rman> list expired backup;
rman> delete expired backup;
rman> list expired backupset;
rman> delete expired backupset;
rman> RESYNC CATALOG;


Image Copies

An Image Copy is a Full OS type copy of a single file
The advantage of using Image Copies is that they can be used in manual recovery operations. The disadvantage of them is that they take more space than backup sets.
By default the BACKUP command in RMAN creates BackupSet(s) -- each of which is one or more BackupPiece(s).  A datafile may span BackupPieces but may not span a BackupSet.
However, RMAN does allow another method -- BACKUP AS COPY.  This is like a "User Managed Backups" created with OS commands -- except that the ALTER TABLESPACE | DATABASE BEGIN BACKUP command does not have to be issued.
BACKUP AS COPY creates a byte-for-byte copy of each datafile [except, blocks being modified by concurrent writes to the datafile].
If an active datafile is corrupted, the DBA can choose to SWITCH TO COPY instead of having to restore the datafile copy.  Thus, a switch can be a fast operation.  Obviously, the DBA must plan carefully where he creates such copies if he intends to SWITCH anytime later  (he wouldn't keep a datafile copy on a non-protected [RAID or ASM] storage target).
Here's a simple demo :

SQL> select file_id, file_name from dba_data_files where tablespace_name = 'ADD_TBS';
   FILE_ID FILE_NAME
---------- ----------------------------------------
        14 /oradata/orcl/add_tbs_01.dbf

SQL> alter tablespace add_tbs add datafile '/oradata/orcl/add_tbs_02.dbf' size 100M;

SQL> create table scott.add_table tablespace add_tbs as select * from dba_source;

SQL> select file_id, count(*) from dba_Extents where owner = 'SCOTT' and segment_name = 'ADD_TABLE' group by file_id order by 1;
   FILE_ID   COUNT(*)
---------- ----------
        14         48
        15         37
So, I have begun with two datafiles and extents spread across both datafiles. I now take backups of the two datafiles.

RMAN> backup as copy datafile 14 format '/tmp/ADDTBS01.DBF';

RMAN> backup as copy datafile 15 format '/tmp/add_tbs_second.dbf' ;
RMAN> list backup of tablespace add_tbs completed after "sysdate-1";

specification does not match any backup in the repository

RMAN> list backup of datafile 14;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
18      Full    530.30M    DISK        00:01:44     23-MAR-12     
        BP Key: 18   Status: AVAILABLE  Compressed: YES  Tag: TAG20120323T231454
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_03_23/o1_mf_nnndf_TAG20120323T231454_7ps4vgmf_.bkp
  List of Datafiles in backup set 18
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  14      Full 5192430    23-MAR-12 /oradata/orcl/add_tbs_01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
21      Full    530.70M    DISK        00:01:23     23-MAR-12     
        BP Key: 21   Status: AVAILABLE  Compressed: YES  Tag: TAG20120323T235528
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_03_23/o1_mf_nnndf_TAG20120323T235528_7ps77jf2_.bkp
  List of Datafiles in backup set 21
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  14      Full 5194695    23-MAR-12 /oradata/orcl/add_tbs_01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
24      Full    1.23M      DISK        00:00:00     24-MAR-12     
        BP Key: 24   Status: AVAILABLE  Compressed: NO  Tag: TAG20120324T001527
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2012_03_24/o1_mf_nnndf_TAG20120324T001527_7ps8dzm8_.bkp
  List of Datafiles in backup set 24
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  14      Full 5196666    24-MAR-12 /oradata/orcl/add_tbs_01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
29      Full    487.60M    DISK        00:00:00     01-JAN-12     
        BP Key: 29   Status: AVAILABLE  Compressed: YES  Tag: TAG20120101T112516
        Piece Name: /newmountpoint/FRA/2012_01_01/o1_mf_nnndf_TAG20120101T112516_7hznjx24_.bkp
  List of Datafiles in backup set 29
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  14      Full 4956816    01-JAN-12 /oradata/orcl/add_tbs_01.dbf


Notice how a LIST BACKUP doesn't show the backups ! Why ? Because it shows only BACKUPSET Backups ! If I want to see these BACKUP AS COPY backups, I must use LIST COPY :

RMAN> list copy of datafile 14;

List of Datafile Copies
=======================

Key     File S Completion Time Ckp SCN    Ckp Time      
------- ---- - --------------- ---------- ---------------
5       14   A 03-JUN-12       5353830    03-JUN-12     
        Name: /tmp/ADDTBS01.DBF
        Tag: TAG20120603T224508

4       14   A 24-MAR-12       5196322    24-MAR-12     
        Name: /home/oracle/app/oracle/oradata/orcl/add_tbs.dbf


RMAN> list copy of datafile 15;

List of Datafile Copies
=======================

Key     File S Completion Time Ckp SCN    Ckp Time      
------- ---- - --------------- ---------- ---------------
6       15   A 03-JUN-12       5353869    03-JUN-12     
        Name: /tmp/add_tbs_second.dbf
        Tag: TAG20120603T224538

What happens when I lose the datafiles ?

SQL> !rm /oradata/orcl/add_tbs_0[1-2].dbf

SQL> select count(*) from scott.add_table;

  COUNT(*)
----------
    703021

SQL> alter system checkpoint;


SQL> select count(*) from
scott.add_table;
  COUNT(*)
----------
    703021

SQL> alter system flush buffer_cache;


SQL> select count(*) from
scott.add_table;
  COUNT(*)
----------
    703021

SQL> alter tablespace add_tbs offline;


SQL> alter tablespace add_Tbs online;

alter tablespace add_Tbs online
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 14 - see DBWR trace file
ORA-01110: data file 14: '/oradata/orcl/add_tbs_01.dbf'

As an aside : Note how the extents and datafiles seemed to be accessible until I tried to ONLINE the datafiles.
Now, I do not have regular backups. Can I use the datafile copies ?  YES.

RMAN> restore datafile 14;
RMAN> switch datafile 15 to copy;

datafile 15 switched to datafile copy "/tmp/add_tbs_second.dbf"

I actually restored datafile 14 to the target location but only switched datafile 15 to the copy. This is clearly evidenced by :

SQL> select file_id, file_name  from dba_data_files where tablespace_name = 'ADD_TBS';
   FILE_ID FILE_NAME
---------- ----------------------------------------
        14 /oradata/orcl/add_tbs_01.dbf
        15 /tmp/add_tbs_second.dbf

I can now simply recover the tablespace.

RMAN> recover tablespace add_tbs;
RMAN> alter tablespace add_tbs online;

and query it now.

SQL> select /*+ FULL */ count(*) from scott.add_table;
  COUNT(*)
----------
    703021

SQL> alter tablespace add_tbs offline;
SQL> alter tablespace add_tbs online;

SQL> select /*+ FULL */ count(*) from hemant.add_table;
  COUNT(*)
----------
    703021

I can also restore datafile 15 by using the same "AS COPY" and "SWITCH" trick.

RMAN> backup as copy datafile 15 format '/oradata/orcl/add_tbs_02.dbf' ;

RMAN> alter tablespace add_tbs offline;
RMAN> switch datafile 15 to copy;
RMAN> recover tablespace add_tbs;
RMAN> alter tablespace add_tbs online;

SQL> select file_id, file_name from dba_data_files where tablespace_name = 'ADD_TBS';
   FILE_ID FILE_NAME
---------- ----------------------------------------
        14 /oradata/orcl/add_tbs_01.dbf
        15 /oradata/orcl/add_tbs_02.dbf


Incremental backups
If your database is particularly large, you may want to consider making incremental backups.
RMAN has the ability to detect which blocks in a datafile have changed since the last backup and will copy only those modified blocks.
Incremental backups capture block-level changes to a database made after a previous incremental backup. Incremental backups are generally smaller and faster to make than full database backups. Recovery with incremental backups is faster than using redo logs alone.
The starting point for an incremental backup strategy is a level 0 incremental backup, which backs up all blocks in the database. An incremental backup at level 0 is identical in content to a full backup, but unlike a full backup the level 0 backup is considered a part of the incremental backup strategy.
A level 1 incremental backup contains only blocks changed after a previous incremental backup. If no level 0 backup exists in either the current or parent database incarnation when you run a level 1 backup, then RMAN makes a level 0 backup automatically.
A level 1 backup can be a cumulative incremental backup, which includes all blocks changed since the most recent level 0 backup, or a differential incremental backup, which includes only blocks changed since the most recent incremental backup. Incremental backups are differential by default.
When restoring incremental backups, RMAN uses the level 0 backup as the starting point, then updates changed blocks based on level 1 backups where possible to avoid reapplying changes from redo one at a time. Recovering with incremental backups requires no additional effort on your part. If incremental backups are available, then RMAN uses them during recovery.

Each data block in a datafile contains a system change number (SCN), which is the SCN at which the most recent change was made to the block. During an incremental backup, RMAN reads the SCN of each data block in the input file and compares it to the checkpoint SCN of the parent incremental backup. If the SCN in the input data block is greater than or equal to the checkpoint SCN of the parent, then RMAN copies the block.

To improve the performance of incrementally updated backups another feature called Change Tracking has also been introduced since Oracle 10g. This lets you record the changes in the blocks of datafiles in a separate datafile in the database called Change Tracking File. Then when the time for backup comes, RMAN reads the Change Tracking File to find out the changes which happened to the database instead of scanning whole datafile. This makes the backup faster. This feature is available on Enterprise EDITION Only. You can enable Change Tracking with:

SQL> alter database enable block change tracking using file '/usr/local/oracle/testdw0/rman_change_track.dbf';

To make incremental backups of the database:
1- Run the BACKUP INCREMENTAL command.
The following example creates a level 0 incremental backup to serve as a base for an incremental backup strategy:
rman> BACKUP INCREMENTAL LEVEL 0 DATABASE;
Another Example:
rman> backup incremental level 0 tag = "Weekly_Full_Level_0_Database"  format '/data/oracle/BACKUP/rman_LVL0_%d.%t.%p.%c' database;

The following example creates a level 1 cumulative incremental backup (It backs up all blocks changed after the most recent incremental backup at level 0):
rman> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;
Another Example:
backup incremental level 1 cumulative tag="Oracle_Backup_Level_1" filesperset 5 format '/data/oracle/BACKUP/%d_%t_%s_%p.dbf' database  ;

The following example creates a level 1 differential incremental backup (It backs up all blocks changed after the most recent incremental backup at level 1. If no level 1 found then backs up all blocks after most recent incremental backup at level 0):
rman> BACKUP INCREMENTAL LEVEL 1 DATABASE;
Another Example:
rman> backup incremental level 1 tag="Oracle_Backup_Level_1" format '/data/oracle/BACKUP/rman_LVL1_%d.%t.%p.%c' database;


Rmember:
A differential incremental backup tells RMAN to back up blocks that have changed since level n or lower. For example, if you take a level 1 differential backup, you will back up blocks that have changed since the previous level 1 backup. Differential backups are the default incremental backup mode.
A cumulative incremental backup instructs RMAN to back up blocks that have changed since level n-1 or lower. For example, if you take a level 1 cumulative backup, RMAN will back up blocks that have changed since the most recent level 0 backup.

Why all the choices? A differential backup takes less space and time to perform but requires more time to restore. It follows that a cumulative backup takes more space and time to perform but less time to restore. So it becomes a tradeoff issue; do you want to minimize your backup time or minimize your restore time? We prefer to minimize our restore time, and therefore, we use cumulative backups. For small databases, we recommend daily RMAN level 0 backups.


Incrementally Updated Backups

The RMAN incrementally updated backup feature is an efficient incremental backup routine. Changes from level 1 backups roll forward an image copy level 0 incremental backup, so that it includes all changes as of the SCN at which the level 1 incremental backup was created. Recovery of the updated level 0 incremental backup is faster because all changes from the level 1 incremental backup have already been applied.

The BACKUP FOR RECOVER OF COPY command specifies that an incremental backup should contain all changes since the SCN of a specified datafile copy (level 0 incremental backup) of your database. The following table explains which options to use with FOR RECOVER OF COPY to implement an incrementally updated backup strategy.

Using this feature all changes between the SCN of the original image copy and the SCN of the incremental backup are applied to the image copy, winding it forward to make the equivalent of a new database image copy without the overhead of such a backup.

BACKUP Option
Description
Example
FOR RECOVER OF COPY WITH TAG 'tag_name'
Use the TAG parameter to identify the level 0 incremental backup serving as the basis of the incremental backup.
If no level 0 datafile copy with the specified tag exists in either the current or parent database incarnation, then RMAN creates a level 0 datafile copy with the specified tag.
BACKUP INCREMENTAL LEVEL 1
  FOR RECOVER OF COPY
  WITH TAG 'incr_update'
  DATABASE;
FOR RECOVER OF COPY DATAFILECOPY FORMAT 'format'
Identifies the datafile copies to use as the basis for this incremental backup.
BACKUP INCREMENTAL LEVEL 1
  FOR RECOVER OF COPY
  DATAFILECOPY FORMAT
  '/disk2/df1.cpy'
  DATABASE;

To implement an incrementally updated backup strategy:
Start RMAN and connect to a target database.
Run the RECOVER COPY and BACKUP INCREMENTAL commands.
The following script, run on a regular basis, is all that is required to implement a strategy based on incrementally updated backups.

RECOVER COPY OF DATABASE WITH TAG 'incr_update';
BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'incr_update' DATABASE;


You can use the script bellow to take an Incrementally Updated Backup. This is a script which uses non-default RMAN location and this version doesn't use FRA (Flash Recovery Area) either.

# ########################################
# !/bin/bash
# Unix controls
export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
trap cleanup 1 2 3 15

cleanup()
{
echo "Caught CTRL-C Signal ... exiting script."
exit 1
}
# Oracle Variables
export ORACLE_SID=testdw0
export ORACLE_BASE=/usr/local/oracle
export ORACLE_HOME=/usr/local/oracle/product/10.2.0.1
export PATH=$PATH:${ORACLE_HOME}/bin

# RMAN INCREMENTALLY UPDATED BACKUPS (Window of 24 hours)
rman target=/ << EOF
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/oracle/backups/rman_bkps/atbckp_cntrlfile_testdw0%F';
run {
ALLOCATE CHANNEL RMAN_BACK_CH01 TYPE DISK FORMAT '/oracle/backups/rman_bkps/databasefiles_%d_%u_%s_%T';
CROSSCHECK BACKUP;
RECOVER COPY OF DATABASE with TAG 'testdw0_incr_update';
BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY with TAG 'testdw0_incr_update' DATABASE;
ALTER SYSTEM ARCHIVE LOG CURRENT;
BACKUP as compressed backupset ARCHIVELOG ALL format '/oracle/backups/rman_bkps/archivelogs_%d_%u_%s_%T' DELETE INPUT;
CROSSCHECK BACKUP;
DELETE NOPROMPT OBSOLETE;
DELETE NOPROMPT EXPIRED BACKUP;
RELEASE CHANNEL RMAN_BACK_CH01;
}
EXIT;
EOF
#########################################


Example backup strategy using incremental backups

A typical incremental backup cycle would be as follows:
        o. Sun night - level 0 backup performed
        o. Mon night - level 2 backup performed
        o. Tue night - level 2 backup performed
        o. Wed night - level 2 backup performed
        o. Thu night - level 1 backup performed  --> Contains all changes since Sunday
        o. Fri night - level 2 backup performed
        o. Sat night - level 2 backup performed

If the database suffered a failure on Sat morning and this resulted in a restore operation, RMAN could recover to the point of failure by restoring the backups from Sunday, Thursday, and Friday. This is because Thursdays level 1 backup contains all changes since Sunday, and Friday's level 2 backup contains all changes since Thursday. Whether the database could be completely recovered would depend on whether archive logging is enabled.
 
Example:
Following scenario assumes that you want to take one full database once a week, doing every day incrementals.
Backup cycle starts on friday, i.e., every friday full backup, and on other days incrementals.

# Section 1 - Start script for backup cycle
# -------------------------------------------
# The following commands are run each Friday to start the backup cycle.
# The steps are:
# - Take an incremental level 0 backup of the database. A level 0 backup is a complete backup of the entire file which can be used as the basis for a subsequent incremental backup.
# - Backup all archivelogs that have not already been backed up.
# - Delete on-disk archivelogs older than seven days.
run{
backup
incremental level 0 tag = "Weekly_Full_Database" format 'full_inc1_%u' fileperset 4 database plus archivelog;
--backup archivelog all;
delete archivelog until time 'sysdate-4';
delete nopromp obsolete;
}

If the above backup fails for any resaon, you can use the NOT BACKED UP SINCE option on the BACKUP command to continue from the point of failure. The small value of FILESPERSET is good for restartable backups. However you should note that smaller FILESPERSET produces more backup sets.

Use the following commands to re-start backups after a failure:
BACKUP INCREMENTAL LEVEL 0 DATABASE FILESPERSET 4
NOT BACKED UP SINCE TIME 'SYSDATE-1';
BACKUP ARCHIVELOG ALL;
DELETE ARCHIVELOG UNTIL TIME 'SYSDATE-7';


# Section 2 - script for other days of backup cycle
# -----------------------------------------------------
# The following commands can be run from Saturday through Thursday to take cumulative incremental backups.
# The steps are:
# - delete incrementals on disk that were taken before 7 days.
# - take differential incremental backup of complete database.
# - copy incrementals to tape.
# - backup all archiveogs that are not backed up.
# - deletes any copies of archivelog on disk older than 7 days.
run{
delete backup completed before 'sysdate-7' ;
backup incremental level 1 cumulative tag="Incremental_Oracle_Backup" filesperset 4 database plus archivelog;
--backup archivelog all;
delete archivelog until time 'sysdate-7';
}


As in section 1, you can use the NOT BACKED UP SINCE option with the BACKUP command to continue from the point of failure.
Use the following commands to re-start backups after a failure:
DELETE BACKUP COMPLETED BEFORE 'SYSDATE-7' DEVICE TYPE DISK;
BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DEVICE TYPE DISK DATABASE FILESPERSET 4
NOT BACKED UP SINCE TIME 'SYSDATE -1 ';
BACKUP BACKUPSET ALL; # copies backups from disk to tape
BACKUP ARCHIVELOG ALL;
DELETE ARCHIVELOG UNTIL TIME 'SYSDATE-7';




Backup and Recovery Best Practices (ML Note 388422.1)
1. Turn on block checking.
REASON: The aim is to detect, very early the presence of corrupt blocks in the database.  This has a slight performance overhead, but Checksums allow Oracle to detec early  corruption caused by underlying disk, storage system, or I/O system problems.
SQL> alter system set db_block_checking = true scope=both;


2. Turn on block tracking when using RMAN backups
REASON: This will allow RMAN to backup only those blocks that have changed since the last full  backup, which will reduce the time taken to back up, so less blocks will be backed up.
SQL> alter database enable block change tracking using file ‘/u01/oradata/ora1/change_tracking.f’;


3. Duplex log groups and members and have more than one archive log dest.
REASON: If an archivelog is corrupted or lost, by having multiple copies in multiple locations,  the other logs will still be available and could be used.
If an online log is deleted or becomes corrupt, you will have another member that can be used to recover if required.
SQL> alter system set log_archive_dest_2='location=/new/location/archive2' scope=both;
SQL> alter database add logfile member '/new/location/redo21.log' group 1;


4. Use the 'check logical' parameter when backing up the database 
REASON: This will cause RMAN to check for logical corruption within a block as well as the normal head/tail checksumming. This is the best way to ensure that you will get a good backup.
RMAN> backup check logical database plus archivelog delete input;


5. Test your backup.
REASON: This will do everything except actually restore the database. This is the best method to determine if your backup is good and usable.
# Check if database can be restored
RMAN> restore database validate;
or a Full Validation:
run
{
restore database validate;
restore archivelog from time 'sysdate-1' validate;
restore controlfile validate;
restore spfile validate;
}


# Check if tablespace is restorable
RMAN> restore tablespace read_only_tablespace_name validate ;


6. Have each datafile in a single backup piece
REASON: When doing a partial restore RMAN must read through the entire piece to get the datafile/archivelog requested. The smaller the backup piece the quicker the restore can complete. This is especially relevent with tape backups of large databases or where the restore is only on individual / few files.  However you should note that smaller FILESPERSET produces more backup sets.
RMAN> backup database filesperset 1 plus archivelog delete input;


7. Maintain your RMAN catalog/controlfile
REASON: Choose your retention policy carefully. Make sure that it compliments your tape subsystem retention policy, requirements for backup recovery strategy. If not using a catalog, ensure that your controlfile record keep time instance parameter matches your retention policy.
#This will keep 21 days of backup records.
SQL> alter system set control_file_record_keep_time=21 scope=both;


8. Run Regular Catalog Maintenance
REASON: Delete obsolete will remove backups that are outside your retention policy. If obsolete backups are not deleted, the catalog will continue to grow until performance becomes an issue.
RMAN> delete obsolete;

REASON: crosschecking will check that the catalog/controlfile matches the physical backups. If a backup is missing, it will set the piece to 'EXPIRED' (missing), so when a restore is started, that it will not be eligible, and an earlier backup will be used. To remove the expired backups from the catalog/controlfile use the delete expired command.
RMAN> crosscheck backup;
RMAN> delete expired backup;

The CROSSCHECK command has many parameters and formats:
RMAN> crosscheck backup of database;      --Crosscheck all obsolete datafile backups, arc log backups and control file backups
RMAN> crosscheck backup of archivelog all;
RMAN> crosscheck backup of controlfile;
RMAN> crosscheck backup completed after 'sysdate-7';     -- Check existence of pieces created in last 7 days
RMAN> crosscheck backup device type sbt;
RMAN> crosscheck backup of tablespace users;
RMAN> crosscheck archivelog all;     --Check existence of archivelogs


9. Prepare for loss of controlfiles.
set autobackup on
REASON: This will ensure that you always have an up to date controlfile available that has been taken at the end of the current backup not during.
RMAN> configure controlfile autobackup on;


10. Keep your Backup Logs
REASON: The backup log contains parameters for your tape access, locations on controlfile backups that can be utilised if complete loss occurs.


11. Test your recovery
REASON: During a recovery situation this will let you know how the recovery will go without actually doing it, and can avoid having to restore source datafiles again.
SQL> recover database test;


11. Do not specify 'delete all input' when backing up archivelogs
REASON: Delete all input' will backup from one destination then delete both copies of the archivelog where as 'delete input' will backup from one location and then delete what has been backed up. The next backup will back up those from location 2 as well as new logs from location 1, then delete all that are backed up. This means that you will have the archivelogs since the last backup available on disk in location 2 (as well as backed up once) and two copies backup up prior to the previous backup.


Scripting in RMAN
You can integrate RMAN into operating-system command scripts, either as a call to RMAN with a command file or with in-line RMAN scripts.
It is very easy to create and replace stored scripts with RMAN. E.g.

To save a script into the catalog you can use:
create script prod_full_backup {
 # backup the complete database to disk, backup all Arch Logs and delete them
  backup database plus archivelog tag="Full_Database"
  format '/data/archdata/DBbackup/%d_t%t_s%s_p%p';
  alter system archive log current;
  backup archivelog all delete all input;
}

To Drop it from RMAN just perform:
delete script  script_name;

To Execute it from RMAN just perform:
run {execute script prod_full_backup;}

To read the code from the Database you can run the following query:
select text from rc_stored_script_line where script_name='prod_full_backup';

It is possible to create a job command list in a flat file and call that script from the O/S command line as an RMAN option. E.g. to call scripts stored in a file called 'weekly_cold_backup':
    % rman <other RMAN options> cmdfile prod_full_backup

This UNIX script, for example, shows how to work around one of RMAN's current shortcomings - its inability to receive variables.
This script shows you how to pass variables from a UNIX Korn-shell script to RMAN.

#!/bin/ksh
# Back up single tablespace to disk or tape
# Process command-line args
export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
TargetConnStr=$1

RcvcatConnStr=$2
TapeOrDisk=$3
tablespace=$4
# need to do some parameter validation here!
${ORACLE_HOME}/bin/rman << EOF
connect target ${TargetConnStr}
connect rcvcat ${RcvcatConnstr}
run {
execute script alloc_${TapeOrDisk}_channel;
backup tablespace ${tablespace}
format '${tablespace}_%u';
execute script rel_${TapeOrDisk}_channel;
}
exit
EOF
if [ $? -ne 0 ]
then
 echo "RMAN failed to backup ${tablespace}"
else
 echo "${tablespace} backed up OK"
fi
 

Report & Listing commands

RMAN has a few maintenance commands, shown next.

report schema; Report datafiles structure of the DB in RMAN.
The names of all datafiles (permanent and temporary) and tablespaces for the target database at the specified point in time. If you use RMAN in a Data Guard environment, then you can report the schema for a specified DB_UNIQUE_NAME.
report obsolete;

report obsolete redundancy = 2;
report obsolete redundancy = 2 until time 'SYSDATE-14';
delete obsolete
Report backups that are obsolete. Full backups, datafile copies, and archived redo logs recorded in the RMAN repository that can be deleted because they are no longer needed according to the current retention policy.
Report backups that are obsolete because there are at least 2 more recent backups.
Report
backups that are obsolete because there are at least 2 more recent backups that are no more than 2 weeks old

Delete obsolete backups.
report need backup;
report need backup days 2 database;
report need backup days 2 tablespace system;


report unrecoverable;
Display a list of files that need to be backed up based on the retention policy.
To report on those datafiles that, if restored, would require application of two days (or more) worth of archived redo log files:

To generate the same report but only for SYSTEM tablespace datafiles.



Reports on all unrecoverable datafiles.
list incarnation;
list db_unique_name;
You can list all incarnations of a database. A new database incarnation is created when you open with the RESETLOGS option.
A database in a Data Guard environment is distinguished by its DB_UNIQUE_NAME initialization parameter setting. You can list all databases that have the same DBID.
list backup summary;
list backup;
list backup of database;
list backupset;
list backup by file;
list backup of archivelog all;
list backup of controlfile; 
Lists a summary of the Backups and Copies.
List all backup sets, copies, and proxy copies of a database, tablespace, datafile, archived redo log, control file, or server parameter file.
Lists backups of all files in database
List backup by set.
List backups by file.
List backuped files containing Arch Log Files.
List backuped files containing control files.
crosscheck backup; Determines whether a backup set and its related pieces still exist on media. If a backup piece exists in the location recorded in the control file of the target database or in the optional recovery catalog, its status is marked as AVAILABLE. If it is not at the specified location, it is marked as EXPIRED.

delete expired backup;

This command deletes the physical files associated with backup sets and datafile copies, updates their status in the control file, and removes their information from the optional recovery catalog (if one is used).
In Oracle backups are flagged as EXPIRED if they cannot be found at their recorded location. Deletion of EXPIRED backups removes their information from the control file and from the optional recovery catalog (if one is used).




For more information on the meaning of each column in the LIST command, please click HERE


report schema;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name DIE

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    730      SYSTEM               ***     C:\APP\ORADATA\DIE\SYSTEM01.DBF
2    610      SYSAUX               ***     C:\APP\ORADATA\DIE\SYSAUX01.DBF
3    925      UNDOTBS1             ***     C:\APP\ORADATA\DIE\UNDOTBS01.DBF
4    5        USERS                ***     C:\APP\ORADATA\DIE\USERS01.DBF
5    100      EXAMPLE              ***     C:\APP\ORADATA\DIE\EXAMPLE01.DBF
6    1800     FG_DATA              ***     C:\APP\ORADATA\DIE\FG_DATA01.DBF
7    20       FG_INDX              ***     C:\APP\ORADATA\DIE\FG_INDX01.DBF

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767       C:\APP\ORADATA\DIE\TEMP01.DBF


report obsolete;

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
-------------------- ------ ------------------ --------------------
Archive Log          1      05-AUG-11          C:\APP\ORADATA\DIE\ARCH\ARC0000000248_0748807412.0001
Archive Log          2      05-AUG-11          C:\APP\ORADATA\DIE\ARCH\ARC0000000249_0748807412.0001
Archive Log          3      24-AUG-11          C:\APP\ORADATA\DIE\ARCH\ARC0000000270_0748807412.0001
Archive Log          4      24-AUG-11          C:\APP\ORADATA\DIE\ARCH\ARCH_0001_0000000271_748807412.ARC
Backup Set           5      24-AUG-11
  Backup Piece       5      24-AUG-11          C:\APP\RMAN_BACKUP\RMAN_BACKUP_CONTROLFILE_DIE_C-311636530-20110824-00_CTL
Backup Set           7      24-AUG-11
  Backup Piece       7      24-AUG-11          C:\APP\RMAN_BACKUP\ARCH_LOGS_06MKQ5OG
Backup Set           8      24-AUG-11
  Backup Piece       8      24-AUG-11          C:\APP\RMAN_BACKUP\ARCH_LOGS_07MKQ5OG
Backup Set           10     24-AUG-11
  Backup Piece       10     24-AUG-11          C:\APP\RMAN_BACKUP\CONTROLFILE_0AMKQ5ON



report unrecoverable;

Report of files that need backup due to unrecoverable operations
File Type of Backup Required Name
---- ----------------------- -----------------------------------
5    full or incremental     C:\APP\ORADATA\DIE\EXAMPLE01.DBF
6    full or incremental     C:\APP\ORADATA\DIE\FG_DATA01.DBF



list incarnation;
List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       DIE      311636530        PARENT  1          02-APR-10
2       2       DIE      311636530        CURRENT 940976     18-APR-11



Could I restore, if I needed to?

restore database validate;

or a Full Validation:
run
{
restore database validate;
restore archivelog from time 'sysdate-1' validate;
restore controlfile validate;
restore spfile validate;
}



RMAN has extensive listing and reporting functionality allowing you to monitor you backups and maintain the recovery catalog. Here are a few useful commands:

Lists
The LIST command will query the RMAN repository (control file or recovery catalog) to produce a record of its contents. Use this command to obtain data about:
        * RMAN-generated files, that is, backup sets, proxy copies, and image copies.
        * Specified objects contained in the RMAN-generated files, that is, archived logs, datafiles, and control files.
        * Incarnations of a specified database or of all databases known to the recovery catalog

The primary purpose of the LIST command is to determine which backups or copies are available. Note that only backups and copies that completed successfully are stored in the repository.
RMAN records the output of the LIST command to either standard output or the message log, but not to both at the same time. You can also control how the output is organized as well as the level of detail in the output.
The LIST command displays the same files that the CROSSCHECK and DELETE commands operate on. Consequently, you can issue LIST to see what is in the repository, and then run CROSSCHECK to ensure that these files exist on disk or tape.
You can control how the output is displayed by using the BY BACKUP and BY FILE options of the LIST command and choosing between the SUMMARY and VERBOSE options.
Let's see a couple of examples:

rman> list backup summary;     # lists a summary of the backup sets
List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
1       B  F  A DISK        24-AUG-11       1       1       NO         TAG20110824T143207
3       B  F  A DISK        24-AUG-11       1       1       NO         TAG20110824T143207
4       B  F  A DISK        24-AUG-11       1       1       NO         TAG20110824T143207
5       B  F  A DISK        24-AUG-11       1       1       NO         TAG20110824T143450
6       B  A  A DISK        24-AUG-11       1       1       NO         TAG20110824T143456
7       B  A  A DISK        24-AUG-11       1       1       NO         TAG20110824T143456
8       B  A  A DISK        24-AUG-11       1       1       NO         TAG20110824T143456
9       B  A  A DISK        24-AUG-11       1       1       NO         TAG20110824T143456
10      B  F  A DISK        24-AUG-11       1       1       NO         CONTROL_FILE
11      B  F  A DISK        24-AUG-11       1       1       NO         TAG20110824T143506
12      B  F  A DISK        24-AUG-11       1       1       NO         TAG20110824T144246
13      B  F  A DISK        24-AUG-11       1       1       NO         FULL_DATABASE
14      B  F  A DISK        24-AUG-11       1       1       NO         TAG20110824T144246
15      B  F  A DISK        24-AUG-11       1       1       NO         TAG20110824T144556

Status:
A: Available
U: Unavailable –RMAN doesn't knows where it is
X: Expired –RMAN can't find it



rman> list backup;                  # lists backup sets, images copies, backup pieces, and proxy copies

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1       Full    441M       DISK        00:01:23     15-OCT-04     
        BP Key: 1   Status: AVAILABLE  Compressed: NO  Tag: TAG20041015T175207
        Piece Name: /export/rman/rman_CUDDLE_01g2k8m8_1_1.bus
  List of Datafiles in backup set 1
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 396472     15-OCT-04 /u02/oradata/cuddle/system01.dbf
  2       Full 396472     15-OCT-04 /u02/oradata/cuddle/undotbs01.dbf
  3       Full 396472     15-OCT-04 /u02/oradata/cuddle/sysaux01.dbf
  4       Full 396472     15-OCT-04 /u02/oradata/cuddle/users01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2       Full    2M         DISK        00:00:02     15-OCT-04     
        BP Key: 2   Status: AVAILABLE  Compressed: NO  Tag: TAG20041015T175207
        Piece Name: /export/rman/rman_CUDDLE_02g2k8ou_1_1.bus
  Controlfile Included: Ckp SCN: 396502       Ckp time: 15-OCT-04
  SPFILE Included: Modification time: 15-OCT-04

Here we can see 2 backup pieces:
The first contains the datafiles for the "cuddle" database, is 441MB in side, was made to disk and took 1 minute and 23 seconds to make. We aslo see the peices tag (notice the tag is the same for both peices). Notice also that each datafile in the backup peice has a Checkpoint System Change Number (Ckp SCN) associated with it (SCNs were covered earlier).
The second peice is 2MB in size, took 2 seconds to backup and was done to disk. Notice that the second peice lists the modification time for the SPFILE and the SCN for the Controlfile.



rman> list backup of database;                               # Show all backup details for backup sets
List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
9       Full    376.00K    DISK        00:00:01     25-APR-08
        BP Key: 3   Status: AVAILABLE  Compressed: NO  Tag: TAG20080425T195459
        Piece Name: C:\ORACLE\RMAN_BACKUP\BACKUP\BACKUP_DB_DIE_S_9_P_1_T_652996499
  List of Datafiles in backup set 9
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  4       Full 3459109    25-APR-08 C:\ORACLE\PRODUCT\10.2.0\ORADATA\DIE\USERS01.DBF

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
11      Full    880.00M    DISK        00:02:08     25-APR-08
        BP Key: 5   Status: AVAILABLE  Compressed: NO  Tag: TAG20080425T195748
        Piece Name: C:\ORACLE\RMAN_BACKUP\BACKUP\BACKUP_DB_DIE_S_11_P_1_T_652996669
  List of Datafiles in backup set 11
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 3459225    25-APR-08 C:\ORACLE\PRODUCT\10.2.0\ORADATA\DIE\SYSTEM01.DBF
  2       Full 3459225    25-APR-08 C:\ORACLE\PRODUCT\10.2.0\ORADATA\DIE\UNDOTBS01.DBF
  3       Full 3459225    25-APR-08 C:\ORACLE\PRODUCT\10.2.0\ORADATA\DIE\SYSAUX01.DBF
  4       Full 3459225    25-APR-08 C:\ORACLE\PRODUCT\10.2.0\ORADATA\DIE\USERS01.DBF
  5       Full 3459225    25-APR-08 C:\ORACLE\PRODUCT\10.2.0\ORADATA\DIE\FG_DATA.DBF


rman> list expired backup;      # lists backups not found during crossckeck

rman> list backup by file;      # lists in which place is each file from the database
List of Datafile Backups
========================

File Key     TY LV S Ckp SCN    Ckp Time  #Pieces #Copies Compressed Tag
---- ------- -  -- - ---------- --------- ------- ------- ---------- ---
1    11      B  F  A 3459225    25-APR-08 1       1       NO         TAG20080425T195748
2    11      B  F  A 3459225    25-APR-08 1       1       NO         TAG20080425T195748
3    11      B  F  A 3459225    25-APR-08 1       1       NO         TAG20080425T195748
4    11      B  F  A 3459225    25-APR-08 1       1       NO         TAG20080425T195748
     9       B  F  A 3459109    25-APR-08 1       1       NO         TAG20080425T195459
5    11      B  F  A 3459225    25-APR-08 1       1       NO         TAG20080425T195748

List of Archived Log Backups
============================

Thrd Seq     Low SCN    Low Time  BS Key  S #Pieces #Copies Compressed Tag
---- ------- ---------- --------- ------- - ------- ------- ---------- ---
1    214     3351077    24-APR-08 10      A 1       1       NO         FULL_DATABASE
1    215     3358167    24-APR-08 10      A 1       1       NO         FULL_DATABASE
1    216     3360062    24-APR-08 10      A 1       1       NO         FULL_DATABASE
1    217     3360407    24-APR-08 10      A 1       1       NO         FULL_DATABASE
1    218     3392731    25-APR-08 10      A 1       1       NO         FULL_DATABASE
1    219     3409205    25-APR-08 10      A 1       1       NO         FULL_DATABASE
1    220     3425916    25-APR-08 10      A 1       1       NO         FULL_DATABASE
1    221     3435770    25-APR-08 10      A 1       1       NO         FULL_DATABASE
1    222     3449518    25-APR-08 10      A 1       1       NO         FULL_DATABASE
1    223     3459186    25-APR-08 13      A 1       1       NO         FULL_DATABASE

List of Control File Backups
============================

CF Ckp SCN Ckp Time  BS Key  S #Pieces #Copies Compressed Tag
---------- --------- ------- - ------- ------- ---------- ---
3459462    25-APR-08 12      A 1       1       NO         TAG20080425T195748
List of SPFILE Backups
======================

Modification Time BS Key  S #Pieces #Copies Compressed Tag
----------------- ------- - ------- ------- ---------- ---
25-APR-08         12      A 1       1       NO         TAG20080425T195748
24-APR-08         7       A 1       1       NO         TAG20080424T112235



rman> list copy of archivelog all;                          # lists all archived redo logs and copies of logs
List of Archived Log Copies
Key     Thrd Seq     S Low Time  Name
------- ---- ------- - --------- ----
1       1    214     A 24-APR-08 C:\ORACLE\PRODUCT\10.2.0\ADMIN\DIE\ARCHIVE\ARCH_1_214_640688428.DBF
2       1    215     A 24-APR-08 C:\ORACLE\PRODUCT\10.2.0\ADMIN\DIE\ARCHIVE\ARCH_001_00215_640688428.ARC
3       1    216     A 24-APR-08 C:\ORACLE\PRODUCT\10.2.0\ADMIN\DIE\ARCHIVE\ARCH_001_00216_640688428.LOG
4       1    217     A 24-APR-08 C:\ORACLE\PRODUCT\10.2.0\ADMIN\DIE\ARCHIVE\ARCH_001_00217_640688428.LOG
5       1    218     A 25-APR-08 C:\ORACLE\PRODUCT\10.2.0\ADMIN\DIE\ARCHIVE\ARCH_001_00218_640688428.LOG
6       1    219     A 25-APR-08 C:\ORACLE\PRODUCT\10.2.0\ADMIN\DIE\ARCHIVE\ARCH_001_00219_640688428.LOG
7       1    220     A 25-APR-08 C:\ORACLE\PRODUCT\10.2.0\ADMIN\DIE\ARCHIVE\ARCH_001_00220_640688428.LOG
8       1    221     A 25-APR-08 C:\ORACLE\PRODUCT\10.2.0\ADMIN\DIE\ARCHIVE\ARCH_001_00221_640688428.LOG
9       1    222     A 25-APR-08 C:\ORACLE\PRODUCT\10.2.0\ADMIN\DIE\ARCHIVE\ARCH_001_00222_640688428.LOG
10      1    223     A 25-APR-08 C:\ORACLE\PRODUCT\10.2.0\ADMIN\DIE\ARCHIVE\ARCH_001_00223_640688428.LOG


More List Commands:

list backup of archivelog all;                     # List backuped files containing Arch Log Files.
list backup of controlfile;                           # lists all control file backups
report obsolete;
delete obsolete or delete noprompt obsolete;
  # Delete un-necessary backups. This command deletes backups based on the retention policy


To restrict by object, use list copy or list backup with the of listObjList condition. For example, enter:
list copy of datafile '/oracle/dbs/tbs_1.f';     # lists copy of specified datafile
list backup of tablespace SYSTEM;             # lists all backups of SYSTEM tablespace

You can also restrict your search by specifying a combination of tag, device type, filename pattern, or time options. For example, enter:
list backup tag 'weekly_full_db_backup';                        # by tag
list copy of datafile '/oracle/dbs/tbs_1.f' type 'sbt_tape';  # by type
list backup like '/oracle/backup/tbs_4%';                         # by filename pattern
list backup of archivelog until time 'SYSDATE-30';        # by time
list copy of datafile 2 completed between '10-DEC-1999' and '17-DEC-1999'; i
 

Report objects that needs backup:
1. If necessary, issue crosscheck commands to update the status of backups and change ... crosscheck commands to update the status of image copies (if you want to specify image copies by primary key, issue a list command to obtain the keys). Following is a possible crosscheck session:
# must allocate maintenance channel for crosscheck
allocate channel for maintenance type disk;
crosscheck backup;                                                                                         # checks RMAN backups on configured devices
change datafile copy 100,101,102,103,104,105,106,107 crosscheck;           # specified by key
change archivelog copy 50,51,52,53,54 crosscheck;                                     # specified by key
release channel;

2. Use the need backup option to identify which datafiles need a new backup, restricting the report by a threshold number of days or incremental backups. RMAN considers any backups older than the days parameter value as needing a new backup because backups require days worth of archived redo logs for recovery.
For example, enter:
report need backup days = 7 database;                             # needs at least 7 days of logs to recover
report need backup days = 30 tablespace system;
report need backup days = 14 datafile '/oracle/dbs/tbs_5.f';

You can also specify the incremental parameter. If complete recovery of a datafile requires more than the specified number of incremental backups, then RMAN considers it in need of a new backup. For example, enter:
report need backup incremental = 1 database;
report need backup incremental = 3 tablespace system;
report need backup incremental = 5 datafile '/oracle/dbs/tbs_5.f';
 

To report on backups that are obsolete:
1. Perform crosscheck if neccesary (see previous step 1).
2. Use the obsolete option to identify which backups are obsolete because they are no longer needed for recovery. The redundancy parameter specifies the minimum level of redundancy considered necessary for a backup or copy to be obsolete. If you do not specify the parameter, redundancy defaults to 1.
A datafile copy is obsolete if at least integer more recent backups of this file exist; a datafile backup set is obsolete if at least integer more recent backups or image copies of each file contained in the backup set exist. For example, enter:
# lists backups or copies that have at least 2 more recent backups or copies
report obsolete redundancy = 2;

Use the untilClause to use make the redundancy check for backups sets or copies that are more recent, but not later than the specified time, SCN, or log sequence number:
# obsolete if there are at least 2 copies/backups that are no more than 2 weeks old
report obsolete redundancy = 2 until time 'SYSDATE-14';
report obsolete until scn 1000;
report obsolete redundancy = 3 until logseq = 121 thread = 1;

3. Use the orphan option to list which backups and copies are unusable because they belong to a incarnation that is not a direct predecessor of the current incarnation:
report obsolete orphan;

4. Examine the report and report those backups that are obsolete.
report obsolete;
 
5. To delete the obsolete:
delete obsolete;

To report on backups that are unrecoverable
report unrecoverable database;    # examines all datafiles
 
# Show datafiles that connot currently be recovered
report unrecoverable database;
report unrecoverable tablespace 'USERS';

 
 

Recovere from Failure (with Examples)
Backing up the database is the easy part. The real challenge begins when the "ORA-01113 file needs recovery" message flashes on your console. An instance can be started up from SQL Plus or RMAN using the startup command. Three variants of the startup command, relevant to the present discussion are:

  • startup nomount: The instance is started up - i.e. the required memory structures are associated with the pre-existing Oracle process. At this point the instance is not associated with any database.
  • startup mount: The instance is started up and the database control file is read. At this point Oracle knows the locations of all files that make up the database. However, the database is not open. It is possible to go from the nomount state to the mount state using the SQL command "alter database mount".
  • startup: The instance is started up, the control file is read and the database opened for general use. At this point the instance is fully associated with the database. It is possible to go from nomount / mount to the open state using the SQL command "alter database open"

For Information on how to use Recovery Manager (RMAN) to back up and restore Oracle Real Application Clusters (RAC) databases click HERE.

The RESTORE and RECOVER commands are the most common recovery-related RMAN commands. However, there are several additional recovery- related commands:
    * RESTORE – Restore any of the following files: SPFILE, Control file, datafile, archivelog, image copy.
    * RECOVER – Apply incremental backups and/or redo to datafiles to bring the file up to the most recent commit (complete recovery) or up to the specified log sequence number, SCN or time (point-in-time recovery).
    * SET NEWNAME – Restore files to an alternate location.
    * SWITCH – Update the control file to reflect new file location.
    * SET UNTIL – Use in point-in-time (PIT) recoveries to stop recovery at log sequence, SCN or time.
    * ALTER DATABASE OPEN RESETLOGS – Use in PIT recoveries to open database and reset the log sequence number back to zero. This creates a new “incarnation” of the database and prevents logs from the old incarnation from being applied to this new incarnation. The RESETLOGS option is required if complete recovery was not used. (It is notable that when using RMAN with a recovery catalog, recovery across a resetlog operation is supported. This is because the RMAN catalog knows about earlier incarnations of the database and knows which logs belong to those incarnations. Refer to the Oracle Recovery Manager User’s Guide, Chapter 10, “Recovering Through a Resetlogs Operation” for more information.)
    * RESET DATABASE – Record the new incarnation of database in recovery catalog. Used after OPEN RESETLOGS.


Restore and Recovery process is dependent on the type of failure.
Generally RMAN automates much of the process. There are still some times that you might have to perform some manual intervention.
Generally the commands you will use are:

  • Startup mount – Mount the database
  • Restore database – Restores datafiles
  • Recover database – Recovers database, also restores archived redo logs as required.
  • Alter database open {resetlogs} – Opens the database.


Recovery Methods


In general there are three steps involved in restoring files:

  1. Ensure that the target database is started in the appropriate mode for the restoration operation. For lost control files, this will be nomount mode. If the entire database needs to be restored, this will be mount mode. If datafiles that don't belong to the SYSTEM tablespace are damaged, you have the option of keeping the database open and taking only the tablespace(s)/datafile(s) that needs to be restored offline.
  2. Start RMAN and connect to the target and recovery catalog if one is being used.
  3. Run the appropriate RMAN RESTORE command to bring back required files. The requested files and the appropriate archived redo log files will be restored.

Once the necessary files are restored, you need to recover your database and open it for use. You can recover the database from either RMAN or SQL*Plus.


Preview Files to Restore
One nice feature is that you can "see" what files are needed for Recovery. If you have ever wondered exactly what backup files or image copies RMAN will use to perform restoration, Oracle offers the RESTORE ... PREVIEW command set to show exactly what backup pieces or image copies RMAN plans to utilize. For example, if I wanted to explore exactly what RMAN will choose if I want to restore the database's SYSTEM tablespace, from within an RMAN session, I can issue the RESTORE DATAFILE 1 PREVIEW; .
Example:  What files will RMAN use during a RESTORE operation?

-- NOTE: these commands should be issued from within an active rman session
spool log to c:\restoresummary.txt;
restore datafile 1 preview;                                #
# show what files will be used to restore the system tablespace's datafile
restore tablespace USERS preview;                 #
# show what files will be used to restore a specific tablespace
restore database preview summary;                  #
# show a summary for a full database restore
spool log off;                                                    #
# close the log file

-- The resulting output:
Starting restore at 26-APR-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK

List of Backup Sets
===================
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
22      Full    880.47M    DISK        00:02:06     25-APR-08     
        BP Key: 13   Status: AVAILABLE  Compressed: NO  Tag: TAG20080425T204941
        Piece Name: C:\ORACLE\RMAN_BACKUP\BACKUP\BACKUP_DB_DIE_S_22_P_1_T_652999781
  List of Datafiles in backup set 22
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  4       Full 3462522    25-APR-08 C:\ORACLE\PRODUCT\10.2.0\ORADATA\DIE\USERS01.DBF
using channel ORA_DISK_1


List of Archived Log Copies
Key     Thrd Seq     S Low Time  Name
------- ---- ------- - --------- ----
14      1    227     A 25-APR-08 C:\ORACLE\PRODUCT\10.2.0\ADMIN\DIE\ARCHIVE\ARCH_001_00227_640688428.LOG
Media recovery start SCN is 3462522
Recovery must be done beyond SCN 3462522 to clear data files fuzziness
Finished restore at 26-APR-08

RMAN>
Starting restore at 26-APR-08
using channel ORA_DISK_1


List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
22      B  F  A DISK        25-APR-08       1       1       NO         TAG20080425T204941


List of Archived Log Copies
Key     Thrd Seq     S Low Time  Name
------- ---- ------- - --------- ----
14      1    227     A 25-APR-08 C:\ORACLE\PRODUCT\10.2.0\ADMIN\DIE\ARCHIVE\ARCH_001_00227_640688428.LOG
Media recovery start SCN is 3462522
Recovery must be done beyond SCN 3462522 to clear data files fuzziness
Finished restore at 26-APR-08

CASE 2 - Recovery from Missing or Corrupted Datafile(s) or Tablespace or a non-backed up Datafile:

Case A = One of the Datafiles or Tablespace is Lost, DB is UP, Valid Backups exist.
Note that if it is the system tablespace datafiles to be restored, the database must be closed. It is not possible to offline the system tablespace.

In this scenario, the database is already in the MOUNT or OPEN state before the RMAN session is initiated.
If the database is not mounted, you should issue a "startup mount" command before attempting to restore the missing datafile.
The database must be mounted before any datafile recovery can be done.

If the database is already open when datafile corruption is detected, you can recover the datafile without shutting down the database.
The only additional step is to take the relevant tablespace offline before starting recovery.


Example, you get the following error message:
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-00376: file 4 cannot be read at this time
ORA-01110: data file 4: 'D:\ORACLE_DATA\DATAFILES\ORCL\USERS01.DBF'


The error message tells us that file# 4 is missing. The DB is in mount or opened state, thus, the database control file, which is also the RMAN repository can be accessed by the instance and by RMAN. We now recover the missing file using RMAN.
-- The following query may help you to identify the file that needs recovery:
select rf.file#, d.name, rf.error from v$recover_file rf, v$datafile d where rf.file#=d.file#;


Check if we have a Backup of that datafile or tablespace:
RMAN> list backup of datafile 4;
or
RMAN> list backup of tablespace USERS;

Take the datafile(s)  Offline
RMAN> alter tablespace USERS offline immediate;
or
RMAN> alter database datafile  'D:\ORACLE_DATA\DATAFILES\ORCL\USERS01.DBF' offine;


Restore and recover the missing datafile
(s) or Tablespaces - RMAN applies all logs automatically
RMAN> restore tablespace USERS;
RMAN> recover tablespace USERS;
or
RMAN> restore datafile 4;
RMAN> recover datafile 4;
or
RMAN> restore datafile 'D:\ORACLE_DATA\DATAFILES\ORCL\USERS01.DBF';
RMAN> recover datafile'D:\ORACLE_DATA\DATAFILES\ORCL\USERS01.DBF'

If you want to restore to a different location, uncomment the following command.
#
RMAN> SET NEWNAME FOR DATAFILE 4 TO '/newdirectory/new_filename.dbf';
or
#
RMAN> SET NEWNAME FOR DATAFILE '/old_dir/old_filename.dbf' TO '/newdirectory/new_filename.dbf';


--Online recovered tablespace or Datafile
RMAN> alter tablespace USERS online;
or
RMAN> alter database datafile  'D:\ORACLE_DATA\DATAFILES\ORCL\USERS01.DBF' online;


--
Open database for general use (if not already opened)
RMAN> alter database open;


IMPORTANT NOTE on READ ONLY TABLESPACES
If the tablespace to recover is READ ONLY, use the CHECK READONLY option during database restore
By default "restore database" and "recover database" will ignore tablespaces in read-only mode.
So you will use:
RMAN> restore database check readonly;
RMAN> recover database check readonly;

IMPORTANT NOTE on TEMP TABLESPACE
As tempfiles aren't checkpointed, we don't need to back them up. We can recreate them at any point after the database has been restored, recovered and opened.
Temporary datafiles that belong to locally managed temporary tablespaces are automatically re-created during database recovery. This eliminates the need to manually create temporary tablespaces after recovery.


IMPORTANT NOTE WHEN DB IS OPENED

If the DB was in "open" state, when you type the following:
RMAN> restore datafile 4;

You will get:
RMAN> restore datafile 4;

Starting restore at 08-OCT-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=379 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to /oradata/new_location/users01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/rmanbkp/full_s5kr9tuh_1_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 10/08/2009 16:24:31
ORA-19870: error reading backup piece /home/oracle/rmanbkp/full_s5kr9tuh_1_1
ORA-19573: cannot obtain exclusive enqueue for datafile 4

To avoid this error we must start database in mount mode, but what will happen when we simple execute 'shutdown immediate':
SQL> alter database datafile '/oradata/new_location/users01.dbf' offline drop;
SQL> shutdown immediate;
SQL> startup mount;
RMAN> restore datafile 4;
RMAN> recover datafile 4;
SQL> alter tablespace users online;


CASE 3 -  Recovery from Block Corruption
It is possible to recover corrupted blocks using RMAN backups. This is a somewhat exotic scenario, but it can be useful in certain circumstances, as illustrated by the following example.
Here's the situation: a user connected to SQLPlus gets a data block corruption error when she queries a table. Corruption can be reported in alert logs, trace files or results of SQL queries..

To simulate a block corruption scenario, we will do the following:
•    Create a table in tablespace users
•    Identify the blocks belonging to that table
•    Corrupt all or some of those blocks using the Unix dd command.
•    Flush the buffer cache to ensure we read blocks from disk and not from memory(buffer cache)
•    Verify block corruptions from V$DATABASE_BLOCK_CORRUPTION

create table mytab tablespace users as select * from tab;
select count(*) from mytab;

Now on UNIX:
$ dd of=/u03/oradata/leventwo/users01.dbf bs=8192 seek=1027 conv=notrunc count=1 if=/dev/zero
1+0 records in
1+0 records out

alter system flush buffer_cache;

select count(*) from mytab;
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 1027)
ORA-01110: data file 4: '/u03/oradata/leventwo/users01.dbf'


select * from v$database_block_corruption;


     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
         4       1027          1                  0 ALL ZERO

Get the corruption details:
SELECT tablespace_name, segment_type, owner, segment_name
FROM  dba_extents
WHERE file_id = 4
and 1027  between block_id AND block_id +  blocks - 1;


We can either now recover the corrupted blocks using the command
BLOCKRECOVER DATAFILE 4, BLOCK 1027

Or, if there are a number of data blocks which are corrupted, we can issue a single command
BLOCKRECOVER CORRUPTION LIST

# The BLOCKRECOVER command can also be used to repair all corrupted blocks listed in V$BACKUP_CORRUPTION and V$COPY_CORRUPTION.
# These views are populated whenever an RMAN process peforms a complete scan of a file for the purpose of backing it up, such as with the BACKUP or COPY command.
# Use the following command to repair all blocks listed in the V$xxx_CORRUPTION views:
BLOCKRECOVER CORRUPTION LIST;

Since we know the file and block number, we can perform block level recovery using RMAN. Syntax:
blockrecover [datafile | device | tablespace] block (block#) ;


So we will execute the following command:
RMAN> blockrecover corruption list;

. Another Example:
--restore AND recover specific block (on this example, do it for 2 blocks at the same time!)
RMAN> blockrecover datafile 7 block 233,235 datafile 4 block 101;

We can also recover to a different location:
RMAN> run
{
allocate channel d1 type disk  format '/u01/backupdb/orcl_%T_%s_%p.bkp';
set newname for datafile 5 to  '/u03/oradb/oracle/example.dbf';
restore database ;
switch datafile  all;
recover database ;
}
Select * from v$datafile;

A couple of important points regarding block recovery:

  1. Block recovery can only be done using RMAN.
  2. The entire database can be open while performing block recovery.
  3. Check all database files for corruption. This is important - there could be other corrupted blocks. Verification of database files can be done using RMAN or the dbverify utility. To verify using RMAN simply do a complete database backup with default settings. If RMAN detects block corruption, it will exit with an error message pointing out the guilty file/block.



CASE 4 -  Recovery from Missing or Corrupted Redo Log Group:

The STATUS column of V$LOG view reflects the status of the log group:
CURRENT: The log group that is currently being written to by the log writer.
ACTIVE    : The log group is required for crash recovery and may or may not have been archived.
INACTIVE :The log group isn’t needed for crash recovery and may or may not have been archived.
UNUSED   : The log group has never been written to as it was recently created.

The STATUS column of V$LOGFILE reports the status of a online redo log file member :
INVALID:The log file member is inaccessible, or it has been recently created.
NULL:   The log file member is being used by the database.

Option 1: A member of multiplexed redo log group is lost and DB is UP
Solution(s):
Fix the media or Drop the affected Redo Log File and Create a new one in a different location

DIE/SYSTEM> select group#, status from v$log;
    GROUP# STATUS
---------- ----------------
         1 CURRENT
         2 INACTIVE
         3 INACTIVE
         4 INACTIVE

DIE/SYSTEM> select group#, status , substr(member,1,60) member from v$logfile;
    GROUP# STATUS  MEMBER
---------- ------- ------------------------------------------------------------
         1         C:\ORACLE\PRODUCT\10.2.0\ORADATA\DIE\REDO01.LOG
         2         C:\ORACLE\PRODUCT\10.2.0\ORADATA\DIE\REDO02.LOG
         3         C:\ORACLE\PRODUCT\10.2.0\ORADATA\DIE\REDO03.LOG
         4         C:\ORACLE\PRODUCT\10.2.0\ORADATA\DIE\REDO04a.LOG
         4         E:\ORADATA\DIE\REDO04b.LOG

If we lost , E:\ORADATA\DIE\REDO04b.LOG then Oracle keeps working but alert.log will report the errors (you can force the errors by performing a few log switches):

Errors in file c:\DIE\dump\DIE_lgwr_3724.trc:
ORA-00321: log 4 of thread 1, cannot update log file header
ORA-00312: online log 4 thread 1: 'E:\ORADATA\DIE\REDO04a.LOG'
ORA-27091: unable to queue I/O
ORA-27070: async read/write failed
OSD-04008: WriteFile() failure, unable to write to file
O/S-Error: (OS 1006) The volume for a file has been externally altered so that the opened file is no longer valid.
Sat Aug 14 20:43:50 2010
Errors in file c:\DIE\dump\db10g_lgwr_3724.trc:
ORA-00313: open failed for members of log group 4 of thread 1

Oracle marks the unavailable Redo Log File as INVALID and the DB remains UP:
alter system switch logfile;
alter system switch logfile;

select group#, status from v$log;
    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 INACTIVE
         3 INACTIVE
         4 CURRENT

select group#, status , substr(member,1,60) member from v$logfile;
    GROUP# STATUS  MEMBER
---------- ------- ------------------------------------------------------------
         1         C:\ORACLE\PRODUCT\10.2.0\ORADATA\DIE\REDO01.LOG
         2         C:\ORACLE\PRODUCT\10.2.0\ORADATA\DIE\REDO02.LOG
         3         C:\ORACLE\PRODUCT\10.2.0\ORADATA\DIE\REDO03.LOG
         4         C:\ORACLE\PRODUCT\10.2.0\ORADATA\DIE\REDO04a.LOG
         4 INVALID E:\ORADATA\DIE\REDO04b.LOG


Force a Log Switch:
Log Group Status = ACTIVE
Member Status = INVALID

alter system switch logfile;

select group#, status from v$log;
    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 INACTIVE
         3 CURRENT
         4 ACTIVE

select group#, status , substr(member,1,60) member from v$logfile;
    GROUP# STATUS  MEMBER
---------- ------- ------------------------------------------------------------
         1         C:\ORACLE\PRODUCT\10.2.0\ORADATA\DIE\REDO01.LOG
         2         C:\ORACLE\PRODUCT\10.2.0\ORADATA\DIE\REDO02.LOG
         3         C:\ORACLE\PRODUCT\10.2.0\ORADATA\DIE\REDO03.LOG
         4         C:\ORACLE\PRODUCT\10.2.0\ORADATA\DIE\REDO04a.LOG
         4 INVALID E:\ORADATA\DIE\REDO04b.LOG

SOLUTION
Drop and recreate the affected member to a different location
alter database drop logfile member 'E:\ORADATA\DIE\REDO04b.LOG';
alter database add logfile member 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\DIE\REDO04b.LOG' to group 4;


Option 2: Loosing INACTIVE Redo Log Files
Problem:
The only member of the redo log group is lost
Status of the Redo Log Group is INACTIVE
Database is Up

Simulating Media Failure
- Remove the Redo Log Group
- Perform log switches until Oracle tries to reuse the redo log file just deleted
- LGWR terminates the instance with ORA-00321 error as shown:
ORA-00321: log string of thread string, cannot update log file header

Solution
1)Fix the Media
Start the DB and Oracle perform crash recovery behind the scenes

2) If the redo log file is lost while the media remains available then clear the archived/unarchived log file
Identify whether redo log was archived or not by querying V$LOG view:
select group#, status , archived from v$log order by 1;
    GROUP# STATUS           ARC
---------- ---------------- ---
         1 CURRENT          NO
         2 INACTIVE         YES
         3 INACTIVE         YES

If the redo log file is archived then use CLEAR ARCHIVED command
alter database clear logfile group 3;

If the redo log file is not archived then use CLEAR UNARCHIVED command
alter database clear unarchived logfile group 3;
alter database open ;

3) If the media failure is permanent then drop and re-create the redo log group to a new location
alter database drop logfile member 'E:\ORADATA\DIE\REDO04b.LOG';
alter database add logfile member 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\DIE\REDO04b.LOG' to group 4;
alter database open;


Option 3: A multiplexed copy of the missing log is available.

If a redo log is missing, it should be restored from a multiplexed copy, if possible. This is the only way to recover without any losses. Here's an example, where I attempt to startup from SQLPlus when a redo log is missing:

SQL> startup
ORACLE instance started.
Database mounted.
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: 'D:\ORACLE_DATA\LOGS\ORCL\REDO03A.LOG'

Check their Status by:
select * from v$log;
select * from v$logfile;

To fix this we simply copy REDO03A.LOG from its multiplexed location on E: to the above location on E:. After copying the file, we issue an "alter database open" from the above SQLPlus session:

SQL> alter database open;
Database altered.

That's it - the database is open for use.

Option 4: All members of a log group lost.
In this case an incomplete recovery is the best we can do. We will lose all transactions from the missing log and all subsequent logs. We illustrate using the same example as above. The error message indicates that members of log group 1 are missing. We don't have a copy of this file, so we know that an incomplete recovery is required. The first step is to determine how much can be recovered. In order to do this, we query the V$LOG view (when in the mount state) to find the system change number (SCN) that we can recover to (Reminder: the SCN is a monotonically increasing number that is incremented whenever a commit is issued):

--The database should be in the mount state for v$log access
SQL> startup mount;

We will try to identify the last good SCN
select group#, sequence#, status, archived, first_change# from v$log order by sequence#;
    GROUP#  SEQUENCE# STATUS           ARC FIRST_CHANGE#
---------- ---------- ---------------- --- -------------
         2        348 INACTIVE         YES      11306138
         3        349 INACTIVE         YES      11306234
         1        350 CURRENT          NO       11306491        
  
The FIRST_CHANGE# is the first SCN stamped in the missing log. This implies that the last SCN stamped in the previous log is 11306491(FIRST_CHANGE#-1). This is the highest SCN that we can recover to. In order to do the recovery we must first restore ALL datafiles to this SCN, followed by recovery (also up to this SCN). This is an incomplete recovery, so we must open the database resetlogs after we're done. Here's a transcript of the recovery session (typed commands in bold, comments in italics, all other lines are RMAN feedback):

--Restore ENTIRE database to determined SCN
RMAN> restore database until scn 11306491;

--Recover database
RMAN> recover database until scn 11306491;

--open database with RESETLOGS (see comments below)
RMAN> alter database open resetlogs;

-- Re-create the redo log group to a different location
alter database rename file 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\DIE\REDO03.LOG' to 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\DIE\REDO03.LOG'

The following points should be noted:

  1. The entire database must be restored to the SCN that has been determined by querying v$log.
  2. All changes beyond that SCN are lost. This method of recovery should be used only if you are sure that you cannot do better. Be sure to multiplex your redo logs, and (space permitting) your archived logs!
  3. The database must be opened with RESETLOGS, as a required log has not been applied. This resets the log sequence to zero, thereby rendering all prior backups worthless. Therefore, the first step after opening a database RESETLOGS is to take a fresh backup. Note that the RESETLOGS option must be used for any incomplete recovery.

Check their Status by:
select * from v$log;
select * from v$logfile;



Case 6 - Database Point-in-Time Recovery
This scenario assumes that all initialization files and the current controlfile are in place and you want to recover to a point in time '2001-04-09:14:30:00' because at that time a TRUNCATE TABLE statement was erroneously run in production.
The 'SET UNTIL TIME' must match with the variable NLS_DATE_FORMAT.
Prior logging on RMAN set the NLS_DATE_FORMAT in the desired format. For example:
If unix ===> export NLS_DATE_FORMAT='YYYY-MM-DD:HH24:MI:SS';
If on windows nt ===> set this vaiable in the registery.

STARTUP MOUNT FORCE;
RUN
{
SET UNTIL TIME "TO_DATE('2001-04-09:14:30:00','yyyy-dd-mm:hh24:mi:ss')";
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS;
}

# You must take a new whole database backup after resetlogs since backups of previous incarnation are not easily usable.

If you can't mount it, you can try:
startup nomount;
run {
SET UNTIL TIME "TO_DATE('2001-04-09:14:30:00','yyyy-dd-mm:hh24:mi:ss')";
allocate channel  t1 type disk;

allocate channel  t2 type disk;
allocate channel  t3 type disk;
restore controlfile;
restore archivelog all;
alter database mount;
restore database;
recover database;
}
alter database open resetlogs;


Case 7 - Restore Archivelog (may be needed for a standby DB)
Look and check which logfile sequence numbers are in the gap.
Then login to RMAN and type:
RMAN> list backupset;
List of Archived Logs in backup set 40885
Thrd Seq Low SCN Low Time Next SCN Next Time
1 12870 132214214 31-OCT-05 132240966 31-OCT-05
1 12871 132240966 31-OCT-05 132291702 01-NOV-05
1 12872 132291702 01-NOV-05 132400078 01-NOV-05
1 12873 132400078 01-NOV-05 132504581 01-NOV-05
1 12874 132504581 01-NOV-05 132594119 01-NOV-05
1 12875 132594119 01-NOV-05 132683505 01-NOV-05


Let’s say I needed 12872-1874.
RMAN> run {
2> ALLOCATE CHANNEL c1 DEVICE TYPE DISK;
3> restore archivelog sequence 12872;
4> restore archivelog sequence 12873;
5> restore archivelog sequence 12874;
}
or
RMAN> restore archivelog from logseq=30 until logseq=50 thread=1;

You can also define the location where to restore these files:
RMAN> run {
Set archivelog destination to "d:\oracle\restore_archs";
Restore archivelog all;
}

As another option, you can recover them by specific dates:
$ rman target / admin admin/admin@oraprod
RMAN> restore ARCHIVELOG FROM TIME 'SYSDATE-1' UNTIL TIME 'SYSDATE';
or
RMAN> restore ARCHIVELOG FROM TIME "to_date('01/01/12 00:00:01','MM/DD/YY HH24:MI:SS') UNTIL TIME 'SYSDATE';



Case 8 - Recovering a Table from Hot backup
1- Since the table is dropped, we need to first recover the same using an interim TEST database as direct recovery is not possible. Let us consider the database name as TEST.
2- We need to identify the data files, control files and the log files of the existing production database. This can be obtained by the following:
select file_name,file_id,status,tablespace_name
from dba_data_files
order by tablespace_name desc;

Also, get the list of the Log files:
select * from v$logfile;

3- Once we obtain the details of the data files and the log files, the next step is to have the INTERIM or the TEMP database created for doing the recovery. For this, we may need to copy the existing INIT.ORA file of the production database from where we need to get the table and get the create control file script of that database. This can be obtained by using the following command or you can restore it from the previous successful online backup:
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

The control file script will look as follows:
CREATE CONTROLFILE REUSE DATABASE "<DB1>" NORESETLOGS
NOARCHIVELOG
-- SET STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 5
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 1588
LOGFILE
GROUP 1 'D:\ORACLE\ORA92\<DB1>\REDO01.LOG' SIZE 100M,
GROUP 2 'D:\ORACLE\ORA92\<DB1>\REDO02.LOG' SIZE 100M,
GROUP 3 'D:\ORACLE\ORA92\<DB1>\REDO03.LOG' SIZE 100M
-- STANDBY LOGFILE
DATAFILE
'D:\ORACLE\ORA92\<DB1>\SYSTEM01.DBF',
'D:\ORACLE\ORA92\<DB1>\UNDOTBS01.DBF',
'D:\ORACLE\ORADATA\<DB1>\IMPACT_01.DBF',
'D:\ORACLE\ORADATA\<DB1>\IMPACT2_01.DBF',
'D:\ORACLE\ORADATA\<DB1>\IMPINDEX_01.DBF',
'D:\ORACLE\ORA92\<DB1>\INDX01.DBF',
'D:\ORACLE\ORA92\<DB1>\TOOLS01.DBF',
'D:\ORACLE\ORA92\<DB1>\XDB01.DBF',
'D:\ORACLE\ORADATA\<DB1>\ORWTRC.DBS',
'C:\ORACLE\ORADATA\<DB1>\UNDOTBS02.DBF',
'D:\ORACLE\ORADATA\<DB1>\IMPINDEX_02.DBF'
CHARACTER SET WE8ISO8859P1
;

In the above script, change the Keywords REUSE to SET, NORESETLOGS to RESETLOGS and also the database name to a TEST database name.
Instead of this, you can also use the existing the control files that have been backed up, by just changing the location of the files in the INIT.ORA file so that it points to the new location where the files are restored.

4. Restore the Data files from the previous successful backup to a temporary location and also make the

5. Edit the INIT.ORA file to update the correct location of the restored control files, the database and the Instance name to the TEST database name.

6. Set the Oracle SID value for the new database. I.e. set ORACLE_SID=TEST. Then login to the TEST database as SYSDBA and execute the following command to start with the modified INIT.ORA file as below :
STARTUP pfile = ‘D:\ORACLE\ORA92\ADMIN\inittest.ora MOUNT

7. Check for the tablespace which contains the TABLE that needs to be recovered and drop or mark other tablespaces as OFFLINE which does not need recovery. By doing this, we can expedite the process of recovery. Execute the ALTER DATABASE RENAME FILE command for all the data files so that it has the new location updated in the database dictionary views.

8. Once the above step is done, recover the database by executing the following command :
SQL> RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE;
This will prompt you to apply for the Archive log files. So enter the location of the log files or just enter CANCEL which will recover the database.

9. Once the recovery is done, open the database with the RESETLOGS option as below :
SQL> ALTER DATABASE OPEN RESETLOGS;

10. Once this is done, we need to export the TABLE that we need to recover. Use the following command to do the same :
EXP username/password@connectionstring file=<file.dmp> owner=<schemaname> tables=<tab1,tab2> rows=y grants=y consistent=y log=test1.log
This will export the table that needs to be recovered.

11. Once we have the dump, IMPORT the table to the existing production database. Before that please disable all the constraints, triggers and the Referential constraints so that ROW Rejections can be avoided. The import can be done using the following command:
IMP username/password file=<file1.dmp> fromuser=<user1> touser=<user1> tables=<tab1,tab2> rows=y grants=y ignore=y buffer=100000 log=imp.log

12. Check the Import log for any errors and take corrective action based on the same.

13. Enable all the constraints, triggers and the Referential constraints.


Case 9- Restore Backup files from Disk Location other than Default
RMAN looks for the backupfiles to restore within its catalog and therefore there is no RESTORE DATABASE FROM <Disk-Location> command. You can circumvent this constraint using the RMAN command CATALOG START WITH <Disk-Location>. The following example shows how to restore from any disk location:
1. Copy the Backup Set Files to any Disk Location
    cp <backupset> /tmp/backup

2. Mount the Database
    sqlplus / as sysdba
    startup mount;

3. Cleanup RMAN Catalog
    rman target /
    crosscheck backup;
    delete noprompt expired backup of database;
    delete noprompt expired backup of controlfile;
    delete noprompt expired backup of archivelog all;
    list backup;

    At this point no backup should be available !


4. Make new Backup Location visible to RMAN Catalog
    catalog start with '/tmp/backup';

    searching for all files that match the pattern /tmp/backup

    List of Files Unknown to the Database
    =====================================
    File Name: /tmp/backup/PROD_datafile_14_1.bak
    File Name: /tmp/backup/PROD_controlfile_17.bak
    File Name: /tmp/backup/PROD_archivelog_16_1.bak
    File Name: /tmp/backup/PROD_datafile_15_1.bak

    Do you really want to catalog the above files (enter YES or NO)? yes

    List of Cataloged Files
    =======================
    File Name: /tmp/backup/PROD_datafile_14_1.bak
    File Name: /tmp/backup/PROD_controlfile_17.bak
    File Name: /tmp/backup/PROD_archivelog_16_1.bak
    File Name: /tmp/backup/PROD_datafile_15_1.bak

    list backup;

    At this point the backup must be available !


5. Restore and Recover the Database
    restore database;
    recover database;
    alter database open;



Case 11: Recover to a Restore Point
You can also use RMAN to restore a database to a Restore Point. Look:
1- Create a Restore Point
create restore point test_rp guarantee flashback database;

2- Perform your tests / drop tables, etc

3- You can review your restore points with:
select name, scn, time, guarantee_flashback_database from v$restore_point;

4- Shutdown the DB and start in MOUNT Mode
RMAN> shutdown immediate
RMAN> startup force mount;

5- Restore and Recover Database until Restore Point
RMAN> restore database until restore point test_rp;
RMAN> restore database until restore point test_rp;
RMAN> alter database open resetlogs;


Case 12: Recover a NOARCHIVELOG database using a backup controlfile
Be sure to have set the backup controlfile option in RMAN to ON, before taking your first backup as I did. This approach, using the controlfile coming from the autobackup, is different compared with the copy of an available multiplexed copy of a current controlfile because there you have to perform an incomplete recovery of your database, while using the autobackup controlfile you have already a controlfile with SCN consistent with all your datafiles.
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

Shutdown the DB to simulate the problem and remove the datafiles, redolog and current controlfile:
SQL> shutdown immediate;
$ ls
example01.dbf
redo02.log
sysaux01.dbf
temp01.dbf
users01.dbf
control01.ctl
redo01.log
redo03.log
system01.dbf
undotbs01.dbf

$ rm *

From rman console I typed the following commands:
$ rman target /
RMAN> startup nomount;
RMAN> restore controlfile from autobackup;
Starting restore at 17-JUL-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK

recovery area destination: /home/oracle/app/oracle/flash_recovery_area
database name (or database unique name) used for search: ORCL
channel ORA_DISK_1: AUTOBACKUP
/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_07_17/o1_mf_s_788864449_80c39jlo_.bkp
found in the recovery area
AUTOBACKUP search with format "%F" not attempted because DBID was not set
channel ORA_DISK_1: restoring control file from AUTOBACKUP
/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_07_17/o1_mf_s_788864449_80c39jlo_.bkp
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/home/oracle/app/oracle/oradata/orcl/control01.ctl
output file
name=/home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl
Finished restore at 17-JUL-12

And now I'm able to at least mount my database
RMAN> alter database mount;

And check that my database is using a backup controlfile
$ sqlplus / as sysdba
SQL> select controlfile_type from V$DATABASE;
CONTROL
-------
BACKUP

From the rman console issue the restore command and then open it with the resetlogs option:
RMAN> restore database;
RMAN> alter database open resetlogs;




Duplicate a Database

If you are using 11g, you have these 2 nice approaches:

In Oracle 10G you have new features to perform this. But the Oracle9i procedure is still valid for 10G as well.
You can use the RMAN DUPLICATE command to create a duplicate database from target database backups while still retaining the original target database.
The duplicate database can be either identical to the original database or contain only a subset of the original tablespaces.
To prepare for database duplication, you must first create an auxiliary instance. For the duplication to work, you must connect RMAN to both the target (primary) database and an auxiliary instance started in NOMOUNT mode.
As part of the duplicating operation, RMAN manages the following:
  1) Restores the target datafiles to the duplicate database and performs incomplete recovery by using all available incremental backups and archived logs.
  2) Shuts down and starts the auxiliary database.
  3) Opens the duplicate database with the RESETLOGS option after incomplete recovery to create the online redo logs.
  4) Generates a new, unique DBID for the auxiliary database.

During duplication, RMAN must perform incomplete recovery because the online redo logs in the target are not backed up and cannot be applied to the auxiliary database. The farest that RMAN can go in recovery of the duplicate database is the most recent redo log archived by the target database.
When duplicating a database, you can do the following:
  1) Run the DUPLICATE command with or without a recovery catalog
  2) Skip read-only tablespaces with the SKIP READONLY clause. Read-only tablespaces are included by default. If you omit them, then you can add them later.
  3) Exclude tablespaces from the auxiliary database with the SKIP TABLESPACE clause. You cannot skip the SYSTEM tablespace or tablespaces containing rollback or undo segments.
  4) Create the auxiliary database in a new host. If the directory structure is the same on the new host, then you can specify the NOFILENAMECHECK option and reuse the target datafile filenames for the auxiliary datafiles.
  5) Use the SET UNTIL command or DUPLICATE command with the UNTIL clause when creating the auxiliary database to recover it to a noncurrent time.
     By default, the DUPLICATE command creates the database by using the most recent backups of the target database and then performs recovery to the most recent consistent point contained in the incremental backups and  archived logs.

Basic Steps
 Perform these tasks before performing RMAN duplication:
Task 1: export ORACLE_SID=AUX
        Create an Oracle Password File for the Auxiliary Instance
Task 2: Ensure Oracle Net Connectivity to the Auxiliary Instance. You must connect to the auxiliary instance with SYSDBA privileges, so a password file must exist.
Task 3: Create an Initialization Parameter File for the Auxiliary Instance. Mandatory  initialization parameter settings for the auxiliary database:
db_block_size = <same size as the target>
DB_NAME=AUX
compatible = 9.2.0.0                     /* should be the same as the target
CONTROL_FILES=(/dup/oracle/oradata/trgt/control01.ctl, /dup/oracle/oradata/trgt/control02.ctl)
#DB_FILE_NAME_CONVERT=('/oracle/oradata/trgt/','/dup/oracle/oradata/trgt/')
#LOG_FILE_NAME_CONVERT=('/oracle/oradata/trgt/redo','/dup/oracle/oradata/trgt/redo')
Task 4: Start the Auxiliary Instance NOMOUNT
            CONNECT SYS/oracle@aux AS SYSDBA
            STARTUP FORCE NOMOUNT
Task 5: Mount or Open the Target Database
Task 6: Make Sure You Have the Necessary Backups and Archived Redo Logs
Task 7: Allocate Auxiliary Channels if Automatic Channels Are Not Configured

Start RMAN with a connection to the target database, the auxiliary database, and (if you use one) the recovery catalog database.
You can start the RMAN executable on any host so long as it can connect to all the instances.
If you do not have automatic channels configured, then before issuing the DUPLICATE command, manually allocate at least one auxiliary channel within the same RUN command.
If the backups reside on disk, then the more channels you allocate, the faster the duplication will be.
For tape backups, limit the number of channels to the number of devices available for the operation.

This example assumes the following:
- You are using a recovery catalog.
- The target database is on HOST1 and contains eight datafiles.
- You want to duplicate the target to database aux on the same host.
- You want to store the datafiles for AUX  under /export/home/oracle/AUX/ subdirectory.
- You want to exclude tablespace INDX from the duplicate database, but keep all of the other tablespaces.
- You want to restore the duplicate db at a noncurrent time.
- You want two online redo logs groups, each with two members of size 200 KB.
- You have configured the default device to disk.
- The auxiliary instance AUX has initialization parameter file in the default location (so the PFILE parameter is not necessary on the DUPLICATE command).
- You start RMAN from the AUX site.

RMAN> CONNECT TARGET system/system@R920;
RMAN> CONNECT CATALOG rman/rman@T920;
RMAN> CONNECT AUXILIARY SYS/oracle;

RMAN> CONFIGURE CHANNEL DEVICE TYPE disk CLEAR;
RMAN> CONFIGURE DEFAULT DEVICE TYPE TO disk;
RMAN> CONFIGURE DEVICE TYPE disk PARALLELISM 3;

# note that a RUN command is necessary because you can only execute SET NEWNAME
# within a RUN command
RMAN> RUN
{
  # the DUPLICATE command uses an automatic disk channel
  set until time "to_date('Jan 29 2003 10:50:00','Mon DD YYYY HH24:MI:SS')";
  SET NEWNAME FOR DATAFILE 1 TO '/export/home/oracle/AUX/system01.dbf';
  SET NEWNAME FOR DATAFILE 2 TO '/export/home/oracle/AUX/undotbs01.dbf';
  SET NEWNAME FOR DATAFILE 3 TO '/export/home/oracle/AUX/example01.dbf'; 
  SET NEWNAME FOR DATAFILE 5 TO '/export/home/oracle/AUX/tools01.dbf';
  SET NEWNAME FOR DATAFILE 6 TO '/export/home/oracle/AUX/users01.dbf';
  SET NEWNAME FOR DATAFILE 7 TO '/export/home/oracle/AUX/logmnrts.dbf';
  SET NEWNAME FOR DATAFILE 8 TO '/export/home/oracle/AUX/marius01.dbf';
  DUPLICATE TARGET DATABASE TO AUX
    SKIP TABLESPACE indx
    LOGFILE
      GROUP 1 ('/export/home/oracle/AUX/redo01a.log',
               '/export/home/oracle/AUX/redo01b.log') SIZE 10M REUSE,
      GROUP 2 ('/export/home/oracle/AUX/redo02a.log',
               '/export/home/oracle/AUX/redo02b.log') SIZE 10M REUSE;
}



Duplicate a database on the local server with a different file structure
On this example we will create a new DB called AUX based on one called PROD on the same box.

1. Create the directories for the Datafiles required by the Duplicate database. Ensure that their is enough space on this volume to hold all required files. At a minimum this will be the following: (1 control file, System data files, Undo data files, All datafiles required for the duplicate clone application tablespaces, The number of redo log groups and redo logs as on the production database).

2. Create the directories for the database administration area for the duplicate clone database AUX. This administration area will hold the following directories: bdump, cdump, udump.

3. Next create an init.ora file for the duplicate database. Since we are duplicating the database onto the same server as the original we must convert the file names so there is no conflict: (initAUX.ora)
    # Minimum Requirement.
    DB_NAME=DUP
    INSTANCE_NAME=AUX
    CONTROL_FILES=(/u02/oradata/AUX/control01.ctl, /u02/oradata/AUX/control02.ctl, /u02/oradata/AUX/control03.ctl)
    background_dump_dest
    core_dump_dest
    user_dump_dest
    log_archive_dest_1
    # Convert file names to allow for different directory structure.
    DB_FILE_NAME_CONVERT=(/u02/oradata/PROD/,/u02/oradata/AUX/)
    LOG_FILE_NAME_CONVERT=(/u01/oradata/PROD/,/u01/oradata/AUX/)
    # make sure block_size and compatible parameters match if you are not using the default.
    DB_BLOCK_SIZE=8192
    COMPATIBLE=9.2.0.0.0

4. From the operating system level ensure the ORACLE_SID environment variable is set to the duplicate clone database
    ORACLE_SID=DUP; export ORACLE_SID
We also need to create a password file for the duplicate instance:
    orapwd file=/u01/app/oracle/product/9.2.0.1.0/dbs/orapwAUX password=password entries=10

5. Next add the appropriate entries into the tnsnames.ora and listener.ora files in the $ORACLE_HOME/network/admin directory. Remember to load the new configuration into the listener:
    # Added to the listener.ora SID_LIST
    (SID_DESC =
      (ORACLE_HOME = /u01/app/oracle/product/9.2.0.1.0)
      (SID_NAME = AUX)
    )

    # Added to the tnsnames.ora
    AUX =
      (DESCRIPTION =
        (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.10)(PORT = 1521))
        )
        (CONNECT_DATA =
          (ORACLE_SID = AUX)
        )
      )

    # Reload listener
    lsnrctl reload


6. Next connect to the duplicate instance: and nomount it:
    sqlplus /nolog
    conn / as sysdba
Next we create an SPFILE based on the init.ora:
    CREATE SPFILE FROM PFILE='/u01/app/oracle/admin/AUX/pfile/initAUX.ora';
    STARTUP FORCE NOMOUNT;

7.  With the duplicate database mounted, we can now connect to it from RMAN. For the duplication to work we must connect to the original database (TARGET), the recovery catalog (CATALOG) and our duplicate database (AUXILIARY):
    ORACLE_SID=AUX; export ORACLE_SID
    rman TARGET sys/password@PROD CATALOG rman/rman@RMAN AUXILIARY sys/password@AUX

We can then dupicate the database using one of the following commands:
    # Duplicate database to TARGET's current state.
    DUPLICATE TARGET DATABASE TO AUX;

    # Duplicate database to TARGET's state 4 days ago.
    DUPLICATE TARGET DATABASE TO AUX UNTIL TIME 'SYSDATE-4';

    #Duplicate a database upto a given point in time:
    run {
      set until time '31-AUG-1999 11:08:00';
      allocate auxiliary channel ch1 type disk;
      allocate auxiliary channel ch2 type disk;
      allocate auxiliary channel ch3 type disk;
      duplicate target database to AUX;  
    }

    # Duplicate database to TARGET's except a few tablespaces.
    DUPLICATE TARGET DATABASE TO AUX skip tablespace indx, users, tools;

Once the process is finished RMAN produces a completion message and you have your duplicate instance.
After you have your database, you can perform several actions:
  • Add it to /etc/oratab
  • Modify init.ora parameters
  • etc


Restoring an RMAN Backup to Another Node
In certain circumstances, it may be desirable to restore a database from an RMAN backup onto a machine other than the original host. For example, to recover   data at a given point in time, or to duplicate a production instance.
The example assumes:
 - the target database is on host A
 - the database is to be restored onto host B
 - the directory structure of host B is different to host A
 - the ORACLE_SID will not change for the restored database
 - a recovery catalog is being used
 - the backups were carried out to disk (for illustrative purposes, and to disassociate from any media manager specific issues)
 
The following steps are required:
 1- backup the target on host A
 2- list the datafile locations on host A
 3- make the backup available to host B
 4- make a copy of the init.ora available to host B
 5- edit the init.ora to reflect directory structure changes
 6- configure SQL*Net connectivity from host to the recovery catalog and duplicated database
 7- set up a password file for the duplicated database
 8- startup nomount the duplicated database
 9- RMAN restore the controlfile(s)
 10- mount the database
 11- restore and rename the datafiles
 12- recover and open the database
 
These steps are expanded further below. 
 
1.Backup the Target on Host A
The target database needs to be backed up using RMAN. The following is one example of RMAN doing an online database backup. In this example, the backup sets are written to disk.
run {
allocate channel t1 type disk;
allocate channel t2 type disk;
allocate channel t3 type disk;

#backup the whole db
backup tag whole_database_open
  format '/oracle/backups/BFS/df_%u'
  database;
 
# switch out of the current logfile
alter system archive log current;
 
#backup the archived logs
backup archivelog all
  format '/oracle/backups/BFS/al_%u';
 
# backup a copy of the controlfile that contains records for the other backups just made
backup current controlfile tag = cf1 format '/oracle/backups/BFS/cf_%u' reuse;
}

 
2.List Datafile Locations on Host A
The datafile numbers and location on host A are required. These datafile locations will change on host B (see Section 7.3).
select file#, name from v$datafile;
  file#   name
  -----   ------------------------------
  1       /oracle/OFA_base/u01/oradata/V805X/system01.dbf
  2       /oracle/OFA_base/u01/oradata/V805X/rbs01.dbf
  3       /oracle/OFA_base/u01/oradata/V805X/temp01.dbf
  4       /oracle/OFA_base/u01/oradata/V805X/tools01.dbf
  5       /oracle/OFA_base/u01/oradata/V805X/users01.dbf
  6       /oracle/OFA_base/u01/oradata/V805X/users02.dbf
  7       /oracle/OFA_base/u01/oradata/V805X/rbs02.dbf
  8       /oracle/OFA_base/u01/oradata/V805X/rcvcat.dbf
 
The log file names should also be recorded (see Section 7.4).
 select group#, member from v$logfile;
  group#  member
  -----   ------------------------------
  1       /oracle/OFA_base/u01/oradata/V805X/redo01.log
  2       /oracle/OFA_base/u01/oradata/V805X/redo02.log
  3       /oracle/OFA_base/u01/oradata/V805X/redo03.log
 
3 Make the Backups Available to Host B
3.1 Disk Backups
During restore, RMAN will expect the backup sets to be located in the same directory as written to during the backup. For disk backups, the DBA can accomplish this in many ways:
   - set up an NFS directory, mounted on both host A and host B
   - create the same directory structure on host A and host B
   - use of symbolic links on host B
 
3.2 Tape Backups
The media management software must be configured such that host B is a media manager client, and can read the backup sets. The media management vendor should be consulted for support on this issue.
 
4. and 5. init.ora on host B
The "init.ora" needs to be made available on host B. Any location specific parameters must be ammended. For example,
  - ifile
  - *_dump_dest
  - log_archive_dest*
  - control_files
 
6. SQL*Net configuration
If running rman from host A:
 a. connectivity to the catalog remains unchanged
 b. configure tnsnames.ora on host A to connect to duplicated db on host B configure listener.ora on host B to accept connections for duplicated database
 
If running rman from host B:
 a. configure tnsnames.ora on host B to connect to catalog listener.ora on catalog host remains unchanged
 b. configure tnsnames.ora on host B to connect to duplicated db on host B configure listener.ora on host B to accept connections for duplicated database   
 
If running rman from host C (ie, neither host A or host B):
 a. connectivity to the catalog remains unchanged
 b. configure tnsnames.ora on host C to connect to duplicated db on host B configure listener.ora on host B to accept connections for duplicated database   
 
 
7. Setup Password File
In order to allow RMAN remote connections, a password file must be setup for the duplicated database. For example,
orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=kernel
 
 
8. Startup nomount the database
    SVRMGR> startup nomount pfile=<location of init.ora>

9. RMAN restore the controlfile(s)
Restore the controlfile(s). For example,
    run{
      allocate channel c1 type disk;
      restore controlfile;
    }

10. Mount the database
    SVRMGR> alter database mount;
 
11. Rename and restore the datafiles
Rename and Restore the Files, and perform database recovery. RMAN can be used to change the location of the datafiles from the location on host A (see Section 2) to the new location on host B. For example,
    run {
     allocate channel c1 type disk;
     allocate channel c2 type disk;
     allocate channel c3 type disk;
     set newname for datafile 1 to '/oracle/datafiles/system01.dbf';
     set newname for datafile 2 to '/oracle/datafiles/rbs01.dbf';
     set newname for datafile 3 to '/oracle/datafiles/temp01.dbf';
     set newname for datafile 4 to '/oracle/datafiles/tools01.dbf';
     set newname for datafile 5 to '/oracle/datafiles/users01.dbf';
     set newname for datafile 6 to '/oracle/datafiles/users02.dbf';
     set newname for datafile 7 to '/oracle/datafiles/rbs02.dbf';
     set newname for datafile 8 to '/oracle/datafiles/rcvcat.dbf';
     restore database;
     switch datafile all;
    }

 
11. Recover and open the database
Perform incomplete recovery: 
    SVRMGR> recover database using backup controlfile until cancel;

    Forward the database applying archived redo log files until you decide to stop recovery by typing cancel at the prompt (assuming that you have required archived redo log files in the log_archive_dest directory). You may archive the source database redo log files and apply them at the target database if required.     
    SVRMGR> alter database open resetlogs;
    
    Note: this will create the online redo logs in the same location as that on host A. If this directory location does not exist, then this will fail with:
    ora-344 : unable to recreate online log <name>
 
    The workaround is to rename the logfiles prior to opening the database:
    SVRMGR> alter database rename file '<host A location>' to '<host B location>';
 
    Alternatively, the logfile groups can be dropped and recreated. However, attempts to drop the current logfile group will fail. The current logfile must be renamed.


Managing Errors

ORA-19809
If you are trying to do a backup of your database and you get somethinkg like:
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on t1 channel at 10/19/2011 10:54:21
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 52428800 bytes disk space from 6442450944 limit

The first thing to do is to check the amount of used and free space in your recovery area:
set lines 100
col name format a60
select    name, floor(space_limit / 1024 / 1024) "Size MB", ceil(space_used  / 1024 / 1024) "Used MB", floor(space_limit / 1024 / 1024) - ceil(space_used  / 1024 / 1024) "Free Space MB"
from v$recovery_file_dest
order by name;

NAME                                                            Size MB    Used MB Free Space MB
------------------------------------------------------------ ---------- ---------- -------------
C:\app\flash_recovery_area                                         6144       5769           375

As you can see on this example, you do not have much space free.
To fix the problem, you need to either make the flash recovery area larger, or remove some files from it.

If you have the disk space available, make the recovery area larger. Example:
alter system set db_recovery_file_dest_size=15G scope=both;

To remove files you must use RMAN. Manually moving or deleting files will have no effect as oracle will be unaware. The obvious choice is to backup and remove some archive log files. However, if you usually write your RMAN backups to disk, this could prove tricky. RMAN will attempt to write the backup to the flash recovery area...which is full. You could try sending the backup elsewhere using a command such as this:
rman target /
run {
allocate channel t1 type disk;
backup archivelog all delete input format '/<temp backup location>/arch_%d_%u_%s';
release channel t1;
}

This will backup all archive log files to a location of your choice and then remove them.

Another "dirty" choice
The removal is desribed in the RMAN documentation but this is a quick and dirty way if you don’t have an rman repository - but could endanger your ability to recover - so be careful.
a) delete unwanted archive log files from disk ( rm /del )
b) connect to rman
c) crosscheck archivelog all;     --> marks the controlfile that the archives have been deleted
d) delete expired archivelog all; --> deletes the log entries identified above.


RMAN-06059: expected archived log not found, lost of archived log compromises recoverability
It means that the Archive Log Files doesn't exist (maybe was removed on OS level but not by RMAN). 
Your options are either to restore the missing file(s), or to perform a crosscheck. To perform a crosscheck, run the following command from within RMAN:
RMAN> crosscheck archivelog all;
This will give non-existing archivelogs status of 'EXPIRED', these are no longer used for backups.
When an archive log crosscheck is performed, RMAN checks each archive log in turn to make sure that it exists on disk (or tape). Those that are missing are marked as unavailable. If you have got missing logs, this won't bring them back. It will allow you to get past this error and back-up the database though.
It is advisable to perform a full backup of the database at this point. Schedule a crosscheck every once in a while (e.g. weekly) in order to prevent this.
And then run:
RMAN> list expired archivelog all;
to see if it lists any missing archive logs.
And then:
RMAN> delete expired archivelog all;
to remove the expired ones.


11g New Feature: RMAN Data Recovery Advisor (DRA)
One of the great new features of RMAN in Oracle 11g is the new tool named Data Recovery Advisor (DRA).
This tool automates the diagnose of the data failure and corruption, advises the recovery steps and performs automatic recovery of the database failure.  This tool could be used from GUI as well as from CLI.

To list of database failures, you can issue:

RMAN> list failure;
 
List of Database Failures
=========================
Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
522        HIGH     OPEN      08-SEP-09     One or more non-system datafiles are missing

DRA will consolidate related failures into a single failure. You can list a failure individually by issuing "list failure X detail" command.

RMAN> list failure 522 detail;

 
List of Database Failures
=========================
Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
522        HIGH     OPEN      08-SEP-09     One or more non-system datafiles are missing
 
  Impact: See impact for individual child failures
  List of child failures for parent failure ID 522
  Failure ID Priority Status    Time Detected Summary
  ---------- -------- --------- ------------- -------
  525        HIGH     OPEN      08-SEP-09     Datafile 4: '/abc' is missing
 
    Impact: Some objects in tablespace USERS might be unavailable


By default, the list failure command shows all failures with the critical and high priorities. You can list failures for a specific priority:
RMAN> list failure closed;
RMAN> list failure low;
RMAN> list failure high;
RMAN> list failure critical;


When you fix a failure, the failure will be closed automatically.
However, sometimes a failure may be irrelevant for you. In this case, you can use RMAN’s change failure command to close it.
You can also use this command to change the priority (you can’t change a priority level of critical):
RMAN> change failure 522 closed;
RMAN> change failure 522 priority low;


DRA can also recommend how to fix the failures:

RMAN> advise failure;

 
List of Database Failures
=========================
Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
522        HIGH     OPEN      08-SEP-09     One or more non-system datafiles are missing
 
analyzing automatic repair options; this may take some time
using channel ORA_DISK_1
analyzing automatic repair options complete
 
Mandatory Manual Actions
========================
no manual actions available
 
Optional Manual Actions
=======================
1. If file /u01/oracle/oradata/users01.dbf was unintentionally renamed or moved, restore it
 
Automated Repair Options
========================
Option Repair Description
------ ------------------
1      Restore and recover datafile 4
 
  Strategy: The repair includes complete media recovery with no data loss
  Repair script: /u01/oracle/diag/rdbms/reco_990089206.hm


The REPAIR FAILURE command applies the repair scripts produced by the ADVISE FAILURE command. Using the PREVIEW option lists the contents of the repair script without applying it.

RMAN> REPAIR FAILURE PREVIEW;


Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/oracle/diag/rdbms/reco_990089206.hm

contents of repair script:
   # restore and recover datafile
   alter database datafile 4 offline;
   restore datafile 4;
   recover datafile 4;
   alter database datafile 4 online;

Once DRA provides its repair recommendations, you can choose to run the repair failure command to fix and close a specific failure.
By default, the REPAIR FAILURE command prompts the user to confirm the repair, but this can be prevented using the NOPROMPT keyword.
If the advise failure command recommends any manual repairs, you need to perform these repairs first:
RMAN> repair failure;  -- Optional use repair failure noprompt
 
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/oracle/diag/rdbms/reco_990089206.hm
 
contents of repair script:
   # restore and recover datafile
   alter database datafile 4 offline;
   restore datafile 4;
   recover datafile 4;
   alter database datafile 4 online;
 
Do you really want to execute the above repair (enter YES or NO)? YES
executing repair script
 
sql statement: alter database datafile 4 offline
 
Starting restore at 08-SEP-09
using channel ORA_DISK_1
 
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 4 to /u01/oracle/oradata/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/oracle/fra/backup.bkp
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 08-SEP-09
 
Starting recover at 08-SEP-09
using channel ORA_DISK_1
 
starting media recovery
media recovery complete, elapsed time: 00:00:01
 
Finished recover at 08-SEP-09
 
sql statement: alter database datafile 4 online
repair failure complete

The full syntax for the REPAIR FAILURE command can be seen here.


The VALIDATE command initiates data integrity checks, logging physical, and optionally logical, block corruptions of database files and backups in the V$DATABASE_BLOCK_CORRUPTION view and the Automatic Diagnostic Repository as one or more failures.
The following code shows some of the possible syntax variations.

# Check for physical corruption of all database files.

VALIDATE DATABASE;

# Check for physical and logical corruption on a tablespace
VALIDATE TABLESPACE USERS;

# Check for physical and logical corruption of a tablespace.
VALIDATE CHECK LOGICAL TABLESPACE USERS;

# Check for physical and logical corruption on a datafile and block

VALIDATE DATAFILE 4 block 1;

# Check for physical and logical corruption of a datafile.
VALIDATE CHECK LOGICAL DATAFILE 4;

# Check for physical corruption of all archived redo logs files.
VALIDATE ARCHIVELOG ALL;

# Check for physical and logical corruption of the controlfile.
VALIDATE CHECK LOGICAL CURRENT CONTROLFILE;

# Check for physical and logical corruption of a specific backupset.
VALIDATE CHECK LOGICAL BACKUPSET 3;




The BACKUP VALIDATE and RESTORE VALIDATE commands perform the same checks as the VALIDATE command for the files targeted by the backup or restore command, but they don't actually perform the specified backup or restore operation. This allows you to check the integrity of a backup or restore operation before actually performing it.
The following code shows some of the possible syntax variations.

# Check for physical corruption of files to be backed up.
BACKUP VALIDATE DATABASE ARCHIVELOG ALL;

# Check for physical and logical corruption of files to be backed up.
BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;

# Check for physical corruption of files to be restored.
RESTORE VALIDATE DATABASE;

# Check for physical and logical corruption of files to be restored.
RESTORE VALIDATE CHECK LOGICAL DATABASE;



SET NEWNAME Clause Enhancements

Now it simplifies setting new names for files. It handles all files with a single SET NEWNAME. Use it with Duplicate, Restore and Switch commands.
The order of Precedence is:
- FOR DATAFILE
- FOR TEMPFILE

- FOR TABLESPACE
SET NEWNAME FOR TABLESPACE is used for setting the datafiles for a particular tablespace using the specified format.  When using the SET NEWNAME FOR TABLESPACE the substitution variables in the TO <filename> clause are required to avoid duplicate filenames, therefore at least one of the following substitution variables must be utilized: %b, %f and %U.  %I and %N are additional optional substitution variables that can be utilized. Note the precedence order is used on this example:
RUN
    {  SET NEWNAME FOR DATABASE TO ‘%U’ ;
       SET NEWNAME FOR TEMPFILE TO ‘/opt/oracle/oradata/%U’ ;
       SET NEWNAME FOR TABLESPACE example TO ‘/opt/oracle/oradata/%b’ ;
       DUPLICATE TARGET DATABASE TO newdb ;
}

- FOR DATABASE
SET NEWNAME FOR DATABASE clause is used to set a new name format for all datafiles for the database for DUPLICATE, RESTORE and SWITCH commands. 
When using the SET NEWNAME FOR DATABASE the substitution variables in the TO <filename> clause are required to avoid duplicate filenames, therefore at least one of the following substitution variables must be utilized: %b, %f and %U.  %I and %N are additional optional substitution variables that can be utilized. 
Note that SET NEWNAME FOR DATABASE does not set new filenames for tempfiles therefore you will need to set a specification for tempfiles when using SET NEWNAME FOR DATABASE. Example:
RUN
    {  SET NEWNAME FOR DATABASE TO ‘/opt/oracle/oradata/%U’ ;
       SET NEWNAME FOR TEMPFILE TO ‘/opt/oracle/oradata/%U’ ;
       DUPLICATE TARGET DATABASE TO newdb ;
}


Substitution Variables
The setting of a file format can use a defined set of substitution variables in the TO <filename> format specification. 
This allows file names in the TO to be generated and simplifies that naming so that all can be handles in a single set operation.

%b    filename without the directory path
%f    absolute file number of the datafile
%I    Database ID (DBID)
%N    Tablespace Name
%U    System Generated file name in the format (data-D-%d_id-%I_TS_FNO-%f)



RMAN Backup Strategy

Option 1: Full & Incremental Tape Backups
• Well-suited for:
– Databases that can tolerate hours/days of Recovery Point Objective (RTO)
– Environments where disk is premium
– Low-medium change frequency between backups, e.g. < 20%
• Backup strategy:
– Weekly level 0 and daily ‘differential’ incremental backup sets to tape, with optional backup compression
– Enable block change tracking -only changed blocks are read and written during incremental backup
– Archived logs are backed up and retained on-disk, as needed

Script Example
- Configure SBT(i.e. tape) channels:
CONFIGURE CHANNEL DEVICE TYPE SBT PARMS '<channel parameters>';

- Weekly full backup:
BACKUP AS BACKUPSET INCREMENTAL LEVEL 0 DATABASE PLUS ARCHIVELOG;

- Daily incremental backup:
BACKUP AS BACKUPSET INCREMENTAL LEVEL 1 DATABASE PLUS ARCHIVELOG;


Option 2: Incrementally Updated Disk Backups

• Well-suited for:
– Databases that can tolerate no more than a few hours RTO
– Environments where disk can be allocated for 1X size of database or most critical tablespaces
• Backup strategy:
– Initial image copy to FRA, followed by daily incremental backups
– Roll forward copy with incremental, to produce new on-disk copy
– Full backup archived to tape, as needed
– Archived logs are backed up and retained on-disk, as needed
– Fast recovery from disk or SWITCHto use image copies

Script Example
- Configure SBT(i.e. tape) channels if needed:
CONFIGURE CHANNEL DEVICE TYPE SBT PARMS '<channel parameters>';

- Daily roll forward copy and incremental backup:
RECOVER COPY OF DATABASE WITH TAG 'OSS';
BACKUP DEVICE TYPE DISK INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'OSS' DATABASE;
[BACKUP DEVICE TYPE SBT ARCHIVELOG ALL;]

- What happens?
First run: Image copy
Second run: Incremental backup
Third run+: Roll forward copy & create new incremental backup

- Backup FRA to tape, if needed:
[BACKUP RECOVERY AREA;]


Option 3: Offload Backups to Physical Standby Database in Data Guard Environment
• Well-suited for:
– Databases that require no more than several minutes of recovery time, in event of any failure
– Environments that can preferably allocate symmetric hardware and storage for physical standby database
– Environments whose tape infrastructure can be shared between primary and standby database sites
• Backup strategy:
– Full and incremental backups offloaded to physical standby database
– Fast incremental backup on standby with Active Data Guard
– Backups can be restored to primary or standby database


Scripts to get information from RMAN


Using a  Catalog

rem -----------------------------------------------------------------------
rem Purpose:   
Determine which DB's are registered in the recovery catalog
rem Author:     Diego Pafumi
rem -----------------------------------------------------------------------
SELECT * FROM RC_DATABASE;

 DB_KEY   DBINC_KEY   DBID         NAME   RESETLOGS_CHANGE#   RESETLOGS
-------- ----------- ------------ ------ -------------------- -----------
      1           2   3939414311   OLTP4R                   1   27-APR-06


rem ------------------------------------------------------------------------------------

rem Purpose:   
Determine which tablespaces are currently stored in the recovery catalog
rem Author:     Diego Pafumi
rem ------------------------------------------------------------------------------------
SELECT DB_KEY, DBINC_KEY, DB_NAME, TS#, substr(NAME,1,20) name, CREATION_CHANGE#, CREATION_TIME
     FROM RC_TABLESPACE
     order by 4;
     
 DB_KEY   DBINC_KEY   DB_NAME   TS#   NAME                   CHANGE#   CRE_DATE
-------- ----------- --------- ----- ------------------    ---------  ----------
      1           2   OLTP4R     0     SYSTEM                      3   27-APR-06
      1           2   OLTP4R     1     UNDOTBS1                 9611   27-APR-06
      1           2   OLTP4R     1     SYSAUX                   9599   27-APR-06
      1           2   OLTP4R     4     TEMP                    14023   27-APR-06
      1           2   OLTP4R     2     UNDOTBS2                 9605   27-APR-06
      1           2   OLTP4R     2     USERS                    9605   27-APR-06
      1           2   OLTP4R     2     WPJ_STW_INDEXES_TBS      9605   27-APR-06
      1           2   OLTP4R     2     WPJ_STW_TABLES_TBS       9605   27-APR-06
      1           2   OLTP4R     2     WPJ_T01_INDEXES_TBS      9605   27-APR-06
      1           2   OLTP4R     2     WPJ_T01_LOBS_TBS         9605   27-APR-06
      1           2   OLTP4R     2     WPJ_T01_TABLES_TBS       9605   27-APR-06
      1           2   OLTP4R     2     WPJ_T01_INDEXES_TBS      9605   27-APR-06



rem ------------------------------------------------------------------------------------
rem Purpose:   
Determine which scripts are currently stored in the recovery catalog
rem Author:     Diego Pafumi
rem ------------------------------------------------------------------------------------
SQL> SELECT * FROM RC_STORED_SCRIPT;

 DB_KEY   DB_NAME   SCRIPT_NAME
-------- --------- ---------------
      1   OLTP4R     nightlybackup
      1   OLTP4R     archivebackup
     

rem -----------------------------------------------------------------------
rem Purpose:    Get information from Catalog
rem Author:     Diego Pafumi
rem -----------------------------------------------------------------------
set pages 200
col datafileMb format 9,999,999 heading "Datafile Size Mb"
col backedupMb format 9,999,999 heading "Backuped Size Mb"
break on report
compute sum of datafileMb on report
compute sum of backedupMb on report
show user
SELECT db_name,MIN(completion_time)
FROM rc_backup_datafile
GROUP BY db_name;

REM top running backups
select rownum as rank, name,
       DECODE(backup_type,'D','Full','I','Incremental','L','Archive Log',backup_type) backup_type
       ,round(max_secs/60) mins
FROM (select name, backup_type, max(elapsed_seconds) max_secs
       from rc_backup_set bs, rc_database d
       where bs.db_key = d.db_key
       group by name, backup_type
       order by max(elapsed_seconds) desc
     )
WHERE rownum <= 10;

SELECT db_name, DECODE(status,'A','Available','D','Deleted','O','Unusable',status) status
      , COUNT(*)
from rc_backup_datafile
GROUP BY db_name,status;

SELECT db_name, DECODE(backup_type, 'D','Full','Incremental') backup_type,
       SUM(datafile_blocks*block_size) /1024/1024 datafileMb,
       SUM(blocks*block_size) /1024/1024 backedupMb
FROM rc_backup_datafile
GROUP BY db_name, DECODE(backup_type, 'D','Full','Incremental');


rem -----------------------------------------------------------------------
rem Filename:   rmanlist.sql
rem Purpose:    List backups registered in RMAN catalog database
rem Author:     Diego Pafumi
rem -----------------------------------------------------------------------

connect rman/rman

col media   format a8
col tag     format a12 trunc
col minutes format 990

select d.name, p.tag, p.media,
       s.incremental_level "LEVEL",
       to_char(s.start_time, 'DD-MON-YY HH24:MI') start_time,
       s.elapsed_seconds/60 "MINUTES"
from  RC_DATABASE d, RC_BACKUP_PIECE p, RC_BACKUP_SET s
where d.name            = 'WH'
  and s.start_time      > sysdate-5
  and s.completion_time < sysdate
  and p.backup_type     = 'D'      -- D=Database, L=Log
  and d.db_key = p.db_key
  and s.db_key = p.db_key
  and p.bs_key = s.bs_key;



Without a Catalog
 

rem -----------------------------------------------------------------------
rem Filename:   rmanlist24.sql
rem Purpose:    List completed RMAN backups for the last 24-hours
rem             (use info from Dictionary - Control File Views)
rem Author:     Diego Pafumi
rem -----------------------------------------------------------------------
tti "RMAN Backups for last 24-hours"
col type     format a4
col handle   format a35 trunc
col file#    format 9999
col duration format a9
select decode(BACKUP_TYPE, 'L', 'ARCH', 'D', 'DB', 'I', 'INC', 'Unknown type='||BACKUP_TYPE ) TYPE,
       to_char(a.start_time, 'DDMON HH24:MI') start_time,
       to_char(a.elapsed_seconds/60, '99.9')||' Min' DURATION,
       substr(handle, -35) handle,
       nvl(d.file#, l.sequence#) file#, nvl(d.blocks, l.blocks) blocks
from   SYS.V_$BACKUP_SET a, SYS.V_$BACKUP_PIECE b,
       SYS.V_$BACKUP_DATAFILE d, SYS.V_$BACKUP_REDOLOG l
where  a.start_time between sysdate-1 and sysdate
  and  a.SET_STAMP = b.SET_STAMP
  and  a.SET_STAMP = d.SET_STAMP(+)
  and  a.SET_STAMP = l.SET_STAMP(+)
order  by start_time, file#;


rem -----------------------------------------------------------------------
rem Filename:   rmanstat24.sql
rem Purpose:    Summary of all datafiles and archlogs backed up over the
rem             last 24-hours.
rem Author:     Diego Pafumi
rem -----------------------------------------------------------------------

tti "RMAN Status for past 24-hours"

-- Datafiles Backed up during past 24 Hours
SELECT substr(dbfiles||' from '||numfiles, 1, 30) "Datafiles backed up",
       cfiles "Control Files backed up", spfiles "SPFiles backed up"
  FROM (select count(*) numfiles from sys.v_$datafile),
       (select count(*) dbfiles
          from sys.v_$backup_datafile a, sys.v_$datafile b
         where a.file# = b.file#
           and a.completion_time > sysdate - 1),
       (select count(*) cfiles from sys.v_$backup_datafile
         where file# = 0 and completion_time > sysdate - 1),
       (select count(*) spfiles from sys.v_$backup_spfile
         where completion_time > sysdate - 1);

-- Archlog Files Backed up during past 24 Hours
SELECT substr(backedup||' from '||archived, 1, 30) "Archlog files backed up",
       ondisk "Archlog files still on disk"
  FROM (select count(*) archived
          from sys.v_$archived_log where completion_time > sysdate - 1),
       (select count(*) backedup from sys.v_$archived_log
         where backup_count > 0
           and completion_time > sysdate - 1),
       (select count(*) ondisk from sys.v_$archived_log
         where archived = 'YES' and deleted  = 'NO');



rem -----------------------------------------------------------------------
rem Filename:   rman_run.sql
rem Purpose:    Monitor RMAN status, while a backup is running
rem Author:     Diego Pafumi
rem -----------------------------------------------------------------------
select SID, to_char(start_time, 'DD/MON/YY HH24:MI') START_TIME, TOTALWORK,
       sofar, (sofar/totalwork) * 100 PCT_DONE,
       sysdate + TIME_REMAINING/3600/24 END_AT
--       ,
elapsed_seconds/60 "ELAPSE (Min)", round(sofar/totalwork*100,2) "Complete%"
from v$session_longops
where totalwork > sofar
  AND opname NOT LIKE '%aggregate%'
  AND opname like 'RMAN%';

       SID START_TIME         TOTALWORK      SOFAR       DONE END_AT
----------- ----------------- ---------- ---------- ---------- -----------------
       287 15/07/08 21:08:58   22824638   21120316 92.5329725 16/07/08 04:19:42


rem -----------------------------------------------------------------------
rem Filename:  
rem Purpose:    Review the results and keep a long-term record of your backups.
rem Author:     Diego Pafumi
rem -----------------------------------------------------------------------
set linesize 100
col object_type format a15
alter session set nls_date_format='DD-MON-YYYY hh24:mi:ss';

select object_type,mbytes_processed, start_time, end_time,status
from v$rman_status
where session_recid = (select max(session_recid) from v$rman_status)
and operation !='RMAN'
order by recid;

OBJECT_TYPE     MBYTES_PROCESSED START_TIME           END_TIME             STATUS
--------------- ---------------- -------------------- -------------------- -----------------------
CONTROLFILE                    7 15-JUN-2012 08:48:36 15-JUN-2012 08:48:42 COMPLETED
DATAFILE FULL                 24 15-JUN-2012 08:57:52 15-JUN-2012 08:57:57 COMPLETED
DB FULL                     2311 15-JUN-2012 09:02:10 15-JUN-2012 09:03:30 COMPLETED


Unix Scripts

For all the UNIX scripts, is HIGHLY suggested to use:
export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
To get a nice format of the date that the backups starts and ends.


Script to clean up OLD Trace files and Log Files
#!/bin/ksh
# Cleanup archive logs more than 7 days old
find /u01/app/oracle/admin/mysid/arch/arch_mysid*.arc -ctime +7 -exec rm {} ;



Script to loop through each database, visiting the bdump, udump, and audit directories, and removes all files that are more than two weeks old.
#!/bin/ksh
for ORACLE_SID in `cat /etc/oratab|egrep ':N|:Y'|grep -v \*|cut -f1-d':'`
do
ORACLE_HOME=`cat /etc/oratab|grep ^$ORACLE_SID:|cut -d":" -f2`
DBA=`echo $ORACLE_HOME | sed -e 's:/product/.*::g'`/admin
find $DBA/$ORACLE_SID/bdump -name \*.trc -mtime +14 -exec rm {} \;
$DBA/$ORACLE_SID/udump -name \*.trc -mtime +14 -exec rm {} \;
find $ORACLE_HOME/rdbms/audit -name \*.aud -mtime +14 -exec rm {} \;
done


Script for Oracle 9/10 database that shuts down the database, mounts it, backs it up, and opens it:
#!/bin/bash
rman target / catalog rman/rman@RMAN <<EOF
shutdown immediate;
startup mount;
backup database format '/u01/oracle/db/AKI1/bck/ora_df%t_s%s_s%p';
alter database open;
EOF
exit
$ ./rman_offline.bash


Full Database Online Backup and Send e-mail
#!/bin/ksh
#
################################################################################
#* Name: Full_RMAN_Backup.ksh
#*
#* Date: May, 07, 2008
#*
#* Author     : Diego Pafumi
#*
#* Description: Performs Full HOT Backup using RMAN
#*
#* Output:      Full_RMAN_Backup.txt
#*
#* Exceptions:   No handled errors.
#*
#* Usage: Full_RMAN_Backup.ksh
#*
#* Modifications: NONE
################################################################################
#
##############Initialization parameters ################################
#Set env info for oracle
#sh /home/oracle/DBA_SCRIPTS/Ora_Param.ksh
export ORACLE_BASE=/u01/app/oracle
export ORACLE_SID=CCOM
export ORACLE_HOME=$ORACLE_BASE/OraHome_1
export PATH=$PATH\:$ORACLE_HOME/bin
export SQL="sqlplus -s /nolog"
export TNS_ADMIN=$ORACLE_HOME/network/admin
export LOGIN="/ as sysdba"
export DATESTAMP=`date '+%C%y%m%d'`  #Will show 20080508
export LOG_FILE="Full_RMAN_Backup.txt"
export BACKUP_LOC=/data/u03/HOTBCKP
cd /home/oracle/DBA_SCRIPTS
/RMAN
##############End Initialization parameters ################################
$ORACLE_HOME/bin/rman target=/ nocatalog msglog Full_RMAN_Backup_${
DATESTAMP}.log <<EOF

crosscheck backup;
crosscheck archivelog all;
delete noprompt force obsolete;
delete noprompt force expired backup;
delete noprompt force expired archivelog all;

#backup full database tag rman_nocat_${ORACLE_SID} plus archivelog delete input tag rman_nocat_${ORACLE_SID} ;

backup database plus archivelog tag="Full_Database";
list recoverable backup;
exit;
EOF

cat Full_RMAN_Backup_${DATE}.log | mail -s "RMAN_Backup" mail_Address




Another Simple FULL DB Backup Script
#!/bin/bash
##############Initialization parameters ################################
#Set env info for oracle
#sh /home/oracle/DBA_SCRIPTS/Ora_Param.ksh
export ORACLE_BASE=/u01/app/oracle
export ORACLE_SID=CCOM
export ORACLE_HOME=$ORACLE_BASE/OraHome_1
export PATH=$PATH\:$ORACLE_HOME/bin
export SQL="sqlplus -s /nolog"
export TNS_ADMIN=$ORACLE_HOME/network/admin
export LOGIN="/ as sysdba"
export DATESTAMP=`date '+%C%y%m%d'`  #Will show 20080508
export LOG_FILE="Full_RMAN_Backup.txt"
export BACKUP_LOC=/data/u03/HOTBCKP
cd /home/oracle/DBA_SCRIPTS/RMAN
#set a date format that includes the time-of-day portion
NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'; export NLS_DATE_FORMAT
##############End Initialization parameters ################################
# Compress the older archive_log files, so we can keep more on disk,
#  have the most recent days uncompressed, and prevent RMAN from deleting them as "obsolete".
cd /data/u03/app/oracle/oradata/CCOM/ARCH/
find . -name 'arc*.log' -mtime +1 -exec nice gzip -9 {} \;

# Run RMAN with "nice" to minimize impact on other processes:
nice $ORACLE_HOME/bin/rman target=/ catalog=rman/[rman_password]@rman log='/home/oracle/DBA_SCRIPTS/rman.log' @/home/oracle/DBA_SCRIPTS/backdb_rman.sql

# Rename today's *.log file to include the current month, day, hour and minute:
mv rman.log rman_`date '+%m%d%H%M'`.log

# The RMAN syntax for a compressed backup is:
#backup as compressed backupset database
# (instead of the simple: "backup database" command, but it took 2-3 times longer when tested
# with 10gR1 on Aug. 11, 2006 and gave only a 33% reduction in space used)
exit


"backdb_rman.sql" Script
delete noprompt obsolete;
crosscheck archivelog all;
delete noprompt expired archivelog all;
#resync catalog;
backup database plus archivelog tag="Full_Database";
exit;



Another FULL DB Backup Script with more control
#!/usr/bin/ksh
# Instructions:
#   1- Determine target instance nls setting for nls_lang parameter.
#     SQL> select value from v$nls_parameters where parameter = 'NLS_LANGUAGE';
#     SQL> select value from v$nls_parameters where parameter = 'NLS_TERRITORY';
#     SQL> select value from v$nls_parameters where parameter = 'NLS_CHARACTERSET';
#     Use above nls information to edit script to change charset parameter.
#          charset="LANGUAGE_TERRITORY.CHARACTERSET" # Characterset of the database
##########################################################################
#set -x      # Uncomment for debugging the code
##########################################################################
#* Name: RMAN_Full.ksh
#*
#* Date  : Sept, 10, 2006
#*
#* Description : Take an RMAN full backup
#*
#* Modifications:
#*
#* Author:        Diego Pafumi
#*
#* Usage: RMAN_Full.ksh
#*
#* Used By: CRON job via oracle UNIX account
#* 03 4 * * 0-6 /opt/oracle/DBA_SCRIPTS/RMAN_Full.ksh
#*
#****************************************************************
##############Initialization parameters ################################
#Set env info for oracle
#sh /home/oracle/DBA_SCRIPTS/Ora_Param.ksh
export ORACLE_BASE=/u01/app/oracle
export ORACLE_SID=CCOM
export ORACLE_HOME=$ORACLE_BASE/OraHome_1
export PATH=$PATH\:$ORACLE_HOME/bin
export SQL="sqlplus -s /nolog"
export TNS_ADMIN=$ORACLE_HOME/network/admin
export LOGIN="/ as sysdba"
export DATESTAMP=`date '+%C%y%m%d'`  #Will show 20080508
export LOG_FILE="Full_RMAN_Backup.txt"
export BACKUP_LOC=/data/u03/HOTBCKP
cd /home/oracle/DBA_SCRIPTS/RMAN
#set a date format that includes the time-of-day portion
export NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8         # Characterset of the database
export LOG_FOLDER=/home/oracle/DBA_SCRIPTS/RMAN   # Log Location
##############End Initialization parameters ################################

db_status="CLOSED"
archive_log="NOARCHIVELOG"
#Get the day of the week such as Sun, Mon,....,Sat
BACKUP_DAY=`date +%a`
#Get the date in the format YYMMDD
DATE=`date +%Y%m%d`
############# End Initialization Parameters ############################

#
# Check status of database
#
pmon=`ps -ef | egrep pmon_$ORACLE_SID | grep -v grep`

if [ "$pmon" = "" ]; then
  db_status="CLOSED"
else
  db_status=`sqlplus -s "/ as sysdba" <<EOF
  set heading off
  set feedback off
  set verify off
  select status from v\\$instance;
  exit
EOF
`
fi


if [ $db_status = "MOUNTED" -o $db_status = "OPEN" ]; then
#
# Get the archivelog-mode of the database
#
  archive_log=`sqlplus -s "/ as sysdba" <<EOF
  set heading off
  set feedback off
  set verify off
  select log_mode from v\\$database;
  exit
EOF
`
fi


if [ $archive_log = "ARCHIVELOG" ]; then
rman target / catalog rman/rman@RMAN msglog
/home/oracle/DBA_SCRIPTS/RMAN/Connection.txt <<EOF
   #Show Some Information
   spool log to '$LOG_FOLDER/Schema$BACKUP_DAY.log'
   show all;
   crosscheck backup;
   report obsolete;
   list expired backup;
   list expired backupset;
   delete noprompt obsolete;
   delete expired backup;
   delete expired backupset;
   report schema;
#   RESYNC CATALOG;
   spool log off

   spool log to '$LOG_FOLDER/Backup$BACKUP_DAY.log'
   BACKUP DATABASE PLUS ARCHIVELOG delete input tag="Full_DB_CCOM";
   delete noprompt obsolete;
#   resync catalog;
   spool log off
EOF
else
   echo "ERROR ON RMAN BACKUP!!!!"                                        > $LOG_FOLDER/Backup$BACKUP_DAY.log
   echo "DATABASE $ORACLE_SID IS NOT OPENED OR NOT IN ARCHIVE LOG MODE"  >> $LOG_FOLDER/Backup$BACKUP_DAY.log
fi

#mail -s "RMAN Backup Report for $ORACLE_SID" mail@gmail.com < $LOG_FOLDER/Backup$BACKUP_DAY.log
#mail -s "RMAN Backup Report for $ORACLE_SID" mail@yahoo.com < $LOG_FOLDER/Backup$BACKUP_DAY.log







Another FULL DB Backup Script with more control
#! /bin/sh
# script: prod_rman_backup_job.sh
# Features: This shell script performs an RMAN full database backup of all
# datafiles, archivelogs, control file, init.ora and the orapw file.
# The prod1_disaster_recovery.txt file is also created every time a backup is
# performed for disaster recovery purposes.
# This RMAN backup procedure uses the controlfile for storage
# of information about backup sets.
#
# Output Files: /home/oracle/DBA_SCRIPTS/RMAN/prod_disaster_recovery.txt
#
# Used By: CRON job via oracle UNIX account
# Usage:
# ******** RMAN Oracle database full backup at 4:03 ********
# 03 4 * * 0-6 /opt/server_scripts/prod1_rman_backup_job_1.sh
#
# ---------------------- Revision History ---------------
# Date By Changes

##############Initialization parameters ################################
#Set env info for oracle
#sh /home/oracle/DBA_SCRIPTS/Ora_Param.ksh
export ORACLE_BASE=/u01/app/oracle
export ORACLE_SID=CCOM
export ORACLE_HOME=$ORACLE_BASE/OraHome_1
export PATH=$PATH\:$ORACLE_HOME/bin
export SQL="sqlplus -s /nolog"
export TNS_ADMIN=$ORACLE_HOME/network/admin
export LOGIN="/ as sysdba"
export DATESTAMP=`date '+%C%y%m%d'`  #Will show 20080508
export LOG_FILE="Full_RMAN_Backup.txt"
export BACKUP_LOC=/data/u03/HOTBCKP
cd /home/oracle/DBA_SCRIPTS/RMAN
export ORACLE_OWNER=oracle
export ORACLE_GROUP=dba
export ORACLE_OWNER_PATH=/home/oracle
export ORA_BDUMP_DIR=/data/u01/app/oracle/admin/CCOM/bdump/
export LD_LIBRARY_PATH=/u01/v901/lib:/u01/v901/lib64:/usr/dt/lib
##############End Initialization parameters ################################

# make a backup copy of the init.ora file
cp $ORACLE_HOME/dbs/init$ORACLE_SID.ora $BACKUP_LOC

# make a backup copy of the orapwd file
cp $ORACLE_HOME/dbs/orapw$ORACLE_SID $BACKUP_LOC

# start the rman backup
$ORACLE_HOME/bin/rman target / << EOF
backup database plus archivelog tag="Full_Database";
crosscheck backup;
restore controlfile validate;
restore tablespace SYSTEM validate;
restore archivelog all validate;
list backup of database;
report unrecoverable;
report schema;
report need backup;
report obsolete;
delete noprompt obsolete;
delete noprompt expired backup of database;
delete noprompt expired backup of controlfile;
quit
EOF


# write the SQL file which obtains the list of disaster recovery info
FORMAT_VAR=\'99,999,990.90\';
TS_FREE='SM$TS_FREE'
TS_AVAIL='SM$TS_AVAIL'
exec 3>/home/oracle/DBA_SCRIPTS/RMAN/CCOM_disaster_recovery_get.sql
echo "set echo off">&3
echo "set feedback off">&3
echo "set verify off">&3
echo "set pagesize 0">&3
echo "set linesize 100">&3
echo "spool /data/u03/HOTBCKP/CCOM_disaster_recovery.txt">&3
echo "select '-- file: CCOM_disaster_recovery.txt ' from dual;">&3
echo "select '-- Features: This text file provides a listing of tablespaces' from dual;">&3
echo "select '-- data file names,sizes and user account info. ' from dual;">&3
echo "select '-- ' from dual;">&3
echo "select '-- Used By: DBA for disaster recovery purposes ' from dual;">&3
echo "select '-- ' from dual;">&3
echo "select 'Report produced on: ' || sysdate from dual;">&3
echo "select 'Database name: CCOM' from dual;">&3
echo "select ' ' from dual;">&3
echo "select 'Tablespace' || 'File' || CHR(9) || CHR(9) || CHR(9) || CHR(9) || 'Size (bytes)' from dual;">&3
echo "select tablespace_name || CHR(9) || CHR(9) || file_name || CHR(9) || CHR(9) || bytes from dba_data_files order by tablespace_name;">&3
echo "select ' ' from dual;">&3
echo "select 'Username' || CHR(9) || 'Status' || CHR(9) || 'Tablespace'|| CHR(9) || 'Temp' || CHR(9) || 'Created' from dual;">&3
echo "select username || CHR(9) || CHR(9) || account_status || CHR(9) || default_tablespace || CHR(9) || CHR(9) || temporary_tablespace || CHR(9) || created from dba_users order by username;">&3
echo "select ' ' from dual;">&3
echo "select 'Tablespace' || CHR(9) || CHR(9) || CHR(9) || 'Status' || CHR(9) || CHR(9) || 'Size (Mb)' || CHR(9) || 'Used (Mb)' || CHR(9) || 'Free (Mb)' from dual;">&3
echo "SELECT D.TABLESPACE_NAME,D.STATUS,TO_CHAR((A.BYTES/1024/1024),$FORMAT_VAR),TO_CHAR(((A.BYTES-DECODE(F.BYTES,NULL,0,F.BYTES))/1024/1024),$FORMAT_VAR),TO_CHAR(((A.BYTES/1024/1024)-(A.BYTES-DECODE(F.BYTES,NULL,0,F.BYTES))/1024/1024),$FORMAT_VAR) FROM DBA_TABLESPACES D,SYS.$TS_AVAIL A,SYS.$TS_FREE F WHERE D.TABLESPACE_NAME = A.TABLESPACE_NAME AND F.TABLESPACE_NAME(+)=D.TABLESPACE_NAME;">&3
echo "spool off">&3


# check for corrupt blocks found during RMAN backup
echo "check for corrupt blocks found during RMAN backup"
# only use the next line if this script needs run via CRON
#su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/sqlplus /nolog"<< EOF

$ORACLE_HOME/bin/sqlplus /nolog<< EOF
connect / as SYSDBA
set echo on
set verify on
set feedback on

-- check for corruption found by RMAN
select * from V\$BACKUP_CORRUPTION;

-- temporarily change the user dump dest so that
-- the text copy of the control file rebuilding sql commands
-- go into the /u01/backup directory
ALTER SYSTEM SET USER_DUMP_DEST='/data/u03/HOTBCKP';
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
ALTER SYSTEM SET USER_DUMP_DEST='/data/u01/app/oracle/admin/CCOM/udump';

-- output the disaster recovery info to /data/u03/HOTBCKP/CCOM_disaster_recovery.txt
@/home/oracle/DBA_SCRIPTS/RMAN/CCOM_disaster_recovery_get.sql
exit;

# ---------------------- Troubleshooting ---------------
# to solve RMAN 6089 error - archivelog file not found, after file deleted
# after being logged into RMAN
# CHANGE ARCHIVELOG ALL VALIDATE;





Another one!!!!
#/bin/bash

# --------------------------------------------------------------------------
# File:        backup_database.bash
#
# Autor:       Martin Zahn, Akadia AG, 10.10.2008
#
# Purpose:     Backup Oracle Database using RMAN (Physical Backup)
#              and DataPump (Logical  Backup)
#
# Usage:       backup_database.bash 1>${BACKUP_LOG} 2>&1
#              as a Unix User which belongs to the DBA group
# --------------------------------------------------------------------------
# DEBUG
# set -x
#
# Define Mail Recipient(s)
# ------------------------
SENDTO="<your-mail-address>"
export SENDTO

# Setup ORACLE Environment
# ------------------------
ORAENV=/export/home/oracle/admin
ORACLE_SID=$1
if [ -z "${ORACLE_SID}" ]
then
   echo "Usage: $0 ORACLE_SID"
   exit 1
fi

if [ ! -f ${ORAENV}/${ORACLE_SID}/.oraenv ]
then
   echo "Couldn't find  ${ORAENV}/${ORACLE_SID}/.oraenv to set Oracle Environment ... exiting"
   exit 1
else
   .  ${ORAENV}/${ORACLE_SID}/.oraenv
fi

# Logfile
# -------
BACKUP_LOG=/var/log/oracle/backup_database.log
export BACKUP_LOG

# Check that User belongs to the DBA Group
# ----------------------------------------
DBA_GROUP=`/usr/bin/id -G -n | grep "dba" | wc -l`
if [ "${DBA_GROUP}" != "1" ]
then
   exit 1
fi

# Keep Backup online for X days
# -----------------------------
BACKUP_WINDOW=1
export BACKUP_WINDOW

# Setup ORACLE Environment
# ------------------------
echo $0
exit 0

if [ ! -f ../.oraenv ]
then
   echo "Couldn't find ../.oraenv to set Oracle Environment ... exiting"
   exit 1
else
   . ../.oraenv
fi

# Set Backup Destination
# ----------------------
BACKUP_DATE=`date +%Y%m%d`; export BACKUP_DATE
BACKUP_DEST=${ORACLE_BASE}/work/backup/${ORACLE_SID}
cd ${BACKUP_DEST} 1>/dev/null 2>&1
if [ `pwd` != ${BACKUP_DEST} ]
then
   echo "Backup Directory: ${BACKUP_DEST} not exists ... exiting"
   exit 1
fi

# Set DataPump Parameters
# -----------------------
EXPORT_FILE=${BACKUP_DATE}_datapump_full_${ORACLE_SID}.dp
export EXPORT_FILE

EXPORT_LOG=${BACKUP_DATE}_datapump_full_${ORACLE_SID}.log
export EXPORT_LOG

ORACLE_DATAPUMP_DIR=dp${ORACLE_SID};
export ORACLE_DATAPUMP_DIR

# Backup Parameterfiles
# ---------------------
if [ -f ${ORACLE_HOME}/dbs/init${ORACLE_SID}.ora ]
then
   cp ${ORACLE_HOME}/dbs/init${ORACLE_SID}.ora ${BACKUP_DEST}/${BACKUP_DATE}_init${ORACLE_SID}.ora
fi
if [ -f ${ORACLE_HOME}/dbs/spfile${ORACLE_SID}.ora ]
then
   cp ${ORACLE_HOME}/dbs/spfile${ORACLE_SID}.ora ${BACKUP_DEST}/${BACKUP_DATE}_spfile${ORACLE_SID}.ora
fi

# Backup Passwordfile
# -------------------
if [ -f ${ORACLE_HOME}/dbs/orapw${ORACLE_SID} ]
then
   cp ${ORACLE_HOME}/dbs/orapw${ORACLE_SID} ${BACKUP_DEST}/${BACKUP_DATE}_orapw${ORACLE_SID}
fi

# Start the RMAN physical backup
# ------------------------------
${ORACLE_HOME}/bin/rman nocatalog target / <<-EOF
   configure retention policy to redundancy ${BACKUP_WINDOW};
   configure retention policy to recovery window of ${BACKUP_WINDOW} days;
   configure backup optimization on;
   configure controlfile autobackup off;
   configure default device type to disk;
   configure device type disk parallelism 1 backup type to compressed backupset;
   configure datafile backup copies for device type disk to 1;
   configure channel device type Disk format '${BACKUP_DEST}/${BACKUP_DATE}_${ORACLE_SID}_datafile_%s_%p.bak';
   configure maxsetsize to unlimited;
   configure snapshot controlfile name to '${BACKUP_DEST}/${BACKUP_DATE}_${ORACLE_SID}_snapshot_controlfile';
   set controlfile autobackup format for device type Disk to '${BACKUP_DEST}/${BACKUP_DATE}_${ORACLE_SID}_controlfile_%F';
   show all;

   run {
     allocate channel ch1 type Disk maxpiecesize = 1900M;
     backup full database noexclude
     include current controlfile
     format '${BACKUP_DEST}/${BACKUP_DATE}_${ORACLE_SID}_datafile_%s_%p.bak'
     tag 'datafile_daily';
   }

   run {
     allocate channel ch1 type Disk maxpiecesize = 1900M;
     backup archivelog all
     delete all input
     format '${BACKUP_DEST}/${BACKUP_DATE}_${ORACLE_SID}_archivelog_%s_%p.bak'
     tag 'archivelog_daily';
   }

   run {
      allocate channel ch1 type Disk maxpiecesize = 1900M;
      backup format '${BACKUP_DEST}/${BACKUP_DATE}_${ORACLE_SID}_controlfile_%s.bak' current controlfile;
   }

   crosscheck backup;

   # -----------------------------------------------------------------------
   # The following commands can be run any time to check if RMAN is capable
   # of restoring database/tablespace using existing backups.
   # -----------------------------------------------------------------------
   # restore controlfile validate;
   # restore database validate;
   # restore archivelog all validate;
   # -----------------------------------------------------------------------
   list backup of database;
   report unrecoverable;
   report schema;
   report need backup;
   report obsolete;
   delete noprompt expired backup of database;
   delete noprompt expired backup of controlfile;
   delete noprompt expired backup of archivelog all;
   delete noprompt obsolete recovery window of ${BACKUP_WINDOW} days;
   quit
EOF
if [ "$?" != 0 ]
then
   cat ${BACKUP_LOG} | mail -s "`uname -n`: Oracle RMAN Backup for ${ORACLE_SID} failed." ${SENDTO}
   exit 1
fi

# Store the old Password for SYSTEM
# ---------------------------------
${ORACLE_HOME}/bin/sqlplus -s "/ as sysdba" <<-SAVE_PASSWORD 1>/dev/null 2>&1
   set heading off
   set pagesize 0
   set tab off
   set trimspool on
   spool /tmp/old_password.lst
   SELECT password FROM dba_users WHERE UPPER(username) = 'SYSTEM';
   spool off;
SAVE_PASSWORD

OLD_PASSWOD=`cat /tmp/old_password.lst | sed -e 's/^$//' -e '/^ *$/d'`
export OLD_PASSWOD
rm -f /tmp/old_password.lst 1>/dev/null 2>&1

# Change temporarily the SYSTEM Password
# --------------------------------------
${ORACLE_HOME}/bin/sqlplus -s "/ as sysdba" <<-TMP_PASSWORD 1>/dev/null 2>&1
   ALTER USER SYSTEM IDENTIFIED BY manager;
TMP_PASSWORD

# Create DataPump Directory
# -------------------------
${ORACLE_HOME}/bin/sqlplus -s "system/manager" <<-DATA_PUMP_DIR 1>/dev/null 2>&1
   CREATE OR REPLACE DIRECTORY ${ORACLE_DATAPUMP_DIR} AS '${BACKUP_DEST}';
   exit;
DATA_PUMP_DIR

# Start the DataPump logical backup
# ---------------------------------
cd ${BACKUP_DEST}
ORACLE_DATAPUMP_USR_PWD="system/manager";
export ORACLE_DATAPUMP_USR_PWD
${ORACLE_HOME}/bin/expdp userid=${ORACLE_DATAPUMP_USR_PWD} directory=${ORACLE_DATAPUMP_DIR} parallel=5 full=y dumpfile=${EXPORT_FILE} logfile=${EXPORT_LOG}
gzip ${EXPORT_FILE}

# Reset the SYSTEM Password
# -------------------------
${ORACLE_HOME}/bin/sqlplus -s "/ as sysdba" <<-RESET_PASSWORD 1>/dev/null 2>&1
   ALTER USER SYSTEM IDENTIFIED BY VALUES '${OLD_PASSWOD}';
RESET_PASSWORD

# Create TAR/GZIP file with all the backuped files
# ------------------------------------------------
cd ${BACKUP_DEST}
if [ "`pwd`" = "${BACKUP_DEST}" ]
then
   tar czvf ${BACKUP_DATE}_backupset_${ORACLE_SID}.tgz ${BACKUP_DATE}_*
   find . -type f -mtime +${BACKUP_WINDOW} -exec rm -rf {} \;
fi

exit;






#!/bin/ksh
# -----------------------------------------------------------------------
# Filename:   purgecat.ksh
# Purpose:    RMAN Catalog maintenance:
#             Delete RMAN backupsets older than a specified number of days
# Author:     Frank Naude, Oracle FAQ
# -----------------------------------------------------------------------

DAYSTOKEEP=60

# -- Do not change anything below this line ---------------------------
CMDFILE=/tmp/rmanpurge$$.rcv
LOGFILE=/tmp/rmanprige$$.log

if [ ! -x $ORACLE_HOME/bin/rman ]; then
   echo "ERROR: RMAN not found or ORACLE_HOME not set."
   exit 8
fi
if [ ! $ORACLE_SID ]; then
   echo "ERROR: ORACLE_SID not set."
   exit 8
fi
if [ ! $1 ]; then
   echo "USAGE: $0 CatalogConnectString"
   exit;
else
   RCVCAT=$1
fi;

echo "Delete RMAN backupsets older than $DAYSTOKEEP days for db $ORACLE_SID."
echo "ALLOCATE CHANNEL FOR DELETE TYPE 'SBT_TAPE';" >>$CMDFILE

# List all old entries that needs to be deleted
(rman rcvcat $RCVCAT target / <<-EOF
        list backupset of database
                from time 'SYSDATE-3000' until time 'SYSDATE-$DAYSTOKEEP';
        exit;
        EOF
) | grep RMAN-06233 | while read filler key filler filler date rest
do
        echo "# Delete backupset $key dated $date..." >>$CMDFILE
        echo "CHANGE BACKUPSET $key DELETE;"          >>$CMDFILE
done

echo "RELEASE CHANNEL;" >>$CMDFILE

# Delete the old entries
rman rcvcat $RCVCAT target / cmdfile $CMDFILE

echo "Done!"


Another FULL DB Backup Script, with incremental options
This Korn Shell script incorporates the RMAN scripts for Oracle database backup.
It takes incremental or full db backup, deletes the old archived logs and also crosscheck and deletes the two weeks old disk backups.

#!/bin/ksh
################################################################################
#* Name: RMAN_Incremental_Backup.ksh
#*
#* Date: May, 07, 2008
#*
#* Author  : Diego Pafumi
#*
#* Description: Perform Incremental RMAN Backup
#*
#* Exceptions:   No handled errors.
#*
#* Usage: Incremental_RMAN_Backup.ksh
#*
#* Modifications: NONE
#*
##############Initialization parameters ################################
#Set env info for oracle
. /home/oracle/DBA_SCRIPTS/RMAN/Ora_Param.ksh
export DATESTAMP=`date '+%C%y%m%d'`        #Will show 20080508
export LOG_FILE="Full_RMAN_Backup.txt"
export BACKUP_LOC=/data/u03/HOTBCKP
cd /home/oracle/DBA_SCRIPTS/RMAN
export NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS";
export NLS_LANG=AMERICAN_AMERICA.UTF8;

#Get Date of Week
DOW=`date '+%a'`
CTLBKFILE="'/data/u03/HOTBCKP/`date '+%Y-%m-%d_%H%M%S'`_ctl_bk'"
LEVEL0="'/data/u03/HOTBCKP/`date '+%Y-%m-%d_%H%M%S'`_level0_%U'"
LEVEL1="'/data/u03/HOTBCKP/`date '+%Y-%m-%d_%H%M%S'`_level1_%U'"
LEVEL2="'/data/u03/HOTBCKP/`date '+%Y-%m-%d_%H%M%S'`_level2_%U'"
##############End Initialization parameters ################################

############### FUNCTIONS ################
CTLBK()
{
$ORACLE_HOME/bin/sqlplus / as sysdba << EOF
alter system switch logfile;
alter database backup controlfile to $CTLBKFILE;
exit;
EOF
}

XCHECK()
{
$ORACLE_HOME/bin/rman target / msglog Incremental_CHECK_RMAN_Backup_${DATESTAMP}.log << EOF
allocate channel for maintenance type disk;
delete noprompt force obsolete;
delete noprompt force expired backup;
delete noprompt force expired archivelog all;
crosscheck backup;
crosscheck archivelog all;
release channel ;
exit;
EOF
}

INCR0()
{
$ORACLE_HOME/bin/rman target=/ nocatalog msglog Incremental_RMAN_Backup_${DATESTAMP}.log << EOF
run {
alter system archive log current;
backup incremental level 0 tag = "Level_0_Backup" format $LEVEL0 database plus archivelog;
}
EOF
}

INCR1()
{
$ORACLE_HOME/bin/rman target=/ nocatalog msglog Incremental_RMAN_Backup_${DATESTAMP}.log << EOF
run {
alter system archive log current;
backup incremental level 1 tag = "Level_1_Backup" format $LEVEL1 database plus archivelog;
}
EOF
}

INCR2()
{
$ORACLE_HOME/bin/rman target=/ nocatalog msglog Incremental_RMAN_Backup_${DATESTAMP}.log << EOF
run {
alter system archive log current;
backup incremental level 2 tag = "Level_2_Backup" format $LEVEL2 database plus archivelog;
}
EOF
}

case $DOW in
Mon ) INCR1; CTLBK ;;
Tue ) INCR0; XCHECK; CTLBK ;;
Wed ) INCR0; CTLBK ;;
Thu ) INCR1; XCHECK; CTLBK ;;
Fri ) INCR2; CTLBK ;;
Sat ) INCR0; XCHECK; CTLBK ;;
Sun ) INCR0; XCHECK; CTLBK ;;
esac

$ORACLE_HOME/bin/rman target=/ nocatalog msglog Incremental_LIST_SUMMARY_RMAN_Backup_${DATESTAMP}.log << EOF
list backup summary;
exit;
EOF


#Copy some other files that are important
$ORACLE_HOME/bin/rman target=/ nocatalog  << EOF
run {
create pfile from spfile;
host 'cp $ORACLE_HOME/network/admin/tnsnames.ora $BACKUP_LOC';
host 'cp $ORACLE_HOME/network/admin/listener.ora $BACKUP_LOC';
host 'cp $ORACLE_HOME/dbs/initCCOM.ora $BACKUP_LOC';
EOF

#gzip $BACKUP_LOC/*_1_1
#find $BACKUP_LOC/*.gz -mtime +3 -exec rm {} \;

#Delete the ARCH LOGS from RMAN Directory
find /data/u03/app/oracle/oradata/CCOM/ARCH_RMAN/CCOM1_*.dbf -mtime +5 -exec rm {} \;

mail -s "Incremental_RMAN_Backup" mail@someone.com < Incremental_RMAN_Backup_${DATESTAMP}.log
mail -s "Incremental_CHECK_Backup" mail@someone.com < Incremental_CHECK_RMAN_Backup_${DATESTAMP}.log
mail -s "Incremental_LIST_SUMMARY_Backup" mail@someone.com < Incremental_LIST_SUMMARY_RMAN_Backup_${DATESTAMP}.log




Collected Resources
Oracle10g - Getting Started with Recovery Manager (RMAN)  Note:360416.1  GREAT!!
RMAN: RAC Backup and Recovery using RMAN  Note: 243760.1
Overview of the RMAN Environment
Oracle RMAN Demo by psoug
Duplicate a DB using RMAN 11gr2
Creating a Duplicate Database Note: 73912.1
Restoring an RMAN Backup to Another Node Note: 73974.1
Duplicate Database in Oracle9i / Oracle 10G Note: 228257.1
Oracle10G RMAN Database Duplication Note: 259694.1
RMAN and Flash Recovery Area Note: 305796.1
Backup Backupset  Note: 151123.1
Create A Production (Full or Partial) Duplicate On The Same Host   Note: 388424.1
Backup and Recovery Practices Best Practices. Note: 388422.1
Creating a 9i Data Guard Database with RMAN (Recovery Manager) Note:183570.1
Step By Step Guide To Create Physical Standby Database Using RMAN Note:469493.1
Creating a Standby Database on a new host Note:374069.1
11gr2 Backup and Recovery


PRINTED and NOT USED
RAC Survival Kit: Real Application Clusters Troubleshooting and Information, Note:203226.1
RAC Survivial Kit: Troubleshooting Manual Backups, Note:207059.1
How to delete Archivelog from multiple locations for a RAC database, Note:360473.1
RMAN Backup Shell Script Example, Note:137181.1