16 Change Data Capture

Capturing Change Data Without Change Data Capture

Prior to the introduction of Change Data Capture, there were a number of ways that users could capture change data, including table differencing and change-value selection.

Table differencing involves transporting a copy of an entire table from the source (production) database to the staging database (where the change data is captured), where an older version of the table already exists. Using the SQL MINUS operator, you can obtain the inserted and new versions of updated rows with the following query:

SELECT * FROM new_version 
MINUS SELECT * FROM old_version;

Moreover, you can obtain the deleted rows and old versions of updated rows with the following query:

SELECT * FROM old_version 
MINUS SELECT * FROM new_version;

However, there are several problems with this method:

• It requires that the new version of the entire table be transported to the staging database, not just the change data, thereby greatly increasing transport costs.

• The computational cost of performing the two MINUS operations on the staging database can be very high.

• Table differencing cannot capture data changes that have reverted to their old values. For example, suppose the price of a product changes several times between the old version and the new version of the product's table. If the price in the new version ends up being the same as the old, table differencing cannot detect that the price has fluctuated. Moreover, any intermediate price values between the old and new versions of the product's table cannot be captured using table differencing.

• There is no way to determine which changes were made as part of the same transaction. For example, suppose a sales manager creates a special discount to close a deal. The fact that the creation of the discount and the creation of the sale occurred as part of the same transaction cannot be captured, unless the source database is specifically designed to do so.

Change-value selection involves capturing the data on the source database by selecting the new and changed data from the source tables based on the value of a specific column. For example, suppose the source table has a LAST_UPDATE_DATE column. To capture changes, you base your selection from the source table on the LAST_UPDATE_DATE column value.

However, there are also several problems with this method:

• The overhead of capturing the change data must be borne on the source database, and you must run potentially expensive queries against the source table on the source database. The need for these queries may force you to add indexes that would otherwise be unneeded. There is no way to offload this overhead to the staging database.

• This method is no better at capturing intermediate values than the table differencing method. If the price in the product's table fluctuates, you will not be able to capture all the intermediate values, or even tell if the price had changed, if the ending value is the same as it was the last time that you captured change data.

• This method is also no better than the table differencing method at capturing which data changes were made together in the same transaction. If you need to capture information concerning which changes occurred together in the same transaction, you must include specific designs for this purpose in your source database.

• The granularity of the change-value column may not be fine enough to uniquely identify the new and changed rows. For example, suppose the following:

  • You capture data changes using change-value selection on a date column such as LAST_UPDATE_DATE.

  • The capture happens at a particular instant in time, 14-FEB-2003 17:10:00.

  • Additional updates occur to the table during the same second that you performed your capture.

  When you next capture data changes, you will select rows with a LAST_UPDATE_DATE strictly after 14-FEB-2003 17:10:00, and thereby miss the changes that occurred during the remainder of that second.

  To use change-value selection, you either have to accept that anomaly, add an artificial change-value column with the granularity you need, or lock out changes to the source table during the capture process, thereby further burdening the performance of the source database.

• You have to design your source database in advance with this capture mechanism in mind – all tables from which you wish to capture change data must have a change-value column. If you want to build a data warehouse with data sources from legacy systems, those legacy systems may not supply the necessary change-value columns you need.

Change Data Capture does not depend on expensive and cumbersome table differencing or change-value selection mechanisms. Instead, it captures the change data resulting from INSERT, UPDATE, and DELETE operations made to user tables. The change data is then stored in a relational table called a change table, and the change data is made available to applications or individuals in a controlled way.

Capturing Change Data with Change Data Capture

Change Data Capture can capture and publish committed change data in either of the following modes:

• Synchronous

  Change data is captured immediately, as each SQL statement that performs a data manipulation language (DML) operation (INSERT, UPDATE, or DELETE) is made, by using triggers on the source database. In this mode, change data is captured as part of the transaction modifying the source table. Synchronous Change Data Capture is available with Oracle Standard Edition and Enterprise Edition.

• Asynchronous

  Change data is captured after a SQL statement that performs a DML operation is committed, by taking advantage of the data sent to the redo log files. In this mode, change data is not captured as part of the transaction that is modifying the source table, and therefore has no effect on that transaction. Asynchronous Change Data Capture is available with Oracle Enterprise Edition only.

  Asynchronous Change Data Capture is built on, and provides a relational interface to, Oracle Streams. See Oracle Streams Concepts and Administration for information on Oracle Streams.

The following list describes the advantages of capturing change data with Change Data Capture:

• Completeness

  Change Data Capture can capture all effects of INSERT, UPDATE, and DELETE operations, including data values before and after UPDATE operations.

• Performance

  Asynchronous Change Data Capture can be configured to have minimal performance impact on the source database.

• Interface

  Change Data Capture includes the PL/SQL DBMS_CDC_PUBLISH and DBMS_CDC_SUBSCRIBE packages, which provide easy-to-use publish and subscribe interfaces.

• Cost

  Change Data Capture reduces overhead cost because it simplifies the extraction of change data from the database and is part of Oracle9i and later databases.

A Change Data Capture system is based on the interaction of a publisher and subscribers to capture and distribute change data, as described in the next section.

Publish and Subscribe Model

Most Change Data Capture systems have one person who captures and publishes change data; this person is the publisher. There can be multiple applications or individuals that access the change data; these applications and individuals are the subscribers. Change Data Capture provides PL/SQL packages to accomplish the publish and subscribe tasks.

The following sections describe the roles of the publisher and subscriber in detail. Subsequent sections describe change sources, more about modes of Change Data Capture, and change tables.

Publisher

The publisher is usually a database administrator (DBA) who creates and maintains the schema objects that make up the Change Data Capture system. Typically, a publisher deals with two databases:

• Source database

  This is the production database that contains the data of interest. Its associated tables are referred to as the source tables.

• Staging database

  This is the database where the change data capture takes place. Depending on the capture mode that the publisher uses, the staging database can be the same as, or different from, the source database. The following Change Data Capture objects reside on the staging database:

  • Change table

    A change table is a relational table that contains change data for a single source table. To subscribers, a change table is known as a publication.

  • Change set

    A change set is a set of change data that is guaranteed to be transactionally consistent. It contains one or more change tables.

  • Change source

    The change source is a logical representation of the source database. It contains one or more change sets.

The publisher performs these tasks:

• Determines the source databases and tables from which the subscribers are interested in viewing change data, and the mode (synchronous or asynchronous) in which to capture the change data.

• Uses the Oracle-supplied package, DBMS_CDC_PUBLISH, to set up the system to capture change data from the source tables of interest.

• Allows subscribers to have controlled access to the change data in the change tables by using the SQL GRANT and REVOKE statements to grant and revoke the SELECT privilege on change tables for users and roles. (Keep in mind, however, that subscribers use views, not change tables directly, to access change data.)

In Figure 16-1, the publisher determines that subscribers are interested in viewing change data from the HQ source database. In particular, subscribers are interested in change data from the SH.SALES and SH.PROMOTIONS source tables.

The publisher creates a change source HQ_SRC on the DW staging database, a change set, SH_SET, and two change tables: sales_ct and promo_ct. The sales_ct change table contains all the columns from the source table, SH.SALES. For the promo_ct change table, however, the publisher has decided to exclude the PROMO_COST column.

