Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Multi-Master Replicatoin

Re: Multi-Master Replicatoin

From: Laurie Heigl <heigl_at_buyitnow.com>
Date: Fri, 03 Sep 1999 14:35:05 -0600
Message-ID: <37D030F6.CEE346FA@buyitnow.com>


tmurrayiii_at_aol.com wrote:

> Hi,
>
> I am looking for some documentation on how to setup multi-master
> replication. I have read the online documentation, which instructs one
> to use the replication manager. Unfortunately, the replication manager
> wizard does not work.
>
> Tom.
>

The replication manager only works if you know where to look for errors. Here
are some scripts that can be modified, look for errors after each step.

rticle-ID:         <PR:1020279.6>
Circulation:        PUBLISHED (EXTERNAL)
Platform:           GENERIC
Folder:             server.Rdbms.Distributed.Replication
Area:               RDBMS
Subject:            TFTS: SCRIPT TO CREATE A BASIC REPLICATED
ENVIRONMENT
Author:             MRMORRIS.US
Last-Updated:       07-AUG-96
Component:          RDBMS_V07.XX


Circulation:            ** Available to Customers **
Script Creator:         RDBMS Analysts
Topic:                  ** Tales from the Scrypt **
Subject:                TFTS: CREATE A BASIC REPLICATED ENVIRONMENT
Keywords:


Title:

Create a Basic Replicated Environment in Oracle V7.1.6 -->V7.2XXX.



Disclaimer:

This script is provided for educational purposes only. It is NOT supported by
Oracle World Wide Technical Support. The script has been tested and appears
to work as intended. However, you should always test any script before
relying on it.

PROOFREAD THIS SCRIPT PRIOR TO USING IT! Due to differences in the way text
editors, email packages and operating systems handle text formatting (spaces,
tabs and carriage returns), this script may not be in an executable state when

you first receive it. Check over the script to ensure that errors of this
type are corrected.



Abstract:

The following SQL*Plus scripts will create a basic Oracle replicated environment for you. You need to customize them to your particular configuration, names, etc. The first script should be run on the Master
Definition Site, the second one on the Master Site and the third one on the
Master Definition Site.



Requirements:

Needs to be run as SYS



Script:

SET ECHO off
REM NAME: TFSSYMR1.SQL
REM USAGE:"@path/tfssymr1"
REM



REM REQUIREMENTS:
REM Needs to be run as SYS
REM

REM AUTHORS:
REM Todd James, Peiman Milani, Richard Jobin, Rowena Serna REM Sue Jang, Pavana Jain and Jo Ann Palchak REM Copyright 1996, Oracle Corporation REM

REM PURPOSE:
REM The following SQL*Plus scripts will create a basic Oracle REM replicated environment for you. You need to customize them to
REM your particular configuration, names, etc. The first script REM should be run on the Master Definition Site, the second one on
REM the Master Site and the third one on the Master Definition Site.
REM
REM

REM EXAMPLE:
REM N/A
REM

REM DISCLAIMER:
REM This script is provided for educational purposes only. It is NOT
REM supported by Oracle World Wide Technical Support. REM The script has been tested and appears to work as intended. REM You should always run new scripts on a test instance initially. REM

REM Main text of script follows:

# Use this as a sample only. Please refer to the Replication document:
# The 'xxxxx' in this script will need to be reviewed and site specific
# values applied.

######################################################################
######################################################################
############                   SCRIPT 1
###########
############     To be run on the Master Definition Site
############
######################################################################

connect sys/xxxxx

set termout on
set feedback on

######################################################################
# This statement is optional and can be defined by the init.ora # parameters db_name and db_domain.
######################################################################

ALTER DATABASE RENAME GLOBAL_NAME TO xxxxx.WORLD;

drop user repsys cascade;
drop user repadmin cascade;
drop user scott cascade;

create user repsys identified by repsys default tablespace xxxxx temporary tablespace temp;

execute dbms_repcat_auth.grant_surrogate_repcat ('repsys');

execute dbms_repcat_admin.grant_admin_any_repschema('repsys');

grant execute on dbms_defer_internal_sys to repsys;

drop public database link xxxxx.WORLD;

drop database link xxxxx.WORLD;

create public database link xxxxx.WORLD using 'xxxxx';

#
# The using clause literal must match the alias in the tnsnames.ora file
#

