Restoring Oracle Tables to a User-Defined Auxiliary Instance

You can restore Oracle tables to a specific auxiliary instance on the target host, or restore a table to a specific failure point.

When you restore database tables to a target instance, the system automatically duplicates the source database to an auxiliary instance in the specified temporary staging location. Once the database is duplicated, you can import the tables to the target instance.

The auxiliary instance is located in the staging_path/jobID directory.

You can have the Commvault software automatically clean up the auxiliary instance that it created (Table Restore tab, Cleanup Auxiliary check box). If the restore job fails, the software leaves the auxiliary instance intact. You can manually import the tables and database objects from the auxiliary instance, or you can shut down the auxiliary instance and remove the files (by using the rm -rf command).

Best Practice: Use the Oracle catalog when you perform a table-level restore on Oracle 11g or a more recent version. The restore needs the catalog in order to skip Oracle TNS failures that happen when the target connection occurs.

In order to successfully import the tables, you must have a user that has the dba privilege, or the user must own the full table. You cannot have a user with the sysbackup privilege for the import.

On Windows configurations, the Oracle home user that you use for the Oracle instance must have the same credentials and password as the local administrator, when the local administrator is configured for the Commvault Oracle instance properties.

Before You Begin

• Configure Oracle Subclients for Table Level Restores.

• Perform a full backup of the subclient.

• If your table contains non-English characters, follow Restoring Tables with Non-English Characters for Oracle.

• Set up an auxiliary instance.

• If you use an auxiliary instance, you can avoid problems with the auxiliary database not starting because it is using default PFILE parameters. For more information, see Defining Oracle Parameters for an Auxiliary PFILE.

• On the destination host, add an instance on the CommCell Console.

Procedure

1. From the CommCell Browser, expand Client Computers > client > Oracle.

2. Right-click the instance, point to All Tasks and select Browse and Restore.

3. Select the Table View check box and click View Content.

4. From the Browse window, select the tables to restore and click Recover All Selected.

   The Table Restore Options dialog box appears.

5. On the Table Restore tab:

   1. Select the Auxiliary Instance check box.

   2. In the Database Instance box, type the auxiliary instance name.

   3. In the Database Client box, select the destination client for the auxiliary instance.

   4. In the PFile box, type the path to the PFILE of the auxiliary instance.

   5. In the Staging Path box, type the location where the auxiliary instance will be created.

      The software creates the export dump file in this location. Verify that there is enough free space for the file, the control file, the Oracle diagnostic directory, and the required tablespaces.

   6. Clear the Cleanup Auxiliary check box to leave the auxiliary instance intact after the table restore.

6. On the Advanced Options tab, enter the client destination information.

   1. Select the Import to a Different DB check box.

   2. In the Enter Import Oracle Instance, box, type the destination instance name.

   3. In the Select a Client box, select the destination client.

   4. Click Advanced.

7. Select the table recover option.

   Option	Description
   Point-in-Time	Recover the tables to a specified point-in-time. Enter the date and time.
   SCN	Recover the tables to a System Change Number (SCN). Enter the SCN.

8. Optional: Export table procedures and table additional parameters.

   Select the Use additional export parameters check box and type the parameters to export.

   When you restore Oracle tables, you can restore table objects other than triggers, constraints, indexes, and grants.

   Note

   Stored procedures are restored from the schema level. If one table within the schema is selected for restore, all the stored procedures for that schema are restored.

   The object export uses the Data Pump Export utility. If your Oracle version does not support the Data Pump Export utility, you cannot include stored procedures when you export tables.

9. Click OK to close the Advanced Table Restore Options dialog box.

10. Click OK to close the Restore Options dialog box and start the restore.