Oracle Database 12c: In-Database Archiving to deal with logically discarded records

When a record is no longer active – it is logically discarded – you may want to delete it. However, the record may still be required – for compliancy and auditing reasons for example. Or to produce incidental reports. So actually deleting it may not be an option. In the past I have used approaches such as a VALID_YN column or an END_OF_LIFE Date column to mark rows that were really no longer alive. In my applications and using views or VPD policies I can make sure to exclude such rows. That has to be an explicit act and it is a little cumbersome and non-trivial.

Oracle Database 12c introduced the concept of in-database archiving, which is basically what I described overhead – provided by a standard database mechanism that of course the SQL engine knows how to interpret. It works very much like the VALID_YN column – but of course a little differently. The Oracle Documentations says the following: “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.” Note the emphasis on ‘without compromising performance’. It would seem that such in line archived records are skipped in a smart way during full table scans and other records retrieval operations. Note that such archived rows do logically still exist: declarative constraints include these rows in their evaluation of uniqueness and referential integrity.

I was going to demonstrate this functionality at length – and then I cam across this excellent write up by Robert Geier: http://blog.contractoracle.com/2013/06/oracle-12c-new-features-in-database-row.html that really says it all in terms of how to get going.

Well, maybe just a little.

To enable in database archiving on a table, you use the clause row archival on the create or an alter statement on the table. This allows you to designate table rows as active or archived. You can then perform queries on only the active rows within the table. When you specify this clause, a hidden column ORA_ARCHIVE_STATE is created in the table. The column is of data type VARCHAR2. You can specify a value of 0 or 1 for this column to indicate whether a row is active (0) or archived (1). If you do not specify a value for ORA_ARCHIVE_STATE when inserting data into the table, then the value is set to 0 (from the SQL Language reference).

It seems that in how it works out, this features is quite similar to what you could do using VPD (Virtual Private Database): add policy to a table that eliminates all rows from query results that do not have the value 0 in their ORA_ARCHIVE_STATE column. Yet these rows are still part of constraint checking and can also be still queried if the policy has no effect in a certain session. It seems to be part of every database edition – unlike VPD – and it also seems to have a better performance characteristic. I have not investigated that last part though.

I can see a simple migration path for tables that today use a VALID_YN column (that the application knows about) to the standard In-Database Archiving: alter table X row archival; then create a row level  update trigger that fires when the VALID_YN is set and set the ORA_ARCHIVE_STATE column accordingly. Depending on whether the session setting ROW ARCHIVE VISIBILITY is at ACTIVE (the default) or ALL – any row that has VALID_YN set to N will also have its ORA_ARCHIVE_STATE set to 1 and is therefore not visible in under default conditions.

A quick demo:

The last two select statements return 107 and 77 rows respectively – indicating the number of archived records.

create table emp as select * from employees
desc emp
alter table emp row archival;
select * from emp
select employee_id, last_name , ora_archive_state from emp
update emp set ora_archive_state = 1 where employee_id < 130 select employee_id, last_name , ora_archive_state from emp alter session set row archival visibility = active select employee_id, last_name , ora_archive_state from emp [/code]  

Resources

Oracle Database 12c documentation on In Database Archiving: docs.oracle.com/cd/E16655_01/server.121/e17613/part_lifecycle.htm#CHDDAIDE.

SQL Language Reference: http://docs.oracle.com/cd/E16655_01/server.121/e17209/statements_7002.htm#SQLRF01402

Blog article: http://blog.contractoracle.com/2013/06/oracle-12c-new-features-in-database-row.html