Change data capture implementation in Oracle Data warehouses - Part 2 - Oracle Streams implementation

Friday Mar 28th 2003 by DatabaseJournal.com Staff
Share:

In part-1 of the series, we discussed the overall view of Oracle Streams and the process of information sharing between databases. Part-2 of this article, presents a scenario to demonstrate a simple and 'straight-forward' configuration for Oracle Streams.

In part-1 of the series, we discussed the overall view of Oracle Streams and the process of information sharing between databases. Part-2 of this article, presents a scenario to demonstrate a simple and "straight-forward" configuration for Oracle Streams.

Oracle Streams Configuration

In this example scenario, we use two databases (both Oracle 9.2.0) "local.world" and "remote.world". Any changes (DML) in the schema, DEMO@local.world, will be setup to be captured, propagated and applied to the corresponding tables in the schema DEMO at " remote.world."

This configuration does take into consideration any DDL changes that might take place in the DEMO@local.world schema or replicate any changes that occur in DEMO@remote.world schema. In other words, the capture, propagate and apply takes place in only one direction from DEMO@local.world to DEMO@remote.world and does not include any DDL changes.

However, it will give a brief idea as to how Oracle streams is configured automatically using the Default replication rules implemented using Oracle supplied PL/SQL package DBMS_STREAMS_ADM.

Requirements:

Oracle9i (9.2.0) Enterprise edition.

Preliminary steps

The preliminary steps in creating an Oracle Streams environment in brief are as follows:

  1. Verify the initialization parameters for the databases involved in the Streams setup.
  2. Start the database (where changes are captured) in the ARCHIVELOG MODE and specify supplemental logging at the "source" database using

    ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX) COLUMNS;

    Supplemental logging though is optional and depends on configuration. Refer to PART-1 of the series for information on use of Supplemental logging.

  3. Create a Streams administrator user at the source and destination database respectively, granting appropriate privileges.
  4. Establish a database link between the two databases from the source database (and also from the destination database if changes are to be propagated from the destination database to other subscriber databases.)
  5. Create a Streams queue in the Streams administrator schema of both, the source and the destination databases respectively (and any other databases involved in the Streams setup)
  6. Create the Capture, Propagate and Apply rules in the Streams administrator schemas of all the databases involved.
  7. Grant appropriate privileges to the Objects / schema for which the apply rules have been setup, to apply the DDL or DML changes.
  8. Set up the instantiation SCNs for the source database tables (where changes are captured) at the other databases tables (where changes are applied).

    For details on database object SCN instantiation in Streams environment, refer to http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96571/apply.htm#55587

  9. Start the processes. (Note: The Apply/ propagation processes should exist and be associated with a rule that handles the events, before creating or starting a Capture process for the said event, to avoid loosing events)