create database link xxxxx.WORLD connect to repsys identified by repsys;

create user repadmin identified by repadmin default tablespace xxxxx temporary tablespace temp;

grant execute on dbms_repcat to repadmin;
grant execute on dbms_snapshot to repadmin;
grant execute on dbms_repcat_mas to repadmin;
grant execute on dbms_defer to repadmin;
grant execute on dbms_defer_sys to repadmin;
grant execute on dbmsobjgwrapper to repadmin;

execute dbms_repcat_admin.grant_admin_any_repschema('repadmin');

grant unlimited tablespace to repadmin; grant connect to repadmin;
grant dba to repadmin;

##################################################################
# The following grants are optional unless you are running 7.1.6 #
##################################################################

grant create any table,
      create any view,
      create any index,
      create any trigger,
      create any procedure,
      create any synonym,
      create any snapshot to repadmin;

grant alter any table,
      alter any procedure,
    alter any index,
      alter any trigger,
      alter any snapshot to repadmin;

grant drop any table,
      drop any view,
      drop any index,
   drop any trigger,
      drop any procedure,
      drop any synonym,

drop any snapshot to repadmin;
grant select any table,
      insert any table,
      update any table,
      delete any table to repadmin;

connect repadmin/repadmin

create database link xxxxx.world
connect to repadmin identified by repadmin;

@$ORACLE_HOME/rdbms/admin/catdbsyn

connect sys/xxxxx

####################################################
# If the user already exists then there is NO need
# to drop and recreate it.
####################################################

create user scott identified by tiger
default tablespace xxxxx
temporary tablespace temp;

grant connect, resource to scott;
grant execute any procedure to scott;

grant execute on dbms_repcat to scott;
grant execute on dbms_snapshot to scott;
grant execute on dbms_repcat_mas to scott;
grant execute on dbms_defer to scott;
grant execute on dbms_defer_sys to scott;
grant execute on dbmsobjgwrapper to scott;

execute dbms_repcat_admin.grant_admin_any_repschema('scott');

connect scott/tiger

create database link xxxxx.WORLD connect to scott identified by tiger;

###################################################################
# If the user is a new user this is where you could create the
# tables for replication

# @demobld.sql this script builds scott tables.
###################################################################

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



Examples:

N/A



Script:

SET ECHO off
REM NAME: TFSSYMR2.SQL
REM USAGE:"@path/tfssymr2"
REM



REM REQUIREMENTS:
REM Must be run as SYS
REM

REM AUTHOR:
REM Anonymous
REM Copyright 1996, Oracle Corporation REM

REM PURPOSE:
REM Set up a basic replicated environment II REM

REM EXAMPLE:
REM N/A
REM

REM DISCLAIMER:
REM This script is provided for educational purposes only. It is NOT
REM supported by Oracle World Wide Technical Support. REM The script has been tested and appears to work as intended. REM You should always run new scripts on a test instance initially.
REM

REM Main text of script follows:
######################################################################
######################################################################
############                   SCRIPT 2
###########
############           To be run on the Master Site
############
######################################################################


set termout on
set feedback on

connect sys/xxxxx

######################################################################
# This statement is optional and can be defined by the init.ora # parameters db_name and db_domain.
######################################################################

ALTER DATABASE RENAME GLOBAL_NAME TO xxxxx.WORLD;

drop user repsys cascade;
drop user repadmin cascade;
drop user scott cascade;

create user repsys identified by repsys default tablespace xxxxx temporary tablespace temp;

execute dbms_repcat_auth.grant_surrogate_repcat ('repsys');

execute dbms_repcat_admin.grant_admin_any_repschema('repsys');

grant execute on dbms_defer_internal_sys to repsys;

drop public database link xxxxx.WORLD;

drop database link xxxxx.WORLD;

create public database link xxxxx.WORLD using 'xxxxx';

create database link xxxxx.WORLD connect to repsys identified by repsys;

create user repadmin identified by repadmin default tablespace fraadev temporary tablespace temp;

grant execute on dbms_repcat to repadmin;
grant execute on dbms_snapshot to repadmin;
grant execute on dbms_repcat_mas to repadmin;
grant execute on dbms_defer to repadmin;
grant execute on dbms_defer_sys to repadmin;
grant execute on dbmsobjgwrapper to repadmin;

execute dbms_repcat_admin.grant_admin_any_repschema('repadmin');

