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.

• Save this output in a text editor. It will likely be too big for the default file size for SQL Developer worksheets. You can edit the default allowed size of worksheets in the options if you want.

Diff analysis

a. Disregard any “add supplemental log data” lines

• If you copy and paste into sublime text, you can cmd + shift + g to select “supplemental logging” text throughout the document and then cmd + k to select the entire line, then safely delete those lines from the diff.

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

• Table Changes/Alters, Constraints, etc
• Indexes
• Triggers
• Sequences
• Stored Procs (if you’ve got any)
• Other stuff. (Again, if they exist.)

e. Now you can run those against the Target DB in the following order, ensure you save all the output.

• Indexes & Constraints
• Sequences & Triggers
• Stored Procs.
• Anything else

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:

• Some standard constraints or SYS constraints with a different name, such as a constraint with a different name being enabled or disabled. Compare the details of the constraint, not just the name.
• Old Tables no longer in use
• Supplemental logging statements

Things that are not OK to be missing. Missing these will break your app. Be thorough.

• Indexes
• Sequences
• Triggers
• Primary Key constraints
• Foreign Key constraints

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

• RDS values should be higher than on-premise / Previous DB values. The following can be used for sequence adjustment if required.

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

• For your important tables, such as transactions or status (You should know what these are) check your row count. It should line up with the source as a quick way to verify that they are in sync. This does not validate the contents of the table though, I expect that you have done that in non-production and pre-production first as part of application testing. The below command is for spot and sanity checking only.

SELECT COUNT(*) FROM SCHEMANAME.SOMETABLE;

• Run analyse on the schema, if you can tolerate a manual failover of Multi-AZ RDS instance after running this, do so. If not, ensure that you have a maintenance window coming up to restart the RDS Oracle instance.

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.