This discussion is archived
1 2 Previous Next 29 Replies Latest reply: Feb 14, 2013 10:24 AM by user626836 Go to original post RSS
  • 15. Re: Upgrading from 10g R2 to 11g R2
    Simon_DBA Journeyer
    Currently Being Moderated
    user626836, doing a migration via export/import is an extremely complex process. There's hundreds of things you need to take into consideration. It's easy to do a export with FULL=Y but unfortunately importing into a new database is far more complicated.

    When it comes to schemas with just basic object types, I recommend importing them in three steps:

    #1: with rows=y, indexes=n, constraints=n, grants=n -- the purpose here is to ensure that you get all of the row data in cleanly with as few error messages (noise) as possible
    #2: with rows=n, indexes=y, constraints=y, grants=n, ignore=y -- the purpose here is to make sure you get the indexes and constraints with as few error messages (noise) as possible)
    #3: with rows=n, indexes=y, constraints=y, grants=y, ingore=y -- same as above but this time to pick up the grants

    But that's only for schemas with basic object types. What I mean by that is if say for example your schemas contains context indexes then the process is going to be much more complicated. Or Workspace Manager versioned tables. Or even APEX. There's probably a dozen of other items that fall under this category.

    Then there's all the things that exp/imp isn't going to properly or fully pick up on. A couple of random items on this list: password profile functions in SYS, AQ activations, resource consumer groups, SYS object grants, password file users, SQL Tuning Sets, etc, etc. There's probably about 30-50 items in this area.

    Bottom line is that it sounds like you're just trying to do a full export and full import and hope that you're done. A 10g to 11g migration that way is possible but it's going to be much much more complicated than that. And you need a DBA who knows how to take care of all of those things: how to A) know what features, options, settings, and schema objects you have in your 10g database, and B) be able to migrate them.

    You can also use data pump but that's not going to make things much better. A DBA can spend years and years getting to learn all of the intricacies of exp/imp and expdp/impdp and specifically what it does and does not handle properly in the various versions. And even then after migrating from 10g to 11g they'll probably leave things behind and not even realize it.
  • 16. Re: Upgrading from 10g R2 to 11g R2
    User286067 Journeyer
    Currently Being Moderated
    Simon_DBA wrote:
    user626836, doing a migration via export/import is an extremely complex process. There's hundreds of things you need to take into consideration. It's easy to do a export with FULL=Y but unfortunately importing into a new database is far more complicated.
    Really? If you are upgrading by exp/imp, then you should never ever use FULL=Y because it is counter-intutive. Why would you overwrite sys etc schema in target db with one from lower version? If you are trying to upgrade, you should move only application and related schema and never sys (and alike). If there are any objects, you should just re-create them in target db.
    >
    When it comes to schemas with just basic object types, I recommend importing them in three steps:

    #1: with rows=y, indexes=n, constraints=n, grants=n -- the purpose here is to ensure that you get all of the row data in cleanly with as few error messages (noise) as possible
    Well that is good, the basic export/import works fine as well. All it needs is some basic preparation and understanding that your source and target database are not identical in terms of users/tablespaces/roles/sys privileges/profiles etc.
    But that's only for schemas with basic object types. What I mean by that is if say for example your schemas contains context indexes then the process is going to be much more complicated. Or Workspace Manager versioned tables. Or even APEX. There's probably a dozen of other items that fall under this category.
    Most of them have proper documents on metalink on upgrades, reading those is a mandatory pre-requisit.
    You can also use data pump but that's not going to make things much better. A DBA can spend years and years getting to learn all of the intricacies of exp/imp and expdp/impdp and specifically what it does and does not handle properly in the various versions. And even then after migrating from 10g to 11g they'll probably leave things behind and not even realize it.
    Dunno, I exported 12 schemas from a 10g db, created a script for roles, and logon trigger. Admins basically re-imaged the box, we installed grid control, asm, 11203 db, I ran my scripts for roles, trigger, ran impdp for all 12 schemas. Ran a utlrp and after changing ip/port on application servers we were back in business. Nothing complicated at all. YMMV of course.

    To OP,

    your problem seems like you either have some tables in target db that are throwing errors. Drop all objects owned by the schema you are trying to import in target db and then retry your import. It is not complicated at all.

    Raj
  • 17. Re: Upgrading from 10g R2 to 11g R2
    Simon_DBA Journeyer
    Currently Being Moderated
    rjamya, do you know that there are certain component (such as Workspace Manager) that you can only migrate via FULL exp/imp? Also SYS is not included in FULL exports and is not over-written when you import from a full export!!

    But that's all besides the point. My point is that migrating (upgrading) via exp/imp or expdp/impdp is extremely difficult and requires a lot of expertise depending on how complicated your database is and what options your schemas and the database uses. rjamya sounds like your 12 schema database was pretty basic or maybe you don't even realize what items you left behind or are different on the new database. Like I said I have a list of about 50 items that need to be checked - I had listed just a few of those.
  • 18. Re: Upgrading from 10g R2 to 11g R2
    User286067 Journeyer
    Currently Being Moderated
    I dont think OP mentioned Workspace or any such non standard options in place. They way I read your response, exp/imp seems a big scary thing, which isn't. That way, even a simple SELECT is a really complicated option. What OP needed was a simple solution, and that is what a simple exp/imp does. Now, as like everything you need to do a bit of groundwork to ensure everything is running smoothly, which is why there is mandatory metalink document reading and mandatory testing to ensure everything works fine.

    Trust me, if my 12 schemas were any complicated, I'd just use rman clone and then upgrade, rather than wasting my time with exp/imp, it makes my life simpler and easier. Back in the days when 10g was all rage, we had an automated process that would take 9i hotbackup from one machine, scp it to another which had 10g installed, create db, upgrade and make it available to users couple of times a week for testing.

    So, if you plan properly nothing is complicated but if you dont, like i mentioned even a SELECT can be. I still believe, the OP needs to cleanup target db, make proper tablespace mapping and simple schema exp/imp will work fine with a bit of legwork. If you have exotic options installed and in use then you must tend to them, exp/imp is not nirvana, it is just a solution.

    Raj
  • 19. Re: Upgrading from 10g R2 to 11g R2
    Simon_DBA Journeyer
    Currently Being Moderated
    Again, my other point is that most experienced DBAs don't even know what options or features are used in their databases and hence will undoubtedly leave something behind (out of ignorance) when upgrading via exp/imp expdp/impdp. I gave examples of some of those things previously. In reality I have a list of 52 items not handled by export/import that the DBA has to check on and move manually.

    And yes of course you can duplicate with RMAN and upgrade via catalog scripts from there. But there are also legitimate reasons where that may not be possible (i.e. endian changes).

    I still stand by my point that migrating successfully via export/import can be extremely complicated and it takes a lot of experience to know what options and features you do use and how to move them.
  • 20. Re: Upgrading from 10g R2 to 11g R2
    user626836 Newbie
    Currently Being Moderated
    Hi Srini,
    Here is my complete import command that I'm using:
    First I open a command prompt and type imp. Then I enter my database user credentials and then this is what it follows:
    -Enter insert buffer size <minimun is 8192>30720>
    -Export file created by export V10.02.01 via conventional path
    -Import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
    -Import server uses AL32UTF8 character set <possible charset conversion>
    -List contents of import file only <yes/no>: no>
    -Ignore create error due to object existence <yes/no>: no>
    -Import grants <yes/no>: yes>
    -Import table data <yes/no>: yes>
    -Import entire export file <yes/no>: no>
    -Username: myusername
    -Enter table <T> or partition <T : P> names. Null list means all tables for user
    -Enter table <T> or partition <T : P> name or . if done:

    Then I receive all the warnings related to the same error which is object already exists.
    imp-00015: following statement failed because the object already exists:
    "Create table "table_name" <"field1" number <10>, "field2" varchar2<5>>
  • 21. Re: Upgrading from 10g R2 to 11g R2
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    user626836 wrote:
    Hi Srini,
    Here is my complete import command that I'm using:
    First I open a command prompt and type imp. Then I enter my database user credentials and then this is what it follows:
    -Enter insert buffer size <minimun is 8192>30720>
    -Export file created by export V10.02.01 via conventional path
    -Import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
    -Import server uses AL32UTF8 character set <possible charset conversion>
    This indicates that the NLS_LANG parameter has not been set (or set incorrectly) - it should be set to AL32UTF8 before imp is invoked.
    -List contents of import file only <yes/no>: no>
    -Ignore create error due to object existence <yes/no>: no>
    Input "yes" here
    -Import grants <yes/no>: yes>
    -Import table data <yes/no>: yes>
    -Import entire export file <yes/no>: no>
    -Username: myusername
    -Enter table <T> or partition <T : P> names. Null list means all tables for user
    -Enter table <T> or partition <T : P> name or . if done:

    Then I receive all the warnings related to the same error which is object already exists.
    imp-00015: following statement failed because the object already exists:
    "Create table "table_name" <"field1" number <10>, "field2" varchar2<5>>
    HTH
    Srini
  • 22. Re: Upgrading from 10g R2 to 11g R2
    user626836 Newbie
    Currently Being Moderated
    Hi Srini and thanks for your reply,
    I changed the NLS_LANG through the regedit, but I don't know why it keeps telling me that. Is there any other place where I have to make the change also? I made the changes on my import server. Do I have also to do it on my export server as well?
  • 23. Re: Upgrading from 10g R2 to 11g R2
    Richard Harrison . Expert
    Currently Being Moderated
    Hi,
    As you are using the old imp utility the user you are importing has to exist (unless you did a full=y exp which i don't think you did). The error is simply saying that the object that imp is trying to create already exists. If you log on as the user you are trying to import into and select * from user_tables do you see anything? If you expect the schema to be clean and empty just drop the user and manually recreate it - then re-run the import. I suspect an earlier imp command was partially succesful and managed to create some objects?

    expdp/impdp is much better at all of this - it will even create the user for you if it doesn't exist and is about 10x faster than exp/imp.

    Regards,
    Harry
  • 24. Re: Upgrading from 10g R2 to 11g R2
    user626836 Newbie
    Currently Being Moderated
    Hi Harry,
    I followed your instructions and it worked pretty well, but when run an export from my 11g server now it says: Export terminated successfully with warnings.
    The warnings that I received are all the same and this is the message I receive: EXP-00091: Exporting questionable statistics.
    What should I do to fix that warning?
    Thanks in advance!!
  • 25. Re: Upgrading from 10g R2 to 11g R2
    Paul M. Oracle ACE
    Currently Being Moderated
    EXP-00091: Exporting questionable statistics.
    Re: EXP-00091: Exporting questionable statistics.
  • 26. Re: Upgrading from 10g R2 to 11g R2
    Richard Harrison . Expert
    Currently Being Moderated
    Hi,
    Paul has pointed you in the right direction with his link. It's basically due to NLS settings not being correct.

    Cheers,
    harry
  • 27. Re: Upgrading from 10g R2 to 11g R2
    user626836 Newbie
    Currently Being Moderated
    Hi Paul and sorry for the delay,
    I followed the steps on that forum, but when I execute the following query on my 11g server:
    select * from NLS_DATABASE_PARAMETERS where parameters in ('NLS_LANGUAGE','NLS_TERRITORY','NLS_CHARACTERSET');
    It shows me the following:
    NLS_LANGUAGE = AMERICAN
    NLS_TERRITORY = AMERICA
    NLS_CHARACTERSET = AL32UTF8

    But the file that I imported to my 11g server comes from my 10g server which has a NLS_CHARACTERSET = WE8MSWIN1252
    So after running the following code on my 11g server posted on that forum:
    set NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252
    Everytime I log in with my administrator credentials it tells me that my password will expire within 7 days. ORA-28002, but the NLS_LANG stills the same
    AL32UTF8 on my 11g server.

    Can you guys please help me?
  • 28. Re: Upgrading from 10g R2 to 11g R2
    Richard Harrison . Expert
    Currently Being Moderated
    Hi,
    This is a seperate issue from the original ost now? Are you still having problems with NLS_LANG and statistics? The problem you refer to now is just a simple password expiry setting on the profile of your 'admin' user. Change the profile of your user to one that doesnt have password expiry if thats not what you want.
    alter user you_user_here profile xxxxx;
    where xxx is a profile which doesn;t have password expiry. This can be found by picking one of the values returned by this SQL:
    select profile from dba_profiles where resource_name='PASSWORD_LIFE_TIME' and limit='UNLIMITED'
    Cheers,
    Harry
  • 29. Re: Upgrading from 10g R2 to 11g R2
    user626836 Newbie
    Currently Being Moderated
    Hi Harry and thanks for your reply,
    I'm still having the same issue with NLS_LANG and after following the steps to change it I started to receive that error related with the admin password.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points