Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2004
    Posts
    6

    Unanswered: problems importing from 817 to 920:

    Hi,

    I’m trying to perform a manual database import/upgrade from Oracle 8.1.7 to 9.2.0 on an NT system.

    The export appeared to work fine, and the manual creation of an “empty” database also appeared to work fine.

    The import, however, is bombing spectacularly.

    my import statement is : C:>imp SYSTEM/password FILE=PRODTEST.dmp FULL=y LOG=myimp2.log

    the first few lines of the import log are shown below:

    I’ve set up a folder in “E:\ORACLE\ORADATA\PRODTEST” to house the data etc, but looking at the log, it looks like it’s trying to write to the PROD directory when it should be writing to the PRODTEST directory.

    Perhaps I’m misreading it, but that’s what it looks like to me. I’ve googled some of these errors (e.g. IMP-00017) and searched this site, and there are a few results returned, but none is particularly clear (e.g. I tried the IGNORE=y suggestion in the import statement, but this didn't do the trick).

    Am I correct in assuming that the import is trying to overwrite the PROD data instead of going to PRODTEST? If so, how is this fixed?
    Otherwise, any ideas as to what is going wrong here?

    Thanks!

    ************************************************** ************
    Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
    With the Partitioning, OLAP and Oracle Data Mining options
    JServer Release 9.2.0.4.0 - Production

    Export file created by EXPORT:V08.01.07 via conventional path
    import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
    export client uses WE8ISO8859P1 character set (possible charset conversion)
    export server uses WE8ISO8859P1 NCHAR character set (possible ncharset conversion)
    IMP-00017: following statement failed with ORACLE error 1119:
    "CREATE TABLESPACE "RBS" DATAFILE 'E:\ORACLE\ORADATA\PROD\RBS01.DBF' SIZE 2"
    "41172480 AUTOEXTEND ON NEXT 5242880 MAXSIZE 32767M DEFAULT STORAGE("
    "INITIAL 524288 NEXT 524288 MINEXTENTS 8 MAXEXTENTS 4096 PCTINCREASE 50) ONL"
    "INE PERMANENT EXTENT MANAGEMENT DICTIONARY"
    IMP-00003: ORACLE error 1119 encountered
    ORA-01119: error in creating database file 'E:\ORACLE\ORADATA\PROD\RBS01.DBF'
    ORA-27038: skgfrcre: file exists
    OSD-04010: <create> option specified, file already exists
    IMP-00017: following statement failed with ORACLE error 1119:
    "CREATE TABLESPACE "USERS" DATAFILE 'E:\ORACLE\ORADATA\PROD\USERS01.DBF' SI"
    "ZE 20971520 AUTOEXTEND ON NEXT 1310720 MAXSIZE 32767M DEFAULT STORA"
    "GE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 4096 PCTINCREASE 0) O"
    "NLINE PERMANENT EXTENT MANAGEMENT DICTIONARY"
    IMP-00003: ORACLE error 1119 encountered
    ************************************************** *************

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    how about removing full=y and using:
    fromuser=prod
    touser=prodtest
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Oct 2004
    Posts
    145

    Importing from 8i to 9i

    Full import is definitely not recommended.

    It will over write all your system procedures and will cause other problems. If you perform this, you must ensure all old files are removed. The errors you are encountering are due to old files not being cleaned up and it already exists.

    Proper migration procedure would be.
    1. extract all users, roles, synonyms, etc that are maintained in system tablespace.
    2. create new 9i instance
    3. create all users, roles (objects not dependent on user objects).
    4. import only users with data (as per suggested in previous thread, same user name can be used).
    5. create all synonyms, etc.

  4. #4
    Join Date
    Mar 2004
    Posts
    6
    >Apologies as I am an Oracle newbie, so my questions may be somewhat rudimentary.
    Full import is definitely not recommended.
    >I assume this means that full export is also not recommended.


    It will over write all your system procedures and will cause other problems. If you perform this, you must ensure all old files are removed. The errors you are encountering are due to old files not being cleaned up and it already exists.

    Proper migration procedure would be.
    1. extract all users, roles, synonyms, etc that are maintained in system tablespace.
    >Can this be done with the exp utility, or is there another way? If done with exp, what is the syntax?

    2. create new 9i instance
    >I believe I've done point 2.

    3. create all users, roles (objects not dependent on user objects).
    >is this done with the imp utility, or do you issue a collection of sql commands in the 9i instance that create copies of the users/roles that you find in the 8i instance?

    4. import only users with data (as per suggested in previous thread, same user name can be used).
    >again, I assume this is done w/ exp/imp utility, but I'm not clear on the syntax.

    5. create all synonyms, etc.
    >again, is this done "manuallY" through a collection of sql statements that mimic what is seen in the 8i database or is there another way?

    >Thanks for any help you can provide.

  5. #5
    Join Date
    Oct 2004
    Location
    Karachi
    Posts
    9
    I have imported my 8i data into 9i once. I did this with:
    imp userid=system/manager file=c:\abc.dmp full=y ignore=y commit=y
    what it did was, it created all the users & their privliges forcefully.
    Have you tried as The_Duck said: fromuser=prod
    touser=prodtest. I think ur problem should solve by this.
    I remember one thing, before ORACLE 8 the SIDs of the Databases were only of 4 characters like ORCL, so why I m still stuck to it. I know its not making any sense but try doing this, try giving ur ORACLE 9 database same SID as of ur old ORACLE.

  6. #6
    Join Date
    Mar 2004
    Posts
    6
    Quote Originally Posted by maliksarmad
    I have imported my 8i data into 9i once. I did this with:
    imp userid=system/manager file=c:\abc.dmp full=y ignore=y commit=y
    what it did was, it created all the users & their privliges forcefully.
    Have you tried as The_Duck said: fromuser=prod
    touser=prodtest. I think ur problem should solve by this.
    I remember one thing, before ORACLE 8 the SIDs of the Databases were only of 4 characters like ORCL, so why I m still stuck to it. I know its not making any sense but try doing this, try giving ur ORACLE 9 database same SID as of ur old ORACLE.
    Did it overwrite the existing 8i database?

  7. #7
    Join Date
    Oct 2004
    Posts
    145

    Response

    If you have a full export already and have trashed 8i do the following.

    1. Remove all database files (containers) from your system.
    2. Rebuild the 9i instance.
    3. Perform full import.
    4. Run upgrade scripts found in oracle_home/rdbms/bin directory usually u80902.sql, etc.
    5. Remove any unnecessary tablespaces, RBS, USERS, etc.

    Email me if you require more information.

  8. #8
    Join Date
    Jul 2003
    Posts
    2,296
    it seems you are not familiar enought with export/import:
    Code:
    http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96652/toc.htm
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  9. #9
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    The other problem is that it will try to recreate all the tablespaces that were in the old database, in the same locations. If you pre-create the tablespaces, your full import will work fine.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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