5 Recovering Data from Recovery Appliance

This chapter explains how to use backups stored on Recovery Appliance to recover your protected database after a failure.

This chapter contains the following sections:

5.1 Overview of Restoring and Recovering Data from Recovery Appliance

You can recover the entire protected database, one or more data files, or one or more tablespaces. If only certain data blocks in the protected database are corrupt, then you can perform block recovery to repair only the corrupted blocks. The recovery procedures using Recovery Appliance are identical to those used to recover a database within a conventional RMAN environment. The major difference is the use of a Recovery Appliance as the source for recovery data by configuring or allocating an RMAN channel that corresponds to the Recovery Appliance backup module.

You can use Enterprise Manager Cloud Control (Cloud Control) or RMAN to restore and recover protected databases.

5.2 Recovering Protected Databases Using Cloud Control

Cloud Control provides the following techniques to recover protected databases:

  • Oracle Advised Recovery

    Oracle Advised Recovery enables you to recover the protected database by using the automatic repair actions recommended by Data Recovery Advisor. The Data Recovery Advisor automatically diagnoses data failures, assesses their impact, reports these failures to the user, determines appropriate repair options, and executes repairs at the user's request.

    See Also:

    Oracle Database Backup and Recovery User's Guide for more information about performing Oracle advised recovery

  • User Directed Recovery

    This technique performs manual recovery based on the specified criteria. You must provide information such as the objects that must be recovered (database, data files, tablespaces, archived redo logs), whether to perform complete recovery or point-in-time recovery, location to which database files must be recovered, and so on.

    See Also:

    Oracle Database Backup and Recovery User's Guide for more information about performing user-directed recovery

5.2.1 Prerequisites for Recovering Protected Databases Using Cloud Control

  • The protected database must be enrolled and registered with the target Recovery Appliance.

  • Backups required for the recovery process must be stored on the Recovery Appliance. When performing point-in-time recovery, you can recover to any point within the recovery window defined for a protected database.

5.2.2 Performing Block Media Recovery Using Cloud Control

This section describes how to recover corrupted data blocks by using Oracle Advised Recovery.

To recover from a failure caused by corrupted data blocks:

  1. Access the home page for the protected database as described in "Accessing the Protected Database Home Page Using Cloud Control".
  2. Ensure that the prerequisites described in "Prerequisites for Recovering Protected Databases Using Cloud Control" are met.
  3. From the Availability menu, select Backup & Recovery, and then select Perform Recovery.

    The Perform Recovery page is displayed. Any failures diagnosed by the Data Recovery Advisor are displayed in the Oracle Advised Recovery section.

  4. Click Advise and Recover.

    The failures detected by Data Recovery Advisor are listed.

  5. In the User Directed Recovery section, select the following options:

    • In Recovery Scope, select Datafiles.

    • In Operation Type, select Block Recovery.

  6. Click Recover to display the Perform Object Level Recovery: Block Recovery page.
  7. Select Corruption List and click Next.

    The Perform Object Level Recovery: Corrupted Blocks page is displayed. The Datafile Name section displays the data file name and the block IDs of the corrupt blocks.

  8. Review the data file name and list of corrupt blocks and click Next.

    The Perform Object Level Recovery: Schedule page is displayed. A default name and description are entered for the recovery job.

  9. If required, edit the name and description of the recovery job and click Next.

    The Perform Object Level Recovery: Review page is displayed.

  10. (Optional) View and edit the RMAN script generated for this recovery job by clicking Edit RMAN Script.
  11. Click Submit Job.

    A message is displayed indicting that the job is submitted successfully.

  12. Click View Job.

    The Job page containing the job execution details is displayed. The Summary section provides information such as the type of job, protected database name, SID, and Recovery Appliance catalog user name. The table at the bottom of this page displays the execution steps and their status.

5.2.3 Recovering an Entire Database Using Cloud Control

This section describes how to recover the entire protected database to the current time using the user-directed recovery process in Cloud Control.

To perform complete recovery of the protected database:

  1. Access the home page for the protected database as described in "Accessing the Protected Database Home Page Using Cloud Control".
  2. Ensure that the prerequisites described in "Prerequisites for Recovering Protected Databases Using Cloud Control" are met.
  3. From the Availability menu, select Backup & Recovery, and then select Perform Recovery.

    The Perform Recovery page is displayed.

  4. In the User Directed Recovery section, select the following options:

    • In Recovery Scope, select Whole Database.

    • In Operation Type, select Recover to the current time or a previous point-in-time.

  5. Click Recover to display the Perform Whole Database Recovery: Point-in-time page.
  6. Select Recover to the current time and click Next.

    The Perform Whole Database Recovery: Rename page is displayed.

  7. Select No. Restore the files to the default location and click Next.

    The Perform Whole Database Recovery: Schedule page is displayed.

  8. (Optional) Modify the default names provided for the Job Name and Job Description.
  9. Click Next to display the Perform Whole Database Recovery: Review page.
  10. (Optional) To edit the RMAN script generated for this recovery job, click Edit RMAN Script.
  11. Click Submit Job.

    A message is displayed indicting that the job is submitted successfully.

  12. Click View Job.

    The Job page containing the job execution details is displayed. The Summary section provides information such as the type of job, protected database name, SID, and Recovery Appliance catalog user name. The table at the bottom of the Summary page displays the execution steps and their status.

