Offline Data Move DATEs

During the offline data move, scripts are created to use BCP to dump out your data in SQL Server or Sybase to .dat files. Other scripts are created to use Oracle SQL*Loader to load the .dat files into the Oracle tables.

Your SQL Server/ Sybase database will dump out the DATETIME and SMALLDATETIME values in formats which may not be recognized by Oracle. So you have to specify what the format mask is.

SQL Server / Sybase DataTypes
DATETIME has millisecond precision.
SMALLDATETIME has only second precision.

Oracle DataTypes
DATE has only second precision
TIMESTAMP has millisecond precision

You can specify their format mask in the migration preferences.



The preferences work like this

• The Timestamp Mask is applied to the DATETIME Sybase or SQL Server values
• The Date Mask is applied to the SMALLDATIME Sybase or SQL Server values.

So now the full date value is recognized by SQL*Loader.

The best bit is that even if you map Sybases/ SQL Servers DATETIME to Oracles DATE (which is the default mapping). The TO_TIMESTAMP that happends in SQL*Loader will be implicitly converted to a DATE when inserted, and the milliseconds automatically truncated.


Here are some Defaults which you should specify in the Migration preferences
SQL Server
Timestamp Mask Preference = DATETIME in SQL Server = 'yyyy-mm-dd HH24:mi:ss.ff3'
Date Mask Preference= SMALLDATETIME in SQL Server = 'yyyy-mm-dd HH24:mi:ss'

Sybase
Timestamp Mask Preference = DATETIME in Sybase = 'Mon dd yyyy hh:mi:ss:ff3AM'
Date Mask Preference = SMALLDATETIME in Sybase = 'Mon dd yyyy hh:mi:ss'

