Importing full database....can i create a new
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Importing full database....can i create a new

  1. #1
    Join Date
    Dec 2000
    Posts
    255
    Hi All

    I have a database on my NT server named "ndb". I have exported the entire database into a file using command....
    exp system/manager file=ndb1 log=ndb1 full=y

    Now I have to import it on my win2000 machine. On that machine I already have a default database. This exported database I have to set up as a new database. Please tell me what next I have to do. I know the following steps.. Please tell me whethere they are correct or not ....

    1) I have to create a new ini.ora file first
    2) then create a new sid with ORADIM utility and
    3) set oracle_sid=ndb on command prompt
    4) then import database using imp system/manager file = ndb1 log=ndb full=y ignore=y

    Since I set oracle_sid=ndb on my machine which does not exist physically.. It does not recognise the system user. How do I do it. How can I create a new database out of the export file.

    Thanks in Advance
    Amol

  2. #2
    Join Date
    Feb 2001
    Location
    Adelaide, Australia
    Posts
    159
    Amol,

    Creating the sid and an init.ora is not enough.

    You need to create a database first!!

    Try looking up the 'Create database' command.

    Check out your documentation or training docs for information on how to do this.

    Good luck!

  3. #3
    Join Date
    Dec 2000
    Posts
    255
    Hi

    I have run the create database script and gave the dbf and log files the same name as that of in earlier database . (Is it ok if I give different names ? ) Now do I need to run the catalog.sql and catproc.sql to created the database or will it be formed from import ?

    Amol


  4. #4
    Join Date
    Sep 2001
    Posts
    112
    you will need to run the scripts you mentioned yes.

  5. #5
    Join Date
    Feb 2001
    Location
    Adelaide, Australia
    Posts
    159
    you can create with a different name if you wish.

    Yes, you must run catalog and catproc as sysdba before importing the full export as system.

    You should make sure you log your import to a file.
    ALSO - ensure that the tablespace creation commands will work - i.e. you have the same file paths available as the previous db and enough disk space.
    If not you must pre-create your tablespaces.

    Rgds

  6. #6
    Join Date
    Dec 2001
    Posts
    1

    Similar problem

    Hi,

    I have a similar problem. I exported a production database with option full=y.

    I have a development and a test environment. I would like to import data from my exported file to these dev and test instances. The production instance is name DB_PROD. But the dev and test isntances are respectively DB_DEV and DB_PROD.

    How can I import data form the export to these instances. They have the same table structures but different tablespaces.

    Thanks,
    Stevens

  7. #7
    Join Date
    Feb 2000
    Posts
    175
    Hi,

    Set the SID=to the database to be loaded into (DB_DEV or DB_TEST I presume)
    If the tables already exist then setting the ignore=Y flag should ignore any create error messages. If the tables do not already exist then they will be created in the default tablespace of the user identified by the touser=username flag.
    if the tables already exist in the database you may need to truncate them to remove any data within them. Then import the data for each users schema using the ignore flag set to Y

    eg

    imp system/manager file=exp.dmp log=exp.log fromuser=username touser=username ignore=y

    Cheers
    Moff

  8. #8
    Join Date
    Oct 2001
    Posts
    83
    To important your DB, you have to create a database having the same structure (same tablespaces... So if you have 4 TS in you old DB, you have to create them in your new. The loaction or the number of file behind the DB don't matter).

    Hope this helps

  9. #9
    Join Date
    Nov 2000
    Posts
    224
    Situation where you have to replicate the database from EXPORT dump, IMPORT is 2 step process,

    Prior to that, assuming you have Database and required Tablespace created,

    Step 1:

    imp system/manger file=EXPDAT.DMP INCTYPE=SYSTEM

    Step 2:

    imp system/manger file=EXPDAT.DMP INCTYPE=RESTORE


Posting Permissions

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


Click Here to Expand Forum to Full Width