5.3 Restoring and Recovering Data from Recovery Appliance Using the Command Line

The examples in this section contain procedures that represent typical restore and recovery scenarios. If a protected database has been correctly configured for backup operations with a Recovery Appliance as described in "Configuring Backup and Recovery Settings for Protected Databases (Command Line)", it can use the same configuration for recovery operations.

When using Recovery Appliance for restore and recovery operations, the RMAN connection syntax used is the same as with a regular RMAN recovery catalog connection. The only difference is that you connect to the Recovery Appliance catalog and configure RMAN channels as described in "Using RMAN Channels for Recovery Appliance Backup and Recovery Operations".

See Also:

Oracle Database Backup and Recovery User's Guide for a complete description of how to recover databases

This section contains the following examples:

5.3.1 Prerequisites for Restoring and Recovering Data from Recovery Appliance

Ensure that the following prerequisites are met before you use backups stored on Recovery Appliance to restore and recover your protected database:

  • The protected database must be enrolled and registered with the target Recovery Appliance.

    This is important if you have multiple Recovery Appliances or a Data Guard environment where primary and standby backs up to different Recovery Appliances.

    See Also:

    "Enrolling the Protected Database with Recovery Appliance (Command Line)"

  • Backups required to restore and recover the protected database must be stored on the Recovery Appliance. When performing point-in-time recovery, you can recover to any point within the recovery window defined for the protected database.

  • The Oracle wallet containing credentials used to authenticate with the Recovery Appliance must be configured on the protected database.

    See Also:

  • Configure or allocate RMAN SBT channels that correspond to the Recovery Appliance backup module. The examples in this chapter assume that an SBT channel is configured for Recovery Appliance.

    It is recommended that you configure channels using the RMAN CONFIGURE command because these settings are persistent settings. However, you can override the configured channel settings by using the ALLOCATE CHANNEL command within the RUN block that performs the backup or recovery operation.

    See Also:

    "Using RMAN Channels for Recovery Appliance Backup and Recovery Operations"

5.3.2 Restoring Protected Databases Using a Downstream Recovery Appliance

When Recovery Appliance replication is configured, the protected database sends backups to the upstream Recovery Appliance. The upstream Recovery Appliance then forwards these backups to the downstream Recovery Appliance. In the event of a failure, if the upstream Recovery Appliance is unavailable, then you can perform restore operations using the downstream Recovery Appliance.

See Also:

"Protected Databases and Recovery Appliance Architecture" for a brief overview of replication

Use the following high-level steps to restore a protected database directly from a downstream Recovery Appliance:

  1. Create an Oracle wallet that contains the credentials of the VPC user with which the protected database will authenticate with the downstream Recovery Appliance.

    See Also:

    "Creating an Oracle Wallet on the Protected Database"

    Note:

    The protected database need not be explicitly added to or registered with the downstream Recovery Appliance before performing restore operations. When replication is configured between the upstream and downstream Recovery Appliance, the protected databases enrolled with the upstream Recovery Appliance are registered with the downstream Recovery Appliance.

  2. Connect to the protected database as TARGET and to the downstream Recovery Appliance catalog as CATALOG.

    See Also:

    "Connecting to the Protected Database and Recovery Appliance Using CLI"

  3. Allocate an RMAN SBT channel that corresponds to the downstream Recovery Appliance, place the protected database in MOUNT mode, and restore the protected database.

    All these statements must be enclosed within a RUN block as shown in "Example: Restoring and Recovering an Entire Database With the Existing Current Control File".

    See Also:

    "Allocating RMAN SBT Channels for Recovery Appliance"

5.3.3 Example: Restoring and Recovering an Entire Database With the Existing Current Control File

This example assumes that some or all the data files in the protected database are lost or damaged. However, the control file is available.

To restore and recover all the data files in a protected database:

  1. Ensure that the prerequisites described in "Prerequisites for Restoring and Recovering Data from Recovery Appliance" are met.
  2. Use RMAN to connect to the protected database as TARGET and the Recovery Appliance catalog as CATALOG.
  3. Restore and recover all the data files using the following command: 
    STARTUP MOUNT;
RUN 
{
  RESTORE DATABASE;
  RECOVER DATABASE;
  ALTER DATABASE OPEN;
}

5.3.4 Example: Recovering an Entire Database to a Specified Point-in-Time

