Full exp 8.1.7 and Full imp 9i
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Full exp 8.1.7 and Full imp 9i

Hybrid View

  1. #1
    Join Date
    Feb 2005
    Location
    Luxembourg
    Posts
    28

    Full exp 8.1.7 and Full imp 9i

    Hi,

    i'm trying to make a migration from 8.1.7 to 9i. Both are on the same Machine, with Windows XP (please don't blame me...)

    It's my first attempt to make a full exp, so i'm guessing it's a dummy mistake, but HOW do i get the users into the new database?

    I've found different suggestions. Some are saying that i have to create the users, tablespaces and roles manually in 9i.
    Is that right? If yes, How can i do that, efficient?

    I tried to find the "create user" statements in the error.log
    (imp userid='sys/sys as sysdba' file=exp_8_1_7.dmp log=error.log full=y indexfile=index.sql show=y)
    but they aren't there.

    Here are my Import/Export statements :

    exp userid='sys/sys as sysdba' file=exp_8_1_7.dmp log=error.log full=y consistent=y

    imp userid='sys/sys as sysdba' file=exp_8_1_7.dmp log=error.log full=y ignore=y

    Any Idea of what i'm missing here?

    thanks,
    Stefan

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    why are you using exp / imp when you should be upgrading the database using the upgrade scripts.

    Anyway if they are on the same box you must pre-create the tablespaces as it will try and create and create them in the same place as the ones that already exist. Will all work after that.

    I presume that you are using the correct version of exp / imp for your versions as well

  3. #3
    Join Date
    Feb 2005
    Location
    Luxembourg
    Posts
    28
    davey, thanks for your reply!

    why are you using exp / imp when you should be upgrading the database using the upgrade scripts.
    Because it seems to me, that the full exp/imp way is the most flexible one.

    Could you please explain, what do you mean with "upgrade scripts"? Where can i found them?

    Anyway if they are on the same box you must pre-create the tablespaces as it will try and create and create them in the same place as the ones that already exist. Will all work after that.
    Ok, but how will the users and be created? Because the "create role" and "create user" statements aren't in the export file which we created from 8.1.7.

    I presume that you are using the correct version of exp / imp for your versions as well
    Yes, i'm using exp from the 8.1.7 database and imp from the 9i db.

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    exp / imp is by no means the most flexible (or correct way) to upgrade a database.

    Run the upgrade assitant program (will be in the start menu -> programs) area and it will do it for you.

    you sure you did a full export then? at the top of an export file you should have stuff like this

    Code:
    CREATE USER "ANONYMOUS" IDENTIFIED BY VALUES 'anonymous' DEFAULT TABLESPACE "XDB" TEMPORARY TABLESPACE "TEMP" ACCOUNT LOCK

  5. #5
    Join Date
    Feb 2005
    Location
    Luxembourg
    Posts
    28
    you sure you did a full export then? at the top of an export file you should have stuff like this
    yes, if full=y is the necessary argument then i did a full export.

    I am thinking, that this is a specific 8.1.7 behaviour! Could anybody confirm that?

    Because if i do a full export on version 9i (with the exact same statement as i used for the 8.1.7) i can find the "create user" statements on the .dmp file (like you postet, davey).

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Quote Originally Posted by buck
    I am thinking, that this is a specific 8.1.7 behaviour! Could anybody confirm that?
    No, it is not a specific 8.1.7 behaviour, I can confirm that. Full exp/full imp should create users on the new database without any problem. But you are not performing full import, that's it.

    Quote Originally Posted by buck
    imp userid='sys/sys as sysdba' file=exp_8_1_7.dmp log=error.log full=y indexfile=index.sql show=y
    The bold part in your command is your problem. With INDEXFILE= parameter you are telling imp to create you only the CREATE INDEX commands and that's what you see in your log file, all the other stuff is simply skipped by the imp utility. Remove your INDEXFILE= from your command and you'll be able to see the CREATE USERS command in the log file.

    And BTW, do not run imp or exp utilities by connecting as sysdba - that's a guaranteed way to problems...
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  7. #7
    Join Date
    Feb 2005
    Location
    Luxembourg
    Posts
    28
    Hi,

    the
    Code:
    indexfile=index.sql
    Code was just a try. I tried it also without this parameter. (only show=y ). But i couldn't find the "create user" statements in there also.

    jmodic: Could you give me an good example for the exp and imp statement?

    Do we have to create anything in 9i bevor we do the full import?

    Actually it's impossible that this simple statement is so tricky, but this is the 2nd day i'm trying to do the full exp/imp but it still doesn't work (and freaking me out, btw. )


    thanks!

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Code:
    (using 8i exp.exe)
    exp userid=system/manager file=exp_8_1_7.dmp compress=N log=error.log full=y consistent=y
    
    (using 9i exp.exe)
    imp userid=system/manager file=exp_8_1_7.dmp log=error.log full=y ignore=y
    Those two should be just fine.

    Do you need to precreate anything before importing? As davey23uk allready mentioned, in your case you have to precreate all your tablespaces manualy because you are importing to the same box where the original 8i database exists and you do not want the imp to overwrite your 8i datafiles. Apart from that, imp will take care for everything else.

    Give it a try (maybe with show=Y), but this time not with SYSDBA user, use some other "ordinar" user like SYSTEM - I have a feeling your problems have a root cause exeatly at the SYSDBA connection!
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  9. #9
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    id still use the upgrade scripts - its what they are there for

  10. #10
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Quote Originally Posted by davey23uk
    id still use the upgrade scripts - its what they are there for
    I agree 100% with that!
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

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