Home » Server Options » Streams & AQ » Replication using streams
| Replication using streams [message #276219] |
Wed, 24 October 2007 07:04  |
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 #277982 is a reply to message #276219] |
Thu, 01 November 2007 06:37  |
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 #277999 is a reply to message #277996] |
Thu, 01 November 2007 07:12 |
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 #278367 is a reply to message #276219] |
Sat, 03 November 2007 08:19 |
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 |
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 |
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?
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Dec 12 21:03:30 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