This example demonstrates how to perform point-in-time recovery (PITR) for a protected database. PITR may be required to revert the protected database to a prior date to recover from user errors (accidentally dropping tables or updating the wrong tables), media failure, or a failed database upgrade. You need to restore the control file only if there has been a structural change to the control file (such as creating or dropping tablespaces). Use the SET UNTIL clause to specify the time, SCN, or log sequence to which the protected database must be recovered.

If Flashback Database is enabled for the protected database, you can also use this feature to recover to a prior point-in-time.

See Also:

  • Oracle Database Backup and Recovery User's Guide

  • Oracle Database Backup and Recovery Reference

To restore and recover the entire protected database, including the control file, to a specific point-in-time:

  1. Ensure that the prerequisites described in "Prerequisites for Restoring and Recovering Data from Recovery Appliance" are met.
  2. Use RMAN to connect to the protected database as TARGET and the Recovery Appliance catalog as CATALOG.
  3. Determine the point-in-time to which the protected database must be recovered. You can use an SCN, a time, or log sequence number to specify the point-in-time.

    Use the following query to translate between timestamp and SCN:

    SQL> set linesize 222
SQL> select name, current_scn, scn_to_timestamp(current_scn) "Time" 
     from v$database;

    NAME        CURRENT_SCN   TIME
---------   -----------   ------------------------------------
ORA121      122019556     22-APR-14 12.30.15.000000000 PM

    If the protected database is not available, you can query the Recovery Appliance catalog views to obtain the SCN number. You must provide the range date and time for your recovery window and the db_unique_name of the protected database. The following query (sample output included) is run when connected to the Recovery Appliance catalog:

    SELECT
a.db_key,a.db_name,a.sequence#,a.first_change#,a.next_change#,a.completion_time
FROM rc_archived_log a, db db_key
WHERE reg_db_unique_name = 'PTDB2' AND a.db_key = db.db_key 
    AND to_date('16-Jul-2014 06:55:23','DD-Mon-YYYY HH24:MI:SS') BETWEEN       
          a.first_time AND a.next_time;

DB_KEY DB_NAME   SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#   COMPLETION_TIME
------ -------- ---------- ------------- ------------   ---------------
24201  PTDB2      9911      288402086    288430116     14/07/2014 5:27:49 PM

    The FIRST_CHANGE# corresponds to the first SCN number in the archive redo log and the NEXT_CHANGE# is the last SCN number in the archive redo log.

  4. Restore and recover the control file and the protected database. 
    STARTUP NOMOUNT;
RUN 
{
   SET UNTIL TIME "TO_DATE('2014-14-07:17:27:49','yyyy-dd-mm:hh24:mi:ss')";
   RESTORE CONTROLFILE;
   ALTER DATABASE MOUNT;
   RESTORE DATABASE;
   RECOVER DATABASE;
   ALTER DATABASE OPEN RESETLOGS;
}

5.3.5 Example: Restoring and Recovering the Control File

This example demonstrates how to recover a protected database after the loss of all control file copies. It is strongly recommended that you create multiple copies of your control files in separate disk locations.

To restore and recover the control file in a protected database:

  1. Ensure that the prerequisites described in "Prerequisites for Restoring and Recovering Data from Recovery Appliance" are met.
  2. Use RMAN to connect to the protected database as TARGET and the Recovery Appliance catalog as CATALOG.
  3. Restore the control file and then mount the database using the following command: 
    STARTUP NOMOUNT;
RUN 
{
  RESTORE CONTROLFILE;
  ALTER DATABASE MOUNT;
}

5.3.6 Example: Restoring and Recovering Tablespaces in the Protected Database

This example demonstrates how to restore and recover one or more tablespaces in the protected database after they are accidentally dropped or corrupted. The example assumes that the database is up and running and that you will restore only the affected tablespaces.

To restore and recover one or more tablespaces:

  1. Ensure that the prerequisites described in "Prerequisites for Restoring and Recovering Data from Recovery Appliance" are met.
  2. Use RMAN to connect to the protected database as TARGET and the Recovery Appliance catalog as CATALOG.
  3. Restore and recover the affected tablespaces.

    The following command restores and recovers the USERS tablespace:

    RUN 
{
  SQL 'ALTER TABLESPACE users OFFLINE';
  RESTORE TABLESPACE users;
  RECOVER TABLESPACE users;
  SQL 'ALTER TABLESPACE users ONLINE';
}

5.3.7 Example: Restoring and Recovering a Data File in the Protected Database

This example demonstrates how to restore and recover a data file that was accidentally deleted or corrupted.

