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.
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)
Notes
Extract One Database
SYB15_OFFLINE_CAPTURE sa sa_password pubs2 servername
The result should be something like this
From Directory server2
The result should be something like this.
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 ...
( SQL Server, MySQL, Sybase, DB2, Teradata)
Running Scripts
Prerequisites
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.
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.
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.
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.
Create a brand new migration project. Tools > Migration > Migrate to Oracle
Follow the migration wizard.On Step 4 (Source Database)
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.
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.
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.