Sunday, December 21, 2008

Streams 10gR2 Implementation -Table Level

Oracle Streams 10gR2 Step by Step

Purpose:

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

The purpose of this article is to outline the steps for setting up one-way replication between two ORACLE databases using streams at table level.

Source database (S): ORCL.ORACLEDB1.KRISH.COM 192.162.2.138
Target database (T): MYDEV.ORACLEDB2.KRISH.COM 192.162.2.139
Source Schema: SCOTT
Target Schema: SCOTT
Replication Tables:
EMP,
DEPT,
SALGRADE,
BONUS


Streams Setup Steps:

Following sequence of steps are required in order to implement streams in online.

1. Set parameters Relevant to Streams (S & T)
2. Set up ARCHIVELOG mode (S & T)
3. Create separate Tablespace for stream admin user (S & T)
4. Setup streams admin user (S & T)
5. Create a database link on source (S)
6. Setup Streams queues on both Source(S) and Target (T)
7. Setup supplemental logging on Source(S)
8. Configure capture process (S)
9. Configure propagation process (S)
10.Create destination tables (Metadata-only export/import) on Target (T)
11.Set Instantiation of tables from Source(S)
12.Export, Import of tables(Data) from Source(S) to Target (T)
13.Grant object privileges to stream admin user (T)
14.Configure apply process on Target(T)
15.Start the apply process (T) and Capture process(S)

1 Set parameters Relevant to Streams (S & T)

1a) Initialization parameters

Set below Init.ora parameters on both Source (S) and Target (T) databases.

db_name =
db_domain = ORACLEDB1.KRISH.COM
global_names = TRUE
compatible = 10.2.0
job_queue_processes =4
timed_statistics =TRUE
statistics_level =TYPICAL
sga_target=>0 or streams_pool_size >=200m
open_links =4
logmnr_max_persistent_sessions =1(>= no. of capture processes)
parallel_max_servers =2 or more(current value + (3 * capture processes) + (3
* apply processes)

1b) Streams Process Parameters

Capture: (DBMS_CAPTURE package)
Set capture parameters on source database

1. Set retention time for capture checkpoints as needed
Alter_capture(‘captureName’, checkpoint_retention_time=>7)

2. Reduce the capture checkpoint frequency parameter
Set_parameter(‘captureName’,’_checkpoint_frequency’,’1000’)
Note: Recommended “_checkpoint_frequency=1000” If redo logs are over 300Mb in size.

Apply: (DBMS_APPLY package)
Set Apply parameters on Target database

1. Set_parameter(‘applyName’,’parallelism’,’4’)
2. Set_parameter(‘applyName’,’disable_on_error’,’N’)

2 Set up ARCHIVELOG mode (S & T)

Set below parameters in init.ora and turn on “ARCHIVELOG” mode

log_archive_dest=‘/u002/oradata/ORCL/arch’
log_archive_format=‘ARCH%t_%s_%r.LOG’

Issue below command in mount state

SQL> alter database archivelog;
SQL> alter database open;

3 Create separate Tablespace for stream admin user (S & T)

Create default tablespace (streams01) for streams administrator account which stores the queue table. Minimum tablespace size should be 200m.

Source(ORCL.ORACLEDB1.KRISH.COM):

connect system/<@ORCL.ORACLEDB1.KRISH.COM

Create tablespace streams01 datafile ‘/a002/oradata/ORCL/STREAMS0101.dbf’ size 200m autoextend on extent management local uniform size 1m;

Target(MYDEV.ORACLEDB2.KRISH.COM):

connect system/pwd@MYDEV.ORACLEDB2.KRISH.COM

Create tablespace streams01 datafile ‘/a002/oradata/MYDEV/STREAMS0101.dbf’ size 200m autoextend on extent management local uniform size 1m;

4 Setup streams admin user (S & T)

Source(ORCL.ORACLEDB1.KRISH.COM):

Script creates strmadmin user at source and grants set of privileges.
This script needs to be run as "sys"

connect /as sysdba