To restore and recover a data file in a protected database:

  1. Ensure that the prerequisites described in "Prerequisites for Restoring and Recovering Data from Recovery Appliance" are met.
  2. Use RMAN to connect to the protected database as TARGET and the Recovery Appliance catalog as CATALOG.
  3. Restore and recover the affected data file in the protected database using the following command:

    The following command restores and recovers data file 3 in the protected database:

    RUN
{
  SQL 'ALTER DATABASE DATAFILE 3 OFFLINE';
  RESTORE DATAFILE 3;
  RECOVER DATAFILE 3;
  SQL 'ALTER DATABASE DATAFILE 3 ONLINE';
}

5.3.8 Example: Restoring and Recovering PDBs

The multitenant architecture, introduced in Oracle Database 12c Release 1, enables an Oracle Database to function as a multitenant container database (CDB) that includes zero, one, or many customer-created pluggable databases (PDBs). All Oracle databases before Oracle Database 12c are non-CDBs.

A CDB includes the following components: root, seed, and user-created PDBs. The root stores the common users and Oracle-supplied metadata such as the source code for Oracle-supplied packages. The seed is a template that can be used to create new PDBs. A PDB is a portable collection of schemas, schema objects, and nonschema objects that appears to an Oracle Net client as a non-CDB.

This section demonstrates various restore and recovery scenarios for PDBs. The steps to restore and recover a PDB are similar to those used for restore and recover operations on non-CDBs. This section contains the following examples:

5.3.8.1 Performing Complete Recovery of the Whole PDB

This example demonstrates how to perform complete recovery for a PDB in the protected database.

To restore and recover a whole PDB:

  1. Ensure that the prerequisites described in "Prerequisites for Restoring and Recovering Data from Recovery Appliance" are met.
  2. Use RMAN to connect to the root of the CDB as TARGET and the Recovery Appliance catalog as CATALOG.
  3. Identify the PDB that needs to be restored by running the following query in the CDB: 
    SELECT name FROM v$pdbs;
  4. Restore and recover the required PDB in your protected database.

    The following command restores and recovers the PDB hr_pdb:

    RUN 
{
  ALTER PLUGGABLE DATABASE "hr_pdb" CLOSE IMMEDIATE;
  RESTORE PLUGGABLE DATABASE 'hr_pdb';
  RECOVER PLUGGABLE DATABASE 'hr_pdb';
  ALTER PLUGGABLE DATABASE "hr_pdb" OPEN;
}

5.3.8.2 Performing Point-in-Time Recovery for the Whole PDB

This example demonstrates how to perform point-in-time recovery for one or more PDBs in your protected database. Specify the SET UNTIL clause to indicate the point to which the PDB must be recovered.

To restore and recover a PDB to a specific point-in-time:

  1. Ensure that the prerequisites described in "Prerequisites for Restoring and Recovering Data from Recovery Appliance" are met.
  2. Use RMAN to connect to the root of the CDB as TARGET and the Recovery Appliance catalog as CATALOG.
  3. Identify the PDB that needs to be restored by running the following query in the CDB: 
    SELECT name FROM v$pdbs;
  4. Restore and recover the affected PDB to the specified point in time.

    The following command restores and recovers the PDB hr_pdb to the point in time specified by the SET UNTIL clause.

    RUN
{
  SET UNTIL TIME "to_date('2014-08-16 09:00:00','YYYY-MM-DD HH24:MI:SS')";
  ALTER PLUGGABLE DATABASE "hr_pdb" CLOSE IMMEDIATE;
  RESTORE PLUGGABE DATABASE 'hr_pdb';
  RECOVER PLUGGABLE DATABASE 'hr_pdb';
  ALTER PLUGGABLE DATABASE hr_pdb OPEN RESETLOGS;
}

5.3.8.3 Recovering Specific Data Files in a PDB

Restoring and recovering data files in a PDB is similar to restoring and recovering any data file using RMAN. This example demonstrates how to restore and recover a data file in a PDB.

To restore and recover a specific data file in a PDB:

  1. Ensure that the prerequisites described in "Prerequisites for Restoring and Recovering Data from Recovery Appliance" are met.
  2. Use RMAN to connect to the root of the CDB as TARGET and the Recovery Appliance catalog as CATALOG.
  3. Identify the PDB that needs to be restored by running the following query in the CDB: 
    SELECT name FROM v$pdbs;
  4. Identify the number of the data file in the PDB that needs to be recovered using the following query: 
    SELECT p.PDB_ID, p.PDB_NAME, d.FILE_ID, d.TABLESPACE_NAME, d.FILE_NAME
  FROM DBA_PDBS p, CDB_DATA_FILES d
  WHERE p.PDB_ID = d.CON_ID
  ORDER BY p.PDB_ID;
  5. Restore and recover the affected data files in the PDB.

    The following example restores and recovers data file number 10 in the PDB.

    RUN 
{
  SQL 'ALTER DATABASE DATAFILE 10 OFFLINE';
  RESTORE DATAFILE 10;
  RECOVER DATAFILE 10;
  SQL 'ALTER DATABASE DATAFILE 10 ONLINE';
}