grant unlimited tablespace to repadmin; grant connect to repadmin;
grant dba to repadmin;

grant create any table,

      create any view,
      create any index,
      create any trigger,
      create any procedure,
      create any synonym,
      create any snapshot to repadmin;

grant alter any table,
      alter any procedure,
      alter any index,
      alter any trigger,
      alter any snapshot to repadmin;

grant drop any table,
      drop any view,
  drop any index,
      drop any trigger,
      drop any procedure,
      drop any synonym,
      drop any snapshot to repadmin;

grant select any table,
      insert any table,
      update any table,
      delete any table to repadmin;

create user scott identified by tiger
default tablespace fraadev
temporary tablespace temp;

grant connect, resource to scott;

grant execute on dbms_repcat to scott;
grant execute on dbms_snapshot to scott;
grant execute on dbms_repcat_mas to scott;
grant execute on dbms_defer to scott;
grant execute on dbms_defer_sys to scott;
grant execute on dbmsobjgwrapper to scott;

execute dbms_repcat_admin.grant_admin_any_repschema('scott');

connect scott/tiger

create database link xxxxx.WORLD connect to scott identified by tiger;


Examples:

N/A



Script:

SET ECHO off
REM NAME: TFSSYMR3.SQL
REM USAGE:"@path/tfssymr3"
REM



REM REQUIREMENTS:
REM Must be run as SYS
REM

REM AUTHOR:
REM Anonymous
REM Copyright 1996, Oracle Corporation REM

REM PURPOSE:
REM Set up a basic replicated environment III. REM

REM EXAMPLE:
REM N/A
REM

REM DISCLAIMER:
REM This script is provided for educational purposes only. It is NOT
REM supported by Oracle World Wide Technical Support. REM The script has been tested and appears to work as intended. REM You should always run new scripts on a test instance initially.
REM

REM Main text of script follows:
######################################################################
######################################################################
############          SCRIPT 3                    ###########
############ To be run on the Master Definition Site ONLY ###########
######################################################################


connect repadmin/repadmin
spool repstart.log

#---------------------------------------------------------------------
# creating replication schema for SCOTT
#---------------------------------------------------------------------


execute dbms_repcat.create_master_repschema ('scott');

execute dbms_repcat.create_master_repobject ('scott','EMP','TABLE'); execute dbms_repcat.create_master_repobject ('scott','DEPT','TABLE');
execute dbms_repcat.create_master_repobject ('scott','BONUS','TABLE');
execute dbms_repcat.create_master_repobject ('scott','SALGRADE','TABLE');

rem--------------------------------------------------------------------
rem File:     altertab.sqldd the constraints required for
replication
rem--------------------------------------------------------------------

alter table scott.emp add (constraint EMP_EMPNO_PK PRIMARY KEY (EMPNO));
alter table scott.dept add(constraint DEPT_DEPTNO_PK PRIMARY KEY (DEPTNO));
alter table scott.emp add (constraint EMP_DEPTNO_FK FOREIGN KEY (DEPTNO) references scott.DEPT(DEPTNO)); alter table scott.bonus add(constraint BONUS_PK PRIMARY KEY (ENAME));
alter table scott.salgrade add(constraint SALGRADE_PK PRIMARY KEY (GRADE));

####################################################################
#
# Warning:  Do not have NOT NULL constraints on Primary Key fields #
#           Primary keys are required.                           #
####################################################################


rem--------------------------------------------------------------------
rem generate replication support for all objects
rem--------------------------------------------------------------------

execute dbms_repcat.generate_replication_support ('scott','EMP','TABLE');
execute dbms_repcat.generate_replication_support ('scott','DEPT','TABLE');
execute dbms_repcat.generate_replication_support ('scott','BONUS','TABLE');
execute dbms_repcat.generate_replication_support ('scott','SALGRADE','TABLE');

rem--------------------------------------------------------------------
rem Add master replication site
rem--------------------------------------------------------------------

execute dbms_repcat.add_master_database ('scott','xxxxx.WORLD');

execute dbms_repcat.resume_master_activity ('scott',FALSE);

spool off

###########################################################################
# Once this setup is complete you must still schedule
# execution for the replicated schema.
# Ensure the init.ora parameters job_queue_process and job_queue_interval
# have been set.
###########################################################################

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



Examples:

N/A Received on Fri Sep 03 1999 - 15:35:05 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US