These steps are described in detail as follows:



  1. Verify the following initialization parameters for the databases that are members of the Streams environment.



    DB_DOMAIN

    <Some domain name>

    We have used "world". Though not a must, Oracle recommends using a domain name.

    GLOBAL_NAMES

    TRUE

    In such case, your DBLINK has to be of the name databasename.domain.

    For other initialization parameters refer to

    http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96571/man_prep.htm#46828

    The other parameters can be ignored for this simple configuration, as the defaults did not result in any issue.

    Make sure the Capture database (local.world) is in ARCHIVELOGMODE. And optionally you can also assign a separate tablespace for LOGMINER (recommended) using:

    CREATE TABLESPACE LOGMNRTS DATAFILE 'logmnrts_local.world.dbf' SIZE 25M REUSE 
    AUTOEXTEND ON MAXSIZE UNLIMITED;
    
    BEGIN
      DBMS_LOGMNR_D.SET_TABLESPACE('LOGMNRTS');
    END;
    /
    
  2. Login as SYS/<password>@database AS SYSDBA and Create a Streams Administrator in both, the local.world and the remote.world databases.
    CREATE USER  "strmadmin" IDENTIFIED BY "strmadmin"
    DEFAULT TABLESPACE users
    TEMPORARY TABLESPACE temp
    QUOTA UNLIMITED ON users;
    

    NOTE: Do not use SYS or SYSTEM user as Streams administrator.

  3. Grant the following basic privileges to the Streams administrators

    RESOURCE

    EXP_FULL_DATABASE

    CONNECT

    IMP_FULL_DATABASE

    SELECT ANY DICTIONARY

    AQ_ADMINISTRATOR_ROLE

    SELECT_CATALOG_ROLE

    Example: GRANT CONNECT, RESOURCE to strmadmin;

    Grant EXECUTE privileges on the following PL/SQL supplied packages

    SYS.DBMS_AQ

    SYS.DBMS_AQADM

    SYS.DBMS_STREAMS_ADM

    SYS.DBMS_CAPTURE_ADM

    SYS.DBMS_APPLY_ADM

    SYS.DBMS_RULE_ADM

    Example: GRANT EXECUTE ON SYS.DBMS_STREAMS_ADM TO strmadmin;

  4. Grant these additional privileges using DBMS_AQADM and DBMS_RULE_ADM supplied PL/SQL packages for each Streams administrator user logging in as the respective SYS user for the databases.

    The DBMS_AQADM package provides procedures to manage Advanced Queuing configuration and administration information.

    /* Grants the STRMADMIN user, privilege to ENQUEUE any message to any queues in the database */
    BEGIN 
     DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
       privilege     => 'ENQUEUE_ANY', 
       grantee       => 'STRMADMIN', 
       admin_option  => FALSE);
    
    /* Grants STRMADMIN privilege to DEQUEUE any message from any queues in the database */
     DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
       privilege     => 'DEQUEUE_ANY', 
       grantee       => 'STRMADMIN', 
       admin_option  => FALSE);
    
    /* Grants STRMADMIN privilege to run or execute DBMS_AQADM on any schemas in the database */
    DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
       privilege     => 'MANAGE_ANY', 
       grantee       => 'STRMADMIN', 
       admin_option  => TRUE);
    
    /* Grants STRMADMIN access to AQ object types. 
    However, this procedure is obsolete from Oracle8.1.5. 
    Run this code statement below only if you receive the ORA-24048 error */
    
    -- DBMS_AQADM.GRANT_TYPE_ACCESS(
    --   user_name     => 'STRMADMIN');
    
    END;
    /
    
    

    The DBMS_RULE_ADM package provides the administrative interface for creating and managing rules, rule sets, and rule evaluation contexts.

    BEGIN
    /* Creates a new evaluation context in STRMADMIN's schema */
     DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
        privilege     => DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT_OBJ, 
        grantee       => 'STRMADMIN', 
        grant_option  => TRUE);
    
    /* Creates a new rule set in STRMADMIN's schema */
     DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
        privilege     => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ, 
        grantee       => 'STRMADMIN', 
        grant_option  => TRUE);
    
    /* Creates a new rule in STRMADMIN's schema */
     DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE (
        privilege     => DBMS_RULE_ADM.CREATE_RULE_OBJ, 
        grantee       => 'STRMADMIN', 
        grant_option  => TRUE);
    
    /* Grants STRMADMIN privilege to create a new rule set in any schema.  */
     DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
        privilege     => DBMS_RULE_ADM.CREATE_ANY_RULE_SET,  
        grantee       => 'STRMADMIN', 
        grant_option  => TRUE);
    
    /* Grants STRMADMIN privilege to alter any rule set owned by any user.  */
     DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
        privilege     => DBMS_RULE_ADM.ALTER_ANY_RULE_SET,  
        grantee       => 'STRMADMIN', 
        grant_option  => TRUE);
    
    /* Grants STRMADMIN privilege to execute any rule set owned by any user.  */
     DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
        privilege     => DBMS_RULE_ADM.EXECUTE_ANY_RULE_SET,  
        grantee       => 'STRMADMIN', 
        grant_option  => TRUE);
    
    /* Grants STRMADMIN privilege to create a new rule in any schema.  */
     DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
        privilege     => DBMS_RULE_ADM.CREATE_ANY_RULE,  
        grantee       => 'STRMADMIN', 
        grant_option  => TRUE);
    
    /* Grants STRMADMIN privilege to alter any rule  owned by any user.  */
     DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
        privilege     => DBMS_RULE_ADM.ALTER_ANY_RULE,  
        grantee       => 'STRMADMIN', 
        grant_option  => TRUE);
    
    /* Grants STRMADMIN privilege to execute any rule owned by any user.  */
     DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
        privilege     => DBMS_RULE_ADM.EXECUTE_ANY_RULE,  
        grantee       => 'STRMADMIN', 
        grant_option  => TRUE);
    
    /* Grants STRMADMIN privilege to execute any evaluation context owned by any user.  */
    DBMS_RULE_ADM.GRANT_OBJECT_PRIVILEGE(
        privilege     => DBMS_RULE_ADM.EXECUTE_ON_EVALUATION_CONTEXT,  
        object_name   => 'SYS.STREAMS$_EVALUATION_CONTEXT',
        grantee       => 'STRMADMIN', 
        grant_option  => FALSE );
    END;
    /
    
    
  1. Create a Database Link in strmadmin@local.world to point to the strmadmin user in remote.world database.

    CREATE DATABASE LINK remote.world 
    CONNECT TO strmadmin 
    IDENTIFIED BY strmadmin 
    USING 'remote.world';
    

    NOTE: Alternatively you can login as SYS user and create a public database link.

  2. Login to strmadmin at local.world and remote.world respectively and create a Streams Queue as follows:

    /* Refer to PART-1 of the article series for DBMS_STREAMS_ADM description  */
    BEGIN
     DBMS_STREAMS_ADM.SET_UP_QUEUE(
       queue_user => 'STRMADMIN');
    END;
       /
    
  3. Create the Capture, propagate and Apply rules in the respective streams administrator schemas.

    Login to destination streams administrator schema and create the Apply process

    /* In this example we create DML apply rules on DEMO.DEPt and DEMO.EMP tables 
    respectively. We can also create DML /DDL rules for the entire DEMO schema using 
    DBMS_STREAMS_ADM.ADD_SCHEMA_RULES procedure */
    BEGIN
     DBMS_STREAMS_ADM.ADD_TABLE_RULES(
       table_name => '"DEMO"."DEPT"', 
       streams_type => 'APPLY', 
       streams_name => 'STRMADMIN_LOCAL', 
       queue_name => '"STRMADMIN"."STREAMS_QUEUE"', 
       include_dml => true, 
       include_ddl => false, 
       source_database => 'LOCAL.WORLD');
    
    /* Add Apply rules for DEMO.DEPT */
     DBMS_STREAMS_ADM.ADD_TABLE_RULES(
       table_name => '"DEMO"."EMP"', 
       streams_type => 'APPLY', 
       streams_name => 'STRMADMIN_LOCAL', 
       queue_name => '"STRMADMIN"."STREAMS_QUEUE"', 
       include_dml => true, 
       include_ddl => false, 
       source_database => 'LOCAL.WORLD');
    END;
    /
    
    

    Login to source streams administrator schema and create the Capture and Propagation process

    /* Add capture rules to DEMO.DEPT and DEMO.EMP */
    BEGIN
     DBMS_STREAMS_ADM.ADD_TABLE_RULES(
       table_name => '"DEMO"."DEPT"', 
       streams_type => 'CAPTURE', 
       streams_name => 'STRMADMIN_CAPTURE', 
       queue_name => '"STRMADMIN"."STREAMS_QUEUE"', 
       include_dml => true, 
       include_ddl => false, 
       source_database => 'LOCAL.WORLD');
    
     DBMS_STREAMS_ADM.ADD_TABLE_RULES(
       table_name => '"DEMO"."EMP"', 
       streams_type => 'CAPTURE', 
       streams_name => 'STRMADMIN_CAPTURE', 
       queue_name => '"STRMADMIN"."STREAMS_QUEUE"', 
       include_dml => true, 
       include_ddl => false, 
       source_database => 'LOCAL.WORLD');
    END;
    /
    /* Add propagation rules to DEMO.DEPT and DEMO.EMP */
    BEGIN
     DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
       table_name => '"DEMO"."DEPT"', 
       streams_name => 'STRMADMIN_PROPAGATE', 
       source_queue_name => '"STRMADMIN"."STREAMS_QUEUE"', 
       destination_queue_name => '"STRMADMIN"."STREAMS_QUEUE"@REMOTE.WORLD', 
       include_dml => true, 
       include_ddl => false, 
       source_database => 'LOCAL.WORLD');
    
     DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
       table_name => '"DEMO"."EMP"', 
       streams_name => 'STRMADMIN_PROPAGATE', 
       source_queue_name => '"STRMADMIN"."STREAMS_QUEUE"', 
       destination_queue_name => '"STRMADMIN"."STREAMS_QUEUE"@REMOTE.WORLD', 
       include_dml => true, 
       include_ddl => false, 
       source_database => 'LOCAL.WORLD');
    END;
       /
    
    
  4. Grant appropriate privileges to the Objects / schema for which the apply rules have been setup, to apply the DDL or DML changes.

    If DEMO.DEPT and DEMO.EMP already exist in DEMO@remote.world then grant SELECT, INSERT and UPDATE privileges to the Streams administrator. Otherwise, with respect to this example, export the tables from the source database as specified in step 9.

  5. Set up the instantiation SCNs for the tables.

    In addition to using supplied PL/SQL packages, you can also use Export/Import Oracle utility to set up Instantiation SCNs for database objects.

    NOTE: In Oracle streams the tables identified for capturing and applying changes should have primary keys defined on them.

    From the command line do the following:

    exp USERID="STRMADMIN"@local.world TABLES="DEMO"."DEPT", "DEMO"."EMP" FILE=tables.dmp 
    GRANTS=Y ROWS=Y LOG=exportTables.log  OBJECT_CONSISTENT=Y INDEXES=Y
    
    imp USERID="STRMADMIN"@remote.world FULL=Y CONSTRAINTS=Y FILE=tables.dmp 
    IGNORE=Y GRANTS=Y ROWS=Y COMMIT=Y LOG=importTables.log STREAMS_CONFIGURATION=Y 
    STREAMS_INSTANTIATION=Y
    
    
  6. Now to start the processes at the respective databases.

    /*  Start Apply process at the destination database.
      Login to STRMADMIN@REMOTE.WORLD*/
    DECLARE
     v_started number;
    BEGIN
      SELECT decode(status, 'ENABLED', 1, 0) INTO v_started
        FROM DBA_APPLY WHERE APPLY_NAME = 'STRMADMIN_LOCAL';
    
      if (v_started = 0) then
        DBMS_APPLY_ADM.START_APPLY(apply_name  => 'STRMADMIN_LOCAL');
      end if;
    END;
    /
    
    /*  Start Capture process at the destination database 
    Login to STRMADMIN@LOCAL.WORLD */
    DECLARE
     v_started number;
    BEGIN
      SELECT decode(status, 'ENABLED', 1, 0) INTO v_started
        FROM DBA_CAPTURE WHERE CAPTURE_NAME = 'STRMADMIN_CAPTURE';
    
      if (v_started = 0) then
        DBMS_CAPTURE_ADM.START_CAPTURE(capture_name  => 'STRMADMIN_CAPTURE');
      end if;
    END;
    /
    