5.3.8.4 Recovering Specific Tablespaces in a PDB

This example demonstrates how to restore and recover the tablespace USR_TBS contained in the PDB SH_PDB in your protected database.

Restoring and recovering a tablespace in a PDB is similar to a normal tablespace restore and recovery. The difference is that you need to map the tablespace to the pluggable database (pdb_name:tablespace_name).

To restore and recover specific tablespaces in a PDB:

  1. Ensure that the prerequisites described in "Prerequisites for Restoring and Recovering Data from Recovery Appliance" are met.
  2. Use RMAN to connect to the root of the CDB as TARGET and the Recovery Appliance catalog as CATALOG.
  3. Place the affected tablespace in offline mode.

    The following example places the tablespace use_tbs in the PDB sh_pdb in offline mode.

    ALTER TABLESPACE sh_pdb:usr_tbs OFFLINE;
  4. Restore and recover the affected tablespaces contained in the PDB within your protected database.

    The following example restore and recovers the tablespace usr_tbs in the PDB sh_pdb.

    RUN 
{
  RESTORE TABLESPACE sh_pdb:usr_tbs;
  RECOVER TABLESPACE sh_pdb:usr_tbs;
}
  5. Make the restored and recovered tablespace online.

    The following example brings the tablespace usr_tbs in the PDB sh_pdb online.

    ALTER TABLESPACE sh_pdb:usr_tbs ONLINE;

5.3.9 Example: Recovering a PDB in an Oracle RAC Environment

The process to restore and recover a PDB in an Oracle Real Application Clusters (Oracle RAC) environment has some slight additions to the non-Oracle RAC process. This example demonstrates how to recover a PDB in an Oracle RAC environment.

To restore and recover a PDB in an Oracle RAC environment:

  1. Ensure that the prerequisites described in "Prerequisites for Restoring and Recovering Data from Recovery Appliance" are met.
  2. Use RMAN to connect to the root of the CDB as TARGET and the Recovery Appliance catalog as CATALOG.
  3. Ensure that all instances of the affected PDB are closed.

    The following command closes all instances of the PDB hr_pdb.

    ALTER PLUGGABLE DATABASE "hr_pdb" CLOSE IMMEDIATE INSTANCES=all;
  4. Restore and recover the affected PDB in your protected database.

    The following command restores and recovers the hr_pdb.

    RUN 
{
  ALTER PLUGGABLE DATABASE "hr_pdb" CLOSE IMMEDIATE;
  RESTORE PLUGGABLE DATABASE 'hr_pdb';
  RECOVER PLUGGABLE DATABASE 'hr_pdb';
  ALTER PLUGGABLE DATABASE "hr_pdb" OPEN RESETLOGS;
  ALTER PLUGGABLE DATABASE "hr_pdb" OPEN INSTANCES=all;
}

5.3.10 Example: Restoring and Recovering One or Many Data Blocks in a PDB

Block media recovery enables you to recover one more corrupt data blocks while the data file is still online. This example demonstrates how to perform block media recovery to recover one or more corrupt data blocks.

See Also:

Oracle Database Backup and Recovery User's Guide

The existence of corrupt data blocks can be indicated by one of the following methods:

  • The protected database alert log contains the following message indicating that one or more blocks are corrupt:

    Sun Aug 17 09:34:48 2014
Hex dump of (file 2, block 16385) in trace file /u01/app/oracle/diag/rdbms/dbstress/dbstress/trace/dbstress_ora_9732.trc
 
Corrupt block relative dba: 0x00004001 (file 2, block 16385)
Fractured block found during backing up datafile
Data in bad block:
 type: 6 format: 2 rdba: 0x00004001
 last change scn: 0x0000.00a564c0 seq: 0x1 flg: 0x06
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x00000000
 check value in block header: 0xd6dd
 computed block checksum: 0x58f7

  • During an RMAN backup, the block corruption is detected and a message similar to the following will be displayed.

    RMAN-08038: channel c3: starting piece 1 at 2014/08/17 09:34:43
RMAN-03009: failure of backup command on c1 channel at 08/17/2014 09:34:50
ORA-19566: exceeded limit of 0 corrupt blocks for file /SHARED1/ORADATA/DBF/dbstress/soe.dbf
.
.
RMAN-03002: failure of backup plus archivelog command at 08/17/2014 09:35:55
RMAN-03009: failure of backup command on c1 channel at 08/17/2014 09:34:50
ORA-19566: exceeded limit of 0 corrupt blocks for file /SHARED1/ORADATA/DBF/dbstress/soe.dbf

