Oracle Streams

Oracle Streams

Oracle Streams enables the propagation and management of data, transactions and events in a data stream either within a database, or from one database to another. The stream routes published information to subscribed destinations. The result is a new feature that provides greater functionality and flexibility than traditional solutions for capturing and managing events, and sharing the events with other databases and applications. As users’ needs change, they can simply implement a new capability of Oracle Streams, without sacrificing existing capabilities. Starting from release 9.2, Oracle has introduced a more flexible and efficient way of implementing replication using Streams. Oracle Streams enables the sharing of data and events in a data stream, either within a database or from one database to another. In a nutshell, replication using Oracle Streams is implemented in the following way: 1.-A background capture process is configured to capture changes made to tables,schemas, or the entire database. The capture process captures changes from the redo log and formats each captured change into a logical change record (LCR). The capture process uses logminer to mine the redo/archive logs to format LCRs. 2.-The capture process enqueues LCR events into a queue that is specified. 3.-This queue is scheduled to Propagate events from one queue to another in a different database. 4.-A background apply process dequeues the events and applies them at the destination database. The sample code outlines the steps to set up one-way streams replication at Schema level. Partho = Global Database name of the Source (capture) Site Tanu = Global Database name of the Target (apply) Site STRMADMIN = Streams Administrator with password strmadmin SCOTT = Source schema to be replicated – This schema is already installed on the source site The sample code replicates both DML and DDL. STEP 1.- Create user and the streams queue and the database links that will be used for propagation. There are two databases 1. Partho 2. Tanu. Creating a user “Strmadmin” in Partho. as well as in Tanu database. conn /as SYSDBA create user STRMADMIN identified by STRMADMIN; ALTER USER STRMADMIN DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP QUOTA UNLIMITED ON USERS; GRANT CONNECT, RESOURCE, AQ_ADMINISTRATOR_ROLE,DBA to STRMADMIN; execute DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(’STRMADMIN’); =============================================================== connect STRMADMIN/STRMADMIN@partho BEGIN

