schema and data compare between Oracle and MariaDB/MySQL

marrowyung
marrowyung used Ask the Experts™
on
Hi,

Once i import all data from a dmp file to an orace 11gR2 ex / Oracle 12c to MariaDB/MySQL InnodB cluster, I'd like to compare the different in schema in oracle and MariaDB, any kind of tools for windows can compare both ?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Software Developer / Linux System Administrator / Managing Director
Commented:
There are many schema comparison tools so you would need to check that they support basic comparisons with the ability to map data types supported by the different databases. A quick search picks up the following:

http://www.dbsolo.com/schema_comparison.html
https://www.aquafold.com/aquadatastudio_features

I have used software from here before: https://www.upscene.com/

There are many others as well. Some open source tools also exist.
marrowyungSenior Technical architecture (Data)

Author

Commented:
so they are to compare totally diff DB?

"I have used software from here before: https://www.upscene.com/"

this one seems no SW to compare schema, or you are referring to database workbench. ?
Martyn SpencerSoftware Developer / Linux System Administrator / Managing Director

Commented:
I am referring to Database Workbench. When I used it, it had a schema comparison tool. As to whether they compare totally different database types, that is something you will want to confirm, but IIRC Database Workbench will do, but I had to set up field type mappings.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

marrowyungSenior Technical architecture (Data)

Author

Commented:
"When I used it, it had a schema comparison tool.
"
pro edtion only ! I am checking it out too !

what is  IIRC?
Martyn SpencerSoftware Developer / Linux System Administrator / Managing Director

Commented:
IIRC = If I recall correctly

It's a while since I used it, so there may now be new editions. There are numerous tools for database administrator, many that are free, but in my experience the free ones have limited comparison capabilities when it comes to comparing different schemas from different database systems.

Are you looking to compare just the schema definition, or data as well? The latter, for a large database, could be quite time consuming.

Edit: DBSolo specifically states that it compares schemas from different database engines and it looks to be one of the lowest cost of the commercial tools I have seen. It also has a free trial, so you can see if it meets your requirements.
marrowyungSenior Technical architecture (Data)

Author

Commented:
" many that are free, but in my experience the free ones have limited comparison capabilities when it comes to comparing different schemas from different database systems."

sure

"Are you looking to compare just the schema definition, or data as well? "

both ! actually schema first and see how well the convertion will be.

"The latter, for a large database, could be quite time consuming."

this one :   https://www.upscene.com/ ?

as it check everything ?

"Edit: DBSolo specifically states that it compares schemas from different database engines and it looks to be one of the lowest cost of the commercial tools I have seen."

tks.
Martyn SpencerSoftware Developer / Linux System Administrator / Managing Director

Commented:
"The latter, for a large database, could be quite time consuming."
What I mean is that comparing data between two large databases could be very time consuming. If it is a one-off, it's probably not an issue. If it is something regular, optimising the comparison would be important. I am, perhaps, stating the obvious here.
marrowyungSenior Technical architecture (Data)

Author

Commented:
"What I mean is that comparing data between two large databases could be very time consuming. "

true !
marrowyungSenior Technical architecture (Data)

Author

Commented:
let me try and update you later.
marrowyungSenior Technical architecture (Data)

Author

Commented:
tks man, I might come back later but hopefully not.
marrowyungSenior Technical architecture (Data)

Author

Commented:
Hi,

as now I found out that there will be column type different so comparing column type is not making sense at all, usually AFTER migration from Oracle 10gR2/12c to MariaDB 10.3.x, what is the best way to compare and make sure that the migration is ok?
Martyn SpencerSoftware Developer / Linux System Administrator / Managing Director

Commented:
Usually schema comparison tools offer a method of mapping column types so that they can treat columns as identical if they are either the same data type or the mapping is consistent. In terms of comparing data, so long as the data is comparable, so for example you are comparing an integer to a string and the string contains data that can be converted to an integer, your comparison tool should also handle it. In situations where columns are being introduced or removed, most schema comparison tools will also allow you to exclude columns from the comparison.
marrowyungSenior Technical architecture (Data)

Author

Commented:
hi,

from your point of view, other than using compare tools to verify if the migration done, any other way to suggest we can try to conclude the migration is done without any problem ?
Martyn SpencerSoftware Developer / Linux System Administrator / Managing Director

Commented:
Your migration tool itself should give an indication of success or failure. What did you decide to use for the migration in the end? There are various ways you could do this. For example you could create a hash of the data in all of the tables on the source and verify that the hash matches on the destination. It could be very time consuming, so using a migration tool that checks and logs errors would be the best approach. I presume you are carrying out a single migration?
marrowyungSenior Technical architecture (Data)

Author

Commented:
hi,

"What did you decide to use for the migration in the end? "

for DB migration we will try to use Ispirer migration tools as they have a lot of experience on this.

"For example you could create a hash of the data in all of the tables on the source and verify that the hash matches on the destination."

how to do this?

I understand a lot of way to do it ! e.g. count no. of row of each table BEFORE and AFTER migration. then can script out the answer too.

" I presume you are carrying out a single migration?"
for DB level only, probably use ispirer.
Martyn SpencerSoftware Developer / Linux System Administrator / Managing Director

Commented:
Looking at the documentation for Inspirer, I see no command line options for verification and to be honest, I would expect their utility to be able to do this. You really should have a word with their technical support and ask if the option to stop on error is disabled, is it safe to assume a valid and equivalent migration. If you are unable to trust the migration tool, then you would need to compare schemas yourself. You would do this with a comparison tool or create your own scripts.

Counting rows is one type of validation. You are assuming that equal row counts equals the same data in both databases. Since you are converting between databases and there may well be different data representations, this may or may not be enough. If you want to be certain, you could read the data from each table in each database and compare it. This could be done using a server based hash or you could create your own client application to do it. Both are likely to be slow for large databases.

I think we risk de-railing the conversation too much in suggesting ways of comparing data manually, because you should be able to trust your conversion tool. If you can't you need to ask the question is it the right tool for the job.
marrowyungSenior Technical architecture (Data)

Author

Commented:
"Looking at the documentation for Inspirer, I see no command line options for verification and to be honest, I would expect their utility to be able to do this. You really should have a word with their technical support and ask if the option to stop on error is disabled, "

asking .

"Since you are converting between databases and there may well be different data representations:"

That's why I do whatever I can, counting row and distinct row is one way,

" If you want to be certain, you could read the data from each table in each database and compare it."

in diff Db, the presentation can be slightly diff between DB. this is the problem that make the tools shows DB is diff after compare.

"I think we risk de-railing the conversation too much in suggesting ways of comparing data manually, "

I am testing one tools, already show me incorrect result, we are checking it.

so I want to know any other METHOD.
Martyn SpencerSoftware Developer / Linux System Administrator / Managing Director

Commented:
Since this question has been marked as answered, you may be better asking for specific detail in another question, since it is likely that only I am receiving notifications. You could reference this question to make sure that other experts know what has already been discussed. I usually only offer general advice here and I think that it has answered the original question. As to whether or not there is another method, I suggest waiting until you have a response from the migration tool support team. You should be able to trust your migration tool. It is responsible for correctly transferring from one database to another.
marrowyungSenior Technical architecture (Data)

Author

Commented:
hi,

tks.

"I suggest waiting until you have a response from the migration tool support team. "

Ispirer replied, they said we have to test it ourselves. I am asking for a migration report feature.


"You should be able to trust your migration tool. It is responsible for correctly transferring from one database to another."

yes! right now only Ispirer can be trust.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial