Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Mar 28, 2003

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

By DatabaseJournal.com Staff





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


Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM