Loading...

Oracle Database Archiving

Business and regulatory needs are driving Enterprises to store data for longer time in the production databases, causing them to significantly grow. The increase in size leads to larger storage requirements, database performance issues and longer backup and recovery time. The SnapProtect Oracle Database Archiving provides a comprehensive solution by securely archiving inactive Oracle data to both an archiving database and backup media, while providing seamless access of the archived data from the production database.

Database administrators define the tables and the criteria to select inactive rows for archiving. All related tables from the existing database relationships or new user-defined virtual relationships can be included for archiving. Archived data can also be retained in both the archive database and on the media using different retention settings. Tools include archive data size prediction, previewing the archive criteria for each table and also the data that will be archived.

Archive Operation

The SnapProtect archiving process is shown in the picture.

The software copies the production database table data to the archive database, which can be on a different host. Archive tables are created on the archive database. The data on the archive database is then backed up to the backup media. It remains on the archive database until the configured data retention expires. The backup media includes two pieces of information, the archived data search index and the archived data. Data remains on the backup media for the specified media retention, which is independent from the archive database retention. The archived rows are pruned from the production database (when configured). Data retention settings can be configured for both the archive database and the backup media.

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

    The Apache Parquet format is application independent and uses less storage because of the compression. For information about Apache Parquet, go to the Apache Parquet web site.

Restore Operation

Users can search archived data on the backup media to determine if a given table or row was archived and if so, retrieve the details to the staging or production database.

The restore archive operation takes data from the backup media and moves it to a staging database, which can be the archive database. The data is then imported into the source database. Once the data has been successfully restored to the production database, it is removed from the staging database.

Data is automatically restored as follows:

  1. When the data is on the archive/staging database, the data is restored from the archive/staging database to the production database.
  2. When the data is not on the archive/staging database, the data is restored from the backup media to the archive/staging database and then to the production database.

All of the data, or a subset based on user defined queries can be imported into the source database. Once it has been successfully imported, the data is removed from the staging database.

Key Benefits

  • Lower production database storage costs while optimizing performance by safely archiving inactive data and removing it from the production database
  • Multiple production databases can use the same archiving/staging database
  • Tools include a way to preview both the size of the archive data as well as the table rows that will be archived. Based on the results, you can modify the script to add or remove all relevant data.
  • Automatically include database defined child/parent tables as part of the archive.
  • Create additional table relationships to meet business rules.
  • Application users and reporting applications can query and access the archived data along with the production data with no operational or configuration changes.
  • Restore all or part of the archived data by creating queries.
  • Automatically clean the archived database after a restore.

Terminology

Production (source) database - The database containing production data that needs to be archived.

Archive database - Production data is copied to this database during the archive process. For archives, data is copied here prior to moving to the backup media. The database must be in the OPEN mode (up and running) for archiving.

Backup Media - The storage media used for long term retention of archived data.

Staging (destination) database - The database used during the restore operation. Data is moved from the backup media to here and then moved to the production database. Data is not retained on the staging database after a successful restore. The archive database can serve as the staging database. The database must be in the OPEN mode (up and running) for archiving.

Archive Table - A table from which inactive rows are archived based on archive criteria.