Friday, 31 August 2012

11g R2 - Reducing overheads in a Data Guard Environment

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 In a Data Guard environment to reduce overheads of fast incremental backups on the primary database

RMAN can perform backups with minimal effect on the primary database and quickly recover from the loss of individual datafiles, or the entire database. RMAN and Data Guard can be used together to streamline the administration of a Data Guard setup.

You can’t use a logical standby database to backup the primary database, because the logical standby database is not a block for block copy of the primary database.About RMAN File Management in a Data Guard configuration

RMAN behaves transparently across various physical databases in a Data Guard environment, by using the recovery catalog metadata. You can backup a tablespace a physical standby database, and restore and recover it on the primary database. You can backup a tablespace on a primary database and restore and recover it on a physical standby database. You can’t do this with Logical standby databases.

Backups of standby control files and nonstandby control files are interchangeable. You can restore a standby control file on a primary database and a primary control file on a physical standby database.

This means you can offload control file backups to one database in a Data Guard environment. RMAN will automatically update filenames for database files during restore and recovery at the databases.

Association of Backups in a Data Guard Environment

In the Data Guard environment,the recovery catalog associates every database file or backup file with a DB_UNIQUE_NAME. The database that creates the file is associated with that file.

When RMAN backs up the database called standby1, then the DB_UNIQUE_NAME standby1 is associated with this backup. The backup will remain associated with the database that created it, unless you associate the backup with a different database, by changing the DB_UNIQUE_NAME, by invoking: CHANGE … RESET DB_UNIQUE_NAME

Accessibility of Backups in a Data Guard Environment

The accessibility of a backup is different from its association. In a Data Guard environment, the recovery catalog considers disk backups as accessible only to the database with which it is associated, whereas tape backups created on one database are accessible to all databases. If a backup file is not associated with any database, then the row describing it in the recovery catalog, shows null for the SITE_KEY column. By default, RMAN associates files whose SITE_KEY is NULL with the target database.

RMAN commands such as BACKUP, RESTORE, AND CROSSCHECK work on any accessible backup. For example, for a RECOVER COPY operation, RMAN considers only image copies that are associated with the database as eligible to be recovered.

RMAN considers the incremental backups on disk and tape as eligible to recover the image copies. In a database recovery, RMAN considers only the disk backups associated with the database and all files on tape as eligible to be restored.

Let’s assume that the database PROD resides on a different host to the database STANDBY1. RMAN backs up datafile 1 on prod to /prod/backup/datafile1.dbf, as well as a copy of it to tape. RMAN backups datafile 1 on the standby host to /standby/backup/datafile1.dbf, as well as a copy to tape.

If you are connected with RMAN to PROD, then you cannot use RMAN operations to manipulate the backup located on /standby/backup/datafile1.dbf. However if you really needed this file, then RMAN would consider the backup of this same file on tape to be eligible to be restored.

If you don’t have a tape backup, then you can FTP a backup from the standby host to the production host, connect as TARGET to the production host, then you can CATALOG the backup. Once you have catalogued the backup in the target database, then that backup is associated with the target database, in this case PROD database.About RMAN Configuration in a Data Guard Environment

In a Data Guard environment, you can offload the process of backing up control files, datafiles, and archived logs to the standby system. By doing this you minimize the effect of backups on the production system. The backups taken in this way can be used to recover the primary or the standby database.

RMAN uses the DB_UNIQUE_NAME initialization parameter to distinguish one database site from another database site. Therefore it is essential that the uniqueness of DB_UNIQU_NAME be maintained in a Data Guard configuration.

Only the primary database must be explicitly registered using the RMAN> REGISTER DATABASE command. You would issue this command after connecting to the recovery catalog, and the primary database as the target.

You can use the RMAN command CONFIGURE, to set up the various configurations in RMAN. When you use the CONFIGURE command in conjunction with the FOR DB_UNIQUE_NAME option, then RMAN sets the CONFIGURE for the site-specific database, based on the DB_UNIQUE_NAME that was specified.

RMAN> set dbid 1625818167;

Use this if you have not connect to RMAN as TARGET.

RMAN> configure default device type to SBT for db_unique_name jhb;

