Reason for a Data Loss While Making Schema Changes on SDE - Oracle DB

[AdityarajChavada]

I am not an expert when it comes to Administering Multi-User Geodatabase. However, I refer to Esri's documentation for best practices and efficient workflows before putting on my GIS Admin's hat to perform any Geodatabase Administrative task.

Recently, one my tasks involved making schema changes (adding fields and deleting fields) to a feature class part of feature dataset which is registered as versioned. As per the Esri documentation here, it is required to unregister the dataset as Versioning before making any schema changes to the feature class. Keeping this mind, I followed the steps highlighted below:

1. Take a Geodatabase and XML Backup of the Production DB
2. Connected to the Geodatabase Administrative Account
3. Removed all the locks from the dataset
4. Connected to the Geodatabase Data-owner Account
5. Unregister the Dataset as Versioning - ArcGIS Prompted me to compress the edits to the base - I checked option before hitting Continue (I remember checking the mark but have no way to confirm I did)

6. Added the fields and deleted the fields required to complete the task
7. Registered the feature dataset as versioned

ArcGIS for Desktop Version - 10.4.1 Oracle Enterprise Database Version - 11g

Later, the data owner reported that was data missing from the user's version. Upon investigating further, the data was missing from DEFAULT Version and User's Version (one year worth). The data was retrieved from the backup but my still need to know what caused such huge data loss.

My research tell me that the data loss occurred due to not checking the compress option. However, the data editor's version regularly post their edits to DEFAULT which should logically delete only the data (edits worth 3 days) that was not posted to Default before making the above changes.

I am asking for some guidance from experts in the field who can propose any reason for such huge data loss or guide me to the right direction to find a way to look into the System Tables to answer this question.

Thank you in advance.

[Asrujit_SenGupta]

Unregistering data as versioned—ArcGIS Help | ArcGIS Desktop 

However, you must unregister a feature dataset as versioned when you want to do one of the following:

• Create a topology.
• Create a geometric network.
• Add or remove a feature class from a geometric network.
• Create a network dataset.
• Add or remove a feature class from a network dataset or make other schema changes.

You did not perform any of the above listed operations. You just added\deleted some fields, which could have been done anyway.

Your assumption is correct, the data was lost because you did not perform the compress. The Compress operation moves all edits from the Delta tables (a- and d- tables) to the Base\Business Table. Even if you do Reconcile\Post on a daily basis, the edits remain in the Delta tables till you perform the Compress.

So as soon as you unregistered as Versioned, without performing the compress, everything in the Delta tables was lost.....leading to the observed scenario.

[AdityarajChavada]

Thank you for the quick response, Asrujit! 

I just have following questions:

1) What does the "other schema changes" refer to in the fifth point? 

2) When you take the backup of the enterprise geodatabase using GDB and XML that is versioned, does it copy only dataset in Default version or all the versions?  

3) Does ArcGIS Desktop keep a log file of all the geoprocessing tasks that are executed included the one mentioned above? I need to verify that I checked the Compress option.

Thanks again.

[Asrujit_SenGupta]

1) What does the "other schema changes" refer to in the fifth point? 

I believe (I may be incorrect), the "other schema changes" might be related to the Network Dataset part. Someone else may add to this, if I am missing something.

However, I can confirm that add\delete of Fields can be done, even when the Feature Class is Versioned. Obviously, its always better to test this on your own, if you are not sure, in a Test environment.

2) When you take the backup of the enterprise geodatabase using GDB and XML that is versioned, does it copy only dataset in Default version or all the versions?  

If you are taking a Full database backup, from the SQL Server end, it will have everything. Data from all versions.

If you are exporting the data as a Feature Class to another geodatabase or as XML workspace, it will only export data from the Version you are connected to while performing the Export. By default, ArcCatalog connects to DEFAULT version, but you can change this. Connect to a specific geodatabase version—Help | ArcGIS Desktop 

3) Does ArcGIS Desktop keep a log file of all the geoprocessing tasks that are executed included the one mentioned above? I need to verify that I checked the Compress option.

If you remember the approximate time, when you did this task, check the Compress Log in the geodatabase and verify if a Compress was performed.

[AdityarajChavada]

Hi Asrujit,

Do you know if the SDE stores a log for a compression for the performed task mentioned above in this question? Specifically, when you only unregister 1 feature dataset out 15 as versioning and check the compress box.

I didn't see any records in the SDE_Compress table . I made changes to two feature dataset but the data is missing in only one of the feature dataset. That means one of the feature dataset was compressed without creating a log?  

In fact, I performed the same steps again using sample data on my laptop. I found that the compress log file is creating a entry for the case mentioned above. This means that I did not check the compress option for the the feature dataset that was missing the data. 

[Asrujit_SenGupta]

If you are Unregistering as Versioned, a particular data and Compressing during the above mentioned process....then there will be no entries in the Compress Log file.

Compress Log file will only add an entry when you perform the Compress for the entire Geodatabase, not for a particular Feature Dataset\Feature Class.