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 [EMAIL PROTECTED];
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 [EMAIL PROTECTED];
/*Connecting to site 'source_db.WORLD' as user 'REPADMIN'...*/
connect [EMAIL PROTECTED];
/* 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 [EMAIL PROTECTED];
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 [EMAIL PROTECTED];
/*Connecting to site 'target_db.WORLD' as user 'REPADMIN'...*/
connect [EMAIL PROTECTED];
/* 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 [EMAIL PROTECTED];
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