Test your Streams configuration.

The simplest way to test whether the streams setup is successful is to use the Oracle Enterprise Manager Console (OEM). When you login OEM console (you have to configure the OMS) you will see the Streams process status as shown below in fig 1:

Figure 1: OEM console showing Streams setup status

The other option is to check the dictionary views.

At the destination database

SQL> select apply_name,queue_name,apply_user, status from dba_apply;
SQL> 
APPLY_NAME    QUEUE_NAME   APPLY_USER                          STATUS
------------------------------ ------------------------------ ---------------------
STRMADMIN_LOCAL  STREAMS_QUEUE  STRMADMIN                      ENABLED

At the source database

SQL> select capture_name,queue_name,status from dba_capture;
SQL> 
CAPTURE_NAME                   QUEUE_NAME                     STATUS
------------------------------ ------------------------------ --------
STRMADMIN_CAPTURE              STREAMS_QUEUE                  ENABLED


Once the above tests are successful, you are all set to see how Streams works.

Login to DEMO@local.world schema and update the DEPT or the EMP tables and commit the changes.

Login to DEMO@remote.world , you will see the changes applied to the destination tables. Depending on the resources available, the process may take some time. The status of the change capture and apply process can also be seen in the OEM console.

Other Streams Related Dictionary Views

ALL_CAPTURE

