Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2003
    Location
    Sacramento, CA
    Posts
    28

    Unanswered: Copy Production to Test

    Hello,
    I am new to Oracle (experienced in SQL Server mainly). I have to say going from SQL Server to Oracle
    has been painful to say the least. But I am not giving up because I am now working for a company
    that is using Oracle and I need to learn it. So I thought I would start with a small issue first before tackling
    more difficult things.
    So I am trying to copy our Production database called CPSPROD over to an existing Test database
    called CPSTest. I am using the Oracle SQL Developer to do a database COPY. The copy process errors
    out on various objects but most seem to copy over. I am thinking maybe the errors are happening
    because there is already data and objects in the CPSTest database and is causing some kind of conflict
    when copying the Production data over.

    So I want to know is there a way I can completely clear out the CPSTest database first before I begin
    the Database COPY from CPSPROD? CPSProd and CPSTest are located on the same server.

    In SQL Server this would be so easy. Just backup to a single backup file, then restore from that file
    with the option to overwrite the destination.

    Any help would be much appreciated.

    Thanks,
    David

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    What you call a "database" (in SQL Server) is, I presume, a "schema" in Oracle.

    If that's so, here's what I do: I have a script which creates CPSTEST user / schema (schema is a user along with his objects - tables, views, procedures, ...). Whenever I want to copy production to test, I drop CPSTEST and create it once again (CREATE USER CPSTEST ...). Then I export (EXPDP, export data pump) CPSPROD and import (IMPDP, import data pump) it into CPSTEST.

    It *might* require some additional actions (such as granting certain privileges to another user's objects and similar), but - generally speaking - it is quite an easy task.

    Note that I'm not a DBA but a developer, so DBAs might have another, better approach. Wait a little bit more, someone of them might respond.

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    when all else fails Read The Fine Manual

    http://docs.oracle.com/database/121/...htm#SQLRF01811
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    "Database" in Oracle and SQL Server are actually pretty much the same thing, with the exception that the system table space is not a part of a SQL Server database. After that, there are a lot of analogues. The main one being that the "database" is generally the unit of backup/restore.

    VbMan, go with LittleFoot's suggestion, unless there is a reason you need to restore objects outside the particular schema that application uses (this is fairly rare, in my experience). The Export and Import utilities are a lot like the SQL Server BCP utility, but with a lot more capability. It can export all objects in a schema (table definition, packages, functions, etc.).

  5. #5
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by MCrowley View Post
    "Database" in Oracle and SQL Server are actually pretty much the same thing, with the exception that the system table space is not a part of a SQL Server database.
    No, that's not the case.
    A "database" in Oracle is a complete new Oracle instance (separate processes, separate tablespaces, separate users, ...) unless you are talking about the new "pluggable database" concept which was introduced in 12c
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I thought in Oracle, the concept of instance and database were separate with the instance controlling the memory structures and processes. As for tablespaces, SQL Server has filegroups at the database level. The security model is different between the two, of course.

  7. #7
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by MCrowley View Post
    I thought in Oracle, the concept of instance and database were separate
    They are somewhat separate concepts, but for all practical usages (with RAC being the exception) you can use the terms "database" and "instance" as synonyms in Oracle. If you create a new database in Oracle (e.g. through dbca - Database Configuration Assistant) you do wind up with a new instance. In nearly all cases where you create a "database" in SQL Server you would create a schema in Oracle.

    From the manual:
    Quote Originally Posted by The Oracle Manual
    A database instance is a set of memory structures that manage database files. A database is a set of physical files on disk created by the CREATE DATABASE statement. The instance manages its associated data and serves the users of the database.
    As for the tablespaces: I just mentioned them to make the point clear that two database instances in Oracle don't share anything, including the tablespaces. I know that the equivalent is a filegroup.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •