Home » Server Options » Streams & AQ » Replication Issue Using Streams (10.2.0.1.0)
| Replication Issue Using Streams [message #442912] |
Thu, 11 February 2010 04:56  |
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
|
Senior Member |
|
|
Hi,
Can you please help me in pointing what can be the missing link
in our replication process ?
Issue is we are unable to make changes(insert/update in source) in destination DB using streams.
Step followed -: Capture process defined at source, DB link also created, propagation process also defined.
At destination -:
V$STREAMS_APPLY_READER is showing below entries -:
state TOTAL_MESSAGES_DEQUEUED TOTAL_MESSAGES_SPILLED DEQUEUED_MESSAGE_NUMBER OLDEST_XIDUSN OLDEST_XIDSLT OLDEST_XIDSQN
DEQUEUE MESSAGES 12 0 3580495238347 7 42 51408
V$STREAMS_APPLY_COORDINATOR is showing below entries -:
Total admin -: 7
State -: Idle
Rest all columns are showing O value apart from ELAPSED_IDLE_TIME and ELAPSED_SCHEDULE_TIME.
V$STREAMS_APPLY_SERVER is also having all the values as zero.
We have not configured any DML handlers as we want oracle to handle this automatically.
Can you pls help as i am struggling from last couple of days and not been able to get any breakthrough.
Oracle version is 10.2.0.1.0.
Regards,
Rajat
|
|
|
|
|
|
|
|
| Re: Replication Issue Using Streams [message #443549 is a reply to message #442912] |
Tue, 16 February 2010 03:38  |
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
|
Senior Member |
|
|
Please find the Steps That I executed In Both The DB'S
Source Database:
SQL> CREATE USER strmadm IDENTIFIED BY strmadm
2 /
User created.
SQL> ALTER USER strmadm DEFAULT TABLESPACE streams_tbs quota unlimited on streams_tbs
2 /
User altered.
SQL> grant connect,resource,dba to strmadm
2 /
Grant succeeded.
SQL>
1 BEGIN
2 DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
3 grantee => 'strmadm',
4 grant_PRIVILEGES=> TRUE);
5* END;
6 /
PL/SQL procedure successfully completed.
SQL> conn strmadm/strmadm@wtositdb
Connected.
SQL>
1 BEGIN
2 DBMS_STREAMS_ADM.SET_UP_QUEUE(queue_table=>'strmadm.NY1_QUEUE_TABLE',
3 QUEUE_NAME=>'STRMADM.NY1_QUEUE');
4* END;
5 /
PL/SQL procedure successfully completed.
SQL>
1 BEGIN
2 DBMS_STREAMS_ADM.ADD_TABLE_RULES(
3 table_name => 'HLUSR.STREAMS_TEST',
4 streams_type => 'capture',
5 streams_name => 'NY1_capture',
6 queue_name => 'NY1_QUEUE',
7 include_dml => true,
8 include_ddl => true,
9 inclusion_rule => true);
10* END;
11 /
PL/SQL procedure successfully completed.
SQL>
1 BEGIN
2 DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
3 table_name => 'HLUSR.STREAMS_TEST',
4 streams_name => 'NY1_PROPAGATE',
5 source_queue_name => 'strmadm.NY1_QUEUE',
6 destination_queue_name => 'strmadm.LN1_QUEUE@WTO9206.US.ORACLE.COM',
7 include_dml => true,
8 include_ddl => true,
9 source_database => 'ORABASE.PSOUG.ORG',
10 inclusion_rule => true);
11* END;
SQL> /
PL/SQL procedure successfully completed.
SQL> SET SERVEROUTPUT ON
SQL>
1 DECLARE
2 iscn NUMBER; -- Variable to hold instantiation SCN value
3 BEGIN
4 iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
5 DBMS_OUTPUT.PUT_LINE(iscn);
6 DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@WTO9206.US.ORACLE.COM(
7 source_object_name => 'HLUSR.STREAMS_TEST',
8 source_database_name => 'ORABASE.PSOUG.ORG',
9 instantiation_scn => iscn);
10* END;
SQL> /
PL/SQL procedure successfully completed.
SQL>
1 BEGIN
2 DBMS_CAPTURE_ADM.START_CAPTURE(
3 capture_name => 'NY1_CAPTURE');
4* END;
SQL> /
PL/SQL procedure successfully completed.
SQL> spool off
Target Database:
SQL> CREATE USER strmadm IDENTIFIED BY strmadm
2 /
User created.
SQL> ALTER USER strmadm DEFAULT TABLESPACE streams_tbs quota unlimited on streams_tbs
2 /
User altered.
SQL> grant connect,resource,dba to strmadm
2 /
Grant succeeded.
SQL> GRANT EXECUTE ON DBMS_AQADM TO strmadm;
Grant succeeded.
SQL> /
Grant succeeded.
SQL> GRANT EXECUTE ON DBMS_AQADM TO strmadm;
Grant succeeded.
SQL> GRANT EXECUTE ON DBMS_CAPTURE_ADM TO strmadm;
Grant succeeded.
SQL> GRANT EXECUTE ON DBMS_PROPAGATION_ADM TO strmadm;
Grant succeeded.
SQL> GRANT EXECUTE ON DBMS_STREAMS_ADM TO strmadm;
Grant succeeded.
SQL> GRANT EXECUTE ON DBMS_APPLY_ADM TO strmadm;
Grant succeeded.
SQL> GRANT EXECUTE ON DBMS_FLASHBACK TO strmadm;
Grant succeeded.
SQL> CONN STRMADM/STRMADM@WTO9206
Connected.
SQL>
1 BEGIN
2 DBMS_STREAMS_ADM.SET_UP_QUEUE(queue_table=>'strmadm.LN1_QUEUE_TABLE',
3 QUEUE_NAME=>'LN1_QUEUE');
4* END;
SQL> /
PL/SQL procedure successfully completed.
SQL>
1 BEGIN
2 DBMS_STREAMS_ADM.ADD_TABLE_RULES(
3 table_name => 'HLSITUSR.STREAMS_TEST',
4 streams_type => 'apply',
5 streams_name => 'LN1_apply',
6 queue_name => 'LN1_QUEUE',
7 include_dml => true,
8 include_ddl => true,
9 source_database =>'ORABASE.PSOUG.ORG');
10* END;
SQL> /
PL/SQL procedure successfully completed.
SQL>
1 BEGIN
2 DBMS_APPLY_ADM.START_APPLY(
3 apply_name => 'LN1_APPLY');
4* END;
SQL> /
PL/SQL procedure successfully completed.
SQL> spool off
Kindly check if i am missing something.Is thier any issue replicating from one oracle version to another oracle version
i.e Source 10g Target 9i
Regards,
Rajat Ratewal
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Replication Issue Using Streams [message #443576 is a reply to message #443574] |
Tue, 16 February 2010 05:33 |
 |
Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Sorry but your first post mention:
Quote:Oracle version is 10.2.0.1.0.
So answer is for this version.
It is much more difficult in 9.2 and require writing a transformation function, creating a rule, and so on. It is explained in a Metalink note.
Regards
Michel
[Edit: typos]
[Updated on: Tue, 16 February 2010 06:04] Report message to a moderator |
|
|
|
|
|
| Re: Replication Issue Using Streams [message #443579 is a reply to message #443576] |
Tue, 16 February 2010 06:19 |
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
|
Senior Member |
|
|
Dear Michel,
Thank you so much for the much needed help.
Can you pls provide link of metalink note as we don't have access to metalink.
pls provide metalink note number also so that we can arrange the solution.
Regards,
Rajat
|
|
|
|
|
|
| Re: Replication Issue Using Streams [message #443786 is a reply to message #443580] |
Wed, 17 February 2010 05:40 |
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
|
Senior Member |
|
|
Dear Michel,
We found one note "309575.1", but it is of transforming schema rules. We tried to change it to transform table rules but it is still not working and DBA_apply_error is also not giving any error.
This is what we have done. Can you pls help in pointing what is missing in our approach.
Create or Replace function to_test1_tran_ddl_dml( p_in_data in SYS.AnyData)
RETURN SYS.AnyData IS
out_data_dml SYS.LCR$_ROW_RECORD;
out_data_ddl SYS.LCR$_DDL_RECORD;
tc pls_integer;
typenm VARCHAR2(61);
BEGIN
typenm := p_in_data.getTypeName();
IF typenm = 'SYS.LCR$_ROW_RECORD' THEN
-- Typecast AnyData to LCR$_ROW_RECORD
tc := p_in_data.GetObject(out_data_dml);
IF out_data_dml.get_object_owner() = 'HLUSR' THEN
-- Transform the in_data into the out_data
out_data_dml.set_object_owner('HLSITUSR');
RETURN SYS.AnyData.ConvertObject(out_data_dml);
END IF;
ELSIF typenm = 'SYS.LCR$_DDL_RECORD' THEN
-- Typecast AnyData to LCR$_DDL_RECORD
tc := p_in_data.GetObject(out_data_ddl);
IF out_data_ddl.get_current_schema() = 'HLUSR' THEN
-- Transform the in_data into the out_data
out_data_ddl.set_current_schema('HLSITUSR');
RETURN SYS.AnyData.ConvertObject(out_data_ddl);
END IF;
END IF;
RETURN p_in_data;
END;
DECLARE
glbl_name VARCHAR2(128);
to_sra_rep_rulename_dml VARCHAR2(30);
to_sra_rep_rulename_ddl VARCHAR2(30);
action_ctx_ddl sys.re$nv_list;
action_ctx_dml sys.re$nv_list;
ac_name varchar2(30) := 'STREAMS$_TRANSFORM_FUNCTION';
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'HLUSR.STREAMS_TEST',
streams_type => 'apply',
streams_name => 'LN1_apply',
queue_name => 'LN1_QUEUE',
include_dml => true,
include_ddl => true,
source_database =>'WTO9206.US.ORACLE.COM',
dml_rule_name => to_sra_rep_rulename_dml,
ddl_rule_name => to_sra_rep_rulename_ddl);
action_ctx_dml := sys.re$nv_list(sys.re$nv_array());
action_ctx_dml.add_pair( ac_name, sys.anydata.convertvarchar2('strmadm.to_test1_tran_ddl_dml'));
dbms_rule_adm.alter_rule(
rule_name => to_sra_rep_rulename_dml,
action_context => action_ctx_dml);
action_ctx_ddl := sys.re$nv_list(sys.re$nv_array());
action_ctx_ddl.add_pair( ac_name, sys.anydata.convertvarchar2('strmadm.to_test1_tran_ddl_dml'));
dbms_rule_adm.alter_rule(
rule_name => to_sra_rep_rulename_ddl,
action_context => action_ctx_ddl);
END;
DECLARE
rs_name VARCHAR2(64); -- Variable to hold rule set name
BEGIN
SELECT RULE_SET_OWNER||'.'||RULE_SET_NAME
INTO rs_name
FROM DBA_APPLY
WHERE APPLY_NAME='LN1_APPLY';
DBMS_RULE_ADM.GRANT_OBJECT_PRIVILEGE(
privilege => SYS.DBMS_RULE_ADM.EXECUTE_ON_RULE_SET,
object_name => rs_name,
grantee => 'HLUSR');
END;
grant execute on to_test1_tran_ddl_dml to HLUSR
BEGIN
DBMS_APPLY_ADM.ALTER_APPLY(
apply_name => 'LN1_APPLY',
apply_user => 'HLUSR');
END;
BEGIN
DBMS_APPLY_ADM.START_APPLY(
apply_name => 'LN1_APPLY');
END;
Thanks in advance.
Regards,
Rajat
|
|
|
|
| Re: Replication Issue Using Streams [message #444179 is a reply to message #443574] |
Fri, 19 February 2010 02:14 |
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
|
Senior Member |
|
|
Dear Michel,
Thanks for help and suggestion. We have been able to replicate DML changes in another schema using rename_schema.
Do oracle have any inbuilt functions for transforming DDL also ?
Can you pls help in guiding us on how to replicate DDL also in another schema (destination schema name is different then source) ?
Appreciate your much needed help.
Regards,
Rajat
|
|
|
|
|
|
Goto Forum:
Current Time: Wed Jan 08 08:20:00 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
