How can we do data analysis for DB replication project

Question

We are facing one issue in our project i.e. Data verification issue.

The project is about Replication of data from Sybase to oracle DBs. The table structures for Table A across Sybase, Oracle is same. Same column and primary key combination across all the databases. e.g. If Sybase has Table A with columns a, b and C same table with same name and same columns will be available in different databses.

We are done with replication stuff part.But we faced some silent failure like data discrepancy just wondering if there will any tool already available for this.

Any information on his would be helpful. Thanks.

Answer 1

The SAP/Sybase rs_subcmp tool is pretty powerful and also pretty hard to use. For details see:

https://help.sap.com/viewer/075940003f1549159206fcc89d020515/16.0.3.3/en-US/feb58db1bd1c1014b134ef4efef25563.html?q=rs_subcmp

You have to pass it key field information, but once you do that, it can retry/restart the compare streams after transient differences. Pretty fancy.

rs_subcmp expects to work on Sybase data source. So to compare against Oracle, you'd probably have to setup one of those Sybase-to-Oracle gateway products ($$$$$).

Could you install the Oracle ODBC drivers and configure them to allow Sybase clients to access Oracle? I'm guessing not (but that's outside the range of my experience).

Note the "-h" option for rs_subcmp. The docs just say it runs a "fast comparison", but what it's actually doing is running queries using the hashbytes() function. Something like:

select keyfield1,keyfield2, hashbytes("Md5",datacol1,datacol2,datacol3)
from mytable

So this sort of query might be good for the "summary view" type comparison discussed above (if the Oracle STANDARD_HASH() function output matches up with the Sybase hashbytes() function (again, outside my experience))

Note, as of ASE 16, there was a bug with the hash() & hashbytes() functions running the Md5 hash option against large varbinary columns where they could use up all procedure cache, potentially crashing the server (CR 811073)

Answer 2

Sybase (now SAP) has a couple products that can be used for data comparisons and reconciliation:

• rs_subcmp - an older, 32-bit tool that comes with the Sybase Replication Server product that can be used to compare data between source and target; SQL reconciliation scripts can be generated from the differences and then applied to the target to bring it in sync with the source; if your tables are more than 1GB in size you can still use rs_subcmp but you'll need to create multiple comparison jobs (via where clauses) to work on different subsets of your tables [I don't recall if rs_subcmp can be use for heterogeneous replication setsup, eg, ASE-Oracle.]
• Data Assurance (DA) - the newer, 64-bit product ... also from Sybase ... which can also compare data and (re)sync the target(s) from the source (either via SQL reconciliation scripts or directly); DA is capable of handling comparisons between a handful of different RDBMS products (eg, ASE-Oracle); I'm currently working on a project where one of the requirements is to validate (and reconcile where needed) 200+TB of data being migrated from Oracle to HANA and I'm using DA for the validation/reconciliation portion of the project

As @TenG has hinted at with his answer, there's a good bit of effort involved to compare data and generate code to reconcile the differences. Rolling your own code is doable but will entail a lot of work. If you've got the money you'll likely find 3rd party tools can get most/all of the work done for you.

If you used a 3rd party product to replicate your data from Sybase to Oracle, you may want to see if the same vendor has a comparison/validation/reconciliation tool you could use.

Answer 3

I've worked on a few migration projects and a key part has always been data reconciliation.

I can only talk about the approaches we took, based on constraints around tools available and minimising downtime, and constraints of available space.

In all cases I took to writing scripts that worked on two levels - summary view and "deep dive". We couldn't find any tools readily available that did what we wanted in a timely enough manner. In fact even the migration tools we found had limitations (datapump, sqlloader, golden gate, etc) and hand coded scripts to handle the bits that we found to be lacking or too slow in the standard tools.

The summary view varied from project to project. It was part functional based (do the accounting figures for transactions match) for the users to verify, and part technical. For smaller tables we could just write simple reports and the diff was straight forward.

For larger tables we wrote technical reports that looked at bands of data (e.g group the PK into 1000s) collect all the column data and produce checksum, generating a report for each table like:

PK ID Range Start   Checksum
-----------------   -----------
100000              22773377829
200000              38938938282
.
.

Corresponding table pairs from each database were then were "diff"d against each other to highlight discrepancies. Any differences that were found could then be looked at in more detail.

The scripts were written in such a way to allow them to run in parallel looking at discrete bands. Te band ranges were tunable as well to get the best throughput. This obviously sped things up.

The scripts were shell scripts firing off sqlplus reports, and similar for the source database.

On one project there wasn't enough diskspace to do these reports, so I wrote a Java program that queried the two databases side by side, using block queues to fetch and compare rowsets. Being in memory meant this was super fast.

For the "deep dive" we looked at the details for key tables, or for tables that reports a checksum difference.

For the user reports, the users would specify what they wanted to see, and we wrote the reports accordingly.

On the last project, the only discrepancies found were caused by character set conversion issues (people names with accents weren't handled correctly).

On projects where the overall dataset was smaller we extracted the data to XML files and wrote a Java tool to processes pairs and report differences.