CREATE USER strmadmin
IDENTIFIED BY strmadmin
DEFAULT TABLESPACE STREAMS01
TEMPORARY TABLESPACE TEMP01
QUOTA UNLIMITED ON STREAMS01;

--Now grant the CONNECT, RESOURCE, and DBA roles to the Streams administrator:

GRANT CONNECT, RESOURCE, DBA TO strmadmin;
GRANT SELECT_CATALOG_ROLE TO strmadmin;
GRANT SELECT ANY DICTIONARY TO strmadmin;

BEGIN
DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
grantee => 'strmadmin',
grant_privileges => true);
END;
/

Target(MYDEV.ORACLEDB2.KRISH.COM):

Script creates strmadmin user at Target and grants set of privileges.
This script needs to be run as "sys"

connect /as sysdba

CREATE USER strmadmin
IDENTIFIED BY strmadmin
DEFAULT TABLESPACE STREAMS01
TEMPORARY TABLESPACE TEMP01
QUOTA UNLIMITED ON STREAMS01;

--Now grant the CONNECT, RESOURCE, and DBA roles to the Streams administrator:

GRANT CONNECT, RESOURCE, DBA TO strmadmin;
GRANT SELECT_CATALOG_ROLE TO strmadmin;
GRANT SELECT ANY DICTIONARY TO strmadmin;

BEGIN
DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
grantee => 'strmadmin',
grant_privileges => true);
END;
/

5 Create a database link on source (S)

Create a private database link on source site. Do not change the password for strmadmin user after creating the database link, if password changed then the propagation process will fail to propagate the changes to the target database.

Source(ORCL.ORACLEDB1.KRISH.COM):

connect
strmadmin/strmadmin@ORCL.ORACLEDB1.KRISH.COM

drop database link MYDEV.ORACLEDB2.KRISH.COM;

create database link MYDEV.ORACLEDB2.KRISH.COM
CONNECT TO strmadmin
IDENTIFIED BY strmadmin USING 'MYDEV.ORACLEDB2.KRISH.COM'
/


6 Setup Streams queues on both Source(S) and Target(T)

Source and Target queue name similar as STREAMS_QUEUE__

Source(ORCL.ORACLEDB1.KRISH.COM):

-- Create the source queue
-- This script needs to be run as "strmadmin"

connect strmadmin/strmadmin@ORCL.ORACLEDB1.KRISH.COM

begin
dbms_streams_adm.set_up_queue(
queue_name => 'STREAMS_QUEUE_ORCL_MYDEV');
end;
/

Target(MYDEV.ORACLEDB2.KRISH.COM):

-- Create the destination queue
-- This script needs to be run as "strmadmin"

connect strmadmin/strmadmin@MYDEV.ORACLEDB2.KRISH.COM

begin
dbms_streams_adm.set_up_queue(
queue_name => 'STREAMS_QUEUE_ORCL_MYDEV');
end;
/

7 Setup supplemental logging (S)

The tables which are participating in replication should have primary key exist. If no PK, then unique key columns can be used.

Source(ORCL.ORACLEDB1.KRISH.COM):

-- This script needs to be run as "strmadmin"

connect strmadmin/strmadmin@ORCL.ORACLEDB1.KRISH.COM

alter table SCOTT.EMP add supplemental log data(primary key,unique,foriegn key,all) columns;
alter table SCOTT.DEPT add supplemental log data(primary key,unique,foriegn key,all) columns;
alter table SCOTT.SALGRADE add supplemental log data(primary key,unique,foriegn key,all) columns;
alter table SCOTT.BONUS add supplemental log data(primary key,unique,foriegn key,all) columns;

8 Configure capture process (S)

Source(ORCL.ORACLEDB1.KRISH.COM):

-- This script needs to be run as "strmadmin"

