Loading...

Restoring Oracle Tables to the Same Host

Restore Oracle tables when you need to recover tables that have been logically corrupted or lost or after the table structure was modified.

By default, when you restore tables to the source database, the restore operation overwrites the existing tables in the destination database.

Note: If you use Oracle 12c, you can only perform this procedure on non-Container Databases (CDB).

Best Practice: Use the Oracle catalog when you perform a table-level restore on Oracle 11g. The restore needs the catalog in order to skip Oracle TTS 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.

Before You Begin

  1. Configure Oracle Subclients for Table Level Restores.
  2. Perform a full backup of the subclient.
  3. If the tables contain non-English characters, see Restoring Tables with Non-English Characters for Oracle.
  4. If you use an auxiliary instance, then you can avoid issues with the auxiliary database not starting with the default parameters. To the Oracle client, add the additional setting as shown in the following table.

    For instructions on how to add additional settings from the CommCell Console, see Add or Modify an Additional Setting.

    Property

    Value

    Name

    sORACLE_AUX_PARAMETER

    Category

    OracleAgent

    Type

    String

    Value

    The Oracle parameters to add to the auxiliary instance Oracle PFILE.

    Separate each parameter by a semi-colon (;).

    Example

    Add the parameters DB_4K_CACHE_SIZE=4M and DB_16K_CACHE_SIZE=2G

    sORACLE_AUX_PARAMETER DB_16K_CACHE_SIZE=2G;DB_4K_CACHE_SIZE=4M

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. Optional: You can include or exclude any of the following tables in the restore, dependent, referenced 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 Referenced Tables

    Exclude all referenced tables

    Select All Referenced 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, select the restore location:
    1. To restore the table to the same source database, select the Import to Source DB option.
    2. To restore the table to a different database on the same host, select the Import to a Different DB option, enter the Oracle database name in the Oracle Instance box.
  8. Optional: To delete the tables prior to the restore, on the Advanced Options tab, select the Drop Table for Import option.
  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 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.

Last modified: 5/3/2018 6:06:45 PM