This content has been marked as final. Show 0 replies
Use SQL Developer. It can migrate data, stored procedures,triggers, views, tables,indexes etc. Changes to the application will also be required so don't forget that.1 person found this helpful
pnauduri wrote:Thanks for the reply.
Use SQL Developer. It can migrate data, stored procedures,triggers, views, tables,indexes etc. Changes to the application will also be required so don't forget that.
Also, what u mean by "changes to application"???
"Changes to Application" can mean anything like:1 person found this helpful
1. Changes to the database drivers, connection strings and other parameters.
2. Porting of SQL Server specific SQL statements to Oracle compatible SQL statements.
3. Handling of result sets
and so on..
You can get a thorough understanding about application+database migration in our book titled 'Migrating to the Cloud' : http://www.amazon.com/Migrating-Cloud-Oracle-Client-Modernization/dp/1597496472
There is a general perception that database platforms can be changed very easily but it is important to understand the impact of DB on everything else that is built around it.
Here is what I did to move our SQL Server 2008 to Oracle 11g.
(1.) I read this document by Oracle. The only doc there is on migration:
Migrating a Microsoft SQL Server Database to Oracle Database 11g
-- Problem with this method is that you need SQL Developer 2.1 (We used 18.104.22.168) for this. Latest versions (3.1) does not have the Migration > Third Party Database Offline Capture > Load Database Capture Script Output. This is weird, and this Oracle documentation is very poor because it does tell you the real SQLD version.
-- So, we download SQLDev 2.1 and I try to do this (offline method). This works with the supplied test data. But, since we have to create the offline scripts this is long method.
-- Then we used the "To view the steps for the online method, click here." option (i.e. http://st-curriculum.oracle.com/obe/db/hol08/sqldev_migration/mssqlserver/viewlets/sqlserver_online.swf)
This method uses a live connection to a SQL Server DB to do the whole migration process online. Problem with this method is, we get stuck at stuck at Step 7. Capture the SQL Server DB. When we select this option in our SQL Develer 2.1, nothing happens. I think this is because it supports only SQL Server 2005 and does not support 2008. I am not sure. I stop this method here. Anybody can point to why it would be great?
(3.) I use SQL Developer 3.1.07 and create a connection to the SQL Server DB.
I then right-click the connection and click Migrate to Oracle... and use the Migration Wizard. It is also not clear and straightforward. It creates a script file and does not do it on-line. Why is this? You have to run the script and then load the data separately.
(4.) After migrating the Oracle DB, I found that some procedures are invalid. On inspecting I get this this:
This is the SQL Server Original:
Migrated Procedure in 11g:
-- ======================================================== -- Author : xxxxxxx xxx -- Create date : 08 June 2011 -- Description : Get Agent Payment By BatchNo -- ======================================================== CREATE PROCEDURE [dbo].[AgentPaymentConfirmation_GetBy_BatchNo] ( @BatchNo int ) AS SET NOCOUNT ON; SELECT [Id] ,AgentId ,[BatchNo] ,PaymentMode ,Amount ,Data ,ExternalStatus ,[TimeStamp] FROM AgentPayment WHERE BatchNo = @BatchNo ORDER BY Id
My problem is, AgentPayment table does NOT have a DATA column both in the SQL Server and Oracle table.
PROCEDURE AGENTPAYMENTCONFIRMATION_GET_1 -- ======================================================== -- Author : xxxxxxx xxx -- Create date : 08 June 2011 -- Description : Get Agent Payment By BatchNo -- ======================================================== ( v_BatchNo IN NUMBER DEFAULT NULL , cv_1 IN OUT SYS_REFCURSOR ) AS BEGIN OPEN cv_1 FOR SELECT Id , AgentId , BatchNo , PaymentMode , Amount , Data , ExternalStatus , TIMESTAMP FROM AgentPayment WHERE BatchNo = v_BatchNo ORDER BY Id; END; ____________________ PL/SQL: ORA-00904: "DATA": invalid identifier PL/SQL: SQL Statement ignored
**** SCRIPT STARTED Session: DBO_ETA@orcl11gr1chan(1) 4-Jul-2012 13:28:31 **** DESC AGENTPAYMENT ==> Name Data Type ------------------------------ ------------------------------ ID NUMBER(19,0) NOT NULL ROWID_ CHAR(36 CHAR) NOT NULL AGENTID NUMBER(10,0) NOT NULL BATCHNO VARCHAR2(20 CHAR) NOT NULL PAYMENTMODE NUMBER(10,0) NOT NULL AMOUNT NUMBER(19,4) NOT NULL PAYMENTDATE DATE NOT NULL CARDTYPE VARCHAR2(20 CHAR) MERCHANTID VARCHAR2(50 CHAR) TRANSACTIONID VARCHAR2(50 CHAR) NOT NULL PAYMENTDOCUMENT BLOB(4000) PAYMENTDONEAT NUMBER(10,0) NOT NULL EXTERNALSTATUS NUMBER(10,0) NOT NULL DATECREATED DATE NOT NULL CREATEDBY CHAR(36 CHAR) NOT NULL DATEMODIFIED DATE MODIFIEDBY CHAR(36 CHAR) TIMESTAMP RAW NOT NULL SQL*Plus statement executed ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ **** SCRIPT ENDED 4-Jul-2012 13:28:32 **** ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
migrates to Oracle as
DECLARE @QueryString varchar(MAX)
How to solve these please???
Edited by: user12240205 on Jul 5, 2012 8:33 PM
Let me clarify some of the issues you have raised. But I will advice you to get some professional help with migration because:
1. You need to understand the architectural differences between SQL Server and Oracle. They are different databases.
2. Understand the datatype mappings.
3. Understand the complete migration process. Just using the OTN article as a migration guide will not be of much help because it just demonstrates how to use SQL Developer for this task.
4. Understand that 100% successful conversion to Oracle is not possible with any tools. There will always be shortcomings, functionality gaps, bugs etc. So there will be some amount of manual effort involved.
5. There are lot of other tasks involved in the migration that cannot be accomplished with SQL Developer.
Now getting to your questions:
1. In 3.1, there is migration option for 'offline database capture'. It is under the tools option. So in 3.1 we don't have a separate tab called 'Migration'.
2. Use 3.1 as it is much better than 2.1 in terms of capabilities.
3. Migration Wizard is the simplest way to migrate. Where are you getting confused?.
4. "Data" column seems to be in SQL Server stored procedure. If it is not there in the underlying table, why is it getting used in the SQL Statement in the procedure then?. I think we may have a 'reserved word' issue with the column name 'Data'. Will have to check that.
5. VARCHAR(@max) should map into VARCHAR2(4000) in Oracle. It may be a bug in SQL Developer. So during the data type mapping staging in migration wizard you can change the default mappings.
Another issue with the migration is that some tables, columns are missing. These tables and columns are not present in the SQL Server as well. It would be really good if we can identify these things by compiling the procedures in SQL server itself, but SQL developer does not have this facility.
Also, The Migration tools will indicate where you have to modify with the 'TODO' comment in the procedure or functions. These have to be modified. In order to find out procedures and functions specified by migration tool to be modified after migration use this query:
SELECT distinct name FROM USER_SOURCE WHERE upper(text) LIKE '%TODO%' order by 1;
First of all you need to identify a clean, fully functional SQL Server database to migrate. Make sure all dependent databases are also migrated. If you try to migrate a database that is not fully functional then you will need to put effort to identify issues in SQL Server first.