Home » Server Options » Streams & AQ » Replication using streams
Replication using streams [message #276219] Wed, 24 October 2007 07:04 Go to next message
M.Shakeel Azeem
Messages: 226
Registered: September 2006
Senior Member
Dear All,

Oracle Version: 10.1.0.2.0
Operating System: WIndows Server 2003

I am testing Oracle Replication by using the following link
http://www.oracle-base.com/articles/9i/Streams9i.php

i had deployed all the steps mentioned on the link
but after inserting some rows in source table

conn scott/tiger@TESTWLL (Source DB)
INSERT INTO dept (deptno, dname, loc) VALUES (99, 'Test Dept', 'UK')
INSERT INTO dept (deptno, dname, loc) VALUES (50, 'TEST', 'LHR')

SQL> select * from dept;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 TEST LHR
99 Test Dept UK

6 rows selected.

SQL> conn scott/tiger@testdb (target DB)
Connected.
SQL> select * from dept;

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

I didn't miss even a single step but its not working for me
can u please give me any suggestion in this regard?
where i should have to recheck now?


thanx in advance
Re: Replication using streams [message #276240 is a reply to message #276219] Wed, 24 October 2007 08:38 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,
Are you sure you followed all mention steps.?

[Updated on: Wed, 24 October 2007 08:39]

Report message to a moderator

Re: Replication using streams [message #276351 is a reply to message #276219] Wed, 24 October 2007 23:11 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Diagnosis your stream environment . Look at status in dba_capture, dba_propagation and dba_apply and find out where is the problem?
Re: Replication using streams [message #276353 is a reply to message #276219] Wed, 24 October 2007 23:29 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

I saw that link . That like is for 9i and you have version 10g. Why you look for lower one configuration? If you have extra benefit of higher one don't go to downward.
Re: Replication using streams [message #277982 is a reply to message #276219] Thu, 01 November 2007 06:37 Go to previous messageGo to next message
M.Shakeel Azeem
Messages: 226
Registered: September 2006
Senior Member
Dear All,

I had successfully implement Replication with Streams for oracle 9i using the link

http://www.oracle-base.com/articles/9i/Streams9i.php

i think its a uni-Replication ,
e.g
if i update replicated table (EMP) at site dba1 then it will update EMP Table at site dba2 but not vice versa
if i had a TABLE EMP at both sides with same structure and i need if updation is made at dba2 then it'll automatically applied at dba2 and if updation is done at dba2 then it'll automatically applied at dba1
i know i can implement this by using multimaster Replication but i want to know if it is possible with Streams
if its possible then which steps should i have to add to accomplish the task?
thanx in advance
Re: Replication using streams [message #277993 is a reply to message #276219] Thu, 01 November 2007 06:58 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Congrats .

By streams bidirectional replication is possible.
Re: Replication using streams [message #277996 is a reply to message #276219] Thu, 01 November 2007 07:01 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

The parameter bi_directional=>true maintain this.
Re: Replication using streams [message #277999 is a reply to message #277996] Thu, 01 November 2007 07:12 Go to previous messageGo to next message
M.Shakeel Azeem
Messages: 226
Registered: September 2006
Senior Member
Dear MR.Arju

may i ask u where i should have add this parameter
bi_directional=>true to achieve 2-way Replication?

did u see the link which i m using for Replication,i want to ask u where should i have to make modifications to accomplish?


I hope u won't mind it
thanx

Re: Replication using streams [message #278003 is a reply to message #276219] Thu, 01 November 2007 07:19 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Look at DBMS_STREAMS_ADM.MAINTAIN_SCHEMAS

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_strm_a.htm
Re: Replication using streams [message #278367 is a reply to message #276219] Sat, 03 November 2007 08:19 Go to previous messageGo to next message
M.Shakeel Azeem
Messages: 226
Registered: September 2006
Senior Member
Dear MR.Arju

I had configured the Uni-Replication using Streams with following Steps

I had two databases DBA1 and DBA2
DBA1 as source and DBA2 as destination


Step1 Stream Administrator Setup
-------------------------------------------
CONN sys/password@DBA1 AS SYSDBA

CREATE USER strmadmin IDENTIFIED BY strmadminpw
DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;

GRANT CONNECT, RESOURCE, SELECT_CATALOG_ROLE TO strmadmin;

GRANT EXECUTE ON DBMS_AQADM TO strmadmin;
GRANT EXECUTE ON DBMS_CAPTURE_ADM TO strmadmin;
GRANT EXECUTE ON DBMS_PROPAGATION_ADM TO strmadmin;
GRANT EXECUTE ON DBMS_STREAMS_ADM TO strmadmin;
GRANT EXECUTE ON DBMS_APPLY_ADM TO strmadmin;
GRANT EXECUTE ON DBMS_FLASHBACK TO strmadmin;

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

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

CONNECT strmadmin/strmadminpw@DBA1
EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();

CREATE DATABASE LINK dba2 CONNECT TO strmadmin IDENTIFIED BY strmadminpw USING 'DBA2';

GRANT ALL ON scott.dept TO strmadmin;
Note:The above mentioned All steps are also applied DBA2
--------------------------------------------------------------
Step2: LogMinor Tablespace Setup at Source DB DBA1

CONN sys/password@DBA1 AS SYSDBA

CREATE TABLESPACE logmnr_ts DATAFILE '/u01/app/oracle/oradata/DBA1/logmnr01.dbf'
SIZE 25 M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE('logmnr_ts');

---------------------------------------------------

Step3:Supplemental Logging at Source DB DBA1
CONN sys/password@DBA1 AS SYSDBA
ALTER TABLE scott.dept ADD SUPPLEMENTAL LOG GROUP log_group_dept_pk (deptno) ALWAYS;

--------------------------------------------------

Step4:Configure Propagation Process at Source DB DBA1

CONNECT strmadmin/strmadminpw@DBA1
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name => 'scott.dept',
streams_name => 'dba1_to_dba2',
source_queue_name => 'strmadmin.streams_queue',
destination_queue_name => 'strmadmin.streams_queue@dba2',
include_dml => true,
include_ddl => true,
source_database => 'dba1');
END;
/

-----------------------------------------------------
Step5: Configure Capture Process on DBA1

CONNECT strmadmin/strmadminpw@DBA1
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'scott.dept',
streams_type => 'capture',
streams_name => 'capture_simp',
queue_name => 'strmadmin.streams_queue',
include_dml => true,
include_ddl => true);
END;
/

-------------------------------------------------------
Step6: Configure Instantiation SCN on DBA1 (Source DB)

CONNECT strmadmin/strmadminpw@dba1
DECLARE
v_scn NUMBER;
BEGIN
v_scn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@DBA2(
source_object_name => 'scott.dept',
source_database_name => 'dba1',
instantiation_scn => v_scn);
END;
/
-------------------------------------------------
Step7:Configure Apply Process at Destination DB DBA2

CONNECT strmadmin/strmadminpw@DBA2
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'scott.dept',
streams_type => 'apply',
streams_name => 'apply_simp',
queue_name => 'strmadmin.streams_queue',
include_dml => true,
include_ddl => true,
source_database => 'dba1');
END;
/

---------------------------------------------
Step8 Start Apply Process at DEst DB (DBA2)

CONNECT strmadmin/strmadminpw@DBA2
BEGIN
DBMS_APPLY_ADM.SET_PARAMETER(
apply_name => 'apply_simp',
parameter => 'disable_on_error',
value => 'n');

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

-----------------------------------------------
Step9 :Start Capture Process at Source DB DBA1

CONNECT strmadmin/strmadminpw@DBA1
BEGIN
DBMS_CAPTURE_ADM.START_CAPTURE(
capture_name => 'capture_simp');
END;
/

---------------------------------------------

Dear All

These are the basic Steps which i had adopt to implement Replication
This is basically a uni-direction Replication and i came to know that we can configure bi-direction Replication
By Looking at above mentioned steps ,can anybody suggest where i should have to make changes to accomplish the Bidirection Replication???????

i m working on this for the last 3-4 days but unable to do so
Arju had given the suggestion
The parameter bi_directional=>true maintain this
But i don't have an exact idea where i should have to add this
can anybody help me in this regard?

Thanx in advance
Re: Replication using streams [message #278368 is a reply to message #276219] Sat, 03 November 2007 08:26 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

DBMS_STREAMS_ADM.MAINTAIN_TABLES(
   table_names                  IN VARCHAR2,
   source_directory_object      IN VARCHAR2,
   destination_directory_object IN VARCHAR2,
   source_database              IN VARCHAR2,
   destination_database         IN VARCHAR2,
   perform_actions              IN BOOLEAN   DEFAULT TRUE,
   script_name                  IN VARCHAR2  DEFAULT NULL,
   script_directory_object      IN VARCHAR2  DEFAULT NULL,
   dump_file_name               IN VARCHAR2  DEFAULT NULL,
   capture_name                 IN VARCHAR2  DEFAULT NULL,
   capture_queue_table          IN VARCHAR2  DEFAULT NULL,
   capture_queue_name           IN VARCHAR2  DEFAULT NULL,
   capture_queue_user           IN VARCHAR2  DEFAULT NULL,
   propagation_name             IN VARCHAR2  DEFAULT NULL,
   apply_name                   IN VARCHAR2  DEFAULT NULL,
   apply_queue_table            IN VARCHAR2  DEFAULT NULL,
   apply_queue_name             IN VARCHAR2  DEFAULT NULL,
   apply_queue_user             IN VARCHAR2  DEFAULT NULL,
   log_file                     IN VARCHAR2  DEFAULT NULL,
   bi_directional               IN BOOLEAN   DEFAULT FALSE,
   include_ddl                  IN BOOLEAN   DEFAULT FALSE,
   instantiation                IN INTEGER   DEFAULT 
                                           DBMS_STREAMS_ADM.INSTANTIATION_TABLE);
Re: Replication using streams [message #278633 is a reply to message #278368] Mon, 05 November 2007 03:48 Go to previous messageGo to next message
M.Shakeel Azeem
Messages: 226
Registered: September 2006
Senior Member
At Source DB (DBA1)

SQL> conn strmadmin/strmadmin@DBA1
Connected.
SQL> BEGIN
DBMS_STREAMS_ADM.MAINTAIN_TABLES(
table_names => 'SCOTT.SALGRADE',
--source_directory_object => 'hub_dir',
--destination_directory_object => 'spoke1_dir',
source_database => 'DBA1',
destination_database => 'DBA2',
capture_name => 'CAPTURE_SIMP',
capture_queue_table => 'STREAMS_QUEUE_TABLE',
capture_queue_name => 'STREAMS_QUEUE',
propagation_name => 'DBA1_TO_DBA2',
apply_name => 'apply_simp',
apply_queue_table => 'STREAMS_QUEUE_TABLE',
apply_queue_name => ' STREAMS_QUEUE',
bi_directional => TRUE);
END;
17 /
DBMS_STREAMS_ADM.MAINTAIN_TABLES(
*
ERROR at line 2:
ORA-06550: line 2, column 18:
PLS-00302: component 'MAINTAIN_TABLES' must be declared
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored


Please Suggect?
Re: Replication using streams [message #279958 is a reply to message #276219] Mon, 12 November 2007 01:39 Go to previous message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

It sounds like lack of privilege.
Previous Topic: Can Oracle Stream be used for replicating a DB2 database from Oracle Database
Next Topic: multi-directional synchronization
Goto Forum:
  


Current Time: Thu Mar 28 17:00:53 CDT 2024