Monday, July 20, 2009

How To: Setup up of Oracle Streams Replication

Note: I have started using TOAD now, so you wont see the statement complication output.

Step 1: Create stream administration user in both databases.

create user streamadmin identified by streamadmin default tablespace users;

Step 2: Required grants to the user streamadmin.

grant dba,select_catalog_role to streamadmin;
exec dbms_streams_auth.grant_admin_privilege('streamadmin',true);

Step 3: We will use default SCOTT schema for seting up this replication.

Step 4: Check database paramters required for setting up stream replication

For our example:
DB 1:
Name: TEST1
Global Name should be true
SQL> show parameter global_name
NAME TYPE VALUE
------------------------------------ ----------- --------------------------
global_names boolean TRUE

SQL> select * from global_name;
GLOBAL_NAME
-----------------------------------------------
TEST1.REGRESS.RDBMS.DEV.US.ORACLE.COM

SQL> show parameter job_queue

NAME TYPE VALUE
------------------------------------ ----------- ------
job_queue_processes integer 10

SQL> show parameter db_recovery_file_dest_size
NAME TYPE VALUE
------------------------------------ ----------- -----------
db_recovery_file_dest_size big integer 2G

SQL> show parameter db_recovery_file_dest

NAME TYPE VALUE
------------------------------------ ----------- -------------------------------------
db_recovery_file_dest string D:\oracle\product/flash_recovery_area

DB 2:
Name: TEST
Global Name should be true.
SQL> show parameter global_name
NAME TYPE VALUE
------------------------------------ ----------- -------
global_names boolean TRUE

SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------
TEST.REGRESS.RDBMS.DEV.US.ORACLE.COM

SQL> show parameter job_queue
NAME TYPE VALUE
------------------------------------ ----------- ------
job_queue_processes integer 10

SQL> show parameter db_recovery_file_dest_size

NAME TYPE VALUE
------------------------------------ ----------- --------
db_recovery_file_dest_size big integer 2G

SQL> show parameter db_recovery_file_dest

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string D:\oracle\product\10.2.0/flash_recovery_area

Step 5: Enable supplemental logging on the tables of the scott user in both the databases:

Oracle has two types of supplimental logging options:
1.) Unconditional Supplemental Logging: Logs the before images of specified columns any time the table is updated, regardless of whether the update affected any of the specified columns. This is sometimes referred to as an ALWAYS log group.
2.) Conditional Supplemental Log Groups: Logs the before images of all specified columns only if at least one of the columns in the log group is updated.
(From Oracle Documentation)

Login to the SCOTT schema to start conditional supplemental logging:
SQL> alter table emp add supplemental log group supp_log_emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO);
Table altered.
SQL> alter table dept add supplemental log group supp_log_dept (deptno,dname,loc);
Table altered.
SQL> alter table bonus add supplemental log group supp_log_bonus (ename,job,sal,comm);
Table altered.
SQL> alter table salgrade add supplemental log group supp_log_sal (grade,losal,hisal);
Table altered.

The same needs to be done on the other database also.

SQL> select log_group_name, table_name from dba_log_groups where owner='SCOTT';
LOG_GROUP_NAME TABLE_NAME
------------------------------ ------------------------------
SUPP_LOG_EMP EMP
SUPP_LOG_DEPT DEPT
SUPP_LOG_BONUS BONUS
SUPP_LOG_SAL SALGRADE

Step 6: Create Database Links between the stream administrator users in the 2 databases.
SQL> CREATE DATABASE LINK TEST.REGRESS.RDBMS.DEV.US.ORACLE.COM CONNECT TO STREAMADMIN IDENTIFIED BY streamadmin USING 'TEST_REP2';
Database link created.

SQL> CREATE DATABASE LINK TEST1.REGRESS.RDBMS.DEV.US.ORACLE.COM CONNECT TO STREAMADMIN IDENTIFIED BY streamadmin USING 'TEST_REP1';
Database link created.

Step 7: Create Stream Queues under the streamadmin user to apply and capture the database changes to be replicated.
This also needs to run on both the databases as streamadmin.

SQL> show user
USER is "STREAMADMIN"
SQL> exec dbms_streams_adm.set_up_queue(queue_table => 'apply_q', queue_name => 'apply_q', queue_user => 'streamadmin');
PL/SQL procedure successfully completed.

SQL> exec dbms_streams_adm.set_up_queue(queue_table => 'capture_q',queue_name => 'capture_q',queue_user => 'streamadmin');
PL/SQL procedure successfully completed.

Step 8: Setup data capture on both the databases:
Procedure dbms_streams_adm is the administration package for setting up capture process when using streams, like dbms_repcat when using normal replication.
http://www.psoug.org/reference/dbms_streams_adm.html for details reference of this package.

Views sys.streams$_process_params, sys.streams$_capture_process

SQL> BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES (
SCHEMA_NAME => 'SCOTT',
STREAMS_TYPE => 'CAPTURE',
STREAMS_NAME => 'CAPTURE_STREAM',
QUEUE_NAME => 'CAPTURE_Q',
INCLUDE_DML => TRUE,
INCLUDE_DDL => TRUE,
INCLUSION_RULE => TRUE);
END;
/
PL/SQL procedure successfully completed.

The above procedure need to run on both the databases.

Step 9: Setup data apply on both the databases:
SQL> BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES (
SCHEMA_NAME => 'SCOTT',
STREAMS_TYPE => 'APPLY',
STREAMS_NAME => 'APPLY_STREAM',
QUEUE_NAME => 'APPLY_Q',
INCLUDE_DML => TRUE,
INCLUDE_DDL => TRUE,
SOURCE_DATABASE => 'TEST.REGRESS.RDBMS.DEV.US.ORACLE.COM');
END;
/
PL/SQL procedure successfully completed.

SQL> BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES (
SCHEMA_NAME => 'SCOTT',
STREAMS_TYPE => 'APPLY',
STREAMS_NAME => 'APPLY_STREAM',
QUEUE_NAME => 'APPLY_Q',
INCLUDE_DML => TRUE,
INCLUDE_DDL => TRUE,
SOURCE_DATABASE => 'TEST1.REGRESS.RDBMS.DEV.US.ORACLE.COM');
END;
/
PL/SQL procedure successfully completed.

Step 10: Setup propogation process on both the databases:
Its basically setting up related between the capture process on one database and apply process on the other database.
Thes need to run as streamadmin user.
DB 1:

SQL> BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES (
SCHEMA_NAME => 'SCOTT',
STREAMS_NAME => 'TEST1_TO_TEST',
SOURCE_QUEUE_NAME => 'CAPTURE_Q',
DESTINATION_QUEUE_NAME => 'APPLY_Q@TEST.REGRESS.RDBMS.DEV.US.ORACLE.COM',
INCLUDE_DML => TRUE,
INCLUDE_DDL => TRUE,
SOURCE_DATABASE => 'TEST1.REGRESS.RDBMS.DEV.US.ORACLE.COM');
END;
/
PL/SQL procedure successfully completed.

DB 2:
SQL> BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES (
SCHEMA_NAME => 'SCOTT',
STREAMS_NAME => 'TEST_TO_TEST1',
SOURCE_QUEUE_NAME => 'CAPTURE_Q',
DESTINATION_QUEUE_NAME => 'APPLY_Q@TEST1.REGRESS.RDBMS.DEV.US.ORACLE.COM',
INCLUDE_DML => TRUE,
INCLUDE_DDL => TRUE,
SOURCE_DATABASE => 'TEST.REGRESS.RDBMS.DEV.US.ORACLE.COM');
END;
/
PL/SQL procedure successfully completed.

Step 11: Setup schema instantiation SCN on DB 2 (TEST) & DB 1 (TEST1):

SQL> DECLARE
ISCN NUMBER;
BEGIN
ISCN := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN@TEST.REGRESS.RDBMS.DEV.US.ORACLE.COM (
SOURCE_SCHEMA_NAME => 'SCOTT',
SOURCE_DATABASE_NAME => 'TEST1.REGRESS.RDBMS.DEV.US.ORACLE.COM',
INSTANTIATION_SCN => ISCN,
RECURSIVE => TRUE);
END;
/
PL/SQL procedure successfully completed.

DECLARE
ISCN NUMBER;
BEGIN
ISCN := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN@TEST1.REGRESS.RDBMS.DEV.US.ORACLE.COM (
SOURCE_SCHEMA_NAME => 'SCOTT',
SOURCE_DATABASE_NAME => 'TEST.REGRESS.RDBMS.DEV.US.ORACLE.COM',
INSTANTIATION_SCN => ISCN,
RECURSIVE => TRUE);
END;
/
PL/SQL procedure successfully completed.

Step 12: Start capture and apply process:
Setting the disable_on_error parameter to ‘N’ allows the apply process to continue applying row LCRs even when it encounters errors. The default value is ‘Y’ which disables the apply process automatically on the first error encountered.
DB 1 (TEST1):

