Search This Blog

Thursday, January 24, 2008

Oracle Streams Setup between two database

Overview
Database Version: 10.1.0.5.0
Windows XP sp2

Database Name and TNS name
DB1, db1 (source)
DB2, db2 (target)


Set up below parameters on both databases (db1, db2)

1. Enable ARCHIVELOG MODE on both database
Reference: http://dbataj.blogspot.com/2007/09/how-to-enable-archivelog-mode.html

2. Create Stream administrator User
Source Database: DB1
SQL> conn sys@db1 as sysdba
Enter password:
Connected.
SQL> create user strmadmin identified by strmadmin;

User created.

SQL> grant connect, resource, dba to strmadmin;

Grant succeeded.

SQL> begin dbms_streams_auth.grant_admin_privilege
2 (grantee => 'strmadmin',
3 grant_privileges => true);
4 end;
5 /

PL/SQL procedure successfully completed.

SQL> grant select_catalog_role, select any dictionary to strmadmin;

Grant succeeded.

Target Database: DB2
SQL> conn sys@db2 as sysdba
Enter password:
Connected.
SQL> create user strmadmin identified by strmadmin;

User created.

SQL> grant connect, resource, dba to strmadmin;

Grant succeeded.

SQL> begin dbms_streams_auth.grant_admin_privilege
2 (grantee => 'strmadmin',
3 grant_privileges => true);
4 end;
5 /

PL/SQL procedure successfully completed.

SQL> grant select_catalog_role, select any dictionary to strmadmin;

Grant succeeded.

3. Setup INIT parameters
Source Database: DB1
SQL> conn sys@db1 as sysdba
Enter password:
Connected.
SQL> alter system set global_names=true;

System altered.

SQL> alter system set streams_pool_size = 100 m;

System altered.

Target Database: DB2
SQL> conn sys@db2 as sysdba
Enter password:
Connected.
SQL> alter system set global_names=true;

System altered.

SQL> alter system set streams_pool_size = 100 m;

System altered.

4. Create Database Link
Target Database: DB1
SQL> conn strmadmin/strmadmin@db1
Connected.
SQL> create database link db2
2 connect to strmadmin
3 identified by strmadmin
4 using 'DB2';

Database link created.

Source Database: DB2
SQL> conn strmadmin/strmadmin@db2
Connected.
SQL> create database link db1
2 connect to strmadmin
3 identified by strmadmin
4 using 'DB1';

Database link created.

5. Setup Source and Destination queues
Source Database: DB1
SQL> conn strmadmin/strmadmin@db1
Connected.
SQL> EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();

PL/SQL procedure successfully completed.

Target Database: DB2
SQL> conn strmadmin/strmadmin@db2
Connected.
SQL> EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();

PL/SQL procedure successfully completed.

6. Setup Schema for streams
Schema: SCOTT
Table: Taj
NOTE: Unlock scott schema because in 10g scott schema is locked by default
Source Database: DB1
SQL> conn sys@db1 as sysdba
Enter password:
Connected.
SQL> alter user scott account unlock identified by tiger;

User altered.

SQL> conn scott/tiger@db1
Connected.
SQL> create table TAJ ( no number primary key,name varchar2(20),ddate date);

Table created.

Target Database: DB2
SQL> conn sys@db2 as sysdba
Enter password:
Connected.
SQL> alter user scott account unlock identified by tiger;

User altered.

SQL> conn scott/tiger@db2
Connected.
SQL> create table TAJ ( no number primary key,name varchar2(20),ddate date);

Table created.

7. Setup Supplemental logging at the source database
Source Database: DB1
SQL> conn scott/tiger@db1
Connected.
SQL> alter table taj
2 add supplemental log data (primary key,unique) columns;

Table altered.

8. Configure capture process at the source database
Source Database: DB1
SQL> conn strmadmin/strmadmin@db1
Connected.
SQL> begin dbms_streams_adm.add_table_rules
2 ( table_name => 'scott.taj',
3 streams_type => 'capture',
4 streams_name => 'capture_stream',
5 queue_name=> 'strmadmin.streams_queue',
6 include_dml => true,
7 include_ddl => true,
8 inclusion_rule => true);
9 end;
10 /

PL/SQL procedure successfully completed.

9. Configure the propagation process
Source Database: DB1
SQL> conn strmadmin/strmadmin@db1
Connected.
SQL> begin dbms_streams_adm.add_table_propagation_rules
2 ( table_name => 'scott.taj',
3 streams_name => 'DB1_TO_DB2',
4 source_queue_name => 'strmadmin.streams_queue',
5 destination_queue_name => 'strmadmin.streams_queue@DB2',
6 include_dml => true,
7 include_ddl => true,
8 source_database => 'DB1',
9 inclusion_rule => true);
10 end;
11 /

PL/SQL procedure successfully completed.
10. Set the instantiation system change number (SCN)
Source Database: DB1
SQL> CONN STRMADMIN/STRMADMIN@DB1
Connected.
SQL> declare
2 source_scn number;
3 begin
4 source_scn := dbms_flashback.get_system_change_number();
5 dbms_apply_adm.set_table_instantiation_scn@DB2
6 ( source_object_name => 'scott.taj',
7 source_database_name => 'DB1',
8 instantiation_scn => source_scn);
9 end;
10 /

PL/SQL procedure successfully completed.

