topic Re: One-Way Replicate Not Applying Changes (ORA-20048) in Data Management Questions

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

WilliamCraft — Tue, 02 Jul 2013 01:08:41 GMT

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!

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

MarcoBoeringa — Tue, 02 Jul 2013 08:13:37 GMT

Considering the error points to a Stored Procedure part of the SDE schema, it seems to attempt to remove a particular database lock, which fails, as Oracle returns it apparently as "non-existent".

Are there multiple Feature Classes involved in the replica?

You might be able to get a better view of the issue by looking up the code of the Stored Procedure named "sde.lock_util.delete_object_lock" in the SDE schema, but I strongly urge you not to make any modifications (e.g. insert debug code), unless you are a very experienced DBA.

I wonder if you might be able to find out which table or row is affected by using any of the information in the "<220772,22580,1,999,N>" string, which seems a kind of "identifier" for the lock. I guess this requires more knowledge of Oracle locks than I can provide. ArcGIS 10.1 provides options for viewing locks in the Geodatabase Administration, but that doesn't help you on 9.3.1... But there is probably a ton of information regarding Oracle locks, here is one I googled: http://www.dba-oracle.com/t_locked_rows_user_locks.htm

Well, Vince is probably going to give more concrete answers... or point you to Tech Support, where the question may belong.

*** EDIT ***
I now found this knowledgebase article referencing ORA-20048

http://support.esri.com/fr/knowledgebase/techarticles/detail/33111

This article suggests open edit sessions created on the database level against the Multiversioned (Versioned) Views.

Have you attempted to edit any of the Feature Classes in your geodabases using SQL at the database level, and maybe forgot to close a SQL edit session?

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

WilliamCraft — Tue, 02 Jul 2013 13:03:36 GMT

Thank you, Marco. This is helpful in troubleshooting further. I had also performed an Oracle trace at the same time as the SDE intercept. When reviewing the trace file via OraSRP, I was able to better identify what I was seeing with the lock identifier syntax that you commented on earlier. In general, below is how I believe the identifier works:

Lock <sde_id,version_id,object_type,application_id,autolock> as translated from Lock <220772,22580,1,999,N>

The types of values described above were used in the following SQL as shown by my trace file:

DECLARE object_lock SDE.lock_util.object_lock_t; BEGIN /* ArcSDE plsql */
object_lock.sde_id := :sde_id; object_lock.object_id := :object_id;
object_lock.object_type := :object_type; object_lock.application_id :=
:application_id; object_lock.autolock := :autolock; object_lock.lock_type :=
:lock_type; SDE.lock_util.add_object_lock (object_lock);:sql_code :=
SDE.sde_util.SE_SUCCESS; EXCEPTION WHEN OTHERS THEN :sql_code := SQLCODE;
:error_string := SQLERRM; END;

I agree in that it seems as though an attempt is made to delete an object lock but the lock is not there. What is really interesting to me is that the trace file contains a few sde_id values throughout but never the '220772' as found in the SDE intercept files.

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

MarcoBoeringa — Tue, 02 Jul 2013 19:59:22 GMT

Actually, in the Oracle trace, it more looks like it is being attempted to set a lock, instead of deleting one... although it might be that an attempt is made to delete a lock before setting it as part of the procedure to set it.

By the way, I see the knowledgebase article also talking about an open state... have you attempted to load any data using the ArcSDE command line tools, e.g. by setting a layer to load only IO mode and loading data, and possibly forgotten to set it back to normal IO mode?

If the layer wasn't taken out of load only IO mode, you may be running into issues as well.

If you have the ArcSDE command line tools installed, an "sdelayer -o describe_long ..." type command output may be useful, especially for Vince once he joins here...

For some syntax examples regarding IO mode, see here:

http://support.esri.com/en/bugs/nimbus/TklNMDU1NDQ5

and for comprehensive sdelayer syntax including "sdelayer -o describe_long" help (10.0):

http://help.arcgis.com/en/geodatabase/10.0/admin_cmds/support_files/datamgmt/sdelayer.htm

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

WilliamCraft — Tue, 02 Jul 2013 20:32:46 GMT

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;

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

WilliamCraft — Tue, 02 Jul 2013 20:52:51 GMT

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...

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

MarcoBoeringa — Wed, 03 Jul 2013 07:02:46 GMT

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.