Restoring Oracle Tables to a Different Host

If some of the tables in the Oracle database are lost or corrupted, you can restore the tables to a different host from the source.

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 TTS failures that happen when the target connection occurs.

This procedure does not require an auxiliary instance for the destination.

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 the tables contain non-English characters, see Restoring Tables with Non-English Characters for Oracle.

• 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.

• Verify the following configuration:

  • The source and destination hosts have the same schema

  • The source and destination clients use a different connection name in the tnsnames.ora file

  • The database names in the table are included in the files

    Database Name	tnsnames.ora on source	tnsnames.ora on destination	listner.ora on destination
    duplicate database name	√	√	√
    source database name		√

  Example

  The following are examples of the tnsnames.ora files for the source and destination clients. The source database name is db1_table and the destination database name is db1.

  Source client tnsnames.ora file

  db1_table =
  (DESCRIPTION = 
  (ADDRESS = (PROTOCOL = TCP)(HOST = M1)(PORT = 1521))
  (CONNECT_DATA =
  (SERVER = DEDICATED)
  (SERVICE_NAME = db1)
  )
  )
  

  Destination client tnsnames.ora file

  db1 = 
  (DESCRIPTION = 
  (ADDRESS = (PROTOCOL = TCP)(HOST = M2)(PORT = 1521))
  (CONNECT_DATA =
  (SERVER = DEDICATED)
  (SERVICE_NAME = db1)
  ) 
  )
  

Procedure

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

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

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

4. Optional: You can include or exclude any of the following tables in the restore: dependent tables, referenced tables, and recursively dependent tables.

   Right-click the table, and choose whether to include or exclude dependent or referenced tables.

   Option	Description
   Deselect All Dependent Tables	Exclude all dependent tables
   Select All Dependent Tables	Include all dependent tables
   Deselect All Dependent Tables	Exclude all recursive referenced tables
   Select All Dependent Tables	Include all recursive referenced tables
   Deselect All Tables	Exclude all referenced tables
   Select All Tables	Include all referenced tables

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

   The Table Restore Options dialog box appears.

6. On the Table Restore tab, in the Staging Path box, click Browse and select the location where the auxiliary instance is created.

   Note

   The staging path must have enough free space for the control file, the Oracle diagnostic directory, and the required tablespaces.

7. On the Advanced Options tab:

   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. To delete the existing tables on the destination before the restored tables are imported, select the Drop Table for Import check box.

8. To set the Commvault software to automatically move the database to the correct mode before the restore, select the Switch Database Mode for Restore check box.

9. 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 a 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.

10. Click OK to close the Table Restore Options dialog box.