Home » Server Options » Streams & AQ » REPLICATION IN SAME DATABASE.... (11g sn solaris)
REPLICATION IN SAME DATABASE.... [message #320900] Fri, 16 May 2008 11:53 Go to next message
sonumalhi
Messages: 62
Registered: April 2008
Member
All

I am just testing the streams by replication the dml of one table in the same schema.
Ihave followed the following steps and all the steps are completed succesfully.
But still i dont see any rows in the replicated tables.

Create our streams administrator:

create user strmadmin identified by strmadmin
default tablespace streams_tbs
quota unlimited on streams_tbs;

grant dba to strmadmin;

We also want to grant streams admin privs to the user.
BEGIN
DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
grantee => 'strmadmin',
grant_privileges => true);
END;

The next steps we'll run as the HR user.
conn hr/hr
Grant all access to the employee table to the streams admin:
grant all on hr.employees to strmadmin;
We also need to create the employee_audit table. Note that I am adding three columns in this table that do not exist in the
employee table.
CREATE TABLE employee_audit(
employee_id NUMBER(6),
first_name VARCHAR2(20),
last_name VARCHAR2(25),
email VARCHAR2(25),
phone_number VARCHAR2(20),
hire_date DATE,
job_id VARCHAR2(10),
salary NUMBER(8,2),
commission_pct NUMBER(2,2),
manager_id NUMBER(6),
department_id NUMBER(4),
upd_date DATE,
user_name VARCHAR2(30),
action VARCHAR2(30));
Grant all access to the audit table to the streams admin user:
grant all on hr.employee_audit to strmadmin;
We connect as the streams admin user:
conn strmadmin/strmadmin
We can create a logging table. You would NOT want to do this in a high-volume production system. I am doing this to
illustrate user defined monitoring and show how you can get inside the capture process.
CREATE TABLE streams_monitor (
date_and_time TIMESTAMP(6) DEFAULT systimestamp,
txt_msg CLOB );
Here we create the queue. Unlike AQ, where you have to create a separate table, this step creates the queue and the
underlying ANYDATA table.
BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => 'strmadmin.streams_queue_table',
queue_name => 'strmadmin.streams_queue');
END;
/
This just defines that we want to capture DML and not DDL.
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'hr.employees',
streams_type => 'capture',
streams_name => 'capture_emp',
queue_name => 'strmadmin.streams_queue',
include_dml => true,
include_ddl => false,
inclusion_rule => true);
www.odtug.com 2 ODTUG Kaleidoscope 2007
Step-By-Step Streams Cunningham
END;
/
Tell the capture process that we want to know who made the change:
BEGIN
DBMS_CAPTURE_ADM.INCLUDE_EXTRA_ATTRIBUTE(
capture_name => 'capture_emp',
attribute_name => 'username',
include => true);
END;
/
We also need to tell Oracle where to start our capture. Change the source_database_name to match your database.
DECLARE
iscn NUMBER;
BEGIN
iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(
source_object_name => 'hr.employees',
source_database_name => 'ORCL',
instantiation_scn => iscn);
END;
/
And the fun part! This is where we define our capture procedure. I'm taking this right from the docs but I'm adding a couple
steps.
CREATE OR REPLACE PROCEDURE emp_dml_handler(in_any IN ANYDATA) IS
lcr SYS.LCR$_ROW_RECORD;
rc PLS_INTEGER;
command VARCHAR2(30);
old_values SYS.LCR$_ROW_LIST;
BEGIN
-- Access the LCR
rc := in_any.GETOBJECT(lcr);
-- Get the object command type
command := lcr.GET_COMMAND_TYPE();
-- I am inserting the XML equivalent of the LCR into the monitoring table.
insert into streams_monitor (txt_msg)
values (command ||
DBMS_STREAMS.CONVERT_LCR_TO_XML(in_any) );
-- Set the command_type in the row LCR to INSERT
lcr.SET_COMMAND_TYPE('INSERT');
-- Set the object_name in the row LCR to EMP_DEL
lcr.SET_OBJECT_NAME('EMPLOYEE_AUDIT');
-- Set the new values to the old values for update and delete
IF command IN ('DELETE', 'UPDATE') THEN
-- Get the old values in the row LCR
old_values := lcr.GET_VALUES('old');
-- Set the old values in the row LCR to the new values in the row LCR
lcr.SET_VALUES('new', old_values);
-- Set the old values in the row LCR to NULL
lcr.SET_VALUES('old', NULL);
END IF;
-- Add a SYSDATE for upd_date
lcr.ADD_COLUMN('new', 'UPD_DATE', ANYDATA.ConvertDate(SYSDATE));
-- Add a user column
lcr.ADD_COLUMN('new', 'user_name',
lcr.GET_EXTRA_ATTRIBUTE('USERNAME') );
-- Add an action column
lcr.ADD_COLUMN('new', 'ACTION', ANYDATA.ConvertVarChar2(command));
www.odtug.com 3 ODTUG Kaleidoscope 2007
Step-By-Step Streams Cunningham
-- Make the changes
lcr.EXECUTE(true);
commit;
END;
/
Create the DML handlers:
BEGIN
DBMS_APPLY_ADM.SET_DML_HANDLER(
object_name => 'hr.employees',
object_type => 'TABLE',
operation_name => 'INSERT',
error_handler => false,
user_procedure => 'strmadmin.emp_dml_handler',
apply_database_link => NULL,
apply_name => NULL);
END;
/
BEGIN
DBMS_APPLY_ADM.SET_DML_HANDLER(
object_name => 'hr.employees',
object_type => 'TABLE',
operation_name => 'UPDATE',
error_handler => false,
user_procedure => 'strmadmin.emp_dml_handler',
apply_database_link => NULL,
apply_name => NULL);
END;
/
BEGIN
DBMS_APPLY_ADM.SET_DML_HANDLER(
object_name => 'hr.employees',
object_type => 'TABLE',
operation_name => 'DELETE',
error_handler => false,
user_procedure => 'strmadmin.emp_dml_handler',
apply_database_link => NULL,
apply_name => NULL);
END;
/
Create the apply rule. This tells streams, yet again, that we in fact do want to capture changes. The second calls tells streams
where to put the info. Change the source_database_name to match your database.
DECLARE
emp_rule_name_dml VARCHAR2(30);
emp_rule_name_ddl VARCHAR2(30);
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'hr.employees',
streams_type => 'apply',
streams_name => 'apply_emp',
queue_name => 'strmadmin.streams_queue',
include_dml => true,
include_ddl => false,
source_database => 'ORCL',
dml_rule_name => emp_rule_name_dml,
ddl_rule_name => emp_rule_name_ddl);
DBMS_APPLY_ADM.SET_ENQUEUE_DESTINATION(
rule_name => emp_rule_name_dml,
destination_queue_name => 'strmadmin.streams_queue');
END;
/
We don't want to stop applying changes when there is an error, so:
www.odtug.com 4 ODTUG Kaleidoscope 2007
Step-By-Step Streams Cunningham
BEGIN
DBMS_APPLY_ADM.SET_PARAMETER(
apply_name => 'apply_emp',
parameter => 'disable_on_error',
value => 'n');
END;
/
Turn on the apply process:
BEGIN
DBMS_APPLY_ADM.START_APPLY(
apply_name => 'apply_emp');
END;
/
Turn on the capture process:
BEGIN
DBMS_CAPTURE_ADM.START_CAPTURE(
capture_name => 'capture_emp');
END;
/
Connect as HR and make some changes to Employees.
sqlplus hr/hr
INSERT INTO hr.employees VALUES(207, 'JOHN', 'SMITH',
'JSMITH@MYCOMPANY.COM',
NULL, '07-JUN-94', 'AC_ACCOUNT', 777, NULL, NULL, 110);
COMMIT;
UPDATE hr.employees SET salary=5999 WHERE employee_id=206;
COMMIT;
DELETE FROM hr.employees WHERE employee_id=207;
COMMIT;

