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:
column_type_list
value_type
source_database_name
command_type
object_owner
object_name
tag
transaction_id
scn
commit_scn
compatible
instance_number
message_number
row_text
row_id
serial#
session#
source_time
thread#
tx_name
username
EXECUTE_LCR
is TRUE
This 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:
KEEP_COLUMNS
transformation CREATE TABLE
options for the Change TableFor 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
.