Recommended RMAN and Oracle Database Configurations

These configurations can contribute towards simplifying backup and recovery operations.

Configuration Assumptions

· The standby database is a physical standby database, and backups are taken only on the standby database.

· An RMAN recovery catalog is required so that backups taken on one database server can be restored to another database server. It is not sufficient to use only the control file, because the RMAN repository on the primary database, will have no knowledge of backups taken on the standby database.

The RMAN recovery catalog organizes backup history and recovery related metadata in a centralized location. The recovery catalog is configured in a database and maintains backup metadata. A control file has space limitations, whereas the recovery catalog does not, and can store more historical data about the backups.

Ideally you should have a catalog server, physically separate from the primary and standby sites. Oracle recommends this in a Data Guard configuration, because a disaster at either site will not affect the ability to recover the latest backups.

· Assume that all databases are using the Oracle Database 11g release 1 or higher.

· Oracle Secure Backup software, or 3rd party media management software is configured with RMAN to make backups to tape.

Oracle Database Configurations on Primary and Standby Databases

The following Oracle Database configurations are recommended on the primary database and all the standby databases in a Data Guard environment.

· Configure a fast recovery area for each database. The recovery area is local to the database.

The fast recovery area is a single storage area or location on a file system of Oracle ASM disk group, where all the files needed for a recovery reside. These files include the control file, online redo logs, archived logs, flashback logs, and RMAN backups.

As new backups and archived logs are created in the fast recovery area, older files are automatically deleted to make room for them. The files are deleted according to the retention period, or when they have been backed up to tertiary storage. Also notifications can be set up to alert the administrator when space consumption in the fast recovery area is nearing its predefined limit. The DBA can then take an action, such as increasing the recovery area space limit, adding disk hardware space, or decreasing the retention period.

You can set the fast recovery area with these initialization parameters:

DB_RECOVERY_FILE_DEST = <mount point or Oracle ASM Disk Group>

DB_RECOVERY_FILE_DEST_SIZE = <disk space quota>

· Use a server parameter file (SPFILE), so that it can be backed up, to save the instance parameters in the backup.

· Enable Flashback Database on the primary and standby databases.

When Flashback Database is enabled, Oracle Database maintains flashback logs in the fast recovery area. These logs can be used to roll the database back to an earlier point in time, without requiring a complete restore.

RMAN configurations at the Primary Database

You can set a number of persistent RMAN configuration settings for each database in the Data Guard environment, in order to simplify the ongoing use of RMAN in the environment. You can configure the backup retention policy, default destinations for backups to tape or disk, default backup device type, etc. You can use the CONFIGURE command to set and change RMAN configurations.

These configurations are recommended at the Primary database site:

· When you connect, connect RMAN to the primary database and to the recovery catalog.

· Configure the retention policy for the database in terms of n days.

RMAN> configure retention policy to recovery window of <n> days;

old RMAN configuration parameters:

CONFIGURE RETENTION POLICY TO REDUNDANCY 2;

new RMAN configuration parameters:

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 5 DAYS;

new RMAN configuration parameters are successfully stored

This configuration lets you keep the backups necessary to perform database recovery to any point in time within the specified number of days.

Use the DELETE OBSOLETE command to delete any backups that are not required to perform recovery within the specified number of days.

· Use the CONFIGURE ARCHIVELOG DELETION POLICY command, to specify when the archived logs can be deleted.

If you want to delete logs after ensuring that they shipped to all destinations:

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO ALL STANDBY;

If you want to delete logs only after ensuring that they were applied to all standby destinations, then use the following configuration.

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED TO ALL STANDBY;

· Configure the connect string for the primary database and all the standby databases, so that RMAN can connect remotely and perform resynchronization when the RESYNC CALTALOG FROM DB_UNIQUE_NAME command is used.

When you connect to the target database, you must provide a net service name. This requirement applies, even if the other database instance from where the resynchronization is done is on the local host.

The Target and remote instances must use the same SYSDBA password, which means that both instances must already have password files. You can create the password file with a single password, so you can start all the database instances with that password file.

If the TNS alias to connect to a standby database in jhb is jhb_connect_str, you can use the following command to configure the connect identifier for the jhb database site: