In Oracle : Replicate a table with Oracle Streams

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...

Oracle DBA

Real Application Cluster (RAC) Maintenance tasks Backup and Recovery Database Architecture Replication Oracle Performance Tuning Oracle DBA - Other articles Oracle scripts/ Selects for DBA Oracle Errors (ORA-nnnnn)

The last articles in the site (HOT)

The most visited articles in the site

In-Oracle.com  -> Oracle DBA -> Oracle Replication

-> Replicate a table with Oracle Streams

 

 Replicate a table with Oracle Streams

 

 

1. Oracle Streams Overview

2. Implementing Streams: set the correct init.ora parameters related to streams

3. Preparing the Destination Database

4. Preparing the Source Database

5. Export/ Import the table 

6. Manually Instantiate the table at the destination database (conditional)

7. Start the Capture Process

1. Oracle Streams Overview

Oracle Streams enables the sharing of data and events in a data stream, either within a database or from one database to another.

 

The replication using streams is implemented in the following way:

  • A background process is configured to capture changes made to tables, schemas or the entire database. This process captures changes from the redo log (using logminer) and formats each captured change into a logical change record (LCR). 

  •  The capture process enqueues LCR events into a queue

  •  This queue is scheduled to send events from one queue to another in a different database (or the same)

  •  A background process dequeues the events and applies them at the destination database.

NOTE: In my example I will replicate the SCOTT.EMP in PAUL schema, in the same database.

 

 

2. Implementing Streams: set the correct init.ora parameters related to streams

 

 

Parameter Value Comment 
COMPATIBLE 10.2.0  I use Oracle 10.2.0 version
GLOBAL_NAMES TRUE Changes captured by the Streams capture process automatically include the current global name of the source database. If the global name must be modified on the database, do it at a time when NO user changes are possible on the database so that the Streams configuration can be recreated.
JOB_QUEUE_PROCESSES  > 0  DBMS_JOB could be used.
AQ_TM_PROCESSES  > 1 Setting the parameter to 1 or more starts the specified number of queue monitor processes.
LOGMNR_MAX_PERSISTENT_SESSIONS

(Was deprecated in release 10.2)

 > 1 This parameter specifies the maximum number of persistent LOGMINER mining sessions.

(Was deprecated in release 10.2: In my example I will not use it.)

LOG_PARALLELISM

(Was deprecated in release 10.2)

 1 This parameter must be set to 1 at each database that captures events.

(Was deprecated in release 10.2: In my example I will not use it.)

PARALLEL_MAX_SERVERS

(Was deprecated in release 10.2)

 4 Each capture process and apply process may use multiple parallel execution servers. The apply process by default needs two parallel servers.

(Was deprecated in release 10.2: In my example I will not use it.)

SHARED_POOL_SIZE   Each capture process needs 10MB of shared pool space (Streams is limited to using a maximum of 10% of the shared pool). So shared_pool_size has to be set to at least 100MB. If you wish to run multiple capture processes, then this parameter needs to be set to an even higher value.
OPEN_LINKS  > 4 Specifies the maximum number of concurrent open connections to remote databases in one session. Ensure that it is set to 4 or higher. In my example is no present because the replication is done on the same database (in scott schema).  

NOTE: The databases involved in Streams must be running in ARCHIVELOG mode.

 

 

Here is the initdb10.ora initialization file I use for this case (this must be done on each side):

compatible='10.2.0.1.0'

global_names=TRUE

job_queue_processes=10

AQ_TM_PROCESSES = 1

 

db10.__java_pool_size=4194304

db10.__large_pool_size=4194304

db10.__shared_pool_size=120497472

db10.__streams_pool_size=0

audit_file_dest='F:/admin/db10/adump'

background_dump_dest='F:/admin/db10/bdump'

control_files='F:\oradata\db10\control01.ctl','F:\oradata\db10\control02.ctl','F:\oradata\db10\control03.ctl'

core_dump_dest='F:/admin/db10/cdump'

db_block_size=8192

db_domain=''

db_file_multiblock_read_count=16

db_name='db10'

db_recovery_file_dest='F:/flash_recovery_area'

db_recovery_file_dest_size=2147483648

open_cursors=300

pga_aggregate_target=16777216

processes=150

remote_login_passwordfile='EXCLUSIVE'

sga_target=250772160

undo_management='AUTO'

undo_tablespace='UNDOTBS1'

user_dump_dest='F:/admin/db10/udump'

 

3. Preparing the Destination Database (in my case this is done on the same database as the source database)

 

3.1  Create Streams Administrator (connected as SYS or SYSTEM)          

    create user STRMADMIN identified by STRMADMIN;

 

3.2  Grant the necessary privileges to the Streams Administrator (connected as SYS)                  

GRANT CONNECT, DBA, RESOURCE, AQ_ADMINISTRATOR_ROLE to STRMADMIN;