Details about each capture process that stores the captured changes in a queue

ALL_CAPTURE_PARAMETERS

Details about parameters for each capture process that stores the captured changes in a queue visible to the current user

ALL_CAPTURE_PREPARED_DATABASE

Is the local database prepared for instantiation?

ALL_CAPTURE_PREPARED_SCHEMAS

All schemas at the local database that are prepared for instantiation

ALL_CAPTURE_PREPARED_TABLES

All tables visible to the current user that are prepared for instantiation

DBA_CAPTURE

Details about the capture process

DBA_CAPTURE_PARAMETERS

All parameters for capture process

DBA_CAPTURE_PREPARED_DATABASE

Is the local database prepared for instantiation?

DBA_CAPTURE_PREPARED_SCHEMAS

All schemas at the local database that are prepared for instantiation

DBA_CAPTURE_PREPARED_TABLES

All tables prepared for instantiation

ALL_APPLY

Details about each apply process that dequeues from the queue visible to the current user

ALL_APPLY_CONFLICT_COLUMNS

Details about conflict resolution on tables visible to the current user

ALL_APPLY_DML_HANDLERS

Details about the dml handler on tables visible to the current user

ALL_APPLY_ERROR

Error transactions that were generated after dequeuing from the queue visible to the current user

ALL_APPLY_KEY_COLUMNS

