Document ID: 33162.1
Subject: SETTING YOUR ENVIRONMENT FOR SYMMETRIC REPLICATION
Last Revision Date: 14 February 1996
Author: KKASSIS
This bulletin is intended to give the reader an overview of setting up
a Symmetric Replication environment. It is by no means a document that
includes all the aspects involved in Symmetric Replication.
Bulletin Objectives
-------------------
After reading this bulletin, you should be able to:
o Identify important parameters and procedures for replication
environment setup.
o Identify database links in symmetric replication.
o Identify user accounts required for security administration in the
replicated environment.
o Create a master definition site.
I. Before you Start
----------------
Before you create and administer a replication environment, you need
to know the following:
Required Tablespaces:
o System tablespace : replication needs an additional 15 mb of
system space.
o Rollback segment : (at least 2) should be larger than normal.
o User tablespace.
o Temporary tablespace.
Database Scripts:
All the following scripts are located in $ORACLE_HOME/rdbms/admin
and need to be run if they have not been run yet.
o catalog.sql
o catproc.sql <<< will run catsnap.sql and dbmssnap.sql
o catrep.sql <<< for symmetric replication ; this
script takes a long time to run.
(+- 1 Hour)
o pupbld.sql <<< not required for replication but
needed to avoid "user profile ...
error messages.
Initialization Parameters in init<SID>.ora :
o job_queue_processes : at least 2
o job_queue_interval : should be less than the time
interval to push data
along
the dblink.
o shared_pool_size : at least 15 mb ; 25mb suggested.
o distributed_lock_timeout : defualt 300 sec ( 5 min )
o distributed_transaction : maximum 10
o global_names : for Replication to work it should
be set to TRUE.
o open_links : minimum 5
Global Names for the databases:
o Choose global names as appropriate for the environment.
o To assign a global name for the database :
o sqlplus sys/<password>
o alter database rename global_name to <a name>;
Sql*Net version 2 files :
The following files are needed.
o tnsname.ora
o listener.ora
NOTE: Replication needs Sql*Net version 2 ; Sql*Net version 1 is
not supported because it does not support global naming.
Private Database Links :
o Create the necessary private database links using connecters
but not aliases.
II. Now you can Start
-----------------
Now is the time to set up two special administration accounts required
by symmetric replication.
o SYS surrogate user:
Some replication activities are performed under SYS account and
require access to remote nodes. However, the SYS account need not to
have a remote access to the full set of SYS privileges at the remote
node. A SYS Surrogate User is set up to perform replication activities
at all nodes.
The following will create a Surrogate Replication user named "repsys"
and grant Surrogate Replication administration privileges.
o sqlplus sys/<password>
o drop user repsys cascade;
o create user repsys identified by <password>;
o execute dbms_repcat_auth.grant_surrogate_repcat('repsys');
o Replication Administrator User:
The replication administrator is the control user for replication
environments. All replication commands must be executed while
connected to the replication administrator account.
The following will create a Replication Administrator user
named "repadmin" and grant replication catalog and replication
procedures.
o sqlplus sys/<password>
o drop user repadmin cascade;
o create user repadmin identified by <password>;
o execute
dbms_repcat_admin.grant_admin_any_repschema('repadmin');
o grant execute on dbms_defer to repadmin with grant option;
Database Links:
In addition to the special user accounts for replication
administration several kinds of database links are required.
o Public create public database link <target database
name> using 'sql*net alias';
o sys create database link <target database name>
connect to repsys
identified by <surrogate user passwrd>;
o Replication create database link <target database name>
Admin connect to repadmin
identified by <rep admin user password>;
Create a Master Definition Site:
Steps to Create a Master definition Site:
1. After you make sure that you have all the necessary privileges
and links to the sites in your replicated environment.
2. Create master replication schema:
o Syntax for the create_master_repschema
create_master_repschema( sname varchar2,
schema_comment varchar2,
master_comment varchar2 )
sname : The name of the schema that you want to
replicate. Only the objects in this schema
can be replicated to other sites.
schema_comment: This comment will be added to
RepCat table. RepCat, a table contains
all information necessary to maintain
the replicated environment.
master_comment: This comment will be added to
RepSchema table. RepSchema, a logical
construction of replicated objects
in the replicated environment.
NOTE: Schema you want to replicate must already
exist at the site.
o Example:
connect repadmin/<password>
execute dbms_repcat.create_master_repschem('<schema name>' ,
'Created by '||USER||' on 'SYSDATE' ,
'A master definition site xyz ' )
3. For each object in the schema that you wish to replicate, you
need to:
o Add or register the object to the replicated schema.
Syntax of create_master_repobject:
create_master_repobject(sname varchar2,
oname varchar2,
type varchar2,
use_existing_object boolean,
ddl_text varchar2,
comment varchar
2,
retry boolean,
copy_rows boolean )
sname: The name of the schema in which the object that
you want to replicate is located.
oname: The name of the object that you are trying to
replicate. If ddl_text is NULL this object
must already exist in the schema.
type: The type of object that you are replicating
(ex. personal ..)
use_existing_object: Indicate true if you want to reuse
any objects of the same type at the master sites.
ddl_text: If the object does not exist at the master
definition site, you must supply the DDL text
necessary to create this object.
comment: comment will be added to the COMMENT field of
the RepObject view.
retry: Indicate TRUE if you want the oracle sever to
retry to create an object if encountered an error
when attempting to to create the object.
copy_rows: Indicate TRUE if you want the initial contents
of a newly replicated object to match the the
contents of the object at the master definition
site.
Example:
connect repadmin/<password>
execute dbms_repcat.create_master_repobject('SCOTT',
'emp' , 'table' ,'create table emp as select * from
personal',
'created by scott' )
o Call appropriate procedures for the tables that
require conflict resolution (beyond scope of bul.)
o Generate replication support for the objects.
Syntax for generate_replication_support:
generate_replication_support(sname varchar2,
oname varchar2,
type varchar2,
package_prefix varchar2,
procedure_prefix varchar2,
distributed boolean )
sname: The name of the schema in which the object is located.
oname: The name of the object you are generating a
replication support for.
type: The type of object that you are replicating.
package_prefix: For objects of type PACKAGE or
PACKAGE BODY this is prefixed to the
generated wrapper package name.
procedure_prefix: For objects of type PROCEDURE
PACKAGE or PACKAGE BODY this value is prefixed
to the generated wrapper package procedure name.
distributed: Indicate TRUE if you want the procedure
run at each master site. Otherwise, the
supporting objects are generated once at the
master definition site and copied to each of the
master sites.
Example:
connect repadmin/<password>
execute dbms_repcat.generate_replication_support('SCOTT',
'emp' , 'dept', 'table' )
4. Finally, because all replication activities were suspended
(if any) when we issued the
execute rdbms_repcat.create_master_repschema;
we need to restore normal activities by running the
resume_master_activity procedure.
Syntax of resume_master_activity
resume_master_activity(sname varchar2,
override boolean )
sname: The name of the replicated schema.
override: If override is TRUE, it ignores any pending RepCat
administration requests and restore each normal replication
activities at each master site as soon as possible. If FALSE
it restores normal replication activity only when there is
no pending RepCat administration request for 'sname' at
that master site.
Example:
connect repadmin/<password>
execute dbms_repcat.resume_master_activity('<schema name>');
Reference:
o Oracle7 Symmetric Replication Guide. Jan 1995
o Oracle7 Symmetric Replication : Asychronous Distributed
Technology. June 1994