Results 1 to 12 of 12
  1. #1
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Unanswered: ANY SQL Server DBA's out there?

    I need a 3rd party vendor to do a restore of database from their box to one on my network

    when I talked to their DBA they weretalking about schema's, partitions, etc

    I just want to do a dump and restore.....

    What's the correct terminology?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Come on...some one's gotta be hiding under a rock
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    You will probably have more success if you ask in the SQL Server forum instead of the Oracle forum...

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by shammat
    You will probably have more success if you ask in the SQL Server forum instead of the Oracle forum...

    Problem is.....I know most (if not all) of the regulars over there...and they don't know Oracle

    So, Let me ask this

    What would I have to do to get a copy of a database from 1 server to another
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >What would I have to do to get a copy of a database from 1 server to another
    It depends.
    If the task is to move an Oracle schema (SQL Server database) from 1 server to another, it is relatively straight forward.
    But it is version dependent.
    What version now holds the data?
    What is the destination Oracle version?
    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.

  6. #6
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by Brett Kaiser
    What would I have to do to get a copy of a database from 1 server to another
    From Oracle to SQL Server? or the other way round?

    I usually use a plain text export to move data between different servers and/or DBMS

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by shammat
    From Oracle to SQL Server? or the other way round?

    I usually use a plain text export to move data between different servers and/or DBMS

    wouldn't that be very inefficient?

    in any case, it's Oracle to Oracle.


    Company Contracted out...and now in the midst of decommisioning...they need to retain the data for legal reasons...so they want to bring the data in-house so they can do ad-hoc reporting when they need to

    So a Schema...is a SQL Server database?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool O2o

    If it's Oracle to Oracle, all you need is a 'full' export file from the source database.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Quote Originally Posted by LKBrwn_DBA
    If it's Oracle to Oracle, all you need is a 'full' export file from the source database.

    Except when source version is greater than destination version.
    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.

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I believe both are 10g

    What's a full file?

    Is that like a native backup?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Think of Import/Export as a BCP. Instead of working on single tables, Import/Export can work on sets of tables including all indexes and constraints. The utilities are called exp and imp. At a command prompt, type in
    Code:
    exp help=y
    and most of your questions should be answered.

    In Oracle, there is a single database per instance. But instance in SQL Server, and Instance in Oracle mean slightly different things. In Oracle, a Database Instance is the memory structures that are created when the database is started up. In SQL Server, an Instance is the installation AND the memory structures.

    You can run multiple "databases" in an Oracle database by separating them by schema, but this makes recovery considerably more difficult to coordinate. In SQL 2005, SQL Server treats the concept of Schema (with respect to database) exactly like Oracle (or at least as exact as you will need). Does this cover it a bit better for you?

  12. #12
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    If it is about only one schema that is to be moved from one database to another, everything is rather simple: from operating system command prompt which can access the "old" data run
    Code:
    exp old_username/its_password@old_database_alias file=my_data.dmp
    Once it is done, import this file into the target database. OS command prompt again:
    Code:
    imp new_username/its_password@target_database_alias file=my_data.dmp full=y
    Though, things might get complicated. Anyway, you can always try it and see what happens. If something goes wrong, just drop and recreate the "new_username" user/schema and start over.

    This is how it "really" might look like: export Mike's data first:
    Code:
    C:\>exp mike/lion@ora10g file=my_data.dmp
    
    Export: Release 10.2.0.1.0 - Production on Uto Lis 6 22:24:18 2009
    
    Copyright (c) 1982, 2005, Oracle.  All rights reserved.
    
    
    Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
    With the Partitioning, OLAP and Data Mining options
    Export done in EE8MSWIN1250 character set and AL16UTF16 NCHAR character set
    . exporting pre-schema procedural objects and actions
    . exporting foreign function library names for user MIKE
    . exporting PUBLIC type synonyms
    . exporting private type synonyms
    . exporting object type definitions for user MIKE
    About to export MIKE's objects ...
    . exporting database links
    . exporting sequence numbers
    . exporting cluster definitions
    . about to export MIKE's tables via Conventional Path ...
    . . exporting table                           DEPT          4 rows exported
    . exporting synonyms
    . exporting views
    . exporting stored procedures
    . exporting operators
    . exporting referential integrity constraints
    . exporting triggers
    . exporting indextypes
    . exporting bitmap, functional and extensible indexes
    . exporting posttables actions
    . exporting materialized views
    . exporting snapshot logs
    . exporting job queues
    . exporting refresh groups and children
    . exporting dimensions
    . exporting post-schema procedural objects and actions
    . exporting statistics
    Export terminated successfully without warnings.
    Now import it into Brisime's schema:
    Code:
    C:\>imp brisime/utorak@ora10g file=my_data.dmp full=y
    
    Import: Release 10.2.0.1.0 - Production on Uto Lis 6 22:24:34 2009
    
    Copyright (c) 1982, 2005, Oracle.  All rights reserved.
    
    
    Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
    With the Partitioning, OLAP and Data Mining options
    
    Export file created by EXPORT:V10.02.01 via conventional path
    
    Warning: the objects were exported by MIKE, not by you
    
    import done in EE8MSWIN1250 character set and AL16UTF16 NCHAR character set
    . importing MIKE's objects into BRISIME
    . . importing table                         "DEPT"          4 rows imported
    Import terminated successfully without warnings.
    Besides EXP HELP=Y and/or IMP HELP=Y command prompt help, documentation is available at http://tahiti.oracle.com. Search for "Original export and import" in the "Utilities" book (I can't post exact link as the site is currently unavailable).

Posting Permissions

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