The current ethical climate of computing unfortunately mandates the need to identify who made what changes to what data, when and from where. Corporations must now comply with stringent data change auditing mandates associated with such regulations as SOX (Sarbanes Oxley Act), FISMA (Federal Information Security Management Act); to name a couple. Prior to Oracle 11gR2, the Capture and Propagation of data change audit information had to be manually included in Streamed environments. Oracle 11gR2 introduces the DBMS_STREAMS_ADM.MAINTAIN_CHANGE_TABLE procedure that allows the DBA to quickly configure a separate change audit table for a Streamed table, as well as to propagate the change audit data from the source site to all target destination sites. This procedure can also be used to create one-way replication of a table along with the change capture from a source to a destination database. Change tables can be implemented for local or downstream capture, and local or remote apply configurations.
The DBMS_STREAMS_ADM.MAINTAIN_CHANGE_TABLE procedure creates all the components necessary to capture, send, and record data change information to the change table.
The DBMS_STREAMS_ADM.MAINTAIN_CHANGE_TABLE procedure is run at the capture site and accomplishes the following:
Creates a separate change table for the change audit data
The change table can be located in the same database or a remote database
The change table columns tracked for its source table are based on the
column_type_listAdditional audit data columns that can be added to the change table include:
value_typesource_database_namecommand_typeobject_ownerobject_nametagtransaction_idscncommit_scncompatibleinstance_numbermessage_numberrow_textrow_idserial#session#source_timethread#tx_nameusername
Creates a Capture process to capture source table changes
Creates a Propagation process for remote apply
Creates an Apply process at the destination site
Creates and adds DML handlers to the specified Apply process that record change information in the change table based on row LCR information
Configures the Apply process to execute the row LCR on the remote source table if
EXECUTE_LCRisTRUEThis supports the optional one-way replication.
The following figure shows configuration with local capture, remote Apply with Source table replication.
Before configuring change tables, you want to make decisions as to:
The source table columns to track
If/what metadata to record
The values to Track for Update Operations (old, new)
Whether to configure a
KEEP_COLUMNStransformationWhether to specify
CREATE TABLEoptions for the Change TableWhether to perform the Configuration Actions Directly or with a Script
Whether to replicate the Source Table as well
Note
For more information on Streams Change Tables, please refer to Chapter 20, Using Oracle Streams to Record Table Changes in the Oracle Streams Concepts and Administration 11g Release 2 Guide.
For more information on the MAINTAIN_CHANGE_TABLE procedure, please reference the Oracle PL/SQL Packages and Types Reference DBMS_STREAMS_ADM: subprogram MAINTAIN_CHANGE_TABLE.