Re: ** how to kill a replication request

> 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@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@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@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@SID02
CREATE DATABASE LINK SID01.your_domain.com CONNECT TO
repadmin 
IDENTIFIED BY repadmin;

-- Step 2
CONNECT repadmin/repadmin@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@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@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

Other related posts: