Loading...

Accessing Archived Data

Query archived data and retrieve the associated data without restoring archive data.

Uniform Production Database View

The archive database view contains only the archived data.

The uniform production database view contains both the active (source) and archived data. Applications use this view to access all data when required.

The uniform production database view is created under the same schema used for archiving. The production database user must be used to access the uniform production database view. For example, if you use "archive_user" to archive the data from the production database, the database administrator must configure the application to connect to the production database by using "archive_user" in order to access both the active and archived data on the production database.

The archive database view and the production database views are created when an archive job runs. The archive database view is always created. The uniform production database view is created only when the following option is set.

  1. From the CommCell Browser, expand Client Computers > client > Oracle >instance.
  2. Right-click the archive_subclient, and then click Properties.

    The Subclient Properties dialog box appears.

  3. On the Archive Options tab, select the Create uniform view on source for Active and Archive Data check box.
  4. Click OK to close the Subclient Properties dialog box.

The Create uniform view on source for Active and Archive Data option is selected by default, which helps users access archived data that has been pruned from the database with the Delete Rows option. When the Create uniform view on source for Active and Archive Data option is selected a view with the same archived table name is created on the source database under the same schema used to archive the data. The view contains the source (active) and archived data. The view is updated by a union of each archive job.

For example if Jobid 100 picks up 2 records with status=’A’ and Jobid 101 picks up 5 records for archiving, the views for the databases are constructed as follows:

Archive Database View

CREATE OR REPLACE VIEW archiveuser.<sourcetable> AS SELECT * FROM archiveuser.<archivetable_jobid100> UNION ALL SELECT * FROM archiveuser.<archivetable_jobid101>

Production Database View

CREATE OR REPLACE VIEW sourcearchiveuser.<sourcetable> AS SELECT * FROM source_schema.<sourcetable> UNION ALL SELECT * FROM archiveuser.<archivedbview>@<archivedblink>

To not have a uniform production database view, clear the Create uniform view on source for Active and Archive Data check box on the Archive Options tab of the Subclient Properties dialog box.

Searching Archived Data

Procedure

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

    The Browse and Restore Options dialog box appears.

  3. On the  Search Archived Data tab, specify the search options
    1. Enter a valid SQL query in the box.
    2. Click Search Archived Data.
    3. Type the Oracle source (production) database credentials.
    4. Click OK.

The results of the query are displayed in the Data Preview dialog box.

Example

In this example, there is a table ORDERS where orders that are over a year old have been archived. The administrator has configured the columns in bold to be available in the archive.

Column

Type

ORDER_ID

NUMBER(12,0)

ORDER_DATE

DATE

ORDER_MODE

VARCHAR2(8,BYTE)

CUSTOMER_NAME

VARCHAR2(8,BYTE)

ORDER_STATUS

NUMBER(2,0)

ORDER_TOTAL

NUMBER(8,2)

SALES_REP_ID

NUMBER(6,0)

PROMOTION_ID

NUMBER(6,0)

If you need to see the orders that have been archived for customer 'CommVault', use the query below.

Select * from ORDERS where ORDERS.CUSTOMER_NAME=''CommVault'

The result includes each record that matches the query with the following columns:

  • ORDER_ID
  • ORDER_DATE
  • CUSTOMER_NAME
  • ORDER TOTAL

Last modified: 9/5/2018 2:36:06 PM