connect strmadmin/strmadmin@ORCL.ORACLEDB1.KRISH.COM

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'SCOTT.EMP',
streams_type => 'capture',
streams_name => 'STREAMS_CAPTURE',
queue_name => 'strmadmin.STREAMS_QUEUE_ORCL_MYDEV',
include_dml => true,
include_ddl => true,
inclusion_rule => true);
END;
/
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'SCOTT.DEPT',
streams_type => 'capture',
streams_name => 'STREAMS_CAPTURE',
queue_name => 'strmadmin.STREAMS_QUEUE_ORCL_MYDEV',
include_dml => true,
include_ddl => true,
inclusion_rule => true);
END;
/
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'SCOTT.SALGRADE',
streams_type => 'capture',
streams_name => 'STREAMS_CAPTURE',
queue_name => 'strmadmin.STREAMS_QUEUE_ORCL_MYDEV',
include_dml => true,
include_ddl => true,
inclusion_rule => true);
END;
/
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'SCOTT.BONUS',
streams_type => 'capture',
streams_name => 'STREAMS_CAPTURE',
queue_name => 'strmadmin.STREAMS_QUEUE_ORCL_MYDEV',
include_dml => true,
include_ddl => true,
inclusion_rule => true);
END;
/

--By default streams retention time is 64 days, set this to a realistic value 7 days.

begin
dbms_capture_adm.alter_capture(
capture_name => 'STREAMS_CAPTURE',
checkpoint_retention_time => 7);
end;
/
--set checkpoint frequency to 1000

begin
DBMS_CAPTURE_ADM.SET_PARAMETER
('STREAMS_CAPTURE', '_checkpoint_frequency','1000');
end;
/

9 Configure propagation process (S)

Source(ORCL.ORACLEDB1.KRISH.COM):

-- This script needs to be run as "strmadmin"

connect strmadmin/strmadmin@ORCL.ORACLEDB1.KRISH.COM

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name => 'SCOTT.EMP',
streams_name => 'STREAMS_PROPAGATE',
source_queue_name => 'strmadmin.STREAMS_QUEUE_ORCL_MYDEV',
destination_queue_name => 'strmadmin.STREAMS_QUEUE_ORCL_MYDEV@MYDEV',
include_dml => true,
include_ddl => true,
source_database => 'ORCL.ORACLEDB1.KRISH.COM',
inclusion_rule => true);
END;
/
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name => 'SCOTT.DEPT',
streams_name => 'STREAMS_PROPAGATE',
source_queue_name => 'strmadmin.STREAMS_QUEUE_ORCL_MYDEV',
destination_queue_name => 'strmadmin.STREAMS_QUEUE_ORCL_MYDEV@MYDEV',
include_dml => true,
include_ddl => true,
source_database => 'ORCL.ORACLEDB1.KRISH.COM',
inclusion_rule => true);
END;
/
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name => 'SCOTT.SALGRADE',
streams_name => 'STREAMS_PROPAGATE',
source_queue_name => 'strmadmin.STREAMS_QUEUE_ORCL_MYDEV',
destination_queue_name => 'strmadmin.STREAMS_QUEUE_ORCL_MYDEV@MYDEV',
include_dml => true,
include_ddl => true,
source_database => 'ORCL.ORACLEDB1.KRISH.COM',
inclusion_rule => true);
END;
/
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name => 'SCOTT.BONUS',
streams_name => 'STREAMS_PROPAGATE',
source_queue_name => 'strmadmin.STREAMS_QUEUE_ORCL_MYDEV',
destination_queue_name => 'strmadmin.STREAMS_QUEUE_ORCL_MYDEV@MYDEV',
include_dml => true,
include_ddl => true,
source_database => 'ORCL.ORACLEDB1.KRISH.COM',
inclusion_rule => true);
END;
/


10 Create destination tables (Metadata-only exp/imp) on Target (T)

Export the objects metadata only from source and import on target database.

Source(ORCL.ORACLEDB1.KRISH.COM):

expdp parfile=expdp.par

expdp.par
userid=system/@ORCL.ORACLEDB1.KRISH.COM
tables=SCOTT.EMP,SCOTT.DEPT,SCOTT.SALGRADE,SCOTT.BONUS,
DUMPFILE=dpump:expdpScott.dmp
LOGFILE= dpump:expdpScott.log
JOB_NAME=exp_job
CONTENT=METADATA_ONLY

Target(MYDEV.ORACLEDB2.KRISH.COM):