To restore and recover corrupt data blocks in the protected database:

  1. Ensure that the prerequisites described in "Prerequisites for Restoring and Recovering Data from Recovery Appliance" are met.
  2. Use RMAN to connect to the protected database as TARGET and the Recovery Appliance catalog as CATALOG.
  3. Identify the corrupt blocks that need to be recovered.

    Use the entries in the protected database alert log to identify corrupt blocks and the data files that contain these corrupt blocks. Or, query the V$DATABASE_BLOCK_CORRUPTION view to identify corrupt blocks.

  4. Use the BLOCKRECOVER command to recover the corrupt data blocks.

    The following example recovers the data blocks 46, 56, and 84 in data file 4.

    RUN
{
  BLOCKRECOVER CORRUPTION LIST;
  BLOCKRECOVER DATAFILE 4 BLOCK 46,56,84;
}

5.3.11 Example: Recovering a Database Configured for Real-Time Redo Transport After a Severe Storage Failure

Real-time redo transport, when enabled, guarantees the lowest recovery downtime for protected database. More information about real-time redo transport is described in "About Real-Time Redo Transport". When restoring and recovering a protected database immediately after a storage failure, the necessary complete and partial archived log files are restored and recovered so that media recovery can return the database state to the closest state from when the storage failure occurred.

The following example recovers a protected database that was configured to use real-time redo transport after a storage failure that results in the loss of all data files and online redo log files. To recover the protected database to the highest SCN using the backups and redo logs available at the Recovery Appliance, use the FINAL_CHANGE# column of the RC_DATABASE view. The FINAL_CHANGE# column contains the highest SCN to which the protected database must be recovered. Use this SCN value in the SET UNTIL SCN command prior to performing a recovery. The recovery is performed using only the backups and redo logs available at the Recovery Appliance.

Note:

In the following scenarios, RC_DATABASE.FINAL_CHANGE# will contain the value -1 and cannot be used in the SET UNTIL SCN command:

  • version of the protected database is Oracle Database 11g (Release 11.1) or lower

  • COMPATIBLE parameter of the protected database was set to 10.0 or lower while sending real-time redo log data to the Recovery Appliance

Instead, use the NEXT_CHANGE# column in the V$ARCHIVED_LOG view to determine the SCN to which the protected database needs to be recovered.

See My Oracle Support note 243760.1 for additional information. My Oracle Support is available at: https://support.oracle.com.

See Also:

Oracle Database Backup and Recovery Reference for a description of the FINAL_CHANGE# column

To restore and recover a protected database that is configured to use real-time redo transport:

  1. Ensure that the prerequisites described in "Prerequisites for Restoring and Recovering Data from Recovery Appliance" are met.
  2. Use RMAN to connect to the protected database as TARGET and the Recovery Appliance catalog as CATALOG.
  3. Determine the SCN to which the protected database must be recovered by querying the RC_DATABASE view. This SCN is the highest SCN at the time the database crashed. 
    SELECT final_change# FROM rc_database WHERE name='MY_DB';
  4. Restore and recover the protected database.

    This example assumes that the control file is available. If the control file is lost, then you need to first recover the control file before performing the steps listed here.

    STARTUP NOMOUNT;
RUN 
{
  SET UNTIL SCN 23098;
  RESTORE DATABASE;
  RECOVER DATABASE;
  ALTER DATABASE OPEN RESETLOGS;
}

    Note:

    The UNTIL SCN clause is required. Unless a specific SCN value is chosen, the log containing the partial redo is not applied by recovery.

5.3.12 Example: Recovering the Control File and Database When Real-Time Redo Transport is Configured

This example recovers a protected database that is configured to use real-time redo transport from the loss of all database files. Since the control file too is lost, you need to first restore the control file and then perform recovery of the protected database.

To restore and recover a protected database, including the control file, that is configured to use real-time redo transport:

  1. Ensure that the prerequisites described in "Prerequisites for Restoring and Recovering Data from Recovery Appliance" are met.
  2. Use RMAN to connect to the protected database as TARGET and the Recovery Appliance catalog as CATALOG.
  3. Determine the SCN to which the protected database must be recovered by querying the RC_DATABASE view. This SCN is the highest SCN at the time the database crashed. 
    SELECT final_change# FROM rc_database WHERE name='PTDB1';
  4. Restore and recover the protected database.

    This example assumes that the control file is available. If the control file is lost, then you need to first recover the control file before performing the steps listed here.

    STARTUP FORCE NOMOUNT;
SET DBID=ptdb1;
RESTORE CONTROLFILE;
ALTER DATABASE MOUNT;
RUN
{
  SET UNTIL SCN 34568;
  RESTORE DATABASE;
  RECOVER DATABASE;
}
ALTER DATABASE OPEN RESETLOGS;

5.4 Database Duplication from Recovery Appliance

If you need to duplicate a protected database to create a standby database or to clone a protected database to a target host, you can do so by connecting to the Recovery Appliance catalog and using backup-based duplication. By using the catalog, there is no need to connect to the source database. Creating a standby database or a clone both involve running the RMAN DUPLICATE command.

