What is the new In-Database Archiving in the latest Oracle 12c release?
On June 25, 2013, Oracle introduced a new feature called In-Database Archiving with its new release of Oracle 12. “In-Database Archiving enables you to archive rows within a table by marking them as inactive. These inactive rows are in the database and can be optimized using compression, but are not visible to an application. The data in these rows is available for compliance purposes if needed by setting a session parameter. With In-Database Archiving you can store more data for a longer period of time within a single database, without compromising application performance. Archived data can be compressed to help improve backup performance, and updates to archived data can be deferred during application upgrades to improve the performance of upgrades.”
This is an Oracle specific feature and does not apply to other databases.
To enable in database archiving on a table, you include the row archival clause when executing a “create” or “alter table” statement. This allows you to designate table rows as active or inactive (archived). Any queries that go against the table will only have visibility to active records in the table. When row archival is enabled, a hidden column called
ORA_ARCHIVE_STATE is created in the table. You can set the active or inactive state of the records within the table by setting the value of the corresponding hidden column value for that record to 0 or 1 (0 = active, 1 = inactive).
With this new feature in Oracle database why would you still need a database archiving solution?
The same reason that you still need a comprehensive backup solution on top of Oracle database backup and recovery features – because Oracle backup and disaster recovery functionality, such as redo log, undo records, control files, and RMAN provide a set of low level functionality that still require a higher level backup and recovery solution to make them easier to use. The database level set of functionality enable a backup and recovery solution like IBM Tivoli, Symantec, and Commvault to provide a more comprehensive set of features for monitoring and management.
In the same way, Oracle In-Database Archiving feature is a set of functionality that supports a more comprehensive database archiving solution to automate, complement, and make them easier to administer.
So here are the areas where a database archiving solution can complement or help you to better leverage Oracle In-Database Archiving feature:
- Definition of the criteria when data has become inactive – Database archiving solutions allow you to specify criteria or business rules when data can be considered inactive and can therefore be managed differently (e.g. partitioned, archived, purged, etc.)
- Automatically maintain data and referential integrity as you archive – Some database archiving solution allows you to define business entities, composed of groups of related tables that make up a transaction or at least define referential constraints between tables to ensure that inactive data are managed (e.g. partitioned, archived, purged, etc.) while maintaining data and referential integrity.
- Automation of In-Database Archiving with smart partitioning – Smart partitioning (smart partitioning is a standard feature in a database archiving solution like Informatica Data Archive) provides what is effectively In-Database Archiving with the automation based on business entities, business rules, segmentation policy management and execution, and explicit control of who can access which inactive records using access policies. The concept of access policy also enables the notion of “infrequently accessed” rather than an all or nothing access. A database archiving solution can eliminate the need for scripting to implement In-Database Archiving and simpler administration and management.
In Part 2 of this blog, I will continue our discussion with additional areas where a database archiving solution can complement or help you to better leverage the Oracle In-Database Archiving feature.