Re: ** how to kill a replication request

From: mkb <mkb125_at_yahoo.com>
Date: Wed, 9 Mar 2005 08:54:41 -0800 (PST)
Message-ID: <20050309165441.29565.qmail@web41303.mail.yahoo.com>

> scripts and steps to rebuild replication from 
> existing setup starting from fresh backup/restore 
> from primary to secondary.

No, but you could modify the script below. It creates a simple MMR setup using the HR schema. Most of it has simply been cut-n-pasted from the Oracle 9i Replication docs but I have used this as a template to setup a couple of replication environments. Also, make sure that every table that you replicate has a PK or UK index otherwise you eventually end with ORA-xxxx errors (I forget which xxxx specifically).

chapter 2
Setup SID01
step 1 conn system/<PWD>@SID01
step 2 CREATE USER repadmin IDENTIFIED BY repadmin;
step 3 BEGIN DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA ( username => 'repadmin'); END; /

GRANT COMMENT ANY TABLE TO repadmin;
GRANT LOCK ANY TABLE TO repadmin;
GRANT SELECT ANY DICTIONARY TO repadmin;

step 4 BEGIN DBMS_DEFER_SYS.REGISTER_PROPAGATOR ( username => 'repadmin'); END; /
step 5 BEGIN DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP ( username => 'repadmin', privilege_type => 'receiver', list_of_gnames => NULL); END; /
step 6 CONNECT repadmin/repadmin_at_SID01 BEGIN DBMS_DEFER_SYS.SCHEDULE_PURGE ( next_date => SYSDATE, interval => 'SYSDATE + 1/24', delay_seconds => 0); END; /
step 7 CONNECT SYSTEM/<PWD>@SID01 CREATE USER proxy_mviewadmin IDENTIFIED BY proxy_mviewadmin;

BEGIN
DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP ( username => 'proxy_mviewadmin',
privilege_type => 'proxy_snapadmin',
list_of_gnames => NULL);
END;
/

GRANT SELECT_CATALOG_ROLE TO proxy_mviewadmin;

CREATE USER proxy_refresher IDENTIFIED BY proxy_refresher;
GRANT CREATE SESSION TO proxy_refresher; GRANT SELECT ANY TABLE TO proxy_refresher;

Setup SID02
step 1 conn system/<PWD>@SID02
step 2 CREATE USER repadmin IDENTIFIED BY repadmin;
step 3 BEGIN DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA ( username => 'repadmin'); END; /

GRANT COMMENT ANY TABLE TO repadmin;
GRANT LOCK ANY TABLE TO repadmin;
GRANT SELECT ANY DICTIONARY TO repadmin;

step 4 BEGIN DBMS_DEFER_SYS.REGISTER_PROPAGATOR ( username => 'repadmin'); END; /
step 5 BEGIN DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP ( username => 'repadmin', privilege_type => 'receiver', list_of_gnames => NULL); END; /
step 6 CONNECT repadmin/repadmin_at_SID02

BEGIN
DBMS_DEFER_SYS.SCHEDULE_PURGE (
next_date => SYSDATE,
interval => 'SYSDATE + 1/24',
delay_seconds => 0);
END;
/

step 7 CONNECT SYSTEM/<PWD>@SID02 CREATE USER proxy_mviewadmin IDENTIFIED BY proxy_mviewadmin;

BEGIN
DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP ( username => 'proxy_mviewadmin',
privilege_type => 'proxy_snapadmin',
list_of_gnames => NULL);
END;
/

GRANT SELECT_CATALOG_ROLE TO proxy_mviewadmin;

CREATE USER proxy_refresher IDENTIFIED BY proxy_refresher;
GRANT CREATE SESSION TO proxy_refresher; GRANT SELECT ANY TABLE TO proxy_refresher;

Create database links
Step 1 CONNECT SYSTEM/<PWD>@SID01 CREATE PUBLIC DATABASE LINK SID02.your_domain.com USING 'SID02.your_domain.com';

CONNECT repadmin/repadmin_at_SID01
CREATE DATABASE LINK SID02.your_domain.com CONNECT TO repadmin
IDENTIFIED BY repadmin;

CONNECT SYSTEM/<PWD>@SID02
CREATE PUBLIC DATABASE LINK SID01.your_domain.com USING 'SID01.your_domain.com';

CONNECT repadmin/repadmin_at_SID02
CREATE DATABASE LINK SID01.your_domain.com CONNECT TO repadmin
IDENTIFIED BY repadmin;

Step 2 CONNECT repadmin/repadmin_at_SID01

BEGIN
DBMS_DEFER_SYS.SCHEDULE_PUSH (
destination => 'SID02.your_domain.com',
interval => 'SYSDATE + (1/144)',
next_date => SYSDATE,
parallelism => 1,
execution_seconds => 1500,
delay_seconds => 1200);
END;
/

CONNECT repadmin/repadmin_at_SID02

BEGIN
DBMS_DEFER_SYS.SCHEDULE_PUSH (
destination => 'SID01.your_domain.com',
interval => 'SYSDATE + (1/144)',
next_date => SYSDATE,
parallelism => 1,
execution_seconds => 1500,
delay_seconds => 1200);
END;
/

