Home » Server Options » Streams & AQ » Propagating only the required data to destination
Propagating only the required data to destination [message #75638] Wed, 12 November 2003 13:31 Go to next message
Mahesh
Messages: 90
Registered: January 2001
Member
Hi,
How to configure replication for following scenerio.

Database: Db1.net
Table: Dept
Column: deptno
Dname
Loc

Table: Emp
Column: Empno
Ename,
Deptno,
Sal

Database: Db2.net
Table: Dept
Column: deptGuid --unique value autogenerated
Dname
Loc
Db1_deptno

Table: Emp
Column: EmpGuid --unique value autogenerated
Ename,
DeptGuid -- foreign key(select deptguid from dept
where db1_deptno=:deptno)
Sal
Db1_empno

Whenever Insert/update/delete happens in db1.net that
should be replicated to db2.net database.
Db2.net database primary/foreign key columns will be
GUID (In db1.net key columns will be Numbers)

Note: Table and columns are different in both the
database.

We need to use stored procedure ( Message handler )
for replicating this data.

What is best method of replicating data?

Streams or Advanced Replication. ???

Could you please help me?

Thanks & Regards
Mahesh R
Re: Propagating only the required data to destination [message #75639 is a reply to message #75638] Thu, 13 November 2003 05:56 Go to previous messageGo to next message
Frank Naude
Messages: 4579
Registered: April 1998
Senior Member
Hi,

You should be able to solve your problem by implementing STREAMS with rule based transformations to modify the LCR's (logical change records) before they are applied to the destination site.

Examples transformations that can be performed on a LCR:

- Add a new column to a table (lcr.ADD_COLUMN)
- Rename a column (lcr.RENAME_COLUMN)
- Remove a column (lcr.DELETE_COLUMN)
- Change the value of a column (lcr.SET_VALUE)
- Etc.

After writing your transformation function, use the DBMS_RULE_ADM.ALTER_RULE procedure to specify the transformation function as the "action_context" of the rule.

One can apply rule-based transformations at Capture, Propagation or Apply stages. See chapter 6 of the "Oracle9i Streams" guide for more information.

Best regards.

Frank Naude
Re: Propagating only the required data to destination [message #75643 is a reply to message #75639] Fri, 14 November 2003 00:03 Go to previous messageGo to next message
Mahesh
Messages: 90
Registered: January 2001
Member
Thanks Frank Naude.
Re: Propagating only the required data to destination [message #315335 is a reply to message #75643] Mon, 21 April 2008 03:13 Go to previous message
rsethy
Messages: 11
Registered: February 2008
Junior Member
I am configuring Oracle streams in Orcale 9i. I have two databases DBDOCP01 and DBDOCP02. I am configuring to replicate a scenerio where in a table, there is one column(rep_colsupp) less in destination database. I have written a function to delete the extra column before applying in production. But i am not able to add in action_context in dbms_rule_adm package. Plese find the script.
Test II
=======

Column Suppression
=====================

Section 1 - Configured in Destination database(DBDOCP01)
========================================================

1. set up the apply queues
--------------------------
connect strmadmin/strmadmin@dbdocp01

begin
dbms_streams_adm.set_up_queue(
queue_table => 'queue_dbdocp01_colsupt',
queue_name => 'queue_dbdocp01_colsupq',
queue_user => 'strmadmin');
end;
/

2. set up the apply process
---------------------------

connect strmadmin/strmadmin@dbdocp01

begin
dbms_streams_adm.add_table_rules (
table_name => 'td01.TFT_P_GTEE_EBAMEND',
streams_type => 'apply',
streams_name => 'apply_src_dbdocp01_colsup',
queue_name => 'queue_dbdocp01_colsupq',
include_dml => true,
include_ddl => true,
source_database => 'dbdocp02');
end;
/

3. Specify apply user at the destination dbdocp01
-------------------------------------------------
begin
dbms_apply_adm.alter_apply(
apply_name=>'apply_src_dbdocp01_colsup',
apply_user=>'TD01');
end;
/

4.Do not wish the apply process to abort for evry error.
--------------------------------------------------------
begin
dbms_apply_adm.set_parameter (
apply_name => 'apply_src_dbdocp01_colsup',
parameter => 'disable_on_error',
value => 'N');
end;
/

5. Start the apply process
--------------------------
begin
dbms_apply_adm.start_apply (
apply_name => 'apply_src_dbdocp01_colsup');
end;
/


Section 2 - Configured in Source database(DBDOCP02)
===================================================

SQL> select owner,constraint_name,constraint_type,table_name from user_constraints where table_name='TFT_P_GTEE_EBAMEND';

OWNER CONSTRAINT_NAME C TABLE_NAME
------------------------------ ------------------------- - ------------------------------
TD01 TFO_P_GTEE_EBAMEND_01 P TFT_P_GTEE_EBAMEND



SQL> select owner,constraint_name,table_name,column_name,position from user_cons_columns where constraint_name=

'TFO_P_GTEE_EBAMEND_01';

OWNER CONSTRAINT_NAME TABLE_NAME COLUMN_NAME POSITION
------------------------------ ------------------------- ------------------------------ -------------------- ----------
TD01 TFO_P_GTEE_EBAMEND_01 TFT_P_GTEE_EBAMEND BRCH_CODE 2
TD01 TFO_P_GTEE_EBAMEND_01 TFT_P_GTEE_EBAMEND EVT_NO 1
TD01 TFO_P_GTEE_EBAMEND_01 TFT_P_GTEE_EBAMEND ENTY_ID 3


1: Enable table level supplemental logging
------------------------------------------

alter table td01.TFT_P_GTEE_EBAMEND add supplemental log group colsup_TFT_P_GTEE_EBAMEND(evt_no,brch_code,enty_id);


SQL> select log_group_name, table_name from dba_log_groups where owner='TD01';

alter table td01.tft_p_gtee_ebamend drop supplemental log group colsup_TFT_P_GTEE_EBAMEND;


2: set up the capture queue
----------------------------
connect strmadmin/strmadmin@dbdocp02

begin
dbms_streams_adm.set_up_queue(
queue_table => 'queue_dbdocp02_colsupt',
queue_name => 'queue_dbdocp02_colsupq',
queue_user => 'strmadmin');
end;
/

3: set up the capture process
-----------------------------
connect strmadmin/strmadmin@dbdocp02

begin
dbms_streams_adm.add_table_rules (
table_name => 'td01.TFT_P_GTEE_EBAMEND',
streams_type => 'capture',
streams_name => 'capture_dbdocp02strm_colsup',
queue_name => 'queue_dbdocp02_colsupq',
include_dml => true,
include_ddl => true,
source_database=>'dbdocp02');
end;
/


4: set up the propagation process
---------------------------------

connect strmadmin/strmadmin@dbdocp02.world

begin
dbms_streams_adm.add_table_propagation_rules (
table_name => 'td01.TFT_P_GTEE_EBAMEND',
streams_name => 'prop_dbdocp01_dbdocp02_colsup',
source_queue_name => 'strmadmin.queue_dbdocp02_colsupq',
destination_queue_name => 'strmadmin.queue_dbdocp01_colsupq@dbdocp01.world',
include_dml => true,
include_ddl => true,
source_database => 'dbdocp02');
end;
/

5: Rule based transform
-----------------------

CREATE OR REPLACE PACKAGE strmpkg as
Function transform_capture (in_any IN SYS.AnyData) RETURN SYS.AnyData;
END strmpkg;
/

CREATE OR REPLACE PACKAGE BODY strmpkg as
FUNCTION transform_capture (in_any IN SYS.AnyData)
RETURN SYS.AnyData
IS
Lcr SYS.LCR$_ROW_RECORD;
Rc NUMBER;
Ob_owner VARCHAR2(30);
Ob_name VARCHAR2(30);
Cmd VARCHAR2(10);
Newvalue_anydata SYS.AnyData;
Oldvalue_anydata SYS.Anydata;
BEGIN
IF in_any.GETTYPENAME = 'SYS.LCR$_ROW_RECORD' THEN
Rc:=in_any.GETOBJECT(lcr);
Ob_owner:=lcr.GET_OBJECT_OWNER();
Ob_name:=lcr.GET_OBJECT_NAME();
Cmd:=lcr.GET_COMMAND_TYPE();
IF ob_owner='td01' AND cmd = 'UPDATE' THEN
IF ob_name='td01.tft_p_gtee_ebamend' THEN
Newvalue_anydata:=lcr.GET_VALUE( 'new','rep_colsupp');
Oldvalue_anydata:=lcr.GET_VALUE( 'old','rep_colsupp');
IF ((newvalue_anydata IS NOT NULL) OR (oldvalue_anydata IS NOT NULL))
THEN
Lcr.DELETE_COLUMN(column_name=>'rep_colsupp');
END IF;
END IF;
END IF;
RETURN SYS.ANYDATA.CONVERTOBJECT(lcr);
END IF;
RETURN in_any;
END transform_capture;
END strmpkg;
/

6: This Transform Function DML Rule of Capture, and linking.
------------------------------------------------------------
DECLARE
Capture_dml_rule VARCHAR2 (30);
BEGIN
SELECT rule_name INTO capture_dml_rule
FROM sys.streams$_rules
WHERE streams_name = 'CAPTURE_DBDOCP02STRM_COLSUP' and
Rule_type = 1 AND ROWNUM = 1
ORDER BY rule_name DESC;

DBMS_STREAMS_ADM.set_rule_transform_function (
Rule_name => 'capture_dml_rule',
Transform_function => 'strmpkg.transform_capture');
END;
/

DECLARE
Capture_dml_rule VARCHAR2 (30);
BEGIN
SELECT rule_name INTO capture_dml_rule
FROM sys.streams$_rules
WHERE streams_name = 'CAPTURE_DBDOCP02STRM_COLSUP' and
Rule_type = 1 AND ROWNUM = 1
ORDER BY rule_name DESC;

DBMS_rule_ADM.alter_rule (
action_context => 'strmpkg.transform_capture');
END;
/


7: Set the schema instantiation SCN on DBDOCP01
-----------------------------------------------
Setting the schema instantiation SCN for DBDOCP02 at DBDOCP01 ensures that only changes after the instantiation SCN from

DBDOCP02 are applied at DBDOCP01.

connect strmadmin/strmadmin@dbdocp02
declare
v_scn number;
begin
v_scn := dbms_flashback.get_system_change_number();
dbms_apply_adm.set_table_instantiation_scn@dbdocp01.world (
source_object_name => 'td01.TFT_P_GTEE_EBAMEND',
source_database_name => 'dbdocp02',
instantiation_scn => v_scn
);
end;
/

8: Start Capture Process
-------------------------
begin
dbms_capture_adm.start_capture (
capture_name => 'capture_dbdocp02strm_colsup');
end;
/



Please check th script & send me how to delete one column?
Regards.

Raghu
Previous Topic: Dequeue a message from queue after a certain time
Next Topic: How to delete a column from LCR in Oracle 9i
Goto Forum:
  


Current Time: Thu Mar 28 03:54:24 CDT 2024