Oracle DB Comparison

bonkey

Hi guys...

I need to compare two not-so-small (> 1gb) Oracle databases to ensure they're identical - data and everything.

Basically, the people I work for at the mo have ported an 8i database to 9i, and want me to verify (within reason) that the systems are identical, and that behaviour is still the same.

So, I want to compare all objects & Data. Then run a full set of all the various jobs we have on each database (using the same test-script on each db). Then compare all objects and Data again. They should remain identical.

I know there's payware tools like dbDiff out there which can do this, and if I need to I'll get them to buy me a copy (its cheaper then my time writing an app or even a script to do the equivalent will cost them)...but I'm wondering if anyone knows of any open/free apps which I could use instead?

Cheers,

jc

OMcGovern

Then just download a trial of a payware product.... like that DBDiff for Oracle.

http://www.dkgas.com/

regards,
Owen

bonkey

That would be, ummm, politically unexpedient.

In here,shareware and payware must be paid for if its being used on a production system....

jc

stevenmu

How about dumping the tables in both DBs to text files and and using something free like CSDiff to search for differences in the files. I've never ran it against huge files but it works great for smaller ones. The only problem might be if there is a difference early on in a big table such as a record missing, it might pick up every other record as a difference too leading to a huge set of differences (not sure how it'll cope with this memory wise) even though there's really only one record different. If it handles the large files ok it should be relativly quick compared to a product which scans the actual databases.

amen

don't forget to test all your jobs, sps etc
there could be configuration differences that comparing data/structures/sql won't be pick up on such as handling on nulls

amen

should also mention that you should track the perormance of the app/sps across both systems to ensure that the system is not any slower on 9i
is the hw identical ?
is there a Oracle conversion guide and was it read ?

bonkey

amen wrote:

don't forget to test all your jobs, sps etc
there could be configuration differences that comparing data/structures/sql won't be pick up on such as handling on nulls

Which is why, in the original post I said: Then run a full set of all the various jobs we have on each database (using the same test-script on each db)

should also mention that you should track the perormance of the app/sps across both systems to ensure that the system is not any slower on 9i
is the hw identical ?
is there a Oracle conversion guide and was it read ?

None of this is actually part of my remit...thankfully.

The organisation I'm currently in (lets call it a Big Swiss Bank for argument's sake) made the strategic decision to move to 9i. Someone else has specced the hardware, done the migration, etc. etc. etc.

Its now been handed from DBAs back to development simply to verify - within reason - that funcionality does not appear to be broken. Its more like the "Smoke Test" that one does on a nightly build than a stringent full-system test.

I want to make sure that everything runs, and that running a complete set of jobs doesn't yield any differences. I don't need to test and/or ensure, for example, that all code-paths of all jobs are tested, that boundary conditions remain consistent, or any of this stuff. I might need to do so later, if and when someone decides that the cost of such tests is worthwhile....but again...not my call

Thanks for the suggestions tho.
jc

amen

has the business owner signed off ?

Fallschirmjager

there are a number of products that do that but as i recall they are not cheap if this is justa one off but if you check google there are quite a few.

if you are not going to buy a product, you need to get your SQL skills up a bit! :rolleyes:

check the DB system tables...as i recall there are a shed load of stats views (logical and physical) that will help you.

you could also use the SQL UNION, DIFFERENCE command (and thay are quite fast as i recall) (at least i rememebr it as DIFFERENCE!).

you would need to connect both dbs together.

you could also do some anaysis on the tables, byte size, char lenght , no of rows and compare those.

to be honest it wont take too long to run the scripts for your DB...

did u try the support forum. they usually have a load of stuff there u can use for free...or you could post a question and see who has done this before.

also did u use export and import?

bonkey

amen wrote:

has the business owner signed off ?

Has this anything to do with comparing two databases?

rsynnott

Just do a CSV dump and diff the values. 1GB should be manageable, and you'll know soon enough if there's a problem. Alternatively, I'm sure you could do something with PL/SQL that'd do it on the serverside.

Tom Dunne

Not fully what you want, but Toad will do a DB schema comparison. I used it recently to compare to one of our sites in the US. It doesn't compare data (at least, not that I am aware of), but it does compare everything else.

It was a 30 day trial version.