Replication script

From: Yechiel Adar <adar76_at_inter.net.il>
Date: Tue, 17 Sep 2002 11:21:03 -0800
Message-ID: <F001.004D1A5D.20020917112103@fatcity.com>

Hello

One of you guy asked for a simple replication script.

Here is a script that build synchronous replication (two phase commit). The first script does all the ground work: build users, dblinks, admin jobs etc..
The second scripts build a file with commands to activate replication for all tables in the schema.

No guarantee. Works fine for me. Use as you wish but please keep to copyright notice.

First script:
/* Copyright: Yechiel Adar, Mehish computer services */

/* THIS SCRIPTS ASSUMES THAT THE TARGET DB IS FULL.
DO AN EXPORT AND IMPORT TO TARGET DB. TO AVOID CONSTRAINT ERROR DURING INITIAL COPY. */ @@crerep.sql
SET ECHO ON
connect system/manager_at_source_db.WORLD;
spool mds1.out

/* Verifying Master Definition Site Global Name source_db.WORLD */

select * from global_name;

/* Creating user 'REPADMIN' at site 'source_db.WORLD'... */
DROP USER REPADMIN CASCADE;
create user REPADMIN identified by REPADMIN default tablespace users temporary tablespace temp;

/* Granting admin privileges to 'REPADMIN' at site 'source_db.WORLD'... */

BEGIN
DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA( username => 'REPADMIN');
END;
/

/* If you want to be able to create snapshot logs for */
/* any replicated table, grant COMMENT ANY TABLE and */
/* LOCK ANY TABLE to REPADMIN */

grant comment any table to repadmin;
grant lock any table to repadmin;

/* Creating propagator at site 'source_db.WORLD' */
/* Creating receiver 'REPADMIN' at site 'source_db.WORLD'... */
/* Creating receiver 'REPADMIN' at site 'source_db.WORLD'... */

BEGIN
DBMS_DEFER_SYS.REGISTER_PROPAGATOR(
username => 'REPADMIN');
END;
/

grant execute any procedure to REPADMIN;

/* Creating public link 'target_db.WORLD' at site 'source_db.WORLD'... */
DROP PUBLIC DATABASE LINK "target_db.WORLD"; CREATE PUBLIC DATABASE LINK "target_db.WORLD" USING 'target_db.WORLD';

/* Testing link 'target_db.WORLD' at site 'source_db.WORLD'... */

SELECT * FROM GLOBAL_NAME_at_target_db.WORLD;

/*Connecting to site 'source_db.WORLD' as user 'REPADMIN'...*/

connect repadmin/repadmin_at_source_db.WORLD;

/* Scheduling purge at site 'source_db.WORLD'... */

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

CREATE DATABASE LINK "target_db.WORLD"
CONNECT TO "REPADMIN" IDENTIFIED BY "REPADMIN";
/* Scheduling link 'target_db.WORLD' at site 'source_db.WORLD'... */

BEGIN
DBMS_DEFER_SYS.SCHEDULE_PUSH(

     destination => 'target_db.WORLD',
     interval => '/*1:Hr*/ sysdate + 1/24',
     next_date => SYSDATE,
     stop_on_error => FALSE,
     delay_seconds => 0,
     parallelism => 1);

END;
/

------------------------------------cut-------------------------------------

--




/* RUN THIS PART SECOND AT THE MASTER SITE!!! */




/* Connect as SYSTEM user on Master Definition Site */


connect system/manager_at_target_db.WORLD;
spool ms1.out



/* Verifying Master Definition Site Global Name target_db.WORLD */


select * from global_name;



/* Creating user 'REPADMIN' at site 'target_db.WORLD'... */

DROP user REPADMIN CASCADE;
create user REPADMIN identified by REPADMIN
default tablespace users temporary tablespace temp;



/* Granting admin privileges to 'REPADMIN' at site 'target_db.WORLD'... */


BEGIN
   DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA(
 username => 'REPADMIN');
END;


/




/* If you want to be able to create snapshot logs for */

/* any replicated table, grant COMMENT ANY TABLE and  */

/* LOCK ANY TABLE to REPADMIN                         */


grant comment any table to repadmin;
grant lock any table to repadmin;



/* Creating propagator at site 'target_db.WORLD' */

/* Creating receiver 'REPADMIN' at site 'target_db.WORLD'... */