See Also:

5.4.1 Creating a Standby Database for a Protected Database

When you create a standby database from Recovery Appliance, you connect to the standby (auxiliary instance) and to the Recovery Appliance catalog, and run the RMAN DUPLICATE command with the FOR STANDBY option.

Note:

Because the primary database is already registered with the Recovery Appliance catalog, you should not register the standby database with the Recovery Appliance catalog.

To create a standby database for a protected database:

  1. On the target host, prepare the auxiliary instance by performing the following tasks:

    • Create the directories in which the standby database files will be stored.

    • Create an initialization parameter file for the auxiliary instance.

      The mandatory parameters are DB_NAME and DB_CREATE_FILE_DEST.

    • Create a password file for the auxiliary database. This password file will be overwritten during the duplicate operation in step 3.

    • Establish Oracle Net connectivity between the protected database and the auxiliary instance.

    • Start the auxiliary instance in NOMOUNT mode.

  2. Start RMAN and connect as CATALOG to the Recovery Appliance catalog and as AUXILIARY to the auxiliary instance.

    In the following example, ra_rman_user is the Recovery Appliance user that the protected database my_ptdb uses to authenticate with the Recovery Appliance. ra1 is the net service name of the target Recovery Appliance that is configured in the Oracle wallet. stdby is the net service name of the auxiliary instance.

    %rman
RMAN> CONNECT CATALOG ra_rman_user@ra1;
RMAN> CONNECT AUXILIARY "sys@stdby AS SYSDBA";
  3. Create the standby database using the DUPLICATE command. Configure one or more auxiliary channels that correspond to the Recovery Appliance backup module.

    The following example configures three auxiliary channels and creates a standby database for the protected database my_ptdb .

    RUN
{
   ALLOCATE AUXILIARY CHANNEL c1 DEVICE TYPE sbt_tape 
     PARMS='SBT_LIBRARY=/u01/oracle/product/12.1.0.2/dbhome_1/lib/libra.so,
     ENV=(RA_WALLET=location=file:/u01/oracle/product/12.1.0.2/dbhome_1/dbs/ra
     credential_alias=ra-scan:1521/zdlra5:dedicated)' FORMAT'%U_%d';
  ALLOCATE AUXILIARY CHANNEL c2 DEVICE TYPE sbt_tape 
     PARMS='SBT_LIBRARY=/u01/oracle/product/12.1.0.2/dbhome_1/lib/libra.so,
     ENV=(RA_WALLET=location=file:/u01/oracle/product/12.1.0.2/dbhome_1/dbs/ra
     credential_alias=ra-scan:1521/zdlra5:dedicated)' FORMAT'%U_%d';
  ALLOCATE AUXILIARY CHANNEL c3 DEVICE TYPE sbt_tape 
     PARMS='SBT_LIBRARY=/u01/oracle/product/12.1.0.2/dbhome_1/lib/libra.so,
     ENV=(RA_WALLET=location=file:/u01/oracle/product/12.1.0.2/dbhome_1/dbs/ra
     credential_alias=ra-scan:1521/zdlra5:dedicated)' FORMAT'%U_%d';
  DUPLICATE DATABASE my_ptdb FOR STANDBY DORECOVER;
}

5.4.2 Cloning a Protected Database

You can clone a protected database to a target host by using backup-based duplication. The Oracle-recommended method, covered in this section, connects to the Recovery Appliance catalog. By using a catalog for the duplicate operation, a connection to the source database is not required.

The example that follows represents the Oracle best practice for cloning a protected database and includes a sample script that you can customize for your scenario.

This example assumes the following:

  • backups of the target database exist on the Recovery Appliance and are available to the auxiliary instance

  • RMAN connection from the auxiliary database to the Recovery Appliance that contains metadata and backups for the target database is available

  • both source and duplicate database use Oracle Managed Files (OMF)

  • operating system used is Linux or UNIX

  • the audit directory is created on the auxiliary database host

  • prerequisites for backup-based duplication are met

The script provided in this example performs the following tasks:

  • drops an existing auxiliary database

  • backs up the target database

  • creates a dummy auxiliary instance and opens it in NOMOUNT mode

  • duplicates the target database using the target database backups and metadata available on the Recovery Appliance

    The duplicate database control file is stored as +REDO/ORACLE_SID/CONTROLFILE/cf3.ctl and the data files are stored in the +DATA directory.

  • verifies that the required objects are created in the duplicate database