impdp parfile=impdp.par

impdp.par
userid=system/@MYDEV.ORACLEDB2.KRISH.COM
tables=SCOTT.EMP,SCOTT.DEPT,SCOTT.SALGRADE,SCOTT.BONUS
DUMPFILE=dpump:expdpScott.dmp
LOGFILE= dpump:impdpScott.log
JOB_NAME=expfull
TABLE_EXISTS_ACTION=replace


11 Set Instantiation of tables from Source (S)

Make sure SCN # is same for all the tables which are participating in streams.

Source(ORCL.ORACLEDB1.KRISH.COM):

connect strmadmin/strmadmin@ORCL.ORACLEDB1.KRISH.COM

BEGIN
DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION('SCOTT.EMP');
DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION('SCOTT.DEPT');
DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION('SCOTT.SALGRADE');
DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION('SCOTT.BONUS');
END;
/
DECLARE
iscn NUMBER;
BEGIN
iscn:=DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@MYDEV.ORACLEDB2.KRISH.COM (
source_object_name => 'SCOTT.EMP',
source_database_name => 'ORCL.ORACLEDB1.KRISH.COM',
instantiation_scn => iscn);
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@MYDEV.ORACLEDB2.KRISH.COM (
source_object_name => 'SCOTT.DEPT',
source_database_name => 'ORCL.ORACLEDB1.KRISH.COM',
instantiation_scn => iscn);
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@MYDEV.ORACLEDB2.KRISH.COM (
source_object_name => 'SCOTT.SALGRADE',
source_database_name => 'ORCL.ORACLEDB1.KRISH.COM',
instantiation_scn => 'iscn');
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@MYDEV.ORACLEDB2.KRISH.COM (
source_object_name => 'SCOTT.BONUS',
source_database_name => 'ORCL.ORACLEDB1.KRISH.COM',
instantiation_scn => 'iscn');
END;
/

12 Export, import of tables (Data) from Source(S) to Target (T)

Source(ORCL.ORACLEDB1.KRISH.COM):

expdp parfile=expdp.par

expdp.par
userid=system/@ORCL.ORACLEDB1.KRISH.COM
Tables=SCOTT.EMP,SCOTT.DEPT,SCOTT.SALGRADE,SCOTT.BONUS,
DUMPFILE=dpump:exp_Scott_data.dmp
LOGFILE= dpump:exp_Scott_data.log
JOB_NAME=exp_job

Target(MYDEV.ORACLEDB2.KRISH.COM):

impdp parfile=impdp.par

impdp.par
userid=system/@MYDEV.ORACLEDB2.KRISH.COM
Tables=SCOTT.EMP,SCOTT.DEPT,SCOTT.SALGRADE,SCOTT.BONUS,
DUMPFILE=dpump:exp_Scott_data.dmp
LOGFILE= dpump:imp_soctt_data.log
JOB_NAME=imp_job
TABLE_EXISTS_ACTION=APPEND

13 Grant object privileges to stream admin user (T)

The apply user must have all grants/permissions to perform DDL and DML operations on the objects.

Target(MYDEV.ORACLEDB2.KRISH.COM):

connect SCOTT/tiger@MYDEV.ORACLEDB2.KRISH.COM

GRANT ALL ON SCOTT.EMP to strmadmin;
GRANT ALL ON SCOTT.DEPT to strmadmin;
GRANT ALL ON SCOTT.SALGRADE to strmadmin;
GRANT ALL ON SCOTT.BONUS to strmadmin;

14 Configure apply process on Target (T)

Target(MYDEV.ORACLEDB2.KRISH.COM):

