Oracle logical backup... for use in MySQL or SQL Server

Posted on 2014-08-12
Last Modified: 2014-08-12
Question: Is logical backup output script is only for use in Oracle only, or it could be prepared for use in MySQL or SQL Server when backup is being produced in Oracle?

If it is for use in Oracle only, are there utilities with MySQL, SQL Server, etc. to modify the backup script before it is executed in MySQL, SQL Server?

Thank you.
Question by:Mike Eghtebas
    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    If you are referring to a script that has a TON of DDL and DML statements, then it is probably database specific.

    How a table/index/key is created is probably product specific.

    Based on your previous question on this topic:
    What exactly are you trying to do?

    If someone provided you an Oracle backup, you'll need Oracle to recover.

    If someone create a script with a TON of create table/index scripts with even MORE INSERT statements, you'll need to port them.  I don't know of a tool that will to this.

    It gets worse if there are stored procedures/functions/triggers/etc...
    LVL 33

    Author Comment

    by:Mike Eghtebas
    Thank you for your good comments.

    Here is the situation.

    The client has a Oracle database with number of tables. I want a backup script to create these tables with its data content less PK, FK, Triggers, etc.

    Why; I am only interested in their data content. I am figuring that after I execute the script and created the tables even without PKs and FKs I can do what like to do with the data. It is read only situation and data will not be modified.

    Of course if PKs and FKs could be included in the original script, that will be great because the script will be identical in MySQL for joins (I suppose).

    As far as data type is concerned, that also could be handled with a global find/replace actions.

    If a script described above not possible, then the owner of the database possibly could write an stored proc to export the tables as table1.txt, table2.txt (table1 or table2 are oracle table names).

    Now, I could easily (I suppose) could upload them to MySQL or SQL Express. With entity diagram printout at hand, if necessary, I could install the required PKs and FKs.

    Do you see any chance of success in either of above methods? With this background, how would you suggest it should be done knowing that,

    Oracle will not be purchased.
    It is a short fused project (quick and dirty) but reliable.

    Thanks again,

    LVL 76

    Accepted Solution

    How much data are you talking about?

    Oracle XE is free.  One CPU, 11G max size and a few other restrictions that you can find in the License Agreement but still free.

    Never used it but it looks like MySQL has a migration tool that may do everything:

    If that doesn't work:
    Check out dbms_metadata.get_ddl in Oracle for the tables and object DDL.  Then a global search/replace would probably work to pre-create the tables.

    For the data, you will likely need a a text file (fixed width or delimited) or a generated script of INSERT statements.

    I would look at Oracle to Oracle XE.  then an export/import would be pretty simple/easy.
    LVL 33

    Author Closing Comment

    by:Mike Eghtebas
    Thank you very much,

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    Via a live example, show how to shrink a transaction log file down to a reasonable size.
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now