Tuesday, 20 August 2013

Offline Capture

Sometimes its not possible to directly connect over JDBC to a database your want to migrate to Oracle.
Sometimes you want someone else to perform the migration but would rather if you could just email them the metadata to carry out the migration without having them onsite.
In these cases SQL Developer offers Offline Capture.

Instead of making a connection to you database using a SQL Developer JDBC connection and then extracting the database metadata over the connection, you can run a script provided by SQL Developer which extracts the database metadata to files. These files can be used to perform the capture, making it easy to provide the database metadata to others without a connection.

Script Creation

The Offline Capture scripts can be acquired using SQL Developer.
Tools > Migration > Create Database Capture Scripts ...

Choose where you want to place the scripts, Windows or Linux format and which Platform
( SQL Server, MySQL, Sybase, DB2, Teradata)

Running Scripts


The scripts will use the tools of  non Oracle database(ex: BCP in Sybase and SQL Server, db2 for DB2,... ) to export the metadata (table definitions, procedure definitions, ..., not the table data) out to flat dat files on the disk. So it is essential that those "client" tools are available when running the scripts.  

The scripts also require a highly privileged database owner  (ex: sa for Sybase and SQL Server) so that the metadata tables can be read and their data extracted.

  • Disk space is usually not an issue as only the metadata of the database is extracted at this stage, not the table data.
  • SH files may need to made executable before being run

Running Scripts

The scripts have to be run by hand from the command line. The user supplies a privileged login into the database as an argument and the name of the database to extract its metadata.

SYBASE 15 Windows Example
Arguments: username password databasename servername

Note, in SQL Server and Sybase that two directories will be created.
  • Metadata from the databasename chosen
  • Metadata from the master database
This master database metadata is the same for all databases on this server.
If you want to offline capture databases from different servers, then you should create a directory for each server and copy/run the offline capture scripts from each directory.

Extract One Database
SYB15_OFFLINE_CAPTURE sa sa_password pubs2  servername

The result should be something like this

Extract Multiple Databases From the Same Server
SYB15_OFFLINE_CAPTURE sa sa_password database1  servername
SYB15_OFFLINE_CAPTURE sa sa_password database2  servername

The result should be something like this.

Extract Multiple Databases From Different Servers
From Directory server1
SYB15_OFFLINE_CAPTURE sa sa_password database1  servername1
SYB15_OFFLINE_CAPTURE sa sa_password database2  servername1

The result should be something like this.

From Directory server2
SYB15_OFFLINE_CAPTURE sa sa_password database3  servername2
SYB15_OFFLINE_CAPTURE sa sa_password database4  servername2

The result should be something like this.

The following are the expected files in a database directories and the master directory for Sybase 15.
master DIR

database DIR (ex: pubs2)

Now the metadata is on disk, the next step is to package it up to send it to the migrator.

Packaging Metadata

The file names, directories names and structure are important. Do not change the names of any files of directories or move their positions. All the files are required for SQL Developer to perform the  offline capture. Best to zip up the entire directory including scripts, metadata, directories without any changes.
When zipping up the files, make sure to keep the directory structure, so when unzipped the same directory structure is reproduced.

Offline Capture of Files

Unzip the files provided so that they are accessible to SQL Developer.
Create a brand new migration project. Tools > Migration > Migrate to Oracle

Follow the migration wizard.On Step 4 (Source Database)
  • Choose Mode Offline.
  • Choose the *.ocp file. 
Continue through migration

Note that SQL Developer requires a new project for each Server. So if you are migrating multiple servers, you will have to have separate migration projects, and have to perform the offline capture for each server.

Hopefully this makes offline capture a little easier to follow. Ill place up some examples of different databases soon.