Monday, 7 October 2013

Convert SQLServer to Oracle using files - Part 4

This, the last part of a four part tutorial goes over the movement of data using files generated by Oracle SQL Developer.  In part 1 we generated the offline capture scripts to take to the SQL Server machine, unloaded the metadata, zipped it up and copied it back to out local machine. In part 2 we used SQL Developer to create a migration project and load the capture files into SQL Developer.  We then converted the metadata into its Oracle equivalent. In  Part 3, we were able to generate DDL and run this DDL against an Oracle database.
Looking at the data move scripts that we generated in an earlier part.  We need to zip up the files and copy them to the SQL Server machine to run.  Lets look at that now.  The images below show the files moved to our SQLServer machine.  We go into the main directory under data move and run the bat file MicrosoftSQLServer_data.bat.  This batch file takes a number of parameters

This script then unloads the data from the database for the databases selected earlier.  We can see the dat files in the image above.  Now, we just need to go and transfer the data to the Oracle database machine for loading.  We can go back out to the main datamove directory and zip up the entire directory including the scripts.  We then need to ftp that to the database machine.  
The files need to be unzipped on the machine and cd into the main directory until you find a file called oracle_loader.sh.

We can run the files as below.  The output below shows the exact output of running the Oracle_loader.sh script on the data we have taken from SQL Server.


[oracle@Unknown-08:00:27:c8:2a:1c 2013-10-08_00-05-16]$ sh ./oracle_loader.sh orcl blog blog
/scratch/datamove/2013-10-08_00-05-16/Northwind /scratch/datamove/2013-10-08_00-05-16
/scratch/datamove/2013-10-08_00-05-16/Northwind/dbo_Northwind /scratch/datamove/2013-10-08_00-05-16/Northwind

SQL*Plus: Release 11.2.0.2.0 Production on Mon Oct 7 18:58:42 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Table altered.
Table altered.
Trigger altered.
Trigger altered.
Trigger altered.
Trigger altered.
Trigger altered.
Trigger altered.
Trigger altered.
Table altered.
Table altered.
Table altered.
Table altered.
Table altered.
Table altered.
Table altered.
Table altered.
Table altered.
Table altered.
Table altered.
Table altered.
Table altered.
Table altered.
Table altered.
Table altered.
Table altered.
Table altered.
Table altered.
 Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL*Loader: Release 11.2.0.2.0 - Production on Mon Oct 7 18:58:43 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 1
Commit point reached - logical record count 2
Commit point reached - logical record count 3
Commit point reached - logical record count 4
Commit point reached - logical record count 5
Commit point reached - logical record count 6
Commit point reached - logical record count 7
Commit point reached - logical record count 8
Commit point reached - logical record count 9

SQL*Loader: Release 11.2.0.2.0 - Production on Mon Oct 7 18:58:44 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 1
Commit point reached - logical record count 2
Commit point reached - logical record count 3
Commit point reached - logical record count 4
Commit point reached - logical record count 5
Commit point reached - logical record count 6
Commit point reached - logical record count 7
Commit point reached - logical record count 8

SQL*Loader: Release 11.2.0.2.0 - Production on Mon Oct 7 18:58:44 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 49

SQL*Loader: Release 11.2.0.2.0 - Production on Mon Oct 7 18:58:44 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 53

SQL*Loader: Release 11.2.0.2.0 - Production on Mon Oct 7 18:58:45 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


SQL*Loader: Release 11.2.0.2.0 - Production on Mon Oct 7 18:58:45 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


SQL*Loader: Release 11.2.0.2.0 - Production on Mon Oct 7 18:58:45 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 64
Commit point reached - logical record count 77

SQL*Loader: Release 11.2.0.2.0 - Production on Mon Oct 7 18:58:45 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 4

SQL*Loader: Release 11.2.0.2.0 - Production on Mon Oct 7 18:58:46 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 64
Commit point reached - logical record count 128
Commit point reached - logical record count 192
Commit point reached - logical record count 256
Commit point reached - logical record count 320
Commit point reached - logical record count 384
Commit point reached - logical record count 448
Commit point reached - logical record count 512
Commit point reached - logical record count 576
Commit point reached - logical record count 640
Commit point reached - logical record count 704
Commit point reached - logical record count 768
Commit point reached - logical record count 832
Commit point reached - logical record count 896
Commit point reached - logical record count 960
Commit point reached - logical record count 1024
Commit point reached - logical record count 1088
Commit point reached - logical record count 1152
Commit point reached - logical record count 1216
Commit point reached - logical record count 1280
Commit point reached - logical record count 1344
Commit point reached - logical record count 1408
Commit point reached - logical record count 1472
Commit point reached - logical record count 1536
Commit point reached - logical record count 1600
Commit point reached - logical record count 1664
Commit point reached - logical record count 1728
Commit point reached - logical record count 1792
Commit point reached - logical record count 1856
Commit point reached - logical record count 1920
Commit point reached - logical record count 1984
Commit point reached - logical record count 2048
Commit point reached - logical record count 2112
Commit point reached - logical record count 2155

SQL*Loader: Release 11.2.0.2.0 - Production on Mon Oct 7 18:58:46 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 64
Commit point reached - logical record count 128
Commit point reached - logical record count 192
Commit point reached - logical record count 256
Commit point reached - logical record count 320
Commit point reached - logical record count 384
Commit point reached - logical record count 448
Commit point reached - logical record count 512
Commit point reached - logical record count 576
Commit point reached - logical record count 640
Commit point reached - logical record count 704
Commit point reached - logical record count 768
Commit point reached - logical record count 830

SQL*Loader: Release 11.2.0.2.0 - Production on Mon Oct 7 18:58:47 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 1
Commit point reached - logical record count 2
Commit point reached - logical record count 3
Commit point reached - logical record count 4
Commit point reached - logical record count 5
Commit point reached - logical record count 6
Commit point reached - logical record count 7
Commit point reached - logical record count 8
Commit point reached - logical record count 9
Commit point reached - logical record count 10
Commit point reached - logical record count 11
Commit point reached - logical record count 12
Commit point reached - logical record count 13
Commit point reached - logical record count 14
Commit point reached - logical record count 15
Commit point reached - logical record count 16
Commit point reached - logical record count 17
Commit point reached - logical record count 18
Commit point reached - logical record count 19
Commit point reached - logical record count 20
Commit point reached - logical record count 21
Commit point reached - logical record count 22
Commit point reached - logical record count 23
Commit point reached - logical record count 24
Commit point reached - logical record count 25
Commit point reached - logical record count 26
Commit point reached - logical record count 27
Commit point reached - logical record count 28
Commit point reached - logical record count 29

SQL*Loader: Release 11.2.0.2.0 - Production on Mon Oct 7 18:58:47 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 3

SQL*Loader: Release 11.2.0.2.0 - Production on Mon Oct 7 18:58:47 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 64
Commit point reached - logical record count 91

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
/scratch/datamove/2013-10-08_00-05-16/Northwind
/scratch/datamove/2013-10-08_00-05-16
[oracle@Unknown-08:00:27:c8:2a:1c 2013-10-08_00-05-16]$ 


We can now take a look at some data in the Oracle database by going to the dbo_northwind connection we made earlier and look at the data.


And thats it. In these four parts we have generated capture scripts from SQLDeveloper to unload metadata from SQLServer. In part two, we loaded the metadata and converted it into an Oracle version. In Part three, we generated the DDL and ran it creating the new Oracle users. In part 4, we unloaded the data and copied it to the oracle machine.  We then loaded it using the scripts we generated from Oracle SQL Developer.

Convert SQLServer to Oracle using files - Part 3

In part 1 we generated the offline capture scripts to take to the SQL Server machine, unloaded the metadata, zipped it up and copied it back to out local machine. In part 2 we used SQL Developer to create a migration project and load the capture files into SQL Developer.  We then converted the metadata into its Oracle equivalent.
In this episode we will try and generate DDL from our migration project.  Right now, We can see the Oracle objects in the Converted Database Objects node.
If we right click on Converted Database objects and choose generate, we can generate DDL to create the Oracle Schema and Objects.

The wizard appears again with the introduction screen.  Clicking next takes us directly to the Target database Screen.


Click on offline to choose generation of files.  For specifics of how the files get generated, click on advanced options

 You can select what way you want to generate your files, all in one file, a file per object type or a file per object. You can also choose the types of objects you want to generate and run.
 In this demo, I will just generate tables, data and supporting objects.   Clicking next  will take us to the data move page where we again choose offline to generate files.
 Choosing advanced options allows us to be specific about date masks and delimiters for data unload.
 Once we have chosen our options, we click next and review the summary.
 Finally, we click finish and the files are generated in the output directory we specified when setting up the project in part 2.
Now, Lets go see what we generated.  If we go to the output directory we specified in the project, we can see the list of files we generated.  Remember the options we chose for generation.
We also get the master.sql file opened in SQL Developer which looks like this


SET ECHO OFF
SET VERIFY OFF
SET FEEDBACK OFF
SET DEFINE ON
CLEAR SCREEN
set serveroutput on

COLUMN date_time NEW_VAL filename noprint;
SELECT to_char(systimestamp,'yyyy-mm-dd_hh24-mi-ssxff') date_time FROM DUAL;
spool democapture_&filename..log

-- Password file execution
@passworddefinition.sql

PROMPT Creating Role
@role.sql

prompt creating user Emulation
@@Emulation/user.sql

prompt creating user dbo_Northwind
@@dbo_Northwind/user.sql

prompt creating user dbo_pubs
@@dbo_pubs/user.sql

prompt Building objects in Emulation
@@Emulation/master.sql

prompt Building objects in dbo_Northwind
@@dbo_Northwind/master.sql

prompt Building objects in dbo_pubs
@@dbo_pubs/master.sql

Now, lets try and run this file and create the users and objects.  Firstly, we choose a connection to run the script.  This user must have the privileges to create users and all their ancillary objects.
We can run this script to create the users.  Notice the worksheet output showing the output of the files.
Once this is complete, we can create a connection in SQL Developer to one of the users created, dbo_Northwind, dbo_pubs and emulation.


Now, we have created the schema from the DDL which was generated.  In the next and final episode of this, we will visit the data move.  We will run the data move scripts on SQL Server and extract the data which we can load via SQL Loader or external tables.


Convert SQL Server to Oracle using files - Part 2

Ok, Now we have the files as generated and moved in part 1, we can now start SQL Developer to load the files. Start up SQL Developer  and create a connection with the following privileges: CONNECT, RESOURCE and CREATE VIEW.

When the connection is opened, right click on it and choose Migration Repository then Associate Migration Repository.  This will create the repository in the connection.

 Now, We can start the migration wizard. You can do this by either going to the tools menu and selecting migrate from the migration menu, or you can select the migrate icon from the migration project navigator.  The wizard will popup and you can walk through the steps as outlined below.
 Clicking the next button selects the repository page which we can choose the repository connection we just made.
 Next page and we need to create a project to hold the captured databases.
The output directory in the page above is the directory where any log files or generated files will be placed.  When we generate DDL or data move files, this is where they will get generated.  Next page is the capture page.  For using the files from Part 1, we need to choose offline which will then show the page below, which asks us to select the offline capture file.
 This offline capture file is in the zip file we brought over from SQL Server.  Browse to the sqlserver2008.ocp.  This file tells SQL Developer what to expect in the directory.  It will look for the databases that have been unloaded.
 When its selected, SQL Developer parses the files and shows you a list of the databases you ran the offline capture scripts for in Part 1.

 Choose both databases and click next.
 The next page shows a list of the datatypes of SQL Server on the left and a list of equivalent data types on the right.  You can choose a different type if you want and you can also create a new mapping by clicking on the "Add new Rule".
 The next page lists the objects to be translated.  Because we have not captured anything yet, the best we can do is to tell SQL Developer to translate everything.  We can come back later and choose specific  stored programs to convert and translate.

 At this stage, we can click proceed to summary and then finish once you review the summary page.
 When finish is pressed, SQL Developer will capture the database metadata from the files and convert it to its Oracle equivalent.

 When this completes, you will see a new node with the project name you chose earlier. If you click on it, you will get an editor on the right hand side with a summary of the data captured and converted.


Convert SQL Server to Oracle using files - Part 1

Many people want to migrate their SQL Server databases and do not have direct network access to the database. In Oracle SQL Developer, we can migrate from SQL Developer to Oracle using a connection  to SQL Server or  using files to extract the metadata from SQL Server and convert it to an Oracle equivilent.

Today, we'll show you how to use scripts to convert SQL Server.  First we need to start up SQL Developer and choose the Tools menu, then select Migration and Create Offline Capture Scripts

When the dialog appears, choose the SQL Server and the appropriate version you want.  You will also need to choose a directory to put the scripts into.
This will generate a set of files which we will need to move to our SQL Server machine to run.
So on disk, these look like this.
Now, we can zip this up and ftp it to the SQL Server machine you want to migrate, or in my case, I'll scp it to the machine.

Now, lets go to SQL Server and run the scripts against the SQL Server database.  Looking below, I have opened up a command window and created a directory called blog and moved the sqlserver.zip file into that directory.
Now, we have the scripts on the SQL Server box and ready to run.  Its important that when you run the scripts on a server, that you always run it from the same place.  The script which is run takes a number of parameters to run.
OMWB_OFFLINE_CAPTURE sa superuser_password databasename server

  OMWB_OFFLINE_CAPTURE sa saPASSWORD DBNAME_TO_CAPTURE SQLSERVER_SERVER  

This will unload the metadata from the database to flat files.  You need to run this script once for each database you want to migrate.  You'll see something like these as you go.


This is one run for the northwind database.  I've run this again for the pubs database and lets look and see what files exist now.
Now, we go up a directory and zip all this up so we can move it to the machine where we will translate it.
Now, we can move that zip file.  Take a look at it, it is very small in size for this demo, but even for a large system, we are only capturing the metadata structure of the database.  If you are working with a partner or SI, this is the file you will want to send them for analysis.

Ok, for those of you who are doing this right now, read on.

When you have the capture.zip file transferred, unzip it into a clean directory.  We will use SQL Developer on this to  convert these metadata files into DDL to create the new Oracle schema and the data move scripts which can be used to unload the data from SQL Server and load it into Oracle.


Now, we use SQL Developer to load this data.  We will need access to an Oracle database to create a schema to use as a repository. The repository is used to hold the source database information and the converted data.

The next post will walk through SQL Developer loading these files and converting the metadata to an Oracle equivalent.