You should check your .dat files to make sure the format is correct. You can regenerate the scipts after you change the preference. The formats are applied in the generated Oracle/*.ctl files.

SQL Developer Migration Workbench 1.5.1 for Sybase / SQL Server Quick Guide

This Quick Guide will run through the main steps you should take to perform a Sybase or SQL Server migration to Oracle.

Installation

• Download the latest version of SQL Developer ( using 1.5.1 today), to your Development PC which can access your Oracle database and your SQL Server/ Sybase database. SQL Developer ships with the migration extensions built in.
  
• Download and Install Oracle XE if you dont have access to an exiting Oracle database.
  
• UnZip SQL Developer into its own directory (for example "SQLDev")
• Don't use an existing ORACLE_HOME or ORACLE directory
• Run sqldeveloper
• Setup the jtds driver (open source JDBC driver for Sybase and SQL Server) with SQL Developer
• JTDS can be got using the Check For Updates feature. Tools > Prefereneces > Extensions > Check For Updates. Walk through the wizard and choose any new migration updates and the JTDS driver.

Setup Connections & Migration Repository

• Create a connection to Sybase or SQL Server
  
• Create a connection to your Oracle database
  
• Create a new schema for the migration repository (MIGREP)
• GRANT DBA TO MIGREP IDENTIFIED BY MIGREP
• Create a connection to your migration repository schema (MIGREP)
• Associate the migration repository with this schema
• Right Click the migration repository connection (MIGREP), Migration Repository > Associate Migration Repository
  

Setup the Migration Preferences

• Setup the "Is Quoted Identifier On" preference
• Setup the DATETIME and SMALLDATETIME format masks if required.

Capture your Database
Creates a "snapshot" of the Sybase / SQL Server database in our migration repository.

• Capture the Sybase / SQL Server database (dont use Quick Migrate!) .
  
• Note there is an offline capture available as well if you cannot directly connect to your Sybase database
  
• Save the Captured Dialog Text (Handy to have a list of captured number or objects)
  
• Identify any Capture Issues and Resolve (there shouldn't be any!)
• Browse the Captured Model

Convert your Captured Database to Oracle

• Convert the Captured Model to the Oracle Model
• Choose the datatype mapping (best to use the default mapping)
  
• Save the Converted Dialog Text (Handy to have a list of converted number of objects)
  
• Save the Migration Log
• Identify any Convert Issues and Resolve

Generate The Oracle Database (Schema and Objects)

• "Generate" the Migration scripts
• Run the Scripts using the SQL Worksheet to create your Oracle database.
• Save the Result of the Generation Script

Project Plan
Errm. It looks like Im doing the plan in the middle of the project!
But in reality the above steps can be completed for one database within hours, or a day or two. You havent converted any data, we have just been working on the database definition (tables, views, procedures,...). You may have lots of issues remaining, but you now know what has been automated for you and what manual work remains. This is the time to step back and think of the entire project and plan.

• How are you going to validate the Oracle database?
• How are you going to rectify conversion issues or objects which didnt convert?
• How are you going to switch from Sybase / SQL Server to Oracle. Will there be downtime for users?
• What apps are affected by the switch? What work is required to move them to Oracle?
  

Enhance your Oracle Database

• Identify and Generation Issues and Resolve them in your Oracle database. Not in the converted model as there are better tools to work with real Oracle objects.
  
• Verify that all the objects in your Sybase / SQL Server database are present and valid in your Oracle database.

Move the Data from Sybase / SQL Server to Oracle

• Data Move for small dataset (<100mb),>
• Data Move for large dataset (>100mb) , use the offline datamove

Test & Tune
The buck stops with you to validate that the migration is complete and succefull. Note that this part can take longer than the migration itself. How do you test your Sybase / SQL Server database currently? How will you verifiy that the Oracle database works as expected?

Deploy

• Will the database definition have changed since you started the migration?
• Will the data have changed? How to move it to Oracle. Using a delta or perform an entire data move again.

Your migration approach will differ, depending on the complexity of the database, the applications that run on top, your company standards, .... . Hopefully the above steps will help.

SQL Developer Migration Workbench 1.5.1

SQL Developer 1.5 released 8 weeks ago with the new Sybase capability, aiding migrations from Sybase 12 and Sybase 15 to Oracle.

It worked pretty good, and we got some great feedback from customer using it in the real world on their migration projects. All the enhancements and now provided in SQL Developer 1.5.1 released on Monday. Thanks to all.

Download it here
http://www.oracle.com/technology/products/database/sql_developer/index.html


Here are the main improvements

• Improved Offline Data Move Scripts including support for both DATETIME And SMALLDATETIME formats
• Variable Name Collision Management
• Temporary Table Name Collision Management
• Sybase Identity Columns Converted Correctly
• Emulation Packages Improved

Many of the Issues noted by some customers regarding SQL Developer such as ORACLE_HOME issues, Connections dissapearing, NLS settings, .. are resolved.

List of Migration Bug fixes for 1.5.1

 Num      Subject
7137280  SYBASE 12 LINUX OFFLINE CAPTURE SCRIPTS NOT AVAILABLE
7129820  CANNOT DROP CONVERTED INDEXES
7113461  PROCEDURE VARIABLES NAMES ARE NOT COLLISIONED CORRECTLY
7113269  EXECUTE PROCEDURE PARAMETER NAMING INCORRECT
7046907  OFFLINE BLOB DATA MOVE "CREATE PROCEDURE" PREFERENCE SHOULD BE ON BY DEFAULT
7046890  TEMPORARY TABLE NAMES NOT COLLISIONED
7046870  CONVERTING IDENTIFIERS > 100 CHARS IN LENGHT CAUSED THE CONVERT PHASE TO FAIL
7046785  NOEXPAND TABLE HINT IS NOT RECOGNIZED
7022092  SYBASE: IDENTITY COLUMNS NOT CONVERTED TO SEQUENCE AND TRIGGER
7005215  OTNFORUMS : OBJECT/DATABASE/MODEL WORDS USED INCONSISTENTLY
6989645  IMPROVE TEMPORARY TABLE DEFINITION CONVERSION
6989465  CURSOR STATUS SHOULD BE EMULATED @@SQLSTATUS : CURRENT LIMITATION
6989025  SYBASE DATALENGTH FUNCTION NOT TRANSLATED< STILL LIMITATION
6979464  TRIGGER INSERTED REFERENCE NOT TRANSLATED CORRECTLY IN MERGE STMT
6976180  INVALID SYNTAX CAUSES THE TRANSLATOR TO LOOP/ HANG SQL DEV
6970168  EMPTY STRING AND NULL COMPARISON NOT TRANSLATED CORRECTLY
6953135  CONVERT FUNCTION NOT CORRECTLY EMULATED
6944669  DOUBLE QUOTE LITERALS NOT TRANSLATED CORRECTLY
6927102  COLUMN NAME DATA CAUSED SYNTAX ERROR
6853149  NPE DISPLAYED WHEN CAPTURE INTO EXISTING MODEL CLICKED FOR THIRD PARTY DATABASES
6853074  IN CAPTURED MODEL SELECT ALL TABLES RIGHT CLICK,RENAME & REMOVE FOLDER NOT WORKI
5903238  UPGRADE EMULATION PACKAGE
4529352  OMWB USING WRONG TEMPORARY TABLES INSIDE THE STORED PROCEDURE
3545475  OMWB FAILS TO TRASLATE " TO ' IN SOME GROUP BY EXPRESSIONS
2694450  T-SQL PARSER FAILS TO PARSE 'AFTER UPDATE' TRIGGER
7038824  NTEXT TO NCLOB ON NON UNICODE DATABASE CORRUPT WITH CHINESE CHARACTER
7013620  QUICK MIGRATE: PROVIDE INCORRECT PASSWORD, NEVER PROMPTS, JUST FAILS
6994152  ORACLE_HOME ENVIRONMENT VARIABLE SOMETIMES HAS AN ADVERSE AFFECT ON MIGRATIONS
6356767  EXCEPTION OCCURING ON CONNECTING TO A SQL SERVER CONNECTION
2694526  OMWB DOES NOT STORE DEPENDENCY INFORMATION FOR FUNCTIONS

Oflline Data Move with Oracle XE

Noticed that sometimes I got an ORA-12519 error when using the oracle_ctl.bat file which calls SQL*Loader to upload each dat file into its Oracle table.

When I ran the script line by line I got no problems. Seemed like SQL*Loader was a little to fast for Oracle XE. Found this on the forums

Intermittent ORA-12519 error on 10g XE
http://forums.oracle.com/forums/thread.jspa?messageID=1252551

After setting
ALTER SYSTEM SET PROCESSES=150 SCOPE=SPFILE
and restarting the database, my oracle_ctl.bat file ran without issues.