11. Configure the apply process at the destination database
Target Database: DB2
SQL> conn strmadmin/strmadmin@db2
Connected.
SQL> begin dbms_streams_adm.add_table_rules
2 ( table_name => 'scott.taj',
3 streams_type => 'apply',
4 streams_name => 'apply_stream',
5 queue_name => 'strmadmin.streams_queue',
6 include_dml => true,
7 include_ddl => true,
8 source_database => 'DB1',
9 inclusion_rule => true);
10 end;
11 /

PL/SQL procedure successfully completed.
12. Start the capture and apply processes
Source Database: DB1
SQL> conn strmadmin/strmadmin@db1
Connected.
SQL> begin dbms_capture_adm.start_capture
2 ( capture_name => 'capture_stream');
3 end;
4 /

PL/SQL procedure successfully completed.
Target Database: DB2
SQL> conn strmadmin/strmadmin@db2
Connected.
SQL> begin dbms_apply_adm.set_parameter
2 ( apply_name => 'apply_stream',
3 parameter => 'disable_on_error',
4 value => 'n');
5 end;
6 /

PL/SQL procedure successfully completed.

SQL> begin
2 dbms_apply_adm.start_apply
3 ( apply_name => 'apply_stream');
4 end;
5 /

PL/SQL procedure successfully completed.
NOTE: Stream replication environment is ready, just needed to test it.
SQL> conn scott/tiger@db1
Connected.
SQL> --DDL operation
SQL> alter table taj add (flag char(1));

Table altered.

SQL> --DML operation
SQL> begin
2 insert into taj values (1,'first_entry',sysdate,1);
3 commit;
4 end;
5 /

PL/SQL procedure successfully completed.

SQL> conn scott/tiger@db2
Connected.
SQL> --TEST DDL operation
SQL> desc taj
Name Null? Type
----------------------------------------- -------- ----------------------------

NO NOT NULL NUMBER
NAME VARCHAR2(20)
DDATE DATE
FLAG CHAR(1)

SQL> --TEST DML operation
SQL> select * from taj;

NO NAME DDATE F
---------- -------------------- --------- -
1 first_entry 24-JAN-08 1

Reference:
http://www.oracle.com/technology/oramag/oracle/04-nov/o64streams.html

22 comments:

Unknown said...

Taj,
kudos to you. A good article and explanation in simple stemps.

Anonymous said...

Taj
i have followed the same steps ditto but can not get the streams to work, please not that both of my databases are on the same machine, & can u plz point out who does Global_names parameter effects the whole thing.

ALI

Unknown said...

HI taj
thanks Buddy its really helped me lot.
I performed each and every step given below on windows environment and got the success.

Both my database running on same machine.

One more request if you can help me to give some more guidance and exmaple for how to monitoring LCR's and something more about Instantiations

with regards
Gaurav Singhal

Unknown said...

Hello Mohamed,

very nice post.

how can I set up Update resolution and delete resolution in a Streams replication Environment.

thanks,
Ahmed Abd El Latif

Sudipta said...

Hi,

I have afollowed the exact steps mentioned here but can not get the streams to work. Only change i have made is to global_parameters to false. My two databse servers are on dirreent machine and using unix env.

Sudipta said...

Hi,

i have followed each of the steps but streams does not work for me.Can you please help me.. only i have kee global_parameters to false. my two databse server are running in different machine in unix environment

Ahmed Abd El Latif said...

Hi Sudipta

if you are using global_names parameter with false you have to create the dblinks between the 2 databases with the db global names like this

alter database rename global_name to SEDB1.ORACLE.COM;

alter database rename global_name to SEDB2.ORACLE.COM;

create public database link "SEDB1.ORACLE.COM" using 'SE-DB1';

create public database link "SEDB2.ORACLE.COM" using 'SE-DB2';

Anonymous said...

Mr. Taj
Thank-you very much.
I am new to oracle. i just installed oracle 11g r2 on my laptop
64 bit.
and i was able to unlock my account.
Thanks again
Afzal from California
mwahid@edidatanet.com

Kiran Sutar said...

Hi Taj,
I have followd same steps without any error but my stream are not working.
When i am inserting data in DB1 database then it wont comes in DB2.

Thanx in Advance

Kiran Sutar.

Kiran Sutar said...

Hi Taj,
I have followd same steps without any error but my stream are not working.
When i am inserting data in DB1 database then it wont comes in DB2.

Thanx in Advance

Kiran Sutar.
http://www.linkedin.com/in/kiransutar

Anonymous said...

Hi there

Awesome post, just want to say thanks for the share

Anonymous said...

Good point, though sometimes it's hard to arrive to definite conclusions

Anonymous said...

Dear Taj,

this document really helped. would u please post a document to configure streams for database.

Saravanandba said...

ORACLE stream:

consider the scenario, i have implemented oracle streams. i have master to master replication.

consider master1,master2,

if master1 gets crashed, can the user conneting to master1 be automatically connect to master2 without DBA intervention and no downtime?

Anonymous said...

Hey - I am really happy to find this. cool job!

halimdba said...

very nice, thanks

regards
Halim

Mohammed Taj said...

Thank you everyone

Groovy AND Grails said...

Great Job, and Useful, Easy Tutorial

Groovy AND Grails said...

Thank You Very Much !

nelson said...

good article

Anonymous said...

good article

Anonymous said...

Its like you read my mind! You appear to know so much about this, like
you wrote the book in it or something. I think that you can
do with some pics to drive the message home a little bit, but instead
of that, this is fantastic blog. An excellent read. I will certainly be back.


Feel free to surf to my weblog ... hcg weight loss cost