Streams Change tables

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_list
    • Additional audit data columns that can be added to the change table include:
      • 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
  • 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_LCR is TRUE

    This supports the optional one-way replication.

The following figure shows configuration with local capture, remote Apply with Source table replication.

Streams Change tables

Before configuring change tables, you want to make decisions as to:

  • The type of environment to configure
  • 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_COLUMNS transformation
  • Whether to specify CREATE TABLE options for the Change Table
  • Whether 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.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset