One-Way Replicate Not Applying Changes (ORA-20048)

Question

I have a one-way FGDB replica that is not applying changes after successfully being registered and created initially.  Once edits are made in the master GDB (an Oracle 11G R2 9.3.1 SP2 geodatabase) to any of the participating object classes, the file timestamp of the FGDB replicate never updates.  In reviewing the replication log for the replicate via ArcCatalog, I see a GENERAL SYSTEM FAILURE error.  I then decided to perform an SDE intercept while attempting synchronization and below is what I found in the SDE intercept output:

NString:      "Error executing stored procedure sde.lock_util.delete_object_lock"
NString:      "ORA-20048: Lock <220772,22580,1,999,N> not found, not deleted."

There is not much that can be found on this issue within the Esri forums, and Oracle does not need to have much of anything on this particular error number.

I have confirmed that all GDB permissions are correct, all object classes participating in replication are registered as versioned, and everything exists as high precision.

What do the above errors actually mean?  We have 17 other one-way FGDB replicates that update just fine, one of which is based on different feature classes of the same Oracle database mentioned above.

Thanks for any help you can provided!

MarcoBoeringa · Answer

I found a related table that was selected to participate in the replica and it was related by way of a Simple M-N (many to many) relationship class.  At the FGDB level (i.e., the replicate) I am not sure how this is handled because of the fact that an RDBMS will create an intermediary table to act as a sort of "pivot table" in this situation.  In other words, I am not certain whether or not simple, one-way replication even supports a M-N relationship at 9.3.1 SP2.

You may wish to have good look at this 9.3 Help page:

Replicating related data(ArcInfo and ArcEditor only)

Are you applying spatial or attribute filtering to the replica, as per the Help page, possibly resulting in broken relationships?

And have a look here:

Synchronizing with filters and related data(ArcInfo and ArcEditor only)

Especially see the text under "Example two" for this link above. Overall, it seems relationships are pretty well handled in replication.

WilliamCraft · Answer

I found a related table that was selected to participate in the replica and it was related by way of a Simple M-N (many to many) relationship class.  At the FGDB level (i.e., the replicate) I am not sure how this is handled because of the fact that an RDBMS will create an intermediary table to act as a sort of "pivot table" in this situation.  In other words, I am not certain whether or not simple, one-way replication even supports a M-N relationship at 9.3.1 SP2.  So, I've unregistered the replica and re-created it without the related table to see if that allows changes to be applied.  More to come once some time passes...

WilliamCraft · Answer

Good point, Marco, about the lock topic.  I've pulled the SQL for the LOCK_UTIL package and listed it below.  When it gets to the object locks section, I can see that it is supposed to add an object lock and subsequently delete one thereafter.  It's possible that I may need to seek some help eventually in inserting some debug code as you suggested.

I went ahead and wrote a script to iterate through all of my feature classes to determine whether there were in NORMAL or LOAD_ONLY mode.  All of them came back as NORMAL.

I will continue to troubleshoot and reply if I identify a fix/solution.  Unfortunately, I am not having much success with Esri tech support on this issue at the moment.  We have a few more steps to try.

Thank you again, Marco.  Your input is much appreciated.

SQL Code from package is below...

*****************************************************

CREATE OR REPLACE PACKAGE SDE.lock_util
IS

/* Type definitions. */

SUBTYPE layer_lock_t IS SDE.layer_locks%ROWTYPE;
   SUBTYPE layer_id_t IS SDE.layer_locks.layer_id%TYPE;
   
   SUBTYPE state_lock_t IS SDE.state_locks%ROWTYPE;
   SUBTYPE state_id_t IS SDE.state_locks.state_id%TYPE;
   
   SUBTYPE table_lock_t IS SDE.table_locks%ROWTYPE;
   SUBTYPE table_id_t IS SDE.table_locks.registration_id%TYPE;
   
   SUBTYPE object_lock_t IS SDE.object_locks%ROWTYPE;

/* Constants. */

-- The following constant defines the release of lock_util and is used by
   -- the instance startup code to determine if the most up to date version of
   -- the package has been installed.

C_package_release       CONSTANT PLS_INTEGER := 1008;

-- Constant names for autolock parameters.

C_is_autolock           CONSTANT CHAR(1)  := 'Y';
   C_is_not_autolock       CONSTANT CHAR(1)  := 'N';
   
   -- Constant names for lock types.

C_shared_lock           CONSTANT CHAR(1)  := 'S';
   C_exclusive_lock        CONSTANT CHAR(1)  := 'E';
   C_marked_lock           CONSTANT CHAR(1)  := 'M';

C_shared_lock_all       CONSTANT CHAR(1)  := '-';
   C_exclusive_lock_all    CONSTANT CHAR(1)  := 'X';

/* Procedures and Functions. */

-- The following functions perform operations for layer locks stored in
   -- the SDE.LAYER_LOCKS table.  Each operation is an autonomous transaction.

PROCEDURE add_layer_lock (layer_lock  IN  layer_lock_t);
   PROCEDURE delete_layer_lock (sde_id    IN  pinfo_util.sde_id_t,
                                layer_id  IN  layer_id_t,
                                autolock  IN  VARCHAR2);
   PROCEDURE delete_layer_locks_by_sde_id (sde_id  IN  pinfo_util.sde_id_t);
   PROCEDURE update_layer_lock (layer_lock  IN  layer_lock_t);

-- The following functions perform operations for state locks stored in
   -- the SDE.STATE_LOCKS table.  Each operation is an autonomous transaction.

PROCEDURE add_state_lock (state_lock  IN  state_lock_t);
   PROCEDURE delete_state_lock (sde_id    IN  pinfo_util.sde_id_t,
                                state_id  IN  state_id_t,
                                autolock  IN  VARCHAR2);
   PROCEDURE delete_state_locks_by_sde_id (sde_id  IN  pinfo_util.sde_id_t);

-- The following functions perform operations for table locks stored in
   -- the SDE.TABLE_LOCKS table.  Each operation is an autonomous transaction.

PROCEDURE add_table_lock (table_lock  IN  table_lock_t);
   PROCEDURE delete_table_lock (sde_id    IN  pinfo_util.sde_id_t,
                                table_id  IN  table_id_t);
   PROCEDURE delete_table_locks_by_sde_id (sde_id  IN  pinfo_util.sde_id_t);

-- The following functions perform operations for object locks stored in
   -- the SDE.OBJECT_LOCKS object.  Each operation is an autonomous transaction.

PROCEDURE add_object_lock (object_lock  IN  object_lock_t);
   PROCEDURE delete_object_lock (object_lock  IN  object_lock_t);
   PROCEDURE delete_object_locks_by_sde_id (sde_id  IN  pinfo_util.sde_id_t);

-- The following procedures delete layer, table, state, object locks
   -- stored in SDE.LAYER_LOCKS, SDE.TABLE_LOCKS, SDE.STATE_LOCKS, 
   -- SDE.OBJECT_LOCKS respectively within a single autonomous transaction.

PROCEDURE delete_all_locks_by_sde_id (sde_id  IN  pinfo_util.sde_id_t);
   PROCEDURE delete_all_locks_by_pid (pid  IN  pinfo_util.sde_id_t);
   PROCEDURE delete_all_orphaned_locks;

END lock_util;