DBMS_STREAMS_ADM.SET_UP_QUEUE( queue_table => ‘STREAMS_QUEUE_TABLE’, queue_name => ‘STREAMS_QUEUE’, queue_user => ‘STRMADMIN’); END; / PL/SQL procedure successfully completed. conn /as sysdba create public database link tanu using ‘tanu’; conn strmadmin/strmadmin@partho create database link tanu connect to strmadmin identified by strmadmin; (note, check with tnsping whether the link is working perfect or note For db link creation please check my previous article) STEP 2.- Connect as the Streams Administrator in the target site Tanu and create the streams queue connect STRMADMIN/STRMADMIN@tanu BEGIN DBMS_STREAMS_ADM.SET_UP_QUEUE( queue_table => ‘STREAMS_QUEUE_TABLE’, queue_name => ‘STREAMS_QUEUE’, queue_user => ‘STRMADMIN’); END; / PL/SQL procedure successfully completed. STEP 3.- Add apply rules for the Schema at the destination database SQL> BEGIN 2 DBMS_STREAMS_ADM.ADD_SCHEMA_RULES( 3 schema_name => ‘SCOTT’, 4 streams_type => ‘APPLY ‘, 5 streams_name => ‘STREAM_APPLY’, 6 queue_name => ‘STRMADMIN.STREAMS_QUEUE’, 7 include_dml => true, 8 include_ddl => true, 9 source_database => ‘partho’); 10 END; 11 / PL/SQL procedure successfully completed. STEP 4.- Add capture rules for the schema SCOTT at the source database SQL> CONN STRMADMIN/STRMADMIN@partho Connected. SQL> BEGIN 2 DBMS_STREAMS_ADM.ADD_SCHEMA_RULES( 3 schema_name => ‘SCOTT’, 4 streams_type => ‘CAPTURE’,

5 streams_name => ‘STREAM_CAPTURE’, 6 queue_name => ‘STRMADMIN.STREAMS_QUEUE’, 7 include_dml => true, 8 include_ddl => true, 9 source_database => ‘partho’); 10 END; 11 / PL/SQL procedure successfully completed. STEP 5.- Add propagation rules for the schema SCOTT at the source database. This step will also create a propagation job to the destination database SQL> BEGIN 2 DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES( 3 schema_name => ‘SCOTT’, 4 streams_name => ‘STREAM_PROPAGATE’, 5 source_queue_name => ‘STRMADMIN.STREAMS_QUEUE’, 6 destination_queue_name => ‘STRMADMIN.STREAMS_QUEUE@tanu’, 7 include_dml => true, 8 include_ddl => true, 9 source_database => ‘partho’); 10 END; 11 / PL/SQL procedure successfully completed. STEP 6.- Export, import and instantiation of tables from Source to Destination Database; if the objects are not present in the destination database, perform an export of the objects from the source database and import them into the destination database /* If the objects are already present in the destination database, there are two ways of instantiating the objects at the destination site. 1. By means of Metadata-only export/import : Specify ROWS=N during Export Specify IGNORE=Y during Import along with above import parameters. 2. By Manaually instantiating the objects Get the Instantiation SCN at the source database: SQL> connect STRMADMIN/STRMADMIN@partho Connected. SQL> set serveroutput on SQL> DECLARE 2 iscn NUMBER; — Variable to hold instantiation SCN value 3 BEGIN 4 iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER(); 5 DBMS_OUTPUT.PUT_LINE (’Instantiation SCN is: ‘ || iscn); 6 END; 7 / Instantiation SCN is: 1805035 PL/SQL procedure successfully completed.

SQL> connect STRMADMIN/STRMADMIN@tanu Connected. SQL> BEGIN 2 DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN( 3 SOURCE_SCHEMA_NAME => ‘SCOTT’, 4 SOURCE_DATABASE_NAME => ‘partho’, 5 RECURSIVE => TRUE, 6 INSTANTIATION_SCN => &iscn ); 7 END; 8 9 10 11 / Enter value for iscn: 1805035 old 6: INSTANTIATION_SCN => &iscn ); new 6: INSTANTIATION_SCN => 1805035 ); PL/SQL procedure successfully completed. STEP 7.- Specify an ‘APPLY USER’ at the destination database. This is the user who would apply all 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. SQL> conn strmadmin/strmadmin@tanu Connected. SQL> BEGIN 2 DBMS_APPLY_ADM.ALTER_APPLY( 3 apply_name => ‘STREAM_APPLY’, 4 apply_user => ‘SCOTT’); 5 END; 6 / PL/SQL procedure successfully completed. STEP 8.- Set stop_on_error to false so apply does not abort for every error; then, start the Apply process on the destination * SQL> SQL> conn strmadmin/strmadmin@tanu Connected. SQL> BEGIN 2 DBMS_APPLY_ADM.SET_PARAMETER( 3 apply_name => ‘STREAM_APPLY’, 4 parameter => ‘disable_on_error’, 5 value => ‘n’); 6 END; 7 / PL/SQL procedure successfully completed. SQL> DECLARE 2 v_started number;

3 BEGIN 4 SELECT decode(status, ‘ENABLED’, 1, 0) INTO v_started 5 FROM DBA_APPLY WHERE APPLY_NAME = ‘STREAM_APPLY’; 6 if (v_started = 0) then 7 DBMS_APPLY_ADM.START_APPLY(apply_name => ‘STREAM_APPLY’); 8 end if; 9 END; 10 / PL/SQL procedure successfully completed. STEP 9.- Set up capture to retain 7 days worth of logminer checkpoint information, then start the Capture process on the source SQL> conn strmadmin/strmadmin@partho Connected. SQL> BEGIN 2 DBMS_CAPTURE_ADM.ALTER_CAPTURE( 3 capture_name => ‘STREAM_CAPTURE’, 4 checkpoint_retention_time => 7); 5 END; 6 / PL/SQL procedure successfully completed. SQL> SQL> begin 2 DBMS_CAPTURE_ADM.START_CAPTURE(capture_name => ‘STREAM_CAPTURE’); 3 end; 4 / PL/SQL procedure successfully completed. STEP 10.. Create some table in First database called “Partho”. and insert some rows into it. commit the changes. Now connect to the other database Called “Tanu” you will see the table with rows. That’s it………………………………… Note :In 10g there is a procedure that enables you to remove the entire Streams configuration locally. It is part of the dbms_streams_adm package and is run simply as-is: SQL> connect / as sysdba connected. SQL> execute DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION(); You can run this multiple times so if the procedure fails to complete, there is no harm in running it again. Note that the procedure will not remove the STRMADMIN user and will need to be run separately, once at each database where a Streams

environment resides. Use the following for dropping the user DROP USER <strm_user> CASCADE; This example is tested in oracle 10.2. it works perfect, sga is set in both the database of about 1024 MB, Both the database is in archivelog mode, aq_tm_processes are set with value of 1 in both the databases. This article has been written with help of Metalink and Oracle docs. Thanks Partho