SQL to Compare Replicated Table Objects between Master Def Site and Master Site
APPLIES TO:
Oracle Database - Enterprise Edition - Version 7.2.2.0 to 9.2.0.1 [Release 7.2.2 to 9.2]
Information in this document applies to any platform.
PURPOSE
The purpose of this document is to provide several SQL scripts to allow the
Replication Database Administrator (usually the REPADMIN user) to verify
structural differences between a replicated table object on the Master
Definition Site - MDS and that of its corresponding table object within the
replication environment at any other Master Site - MS.
This will complement which documents how to breakdown and
resolve replication set-up issues. The scripts will compare the definition of
two objects. They will check column, trigger and constraint definitions of two
tables in different databases, one of which will be the MDS and the other an
MS in a Replicated Environment.
These differences will become apparent when ever the replicated object is
accessed for the purpose of replication set-up or daily processing and will
usually result in one of he following errors:
ORA-23308: object %s.%s does not exist or is invalid
ORA-23309: object %s.%s of type %s exists
ORA-23318: a ddl failure has occurred
A full explanation of when and why these errors can occur is detailed in
.
SCOPE
The reasoning behind the scripts being read-only anonymous SQL is due to the
fact that these will be used on production databases and if this was in the
form of a package then some customers have to go through rigorous change
control and testing before ANY addition / change to the live set-up can be
implemented. This is not what we want for this as they are problem solving
scripts and need to be used on an adhoc basis.
To run these scripts you must know the name of the replicated table object that
is being verified, as well as the Global Name of the remote database (MS). The
global name will always be the database link required to access the MS from
the MDS due to the need within replication of having the GLOBAL_NAMES parameter
set to TRUE.
DETAILS
note:122039.1note:176913.1The reasoning behind the scripts being read-only anonymous SQL is due to the
fact that these will be used on production databases and if this was in the
form of a package then some customers have to go through rigorous change
control and testing before ANY addition / change to the live set-up can be
implemented. This is not what we want for this as they are problem solving
scripts and need to be used on an adhoc basis.
To run these scripts you must know the name of the replicated table object that
is being verified, as well as the Global Name of the remote database (MS). The
global name will always be the database link required to access the MS from
the MDS due to the need within replication of having the GLOBAL_NAMES parameter
set to TRUE.
Each script should be run locally on the MDS. If a hard copy is required then
the Set OUTPUT option should be used.
Scripts to Determine Replicated Object Differences between MDS and MS
=====================================================================
Please note the following before running the attached scripts :
- You will need to run these scripts as the REPADMIN user.
- Run the scripts against the master definition site and replace the
string with the global name of the remote master.
- The object in question can be found in DBA_REPOBJECTS.
- To attain the global_name of both databases perform the following:
Select * from global_name;
SQL 1
=====
-- Compare Table Columns
spool MDS_table_columns_diff.out
column owner format A10;
column COLUMN_NAME format A15;
column DATA_type format A10;
column DATA_length format 9999;
column DATA_precision format 9999;
select 'local db' database, a.OWNER,a.COLUMN_NAME, a.DATA_type,
a.DATA_LENGTH, a.data_precision
from sys.dba_tab_columns a
where a.table_name=''
union all
select 'remote db', b.OWNER,b.COLUMN_NAME, b.DATA_type,
b.DATA_LENGTH, b.data_precision
from sys.dba_tab_columns@ b
where b.table_name=''
order by column_name;
-- Compare Table Columns
SQL 2
=====
-- Compare Table Triggers
spool MDS_triggers_diff.out
column owner format A15;
column trigger_name format A25;
column table_name format A20;
set long 2000
set pagesize 100
select 'LOCAL DB' database, a.owner, a.trigger_name,
a.table_name, a.description
from sys.dba_triggers a
where a.table_name=''
union all
select 'REMOTE DB' database, b.owner, b.trigger_name,
b.table_name, b.description
from sys.dba_triggers@ b
where b.table_name=''
order by trigger_name;
-- Compare Table Triggers
It may also be worth checking the consistency of the actual trigger body.
However due to an issue in some versions with LONGs and DBLNKS, the local
and remote querys will need to be performed separately from the MDS.
Example:
select 'Remote DB', trigger_body
from sys.dba_triggers@
where trigger_name=''
and owner='';
SQL 3
=====
-- Compare Table Constraints
spool MDS_constraints_diff.out
column owner format A10;
column constraint_name format A20;
column table_name format A15;
column column_name format A20
set pagesize 100
select 'LOCAL DB' database, a.owner,
a.constraint_name, a.table_name, b.column_name,
decode (a.constraint_type, 'P', 'PRIMARY KEY', 'C', 'CHECK',
'U', 'UNIQUE KEY', 'R', 'REFERENTIAL', 'UNDEFINED') Type
from sys.dba_constraints a, sys.dba_cons_columns b
where a.table_name=''
and a.constraint_name = b.constraint_name
union all
select 'REMOTE DB' database, c.owner,
c.constraint_name, c.table_name, d.column_name,
decode (c.constraint_type, 'P', 'PRIMARY KEY', 'C', 'CHECK',
'U', 'UNIQUE KEY', 'R', 'REFERENTIAL', 'UNDEFINED') Type
from sys.dba_constraints@ c, sys.dba_cons_columns@ d
where c.table_name=''
and c.constraint_name = d.constraint_name
order by column_name;
-- Compare Table Constraints
It may also be worth checking the consistency of the actual constraint columns.
However due to the same issue for triggers above with LONGs and DBLNKS, the
local and remote querys will need to be performed separately from the MDS.
E.G.
select 'Remote DB', search_condition
from sys.dba_constraints@
where constraint_name=''
and owner='';
These reports should be fairly self explanatory in terms of what is consistent
and what is different or does not exist. If they contain data that is
contrasting then the output should be forwarded to Oracle Support Services
for further analysis.
This document is copied from Oracle Support (Doc ID 172042.1)