7 Replies Latest reply: Aug 21, 2010 3:00 AM by Mkirtley-Oracle Branched to a new discussion. RSS

    Sql server migration to oracle -Sample vs actual dump

    Hesh
      I am able to successfully migrate the sample data provided in the demo for 'offline captured' option. My original SQL-server file is with extension .BAK which I am able restore in SQL server. How can I create the file in the same format as sample from SQL server data base? in order to simulate the same steps for migration to oracle..

      demo


      Thanks,
      Hesh.
        • 1. Re: Sql server migration to oracle -Sample vs actual dump
          Mkirtley-Oracle
          Hesh,
          You need to generate and run the offline capture scripts for your SQL*Database.
          The following has some details -

          http://www.oracle.com/technology/obe/hol08/sqldev_migration/mssqlserver/viewlets/sqlserver_capture.html

          For further detail have a look at the documentation -

          Oracle® SQL Developer
          User’s Guide
          Release 2.1

          Chapter 3 - Migrating Third-Party Databases

          and the section -

          2.9.1 Transferring the Data Offline

          The documentation is available from -

          http://download.oracle.com/docs/cd/E15846_01/index.htm

          If this is not the information you need then please detail the steps you have made during your migration and the errors you have.

          Regards,
          Mike
          • 2. Re: Sql server migration to oracle -Sample vs actual dump
            Hesh
            Thanks mike..

            For the test data provided by oracle corporation (link from my original post) I am able to migrate successfully..but what my problem is to generate SQL server back up dump which I am getting with the extension .Bak which I am not able to migrate to Oracle as SQL developer migration work bench is asking for .OCP extension file . How to generate .OCP file from SQL server ?

            Thanks,
            Hesh.
            • 3. Re: Sql server migration to oracle -Sample vs actual dump
              Mkirtley-Oracle
              Hesh,
              I am still not sure what you mean by using a .BAK file.
              The OCP file is generated from SQL*Developer as follows -

              2.6.2 Offline Capture
              To perform an offline capture of an IBM DB2, MySQL, Microsoft SQL Server, or
              Sybase Adaptive Server database, you create a set of offline capture scripts, run these
              scripts outside SQL Developer to create the script output (a dump of the third party
              metadata tables), and load the script output (the .ocp file containing the converted
              model) using SQL Developer.
              ■ To create the script file (a Windows .bat file or a Linux or UNIX .sh file) and
              related files, click Tools, then Migration, then Third Party Database Offline
              Capture, then Create Database Capture Scripts.
              When this operation completes, you are notified that several files (.bat, .sql, .ocp)
              have been created, one of which is the controlling script. You must run the
              controlling script (outside SQL Developer) to populate the object capture
              properties (.ocp) file with information about the converted model.
              ■ To load the converted model from the object capture properties (.ocp) file
              generated by the offline capture controlling script, click Tools, then Migration,
              then Third Party Database Offline Capture, then Load Database Capture Script
              Output.

              This is all detailed in the SQL*Developer User Guide referred to earlier.
              Have you referred to the documentation ?
              If you can explain again exactly what you are trying to do and the steps you have done so far then we can help further.

              Regards,
              Mike
              • 4. Re: Sql server migration to oracle -Sample vs actual dump
                Hesh
                Thanks Mike for your detail explanation...

                1) I have generated control script (for windows) 'OMWB_OFFLINE_CAPTURE' and run it, I didn't get activated the option 'Local data base capture script output' !
                2) Do we need to do anything at SQL server DB side? before doing this operation?
                3) Do we need any connection establishment between SQL server and Oracle SQL developer before generating control file?

                Please help...

                Thanks,
                Hesh.
                • 5. Re: Sql server migration to oracle -Sample vs actual dump
                  Mkirtley-Oracle
                  Hesh,
                  These are the steps you should do to capture the SQL*Server database offline -

                  1. Create the database capture scripts - Tools - Migration - Third Party Database Offline Capture - Create Database Capture Scripts.

                  Choose a directory where you are going store the generated files, for example - D:\msql_mig

                  2. Go to the directory where you stored the files D:\msql_mig. Run the OMWB_OFFLINE_CAPTURE from a command line as detailed in the script -

                  OMWB_OFFLINE_CAPTURE login_id password database_name server_name

                  3. If this is successful it will generate the files needed for the capture.

                  4. Then go to SQL*Developer and run the scripts to capture the database -

                  Tools - Migration - Third Party Database Offline Capture - Load Database Capture Script Output

                  - choose the OCP file in the directory where you have the capture scripts - D:\msql_mig - and this will then run the scripts and create an entry in 'Captured Models'.

                  5. You then proceed as detailed in the documentation to complete the migration of the actual tables and data.

                  I hope this helps. It is what I followed on my systems and it worked as expected.

                  Regards,
                  Mike.
                  • 6. Re: Sql server migration to oracle -Sample vs actual dump
                    Hesh
                    Mike I have done these things..

                    at command prompt logged to the directory where the control file resides and entered the following..

                    OMWB_OFFLINE_CAPTURE login_id password database_name server_name ( given related to oracle data base)

                    I got this error message...
                    SQLState = 08001, NativeError = 2
                    Error = [Microsoft][SQL Server Native Client 10.0]Named Pipes Provider: Could no
                    t open a connection to SQL Server [2].
                    SQLState = 08001, NativeError = 2
                    Error = [Microsoft][SQL Server Native Client 10.0]A network-related or instance-
                    specific error has occurred while establishing a connection to SQL Server. Serve
                    r is not found or not accessible. Check if instance name is correct and if SQL S
                    erver is configured to allow remote connections. For more information see SQL Se
                    rver Books Online.
                    SQLState = S1T00, NativeError = 0
                    Error = [Microsoft][SQL Server Native Client 10.0]Login timeout expired
                    SQLState = 08001, NativeError = 2
                    Error = [Microsoft][SQL Server Native Client 10.0]Named Pipes Provider: Could no
                    t open a connection to SQL Server [2].
                    SQLState = 08001, NativeError = 2
                    Thanks again,
                    Hesh.
                    • 7. Re: Sql server migration to oracle -Sample vs actual dump
                      Mkirtley-Oracle
                      Hesh,
                      You are giving the wrong connection information -

                      OMWB_OFFLINE_CAPTURE login_id password database_name server_name ( given related to oracle data base)

                      This should relate to the SQL*Server database NOT the Oracle database.
                      You are trying to capture information about the SQL*Server database to be able to create an Oracle model to make the migration.

                      Replace the information with -

                      login_id - SQL*Server user such as sa
                      password - sa password
                      database_name - the SQL*Server database that you want to migrate to Oracle, for example pubs
                      server_name - the server name where SQL*Server is running.

                      Regards,
                      Mike