Repgen Features
and Benefits
Repgen
Demo Available for download...
-- script name: repdoc
-- Features: Contains basic setup instructions for setting up replication
-- along with the site configuration
-- and troubleshooting commands.
-- Copyright 2002 by .com Solutions Inc.
-- This output file was created by Repgen version 2.2.4 on Fri May 31
13:29:24 2002. By .com Solutions Inc. www.dotcomsolutionsinc.net
# ----------------------- Configuration Information: Common --------------------------
client name: .com Solutions Inc.
Oracle CSI Number: 111333
Oracle Technical Support Phone#: 800-223-1711
replication group: customer_repgroup
tables replicated:
customers
addresses
replication group: static_tables_repgroup
tables replicated:
state_province
country
# ----------------------- Configuration Information:
Site-Specific --------------------------
site type = master def
site name = prod3
host name = host1
oracle SID = prod3
replication-specific tnsnames.ora entry = prod3
replication-specific hostname = host1
repadmin account name = repadmin
sequence starting values = 1000000000
replication tablespace name = replication
replication tablespace file = c:\u01\prod3\replication01.dbf
replication tablespace size = 50Mb
Oracle Home = c:\v901\
Index Tablespace Name = index
Oracle Version = 901
site type = master
site name = prod5
host name = host2
oracle SID = prod5
replication-specific tnsnames.ora entry = prod5
replication-specific hostname = host2
repadmin account name = repadmin
sequence starting values = 2000000000
replication tablespace name = replication
replication tablespace file = /u01/prod5/replication01.dbf
replication tablespace size = 50Mb
Oracle Home = /v901/
Index Tablespace Name = indx
Oracle Version = 817
# ----------------------- Replication
Setup Instructions --------------------------
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 following file to each
master site, then bounce the Oracle instance.
repinit.ora
4) Add the tnsnames.ora entries specified in the following 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.)
reptnsnamesora_prod3_1.txt
reptnsnamesora_prod5_1.txt
5) Add the listener.ora entries specified in the following 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.)
replistenerora_prod3_1.txt
replistenerora_prod5_1.txt
6) Stop and start the listeners on each server:
lsnrctl stop
lsnrctl start
7) Log into svrmgrl (or sqlplus for Oracle 9i and higher) 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 repprecheck1.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 for instructions about changing the global_name of the database.
10) Run the following setup files at each site:
(These files create the replication tablespace, replication user and
set 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.)
repsetup_prod3_1.sql
repsetup_prod5_1.sql
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.
The following 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.
The reppkcolumns files add the primary key column and constraint to
each table. Run the appropriate reppkcolumns file at the site specified
in the filename:
reppkcolumns_prod3_1.sql
reppkcolumns_prod5_1.sql
repsynonyms1.sql - This file creates public synonyms for all replicated
tables.
The following repsequences 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.
repsequences_prod3_1.sql
repsequences_prod5_1.sql
repcolumns1.sql - This file adds the date_modified and site_id columns
used for replication conflict resolution.
idtriggers1.sql - This file adds the primary key trigger to each table.
reptriggers1.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.
12) If the tables only require that the date_modified and site_id columns
added, then run the following files:
repsynonyms1.sql
repsequences_prod3_1.sql
repsequences_prod5_1.sql
repcolumns1.sql
Note: the idtriggers1.sql code may still be required even if there is
a primary key trigger for the primary key column of each table. The
idtriggers1.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) Then 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 "prod3".
14) Run the reptriggers1.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 commands logged into the repadmin account via
sqlplus or svrmgrl at the master def site to add replication support
for the tables:
repaddgroup1.sql - adds each replication group
repaddtable1.sql - adds the tables to each replication group
repregen1.sql - generates replication support for each table. NOTE:
Only run these commands on a few tables at a time, not all of the tables
at once.
represume1.sql - resumes master activity for each replication group.
17) Run the repcheck_mdef1.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: The repcheck_no_mdef1.sql version of this procedure is available
in order to be run at server which is not one of the master sites, as
long as the site contains dblinks to each of the master sites. This
version of the replication check procedure could be installed on an
OEM server.
# ----------------------- Replication Removal
Instructions --------------------------
1) To remove replication support for all of the replicated tables, run
the following scripts while logged into the repadmin account:
repsuspend1.sql - suspends master activity for all groups.
repremovegroup1.sql - removes replication conflict resolution methods,
column groups, removes each table from replication (without dropping
the table), and removes each replication group.
2) To remove all replication support from the database, run the following
script as the SYS user at each site.
These scripts remove the push and purge jobs, replication support within
the database, public and private database links, replication tablespace
(data file is not removed), and drop the repadmin account user.
Note: If there are invalid objects within the database after running
catrepr.sql, then examine the drop_invalid.sql file which gets generated
by the removal scripts. Execute this file manually to drop the invalid
objects manually to prevent catrep.sql from failing the next time replication
is setup. Some versions of catrepr.sql (for instance with Oracle 9.0.1)
do not correctly remove all replication related objects from the database,
thus requiring this extra manual step to be performed.
repremovesetup_prod3_1.sql
repremovesetup_prod5_1.sql
3) The primary key triggers (created by the idtriggers1.sql file) and
the replication triggers (created by the reptriggers1.sql file) will
need to be modified after the removal of replication. These triggers
depend upon the DBMS_REPUTIL.FROM_REMOTE replication package and will
fail after this package is removed by catrepr.sql. Modify these triggers
to remove the dependency upon the DBMS_REPUTIL.FROM_REMOTE in order
to continue using the replicated tables without replication installed.
# ----------------------- Replication
Troubleshooting Commands --------------------------
-- checking global_name of database
select * from global_name;
-- changing global_name of database
1) Write global_name to database using the format "PROD1.WORLD".
Note: No more than one period is allowed, and the name must be in for
format of db_name "." db_domain.
2) Then if the global_name needs updated to a non-standard format like
"PROD1" (with no db_domain), update the table after step 1 has been
completed:
update global_name set global_name = 'PROD1';
commit
or
update global_name set global_name = 'PROD1.NEW';
commit
Note: In all cases, the database link name must exactly match the database
global_name because the init.ora global_names parameter must be set
to TRUE in order for replication to work correctly.
--- check for invalid objects
select object_name, object_type, status, owner from dba_objects where
status='INVALID';
-- check status
select * from dba_repcatlog;
-- dba_repcatlog should contain 0 items at the master def and the other
master sites.
-- Keep running the do_deferred_repcat_admin job manually at the master_def
site
-- then alternating back and forth to the master sites running it as
well. Keep
-- doing this until all items in dba_repcatlog have been processed.
If there are error
-- jobs remaining, fix the errors or delete the error jobs.
select * from dba_jobs;
execute dbms_job.run(48);
select job, broken, failures, what from user_jobs;
execute dbms_job.broken(26,false);
execute dbms_defer_sys.execute(1);
select * from dba_jobs_running;
-- deferred transactions are held here, these transactions need pushed
to the other sites
select count(*) from deftran;
select * from deftran;
select * from defcall;
-- destinations which will get replication changes
select count(*) from deftrandest;
select * from deftrandest;
select job, what from dba_jobs;
select count(*) from deferror;
select * from deferror;
select deferred_tran_id,error_msg from deferror;
-- it is Ok to delete from def$_error according to Oracle support
delete from sys.def$_error;
commit;
-- generate sql which will re-apply deferror transactions
-- apply ORA-0060 transactions first - to make sure records get added
-- order by start_time to apply transactions in correct order
select 'execute DBMS_DEFER_SYS.EXECUTE_ERROR ( deferred_tran_id =>'''
|| DEFERRED_TRAN_ID || ''', destination =>''' || DESTINATION || ''');'
from deferror where error_number in (-60)
order by START_TIME;
-- apply "data not found" transactions now that the records exist
-- order by start_time to apply transactions in correct order
select 'execute DBMS_DEFER_SYS.EXECUTE_ERROR ( deferred_tran_id =>'''
|| DEFERRED_TRAN_ID || ''', destination =>''' || DESTINATION || ''');'
from deferror where error_number in (-100, 1403)
order by START_TIME;
select count(*) from sys.dba_repcatlog;
select deferred_tran_id,error_msg from deferror;
-- transactions stay in deftran until dbms_defer_sys.purge
-- removed from deftrandest when changes have been committed
-- remove one entry from deferror
execute DBMS_DEFER_SYS.DELETE_ERROR ('2.19.804','ORA.WORLD');
-- remove all entries from deferror
execute DBMS_DEFER_SYS.DELETE_ERROR (null,null);
-- remove all entries from deftran
-- *** only do this if replication needs removed and re-created ***
execute dbms_defer_sys.delete_tran(deferred_tran_id =>'', destination
=>'site1');'
-- if tab$.trigflag != 0 - then Oracle internal replication triggers
are enabled
select * from tab$ where trigflag != 0;
-- to remove Oracle internal replication triggers if replication is
not working
-- and needs to be removed in an emergency to allow writing to the tables
-- also do this as sys user
run catrepr
-- also do this as sys user
update tab$ set trigflag=0 where trigflag !=0;
commit;
shutdown immediate;
startup
select * from sys.def$_error;
select count(*) from dba_2pc_pending;
select count(*) from dba_2pc_neighbors;
-- should return 0 records
select count(*) from sys.pending_trans$;
-- if returns more than 0 then run dbms_repcat.purge_master_log
select count(*) from dba_repcatlog;
set transaction use rollback segment system;
delete from dba_2pc_pending where local_tran_id = '13.193.590';
delete from pending_sessions$ where local_tran_id = '13.193.590';
delete from pending_sub_sessions$ where local_tran_id = '13.193.590';
-- only do this if advised by Oracle support
select * from deftran where DEFERRED_TRAN_ID = '13.193.590';
execute dbms_transaction.purge_lost_db_entry('13.193.590');
-- reconciling replicated tables
One way to reconcile tables in earlier versions of Replication
(7.1.6,7.2.X) is through a
SELECT * FROM tab@db1 MINUS SELECT * FROM tab@db2
SELECT * FROM tab@db2 MINUS SELECT * FROM tab@db1
However in release 7.3.X and greater there is a DIFFERENCE facility
to make this easier. See Note:1062732.6 for more details on
using the DBMS_RECTIFIER_DIFF.DIFFERENCES procedure.
-- obtaining the site id within a trigger
:NEW.SITE_ID := DBMS_REPUTIL.GLOBAL_NAME;
-- Oracle TS tracing
alter session set events '10046 trace name context forever, level 12';
-- compiling of invalid objects
-- create sql code to compile invalid objects
-- This task is usually done by utlrp.sql, but sometimes it may
-- be helpful to manually recompile some objects.
set echo off
set feedback off
set verify off
set pagesize 0
conn / as sysdba
spool compile_invalid.sql
select 'alter '||object_type||' ' ||owner||'.'|| chr(34) || object_name
|| chr(34)||' compile '|| object_type || ';' from dba_objects where
status = 'INVALID' and object_type = 'PACKAGE';
select 'alter PACKAGE ' ||owner||'.'|| chr(34) || object_name || chr(34)||'
compile BODY;' from dba_objects where status = 'INVALID' and object_type
= 'PACKAGE BODY';
select 'alter '||object_type||' ' ||owner||'.'|| chr(34) || object_name
|| chr(34) ||' compile;' from dba_objects where status = 'INVALID' and
object_type = 'VIEW';
spool off
set echo on
set feedback on
set verify on
-- removal of invalid objects
-- create sql code to drop invalid objects
-- Warning: examine and edit the output file as needed before running
drop_invalid.sql.
-- run as SYS user
set echo off
set feedback off
set verify off
set pagesize 0
spool drop_invalid.sql
select 'drop '||object_type||' ' ||owner||'.'|| chr(34) ||object_name
|| chr(34)||';' from dba_objects where status = 'INVALID';
spool off
set echo on
set feedback on
set verify on
# ----------------------- end of file --------------------------
