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.

No comments: