Migrating data from SQL Server 2008 R2 to Oracle 11g XE using Oracle SQL Developer 4.0.2. For testing purposes both database systems are hosted on the same box.
1. First create a connection to the SQL Server database. (See
the section "Configuring the Environment" for setting up the third party JDBC drivers)
2. Create a connection to the Oracle system using the "System" account.
3. Create a user account in Oracle for the migration (eg: "migration_repo"):
CREATE USER migration IDENTIFIED BY migration
DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;
GRANT CONNECT, RESOURCE, CREATE VIEW, CREATE PUBLIC SYNONYM
TO migration WITH ADMIN OPTION;
GRANT ALTER ANY ROLE, ALTER ANY SEQUENCE,
ALTER ANY TABLE, ALTER TABLESPACE, ALTER ANY TRIGGER,
COMMENT ANY TABLE, CREATE ANY SEQUENCE, CREATE ANY TABLE,
CREATE ANY TRIGGER, CREATE ROLE, CREATE TABLESPACE,
CREATE USER, DROP ANY SEQUENCE, DROP ANY TABLE,
DROP ANY TRIGGER, DROP TABLESPACE, DROP USER,
DROP ANY ROLE, GRANT ANY ROLE, INSERT ANY TABLE,
SELECT ANY TABLE, UPDATE ANY TABLE
TO migration;
(Note: the above was taken from
this tutorial.)
4.Create a connection to the newly created user account. Right click the connection and choose Migration Repository -> Associate Migration Repository. If the following error appears "try deleting repository before creating repository" then create a new TableSpace and associate the migration account to the TableSpace. This error sometimes occurs when the TableSpace associated with the user is full (see
here for details).
CREATE TABLESPACE <tablespace_name>
DATAFILE 'datafile_name.dbf'
SIZE 20M AUTOEXTEND ON;
5. Open the SQL Server connection and right click on the database to migrate. Choose "Migrate to Oracle".
6. Follow the wizard as show in the
Youtube tutorial given here. You will need to setup a migration script directory to store all the scripts. This directory will be used in later steps and will be referred to as "migration script directory".
7. In the "Convert" stage of the wizard change the UNIQUEIDENTIFIER mapping from CHAR[36] to RAW[16]
8. In the "Translate" stage only ALL_CONSTRAINTS, ALL_VIEWS and ALL_PROCEDURES were chosen.
9. Click finish to generate the migration scripts and the bottom window panel should list the migration project. If this process fails try deleting the migration repository, and try again to associate the migration repository.
10. Right click "Converted Database Objects" and choose "Translate". This will take you back to the wizard but then it will skip some stages to take you to the "Translate" stage.
11. Select the bits that you need translated and click finish. This will try and convert the objects.
12. Right click "Converted Database Objects" again and this time choose "Generate Target". Select the Oracle System account connection created in step 3 as the connection. Click "Finish" to generate the new database.
13. Right click "Converted Database Objects" again and this time choose "Move Data" and set the mode to "Offline". Click "Finish" to generate the move data scripts.
14. For converting the GUIDS in SQL Server to Oracle RAW[16] some script changes are required. Open up Windows Explorer and navigate to the "migration script directory" created in step 6. Open the folder with the migration project name that was specified in the wizard. Open the "datamove" folder and drill down until you find a folder called "control". The "control" folder contains ctl scripts. You will need to modify all the scripts that contain GUID definitions so that they can be mapped to a RAW, this involves removing the "-" symbol in the GUID and passing it to the HEXTORAW function.
So the original text in the file:
organisation_id CHAR(32)
needs to be modified to:
organisation_id "HEXTORAW(REPLACE(:organisation_id ,'-',''))"
Note: If using a text editor such as NotePad++ the following regular expression can be used for the search and replace:
Find:
(.*)\sCHAR\(32\)
Replace:
$1 "HEXTORAW\(REPLACE\(:$1 ,'-',''\)\)"
15. This step is only applicable if you have BLOB columns. VARBINARY(MAX) fields in MSSQL are mapped to BLOB columns in Oracle. The migration tool dumps out the hex values in VARBINARY fields as text, oracle then imports this text into a temporary CLOB column which it then runs a stored procedure on to convert it to a BLOB. In the control folder find all the tables that may have BLOB columns and set the char(XX) to an appropriate value depending on how large the data is. Note that the column may have been renamed to SQLDEVELOPER_CLOB_X. See example below.
load data
infile 'data/DEMO.BinaryData.dat'
"str '<EORD>'"
into table DEMO.BinaryData
fields terminated by '<EOFD>'
trailing nullcols
(
binaryData_id "HEXTORAW(REPLACE(:binaryData_id ,'-',''))",
version ,
SQLDEVELOPER_CLOB_3 char(5564262)
)
Locate the "Orcale_loader.bat" file at the same level as the "control" folder and set the readsize parameter to an appropriate value for each table with a BLOB column.
sqlldr %username%/%password%@%tnsname% control=control\DEMO.BinaryData.ctl readsize=104857600 log=log\DEMO.BinaryData.log
16. Open up a command prompt as administrator. Navigate to the dated directory inside the "datamove" folder.
c:\Windows\System32>cd\
c:\>cd <migration script directory>\<project name>\datamove\<dated folder>
17. Run the following command. If the SQL Server database is hosted on the same box then use localhost for the server name. This command will dump all the data in all the tables into a directory called "data" nested within the above folder structure.
MicrosoftSQLServer_data <server name> <username> <password>
18. Run the following command. The service name will be XE for this scenario. This command requires that the command in step 15 has been run successfully, otherwise you may receive an "lfiopn failed for file (*.log)" indicating that the log folder is missing. A log folder contains details on how many rows were successfully imported and how many failed per table.
oracle_loader <service name> <username> <password>