Figure 16-1 Publisher Components in a Change Data Capture System

Description of the illustration pub_components.gif

Subscribers

The subscribers are consumers of the published change data. A subscriber performs the following tasks:

• Uses the Oracle supplied package, DBMS_CDC_SUBSCRIBE, to:

  • Create subscriptions

    A subscription controls access to the change data from one or more source tables of interest within a single change set. A subscription contains one or more subscriber views.

    A subscriber view is a view that specifies the change data from a specific publication in a subscription. The subscriber is restricted to seeing change data that the publisher has published and has granted the subscriber access to use. See "Subscribing to Change Data" for more information on choosing a method for specifying a subscriber view.

  • Notify Change Data Capture when ready to receive a set of change data

    A subscription window defines the time range of rows in a publication that the subscriber can currently see in subscriber views. The oldest row in the window is called the low boundary; the newest row in the window is called the high boundary. Each subscription has its own subscription window that applies to all of its subscriber views.

  • Notify Change Data Capture when finished with a set of change data

• Uses SELECT statements to retrieve change data from the subscriber views.

A subscriber has the privileges of the user account under which the subscriber is running, plus any additional privileges that have been granted to the subscriber.

In Figure 16-2, the subscriber is interested in a subset of columns that the publisher (in Figure 16-1) has published. Note that the publications shown in Figure 16-2, are represented as change tables in Figure 16-1; this reflects the different terminology used by subscribers and publishers, respectively.

The subscriber creates a subscription, sales_promos_list and two subscriber views (spl_sales and spl_promos) on the SH_SET change set on the DW staging database. Within each subscriber view, the subscriber includes a subset of the columns that were made available by the publisher. Note that because the publisher did not create a change table that includes the PROMO_COST column, there is no way for the subscriber to view change data for that column.

Figure 16-2 Subscriber Components in a Change Data Capture System

Description of the illustration sub_components.gif

Change Data Capture provides the following benefits for subscribers:

• Guarantees that each subscriber sees all the changes

• Keeps track of multiple subscribers and gives each subscriber shared access to change data

• Handles all the storage management by automatically removing data from change tables when it is no longer required by any of the subscribers

  
  

  Note: Oracle provides the previously listed benefits only when the subscriber accesses change data through a subscriber view.

  
  

Synchronous

This mode uses triggers on the source database to capture change data. It has no latency because the change data is captured continuously and in real time on the source database. The change tables are populated when DML operations on the source table are committed.

While the synchronous mode of Change Data Capture adds overhead to the source database at capture time, this mode can reduce costs (as compared to attempting to extract change data using table differencing or change-value section) by simplifying the extraction of change data.

There is a single, predefined synchronous change source, SYNC_SOURCE, that represents the source database. This is the only synchronous change source. It cannot be altered or dropped.

Change tables for this mode of Change Data Capture must reside locally in the source database.

Figure 16-3 illustrates the synchronous configuration. Triggers executed after DML operations occur on the source tables populate the change tables in the change sets within the SYNC_SOURCE change source.

Figure 16-3 Synchronous Change Data Capture Configuration

Description of the illustration synch.gif

Asynchronous

This mode captures change data after the changes have been committed to the source database by using the database redo log files.

The asynchronous mode of Change Data Capture is dependent on the level of supplemental logging enabled at the source database. Supplemental logging adds redo logging overhead at the source database, so it must be carefully balanced with the needs of the applications or individuals using Change Data Capture. See "Asynchronous Change Data Capture and Supplemental Logging" for information on supplemental logging.

There are two methods of capturing change data asynchronously, HotLog and AutoLog, as described in the following sections:

HotLog

Change data is captured from the online redo log file on the source database. There is a brief latency between the act of committing source table transactions and the arrival of change data.

There is a single, predefined HotLog change source, HOTLOG_SOURCE, that represents the current redo log files of the source database. This is the only HotLog change source. It cannot be altered or dropped.

Change tables for this mode of Change Data Capture must reside locally in the source database.

Figure 16-4, illustrates the asynchronous HotLog configuration. The Logwriter Process (LGWR) records committed transactions in the online redo log files on the source database. Change Data Capture uses Oracle Streams processes to automatically populate the change tables in the change sets within the HOTLOG_SOURCE change source as newly committed transactions arrive.

Figure 16-4 Asynchronous HotLog Configuration

Description of the illustration hotlog.gif

AutoLog

Change data is captured from a set of redo log files managed by log transport services. Log transport services control the automated transfer of redo log files from the source database to the staging database. Using database initialization parameters (described in "Initialization Parameters for Asynchronous AutoLog Publishing"), the publisher configures log transport services to copy the redo log files from the source database system to the staging database system and to automatically register the redo log files. Change sets are populated automatically as new redo log files arrive. The degree of latency depends on frequency of redo log switches on the source database.

There is no predefined AutoLog change source. The publisher provides information about the source database to create an AutoLog change source. See "Performing Asynchronous AutoLog Publishing" for details.

Change sets for this mode of Change Data Capture can be remote from or local to the source database. Typically, they are remote.

Figure 16-5 shows a typical Change Data Capture asynchronous AutoLog configuration in which, when the log switches on the source database, archiver processes archive the redo log file on the source database to the destination specified by the LOG_ARCHIVE_DEST_1 parameter and copy the redo log file to the staging database as specified by the LOG_ARCHIVE_DEST_2 parameter. (Although the image presents these parameters as LOG_ARCHIVE_DEST_1 and LOG_ARCHIVE_DEST_2, the integer value in these parameter strings can be any value between 1 and 10.)

Note that the archiver processes use Oracle Net to send redo data over the network to the remote file server (RFS) process. Transmitting redo log files to a remote destination requires uninterrupted connectivity through Oracle Net.

On the staging database, the RFS process writes the redo data to the copied log files in the location specified by the value of the TEMPLATE attribute in the LOG_ARCHIVE_DEST_2 parameter (specified in the source database initialization parameter file). Then, Change Data Capture uses Oracle Streams downstream capture to populate the change tables in the change sets within the AutoLog change source.

Figure 16-5 Asynchronous AutoLog Change Data Capture Configuration

Description of the illustration autolog.gif

Valid Combinations of Change Sources and Change Sets

Table 16-1 summarizes the valid combinations of change sources and change sets and indicates whether each is predefined or publisher-defined. In addition, Table 16-1 indicates whether the change source represents a local or remote source database, and whether the change source is used for synchronous or asynchronous Change Data Capture.

Creating a User to Serve As a Publisher

Typically, a DBA creates a user to serve as a publisher for Change Data Capture. The following sections describe the tasks involved.

ORAPWD FILE=orapw PASSWORD=mypassword ENTRIES=10

Determining the Mode in Which to Capture Data

Three factors influence the decision on the mode in which to capture change data:

• Whether or not the staging database is remote from the source database

• Tolerance for latency between changes made on the source database and changes captured by Change Data Capture

• Performance impact on the source database transactions and overall database performance

Table 16-4 summarizes the factors that might influence the mode decision.

Setting Initialization Parameters for Change Data Capture Publishing

Initialization parameters must be set on the source or staging database, or both, for Change Data Capture to succeed. Which parameters to set depend on the mode in which Change Data Capture is publishing change data, and on whether the parameters are being set on the source or staging database.

