V11 SP8
Loading...

Configuring Oracle Archive Subclients

Applies to: The subclient on the production database.

Configuring an archive subclient includes the following configuration:

  • Select the Archive Database, including the client and the database name.
  • Select the path where the datafiles are created on the archive database.
  • Select the production database tables to be archived.
  • Select the format for the archived data.

You can store the data in any of the following formats:

  • Native database format
  • XML format

    When you store the data in the XML format, you can save the licensing cost for the archive destination. The XML format is not tied to your vendor, so you have flexibility when the application is retired.

  • Apache parquet format

For information about which format to select, see Choosing the Archive Format.

About This Task

You must provide the connect details to connect to the archive database and run all the archive operations. Use separate users for each archive entity so that all archived data and corresponding views are available under one schema. Once you do this, point applications to this user.

Configure the archive database user as a database administrator and assign the GRANT SELECT on DBA_TABLES privilege.

The archive database user can be configured under the archive subclient Options tab. By default the archive database instance connect details will be populated. You can change these user details.

Note: Do not use "/" (slash) or "sys" as the Connect string name.

Procedure

  1. From the CommCell Browser, expand Client Computers > client > Oracle.
  2. Right-click the instance, point to All Tasks, and then click New Archive Subclient.

    The Creating New Subclient dialog box appears.

  3. On the General tab, enter the Subclient Name.
  4. On the Archive Options tab, select the format to use for the archive.
    Format Configuration
    Native database format
    1. Select the Native option.
    2. Select the Archive Database check box.
    3. From the Client list, choose the computer that hosts the archive database.
    4. From the Database Name list, choose the name of the archive database.
    5. In the Connect Details box, type the connect details for the archive database.
    6. To allow applications that connect to the production database to access the production database tables and archived tables, with no additional application changes, select the Create uniform view for Active and History tables check box.
    7. In the Path for Archive Data Files box, type the path where the software creates the datafiles.
    XML Format
    1. Select the XML option.
    2. In the Path for Archive Data Files box, type the path where the software creates the datafiles.
    Parquet format
    1. Select the Parquet option.
    2. In the Path for Archive Data Files box, type the path where the software creates the datafiles.
  5. On the Storage Device tab, select the following options:
    1. From the Data Storage Policy list, select the storage policy that you want to use.
    2. In the Number of Data Backup Streams box, type the number of streams that the software users to back up the files to the secondary media.
  6. On the Contents tab, specify the data to archive:
    1. Click the Add Target Table button.

      The Archiving Configuration Wizard dialog box is displayed.

    2. Select the archiving target table information.
      • From the Schema list, select the archiving target table schema.
      • From the Select Archiving Target Table list, select the table to archive.
      • To add all or a subset of the child tables, select the Add Child Tables check box.
      • To add all or a subset of the parent tables, select the Add Archiving Target Parent Tables check box .
      • Optional: To include the child tables in the archive, select the Add Parents of Children check box.

    3. Click Next.

      The archive target table and corresponding dependent tables are populated.

    4. Click Finish.
    5. To build a query against the table, in the Archiving Target Table area, right-click a table, and then click Specify Archive Criteria.

      The Specify Archive Criteria dialog box appears.

      Option Configuration
      Type the query
      1. In the SELECT …FROM box, type the WHERE clause of the SQL query.
      2. Optional: To delete the rows from the production database after the query, select the Delete Rows check box.
      3. Click OK.
      Create the query in the user interface.
      1. In the Columns box, double-click the table column to query against.

        The Select ...FROM area displays the column selection.

      2. In the Operators box, double-click the operator to use for the query.

        The Select ...FROM area displays the operator after the column selection.

      3. In the Select ...FROM box,  type the value that you want to use in the query. When you have to enter 2 values (for example, using "BETWEEN AND"), separate each value with a space.
      4. Optional: To delete the rows from the production database after the query, select the Delete Rows check box.
      5. Click OK.
  7. Set the data aging rules. On the Archive Options tab, under Data Retention Settings, select the following options:
    1. In the Archive Tables box, type the number of days to retain the data on the archive database (for the native format), or the disk (for the XML format, or the parquet format).
    2. In the Media box, type the number of days to retain the data on the storage media. For more information, see Retention Rules for Secondary Media.
  8. Optional: Run scripts before or after the backup.

    You can choose to run the post-process script even if the backup job failed.

    On the Pre/Post Process tab:

    1. In the Pre-Archive Command box, type the full path name for the script.
    2. In the Post-Archive Command box, type the full path name for the script.
    3. To run the post backup process regardless of the job's outcome, select the Run Post Process for all attempts check box.
  9. Click OK.

Example

One of the most common archive criteria is using the date to determine what gets archived.

If you use the following order table:

Column Type
ORDER_ID NUMBER(12,0)
ORDER_DATE DATE
ORDER_MODE VARCHAR2(8,BYTE)
CUSTOMER_ID NUMBER(6,0)
ORDER_STATUS NUMBER(2,0)
ORDER_TOTAL NUMBER(8,2)
SALES_REP_ID NUMBER(6,0)
PROMOTION_ID NUMBER(6,0)

To archive the records with an ORDER_DATE older than 1 year, type the following query in the Select ...FROM box.

WHERE CV_ARCH_SYSDATE – ORDER_DATE > 365

Note: CV_ARCH_SYSDATE is a Commvault software-defined macro that places the archive job start time in the appropriate format for specifying the archive criteria.