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 Go to next message
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 #442921 is a reply to message #442912] Thu, 11 February 2010 06:30 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Michel can you please help
Re: Replication Issue Using Streams [message #443534 is a reply to message #442912] Tue, 16 February 2010 02:59 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Hi Everyone,

As i checked the issue is becuase the Schema name is different
in both oracle servers which needs to be replicated.

If i am creating the same schema on both servers i am able
to replicate the same.

Is this the restriction or i am missing something.

Is it possible to replicate Data from User1 on Server 1
to User2 On Server 2.Where User 1 and User 2 don't have same
name. Confused

Regards,
Rajat Ratewal

Re: Replication Issue Using Streams [message #443549 is a reply to message #442912] Tue, 16 February 2010 03:38 Go to previous messageGo to next message
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 #443553 is a reply to message #443534] Tue, 16 February 2010 04:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, you can use a rule like:
BEGIN
DBMS_STREAMS_ADM.RENAME_SCHEMA (
   rule_name		=> 'STRMADMIN.FROM_SCOTT_TO_COURS',
   from_schema_name	=> 'SCOTT',
   to_schema_name	=> 'COURS',
   step_number	=> 0,
   operation		=> 'ADD');
END;
/

Regards
Michel
Re: Replication Issue Using Streams [message #443563 is a reply to message #442912] Tue, 16 February 2010 05:10 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Dear Micheal,

Thanks so much for your reply.We want to know one thing whether we have to run the below command in destination or source side.

BEGIN
DBMS_STREAMS_ADM.RENAME_SCHEMA (
rule_name => 'STRMADM.FROM_HLUSR_TO_HLSITUSR',
from_schema_name => 'HLUSR@WTOSITDB',
to_schema_name => 'HLSITUSR@WTO9206',
step_number => 0,
operation => 'ADD');
END;
/

Smile


Re: Replication Issue Using Streams [message #443571 is a reply to message #443563] Tue, 16 February 2010 05:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
On the destination site.
Schema names are actual schema not db link.
Do you really have a schema (user) named 'HLUSR@WTOSITDB'?

Regards
Michel
Re: Replication Issue Using Streams [message #443574 is a reply to message #442912] Tue, 16 February 2010 05:24 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Dear Micheal,

On more thing the process where we are applying these changes is

Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production

and we dont have below function over there so what is the replacement of the below command.

BEGIN
DBMS_STREAMS_ADM.RENAME_SCHEMA (
rule_name => 'STRMADM.FROM_HLUSR_TO_HLSITUSR',
from_schema_name => 'HLUSR',
to_schema_name => 'HLSITUSR',
step_number => 0,
operation => 'ADD');
END;
/

Thanks and regards.
Anurag Chaudhary

Smile
Re: Replication Issue Using Streams [message #443576 is a reply to message #443574] Tue, 16 February 2010 05:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
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 #443577 is a reply to message #442912] Tue, 16 February 2010 05:44 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Dear Michael,

Thanks so much .
We will try different schemas in Oracle 10 g and let you know the results.i think it should work in 10 g without any issue.


Regards,
Anurag Chaudhary
Smile
Re: Replication Issue Using Streams [message #443579 is a reply to message #443576] Tue, 16 February 2010 06:19 Go to previous messageGo to next message
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 #443580 is a reply to message #443579] Tue, 16 February 2010 06:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I can't provide Metalink note as this is illegal
I have not the Metalink note number (it was 5 years ago) but a quick search on Metalink (on "Streams rename schema" or the like) will return you the note.

Regards
Michel

[Updated on: Tue, 16 February 2010 06:23]

Report message to a moderator

Re: Replication Issue Using Streams [message #443786 is a reply to message #443580] Wed, 17 February 2010 05:40 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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
Re: Replication Issue Using Streams [message #444181 is a reply to message #444179] Fri, 19 February 2010 02:25 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sorry it has been years since I configured and used Streams and do not remember many things, I can just tell you to refer to the documentation or Metalink notes or OTN (maybe the following will help http://www.oracle.com/technology/products/dataint/index.html).

Regards
Michel
Previous Topic: Streams apply process terribly slow due to long transactions
Next Topic: queue rebuild fails after import
Goto Forum:
  


Current Time: Thu Mar 28 07:10:14 CDT 2024