GRANT SELECT ANY DICTIONARY TO STRMADMIN;

GRANT EXECUTE ON DBMS_AQ TO STRMADMIN;

GRANT EXECUTE ON DBMS_AQADM TO STRMADMIN;

GRANT EXECUTE ON DBMS_FLASHBACK TO STRMADMIN;

GRANT EXECUTE ON DBMS_STREAMS_ADM TO STRMADMIN;

GRANT EXECUTE ON DBMS_CAPTURE_ADM TO STRMADMIN;

GRANT EXECUTE ON DBMS_APPLY_ADM TO STRMADMIN;

GRANT EXECUTE ON DBMS_RULE_ADM TO STRMADMIN;

GRANT EXECUTE ON DBMS_PROPAGATION_ADM TO STRMADMIN;

 

BEGIN

DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(

privilege => 'ENQUEUE_ANY',

grantee => 'STRMADMIN',

admin_option => FALSE);

 

DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(

privilege => 'DEQUEUE_ANY',

grantee => 'STRMADMIN',

admin_option => FALSE);

 

DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(

privilege => 'MANAGE_ANY',

grantee => 'STRMADMIN',

admin_option => TRUE);

 

DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(

privilege => DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT_OBJ,

grantee => 'STRMADMIN',

grant_option => TRUE);

 

DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(

privilege => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ,

grantee => 'STRMADMIN',

grant_option => TRUE);

 

DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(

privilege => DBMS_RULE_ADM.CREATE_RULE_OBJ,

grantee => 'STRMADMIN',

grant_option => TRUE);

 

DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(

privilege => DBMS_RULE_ADM.CREATE_ANY_RULE_SET,

grantee => 'STRMADMIN',

grant_option => TRUE);

 

DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(

privilege => DBMS_RULE_ADM.ALTER_ANY_RULE_SET,

grantee => 'STRMADMIN',

grant_option => TRUE);

 

DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(

privilege => DBMS_RULE_ADM.EXECUTE_ANY_RULE_SET,

grantee => 'STRMADMIN',

grant_option => TRUE);

 

DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(

privilege => DBMS_RULE_ADM.CREATE_ANY_RULE,

grantee => 'STRMADMIN',

grant_option => TRUE);

 

DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(

privilege => DBMS_RULE_ADM.ALTER_ANY_RULE,

grantee => 'STRMADMIN',

grant_option => TRUE);

 

DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(

privilege => DBMS_RULE_ADM.EXECUTE_ANY_RULE,

grantee => 'STRMADMIN',

grant_option => TRUE);

 

DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(

privilege => DBMS_RULE_ADM.EXECUTE_ANY_EVALUATION_CONTEXT,

grantee => 'STRMADMIN',

grant_option => TRUE);

END;

/

3.3  Create streams queue (connected as  STRMADMIN)

BEGIN

       DBMS_STREAMS_ADM.SET_UP_QUEUE(

                       queue_table => 'STREAMS_QUEUE_TABLE',

                       queue_name => 'STREAMS_QUEUE',

                       queue_user => 'STRMADMIN');

END;

3.4  Add apply rules for the table at the destination database (connected as  STRMADMIN)

BEGIN

     DBMS_STREAMS_ADM.ADD_TABLE_RULES(

table_name => 'PAUL.EMP',

streams_type => 'APPLY',

streams_name => 'STRMADMIN_APPLY',

queue_name => 'STRMADMIN.STREAMS_QUEUE',

include_dml => true,

include_ddl => true,

source_database => 'PRD1');

END;

3.5  Specify an 'APPLY USER' at the destination database (connected as  STRMADMIN)

 

This is the user who would apply all DML statements and DDL statements. The user specified in the APPLY_USER parameter must have the necessary privileges to perform DML and DDL changes on the apply objects. I choose the owner of the target table as 'APPLY USER'.

BEGIN

       DBMS_APPLY_ADM.ALTER_APPLY(

             apply_name => 'STRMADMIN_APPLY',

             apply_user => 'PAUL');

END;

3.6  Set DISABLE_ON_ERROR parameter to 'N' (optional, connected as  STRMADMIN) 

 

When set to 'N', the apply process will not abort for any error that it encounters, but the error details would be logged in DBA_APPLY_ERROR.

BEGIN

       DBMS_APPLY_ADM.SET_PARAMETER(

           apply_name => 'STRMADMIN_APPLY',

           parameter => 'DISABLE_ON_ERROR',

           value => 'N' );

END;

3.7  Start the Apply process (connected as  STRMADMIN) 

BEGIN

     DBMS_APPLY_ADM.START_APPLY(apply_name => 'STRMADMIN_APPLY');

END;

 

4.  Preparing the Source Database

 

4.1  Move LogMiner tables from SYSTEM tablespace (connected as SYS)

 

