Setting up Replication Environment

Hi,


I am trying to setup Master and Snapshot sites for replication of certain tables. I am using a updateable snapshot option so that the changes in the Snapshot are also reflected back in the master. However, while the Snapshot is getting refreshed as expected (fast refresh is being used), the changes in the snapshot are getting lost and not being reflected in the master.

Any ideas on why this could be happening ?

Thanks,
Sanjiv

check if changes go into logs on snapshot site (named USLOG$_Snapshot_Name).

male sure snapshot is part of snapshot group

try to call DBMS_DEFER_SYS.PUSH to push updates from upd. snapshot manually, see what happens: any errors, etc.

HI, Sanjiv & LND,

I need your both big help. I try to setup a snapshot site. it doesnot work, I don't know what I did wrong, could you please kindly spare sometime to help me out.

Here is what I did:

Master site: instance DBU2, schema TST
1. create rep adminstrator with privileges:
create user repadmin identified by repadmin default tablespace system temporary tablespace temp;
grant connect, resource to repadmin;
execute dbms_repcat_admin.grant_admin_any_schema('repadmin');
grant comment any table to repadmin;
grant lock any table to repadmin;
execute dbms_defer_sys.register_propagator('repadmin');
grant execute any procedure to repadmin;
2. create snapshot adminstrator with privileges
create user SNAPADMIN_dbu4 identified by SNAPADMIN;
execute dbms_repcat_admin.grant_snapadmin_proxy(username=>'SNAPADMIN_dbu4');
grant alter session to SNAPADMIN_dbu4;
: :
3. create snapshot log and master group
create snapshot log on SUBSCRIBER with primary key;
create snapshot log on APPLICATION with primary key;
create snapshot log on APP_ACTION with primary key;
: :
: :
connect repadmin/repadmin@DBU2
begin
dbms_repcat.create_master_repgroup(
gname=>'TSTGRP', qualifier=>'', group_comment=>'');
end;
/

SNAPSHOT site: instance DBU4
4. create snapshot adminstrator with privileges:
create user SNAPADMIN identified by SNAPADMIN;
execute dbms_repcat_admin.grant_admin_any_schema(username=>'SNAPADMIN');
grant comment any table to SNAPADMIN;
grant lock any table to SNAPADMIN;
execute dbms_defer_sys.register_propagator(username => 'SNAPADMIN');

grant alter session to tst;
grant create cluster to tst;
: :
: :
connect SNAPADMIN/SNAPADMIN@DBU4

begin
dbms_defer_sys.schedule_push(
destination => 'DBU2.WORLD',
interval => '/*30:Secs*/ sysdate + 30/(60*60*24)',
next_date => sysdate, stop_on_error => FALSE,
delay_seconds => 0, parallelism => 1);
end;
/
5. create link
create database link "dbu2.WORLD" connect to "SNAPADMIN_dbu4" identified by "SNAPADMIN";

begin
dbms_defer_sys.schedule_purge(
next_date => sysdate,
interval => '/*30:Secs*/ sysdate + 30/(60*60*24)',
delay_seconds => 0, rollback_segment => '');
end;
/
6. create a refresh group
begin
dbms_refresh.make(name => 'SNAPADMIN.TSTGRP',
list => '', next_date => SYSDATE,
interval => '/*30:Secs*/ sysdate + 30/(60*60*24)',
implicit_destroy => FALSE, rollback_seg => '',
push_deferred_rpc => TRUE, refresh_after_errors => FALSE);
end;
/
7. create snapshot group
begin
dbms_repcat.create_snapshot_repgroup(
gname => 'TSTGRP',
master => 'DBU2.WORLD',
propagation_mode => 'ASYNCHRONOUS');
end;
/

===> GET ERROR

ERROR at line 1:
ORA-23313: object group TSTGRP is not mastered at DBU2.WORLD
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.DBMS_REPCAT_SNA_UTL", line 1057
ORA-06512: at "SYS.DBMS_REPCAT_SNA", line 55
ORA-06512: at "SYS.DBMS_REPCAT", line 1029
ORA-06512: at line 2

Question:
1. any step I missing
2. I create group on master site why it does not work
3. I have 3 more steps:
create snapshot for each table
add snapshot to refresh group: dbms_refresh.add(name => 'SNAPADMIN.TSTGRP', list => 'TST.TABLE', lax => TRUE);
add table to snap group: dbms_repcat.create_snapshot_repobject

does this will cover the whole process ?

thanks in advance

Jim

1. create database link "dbu2.WORLD" is diff. from
master => 'DBU2.WORLD', in step 7.


2. does the group really exists in master site (in replication catalog views?)