Alternative key columns for a STREAMS table visible to the current user

ALL_APPLY_PARAMETERS

Details about parameters of each apply process that dequeues from the queue visible to the current user

ALL_APPLY_PROGRESS

Information about the progress made by the apply process that dequeues from the queue visible to the current user

DBA_APPLY

Details about the apply process

DBA_APPLY_CONFLICT_COLUMNS

Details about conflict resolution

DBA_APPLY_DML_HANDLERS

Details about the dml handler

DBA_APPLY_ERROR

Error transactions

DBA_APPLY_INSTANTIATED_OBJECTS

Details about objects instantiated

DBA_APPLY_KEY_COLUMNS

alternative key columns for a table for STREAMS

DBA_APPLY_PARAMETERS

All parameters for apply process

For information on other view, query the Dictionary views as

select * from dict
where table_name like '%APPLY%'
or    table_name like '%CAPTURE%'
or    table_name like '%PROPAGATION%'
or    table_name like '%STREAMS%'

Streams Set up using Oracle Enterprise Manager (OEM)

Oracle Streams environment can also be used to set up the Streams environment. However, you should use a patch which is available on the Oracle technology website. This patch should be saved under $ORACLE_HOME/classes in Windows environment. In addition to the scripts generated by OEM, you've got to use some additional steps that are already introduced in the steps above. The more complex configuration of Streams such as that between a non-oracle database and an Oracle database requires custom programming using Oracle supplied PLSQL packages.

Conclusion

In the third part of this article, we will cover the Oracle 9i Change Data Capture framework using the DBMS_CAPTURE_ADM PL/SQL package.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved