Oracle Streams Made Simple

Data Replication using Oracle Streams Made Simple

1

By Z. Haque Oracle Streams are used to replicate data from one database to another. You can replicate a single table or multiple tables or a schema or whole database. In this article I’m going to show you how you can replicate a single table from a source database (srcDB) to a destination database (desDB). It’s a bit of work, so I tried to keep it as simple as possible and as short as possible. Once you know how to use it you can add more stuff to it.

Basic Architecture Update emp set sal= 100 Where ename=’ILL Gates’;

Ename ILL Gates

srcDB

Emp

| Sal | 100

desDB Propagation

Queue ==== ====

Queue ===== =====

Emp

LCR Redo Log

Capture

Apply

Fig. 1 Oracle Streaming Process As illustrated in the figure, if the user makes changes (update, delete etc) in the source database, oracle enters the changes in a redo log file. The capture process captures any DDL and DML changes from the redo log file and creates Logical Change Records (LCRs). The LCRs are stored in the queue in srcDB. If the destination database is up and running, LCRs are propagated to destination database queue. The apply process read from the queue and apply the changes to the destination database.

Data Replication using Oracle Streams Made Simple

2

By Z. Haque

Observation: ™ In the case of table replication, in the beginning of the process, both source table and destination table must have similar data. Otherwise, if you update or delete from source database, stream process try to update or delete from destination database, so you get “No data found” error in the Apply process. ™ The stream process is only one way communication – from source database to destination database. You can delete, update destination database. Stream process will not know about your actions. ™ Data communication is asynchronous. If the destination database is down while you were performing DML/DDL operations in the source database, the data will queue up in the source database queue. Once the destination database is up and running, data will propagate from source database queue to the destination database queue and the apply process will apply the changes. In order to setup Oracle streams process, follow the following steps:

Step 1: Instance Setup Your source database must be running in archive mode. Check the current archive mode of your source database by following command: Conn sys as sysdba@srcDB SQL> SELECT LOG_MODE FROM V$DATABASE; LOG_MODE -----------ARCHIVELOG

If the source database is not in archive mode, start it in archive mode using following command: $sqlplus sys/passwd as sysdba; SQL> shutdown SQL> startup mount SQL> alter database archivelog; Database altered. SQL> alter database open; Database altered.

Following parameters should be set in the spfiles of both participating databases: CONN sys/password@srcDB AS SYSDBA ALTER SYSTEM SET JOB_QUEUE_PROCESSES=1; ALTER SYSTEM SET AQ_TM_PROCESSES=1; ALTER SYSTEM SET GLOBAL_NAMES=TRUE; SHUTDOWN IMMEDIATE; STARTUP;

Data Replication using Oracle Streams Made Simple By Z. Haque From v$parameter table in both SrcDB and DesDB:

Step 2: Create Stream Administrator Account You need to create a stream administrator account and grant all required privileges as shown below: CONN sys/password@DBA1 AS SYSDBA CREATE USER strmadmin IDENTIFIED BY strmadminpw DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;

Grant all required privileges: GRANT GRANT GRANT GRANT GRANT GRANT GRANT

CONNECT, RESOURCE, SELECT_CATALOG_ROLE TO strmadmin; EXECUTE ON DBMS_AQADM TO strmadmin; EXECUTE ON DBMS_CAPTURE_ADM TO strmadmin; EXECUTE ON DBMS_PROPAGATION_ADM TO strmadmin; EXECUTE ON DBMS_STREAMS_ADM TO strmadmin; EXECUTE ON DBMS_APPLY_ADM TO strmadmin; EXECUTE ON DBMS_FLASHBACK TO strmadmin;

BEGIN DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE( privilege => DBMS_RULE_ADM.CREATE_RULE_OBJ, grantee => 'strmadmin', grant_option => FALSE); END; /

Step 3: Create Links Create database links on both SrcDB and DesDB databases. Once the links are created, check them whether they are working. If the links do not work, do not proceed further until your links are working. Because if the links do not work, your data replication is not going to work. So make them are working. If you have trouble to make them working, check transnames.ora file and do the followings: Creating Links in both SrcDB and DesDB: Sys/pw as sysdba CREATE DATABASE LINK SrcDB.world CONNECT TO strmadmin IDENTIFIED BY eta_manager USING 'SrcDB';

Test the link:

3

Data Replication using Oracle Streams Made Simple By Z. Haque SELECT sysdate form [email protected]

If there is no error and displays the system date, then the link is working. If links does not work, check the followings: # tnsnames.ora Network Configuration File: # Generated by Oracle configuration tools. SrcDB = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = source.urcom.com)(PORT = 1522)) ) (CONNECT_DATA = (SID = AUDVL1) (SERVER = DEDICATED) (SERVICE_NAME = SrcDB.world) ) ) DesDB = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = desti.urcom.com)(PORT = 1523)) ) (CONNECT_DATA = (SID = AUTST1) (SERVER = DEDICATED) (SERVICE_NAME = DesDB.world) ) ) # sqlnet.ora # FUNCTION # Oracle Network Client startup parameter file example # ... #names.default_domain = world # #Syntax: domain-name #Default: NULL domain-name = world # #names.directory_path # #Syntax: #Default: TNSNAMES,ONAMES,HOSTNAME # names.directory_path = TNSNAMES #

Below is just an example on creating link taken from psoug.org site: Configure TNSNAMES.ORA ALPHA1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = alpha1.psoug.org)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = alpha1db) ) )

4

Data Replication using Oracle Streams Made Simple

5

By Z. Haque ALPHA2 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = alpha2.psoug.org)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = alpha2db) ) )

Create DB Link on Alpha 1

on Alpha 2

SELECT * FROM global_name; alpha1.psoug.org conn localadmin/localadmin

SELECT * FROM global_name; alpha2.psoug.org conn remadmin/remadmin

CREATE DATABASE LINK alpha2db.psoug.org CONNECT TO remadmin IDENTIFIED BY remadmin USING 'ALPHA2.PSOUG.ORG';

CREATE DATABASE LINK alpha1db.psoug.org CONNECT TO locadmin IDENTIFIED BY locadmin USING 'ALPHA1.PSOUG.ORG';

SELECT SYSDATE FROM dual@alpha2;

SELECT SYSDATE FROM dual@alpha1;

Step 4: Create Tables Create table in hr schema or in your schema in both databases and grant all to strmadmin user using following commands: Conn hr/pw@SrcDB Create table my_emp as select * from emp; GRANT ALL ON hr.my_emp TO strmadmin; Conn hr/pw@DesDB Create table my_emp as select * from emp; GRANT ALL ON hr.my_emp TO strmadmin;

Step 5: Setup Queue CONNECT strmadmin/strmadminpw@srcDB & descDB -- srcDB: BEGIN DBMS_STREAMS_ADM.SET_UP_QUEUE( queue_table => 'strmadmin.streams_queue_table', queue_name => 'strmadmin.streams_queue', storage_clause => 'TABLESPACE YOUR_TBLSP', -- change to your tblspace queue_user => 'STRMADMIN'); END; / -- desDB: BEGIN

Data Replication using Oracle Streams Made Simple

6

By Z. Haque DBMS_STREAMS_ADM.SET_UP_QUEUE( queue_table => 'strmadmin.streams_queue_table', queue_name => 'strmadmin.streams_queue', storage_clause => 'TABLESPACE YOUR_TBLSP', -- change to your tblspace queue_user => 'STRMADMIN'); END; /

Step 6: Supplemental Logging The apply process requires additional information for some actions so we must configure supplemental logging of primary key information for tables of interest: CONN sys/password@DBA1 AS SYSDBA ALTER TABLE hr.my_emp ADD SUPPLEMENTAL LOG GROUP my_emp_pk (emp_id) ALWAYS;

Step 7: Configure Propagation Process Configure the propagation process on srcDB: CONNECT strmadmin/strmadminpw@srcDB BEGIN DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES( table_name => 'hr.my_emp', streams_name => 'my_stream', source_queue_name => 'strmadmin.streams_queue', destination_queue_name => '[email protected]', include_dml => true, include_ddl => true, source_database => 'srcDB'); END; /

Step 8: Configure Capture Process Configure the capture process on srcDB: --CONNECT strmadmin/strmadminpw@DBA1 BEGIN DBMS_STREAMS_ADM.ADD_TABLE_RULES( table_name => 'hr.my_emp', streams_type => 'capture', streams_name => 'capture_simp', queue_name => 'strmadmin.streams_queue', include_dml => true, include_ddl => true); END; /

