I am using ArcMap One-way replication tool to synchronize feature classes from Oracle to MS SQL Server (once a day), but replication only works for the versioned data with move edits to base option as unchecked and only edits or changes from that specific version are synchronized in MSSQL server.

The version of the feature class (Oracle's) in ArcMap is different from the base tables (the one that is shown in Oracle SQL developer), how can I make sure that the data in base table and the one that is shown in ArcMap are same? How can I make sure the edits or changes that are made in either of the version are reflected to the other? How to make ArcMap aware of the changes that are made in base table?

  • I want to reflect changes from ArcMap to base tables and when I make changes in the base tables from SQL developer or anywhere I want ArcMap to know those changes so that the replication can be successful. – Ayaz49 May 16 '19 at 8:05
  • Can I compress without unregistering? Because replication does not work with unregistered. – Ayaz49 May 16 '19 at 8:12
  • Both questions are very different, previous one is how to synchronize changes(either from arcmap or oracle sql developer) to sql server and this one is how to reflect changes that are made in Arcmap to base tables in oracle. Moreover, the previous question in still not answered but this one is answered to some extent and I am working on it. – Ayaz49 May 16 '19 at 11:00
  • This is the same fundamental problem. You're violating use patterns and expecting the software to shrug it off. The solution is to learn and use the versioning workflow. Replication is implemented via versioning, as the lowest version in the version tree. Your versioned edits shouldn't reach the base table until after sync occurs, so leaving that out of the problem makes it unanswerable (or answerable, but unuseful). – Vince May 16 '19 at 12:08
  • So every time I make edits I will have to synchronization first and then move edits to base? Is it what you are saying? – Ayaz49 May 16 '19 at 12:33

Based on information in comments, it appears that you have been editing the base tables using SQL. You should not make changes to the base tables directly using your RDBMS software. You should use only the versioned views when editing directly with SQL. For documentation on this see: http://desktop.arcgis.com/en/arcmap/10.3/manage-data/using-sql-with-gdbs/overview-edit-versioned-data-with-sql.htm (then there are implementation-specific pages for Oracle, MSSQL, etc in the contents at left).

NB: Note that this page says

Editing the base table directly circumvents this and could lead to orphaned records and data loss.

This means that your database is effectively corrupt. There is no easy way out of this situation.

I think you need to abandon your plan to get it working at all using this database.

I would suggest exporting all data and then creating a new geodatabase. Then import all data into the new geodatabase (using either ArcGIS tools, or versioned views with SQL, but NOT using SQL against base tables!).

Then learn about how to use versions and replicas correctly. In particular, familiarise yourself with the information in the page linked to above, and the database-specific pages linked to from the contents on that page. Ie, don't ever touch the base tables using SQL. If you need to do anything with the database directly, you MUST use only the versioned views.

Once you have all the pieces back in place, you can sync your replica from the version of your choice, and you can use reconcile and post to sync data between versions. See:

You can use the compress procedure to pull changes into the base tables and prune the state tree to whatever extent is possible (a full compress requires all replicas to be in sync and all versions to be posted and reconciled, among other criteria). This doesn’t affect what is in each version, but it will improve database performance if the state tree gets complicated.

Note that the "base tables" do NOT represent a "base version" or the built in 'DEFAULT' version. They are a very different things. Versions are internally managed by a complex arrangement of base tables, adds and deletes tables, state table and views that pull it all together. Attempting to access tables directly without going through these views is asking for trouble and making edits that way will put your database into an inconsistent an unreliable state. Which is why it's not working as you expect.

  • I am using the default version therefore don't have any other version to reconcile with. When I make edits in default it appear different from the one in that is shown in Oracle. – Ayaz49 May 16 '19 at 7:31
  • By oracle I mean SQL Developer, if I want to transfer the changes that are made in ArcMap, I will have to compress all edits in the default version into the base table and that option only appears when the layer is unregistered. – Ayaz49 May 16 '19 at 8:00
  • How can I compress edits to base without unregistering? – Ayaz49 May 16 '19 at 8:37
  • Reconcile and post/Sync will not push unversioned edits to the replica. Only destroying the replica and creating a new one will transfer SQL base table changes. Unversioning destroys any unrecognized edits, and Move Edits to Base is incompatible with Replication (because the replica wouldn't see the changes) – Vince May 16 '19 at 10:15
  • @Vince what do mean by destroying the replica? Will I have to create new replica everytime I synchronize? and if I am making changes from ArcMap with registered as versioned, how can I reflect those changes to base tables without unregistering it? I have only default version so I how to reconcile and post? – Ayaz49 May 16 '19 at 11:43

You've described a system that is working exactly as designed. Replication works within the geodatabase versioning framework to identify changes that have occurred within the parent, and to push those changes down to the child.

The problem is that you are not using the system as designed. When you make manual edits to the Oracle enterprise geodatabase via SQL without using versioning-aware mechanisms, there is no way for replication to identify the rows which need to be transmitted. (Doing this also corrupts the master versioned geodatabase, since there is no way to prevent compressed features from reverting your SQL changes.)

You have two fundamental choices here:

  1. You can use ArcGIS clients like ArcMap and supported mechanisms like versioned views to encode the deltas in the geodatabase version tree for distribution to the child geodatabase via replication, or

  2. You can create your own framework for identifying changes to the Oracle database and transmit them to the child SQL Server database via SQL statements in text files. (I've actually done this for a specific table under special circumstances, and it isn't all that difficult -- All you need is a unique key and a perfect hash of row content for each table to be replicated. The first table is 2-3 weeks work; each additional table is just a few minutes. Adding a feedback mechanism to assure client validity is an extra week or so.)

The only other option is to create a new daily replica to transmit the contents of the entire Oracle geodatabase to SQL Server, then destroying that replica so that subsequent editing changes will eventually reach the base tables.

That's it then. You have a choice between using Esri's robust, supported mechanism for heterogeneous database replication, or finding/writing your own tools. In either case, you need to stop corrupting your geodatabase with unversioned edits to base tables.

  • I now understand that I cannot make changes from the base tables directly but from the versioned views otherwise the geodatabase might corrupt. But what I don't understand is that what if I make changes from ArcMap only in the default version, then how can I make sure that the data in default version and base table is same? Do I need to unregister and compress? or is there any other way? – Ayaz49 May 18 '19 at 11:27
  • The act of unregistering a feature class destroys its participation in replication. Do not do this if there are any uncompressed changes outstanding in the version tree. The existence of replication means there is another version in your state tree, which will prevent Compress from placing the edits in the base table. You need to Synchronize the replicas before the base table will see changes. – Vince May 18 '19 at 13:01
  • I made some changes in default version, synchronized the replica and all edits are also transferred in SQL server through that replica. Now my question is that the edits made in the default version(of Oracle geodatabase), are not there in oracle's base table, how can I reflect those changes in oracle's base tables? – Ayaz49 May 18 '19 at 15:14
  • If a Compress fails, or succeeds but doesn't push down the edits, you have versions blocking a level-zero compress. New question. – Vince May 18 '19 at 17:02
  • I did compress the geodatabase but changes were not refelcted in base table(oracle), I have only one version that is the default. It's also part of the question "how can I make sure that the data in base table(oracle) and the one that is shown in ArcMap are same?" , I am afraid asking this with a new question might lead to a duplicate question. BTW thank you for your help, most of the things are clear and this is the last, I will make sure there isn't any other version than default so that the compress is successful. In case of problem Iwill come with another question that is more focused. – Ayaz49 May 18 '19 at 18:05

Not the answer you're looking for? Browse other questions tagged or ask your own question.