25
How to use SQL Developer’s Diff Wizard to Complete a DMS Migration for Oracle 12c
Generating the Diff
Here we are getting all the extra stuff that DMS won’t bring across like certain constraints, foreign keys, sequences etc.
Ensure your ssh tunnels run on different local ports, so that you can connect to the Source and Target simultaneously.
a. In SQL Developer go to Tools -> Diff Wizard
b. Select your Source database and Target database connection respectively.
c. Select all options
d. Click Next, next
e. Wait while SQL developer checks each schema, this takes ~15–20 minutes on a 320KB/s connection for a 1 billion row database.
f. Click the Green SQL icon (top left) when it’s done to generate SQL from Source to Target — alter table statements.
b. Select your Source database and Target database connection respectively.
c. Select all options
d. Click Next, next
e. Wait while SQL developer checks each schema, this takes ~15–20 minutes on a 320KB/s connection for a 1 billion row database.
f. Click the Green SQL icon (top left) when it’s done to generate SQL from Source to Target — alter table statements.
Diff analysis
a. Disregard any “add supplemental log data” lines
b. Check that you have all the tables you need from the source and your diff doesn’t want to create more tables, typically if you have old or legacy tables with 0 rows these may not need to be created in the Target. Remove those lines as necessary.
c. Create or replace statements are fine, drop statements need more investigation. If you see a lot of drop statements, check everything you’ve done so far.
d. Recommend that you separate your diff into the following files
e. Now you can run those against the Target DB in the following order, ensure you save all the output.
f. Take note of any failures during execution and save that output to a separate file. These are all safe to run, Oracle will complain if it can’t do something.
Note:
Running the output of the diff will enable constraints and enable triggers that were previously disabled.
If you feel like it has missed something, you can invert the disable commands in the above section.
It’s possible not all constraints or indexes will create successfully, check the output of executed SQL carefully.
You should have done this a few times by now in nonprod/preprod. Debug why these constraints aren’t working ahead of time and take note of resolutions. Constraints may need to be applied in the correct order, for example.
Rerun the diff
Ensure that you are comfortable with the output here as we are nearing the end of this journey.
Things that are OK to be missing are:
Things that are not OK to be missing. Missing these will break your app. Be thorough.
How to solve duplicate row issues
If you are unable to apply constraints due to duplicate rows, the below will help you identify and remove these rows.
Out of almost 1 billion rows that we moved, we did experience 6 duplicate row issues across 3 tables due to us stopping and starting cdc a few times.
— the following will help you identify the rows.
select ID, count(ID) from TABLENAME group by ID having count (ID) > 1;— if you’re comfortable that the rows are in fact, duplicates, delete them.
Check ALL sequences values
select 'ALTER SEQUENCE SCHEMANAME.'||sequencename||' INCREMENT BY 1000000;' || chr(10) ||'SELECT SCHEMANAME.'||sequencename||'.NEXTVAL FROM dual;' || chr(10) ||'ALTER SEQUENCE SCHEMANAME.'||sequencename||' INCREMENT BY 1;' from usersequences where incrementby=1 order by sequencename;Spot checking and validation
SELECT COUNT(*) FROM SCHEMANAME.SOMETABLE;EXEC DBMSSTATS.GATHERSCHEMA_STATS(OWNNAME => 'schema', CASCADE => TRUE)Check the status of indexes:
select indexname, status from dbaindexes where owner = 'SCHEMANAME'Note:
If you find indexes that have a status of “UNUSABLE” then you will need to investigate why before continuing!
You can use SQL Developer studio or SQL commands to investigate these indexes, drop them and recreate them before continuing.
Unusable indexes can come from things like primary keys indexes failing to create due to constraints or similar issues. Re-creating these in the correct order will usually resolve your problems.
If you have queries that are unindexed and suddenly you have a surge of traffic. Your CPU usage is going to get pegged at 100% and your application will fall to pieces. By checking that all your indexes both exist and are valid first, you can save yourself some significant pain.
Cutover to and Monitor the RDS database
You are ready for traffic.
If you’ve done this as part of a cutover, you’re about to get a flood of users hitting your brand-new RDS database instance.
Recommend that you turn on performance insights for RDS and watch that screen carefully. It can provide another view that something hasn’t been created properly during your diff run.
If you have application logs that are dumping SQL that is failing or taking too long to respond, copy that into a SQL worksheet and run explain on both your Source and Target oracle server. This will give you a visual workflow, so you can see what is going on. Typically, it’ll be an index that is missing or unusable.
25