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

March 28, 2003

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;
   /