Thinking Out Loud

November 20, 2010

My Change Data Capture Adventure Part1

Filed under: 11g,CDC,oracle — mdinh @ 4:38 pm

This is my adventure in automating the configuration for Change Data Capture (CDC) using Asynchronous HotLog.

Please read the reference materials since not all steps are listed here.

For example: setting the database initialization parameter and FORCE LOGGING

Simply, CDC have the following components

  1. Source
  2. Publisher to publish the changed data
  3. Subscriber to subscribe to the changed data

In my example:

  1. Source = SCOTT
  2. Publisher = OPS$ORACLE
  3. Subscriber = SCOTT_SUB

As part of the setup, I have created 2 tables – CLIENTS (Source Schema) and XCDC (Source Table)

OPS$ORACLE@dbtest> select * from CLIENTS;
PK_CLIENT_ID
------------------------------
JOE
JOHN
SCOTT
OPS$ORACLE@dbtest> select * from XCDC;
TABLE_NAME
 ------------------------------
 BADTABLE
 DEPT
 EMP
OPS$ORACLE@dbtest>

NOTE:  BADTABLE is not a valid table.

ANOTHER NOTE:  This is a Multi-Tenant Data Architecture; hence, EMP & DEPT tables will exist for ALL clients.

******* GRANT PRIVILEGES TO THE PUBLISHER: *******

SYS@dbtest> -- Execute as SYS
SYS@dbtest> /*
SYS@dbtest> CREATE TABLESPACE cdc_data DATAFILE
SYS@dbtest>   '/oracle/oradata/dbtest/cdc_data01.dbf' SIZE 524352K AUTOEXTEND ON NEXT 512M MAXSIZE 16777280K
SYS@dbtest> EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1m
SYS@dbtest> SEGMENT SPACE MANAGEMENT AUTO;
SYS@dbtest> */
SYS@dbtest> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Database altered.
SYS@dbtest> ALTER USER ops$oracle QUOTA UNLIMITED ON cdc_data;
User altered.
SYS@dbtest> ALTER USER ops$oracle QUOTA UNLIMITED ON sysaux;
User altered.
SYS@dbtest> ALTER USER ops$oracle DEFAULT TABLESPACE cdc_data;
User altered.
SYS@dbtest> -- system privs
SYS@dbtest> GRANT create session TO ops$oracle;
Grant succeeded.
SYS@dbtest> GRANT create table TO ops$oracle;
Grant succeeded.
SYS@dbtest> GRANT create sequence TO ops$oracle;
Grant succeeded.
SYS@dbtest> GRANT create procedure TO ops$oracle;
Grant succeeded.
SYS@dbtest> GRANT create job TO ops$oracle;
Grant succeeded.
SYS@dbtest> -- role privs
SYS@dbtest> GRANT execute_catalog_role TO ops$oracle;
Grant succeeded.
SYS@dbtest> GRANT select_catalog_role TO ops$oracle;
Grant succeeded.
SYS@dbtest> GRANT dba TO ops$oracle;
Grant succeeded.
SYS@dbtest> GRANT execute ON dbms_cdc_publish TO ops$oracle;
Grant succeeded.
SYS@dbtest> -- streams specific priv
SYS@dbtest> execute dbms_streams_auth.grant_admin_privilege(grantee=>'OPS$ORACLE');
PL/SQL procedure successfully completed.
SYS@dbtest> --
SYS@dbtest> connect /
Connected.
OPS$ORACLE@dbtest> CREATE TABLE xcdc (table_name VARCHAR2 (30));
Table created.
OPS$ORACLE@dbtest> ALTER TABLE xcdc ADD CONSTRAINT PK_xcdc PRIMARY KEY(table_name) USING INDEX;
Table altered.
OPS$ORACLE@dbtest> exit

sqlp is just an alias

> alias sqlp
alias sqlp='sqlplus /'

******* ADD SUPPLEMENTAL LOG DATA FOR SOURCE TABLES: *******

> sqlp @add_sup.sql scott

SQL*Plus: Release 11.2.0.2.0 Production on Sat Nov 20 06:02:38 2010

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

OPS$ORACLE@dbtest> define vsource = &1
OPS$ORACLE@dbtest> DECLARE
 2     l_sql   VARCHAR2 (1000);
 3  BEGIN
 4     FOR x IN (
 5            SELECT t.owner, z.table_name
 6              FROM xcdc z, clients c, dba_tables t
 7             WHERE c.pk_client_id = UPPER('&&vsource')
 8               AND c.pk_client_id = t.owner
 9               AND z.table_name   = t.table_name
 10            MINUS
 11            SELECT g.owner, g.table_name
 12              FROM dba_log_groups g, clients c
 13             WHERE     g.log_group_type = 'ALL COLUMN LOGGING'
 14                   AND c.pk_client_id = owner
 15                   AND c.pk_client_id = UPPER('&&vsource')
 16     ) LOOP
 17        l_sql := 'alter table '||x.owner||'.'||x.table_name||' add supplemental log data (all) columns';
 18        EXECUTE IMMEDIATE l_sql;
 19        DBMS_OUTPUT.put_line (l_sql);
 20     END LOOP;
 21  EXCEPTION
 22     WHEN OTHERS
 23     THEN
 24        DBMS_OUTPUT.put_line ('******* ERROR: ' || l_sql);
 25        RAISE;
 26  END;
 27  /
