2 Replies Latest reply on Feb 29, 2012 8:46 PM by orcldeveloper-Oracle

    Migration from of database and app from SQL server 2005 to Oracle 10g

      Hello Every body,

      Lately, I have been requested to migrate one inhouse developed hub application from SQL server 2005 to Oracle 10g. The objective is to move application and also the database to Oracel 10g.

      Here is the current platform:

      OS Win 2008 64 bit
      JDK 1.4.2
      JBoss 3.2.5
      EJB 2.1

      My question is what kind of issues you guys see in above upgrade. As far I know, Schema and Database migration can be done by Oracle workbench. Oracle 10g supports JDK 1.4. Oracle 10g also supports EJB 2.1.

      Anything, I need to take into consideration or any risks or problems. I would like to list down all the risks, and accordingly, I am thinking of start the upgrade.

      Thanks in advance.

      Zeeshan Qureshi
        • 1. Re: Migration from of database and app from SQL server 2005 to Oracle 10g
          Hi Zeeshan,
          Migrating the data from SQL*Server to Oracle should be straightforward using the SQL*Developer migration workbench.
          It will migrate your data and stored procedures but I advise the following -

          - do the migration offline. Online migration is only intended for very small databases. Making an offline migration gives you more control over how it is done and gives you more control if anything does go wrong.

          - make sure you carry out a dummy migration before doing it in production. Data migration is usually okay but there can be problems migrating stored procedures depending how complicated they are and may need manual intervention to re-write.

          - have you considered migrating to Oracle 11g ? Oracle 10.2 is now in the process of being desupported and will have less support for fixes and patches than 11g in the future.

          I've no experience of migrating the applications so you will have to wait for someone who has migrated those type of applications to get back to.
          Alternatively, check if there are any forums for those specific software products and see if they can help.

          • 2. Re: Migration from of database and app from SQL server 2005 to Oracle 10g
            In general the Java/J2EE application needs work in the following categories:

            1. Connection Settings: Use Oracle JDBC drivers, create new data sources, connection pools and what not. Disable AUTOCOMMIT for Oracle JDBC Connections.
            2. For EJBs, you should regenerate the entity beans because some object names/column names might have changed in oracle.
            3. Any custom SQL that is in use in the EJBs will have to be ported to Oracle just as you would do in stored procedures and other applications.
            4. For Java front-ends, if you are using callouts to stored procedures and expecting result sets then they will require some changes. You need to modify the stored procedure call signature to include the REF CURSOR variables and process them.
            5. Changes to any SQL Server specific database functions manipulating character/date data will be required.
            6. CLOB/BLOB/XML apis are different across databases so if you are using those then focus on them as well.
            7. Retreiving Auto/generated keys from database also may need changes from what I have seen.

            Hope this helps..



            NOTE: Not sure why but my posts from yesterday are not visible today. Even worse is that I can see them in one browser (FireFox) but not in Internet explorer. Crazy browser day I am having.