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)