It takes a few seconds for the data to make it to the logs and then back into the system to be applied. Run this query until you
see data (remembering that it is not instantaneous):

SELECT employee_id, first_name, last_name, upd_Date, action
FROM hr.employee_audit
ORDER BY employee_id;

Any Help where I can start debugging.

I cheched status in dba_capture and dba_apply and it is enabled
there are no rows selected in dba_propagation.
But i was thinking it is due to replicaiton in same database.

thanks
Mehtab





Re: REPLICATION IN SAME DATABASE.... [message #322204 is a reply to message #320900] Thu, 22 May 2008 15:06 Go to previous messageGo to next message
sonumalhi
Messages: 62
Registered: April 2008
Member

All

I have implemented streams on same database from above Doc and it seems to be working fine.

But i have found strange thing here
If I perform one dml operation on emp table and commit it inserts that row in emp_audit table.

If I perform 2 or more DML operations on table and still it insert only 1 row in the audit table not all.

Any ideas why it is happening.

thanks
Mehtab
Re: REPLICATION IN SAME DATABASE.... [message #430115 is a reply to message #322204] Sun, 08 November 2009 14:59 Go to previous messageGo to next message
rakeshgupta_7@yahoo.com
Messages: 1
Registered: November 2009
Junior Member
Mehatb,

What did you do to make it work. It's not working for me.

Rakesh
Re: REPLICATION IN SAME DATABASE.... [message #486424 is a reply to message #322204] Wed, 15 December 2010 08:50 Go to previous message
tapaskmanna
Messages: 98
Registered: January 2007
Location: Cyprus,Nicosia
Member
Hi Mehtab,

Please share the document or steps which you have executed sucessfully for replication on same database.
Previous Topic: DML handler
Next Topic: QMNC problem
Goto Forum:
  


Current Time: Tue Oct 21 12:02:50 CDT 2014

Total time taken to generate the page: 0.10265 seconds