Create Change Data Publisher
Oracle Tips by Burleson
Oracle 10g Creating and granting privileges to the
In the next step, the source database DBA creates
a Change Data Capture publisher (for example, cdcproj) and grants
the necessary privileges to the publisher, so that he or she can
perform the required Streams operations needed to create Change Data
Capture change sources, change sets, and change tables in the
The source database DBA must grant the global
privilege ALL to the publisher on the tables for which the publisher
will be creating change tables. For this example, it is assumed that
a change table will be created for the pl.project_history table
CREATE USER cdcproj IDENTIFIED BY cdcproj
QUOTA UNLIMITED ON SYSTEM
UNLIMITED ON SYSAUX;
CREATE SESSION TO cdcproj;
CREATE TABLE TO cdcproj;
SELECT_CATALOG_ROLE TO cdcproj;
EXECUTE_CATALOG_ROLE TO cdcproj;
EXECUTE DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(GRANTEE => 'cdcproj');
procedure successfully completed.
ALL ON pl.project_history TO cdcproj;
Oracle 10g Preparing the source tables
Next, the source database DBA prepares the source
tables on the source database for asynchronous Change Data Capture
by instantiating each source table so that the underlying Streams
environment for Change Data Capture can capture their changes (in
our example only one table is instantiated, this step is repeated
for all tables where change data capture is to be used):
TABLE_NAME => 'pl.project_history');
Oracle 10g Creating the change sets
After instantiating the tables, the
publisher executes 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, the Streams capture and apply
processes are created, but not started.
The following example creates a change set
called project_daily that captures changes starting today, and stops
capturing change data 7 days from now.
3 set_name => 'PROJECT_DAILY',
4 change_source_name => 'HOTLOG_SOURCE',
5 begin_date => sysdate,
6 end_date => sysdate+7,
8 ignore_ddl_events => 'y',
While the above example works in the beta
refresh version of Oracle Database 10g, Oracle assures me that the
procedure will not use rollback segments with the introduction of
automated undo in 9i and 10g. Instead the format will resemble:
Name Type In/Out Default?
--------------------- ------------------- ------
DESCRIPTION VARCHAR2 IN
CHANGE_SOURCE_NAME VARCHAR2 IN
STOP_ON_DDL CHAR IN DEFAULT
SUPPLEMENTAL_PROCESSES NUMBER IN DEFAULT
BEGIN_DATE DATE IN DEFAULT
END_DATE DATE IN DEFAULT
Creating the change tables that will contain the
changes to the source tables
Once the change set is created, the publisher
uses the dbms_cdc_publish.create_change_table procedure in the
staging database to create the change tables.
The publisher can create one or more change
tables for each source table to be published, specify which columns
should be included, and specify the combination of before and after
images of the change data to capture.
Also, the publisher can set the options_string
field of the dbms_cdc_publish.create_change_table procedure to
exercise more control over the physical, partitioning, and
tablespace properties of the change tables. The options_string field
can contain any option available in the CREATE TABLE statement.
The next example creates a change table in the
staging database that captures changes made to a source table in the
source database. The example uses the table pl.project_history.
3 owner => 'cdcproj',
4 change_table_name => 'project_hist_ct',
5 change_set_name => 'PROJECT_DAILY',
6 source_schema => 'PL',
7 source_table => 'PROJECT_HISTORY',
8 column_type_list => 'EMPLOYEE_ID NUMBER(6),
START_DATE DATE, END_DATE DATE,
9 PROJ_ID VARCHAR(10), DEPARTMENT_ID
10 capture_values => 'both',
11 rs_id => 'y',
12 row_id => 'n',
13 user_id => 'n',
14 timestamp => 'n',
15 object_id => 'n',
16 source_colmap => 'n',
17 target_colmap => 'y',
18 options_string => NULL);
Oracle 10g Creating the change tables that will contain
the changes to the source tables
procedure successfully completed.
This statement creates a change table named
project_history_ct within change set project_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 on the source database.
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 before update row values
and the other row will contain the after update row values.
Oracle 10g Enabling the change set
Next, the publisher must enable the
change set, since asynchronous change sets are always disabled when
they are created. Enabling the change set will start the Streams
capture and apply processes automatically.
change_set_name => 'PROJECT_DAILY',
enable_capture => 'y');
PL/SQL procedure successfully completed.
Granting access to subscribers.
To control subscriber access to the
change data, the publisher grants or revokes the SELECT privilege on
change tables for specific users and roles. The publisher grants
access based on specific change tables. Without these grants, a
subscriber cannot access change data.
SELECT ON cdcpub.project_hist_ct TO subscriber1;
Once this last step is complete, the Change
Data Capture Asynchronous HotLog system is now accessible for
Asynchronous Logging Level Dependencies
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.
The source database DBA must enable minimal
supplemental logging at the database level for asynchronous Change
Data Capture to work:
DATABASE ADD SUPPLEMENTAL LOG DATA;
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
oracle 10g Enabling the change set
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
Oracle 10g Performing Asynchronous AutoLog Publishing
Oracle Database 10g Change Data Capture uses
Oracle Stream's downstream capture to perform asynchronous AutoLog
publishing. The staging database used in Change Data Capture is
considered a downstream database in the Streams environment.
asynchronous AutoLog Change Data Capture, the publisher has to
create 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. Usually, a remote staging database is used for
AutoLog Change Data Capture.
Several steps are
performed on both the source database and the staging database to
set up for change data capture. The items that must be set up for
asynchronous Autolog publishing are:
The source and staging
database may be on separate systems, so this example assumes that
the source database DBA, the staging database DBA, and the publisher
users are different people on different databases.
||Need an Oracle Health Check?
Does your boss blame you for an Oracle performance problem?
Need to prove that your database is properly optimized?
BC Oracle performance guru's can quickly verify every aspect of your
Oracle database and provide a complete certification that your database
is fully optimized.