chapter 3
step 1
create hr schema at master sites
step 2
create master group CONNECT repadmin/repadmin_at_SID01

BEGIN
DBMS_REPCAT.CREATE_MASTER_REPGROUP (
gname => 'hr_repg');
END;
/

--BEGIN
--DBMS_REPCAT.DROP_MASTER_REPGROUP (
--gname => 'hr_repg',
--drop_contents => false,
--all_sites => true);
--END;
--/

step 3
add objects to master group

BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => 'hr_repg',
type => 'TABLE',
oname => 'countries',
sname => 'hr',
use_existing_object => TRUE,
copy_rows => FALSE);
END;
/

BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => 'hr_repg',
type => 'TABLE',
oname => 'departments',
sname => 'hr',
use_existing_object => TRUE,
copy_rows => FALSE);
END;
/

BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => 'hr_repg',
type => 'TABLE',
oname => 'employees',
sname => 'hr',
use_existing_object => TRUE,
copy_rows => FALSE);
END;
/

BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => 'hr_repg',
type => 'TABLE',
oname => 'jobs',
sname => 'hr',
use_existing_object => TRUE,
copy_rows => FALSE);
END;
/

BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => 'hr_repg',
type => 'TABLE',
oname => 'job_history',
sname => 'hr',
use_existing_object => TRUE,
copy_rows => FALSE);
END;
/

BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => 'hr_repg',
type => 'TABLE',
oname => 'locations',
sname => 'hr',
use_existing_object => TRUE,
copy_rows => FALSE);
END;
/

BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => 'hr_repg',
type => 'TABLE',
oname => 'regions',
sname => 'hr',
use_existing_object => TRUE,
copy_rows => FALSE);
END;
/

BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => 'hr_repg',
type => 'INDEX',
oname => 'dept_location_ix',
sname => 'hr',
use_existing_object => TRUE,
copy_rows => FALSE);
END;
/

BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => 'hr_repg',
type => 'INDEX',
oname => 'emp_department_ix',
sname => 'hr',
use_existing_object => TRUE,
copy_rows => FALSE);
END;
/

BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => 'hr_repg',
type => 'INDEX',
oname => 'emp_job_ix',
sname => 'hr',
use_existing_object => TRUE,
copy_rows => FALSE);
END;
/

BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => 'hr_repg',
type => 'INDEX',
oname => 'emp_manager_ix',
sname => 'hr',
use_existing_object => TRUE,
copy_rows => FALSE);
END;
/

BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => 'hr_repg',
type => 'INDEX',
oname => 'jhist_department_ix',
sname => 'hr',
use_existing_object => TRUE,
copy_rows => FALSE);
END;
/

BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => 'hr_repg',
type => 'INDEX',
oname => 'jhist_employee_ix',
sname => 'hr',
use_existing_object => TRUE,
copy_rows => FALSE);
END;
/

BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => 'hr_repg',
type => 'INDEX',
oname => 'jhist_job_ix',
sname => 'hr',
use_existing_object => TRUE,
copy_rows => FALSE);
END;
/

BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => 'hr_repg',
type => 'INDEX',
oname => 'loc_country_ix',
sname => 'hr',
use_existing_object => TRUE,
copy_rows => FALSE);
END;
/

step 4
add additional master sites

BEGIN
DBMS_REPCAT.ADD_MASTER_DATABASE (
gname => 'hr_repg',
master => 'SID02.your_domain.com',
use_existing_objects => TRUE,
copy_rows => FALSE,
propagation_mode => 'ASYNCHRONOUS');
END;
/

wait until SID02.your_domain.com appears SELECT DBLINK FROM DBA_REPSITES WHERE GNAME = 'HR_REPG';
step 5
if conflicts are possible, then configure conflict resolution
See the docs
step 6
generate replication support

BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT ( sname => 'hr',
oname => 'countries',
type => 'TABLE',
min_communication => TRUE);
END;
/

BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT ( sname => 'hr',
oname => 'departments',
type => 'TABLE',
min_communication => TRUE);
END;
/

BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT ( sname => 'hr',
oname => 'employees',
type => 'TABLE',
min_communication => TRUE);
END;
/

BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT ( sname => 'hr',
oname => 'jobs',
type => 'TABLE',
min_communication => TRUE);
END;
/

BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT ( sname => 'hr',
oname => 'job_history',
type => 'TABLE',
min_communication => TRUE);
END;
/

BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT ( sname => 'hr',
oname => 'locations',
type => 'TABLE',
min_communication => TRUE);
END;
/

BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT ( sname => 'hr',
oname => 'regions',
type => 'TABLE',
min_communication => TRUE);
END;
/

wait until the following is empty SELECT COUNT(*) FROM DBA_REPCATLOG WHERE GNAME = 'HR_REPG';
and your off to the races....

Celebrate Yahoo!'s 10th Birthday!
Yahoo! Netrospective: 100 Moments of the Web http://birthday.yahoo.com/netrospective/
--

http://www.freelists.org/webpage/oracle-l Received on Wed Mar 09 2005 - 11:58:09 CST