A Product of
Active Database Solutions, Inc.

The archive process consists of applying all transactions that occur to the production OLTP database to an archive database. This is done by capturing all of the transactions that were applied to the table in the OLTP database. The transactions are then applied, through a scheduled event, to an archive database with the same structure. Inserts and updates occur just as they do in the source database by creating a new record or updating the corresponding record in the archive database. With deletes you have the option of ignoring them or setting a flag on the archive table (deleted_flag=Y). If the process is marking deletes, when a delete occurs in the OLTP database, a the process will save the record to a holding table so the archive process will still be able to apply the record.

The archive process utilizes dynamic PL/SQL, with full bind variable support, and native database triggers to perform all required actions. It will also collapse all transactions, on a given row since the last archive process ran, to a single transaction to apply on the archive database. This means that if a row was inserted, had 8 updates and was subsequently deleted, the archive process would just perform an insert into the archive database with deleted_flag=Y, assuming the process is marking deletes.

The process is very resilient and will continue processing transactions after an error condition has been met. Since all transactions to a record are treated as one logical unit of work, failure of any one transaction will not cause the entire process to fail, only that logical unit of work is rolled back. Once the issue surrounding that error condition is corrected, that record will automatically be applied to the archive database. The process will continue until a configurable number of error conditions, default 6, are encountered, at that point the process assumes that the error must be catastrophic and the job is stopped.