alter table SCOTT.DEPT add supplemental log data (all) columns
alter table SCOTT.EMP add supplemental log data (all) columns

PL/SQL procedure successfully completed.

OPS$ORACLE@dbtest> select owner, table_name from DBA_LOG_GROUPS where owner=UPPER('&vsource');

OWNER                          TABLE_NAME
------------------------------ ------------------------------
SCOTT                          DEPT
SCOTT                          EMP

OPS$ORACLE@dbtest> exit
 

NOTE that BADTABLE was ignored because it is not in DBA_TABLES.

******* PREPARE THE SOURCE TABLES: *******

> sqlp @prepare_tab.sql scott

SQL*Plus: Release 11.2.0.2.0 Production on Sat Nov 20 06:16:27 2010

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

OPS$ORACLE@dbtest> define vsource = &1
OPS$ORACLE@dbtest> DECLARE
 2     l_sql     VARCHAR2 (1000);
 3     l_table   VARCHAR2 (100);
 4  BEGIN
 5     FOR x IN (
 6            SELECT t.owner, z.table_name
 7              FROM xcdc z, clients c, dba_tables t
 8             WHERE c.pk_client_id = UPPER('&&vsource')
 9               AND c.pk_client_id = t.owner
 10               AND z.table_name   = t.table_name
 11            MINUS
 12            SELECT p.table_owner, p.table_name
 13              FROM dba_capture_prepared_tables p
 14              WHERE p.table_owner=UPPER('&&vsource')
 15     ) LOOP
 16        l_table := x.owner||'.'||x.table_name;
 17        DBMS_CAPTURE_ADM.prepare_table_instantiation (l_table);
 18     END LOOP;
 19  EXCEPTION
 20     WHEN OTHERS
 21     THEN
 22        DBMS_OUTPUT.put_line ('******* ERROR: ' || l_sql);
 23        RAISE;
 24  END;
 25  /

PL/SQL procedure successfully completed.

OPS$ORACLE@dbtest> select table_owner, table_name from DBA_CAPTURE_PREPARED_TABLES where table_owner=UPPER('&&vsource');

TABLE_OWNER                    TABLE_NAME
------------------------------ ------------------------------
SCOTT                          EMP
SCOTT                          DEPT

OPS$ORACLE@dbtest> exit
 

******* CREATE CHANGE SETS: *******

> sqlp @changeset.sql

SQL*Plus: Release 11.2.0.2.0 Production on Sat Nov 20 06:32:17 2010

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

OPS$ORACLE@dbtest> DECLARE
 2  l_cs VARCHAR2(100);
 3  l_de VARCHAR2(100);
 4  BEGIN
 5    FOR x IN (SELECT pk_client_id FROM clients)
 6    LOOP
 7       l_cs := x.pk_client_id||'_CS';
 8       l_de := x.pk_client_id||'_CHANGE_SET';
 9       dbms_cdc_publish.create_change_set(change_set_name=>l_cs,description=>l_de,change_source_name=>'HOTLOG_SOURCE',stop_on_ddl=>'Y') ;
 10    END LOOP;
 11  END;
 12  /

PL/SQL procedure successfully completed.

OPS$ORACLE@dbtest> set echo off
==============================
SET_NAME                      : SCOTT_CS
CHANGE_SOURCE_NAME            : HOTLOG_SOURCE
BEGIN_DATE                    :
END_DATE                      :
BEGIN_SCN                     :
END_SCN                       :
FRESHNESS_DATE                :
FRESHNESS_SCN                 :
ADVANCE_ENABLED               : N
IGNORE_DDL                    :
CREATED                       : 20-NOV-2010 06:32:41
ROLLBACK_SEGMENT_NAME         :
ADVANCING                     : N
PURGING                       : N
LOWEST_SCN                    : 0
TABLESPACE                    :
CAPTURE_ENABLED               : N
STOP_ON_DDL                   : Y
CAPTURE_ERROR                 : N
CAPTURE_NAME                  : CDC$C_SCOTT_CS
QUEUE_NAME                    : CDC$Q_SCOTT_CS
QUEUE_TABLE_NAME              : CDC$T_SCOTT_CS
APPLY_NAME                    : CDC$A_SCOTT_CS
SET_DESCRIPTION               : SCOTT_CHANGE_SET
PUBLISHER                     : OPS$ORACLE
LOWEST_TIMESTAMP              :
MAP_NAME                      : CDC$M_SCOTT_CS
==============================

PL/SQL procedure successfully completed.
 

So far, this has been the easy part. The harder part is to create_change_table and provide column_type_list.

I will post as soon as I get a working model.

References:
>>Database Data Warehousing Guide: Change Data Capture
Comparison Between Oracle Streams and Change Data Capture [ID 727445.1]

Advertisements

Blog at WordPress.com.