The following sections describe the database initialization parameter settings for each mode of Change Data Capture. Sometimes you are directed to add a value to a current setting. See "Determining the Current Setting of an Initialization Parameter" to determine the current setting for a parameter if that parameter is not explicitly specified in the initialization parameter file. See Oracle Database Reference and Oracle Data Guard Concepts and Administration for more information about these database initialization parameters.

JAVA_POOL_SIZE = 50000000

SQL> SHOW PARAMETERS

SQL> SHOW PARAMETER STREAMS_POOL_SIZE

SELECT cp.value FROM DBA_CAPTURE_PARAMETERS cp, CHANGE_SETS cset
   WHERE cset.SET_NAME = 'CHICAGO_DAILY' and
   cset.CAPTURE_NAME = cp.CAPTURE_NAME and
   cp.PARAMETER = 'PARALLELISM';

SELECT ap.value FROM DBA_APPLY_PARAMETERS ap, CHANGE_SETS cset
   WHERE cset.SET_NAME = 'CHICAGO_DAILY' and
   cset.APPLY_NAME = ap.APPLY_NAME and
   ap.parameter = 'PARALLELISM';

Performing Synchronous Publishing

For synchronous Change Data Capture, the publisher must use the predefined change source, SYNC_SOURCE. The publisher can define new change sets or can use the predefined change set, SYNC_SET. The publisher should not create change tables on source tables owned by SYS or SYSTEM because triggers will not fire and therefore changes will not be captured.

This example shows how to create a change set. If the publisher wants to use the predefined SYNC_SET, he or she should skip Step 3 and specify SYNC_SET as the change set name in the remaining steps.

This example assumes that the publisher and the source database DBA are two different people.

Step 1 Source Database DBA: Set the JAVA_POOL_SIZE parameter.

The source database DBA sets the database initialization parameters, as described in "Setting Initialization Parameters for Change Data Capture Publishing".

java_pool_size = 50000000

Step 2 Source Database DBA: Create and grant privileges to the publisher.

The source database DBA creates a user (for example, cdcpub), to serve as the Change Data Capture publisher and grants the necessary privileges to the publisher so that he or she can perform the operations needed to create Change Data Capture change sets and change tables on the source database, as described in "Creating a User to Serve As a Publisher". This example assumes that the tablespace ts_cdcpub has already been created.

CREATE USER cdcpub IDENTIFIED BY cdcpub DEFAULT TABLESPACE ts_cdcpub
QUOTA UNLIMITED ON SYSTEM 
QUOTA UNLIMITED ON SYSAUX;
GRANT CREATE SESSION TO cdcpub;
GRANT CREATE TABLE TO cdcpub;
GRANT CREATE TABLESPACE TO cdcpub;
GRANT UNLIMITED TABLESPACE TO cdcpub;
GRANT SELECT_CATALOG_ROLE TO cdcpub;
GRANT EXECUTE_CATALOG_ROLE TO cdcpub;
GRANT CONNECT, RESOURCE TO cdcpub;

Step 3 Staging Database Publisher: Create a change set.

The publisher uses the DBMS_CDC_PUBLISH.CREATE_CHANGE_SET procedure on the staging database to create change sets.

The following example shows how to create a change set called CHICAGO_DAILY:

BEGIN
    DBMS_CDC_PUBLISH.CREATE_CHANGE_SET( 
    change_set_name    => 'CHICAGO_DAILY', 
    description        => 'Change set for job history info', 
    change_source_name => 'SYNC_SOURCE');
END;
/

The change set captures changes from the predefined change source SYNC_SOURCE. Because begin_date and end_date parameters cannot be specified for synchronous change sets, capture begins at the earliest available change data and continues capturing change data indefinitely.

Step 4 Staging Database Publisher: Create a change table.

The publisher uses the DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE procedure to create change tables.

The publisher can set the options_string field of the DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE procedure to have more control over the physical properties and tablespace properties of the change table. The options_string field can contain any option, except partitioning, that is available in the CREATE TABLE statement.

The following example creates a change table that captures changes that occur on a source table. The example uses the sample table HR.JOB_HISTORY as the source table. It assumes that the publisher has already created the TS_CHICAGO_DAILY tablespace.

BEGIN
   DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE(

owner => 'cdcpub',
   change_table_name => 'jobhist_ct', 
   change_set_name => 'CHICAGO_DAILY',
   source_schema => 'HR',
   source_table => 'JOB_HISTORY',
   column_type_list => 'EMPLOYEE_ID NUMBER(6),START_DATE DATE, 
                        END_DATE DATE,JOB_ID VARCHAR2(10), 
                        DEPARTMENT_ID NUMBER(4)',
   capture_values => 'both',
   rs_id => 'y', 
   row_id => 'n',
   user_id => 'n',
   timestamp => 'n',
   object_id => 'n',
   source_colmap => 'y',
   target_colmap => 'y',
   options_string => 'TABLESPACE TS_CHICAGO_DAILY');
END;
 /

This statement creates a change table named jobhist_ct within the change set CHICAGO_DAILY. The column_type_list parameter identifies the columns captured by the change table. The source_schema and source_table parameters identify the schema and source table that reside in the source database.

The capture_values setting in the example indicates that for update operations, the change data will contain two separate rows for each row that changed: one row will contain the row values before the update occurred, and the other row will contain the row values after the update occurred.

Step 5 Staging Database Publisher: Grant access to subscribers.

The publisher controls subscriber access to change data by granting and revoking the SELECT privilege on change tables for users and roles. The publisher grants access to specific change tables. Without this step, a subscriber cannot access any change data. This example assumes that user subscriber1 already exists.

GRANT SELECT ON cdcpub.jobhist_ct TO subscriber1;

The Change Data Capture synchronous system is now ready for subscriber1 to create subscriptions.

Performing Asynchronous HotLog Publishing

Change Data Capture uses Oracle Streams local capture to perform asynchronous HotLog publishing. (See Oracle Streams Concepts and Administration for information on Streams local capture.)

For asynchronous HotLog Change Data Capture, the publisher must use the predefined change source, HOTLOG_SOURCE, and must create the change sets and the change tables that will contain the changes. The staging database is always the source database. This example assumes that the publisher and the source database DBA are two different people.

The following steps set up redo logging, Oracle Streams, and Change Data Capture for asynchronous HotLog publishing:

Step 1 Source Database DBA: Set the database initialization parameters.

The source database DBA sets the database initialization parameters, as described in "Setting Initialization Parameters for Change Data Capture Publishing". In this example, one change set will be defined and the current value of the STREAMS_POOL_SIZE parameter is 50 MB or greater.

compatible = 10.1.0
java_pool_size = 50000000;
job_queue_processes = 2

parallel_max_servers = <current value> + 5
processes = <current value> + 7
sessions = <current value> + 2
streams_pool_size = <current value> + 21 MB
undo_retention = 3600

Step 2 Source Database DBA: Alter the source database.

The source database DBA performs the following three tasks. The second is required. The first and third are optional, but recommended. It is assumed that the database is currently running in ARCHIVELOG mode.

1. Place the database into FORCE LOGGING logging mode to protect against unlogged direct write operations in the source database that cannot be captured by asynchronous Change Data Capture:

   ALTER DATABASE FORCE LOGGING;
   
   

