Copy oracle 12c database to another machine

  1. Jrdnoland

    Jrdnoland Newly Initiated

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    Akron Ohio
    My work computer has a Oracle 12c server set up that contains data for our LIMS application. I'm trying to make a copy of this complete database, which I have full access to, and replicate its structure and data on my home computer.

    I have used dbForge Studio for Oracle to export all the data to Excel files.

    On my Home computer I installed Oracle 12c Home version.

    I can't seem to find step by step instructions of how to make this happen, at least ones that I can understand and follow.

    What steps can I take to make this happen? Free tools, tutorials, instructions?

    Thanks!
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,750
    Likes Received:
    382
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    "I have used dbForge Studio for Oracle to export all the data to Excel files."

    Why? Data pump is designed for this task, if you have access to copy files why are you not familiar with data pump? Use expdp to export the entire database to one or more dump files (there are PLENTY of websites that show how to do this) then transfer the .dmp files to a flash drive so you can import them into your local database.
     
  3. Jrdnoland

    Jrdnoland Newly Initiated

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    Akron Ohio
    I do not have a local database, I was hoping to make one from the remote server database and then import the data to it.
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,750
    Likes Received:
    382
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    With the tools that you're using there is no chance of that succeeding. You need to use the Database Configuration Assistant to create a new database for you on your computer. Once that's done you will then need to export the database from the source server and transfer the resulting .dmp file to your computer, where you will use Data Pump import to create the users and tables and load the data. There are no shortcuts to this given that your database is on Windows and the source database most likely is on UNIX or Linux. The following links will guide you:

    Create the database --
    https://www.dummies.com/programming...stant-dbca-to-create-databases-in-oracle-12c/

    Export the data --


    Import the data --
    How to import an Oracle database from dmp file and log file ...https://stackoverflow.com/.../how-to-import-an-oracle-database-from-dmp-file-and-lo...
     
  5. Jrdnoland

    Jrdnoland Newly Initiated

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    Akron Ohio
    Thank you for taking the time to put this together. I will give it a try this weekend.
     
  6. fennycruz

    fennycruz Starter

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    Tehran
    I have an issue export full database using expdp export data pump utility then i got an error like operation generated oracle error 12154 please help me how can i find my error. Thank U
     
  7. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,750
    Likes Received:
    382
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Oracle provides a utility to report the error, the cause and a likely solution named oerr, found in $ORACLE_HOME/bin. Using that utility you will see:

    Code (SQL):
    oerr ora 12154
    12154, 00000, "TNS:could not resolve the connect identifier specified"
    // *Cause:  A connection TO a DATABASE OR other service was requested USING
    // a CONNECT identifier, AND the CONNECT identifier specified could NOT
    // be resolved INTO a CONNECT descriptor USING one OF the naming methods
    // configured. FOR example, IF the TYPE OF CONNECT identifier used was a
    // net service name THEN the net service name could NOT be found IN a
    // naming method repository, OR the repository could NOT be
    // located OR reached.
    // *Action:
    //   - IF you are USING LOCAL naming (TNSNAMES.ORA file):
    //      - Make sure that "TNSNAMES" IS listed AS one OF the VALUES OF the
    //        NAMES.DIRECTORY_PATH parameter IN the Oracle Net profile
    //        (SQLNET.ORA)
    //      - Verify that a TNSNAMES.ORA file EXISTS AND IS IN the proper
    //        directory AND IS accessible.
    //      - CHECK that the net service name used AS the CONNECT identifier
    //        EXISTS IN the TNSNAMES.ORA file.
    //      - Make sure there are no syntax errors anywhere IN the TNSNAMES.ORA
    //        file.  Look FOR unmatched parentheses OR stray characters. Errors
    //        IN a TNSNAMES.ORA file may make it unusable.
    //   - IF you are USING directory naming:
    //      - Verify that "LDAP" IS listed AS one OF the VALUES OF the
    //        NAMES.DIRETORY_PATH parameter IN the Oracle Net profile
    //        (SQLNET.ORA).
    //      - Verify that the LDAP directory server IS up AND that it IS
    //        accessible.
    //      - Verify that the net service name OR DATABASE name used AS the
    //        CONNECT identifier IS configured IN the directory.
    //      - Verify that the DEFAULT context being used IS correct BY
    //        specifying a fully qualified net service name OR a FULL LDAP DN
    //        AS the CONNECT identifier
    //   - IF you are USING easy CONNECT naming:
    //      - Verify that "EZCONNECT" IS listed AS one OF the VALUES OF the
    //        NAMES.DIRETORY_PATH parameter IN the Oracle Net profile
    //        (SQLNET.ORA).
    //      - Make sure the host, port AND service name specified
    //        are correct.
    //      - Try enclosing the CONNECT identifier IN quote marks.
    //
    //   See the Oracle Net Services Administrators Guide OR the Oracle
    //   operating system specific guide FOR more information ON naming.
     
    All of this is found in the online Oracle documentation which is easily found using google.com.

    What all of that is telling you is that you are trying to use a TNS configuration that does not exist on the server you are using. Check the tnsnames.ora file for the valid aliases configured for your installation.
     
    fennycruz likes this.