My Oracle Support Banner

SQL to Compare Replicated Table Objects between Master Def Site and Master Site (Doc ID 172042.1)

Last updated on NOVEMBER 27, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 7.2.2.0 and later
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

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.