2. Enable supplemental logging. Supplemental logging places additional column data into a redo log file whenever an UPDATE operation is performed. Minimally, database-level minimal supplemental logging must be enabled for any Change Data Capture source database:

   ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
   
   

3. Create an unconditional log group on all columns to be captured in the source table. Source table columns that are unchanged and are not in an unconditional log group, will be null in the change table, instead of reflecting their actual source table values. (This example captures rows in the HR.JOB_HISTORY table only. The source database DBA would repeat this step for each source table for which change tables will be created.)

   ALTER TABLE HR.JOB_HISTORY
   ADD SUPPLEMENTAL LOG GROUP log_group_jobhist
     (EMPLOYEE_ID, START_DATE, END_DATE, JOB_ID, DEPARTMENT_ID) ALWAYS;
   
   

   If you intend to capture all the column values in a row whenever a column in that row is updated, you can use the following statement instead of listing each column one-by-one in the ALTER TABLE statement. However, do not use this form of the ALTER TABLE statement if all columns are not needed. Logging all columns incurs more overhead than logging selected columns.

   ALTER TABLE HR.JOB_HISTORY ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
   
   

   See Oracle Database Administrator's Guide for information about running a database in ARCHIVELOG mode. See "Asynchronous Change Data Capture and Supplemental Logging" for more information on enabling supplemental logging.

Step 3 Source Database DBA: Create and grant privileges to the publisher.

The source database DBA creates a user, (for example, cdcpub), to serve as the Change Data Capture publisher and grants the necessary privileges to the publisher so that he or she can perform the underlying Oracle Streams operations needed to create Change Data Capture change sets, and change tables on the source database, as described in "Creating a User to Serve As a Publisher". This example assumes that the ts_cdcpub tablespace has already been created. For example:

CREATE USER cdcpub IDENTIFIED BY cdcpub DEFAULT TABLESPACE ts_cdcpub
QUOTA UNLIMITED ON SYSTEM 
QUOTA UNLIMITED ON SYSAUX;
GRANT CREATE SESSION TO cdcpub;
GRANT CREATE TABLE TO cdcpub;
GRANT CREATE TABLESPACE TO cdcpub;
GRANT UNLIMITED TABLESPACE TO cdcpub;
GRANT SELECT_CATALOG_ROLE TO cdcpub;
GRANT EXECUTE_CATALOG_ROLE TO cdcpub;
GRANT CREATE SEQUENCE TO cdcpub;
GRANT CONNECT, RESOURCE, DBA TO cdcpub;

EXECUTE DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(GRANTEE => 'cdcpub');

Note that for HotLog Change Data Capture, the source database and the staging database are the same database.

Step 4 Source Database DBA: Prepare the source tables.

The source database DBA must prepare the source tables on the source database for asynchronous Change Data Capture by instantiating each source table so that the underlying Oracle Streams environment records the information it needs to capture each source table's changes. The source table structure and the column datatypes must be supported by Change Data Capture. See "Datatypes and Table Structures Supported for Asynchronous Change Data Capture" for more information.

BEGIN
DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION(TABLE_NAME => 'hr.job_history');
END;
/

Step 5 Staging Database Publisher: Create change sets.

The publisher uses the DBMS_CDC_PUBLISH.CREATE_CHANGE_SET procedure on the staging database to create change sets. Note that when Change Data Capture creates a change set, its associated Oracle Streams capture and apply processes are also created (but not started).

The following example creates a change set called CHICAGO_DAILY that captures changes starting today, and stops capturing change data 5 days from now.

BEGIN
       DBMS_CDC_PUBLISH.CREATE_CHANGE_SET(
       change_set_name => 'CHICAGO_DAILY',
       description => 'Change set for job history info',
       change_source_name => 'HOTLOG_SOURCE',
       stop_on_ddl => 'y',
       begin_date => sysdate,
       end_date => sysdate+5);
END;
/

The change set captures changes from the predefined HOTLOG_SOURCE change source.

Step 6 Staging Database Publisher: Create the change tables that will contain the changes to the source tables.

The publisher uses the DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE procedure on the staging database to create change tables.

The publisher creates one or more change tables for each source table to be published, specifies which columns should be included, and specifies the combination of before and after images of the change data to capture.

The following example creates a change table on the staging database that captures changes made to a source table on the source database. The example uses the sample table HR.JOB_HISTORY as the source table.

BEGIN
       DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE(
       owner => 'cdcpub',
       change_table_name => 'job_hist_ct',
       change_set_name => 'CHICAGO_DAILY',
       source_schema => 'HR',
       source_table => 'JOB_HISTORY',
       column_type_list => 'EMPLOYEE_ID NUMBER(6),START_DATE DATE,END_DATE DATE,
       JOB_ID VARCHAR(10), DEPARTMENT_ID NUMBER(4)',
       capture_values => 'both',
       rs_id => 'y',
       row_id => 'n',
       user_id => 'n',
       timestamp => 'n',
       object_id => 'n',
       source_colmap => 'n',
       target_colmap => 'y',
       options_string => 'TABLESPACE TS_CHICAGO_DAILY');
END;
/

This statement creates a change table named job_history_ct within change set CHICAGO_DAILY. The column_type_list parameter identifies the columns to be captured by the change table. The source_schema and source_table parameters identify the schema and source table that reside on the source database.

The capture_values setting in this statement indicates that for update operations, the change data will contain two separate rows for each row that changed: one row will contain the row values before the update occurred and the other row will contain the row values after the update occurred.

The options_string parameter in this statement specifies a tablespace for the change table. (This example assumes that the publisher previously created the TS_CHICAGO_DAILY tablespace.)

Step 7 Staging Database Publisher: Enable the change set.

Because asynchronous change sets are always disabled when they are created, the publisher must alter the change set to enable it. The Oracle Streams capture and apply processes are started when the change set is enabled.

BEGIN
   DBMS_CDC_PUBLISH.ALTER_CHANGE_SET(
      change_set_name => 'CHICAGO_DAILY',
      enable_capture => 'y');
END;
/

Step 8 Staging Database Publisher: Grant access to subscribers.

The publisher controls subscriber access to change data by granting and revoking the SELECT privilege on change tables for users and roles. The publisher grants access to specific change tables. Without this step, a subscriber cannot access change data. This example assumes that user subscriber1 already exists.

GRANT SELECT ON cdcpub.job_hist_ct TO subscriber1;

The Change Data Capture Asynchronous HotLog system is now ready for subscriber1 to create subscriptions.

Performing Asynchronous AutoLog Publishing

Change Data Capture uses Oracle Streams downstream capture to perform asynchronous AutoLog publishing. The Change Data Capture staging database is considered a downstream database in the Streams environment. See Oracle Streams Concepts and Administration for information on Streams downstream capture.

For asynchronous AutoLog Change Data Capture, the publisher creates new change sources, as well as the change sets and the change tables that will contain the changes that are made to individual source tables. For AutoLog Change Data Capture, the staging database is usually remote from the source database.

Steps must be performed on both the source database and the staging database to set up redo logging, Streams, and Change Data Capture for asynchronous AutoLog publishing. Because the source database and staging database are usually on separate systems, this example assumes that the source database DBA, the staging database DBA, and the publisher are different people.

Step 1 Source Database DBA: Prepare to copy redo log files from the source database.

The source database DBA and the staging database DBA must set up log transport services to copy redo log files from the source database to the staging database and to prepare the staging database to receive these redo log files, as follows:

