Saturday 1 September 2012

11g R2 – Backup Procedure



Hi,

My name is Mark Tiger, creator of this blog.  I am an Oracle Certified Professional (OCP DBA 11g).

Gathering information for some DBA tasks can be time-consuming, even although the commands that you need to issue eventually can be over quite quickly.  I have gone through this process over and over again, and have decided to help other Oracle DBA’s in the community.

In this blog, I will give you the details of how to carry out those tasks; that typically need a lot of research, before you can do them.  I will try to present the information in an easy to understand way.  My hope is that this will save you lots of time in research, and help to make you more productive as an Oracle DBA.  The illustrations are primarily meant for Linux, since this is a Platform; that enjoys preference from Oracle.  However they are easily adaptable for versions of UNIX/AIX and windows etc.

11g R2  Backup Procedure

Oracle’s Maximum Availability Architecture (MAA) best practices; recommend that backups should be taken at both the primary and the standby databases to reduce MTTR.  Also to cater for double outages, and to avoid introducing new site practices upon switchover and failover.

Backing up the Server Parameter files:  Prior to Oracle Database 11g, backups of server parameter files (SPFILE) were assumed to be usable at any other standby database.  In practice, it is not possible for all standby databases to use the same SPFILE.

RMAN does not allow an SPFILE backup taken at one database site to be used at another database site.  This restriction is only in place if the COMPATIBLE initialization parameter is set to 11.0.0.

The standby database will allow you to offload all backup operations to one specific standby database, except the backups of SPFILE’s.  However with a COMPATIBLE = 11.0.0 setting, the SPFILE can be backed up to disk and catalogued manually at standby sites where backups are written to tape.  The additional metadata stored in SPFILE backup sets, enables RMAN to identify which database SPFILE is contained in which backup set.  Therefore the appropriate SPFILE backup is chosen during the restore from tape.

Using Disk as Cache for Tape Backups

The fast recovery area on the standby database can serve as a disk cache for tape backup.  Disk is used as the primary storage for backups, with tape providing, long term, archival storage.  Increamental tape backups can be taken daily.  Full tape backups can be taken weekly.

Commands for Daily Tape Backups Using Disk as Cache

Oracle recommends that you take advantage of daily incremental backups.  Datafile image copies can be rolled forward with the latest incremental backups; therefore up to date datafile image copies are available at all times.

RMAN uses the resulting image copy for media recovery, just as it would use a full image copy taken at that system change number(SCN).  But this is without the overhead of performing a full age copy of the database everyday.  An additional advantage is that the time to recover is reduced because the image copy is updated with the latest block changes and fewer redo logs are required to bring the database back to the current state.

To implement daily incremental backups; a full database backup is taken on the first day, followed by an incremental backup on day two.  Archived redo logs can be used to recover the database to any point in either day.  For day three forward, the previous day’s incremental backup is merged with the datafile copy and a current incremental backup is taken.  This allows for a fast recovery to any point within the last day.  Redo logs can be used to recover the database to any point during the current day.

Below is a sample script.  Note that the last line of the script “DELETE ARCHIVELOG ALL”, is only needed if the fast recovery area is not used to store logs.

RMAN> RESYNC CATALOG FROM DB_UNIQUE_NAME ALL;

RMAN> RECOVER COPY OF DATABASE WITH TAG ‘OSS’;

RMAN> BACKUP DEVICE TYPE DISK INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG ‘OSS’ DATABASE;

RMAN> BACKUP DEVICE TYPE SBT ARCHIVELOG ALL;

RMAN> BACKUP BACKUPSET ALL;

RMAN> DELETE ARCHIVELOG ALL;

The standby control file will be automatically backed up at the conclusion of the backup operation because the control file auto backup is enabled.

·         RMAN> RESYNC CATALOG FROM DB_UNIQUE_NAME ALL;

Resynchronizes the information from all other database sites (primary and other standby databases) in the Data Guard setup that is known as the Recovery Catalog.

For RESYNC CATALOG FROM DB_UNIQUE_NAME to work, RMAN should be connected to the target using the Oracle Net service name and all databases must use the same password file.