By default, all LogMiner tables are created in the SYSTEM tablespace. It is a good practice to create an alternate tablespace for the LogMiner tables.

 

CREATE TABLESPACE LOGMNRTS 

DATAFILE 'c:\oradata\logmnrts.dbf' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED;

 

   BEGIN

        DBMS_LOGMNR_D.SET_TABLESPACE ('LOGMNRTS');

   END;

4.2  Turn on supplemental logging for EMP table (connected as SYS)

 

ALTER TABLE scott.EMP ADD SUPPLEMENTAL LOG GROUP emp_pk(empno) ALWAYS;

 

4.3  Create Streams Administrator and Grant the necessary privileges

I use the same database 3.1, 3.2 already done on source database (In my case source database = target database)

 

4.4  Create a database link to the destination database

connected as STRMADMIN:

 

SQL> create database link "PRD2.REGRESS.RDBMS.DEV.US.ORACLE.COM"
         
connect to PAUL
         
identified by paul
         
using 'PRD2';

4.5 Create streams queue (connected as  STRMADMIN) 

BEGIN

     DBMS_STREAMS_ADM.SET_UP_QUEUE (

queue_name => 'STREAMS_QUEUE',

queue_table =>'STREAMS_QUEUE_TABLE',

queue_user => 'STRMADMIN');

END;

4.6  Add capture rules for the table at the source database (connected as  STRMADMIN) 

BEGIN

         DBMS_STREAMS_ADM.ADD_TABLE_RULES(

table_name => 'SCOTT.EMP',

streams_type => 'CAPTURE',

streams_name => 'STRMADMIN_CAPTURE',

queue_name => 'STRMADMIN.STREAMS_QUEUE',

include_dml => true,

include_ddl => true,

source_database => 'PRD1');

END;

4.7 Add propagation rules for the table at the source database (connected as  STRMADMIN) 

 

This step will also create a propagation job to the destination database.

 

BEGIN
   DBMS_STREAMS_ADM.add_table_propagation_rules
      
(table_name => 'SCOTT.EMP',
        streams_name => 'STRMADMIN_PROPAGATE',
        source_queue_name => 'STRMADMIN.STREAMS_QUEUE',
        destination_queue_name => 
'STRMADMIN.STREAMS_QUEUE@PRD2.REGRESS.RDBMS.DEV.US.ORACLE.COM',
        include_dml => TRUE,
        include_ddl => TRUE,
        source_database => 'PRD1'
       );

END;

 

 

5. Export/ Import the table 

 

The scott.emp table must be exported on the source host:

 

exp USERID=SYSTEM/system TABLES=SCOTT.EMP FILE=/u02/tables.dmp GRANTS=Y ROWS=Y LOG=/u02/exportTables.log OBJECT_CONSISTENT=Y INDEXES=Y STATISTICS = NONE

 

The scott.emp table must be imported in paul schema (the dump file must be copied on the target server first) :

 

imp USERID=SYSTEM/system tables=emp CONSTRAINTS=Y FILE=tables.dmp fromuser=scott touser=paul IGNORE=Y GRANTS=Y ROWS=Y COMMIT=Y LOG=importTables.log STREAMS_INSTANTIATION=Y

 

 

6. Manually Instantiate the table at the destination database (conditional)

 

This must be done if import utility is not used with STREAMS_INSTANTIATION=Y parameter.

 

Get the Instantiation SCN at the source database:


connect STRMADMIN/STRMADMIN@PRD1
set serveroutput on 

DECLARE
   iscn NUMBER; -- Variable to hold instantiation SCN value
BEGIN
   iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
   DBMS_OUTPUT.PUT_LINE ('Instantiation SCN is: ' || iscn);
END;


Instantiate the objects at the destination database with this SCN value.The SET_TABLE_INSTANTIATION_SCN procedure controls which LCRs for a table are to be applied by the apply process.If the commit SCN of an LCR from the source database is less than or equal to this instantiation SCN , then the apply process discards the LCR. Else, the apply process applies the LCR.

connect STRMADMIN/STRMADMIN@PRD2

BEGIN
    DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(
               source_object_name => 'SCOTT.EMP',
               source_database_name => 'DB1',
               instantiation_scn => &iscn);
END;


Enter value for iscn: 
<Provide the value of SCN that you got from the source database>

 

 

7. Start the Capture Process

(connected as  STRMADMIN on the source database) 

 

BEGIN
       DBMS_CAPTURE_ADM.START_CAPTURE(capture_name => 'STRMADMIN_CAPTURE');
END;

 

 

In-Oracle.com  -> Oracle DBA -> Oracle Replication

-> Replicate a table with Oracle Streams

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...

 

     Copyright (c) 2011-2015  www.in-oracle.com  |  Disclaimer: The views expressed on this web site are my own and do not reflect the views of Oracle Corporation. You may use the information from this site only at your risk.