1. The source database DBA configures Oracle Net so that the source database can communicate with the staging database. (See Oracle Net Services Administrator's Guide for information about Oracle Net).

2. The source database DBA sets the database initialization parameters on the source database as described in "Setting Initialization Parameters for Change Data Capture Publishing". In the following code example stagingdb is the network name of the staging database:

   compatible = 10.1.0
   java_pool_size = 50000000
   log_archive_dest_1="location=/oracle/dbs mandatory reopen=5"
   log_archive_dest_2 = "service=stagingdb arch optional noregister reopen=5
                         template = /usr/oracle/dbs/arch1_%s_%t_%r.dbf" 
   log_archive_dest_state_1 = enable
   log_archive_dest_state_2 = enable
   log_archive_format="arch1_%s_%t_%r.dbf"
   remote_login_passwordfile=shared
   
   

   See Oracle Data Guard Concepts and Administration for information on log transport services.

Step 2 Staging Database DBA: Set the database initialization parameters.

The staging database DBA sets the database initialization parameters on the staging database, as described in "Setting Initialization Parameters for Change Data Capture Publishing". In this example, one change set will be defined and the current value for the STREAMS_POOL_SIZE parameter is 50 MB or greater:

compatible = 10.1.0
global_names = true
java_pool_size = 50000000
job_queue_processes = 2
parallel_max_servers = <current_value> + 5
processes = <current_value> + 7
remote_login_passwordfile = shared
sessions = <current value> + 2
streams_pool_size = <current_value> + 21 MB
undo_retention = 3600

Step 3 Source Database DBA: Alter the source database.

The source database DBA performs the following three tasks. The second is required. The first and third are optional, but recommended. It is assumed that the database is currently running in ARCHIVELOG mode.

1. Place the database into FORCE LOGGING logging mode to protect against unlogged direct writes in the source database that cannot be captured by asynchronous Change Data Capture:

   ALTER DATABASE FORCE LOGGING;
   
   

2. Enable supplemental logging. Supplemental logging places additional column data into a redo log file whenever an update operation is performed.

   ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
   
   

3. Create an unconditional log group on all columns to be captured in the source table. Source table columns that are unchanged and are not in an unconditional log group, will be null in the change table, instead of reflecting their actual source table values. (This example captures rows in the HR.JOB_HISTORY table only. The source database DBA would repeat this step for each source table for which change tables will be created).

   ALTER TABLE HR.JOB_HISTORY
   ADD SUPPLEMENTAL LOG GROUP log_group_job_hist
      (EMPLOYEE_ID, START_DATE, END_DATE, JOB_ID, DEPARTMENT_ID) ALWAYS;
   
   

   If you intend to capture all the column values in a row whenever a column in that row is updated, you can use the following statement instead of listing each column one-by-one in the ALTER TABLE statement. However, do not use this form of the ALTER TABLE statement if all columns are not needed. Logging all columns incurs more overhead than logging selected columns.

   ALTER TABLE HR.JOB_HISTORY ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
   
   

   See Oracle Database Administrator's Guide for information about running a database in ARCHIVELOG mode. See "Asynchronous Change Data Capture and Supplemental Logging" for more information on enabling supplemental logging.

Step 4 Staging Database DBA: Create and grant privileges to the publisher.

The staging database DBA creates a user, (for example, cdcpub), to serve as the Change Data Capture publisher and grants the necessary privileges to the publisher so that he or she can perform the underlying Oracle Streams operations needed to create Change Data Capture change sources, change sets, and change tables on the staging database, as described in "Creating a User to Serve As a Publisher". For example:.

CREATE USER cdcpub IDENTIFIED BY cdcpub DEFAULT TABLESPACE ts_cdcpub
   QUOTA UNLIMITED ON SYSTEM
   QUOTA UNLIMITED ON SYSAUX;
GRANT CREATE SESSION TO cdcpub;
GRANT CREATE TABLE TO cdcpub;
GRANT CREATE TABLESPACE TO cdcpub;
GRANT UNLIMITED TABLESPACE TO cdcpub;
GRANT SELECT_CATALOG_ROLE TO cdcpub;
GRANT EXECUTE_CATALOG_ROLE TO cdcpub;
GRANT CONNECT, RESOURCE, DBA TO cdcpub;
GRANT CREATE SEQUENCE TO cdcpub;
EXECUTE DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(grantee => 'cdcpub');

Step 5 Source Database DBA: Build the LogMiner data dictionary.

The source database DBA builds a LogMiner data dictionary at the source database so that log transport services can transport this data dictionary to the staging database. This LogMiner data dictionary build provides the table definitions as they were just prior to beginning to capture change data. Change Data Capture automatically updates the data dictionary with any source table data definition language (DDL) operations that are made during the course of change data capture to ensure that the dictionary is always synchronized with the source database tables.

When building the LogMiner data dictionary, the source database DBA should get the SCN value of the data dictionary build. In Step 8, when the publisher creates a change source, he or she will need to provide this value as the first_scn parameter.

SET SERVEROUTPUT ON
VARIABLE f_scn NUMBER;
BEGIN
    :f_scn := 0;
    DBMS_CAPTURE_ADM.BUILD(:f_scn);
    DBMS_OUTPUT.PUT_LINE('The first_scn value is ' || :f_scn);
END;
/
The first_scn value is 207722

For asynchronous AutoLog publishing to work, it is critical that the source database DBA build the data dictionary before the source tables are prepared. The source database DBA must be careful to follow Step 5 and Step 6 in the order they are presented here.

See Oracle Streams Concepts and Administration for more information on the LogMiner data dictionary.

Step 6 Source Database DBA: Prepare the source tables.

The source database DBA must prepare the source tables on the source database for asynchronous Change Data Capture by instantiating each source table so that the underlying Oracle Streams environment records the information it needs to capture each source table's changes. The source table structure and the column datatypes must be supported by Change Data Capture. See "Datatypes and Table Structures Supported for Asynchronous Change Data Capture" for more information.

BEGIN
   DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION(
       TABLE_NAME => 'hr.job_history');
END;
/

Step 7 Source Database DBA: Get the global name of the source database.

In Step 8, the publisher will need to reference the global name of the source database. The source database DBA can query the GLOBAL_NAME column in the GLOBAL_NAME view on the source database to retrieve this information for the publisher:

SELECT GLOBAL_NAME FROM GLOBAL_NAME;
GLOBAL_NAME                                                                     
----------------------------------------------------------------------------
HQDB 

Step 8 Staging Database Publisher: Identify each change source database and create the change sources.

The publisher uses the DBMS_CDC_PUBLISH.CREATE_AUTOLOG_CHANGE_SOURCE procedure on the staging database to create change sources.

The process of managing the capture system begins with the creation of a change source. A change source describes the source database from which the data will be captured, and manages the relationship between the source database and the staging database. A change source always specifies the SCN of a data dictionary build from the source database as its first_scn parameter.

The publisher gets the SCN of the data dictionary build and the global database name from the source database DBA (as shown in Step 5 and Step 7, respectively). If the publisher cannot get the value to use for the first_scn parameter value from the source database DBA, then, with the appropriate privileges, he or she can query the V$ARCHIVED_LOG view on the source database to determine the value. This is described in the DBMS_CDC_PUBLISH chapter of the PL/SQL Packages and Types Reference.

On the staging database, the publisher creates the AutoLog change source and specifies the global name as the source_database parameter value and the SCN of the data dictionary build as the first_scn parameter value:

BEGIN
   DBMS_CDC_PUBLISH.CREATE_AUTOLOG_CHANGE_SOURCE(
       change_source_name => 'CHICAGO',
       description => 'test source',
       source_database => 'HQDB',
       first_scn => 207722);
END;
/

Step 9 Staging Database Publisher: Create change sets.

The publisher uses the DBMS_CDC_PUBLISH.CREATE_CHANGE_SET procedure on the staging database to create change sets. The publisher can optionally provide beginning and ending dates to indicate where to begin and end the data capture.

Note that when Change Data Capture creates a change set, its associated Oracle Streams capture and apply processes are also created (but not started).

The following example shows how to create a change set called CHICAGO_DAILY that captures changes starting today, and continues capturing change data indefinitely. (If, at some time in the future, the publisher decides that he or she wants to stop capturing change data for this change set, he or she should disable the change set and then drop it.)

BEGIN
   DBMS_CDC_PUBLISH.CREATE_CHANGE_SET(
       change_set_name => 'CHICAGO_DAILY',
       description => 'change set for job history info',
       change_source_name => 'CHICAGO',
       stop_on_ddl => 'y');
END;
/

Step 10 Staging Database Publisher: Create the change tables.

The publisher uses the DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE procedure on the staging database to create change tables.

The publisher creates one or more change tables for each source table to be published, specifies which columns should be included, and specifies the combination of before and after images of the change data to capture.

The publisher can set the options_string field of the DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE procedure to have more control over the physical properties and tablespace properties of the change tables. The options_string field can contain any option available (except partitioning) on the CREATE TABLE statement. In this example, it specifies a tablespace for the change set. (This example assumes that the publisher previously created the TS_CHICAGO_DAILY tablespace.)

The following example creates a change table on the staging database that captures changes made to a source table in the source database. The example uses the sample table HR.JOB_HISTORY.

BEGIN
   DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE(
       owner => 'cdcpub',
       change_table_name => 'JOB_HIST_CT',
       change_set_name => 'CHICAGO_DAILY',
       source_schema => 'HR',
       source_table => 'JOB_HISTORY',
       column_type_list => 'EMPLOYEE_ID NUMBER(6),START_DATE DATE,END_DATE DATE,
       JOB_ID VARCHAR2(10), DEPARTMENT_ID NUMBER(4)',
       capture_values => 'both',
       rs_id => 'y',
       row_id => 'n',
       user_id => 'n',
       timestamp => 'n',
       object_id => 'n',
       source_colmap => 'n',
       target_colmap => 'y',
       options_string => 'TABLESPACE TS_CHICAGO_DAILY');
END;
/

This example creates a change table named job_hist_ct within change set CHICAGO_DAILY. The column_type_list parameter identifies the columns captured by the change table. The source_schema and source_table parameters identify the schema and source table that reside in the source database, not the staging database.

The capture_values setting in the example indicates that for update operations, the change data will contain two separate rows for each row that changed: one row will contain the row values before the update occurred and the other row will contain the row values after the update occurred.

Step 11 Staging Database Publisher: Enable the change set.

Because asynchronous change sets are always disabled when they are created, the publisher must alter the change set to enable it. The Oracle Streams capture and apply processes are started when the change set is enabled.

BEGIN
   DBMS_CDC_PUBLISH.ALTER_CHANGE_SET(
       change_set_name => 'CHICAGO_DAILY',
       enable_capture => 'y');
END;
/

Step 12 Source Database DBA: Switch the redo log files at the source database.

To begin capturing data, a log file must be archived. The source database DBA can initiate the process by switching the current redo log file:

ALTER SYSTEM ARCHIVE LOGFILE;

Step 13 Staging Database Publisher: Grant access to subscribers.

The publisher controls subscriber access to change data by granting and revoking the SQL SELECT privilege on change tables for users and roles on the staging database. The publisher grants access to specific change tables. Without this step, a subscriber cannot access any change data. This example assumes that user subscriber1 already exists.

GRANT SELECT ON cdcpub.job_hist_ct TO subscriber1;

The Change Data Capture asynchronous AutoLog system is now ready for subscriber1 to create subscriptions.

Asynchronous Change Data Capture and Redo Log Files

The asynchronous mode of Change Data Capture uses redo log files, as follows:

• HotLog

  Asynchronous HotLog Change Data Capture reads online redo log files whenever possible and archived redo log files otherwise.

• AutoLog

  Asynchronous AutoLog Change Data Capture reads redo log files that have been copied from the source database to the staging database by log transport services.

  In ARCH mode, log transport services copies archived redo log files to the staging database after a log switch occurs on the source database. In LGWR mode, log transport services copies redo data to the staging database while it is being written to the online redo log file on the source database, and then makes it available to Change Data Capture when a log switch occurs on the source database.

For log files to be archived, the source databases for asynchronous Change Data Capture must run in ARCHIVELOG mode, as specified with the following SQL statement:

ALTER DATABASE ARCHIVELOG;

See Oracle Database Administrator's Guide for information about running a database in ARCHIVELOG mode.

A redo log file used by Change Data Capture must remain available on the staging database until Change Data Capture has captured it. However, it is not necessary that the redo log file remain available until the Change Data Capture subscriber is done with the change data.

To determine which redo log files are no longer needed by Change Data Capture for a given change set, the publisher alters the change set's Streams capture process, which causes Streams to perform some internal cleanup and populates the DBA_LOGMNR_PURGED_LOG view. The publisher follows these steps:

1. Uses the following query on the staging database to get the three SCN values needed to determine an appropriate new first_scn value for the change set, CHICAGO_DAILY:

   SELECT cap.CAPTURE_NAME, cap.FIRST_SCN, cap.APPLIED_SCN, 
           cap.SAFE_PURGE_SCN
   FROM DBA_CAPTURE cap, CHANGE_SETS cset
   WHERE cset.SET_NAME = 'CHICAGO_DAILY' AND
         cap.CAPTURE_NAME = cset.CAPTURE_NAME;
   
   CAPTURE_NAME                    FIRST_SCN APPLIED_SCN SAFE_PURGE_SCN
   ------------------------------ ---------- ----------- --------------
   CDC$C_CHICAGO_DAILY                778059      778293         778293
   
   

2. Determines a new first_scn value that is greater than the original first_scn value and less than or equal to the applied_scn and safe_purge_scn values returned by the query in step 1. In this example, this value is 778293, and the capture process name is CDC$C_CHICAGO_DAILY, therefore the publisher can alter the first_scn value for the capture process as follows:

   BEGIN
   DBMS_CAPTURE_ADM.ALTER_CAPTURE(
     capture_name => 'CDC$C_CHICAGO_DAILY',
     first_scn    => 778293);
   END;
   /
   
   

   If there if not an SCN value that meets these criteria, then the change set needs all of its redo log files.

3. Queries the DBA_LOGMNR_PURGED_LOG view to see any log files that are no longer needed by Change Data Capture:

   SELECT FILE_NAME
   FROM DBA_LOGMNR_PURGED_LOG;
   
   

   
   

   Note: Redo log files may be required on the staging database for purposes other than Change Data Capture. Before deleting a redo log file, the publisher should be sure that no other users need it.

   
   

See the information on setting the first SCN for an existing capture process and on capture process checkpoints in Oracle Streams Concepts and Administration for more information.

The first_scn value can be updated for all change sets in an AutoLog change source by using the DBMS_CDC_PUBLISH.ALTER_AUTOLOG_CHANGE_SOURCE first_scn parameter. Note that the new first_scn value must meet the criteria stated in step 2 of the preceding list for all change sets in the AutoLog change source.

Both the size of the redo log files and the frequency with which a log switch occurs can affect the generation of the archived log files at the source database. For Change Data Capture, the most important factor in deciding what size to make a redo log file is the tolerance for latency between when a change is made and when that change data is available to subscribers. However, because the Oracle Database software attempts a check point at each log switch, if the redo log file is too small, frequent log switches will lead to frequent checkpointing and negatively impact the performance of the source database.

See Oracle Data Guard Concepts and Administration for step-by-step instructions on monitoring log file archival information. Substitute the terms source and staging database for the Oracle Data Guard terms primary database and archiving destinations, respectively.

When using log transport services to supply redo log files to an AutoLog change source, gaps in the sequence of redo log files are automatically detected and resolved. If a situation arises where it is necessary to manually add a log file to an AutoLog change set, the publisher can use instructions on explicitly assigning log files to a downstream capture process described in the Oracle Streams Concepts and Administration. These instructions require the name of the capture process for the AutoLog change set. The publisher can obtain the name of the capture process for an AutoLog change set from the CHANGE_SETS data dictionary view.

Asynchronous Change Data Capture and Supplemental Logging

The asynchronous mode of Change Data Capture works best when supplemental logging is enabled on the source database. (Supplemental logging is not used by synchronous Change Data Capture.)

Oracle recommends that the source database DBA enable minimal supplemental logging at the database level:

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

In addition, Oracle recommends that the source database DBA:

• Supplementally log all source table columns that are part of a primary key or function to uniquely identify a row. This can be done using database-level or table-level identification key logging, or through a table-level unconditional log group.

• Create an unconditional log group for all source table columns that are captured by any asynchronous change table. This should be done before any change tables are created on a source table.

  ALTER TABLE SH.PROMOTIONS
  ADD SUPPLEMENTAL LOG GROUP log_group_cust
  (PROMO_NAME, PROMO_SUBCATEGORY, PROMO_CATEGORY) ALWAYS;
  
  

  If an unconditional log group is not created for all source table columns to be captured, then when an update DML operation occurs, some unchanged user column values in change tables will be null instead of reflecting the actual source table value.

  For example, suppose a source table contains two columns, X and Y, and that the source database DBA has defined an unconditional log group for that table that includes only column Y. Furthermore, assume that a user updates only column Y in that table row. When the subscriber views the change data for that row, the value of the unchanged column X will be null. However, because the actual column value for X is excluded from the redo log file and therefore cannot be included in the change table, the subscriber cannot assume that the actual source table value for column X is null. The subscriber must rely on the contents of the TARGET_COLMAP$ control column to determine whether the actual source table value for column X is null or it is unchanged.

See Oracle Database Utilities for more information on the various types of supplemental logging.

Datatypes and Table Structures Supported for Asynchronous Change Data Capture

Asynchronous Change Data Capture supports columns of all built-in Oracle datatypes except the following:

• BFILE

• BLOB

• CLOB

• LONG

• NCLOB

• ROWID

• UROWID

• object types (for example, XMLType)

Asynchronous Change Data Capture does not support the following table structures:

• Source tables that are temporary tables

• Source tables that are object tables

• Index-organized tables with columns of unsupported datatypes (including LOB columns) or with overflow segments

Managing Asynchronous Change Sets

This section provides information about tasks that the publisher can perform to manage asynchronous change sets. The following topics are covered:

• Creating Asynchronous Change Sets with Starting and Ending Dates

• Enabling and Disabling Asynchronous Change Sets

• Stopping Capture on DDL for Asynchronous Change Sets

• Recovering from Errors Returned on Asynchronous Change Sets

BEGIN
DBMS_CDC_PUBLISH.CREATE_CHANGE_SET(

change_set_name => 'JOBHIST_SET',
description => 'Job History Application Change Set',
change_source_name => 'HQ_SOURCE',
stop_on_ddl => 'Y',
begin_date => sysdate+2);

END;
/

BEGIN
DBMS_CDC_PUBLISH.ALTER_CHANGE_SET(

change_set_name => 'JOBHIST_SET',
enable_capture  => 'y');

END;
/

BEGIN
DBMS_CDC_PUBLISH.ALTER_CHANGE_SET(

change_set_name => 'JOBHIST_SET',
enable_capture  => 'n');

END;
/

BEGIN
DBMS_CDC_PUBLISH.ALTER_CHANGE_SET(

change_set_name  => 'JOBHIST_SET',
stop_on_ddl      => 'y');

END;
/

BEGIN
DBMS_CDC_PUBLISH.ALTER_CHANGE_SET(

change_set_name => 'JOBHIST_SET',
 stop_on_ddl     => 'n');

END;
/

Change Data Capture received DDL for change set JOB_HIST
Change Data Capture received DDL and stopping: truncate table job_history

Change Data Capture received DDL and ignoring: analyze table job_history compute statistics

ORA-31514: change set disabled due to capture error

Change Data Capture has encountered error number: 1688 for change set: CHICAGO_DAILY
Change Data Capture did not process LCR with scn 219337

SQL> SELECT ERROR_MESSAGE FROM DBA_APPLY_ERROR
       WHERE APPLY_NAME =
      (SELECT APPLY_NAME FROM CHANGE_SETS WHERE SET_NAME ='CHICAGO_DAILY');

ERROR_MESSAGE 
-------------------------------------------------------------------------------- 
ORA-01688: unable to extend table LOGADMIN.CT1 partition P1 by 32 in tablespace 
    TS_CHICAGO_DAILY 

BEGIN
DBMS_CDC_PUBLISH.ALTER_CHANGE_SET(
  change_set_name     => 'CHICAGO_DAILY',
  recover_after_error => 'y');
END;
/

ERROR at line 1:
ORA-31468: cannot process DDL change record
ORA-06512: at "SYS.DBMS_CDC_PUBLISH", line 79
ORA-06512: at line 2

Mon Jun  9 16:13:44 2003
Change Data Capture received DDL for change set JOB_HIST
Change Data Capture received DDL and stopping: truncate table job_history
Mon Jun  9 16:13:50 2003
Change Data Capture did not process LCR with scn 219777
Streams Apply Server P001 pid=19 OS id=11730 stopped
Streams Apply Reader P000 pid=17 OS id=11726 stopped
Streams Apply Server P000 pid=17 OS id=11726 stopped
Streams Apply Server P001 pid=19 OS id=11730 stopped
Streams AP01 with pid=15, OS id=11722 stopped

BEGIN
DBMS_CDC_PUBLISH.ALTER_CHANGE_SET(

change_set_name     => 'JOB_HIST',
  recover_after_error => 'y',
  remove_ddl          => 'y');

END;
/

Mon Jun  9 16:20:17 2003
Change Data Capture received DDL and ignoring: truncate table JOB_HISTORY
The scn for the truncate statement is 202998

Managing Change Tables

All change table management tasks are the responsibility of the publisher with one exception: purging change tables of unneeded data. This task requires action from both the publisher and the subscriber to work most effectively.

The following topics are discussed in this section:

• Creating Change Tables

• Understanding Change Table Control Columns

• Understanding TARGET_COLMAP$ and SOURCE_COLMAP$ Values

• Controlling Subscriber Access to Change Tables

• Purging Change Tables of Unneeded Data

• Dropping Change Tables

FE110000000000000000000000000000000000000000000000000000000000000000000000000000
00000000000000000000000000000000000000000000000000000000000000000000000000000000
00000000000000000000000000000000000000000000000000000000000000000000000000000000
000000000000

SELECT COLUMN_NAME, COLUMN_ID FROM ALL_TAB_COLUMNS WHERE OWNER='PUBLISHER_STEWART' AND TABLE_NAME='MY_CT';

COLUMN_NAME                    COLUMN_ID
------------------------------ ----------
OPERATION$                           1
CSCN$                                2
COMMIT_TIMESTAMP$                    3
XIDUSN$                              4
XIDSLT$                              5
XIDSEQ$                              6
RSID$                                7
TARGET_COLMAP$                       8
C_ID                                 9
C_KEY                                10
C_ZIP                                11

COLUMN_NAME                     COLUMN_ID
------------------------------ ----------
C_DATE                               12
C_1                                  13
C_3                                  14
C_5                                  15
C_7                                  16
C_9                                  17

ORA-31496 must use DBMS_CDC_PUBLISH.DROP_CHANGE_TABLE to drop change tables

ORA-31424 change table has active subscriptions

Considerations for Exporting and Importing Change Data Capture Objects

Starting in Oracle Databse 10g, Oracle Data Pump is the supported export and import utility for Change Data Capture. Change Data Capture change sources, change sets, change tables, and subscriptions are exported and imported by the Oracle Data Pump expdp and impdp commands with the following restrictions:

• Change Data Capture objects are exported and imported only as part of full database export and import operations (those in which the expdp and impdb commands specify the FULL=y parameter). Schema-level import and export operations include some underlying objects (for example, the table underlying a change table), but not the Change Data Capture metadata needed for change data capture to occur.

• AutoLog change sources, change sets, and change tables are not supported.

• You should export asynchronous change sets and change tables at a time when users are not making DDL and DML changes to the database being exported.

• When importing asynchronous change sets and change tables, you must also import the underlying Oracle Streams configuration; set the Oracle Data Pump import parameter STREAMS_CONFIGURATION to y explicitly (or implicitly by accepting the default), so that the necessary Streams objects are imported. If you perform an import operation and specify STREAMS_CONFIGURATION=n, then imported asynchronous change sets and change tables will not be able to continue capturing change data.

• Change Data Capture objects never overwrite existing objects when they are imported (similar to the effect of the import command TABLE_EXISTS_ACTION=skip parameter for tables). Change Data Capture generates warnings in the import log for these cases.

• Change Data Capture objects are validated at the end of an import operation to determine if all expected underlying objects are present in the correct form. Change Data Capture generates validation warnings in the import log if it detects validation problems. Imported Change Data Capture objects with validation warnings usually cannot continue capturing change data.

The following are examples of Data Pump export and import commands that support Change Data Capture objects:

> expdp system/manager DIRECTORY=dpump_dir FULL=y
> impdp system/manager DIRECTORY=dpump_dir FULL=y STREAMS_CONFIGURATION=y

After a Data Pump full database import operation completes for a database containing AutoLog Change Data Capture objects, the following steps must be performed to restore these objects:

1. The publisher must manually drop the change tables with the SQL DROP TABLE command. This is needed because the tables are imported without the accompanying Change Data Capture metadata.

2. The publisher must re-create the AutoLog change sources, change sets, and change tables using the appropriate DBMS_CDC_PUBLISH procedures.

3. Subscribers must re-create their subscriptions to the AutoLog change sets.

Change data may be lost in the interval between a Data Pump full database export operation involving AutoLog Change Data Capture objects and their re-creation after a Data Pump full database import operation in the preceding step. This can be minimized by preventing changes to the source tables during this interval, if possible.

See Oracle Database Utilities for information on Oracle Data Pump.

The following are publisher considerations for exporting and importing change tables:

• When change tables are imported, the job queue is checked for a Change Data Capture purge job. If no purge job is found, then one is submitted automatically (using the DBMS_CDC_PUBLISH.PURGE procedure). If a change table is imported, but no subscriptions are taken out before the purge job runs (24 hours later, by default), then all rows in the table will be purged.

  The publisher can use one of the following methods to prevent the purging of data from a change table:

  • Suspend the purge job using the DBMS_JOB package to either disable the job (using the BROKEN procedure) or execute the job sometime in the future when there are subscriptions (using the NEXT_DATE procedure).

    
    

    Note: If you disable the purge job by marking it as broken, you need to remember to reset it once subscriptions have been activated. This prevents the change table from growing indefinitely.

    
    

  • Create a temporary subscription to preserve the change table data until real subscriptions appear. Then, drop the temporary subscription.

• When importing data into a source table for which a change table already exists, the imported data is also recorded in any associated change tables.

  Assume that the publisher has a source table SALES that has an associated change table ct_sales. When the publisher imports data into SALES, that data is also recorded in ct_sales.

• When importing a change table having the optional control ROW_ID column, the ROW_ID columns stored in the change table have meaning only if the associated source table has not been imported. If a source table is re-created or imported, each row will have a new ROW_ID that is unrelated to the ROW_ID that was previously recorded in a change table.

The original level of export and import support available in Oracle9i is retained for backward compatibility. Synchronous change tables that reside in the SYNC_SET change set can be exported as part of a full database, schema, or individual table export operation and can be imported as needed. The following Change Data Capture objects are not included in the original export and import support: change sources, change sets, change tables that do not reside in the SYNC_SET change set, and subscriptions.

Impact on Subscriptions When the Publisher Makes Changes

The Change Data Capture environment is dynamic. The publisher can add and drop change tables at any time. The publisher can also add columns to and drop columns from existing change tables at any time. The following list describes how changes to the Change Data Capture environment affect subscriptions:

• Subscribers do not get explicit notification if the publisher adds a new change table or adds columns to an existing change table. A subscriber can check the ALL_PUBLISHED_COLUMNS view to see if new columns have been added, and whether or not the subscriber has access to them.

• Table 16-9 describes what happens when the publisher adds a column to a change table.

Synchronous Change Data Capture Restriction on Direct-Path INSERT

Synchronous Change Data Capture does not support the direct-path INSERT statement (and, by association, the MERGE statement and the multi_table_insert clause of the INSERT statement) in parallel DML mode.

When the publisher creates a change table in synchronous mode, Change Data Capture creates triggers on the source table. Because a direct-path INSERT statement disables all database triggers, any rows inserted into the source table using the SQL statement for direct-path INSERT in parallel DML mode will not be captured in the change table.

Similarly, Change Data Capture cannot capture the inserted rows from multitable insert and merge operations because these statements use a direct-path INSERT statement. The direct-path INSERT statement does not return an error message to indicate that the triggers used by Change Data Capture did not fire.

See Oracle Database SQL Reference for more information regarding the direct-path INSERT statement and triggers.