This discussion is archived
7 Replies Latest reply: Aug 21, 2010 1:00 AM by mkirtley-Oracle Branched to a new discussion. RSS

Sql server migration to oracle -Sample vs actual dump

Hesh Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points