SQL> EXEC DBMS_APPLY_ADM.SET_PARAMETER (APPLY_NAME => 'APPLY_STREAM', PARAMETER => 'DISABLE_ON_ERROR', VALUE => 'N');
PL/SQL procedure successfully completed.

SQL> EXEC DBMS_APPLY_ADM.START_APPLY (APPLY_NAME => 'APPLY_STREAM');
PL/SQL procedure successfully completed.

SQL> EXEC DBMS_CAPTURE_ADM.START_CAPTURE (CAPTURE_NAME => 'CAPTURE_STREAM');
PL/SQL procedure successfully completed.

Same steps for the other DB also.

Steps 13: Test the setup. I just realized that its "Step 13" for testing, not good not good, not number 13.
Actually I faced a few problems with the test.
1.) The AQ_TM_PROCESSES parameter in my case was 0, but the data was not getting transferred.
2.) I had not set the instantiation SCN for the second DB.

I found the above errors from the below query:
SELECT APPLY_NAME,
SOURCE_DATABASE,
LOCAL_TRANSACTION_ID,
ERROR_NUMBER,
ERROR_MESSAGE,
MESSAGE_COUNT
FROM DBA_APPLY_ERROR;

After rectifying the error, to apply the failed transaction:
BEGIN
DBMS_APPLY_ADM.EXECUTE_ERROR(
local_transaction_id => '4.30.434',
execute_as_user => false);
END;
You ge the local_transaction_id from the above query.

Anyway lets test the setup once again:
Insert test:
------------
SQL> connect scott/tiger@test_rep1
Connected.
SQL> insert into dept values (50,'RESEARCH','MEMPHIS');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from dept@TEST.REGRESS.RDBMS.DEV.US.ORACLE.COM;

DEPTNO DNAME LOC
---------- -------------- -------------
50 RESEARCH MEMPHIS
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

Update Test:
------------
SQL> connect scott/tiger@test_rep2
Connected.
SQL> update dept set loc='DELHI' where deptno=50;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from dept@TEST1.REGRESS.RDBMS.DEV.US.ORACLE.COM;

DEPTNO DNAME LOC
---------- -------------- -------------
50 RESEARCH DELHI
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

Finally u can see that basic steps of replication is working.

26 Comments:

Anonymous said...

Hi, Can you please let me know which tasks should be executed with which user, I believe some tasks are need to be executed with "strmadmin"

Apun Hiran said...

Hi,
In most steps I have mentioned which user you need to login to:
stream administrator=strmadmin.
Step 5: through the user which owns the tables that need to be replicated.
Step6 to Step 13: strmadmin user.

Thanks
Apun

ابراهيم البحيصي said...

hi..
thanks for this useful article..i am trying for 3 days to implement simple stream example..i had no errors in capture,propagate, apply processes, but data was not transferred between my source and destination databases.. i am confused with the arch_log_dest parameter.. how to set up it !
is it must be the same for the two databases ?!

Anonymous said...

Hi Apun,
thanks for your guide. Can you please clarify step 9?
Do I run both commands on both databases or one command on each database and where do I run which script?

Apun Hiran said...

Hi,
For step 9:
You need to run 1 command on each database. You would see that source database value is different in both commands.
Regards
Apun

Naresh Kumar said...

Hi,
I have configured replication(capture,propagation and apply) properly which i can see enterprise manager repication management screen.But replication doesn't happen,can u please help me.Rge database link successfully creates,but when i try to connect,it fails

Naresh Kumar said...

Hi,
I have configured replcaiton(capture,propagation and apply )properly which i can see enterprise manager repication management screen.But replication doesn't happen,can u please help me

The database link fails even though it doesn't give any errors

GoldenGate said...

Great post. Thanks

SRIWIDU said...

Hi,

Can you please let us know what should be the listener and tns configurationns for both the databases?

It would be great if you could paste them.

Thank you.

Regards,
SRIWIDU

Anonymous said...

I have configured replcaiton(capture,propagation and apply )properly which i can see enterprise manager repication management screen.But replication doesn't happen,can u please help me

And the database link are working.

Anonymous said...

Thanks for the clear steps.

Bharat Negi said...

Many Thanks for this uses full article.

valiantvimal said...

Thanks for the article. But I followed Oracle's white paper:

