Simon_DBA wrote: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.
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.
#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
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.
user626836 wrote:This indicates that the NLS_LANG parameter has not been set (or set incorrectly) - it should be set to AL32UTF8 before imp is invoked.
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>Input "yes" here
-Ignore create error due to object existence <yes/no>: no>
-Import grants <yes/no>: yes>HTH
-Import table data <yes/no>: yes>
-Import entire export file <yes/no>: no>
-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>>
It shows me the following:
select * from NLS_DATABASE_PARAMETERS where parameters in ('NLS_LANGUAGE','NLS_TERRITORY','NLS_CHARACTERSET');
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
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:
alter user you_user_here profile xxxxx;
select profile from dba_profiles where resource_name='PASSWORD_LIFE_TIME' and limit='UNLIMITED'