How to generate replication setup
files
Note: Additional information is contained within
the repdoc.txt
file created during the file generation process. The repdoc.txt file
contains additional troubleshooting information which is not included
within this How To document. Repdoc.txt also includes site-specific
configuration information, including a listing of all replication groups,
tables replication account names, replication tablespace setup.
1) Fill in the site and table-specific information within
the graphical interface.
2) Press the Generate button, the output files will be placed into the
destination file directory.
3) Add the init.ora parameters specified in the repinit.ora file to
each master site, then bounce each Oracle instance.
4) Add the tnsnames.ora entries specified in each of the site-specific
reptnsnamesora_SiteName.txt files to each master site:
5) Add the listener.ora entries specified in each of the replistenerora_SiteName.txt
files to each master site:
(This step is only required if a separate dedicated listener has been
configured on a separate network interface for use by replication. Each
file contains site-specific info, the site name is the last part of
the filename.)
6) Stop and start the listeners on each server:
lsnrctl stop
lsnrctl start
7) Log into svrmgrl (or sqlplus) as sys. Shutdown and then startup the
Oracle instance. Perform this task on each server.
shutdown immediate
startup
8) Log into svrmgrl or sqlplus as the sys user (or connect internal)
and run the following scripts to perform the complete replication setup.
9) Run the repprepcheck.sql file at each site in order to verify that
the Oracle configuration parameters are configured properly for a replication
site.
Note: The global_name of the database which is returned by the
repprepcheck.sql script must exactly match the Site Name entered into
the Site Name field of the Sites Folder tab of the graphical interface.
Please see the Troubleshooting Notes item for instructions about changing
the global_name of the database.
10) Run the repsetup_SiteName.sql file at each master site.
(This file creates the replication tablespace, replication user and
sets up the push and purge jobs. If these tasks have already been performed,
this step can be skipped.
Each file contains site-specific info, the site name is the last part
of the filename.)
11) If the tables do not have a primary key, perform the following tasks
at each site, otherwise skip this step.
Log into svrmgrl or sqlplus as the repadmin user.
Export data from the tables.
Truncate the tables.
Execute the code within the reppkcolumns_SiteName.sql file on each site.
This file adds the primary key column and constraint to each table.
The following 4 files make changes to the schema in preparation
for using the tables in a replicated environment. Each file should be
evaluated by the DBA to determine whether it is appropriate to run each
file, modify the files, or not use any of the files. The use of these
files is highly dependent upon the design of the database schema.
repsynonyms.sql
- This file creates public synonyms for all replicated tables.
repsequences_SiteName.sql
- These files add the sequences for each table, which will be used to
populate the primary key column. Sequence starting values must be different
at each master site, therefore each file is specific to the site where
it will be used.
repcolumns.sql
- This file adds the date_modified and site_id columns used
for replication conflict resolution.
idtriggers.sql
- This file adds the primary key trigger to each table.
reptriggers.sql
- This file adds the update trigger which updates the date_modified
and site_id columns upon the update of each row in the table.
Re-import the data exported from the tables, the primary
key, date_modified and site_id columns will be populated correctly.
The data and tables can then be exported and imported into the remaining
replication sites.
Note: After importing the tables into each of the other master sites,
it will be necessary to run the appropriate repsequences_SiteName.sql
file at each site.
12) If the tables only require the addition of the date_modified
and site_id columns, then run the following files:
repsynonymns.sql
repsequences_SiteName.sql
repcolumns.sql
file
Note: the idtriggers.sql code may still be required even if there
is a primary key trigger for the primary key column of each table. The
idtriggers.sql code includes code to prevent the trigger from firing
if the insertion into the table occured as a result of a replication
event. Existing primary key triggers must be modified in a similar manner
by the DBA.
13) Manually run a sql command to update the date_modified column of
each row to sysdate and the site_id column to the name of the masterdef
site.
14) Run the reptriggers.sql
file to add the trigger for updating of the Site ID and Date Modified
columns.
15) Create an OEM repository account for the repadmin account. Configure
the preferred credentials so that the repadmin account will be the account
used to log into each of the master sites with the Replication Manager
program. Use either the Replication Manager or the OEM Console application
to log into each of the master sites to monitor the execution of the
following scripts.
16) Run the following sql files while logged into the repadmin account
via sqlplus or svrmgrl at the master def site to add replication support
for the tables:
repaddgroup.sql
- adds each replication group
repaddtable.sql
- adds the tables to each replication group
repregen.sql
- generates replication support for each table.
Note: Only run the commands in repregen.sql on a few tables at
a time until replication support has been added for all tables. Do not
run the repregen.sql file on all of the tables at once. Use the Oracle
Replication Manager application to monitor the processing of this step
in order to determine when each small group of tables has been completed.
It will be necessary to manually push administrative requests between
sites until the queue is empty.
represume.sql
- resumes master activity for each replication group
16) Run the repcheck_mdef.sql
file via the repadmin account at the Master Def site in order to install
the replication_check PL/SQL procedure. Schedule the automatic execution
of this job on at least a daily basis via a job submitted thru the OEM
Console. This procedure needs to run on an Oracle 8.1.6 or higher version
database in order to send email directory from the database to the DBA
if problems are found with the replication process.
Note: There are additional descriptive comments located
at the top of each SQL file which is generated.