To clone a protected database using backup-based duplication without a target connection:

  1. Create a parameter file (pfile) for the auxiliary instance. The pfile contains only the DB_NAME initialization parameter which is set to the SID of the duplicate database.

    The following pfile, called init_dup.ora and located in the /home/oracle directory, sets the DB_NAME parameter. Replace dup_db with the SID of your duplicate database:

    *.db_name = 'dup_db'
  2. Use a text editor and create a Shell script (called dup_db.sh in this example) with the contents shown below and with the following modifications:

    • Replace the value of the ORACLE_HOME variable with the Oracle home directory of your auxiliary instance.

    • Replace the value of the logdir variable with the directory in which you want to store log files.

    • Replace the following placeholders (shown in Italics) with values appropriate to your duplication scenario:

      dup_db: system identifier (SID) and service name of the auxiliary instance

      tgt_db: SID and service name of the target database

      sys_pswd: password for the SYS user of the target database

      vpc_user: name of the VPC user

      vpc_user_pswd: password for the VPC user vpc_user

      ra_scan: Single Client Access Name (SCAN) of the Recovery Appliance

      ra_servicename: service name of the Recovery Appliance metadata database

      system_pswd: password for the SYSTEM user in the target database

    • If you want to store the duplicate database control file using a name and location that is different from +REDO/ORACLE_SID/CONTROLFILE/cf3.ctl, then replace the value of control_files in the dup_aux_db function with a value that is appropriate for your duplication scenario.

    • If you want to store the duplicate data files in a directory that is different from +DATA, then replace the value of db_create_file_dest in the dup_aux_db function with a value that is appropriate for your duplication scenario.

    #!/bin/bash
export ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_2
export ORACLE_BASE=/uo1/app/oracle
export ORACLE_SID=dup_db
export PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin:$ORACLE_HOME/Opatch
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/rdbms/lib:/lib:/usr/lib;
export LD_LIBRARY_PATH
export logdir=/home/oracle/log
export dt='date +%y%m%d%H%M%S'
export NLS_DATE_FORMAT='DD-MM-YYYY HH24:MI:SS'

function drop_aux_db {
export ORACLE_SID=dup_db
$ORACLE_HOME/bin/sqlplus -s '/ as sysdba' <<EOF2
set pagesize 999 linesize 999 heading off feedback off
select name, open_mode from v\$database;
shutdown immediate;
startup mount exclusive restrict;
drop database;
exit;
EOF2
}

echo "Backup the target database"
function backup_source_db {
$ORACLE_HOME/bin/rman target sys/sys_pswd@tgt_db catalog
vpc_user/vpc_user_pswd@ra_scan:1521/ra_serivcename:dedicated <<EOF
RUN {
backup as backupset cumulative incremental level 1 database include current
controlfile plus archivelog not backed up delete input;}
exit;
EOF
}

sleep 120

echo "List the backup of the target database"
function check_source_db_backup {
$ORACLE_HOME/bin/rman target sys/sys_pswd@tgt_db catalog vpc_user/vpc_user_pswd@ra_scan:1521/ra_serivcename:dedicated <<EOF
LIST BACKUP OF DATABASE COMPLETED AFTER '(SYSDATE-1/24)';
EOF
}

echo "Start the auxiliary database in FORCE NOMOUNT mode"
function nomount_aux_db {
export ORACLE_SID=dup_db
$ORACLE_HOME/bin/rman target / <<EOF2
startup force nomount pfile='/home/oracle/init_dup.ora';
exit;
EOF2
}

echo "Duplicate the target database"
function dup_aux_db {
export ORACLE_SID=dup_db
$ORACLE_HOME/bin/rman catalog vpc_user/vpc_user_pswd@ra_scan:1521/ra_serivcename:dedicated AUXILIARY /
<<EOF
duplicate database tgt_db to dup_db spfile
set control_files '+REDO/${ORACLE_SID}/CONTROLFILE/cf3.ctl'
set db_create_file_dest '+DATA/' ;
exit;
EOF
}

echo "Check schema objects on the target"
function check_source_db {
$ORACLE_HOME/bin/sqlplus -s system/system_pswd@tgt_db <<EOF2
set pagesize 999 linesize 999 heading off feedback off
select name, open_mode from v\$database;
select table_name, num_rows from dba_tables where owner='SOE';
exit;
EOF2
}

echo "Check schema objects on the auxiliary"
function check_aux_db {
export ORACLE_SID=dup_db
$ORACLE_HOME/bin/sqlplus -s '/ as sysdba' <<EOF2
set pagesize 999 linesize 999 heading off feedback off
select name, open_mode from v\$database;
select table_name, num_rows from dba_tables where owner='SOE';
exit;
EOF2
}

drop_aux_db
backup_source_db
check_source_db_backup
nomount_aux_db
dup_aux_db
check_source_db
check_aux_db
  3. Set execute permissions on the script dup_db.sh using the chmod command. 
    $ chmod +x dup_db.sh
  4. On the duplicate host (that hosts the duplicate database), run the dup_db.sh script.

    The following command runs the dup_db.sh script that is stored in the /home/my_scripts/duplication directory:

    $ ./home/my_scripts/duplication/dup_db.sh