/* Creating receiver 'REPADMIN' at site 'target_db.WORLD'... */


BEGIN
   DBMS_DEFER_SYS.REGISTER_PROPAGATOR(
     username => 'REPADMIN');
END;


/


grant execute any procedure to REPADMIN;



/* Creating public link 'source_db.WORLD' at site 'target_db.WORLD'... */

DROP PUBLIC DATABASE LINK "source_db.WORLD";
CREATE PUBLIC DATABASE LINK "source_db.WORLD" USING 'source_db.WORLD';



/* Testing link 'source_db.WORLD' at site 'target_db.WORLD'... */


SELECT * FROM GLOBAL_NAME_at_source_db.WORLD;



/*Connecting to site 'target_db.WORLD' as user 'REPADMIN'...*/


connect repadmin/repadmin_at_target_db.WORLD;



/* Scheduling purge at site 'target_db.WORLD'... */


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


/

DROP DATABASE LINK "source_db.WORLD";
CREATE DATABASE LINK "source_db.WORLD"
CONNECT TO "REPADMIN" IDENTIFIED BY "REPADMIN";



/* Scheduling link 'source_db.WORLD' at site 'target_db.WORLD'... */


BEGIN
   DBMS_DEFER_SYS.SCHEDULE_PUSH(
     destination => 'source_db.WORLD',
     interval => '/*1:Hr*/ sysdate + 1/24',
     next_date => SYSDATE,
     stop_on_error => FALSE,
     delay_seconds => 0,
     parallelism => 1);
END;


/


spool off
---------------------------------------cut----------------------------------
--




/* RUN THESE STEPS THIRD AT THE MASTER DEFINITION SITE!!! */




/* Connect as REPADMIN user on Master Definition Site... */


connect repadmin/repadmin_at_source_db.world;

spool mds2.out



/* Create master replication group 'SCOTT'... */


BEGIN
   DBMS_REPCAT.CREATE_MASTER_REPGROUP(
     gname => '"TEST_REP_HH"',
     qualifier => '',
     group_comment => '');
END;


/




/*  Add master database 'target_db.world' to master group 'SCOTT' */


BEGIN
   DBMS_REPCAT.ADD_MASTER_DATABASE(
     gname => '"TEST_REP_HH"',
     master => 'target_db.WORLD',
     use_existing_objects => TRUE, /* because we pre-created the tables */
     copy_rows => FALSE,            /* because we pre-populated the tables
*/
     propagation_mode => 'SYNCHRONOUS');
END;


/

/* APPLY ALL ADMIN REQUESTS IN target_db AND THEN IN HH-TEST

   BEFORE CONTINUING THE SCRIPT.   */



/*  Add DEPT table to master group 'SCOTT' */




/*  All tables involved in replication should have a PK  */

/*  or you have to use DBMS_REPCAT.SET_COLUMNS to setup  */

/*  an alternate key                                     */

/* Generate replication support for table 'DEPT' and 'EMP' */


@@genrep.sql

spool off



/* **************************************************************  */

/*  Resume replication on group 'SCOTT' from QUIESCED to NORMAL    */



BEGIN
DBMS_REPCAT.RESUME_MASTER_ACTIVITY(
gname => '"TEST_REP_HH"');
END;


/


Second script: CREREP.SQL



/* Copyright: Yechiel Adar, Mehish computer services  */



set linesize 200
set heading off
set termout off
set pagesize 0
set feedback off

spool genrep.sql

SELECT 'EXECUTE DBMS_REPCAT.CREATE_MASTER_REPOBJECT(oname => ''"'||
TABLE_NAME ||
'"'' , gname => ''"TEST_REP_HH"'', type => ''TABLE'', sname => ''"HH"'',
copy_rows => TRUE, use_existing_object => TRUE);'
FROM ALL_TABLES
WHERE OWNER = 'HH';

select 'EXECUTE DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT(oname => ''"' ||
TABLE_NAME ||
'"'' ,   type => ''TABLE'', sname => ''"HH"'', min_communication => TRUE); '
from all_tables
WHERE OWNER = 'HH';

SPOOL OFF



Yechiel Adar
Mehish

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Yechiel Adar
  INET: adar76_at_inter.net.il

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Tue Sep 17 2002 - 14:21:03 CDT