Using RMAN to Back Up and Restore Files in a Data Guard Configuration
Recovery Manager (RMAN) can be used to back up and recover a standby database. Backups that you make on a physical standby database are usable at a primary database or another standby database.
A recovery catalog is required when you use RMAN in a Data Guard configuration containing physical standby databases. Metadata for the primary database and all standby databases is stored in the recovery catalog.
Oracle Maximum Availability Architecture (MAA) best practices recommend that backups be taken at both the primary and the standby databases to:
- Reduce mean time to recover (MTTR)
- Handle outages at multiple sites
- Avoid introducing new site practices during switchover and failover
Offloading Backups to a Physical Standby
RMAN can back up the standby database and its associated archived redo logs. Standby backups of data files, control files, and archived redo logs are fully interchangeable with primary database backups. You can execute the RESTORE command to restore a backup of a standby data file to the primary database, and you can restore a backup of a primary data file to the standby database.
Both the primary database and standby database must use the same recovery catalog. Note that you do not need to register the standby database in the catalog if the primary is already registered; simply connect to the standby database and execute the BACKUP command.
Restrictions and Usage Notes
If physical standby database backups are to be usable for restore jobs at the primary site, you must be connected to the recovery catalog when backing up the standby database or you must resynchronize the physical standby database shortly after the backup. This step is necessary because there is no way for the primary database to know about the standby backups unless the backup records are stored in the recovery catalog.
When you back up the standby database, you must connect to the standby database with the TARGET keyword (not the AUXILIARY keyword). The standby database essentially “substitutes” for the primary database during the backup.
Association and Accessibility of RMAN Backups
The recovery catalog tracks the files in the Oracle Data Guard environment by associating every database file or backup file with a DB_UNIQUE_NAME. The database that creates a file is associated with the file. For example, if RMAN backs up the database with the unique name “standby1,” then standby1 is associated with this backup. A backup remains associated with the database that created it unless you use the CHANGE … RESET DB_UNIQUE_NAME to associate the backup with a different database.
The accessibility of a backup is different from its association. In an Oracle 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 the row describing it in all databases. If a backup file is not associated with any database, the row describing it in the recovery catalog view 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 backups. 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.
Backup and Recovery of a Logical Standby Database
You can back up your logical standby database by using the same method that you use for your primary database. The backup files are not interchangeable with the primary database. Remember that this is a different database. You can use the same recovery techniques as you use with any other database for loss of data files or online log files. You must use the backups of the logical (not the primary) database.
If the entire logical standby database is lost, you must re-create the logical standby. If you lose all copies of your control file, you must use a binary copy of the control file when performing the recovery. (Using a trace file or the CREATE CONTROLFILE command for control file recovery does not create a logical standby control file.) Make a binary copy of the control file by doing either of the following:
1. Shut down the logical standby and copy the control file to a backup.
2. Issue the following command while the logical standby database is open:
ALTER DATABASE BACKUP CONTROLFILE TO '[filename]';
This command creates a binary copy of the control file with the name that you supply.
Using the RMAN Recovery Catalog in a Data Guard Configuration
In a Data Guard configuration, you should use an RMAN recovery catalog. This permits backups taken on one database server to be restored to another database server. If you use only the control file as the RMAN repository, the primary database will have no knowledge of backups taken on the standby database.
Creating the Recovery Catalog
1. Configure the database in which you want to store the recovery catalog.
a. Determine the database in which you will install the recovery catalog schema. Be sure to consider your backup and recovery procedures for this database.
b. Create a tablespace for the recovery catalog, which becomes the default tablespace for the recovery catalog owner. The amount of space required by the recovery catalog schema depends on the number of databases monitored by the catalog. The typical space requirement is 15 MB for each database registered in the recovery catalog.
SQL> CREATE TABLESPACE rc_ts DATAFILE SIZE 30M;
2. Create a user to serve as the recovery catalog owner. Set the default tablespace for this user to the tablespace that you created for the recovery catalog. Be sure to provide UNLIMITED quota on this tablespace for the user. After creating the user, grant the RECOVERY_CATALOG_OWNER role to the user.
SQL> CREATE USER rcowner IDENTIFIED BY rcpass 2 TEMPORARY TABLESPACE temp 3 DEFAULT TABLESPACE rc_ts 4 QUOTA UNLIMITED ON rc_ts;
SQL> GRANT recovery_catalog_owner TO rcowner;
3. Use the CREATE CATALOG RMAN command to create the catalog tables in the default tablespace of the catalog owner.
$ rman RMAN> CONNECT CATAL RMAN> CONNECT CATALOG rcowner/rcpass rcowner/rcpass RMAN> CREATE CATALOG;
Registering a Database in the Recovery Catalog
After creating the recovery catalog, you must register the target databases in the recovery catalog. Only the primary database is registered by using the REGISTER DATABASE command. A standby database is automatically registered in the recovery catalog when you connect to it or when the CONFIGURE DB_UNIQUE_NAME command is executed to specify the net service name for the physical standby database.
To register your primary database:
1. Invoke RMAN and connect to the recovery catalog database and to the target database, as shown in the following example:
rman TARGET / CATALOG rcowner/rcpass@reccatdb
2. Ensure that the target database is mounted or open.
3. Issue the REGISTER command to register the target database in the recovery catalog:
RMAN> REGISTER DATABASE;
Setting Persistent Configuration Settings
Use the CONFIGURE RMAN command to register and configure settings for databases in your Data Guard configuration. RMAN uses the DB UNIQUE NAME initialization parameter to distinguish the databases in the Data Guard configuration. You can use the CONFIGURE command with the FOR DB_UNIQUE_NAME clause to create a persistent configuration for a database in a Data Guard configuration without connecting to the database as TARGET.
Use the SET DBID command to set the DBID in the RMAN session so that you can configure a standby database when RMAN is not connected as TARGET to a database in the Data Guard environment. You can also use this technique to create a configuration for a standby database that is not yet created.
RMAN> configure controlfile autobackup on for db_unique_name boston; new RMAN configuration parameters: CONFIGURE CONTROLFILE AUTOBACKUP ON; new RMAN configuration parameters are successfully stored
RMAN must be connected to a recovery catalog when you create or alter a configuration for a database in the Data Guard environment.
Configuring a Persistent Setting: Example
[oracle@host01 ~]$ rman target sys/oracle_4U@boston catalog rcowner/rcpass@emrep Recovery Manager: Release 12.1.0.1.0 - Production on Wed Dec 18 16:04:49 2013 Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved. connected to target database: BOSTON (DBID=2524687871, not open) connected to recovery catalog database RMAN> configure controlfile autobackup on; new RMAN configuration parameters: CONFIGURE CONTROLFILE AUTOBACKUP ON; new RMAN configuration parameters are successfully stored RMAN> show all for db_unique_name boston; RMAN configuration parameters for database with db_unique_name BOSTON are: CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default CONFIGURE BACKUP OPTIMIZATION OFF; # default CONFIGURE DEFAULT DEVICE TYPE TO DISK # default CONFIGURE CONTROLFILE AUTOBACKUP ON; # default CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # 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 ENCRYPTION FOR DATABASE OFF; # default CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; #default CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/12.1.0/dbhome_1/dbs/snapcf_boston.f'; # default
Setting RMAN Persistent Configuration Parameters on the Primary Database
1. Configure the retention policy to determine the backups that need to be kept to recover to the specified point in time. This setting specifies that necessary backups should be kept to perform database recovery to any point in time within the specified number of days.
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF [n] DAYS;
Execute the DELETE OBSOLETE command to delete any backups that are not required by the retention policy.
2. Use the CONFIGURE ARCHIVELOG DELETION POLICY command to specify when archived logs can be deleted.
– To delete logs after ensuring that they shipped to all destinations:
CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO ALL STANDBY;
– To delete logs after ensuring that they were applied on all standby destinations:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
3. Configure the connect identifier for the primary database and all standby databases so that RMAN can connect remotely and perform resynchronization when the RESYNC CATALOG FROM DB_UNIQUE_NAME command is executed. The connect identifier is the Oracle Net service name that can be used from any database site to connect to the specified database site. CONFIGURE DB_UNIQUE_NAME implicitly registers the database.
RMAN> CONFIGURE DB_UNIQUE_NAME boston CONNECT IDENTIFIER 'boston'; RMAN> CONFIGURE DB_UNIQUE_NAME london CONNECT IDENTIFIER 'london'
Setting RMAN Persistent Configuration Parameters on the Physical Standby Database
On the physical standby database that you will be using for backups, set RMAN persistent configuration parameters as follows:
– Enable automatic backup of the control file and the server parameter file.
CONFIGURE CONTROLFILE AUTOBACKUP ON;
– Set backup optimization to ON so that RMAN does not back up data files for which there is valid backup with the same checkpoint.
CONFIGURE BACKUP OPTIMIZATION ON;
– Specify the archived log file deletion policy to indicate when archived redo log files are deleted.
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DEVICE TYPE DISK;
When backing up to tape, configure tape channels as required by the media management software.
Setting RMAN Persistent Configuration Parameters on the Other Standby Databases
On the standby databases where you will not be performing backups, specify that the archived logs can be deleted after they are applied at the standby database:
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
Configuring Daily Incremental Backups
Oracle recommends that you implement a daily incremental backup strategy. In this backup strategy, data file image copies are rolled forward with the latest incremental backups. The resulting up-to-date data file image copy is used by RMAN for media recovery.
Perform a full database backup 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 during either day. Beginning on day three, the previous day’s incremental backup is merged with the data file copy and a current incremental backup is taken, allowing 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.
You can create an RMAN script to take all of the backups as shown below:
- RESYNC CATALOG FROM DB_UNIQUE_NAME ALL: Resynchronizes the information from all database sites (primary and other standby databases) in the Data Guard configuration that are known to the recovery catalog.
- RECOVER COPY OF DATABASE WITH TAG ‘dgbkup’: Rolls forward the level 0 copy of the database by applying the level 1 incremental backup taken the day before.
- On day 1, there is no roll forward because there is no incremental level 1 backup.
- On day 2 there is no roll forward because there is only a level 0 incremental backup at this time.
- On day 3 and the following days, the roll forward is performed using the level 1 incremental backup created on the previous day.
- BACKUP DEVICE TYPE DISK INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG ‘dgbkup’ DATABASE: Creates a new level 1 incremental backup.
- On day 1, a level 0 incremental backup is created.
- On day 2 and the following days a level 1 incremental backup is created
- BACKUP DEVICE TYPE DISK ARCHIVELOG ALL: Backs up archived logs to disk.
- BACKUP BACKUPSET ALL: Backs up any backup sets that were created as a result of incremental backup.
- 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 flash recovery area, they are automatically deleted when more open disk space is required. Therefore, you need to use this command only if you explicitly want to delete logs each day.
A single RMAN script can be created to implement this strategy:
RESYNC CATALOG FROM DB UNIQUE NAME ALL; RECOVER COPY OF DATABASE WITH TAG 'dgbkup'; BACKUP DEVICE TYPE DISK INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'dgbkup' DATABASE; BACKUP DEVICE TYPE SBT ARCHIVELOG ALL; BACKUP BACKUPSET ALL; DELETE ARCHIVELOG ALL;
Recovering from the Loss of a Data File on the Primary Database
- Using backups
- Using a file on the standby database
These two methods are covered on the following sections.
Using a Backup to Recover a Data File on the Primary Database
Invoke RMAN and connect to the primary database and the recovery catalog.
1. Execute the RESTORE DATAFILE command to restore the data file.
RMAN> CONNECT TARGET sys@boston RMAN> RESTORE DATAFILE 5,6; RMAN> RECOVER DATAFILE 5,6;
2. Execute the RECOVER DATAFILE command to recover the data file.
RMAN> CONNECT TARGET sys@boston RMAN> RESTORE TABLESPACE tbs_name1,tbs_name2; RMAN> RECOVER TABLESPACE tbs_name1,tbs_name2;
Using a Physical Standby Database Data File to Recover a Data File on the Primary Database
To use a current data file from the physical standby database to recover a data file on the primary database:
1. Connect to the standby database as the target database:
RMAN> CONNECT TARGET sys/oracle@london
Connect to the primary database as the auxiliary database:
RMAN> CONNECT AUXILIARY sys/oracle@boston
2. Back up the data file on the standby host across the network to a location on the primary host, as in the following example:
RMAN> BACKUP AS COPY DATAFILE 5 BACKUP AS COPY DATAFILE 5 AUXILIARY FORMAT '/u02/oracle/restore/df5copy.dbf';
3. Exit the RMAN session. Invoke RMAN again and connect to the primary database as the target database and connect to the recovery catalog, as in this example:
RMAN> CONNECT TARGET sys/oracle 4U@boston CATALOG rcowner/rcpass@em12;
4. Use the CATALOG DATAFILECOPY command to catalog this data file copy so that RMAN can use it.
RMAN> CATALOG DATAFILECOPY '/u02/oracle/restore/df5copy.dbf';
5. Use the SWITCH DATAFILE command to switch the data file copy so that the cataloged file becomes the current data file.
RUN {
SET NEWNAME FOR DATAFILE 5 TO
'/u02/oracle/restore/df5copy.dbf';
SWITCH DATAFILE 5;
RECOVER DATAFILE 5;
}
Recovering a Data File on the Standby Database
Using a Backup to Recover a Data File on the Standby Database
To recover a data file on the standby database, you must restore the lost file to the standby database from the backup by using the RESTORE DATAFILE RMAN command. If all archived redo log files that are required for recovery of the file are accessible on disk by the standby database, you only need to restart Redo Apply.
If the archived redo log files that are required for recovery are not accessible on disk, use RMAN to recover the restored data files to an SCN or log sequence that is greater than the last log applied to the standby database. Next, restart Redo Apply to continue the application of redo data.
Using a Data File from the Primary Database
You can use a current data file from the primary database to recover a file on the standby database.
Enhancements to Block Media Recovery
In past releases of Oracle Database, if a block was corrupted, you had to identify the corrupt block and manually perform block media recovery by executing the RECOVER … BLOCK command. The Automatic Block Repair feature enables block media recovery to use blocks from a physical standby database to perform the recovery without manual intervention. The physical standby database must have real-time query enabled to take advantage of this feature.
When a query is executed on a physical standby database configured with real-time query and a corrupted block is detected, a valid block is retrieved from the primary database. When a corrupted block is detected during a recovery operation on the standby database, the recovery process will retrieve a valid block from the primary database.
This feature reduces the amount of time that production data cannot be accessed due to block corruption by automatically repairing the corruption as soon as it is detected. Block recovery time is reduced because up-to-date good blocks from a real-time, synchronized physical standby database are used rather than blocks from disk or tape backups or flashback logs.
Automatic Block Repair also enables the automatic repair of corrupt blocks on the physical standby database. Block media recovery is performed by using blocks retrieved from the primary database. Real-time query must be enabled on the physical standby database to take advantage of this feature.
Executing the RECOVER BLOCK Command
In past releases of Oracle Database, when you executed the RECOVER … BLOCK command to perform block media recovery, RMAN searched the flashback logs for good copies of the blocks, and then searched in full or level 0 incremental backups. Now RMAN first attempts to restore blocks from a physical standby database that is configured with real-time query.
RECOVER DATAFILE 6 BLOCK 3; ### Recover a single block RECOVER ### Recover multiple blocks DATAFILE 2 BLOCK 43,79 ### in multiple data files DATAFILE 6 BLOCK 183;
If RMAN does not find a valid block in the physical standby database, the flashback logs are searched. Finally, the full or level 0 incremental backups are searched.
For RMAN to be able to search a standby database for good copies of corrupt blocks, the target database must be associated with a physical standby database in a Data Guard environment. In addition, the physical standby database must be open read-only in managed recovery.
Excluding the Standby Database
By default, the RECOVER … BLOCK command tries to retrieve a good block from a physical standby database. You can disable the search on the physical standby database by specifying the EXCLUDE STANDBY clause with the command. You may use the EXCLUDE STANDBY clause when you know there is a network issue or other reason that the standby database is not available.
RECOVER BLOCK … EXCLUDE STANDBY