Step 9: Drop Supplemental Log

Data Replication using Oracle Streams Made Simple

7

By Z. Haque CONN sys/password@srcDB AS SYSDBA ALTER TABLE eta_data.zh_stream DROP SUPPLEMENTAL LOG GROUP zh_stream_pk;

Step 10: instantiation of SCN The instantiation of SCN can be set using the DBMS_APPLY_ADM package: CONNECT strmadmin/strmadminpw@srcDB DECLARE v_scn NUMBER; BEGIN v_scn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER(); DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@desDB( source_object_name => 'hr.my_emp', source_database_name => 'srcDB', instantiation_scn => v_scn); END; /

Step 11: Configure Apply Process Configure the apply process on the destination database (desDB): --CONNECT strmadmin/strmadminpw@desDB BEGIN DBMS_STREAMS_ADM.ADD_TABLE_RULES( table_name => 'hr.my_emp', streams_type => 'apply', streams_name => 'apply_simp', queue_name => 'strmadmin.streams_queue', include_dml => true, include_ddl => true, source_database => 'srcDB'); END; /

Step 12: Start Apply Process Start the apply process on destination database desDB) and prevent errors stopping the process: CONNECT strmadmin/strmadminpw@desDB BEGIN DBMS_APPLY_ADM.SET_PARAMETER( apply_name => 'apply_simp', parameter => 'disable_on_error', value => 'n'); END; /

Data Replication using Oracle Streams Made Simple

8

By Z. Haque

Step 13: Start Capture Process Start the capture process on the source database (srcDB): CONNECT strmadmin/strmadminpw@srcDB BEGIN DBMS_CAPTURE_ADM.START_CAPTURE( capture_name => 'capture_simp'); END; /

Step 14: Test It With the streams activated we can see that DML changes to the source table are visible in the destination table: Just login in srcDB in hr schema, issue some DDL, DML commands, then check the desDB. The changes made in srcDB will be reflected in desDB. Here are come examples: CONNECT hr/pwd@srcDB INSERT INTO dept (deptno, dname, loc) VALUES (111, 'New Dept', 'Sydney'); COMMIT; SELECT * FROM dept; DEPTNO ---------10 20 30 40 111

DNAME -------------ACCOUNTING RESEARCH SALES OPERATIONS New Dept

LOC ------------NEW YORK DALLAS CHICAGO BOSTON Sydney

5 rows selected. CONNECT scott/tiger@desDB SELECT * FROM dept; DEPTNO ---------10 20 30 40 111

DNAME -------------ACCOUNTING RESEARCH SALES OPERATIONS New Dept

LOC ------------NEW YORK DALLAS CHICAGO BOSTON Sydney

5 rows selected.

Issue few more commands and test thoroughly.

Data Replication using Oracle Streams Made Simple By Z. Haque

Step 15: Finally Clean Up Using Enterprise manager you can clean up or run the following scripts: srcDB (as strmadmin) desDB ( as sysdba): BEGIN FOR cur_rec IN (SELECT rule_owner, rule_name FROM dba_rules) LOOP DBMS_RULE_ADM.DROP_RULE( rule_name => cur_rec.rule_owner || '.' || cur_rec.rule_name, force => TRUE); END LOOP; END; /

All capture and apply processes can be identified, stopped and dropped using: BEGIN FOR cur_rec IN (SELECT capture_name FROM dba_capture) LOOP DBMS_CAPTURE_ADM.STOP_CAPTURE( capture_name => cur_rec.capture_name); DBMS_CAPTURE_ADM.DROP_CAPTURE( capture_name => cur_rec.capture_name); END LOOP; FOR cur_rec IN (SELECT apply_name FROM dba_apply) LOOP DBMS_APPLY_ADM.STOP_APPLY( apply_name => cur_rec.apply_name); DBMS_APPLY_ADM.DROP_APPLY( apply_name => cur_rec.apply_name); END LOOP; END; /

All streams information relating to a specific object can be purged using: BEGIN DBMS_STREAMS_ADM.PURGE_SOURCE_CATALOG( source_database => 'desDB', source_object_name => 'hr.my_emp’, source_object_type => 'TABLE'); END; /

All the above scripts seem to be working for me and supplied without warranty. Modify them according to your database parameters.

9