3. Did you checked/tried:

Action: If the given group name was correct, connect to a current master and
retry the request, make the invocation database a master with
ADD_MASTER_DATABASE, or use SWITCH_SNAPSHOT_MASTER if the
invocation database is a snapshot site.

LND, thanks for your reply,

1. I recreate link use upper case,
2. SQL> select sname schema, master, status from dba_repcat;

SCHEMA MASTER STATUS
---------- ------------ --------------
TSTGRP Y QUIESCED

3. from master site:
begin
2 dbms_repcat.add_master_database(
3 gname=>'TSTGRP',
4 master=>'DBU2.WORLD',
5 propagation_mode=>'asynchronous');
6 end;
7 /
begin
*
ERROR at line 1:
ORA-23374: object group TSTGRP already exists
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_REPCAT_MAS", line 1591
ORA-06512: at "SYS.DBMS_REPCAT", line 113
ORA-06512: at line 2

4. from snapshot site:
SQL> begin
2 dbms_repcat.switch_snapshot_master(
3 gname=>'TSTGRP',
4 master=>'DBU2.WORLD');
5 end;
6 /
begin
*
ERROR at line 1:
ORA-23373: object group TSTGRP does not exist
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_REPCAT_SNA_UTL", line 5188
ORA-06512: at "SYS.DBMS_REPCAT_SNA", line 206
ORA-06512: at "SYS.DBMS_REPCAT", line 1042
ORA-06512: at line 2

seems snapshot site can not TALK to master site. anyway to check/verify it?

LND,

I think the link cause the problem, how many link I need to create and who is the ower?

1. snapadmin need link (for create group)
create database link DBU2.WORLD connect to snapadmin_dbu4 identified by snapadmin using DBU2;

2. TST need a link (for creating snapshot for each table)
create database link DBU2.WORLD connect to tst identified by password using DBU2;

am I right?

yes, at least I always set link from the snapshot schema to master schema.
(Otherwise you need either public link (this quite unsecure), or do hard work with synonyms on table@db link in another schema (I even never tried it and probaly it will not work))

check for created links and their names in dba_db_links.
check all relevant links too: select * from [email protected]

LND, thanks for your help, I create snapshot log, sanpshot, refresh group,...it seems the snapshot is ready.

BUT:

when I try to update/insert some row from master site, I don't see it sent to snapshot site. for testing, I set up all interval to ver 6 secs. what I missed?

master site:
repadmin (interval 6 sec)
dbms_defer_sys.schedule_purge
snap**** site:
snapadmin (all 3 interval are 6 sec)
dbms_defer_sys.schedule_push
dbms_defer_sys.schedule_purge
dbms_refresh.make
init.ora
job_queue_interval=10
job_queue_processes=2

I problem is if I issue dbms_refresh.refresh('group') to force refresh snapshot site. it works. but why auto schedule not work?

I keep repeating: for me and others job_queue_processes=8 helps.

Look into alert logs to see what happens too.

you are good, I never thought I should check alert file
here are tons of error in alert and 2 trace file:

ORA-12012: error on auto execute of job 9
ORA-02394: exceeded session limit on IO usage, you are being logged off
ORA-06512: at "SYS.DBMS_DEFER_SYS", line 2883
ORA-06512: at line 1
*** SESSION ID:(9.37413) 2001.02.02.14.42.50.000
*** 2001.02.02.14.42.50.000
ORA-12012: error on auto execute of job 10
ORA-02394: exceeded session limit on IO usage, you are being logged off
ORA-06512: at "SYS.DBMS_DEFER_SYS", line 2883

looks like I have problem for IO usage, how to solve it?
I am on SCO unixware 7.1, oracle 8.0.5

Jim

maybe look into profiles (in SQL manual, create profile).
view is DBA_PROFILE
then what user has whar profile too.

SQL> select * from user_resource_limits;

RESOURCE_NAME LIMIT
-------------------------------- ----------------------------------------
COMPOSITE_LIMIT UNLIMITED
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
IDLE_TIME UNLIMITED
CONNECT_TIME UNLIMITED
PRIVATE_SGA UNLIMITED

I just find out, this is known bug ora-02394 for 8.0.4 and fixed on 8.0.6 and 8.0.5.2 (this is our version)

I guess there is no solution!

does it make sense to limit to some high value?

LND,

believe or not, it works. what I did is bounce DB once.
I tried few times, after setting up snapshot site, before
shutdown instance, I can see the job is doing nothing
(from dba_jobs). after bounce back, it refresh every
6 seconds!!! any idea?

Derek

I will try to remember this idea: reboot, reboot even if not asked.