Home » Server Options » Streams & AQ » One database( two schemas) streams Configuration (Oracle 10g rel 2,Windows Server 2003)
One database( two schemas) streams Configuration [message #342926] Mon, 25 August 2008 10:02 Go to next message
mirasraf
Messages: 48
Registered: June 2006
Location: DHAKA
Member

Dear All

Data does not replicate.
Data will replicate from one schema to another schema in Same database.
I have one database( Global database name "orcl.world" and instance name "orcl"). I have also two schema scott and scott2.
Both schema carrry 1 table(dept).

Step:
1. First I create two tns in database "orcl"
orcl=for schema scott
orcl2=for schema scott2

2.Second I create streams administrator

Conn sys/******@orcl as sysdba
create user strmadmin identified by ****** ;
grant dba,select_catalog_role to strmadmin;
exec dbms_streams_auth.grant_admin_privilege('strmadmin',true);

3.I create db link
conn strmadmin/******@orcl
create database link orcl2.world connect to strmadmin identified by strmadmin using 'orcl2';

4.I create Queue (Apply & Capture )

connect strmadmin/strmadmin@orclbegin
dbms_streams_adm.set_up_queue(
queue_table => 'apply_scotttab',
queue_name => 'apply_scott',
queue_user => 'strmadmin');
end;
/
begin
dbms_streams_adm.set_up_queue(
queue_table => 'capture_scotttab',
queue_name => 'capture_scott',
queue_user => 'strmadmin');
end;
/

5.Create Capture & Apply process

connect strmadmin/strmadmin@orcl
Begin
dbms_streams_adm.add_schema_rules (
schema_name => 'scott',
streams_type => 'capture',
streams_name => 'capture_stm',
queue_name => 'capture_scott',
include_dml => true,
include_ddl => false,
inclusion_rule => true);
end;
/
connect strmadmin/strmadmin@orcl
begin
dbms_streams_adm.add_schema_rules (
schema_name => 'scott2',
streams_type => 'apply',
streams_name => 'apply_sfdb_stm',
queue_name => 'apply_scott',
include_dml => true,
include_ddl => false,
source_database => 'orcl');
end;
/

6.Create Propagation process

conn strmadmin/strmadmin@orcl
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name =>'scott.dept',
streams_name =>'propa_cdb_sfdb',
source_queue_name =>'capture_scott',
destination_queue_name =>'apply_scott@orcl2.world',
include_dml =>true,
include_ddl =>false,
source_database =>'orcl');
END;
/

7. Then I start capture & Apply process

But When I insert one record in dept table in schema scott ,this data did not find schema scott2(dept table).
I did not get any error (dba_apply,dba_propagation ,dba_capture)


Re: One database( two schemas) streams Configuration [message #342969 is a reply to message #342926] Mon, 25 August 2008 12:44 Go to previous messageGo to next message
varunvir
Messages: 329
Registered: November 2007
Senior Member
You have to do instantiation.After that only replication will take place.
Regards,
Varun Punj
Re: One database( two schemas) streams Configuration [message #343149 is a reply to message #342969] Tue, 26 August 2008 08:25 Go to previous messageGo to next message
mirasraf
Messages: 48
Registered: June 2006
Location: DHAKA
Member

Thanks
for reply
I donot know what is the appropriate process to set instantiation.

I worked but do not replicate data
Process:
DECLARE
iscn NUMBER;
BEGIN
iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@orcl2.world
(
source_object_name => 'scott.dept',
source_database_name => 'hcdp',
instantiation_scn => iscn
);
END;
/

How data replicate schema scott to scott2 in same database.

please help me

Regards
Asraf
Re: One database( two schemas) streams Configuration [message #343183 is a reply to message #343149] Tue, 26 August 2008 09:55 Go to previous messageGo to next message
varunvir
Messages: 329
Registered: November 2007
Senior Member
Have you created logminer tablespace(logmnt_tbs).If you already created logminer tablespace,then Please tell me the value of following instance level parameter as:-
JOB_QUEUE_PROCESSES
UNDO_RETENTION
GLOBAL_NAMES
_JOB_QUEUE_INTERVAL
STREAMS_POOL_SIZE
PARALLEL_MAX_SERVERS
Re: One database( two schemas) streams Configuration [message #343186 is a reply to message #342926] Tue, 26 August 2008 10:05 Go to previous messageGo to next message
varunvir
Messages: 329
Registered: November 2007
Senior Member
Start the capture,propagation and apply process after instantiation.
Use the following packages inorder to do this:
dbms_capture_adm
dbms_propagation_adm
dbms_apply_adm

Regards,
Varun Punj
Re: One database( two schemas) streams Configuration [message #343207 is a reply to message #342926] Tue, 26 August 2008 11:26 Go to previous messageGo to next message
varunvir
Messages: 329
Registered: November 2007
Senior Member
I think you already started capture,propagation and apply process.Please recreate the propagation process with follwing changes:
source_queue_name =>'strmadmin.capture_scott',
destination_queue_name =>'strmadmin.apply_scott@orcl2.world',

Let me now if started working??

Regards,
Varun Punj,
Re: One database( two schemas) streams Configuration [message #343602 is a reply to message #343207] Wed, 27 August 2008 11:07 Go to previous messageGo to next message
mirasraf
Messages: 48
Registered: June 2006
Location: DHAKA
Member

Thanks
for Reply

I recreate all process and also run script schema instantiation.
But data did not replicate target schema.
Process:
Begin
dbms_capture_adm.prepare_schema_instantiation
(
schema_name=>'SCOTT'
);
End;
-----
In database I named global database "orcl.world" and instance name "orcl". And tns (orcl for scott and orcl2 for scott2 schema same database)

conn strmadmin/******@orcl
create database link orcl2.world connect to strmadmin identified by strmadmin using 'orcl2';

When I connect
conn strmadmin/******@orcl
and select count(*) from scott2.dept@orcl2.world
an Error message occure "ORA-02085: database link ORCL2.WORLD connects to ORCL.WORLD"

conn sys as .....
SQL> show parameter global_names

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
global_names boolean TRUE
SQL> create database link orcl connect to strmadmin identified by strmadmin using 'orcl2';
create database link orcl connect to strmadmin identified by strmadmin using 'orcl2'
*
ERROR at line 1:
ORA-02082: a loopback database link must have a connection qualifier
SQL> create database link scott2.world connect to strmadmin identified by strmadmin using 'orcl2';

Database link created.

SQL> select count(*) from dept@scott2.world;
select count(*) from dept@scott2.world
*
ERROR at line 1:
ORA-02085: database link SCOTT2.WORLD connects to ORCL.WORLD

SQL> create database link orcl.world connect to strmadmin identified by strmadmin using 'orcl2';
create database link orcl.world connect to strmadmin identified by strmadmin using 'orcl2'
*
ERROR at line 1:
ORA-02082: a loopback database link must have a connection qualifier


SQL> create database link db10.orcl.world connect to strmadmin identified by strmadmin using 'orcl2';

Database link created.

SQL> select count(*) from dept@db10.orcl.world;
select count(*) from dept@db10.orcl.world
*
ERROR at line 1:
ORA-02085: database link DB10.orcl.WORLD connects to orcl.WORLD



I could not understand why db link did not work and Why data did not replicate from one schema to another schema in same database.

please any coding error
Regards
Asraf




Re: One database( two schemas) streams Configuration [message #343861 is a reply to message #343602] Thu, 28 August 2008 06:33 Go to previous messageGo to next message
shreeshsikaria
Messages: 3
Registered: August 2008
Location: India
Junior Member
1. First I create two tns in database "orcl"
orcl=for schema scott
orcl2=for schema scott2


What exactly do you mean by the above ? can you elaborate ...
Re: One database( two schemas) streams Configuration [message #343878 is a reply to message #343861] Thu, 28 August 2008 06:57 Go to previous messageGo to next message
shreeshsikaria
Messages: 3
Registered: August 2008
Location: India
Junior Member
Also,
how did you create the db links ??

Assuming your database name is sampledb.machine.com
schema1 is orcl
schema2 is orcl2

as per the statement below , orcl should be the name of your database , not the schema. "create database link orcl2.world connect to strmadmin identified by strmadmin using 'orcl2'; "
Re: One database( two schemas) streams Configuration [message #343916 is a reply to message #343878] Thu, 28 August 2008 07:57 Go to previous message
mirasraf
Messages: 48
Registered: June 2006
Location: DHAKA
Member

Thanks for percipation
how did you create the db links ??

db link name =orcl2.world

Assuming your database name is sampledb.machine.com

Global database Name=orcl.world
and instance name =orcl





Regards

Asraf
Previous Topic: 1 Table in 2 different Schemas on Single Database
Next Topic: Stream is not working... Need help.
Goto Forum:
  


Current Time: Sat Aug 30 19:45:51 CDT 2014

Total time taken to generate the page: 0.13196 seconds