https://docs.google.com/viewer?a=v&q=cache:PCpDMgATs0AJ:www.oracle.com/technetwork/database/features/availability/maa-10gr2-streams-configuration-132039.pdf+&hl=en&gl=in&pid=bl&srcid=ADGEESiE0K0RJEtBXB8_7IQH2Hw4P75oKtoDCokn32nOVvLYzOSrblErguvZJFbzSW4_mmn71l6Hwsp-jQ5CxNvbg68z3aLgU3WxqEpq6IRa4_XjU_Ll5Ff8EX3j6sK_TdLwuY-n66jG&sig=AHIEtbRluNchac4RXIdhXnwq-_zi-iwoqg

for complete procedure including PFILE configuration.
Thanks anyway.

Mohanad Awad said...

Hello ,

on one of node : when i execute below :

BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES (
SCHEMA_NAME => 'SCOTT',
STREAMS_TYPE => 'CAPTURE',
STREAMS_NAME => 'CAPTURE_STREAM',
QUEUE_NAME => 'CAPTURE_Q',
INCLUDE_DML => TRUE,
INCLUDE_DDL => TRUE,
INCLUSION_RULE => TRUE);
END;

it hangs !! on there node it work fine .

Can you Please help me ... ?

Regards,
Mohanad Awad

Apun Hiran said...

When u say 1 node, are you talking abt a RAC cluster or node 1 is one master location and node 2 is another master location. Can you please check the network and make sure both nodes are setup in the same way. I haven't seen this issue before.

Regards
Apun

Mins said...

hi Apun,

Thanks for this article, seems to be working for a lot of people.

I want to have two schema's of 1 Database having replicated data, can I establish streams between two schemas ?

Thanks
meenakshi

Apun Hiran said...

Hi Meenakshi,
I am not sure why you would want 2 schemas in the same database with the same data. Technically it is possible.
But if the idea is to just have same data, I would rather create synonyms/views in the second schema point to the schema with data.

Regards
Apun

Mins said...

Thanks Apun.
My requirement is that I have to point my application to an alternate data set during daily refresh process.

Since I do not have the budget to have additional memory and CPU for a secondary DB Instance, I would have to look for alternatives.

I am planning to have a separate schema which will be refreshed via streams.

Do you have any reference to implemnet this or any other alternative solution approach ?
Thanks

Unknown said...

hi,

Is it possible to configure a schema level replication with these steps. i have a schema on two different databases and that are created on two different server and the schema will have some 100 tables. I want the bi directional replication for them.

As u mentioned in step 5 to enable supplemental logging for the table. To achieve schema level what if in place of that step 5 the db level supplemental logging in enabled and then the rest of the steps be followed. Please let me know...

Thanks

Unknown said...

hi,

Is it possible to configure a schema level replication with these steps. i have a schema on two different databases and that are created on two different server and the schema will have some 100 tables. I want the bi directional replication for them.

As u mentioned in step 5 to enable supplemental logging for the table. To achieve schema level what if in place of that step 5 the db level supplemental logging in enabled and then the rest of the steps be followed. Please let me know...

Thanks

Apun Hiran said...

Hi Nikhil,
I have never used database level supplemental logging, as I have always replicated a few tables between database. But you can find all the details on logging at http://docs.oracle.com/cd/B12037_01/server.101/b10728/man_gen_.htm#1008213

I think you can use database level supplemental logging.
Regards
Apun

V. Kapoor said...

it was really helpfull.

Arun said...

Hi Mr.Apun
In the step 11 am facing the following error. Using db link i can able to access DB1 to DB2 ; FROM DB2 TO DB1.

can u please help me
ERROR at line 1:
ORA-12154: TNS:could not resolve the connect identifier specified
ORA-06512: at "SYS.DBMS_APPLY_ADM", line 785
ORA-06512: at "SYS.DBMS_APPLY_ADM", line 856
ORA-06512: at line 5

Arun said...

Hi Mr.Apun
In the step 11 am facing the following error. Using db link i can able to access DB1 to DB2 ; FROM DB2 TO DB1.

can u please help me
ERROR at line 1:
ORA-12154: TNS:could not resolve the connect identifier specified
ORA-06512: at "SYS.DBMS_APPLY_ADM", line 785
ORA-06512: at "SYS.DBMS_APPLY_ADM", line 856
ORA-06512: at line 5

Apun Hiran said...

Hi Arun,
Please check the tnsnames.ora for the tns you have setup from DB2 to DB1. Basically check the tns entry from DB1 in DB2 server/host.
This error looks like the tns is not setup properly, or there is a connectivity issue from DB2 to DB1.
You can check if port 1521 is open or not.

telnet 1521 #check if port 1521 is open from DB2 to DB1.

Regards
Apun

Anonymous said...

Thanks for the details. Can you please give us details on how much time the entire confirguration would take approximately?

Also does it require any addition hardware or software changes?