Streams change tables — just tracking the "Facts" Ma'am

Streams Change Tables provide us with the ability to capture and replicate "data change audit information" that is often required to meet Regulatory Standards such as SOX, and FISMA. The Streams Change Tables are new in 11gR2 and are created and maintained using the DBMS_STREAMS_ADM.MAINTAIN_CHANGE_TABLE procedure. As discussed in Chapter 1, this procedure configures a separate change audit table for a Streamed table, and creates all the components necessary to capture, send, and record data change information to the change table. 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.

Before configuring Change tables you will want to have made the following decisions:

  • 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

For our purposes, we will make the following decisions:

  • Type of environment Single-Source Master
  • Table columns and values to track: all old and new
  • Metadata: username and time
  • No additional "create table" options
  • We will keep all columns
  • We will Replicate the source table as well

Based on this, we make the following call to DBMS_STREAMS_ADM.MAINTAIN_CHANGE_TABLE: 

begin
DBMS_STREAMS_ADM.MAINTAIN_CHANGE_TABLE(
change_table_name => 'HR.EMPLOYEES_CHANGES',
source_table_name => 'HR.EMPLOYEES',
column_type_list => 'EMPLOYEE_ID NUMBER(6), FIRST_NAME VARCHAR2(20),
LAST_NAME VARCHAR2(25), EMAIL VARCHAR2(25),
PHONE_NUMBER VARCHAR2(20), HIRE_DATE DATE,
JOB_ID VARCHAR2(10), SALARY NUMBER(8,2),
COMMISSION_PCT NUMBER(2,2),
MANAGER_ID NUMBER(6),
DEPARTMENT_ID NUMBER(4)',
extra_column_list => 'username,source_time',
capture_values => '*',
options_string => NULL,
script_name => 'changetable_employees.sql',
script_directory_object => 'SCRIPT_DIR',
perform_actions => TRUE,
capture_name =>'HR_CAPT_EMP',
propagation_name => 'HR_PROP_EMP',
apply_name => 'HR_APPLY_EMP',
source_database => 'STRM1',
destination_database => 'STRM2',
keep_change_columns_only => TRUE,
execute_lcr => TRUE);
end;
/

Note

Note: At the time of writing, the Authors have experienced potential SQL parsing issues with the DBMS_STREAMS_ADM.MAINTAIN_CHANGE_TABLE procedure on Linux. The Authors are working with Oracle to determine if the issues are related to parameter syntax/datatype or with internal processing. When the cause of the issues have been determined and resolved, this section will be updated to include additional information for the functionality of this new feature.

Some things to keep in mind when running the script:

  • The Change table is created at the database specified for the destination_database parameter.
  • There cannot be a table with the name specified for the change_table_name parameter at the database specified for the destination_database parameter.
  • The apply_name parameter can only be null if there are no Apply processes at the destination database.
  • If an apply_name is specified, no messaging client with the same name can exist at the destination database.
  • If specified capture, propagation, and/or Apply processes already exists, the procedures adds the rules to the positive rule set of the process. If the process does not exist, it will create the process.
  • If you specify the same database for the source_database and the destination_database, then you probably don't want to specify execute_lcr = TRUE as it could end up attempting to apply the same change back to the source table.
  • As with the other DBMS_STREAMS_ADM.MAINTAIN_* procedures, and with a new functionality, we highly recommend that you first run the procedure with perform_actions = FALSE, and have it generate a script that you can then review to fully understand what the procedure is going to do before it does it. You can then either run the script as-is from SQLPlus as the Streams Administrator, or modify the script as you see necessary.

Note

For more detailed information on Streams Change Tables and the DBMS_STREAMS_ADM.MAINTAIN_CHANGE_TABLE procedure, please reference the Oracle Streams Concepts and Administration Guide, and the Oracle PL/SQL Packages and Types Reference.

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

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