·         RECOVER COPY OF DATABASE WITH TAG ‘OSS’;

Rolls forward level 0 copy of the database by applying the level 1 incremental backup taken the day before.  If the previous day’s incremental backup of level 1 was tagged ‘OSS’

This incremental is generated by the BACKUP DEVICE TYPE DISK ... DATABASE command.  On the first day this command is run there will be no roll forward because there is no incremental level 1 yet.  A level 0 incremental will be created by the BACKUP DEVICE TYPE DISK ... DATABASE command.  Again on the second day there is no roll forward because there is only a level 0 incremental.  A level 1 incremental tagged OSS will be created by the BACKUP DEVICE TYPE DISK ... DATABASE command.  On the third and following days, the roll forward will be performed using the level 1 incremental tagged OSS created on the previous day.

·         BACKUP DEVICE TYPE DISK INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG ‘OSS’ DATABASE

Create a new level 1 incremental backup.  On the first day this command is run, this wil be a level 0 incremental.  On the second and following days, this will be a level 1 incremental.

·         BACKUP DEVICE TYPE SBT ARCHIVELOG ALL

Backs up archived logs to tape according to the deletion policy in place.

·         BACKUP BACKUPSET ALL

Backs up any backup sets created as a result of incremental backup creation.

·         DELETE ARCHIVELOG ALL

Deletes archived logs according to the log deletion policy set by the CONFIGURE ARCHIVELOG DELETION POLICY command.  If the archived logs are in a fast recovery area, then they are automatically deleted when more open disk space is required.  Therefore you only need to use this command if you explicitly want to delete logs each day.

To back up all recovery-related files to tape, use the following command once a week:

RMAN> BACKUP RECOVERY FILES;

This ensures that all current incremental, image copy, and archived log backups on disk are backed up to tape.

Performing Backups Directly to Tape

Oracle has a Media Management Layer(MML) API.  This MML API allows third-part vendors to build media manager software that works with RMAN and the vendors hardware, to allow backups to sequential media devices like tape drives.

A Media Manager handles loading, unloading, and labelling of sequential media like tapes.  You must install Oracle Secure Backup, or alternatively a third party media management software in order to user RMAN with sequential media deivices.

Take the following steps to perform backups directly to tape:

1.       Connect to RMAN, target the standby database, and the recovery catalog.

2.       Execute the CONFIGURE command.

RMAN> CONFIGURE DEFAULT DEVICE TYPE TO SBT;

Commands for Daily Backups directly to Tape

1.       Connect to RMAN with the target being the standby database, and to the recovery catalog.

2.       Execute these RMAN commands

RMAN> RESYNC CATALOG FROM DB_UNIQUE_NAME ALL;

RMAN> BACKUP AS BACKUPSET INCREMENTAL LEVEL 1 DATABASE PLUS ARCHIVELOG;

RMAN> DELETE ARCHIVELOG ALL;

These commands resynchronize the information from all other databases in the Data Guard environment.  They also create a level 1 incremental backup of the database, including all archived logs.  On the first day this script is run, if no level 0 backups are found, then a level 0 backup is created.

The DELETE ARCHIVELOG ALL command is only necessary if all archived log files are not in a fast recovery area.

Commands for Weekly Backups Directly to Tape

One day a week, take the following steps to perform a weekly backup directly to tape:

1.       Connect to RMAN with standby as the target database, and to the recovery catalog.

2.       Execute the following RMAN commands:

RMAN> BACKUP AS BACKUPSET INCREMENTAL LEVEL 0 DATABASE PLUS ARCHIVELOG;

RMAN> DELETE ARCHIVELOG ALL;

These commands resynchronize the information from all other databases in the Data Guard environment, and create level 0 database backup that includes all archived logs.

The DELETE ARCHIVELOG ALL command is only necessary if all archived log files are not in a fast recovery area.

MarkTiger01@gmail.com

P.S. I am busy preparing Nine books on preparing for the Oracle 11g R2 OCM DBA exam.  Watch this spot for details on the books, as they start becoming available.