connect strmadmin/strmadmin@MYDEV.ORACLEDB2.KRISH.COM

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'SCOTT.EMP',
streams_type => 'apply',
streams_name => 'streams_apply',
queue_name =>
'strmadmin.STREAMS_QUEUE_ORCL_MYDEV,
include_dml => true,
include_ddl => true,
source_database => 'ORCL.ORACLEDB1.KRISH.COM',
inclusion_rule => true);
END;
/
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'SCOTT.DEPT',
streams_type => 'apply',
streams_name => 'streams_apply',
queue_name =>
'strmadmin.STREAMS_QUEUE_ORCL_MYDEV',
include_dml => true,
include_ddl => true,
source_database => 'ORCL.ORACLEDB1.KRISH.COM',
inclusion_rule => true);
END;
/
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'SCOTT.SALGRADE',
streams_type => 'apply',
streams_name => 'streams_apply',
queue_name =>
'strmadmin.STREAMS_QUEUE_ORCL_MYDEV',
include_dml => true,
include_ddl => true,
source_database => 'ORCL.ORACLEDB1.KRISH.COM',
inclusion_rule => true);
END;
/
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'SCOTT.BONUS',
streams_type => 'apply',
streams_name => 'streams_apply',
queue_name =>
'strmadmin.STREAMS_QUEUE_ORCL_MYDEV',
include_dml => true,
include_ddl => true,
source_database => 'ORCL.ORACLEDB1.KRISH.COM',
inclusion_rule => true);
END;
/

--Set parameter disable on error & parallelism on Target database

connect strmadmin/strmadmin@MYDEV.ORACLEDB2.KRISH.COM

BEGIN
DBMS_APPLY_ADM.SET_PARAMETER(
apply_name => 'streams_apply',
parameter => 'disable_on_error',
value => 'n');
END;
/

begin
dbms_apply_adm.set_parameter(‘streams_apply’,’parallelism’,’4’);
end;
/

15 Start the apply process on Target (T) & Capture process on source(S)

Target(MYDEV.ORACLEDB2.KRISH.COM):

connect strmadmin/strmadmin@MYDEV.ORACLEDB2.KRISH.COM

BEGIN
DBMS_APPLY_ADM.START_APPLY(
apply_name => 'streams_apply');
END;
/

Source(ORCL.ORACLEDB1.KRISH.COM):

connect strmadmin/strmadmin@ORCL.ORACLEDB1.KRISH.COM

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

10 comments:

  1. Using EM you can eliminate these steps.

    ReplyDelete
  2. Please post the solution of my problem to my mailid if possible...
    renu.ch09@yahoo.com

    ReplyDelete
  3. While implementing oracle streams I got this error NMO: non-set userid in unix please tell me how to get rid of it.

    ReplyDelete
  4. Could you please suggest me an idea.
    Here is my problem. I need to replicate some selected tables only from schema1 to schema2 in the same database. Where structure of schema1 and schema2 are same but we just need 120 tables out of 300 tables.

    Then from schema2 to schema3 i need to replicate some tables. Where schema 3 has 90 tables. These 90 tables in schema3 has same structure as tables in schema2.

    I included DDL's but they are not working. DML's are working fine. I followed your script. Could you please give us an example how to include DDL's as schema names are diff.

    Thanks,
    Ray

    ReplyDelete
  5. If the schema name is different then you need to use declarative transformation, using this you can replicate DML and partial DDL's. I am going to post shortly.

    ReplyDelete
  6. @renu

    for NMO not setuid-root (Unix-only,
    Run root.sh script to correct that problem.

    -Bharath Reddy.

    ReplyDelete
  7. root.sh in AGENT_HOME...
    i.e., Run AGENT_HOME/root.sh , logged in as the root user.
    -B

    ReplyDelete
  8. Hello Krishna,

    Thanks for the step by step streams setup.

    I followed the whole setup couple of times but didn't get succeeded, the changes I'm doing on source table is not reflecting on the target table.

    The only change I did in the setup was, I created public db link instead of private and ignored step 1b as they are implemented in step 8 and 14, did they make any difference??

    Please advice??

    Thanks
    Bharath Reddy.

    ReplyDelete
  9. Hi Krishna,

    Adding to the content you have, would you be able to post how to verify streams replication is going on fine and also a way to check all the tables that are being replicated...

    Thanks for your detailed post....it definitely helps...

    ReplyDelete
  10. Hi Sumanth,
    Thanks for suggestion. I will update the post accordingly and will include the verification step as well.

    Thanks,
    Krishna

    ReplyDelete