Friday, 26 August 2011

Very Large Migrations

Most of the time SQL Developer does a good job of migrating a database from SQL Server, Sybase ,... To Oracle. But there are some tricks to help make very large migrations perform smoother.

This talks about Large migrations in terms of object numbers , not amount of data in tables.

If your migrating 50 databases at once, and each has 1,000 tables, 1,000 views , 1,000 procedures . That is a lot of meta data to churn through and sometimes we find
memory issues , open cursors ,... can be a problem.

We are always working to reduce the amount of resources required to perform the migrations , but for the moment (SQL Dev 3.0) here are a few tricks which just might make the difference between the migration failing and the migration completing.

By Default SQL Developer startups with
  • AddVMOption -XX:MaxPermSize=128M
  • (found in sqldeveloper\sqldeveloper\bin\sqldeveloper.conf)
  • AddVMOption -Xmx640M (found in sqldeveloper\ide\bin\ide.conf)
  • AddVMOption -Xms128M (found in sqldeveloper\ide\bin\ide.conf)
This is usually fine, but ... if you have more memory, SQL Dev maybe able to use it.
When using SQL Developer 32 bit on a 32 bit machine with a 32 bit JDK. The following is ok
  • AddVMOption -XX:MaxPermSize=128M
  • (found in sqldeveloper\sqldeveloper\bin\sqldeveloper.conf)
  • AddVMOption -Xmx1024M (found in sqldeveloper\ide\bin\ide.conf)
  • AddVMOption -Xms256M (found in sqldeveloper\ide\bin\ide.conf)
When using SQL Developer 64 bit on a 64 bit machine with a 64 bit JDK. The following is ok . As long as you have the memory.
  • AddVMOption -XX:MaxPermSize=512M
  • (found in sqldeveloper\sqldeveloper\bin\sqldeveloper.conf)
  • AddVMOption -Xmx4096M (found in sqldeveloper\ide\bin\ide.conf)
  • AddVMOption -Xms2048M (found in sqldeveloper\ide\bin\ide.conf)
Oracle Database
By Default Oracle uses a small maximum of open cursors allowed. When migrating large databases SQL Developer does tend to open a lot of cursors against the Oracle database which has the migration repository. We have worked on minimizing this and will continue to do so, but the workaround at the moment is to set this limit higher in the Oracle database you create your migration repository in.


Note if you are migrating multibyte databases (objects names are in chinese, japanese ,.. ) you should create the migration repository in a UTF-8 Oracle database. Otherwise the object names may not be saved/migrated correctly.

SQL Developer
SQL Developer obviously interacts with the migration repository. For example to convert a stored procedure. It queries the migration repository to retrieve the captured SQL and then inserts into the repository the converted SQL. Not only that, SQL Developer also queries the migration repository to identify name changes, data type of columns and so on. These round trips back and forth to the migration repository can slow down a migration.

If you are migrating large amounts of Stored Procedures, Triggers, Views, Functions it is best to install SQL Developer on the same machine as the Oracle database that contains the migration repository. This will remove the network overhead and speed up the migration.

Migrate Incrementally
SQL Developers Migration Wizard allows you to define the entire migration you want up front, and then kick it off. This is ok for smaller migrations, but I think it best that most migrations perform the migration incrementally.
The migration wizard allow you perform each step of the migration by itself. This enables you to review each step of the migration before proceeding to the next.

Just click the "Proceed to Summary" checkbox at the bottom left of the wizard and then click Next. The wizard will then only perform the steps you have choosen so far.

When this step is complete you will get a chance to review the result and then kick off the wizard again , straight back into the wizard at the correct step.

Not only that but breaking it into steps also helps with memory/cursor issues.

Redundant Objects
Some times a database which is being migrated contains a lot of objects which are redundant or not required to migrate. If there is a substantial amount of these objects, its best to remove them from the captured model so as they do not consume resources going forward.
Right click the objects in the captured model you do not want and choose delete.
Note that this is a model and not connected to your Source SQL Server, Sybase ,... database.
So those source objects are not deleted , just items in your model saved in the migration repository.

Generation Script
Very large databases will have very large generation script. By default SQL Developer generates one big script which is then run to create all the target objects in Oracle.
But there is a preference (Migration > Generation Options > A File Per Object) to split each object (table,trigger,view,...) definition into a separate file.
Then a master script calls each file individually to create the objects. This means a much smaller master file.
It is also handy if you want to source control the object definitions.