Any suggestions on validating migration from Oracle 12 to Oracle 19?
I've done some searching the web, and validating database migrations seem to come down to 2 steps: 1: use a migration tool, and 2: check logs from your migration tool. The actual migration is being done by our outsourced "partners" by data dump, so I made script to check the source export log for all expected objects and any errors, check the target import log for all expected objects and any errors, and connect to the target DB to confirm it's up.
Now the business is saying they want to identify some key tables and have us check the data in those tables. Which is totally doable, but I feel worse than useless, provides a false sense of security. This database is for a configured off-the-shelf application, and the DB structure isn't very well documented (at least in what the vendor will share with us). There's something like 450 user data tables and a similar number of tables for application configuration. Any small number of tables we select to manually inspect will not be statistically significant.
What might be useful is to query user_tables and all_views for the table space, and compare rows in the tables and text length for the views. But even that ignores indexes sequences, triggers ,etc. I think those objects end up being validated through regression testing of the application.
So, what are your thoughts? Are there useful queries we can run on the source and target DBs to compare results? Are there useful manual inspections we can do on a subset of 100s of tables with 100ks of rows?
Is there a magical query that will hash all the objects associated with a table space or schema, and reduce them into a single string that can be compared from source to target? Because that would be pretty cool.
I've done some searching the web, and validating database migrations seem to come down to 2 steps: 1: use a migration tool, and 2: check logs from your migration tool. The actual migration is being done by our outsourced "partners" by data dump, so I made script to check the source export log for all expected objects and any errors, check the target import log for all expected objects and any errors, and connect to the target DB to confirm it's up.
Now the business is saying they want to identify some key tables and have us check the data in those tables. Which is totally doable, but I feel worse than useless, provides a false sense of security. This database is for a configured off-the-shelf application, and the DB structure isn't very well documented (at least in what the vendor will share with us). There's something like 450 user data tables and a similar number of tables for application configuration. Any small number of tables we select to manually inspect will not be statistically significant.
What might be useful is to query user_tables and all_views for the table space, and compare rows in the tables and text length for the views. But even that ignores indexes sequences, triggers ,etc. I think those objects end up being validated through regression testing of the application.
So, what are your thoughts? Are there useful queries we can run on the source and target DBs to compare results? Are there useful manual inspections we can do on a subset of 100s of tables with 100ks of rows?
Is there a magical query that will hash all the objects associated with a table space or schema, and reduce them into a single string that can be compared from source to target? Because that would be pretty cool.