Printable View
Hello all,
I'm trying to upgrade my database from 8i (8.1.7) to the 9i (9.2) and I'm wondering if I use export/import method do I have to pre-create all the users in my 9i database first before I do my import. I have about 200 users in my 8i db so I prefer not to pre-create the users if I don't have too. Would import FULL=Y do the trick?? Please help, Thank you in advance.
yes, precreate tablespaces before the import
The question was about precreating users, not tablespaces...Quote:
Originally posted by candyman
yes, precreate tablespaces before the import
To answer the original question: No, there is no need to precreate users before importing if the export was performed with FULL=Y. Imp with FULL=Y will take care of creating users and other stuff....
Jmodic,
please correct me if I am wrong. Don't you have to precreate the tablespaces first before the import??? I don't think you can do a full import without tablespaces
That wasn't the question - need to create tablespace(s) first. Question was about the need to pre-create the users - that's what jmodic was telling you.
All you need is a SYSTEM tablespace (which you allways have if you have a database). Nothing else, no other tablespaces are neccessary for the import with FULL=Y to succeed. And it will create all other tablespaces for you too. Of course for this to succeed the directory structure for the database files in the new system must be existing and the same as it was in the exported system.Quote:
Originally posted by candyman
please correct me if I am wrong. Don't you have to precreate the tablespaces first before the import??? I don't think you can do a full import without tablespaces
Hello, first, thanks for your help, so if my 9i DB has all the preconfigured tablespaces like TEMP,USERS, TOOL..etc do I need to drop them first since during the import full=y process, Oracle will try to create these tablespaces. Please advise. Thanks againQuote:
Originally posted by jmodic
All you need is a SYSTEM tablespace (which you allways have if you have a database). Nothing else, no other tablespaces are neccessary for the import with FULL=Y to succeed. And it will create all other tablespaces for you too. Of course for this to succeed the directory structure for the database files in the new system must be existing and the same as it was in the exported system.
No need to drop the default tablespaces. Just import with ignore=y option.
IGNORE=Y has nothing to do with overwriting datafiles during the import.Quote:
Originally posted by adewri
No need to drop the default tablespaces. Just import with ignore=y option.
Newbie, you don't have to drop your tablespaces before the import, just import with option DESTROY=N, which btw is also the default value for this parameter. DESTROY tells whether imp should overwrite the existing database datafiles (in other words, if it will recreate the existing tablespaces with the same names) during the import with FULL=Y.
Well, i never said it has to do anything with creation of data files. My first part was, no need to drop the tablespace, second part ignore=y option was so that import does not throw errors when trying to create sys objects already existing. May be i was not clear.Quote:
Originally posted by jmodic
IGNORE=Y has nothing to do with overwriting datafiles during the import.
Hello all, I've created a brand-new 9i db with identical dbname and file-structure(mount points) but when I do an import using my export from 8i with full=y option I've got a bunch of these errors:
IMP-00017: following statement failed with ORACLE error 30019:
"CREATE PUBLIC ROLLBACK SEGMENT RBS13 STORAGE(INITIAL 1048576 NEXT 1048576 M"
"INEXTENTS 20 MAXEXTENTS 505) TABLESPACE "RBS""
IMP-00003: ORACLE error 30019 encountered
ORA-30019: Illegal rollback Segment operation in Automatic Undo mode
and these:
IMP-00003: ORACLE error 12913 encountered
ORA-12913: Cannot create dictionary managed tablespace
Are these okay? Thanks
You can more or less ignore this errors - you obviously have set up your database for automatic undo mode, so rollback sefments from export are not relevant for you.Quote:
Originally posted by newbie
IMP-00017: following statement failed with ORACLE error 30019:
"CREATE PUBLIC ROLLBACK SEGMENT RBS13 STORAGE(INITIAL 1048576 NEXT 1048576 M"
"INEXTENTS 20 MAXEXTENTS 505) TABLESPACE "RBS""
IMP-00003: ORACLE error 30019 encountered
ORA-30019: Illegal rollback Segment operation in Automatic Undo mode
Those errors are much more to be worried about. I'm allmost certain your tablespaces from dmp file were not created in your 9.2 database during the import. It's obvious that your system tablespace in 9.2 database was created as localy managed tablespace during database creation (this is the default behavior). I'ts also obvious that tablespaces in your 8i database were created as dictionary managed tablespaces. The problem in 9.2 is that if your system TS is localy managed then no other tablespace can be dictionary managed. So "CREATE TABLESPACE ... EXTENT MANAGEMENT DICTIONARY " commands from your dmp are failing because DMT tablespaces are not allowed with LMT system tablespace.Quote:
and these:
IMP-00003: ORACLE error 12913 encountered
ORA-12913: Cannot create dictionary managed tablespace
So you basicaly have two options:
a) create your 9.2 database again, this time with dictionary managed system tablespace (I wouldn't go for this option).
b) manualy create the tablespaces that you had in your 8i database, only that in 9.2 they will be dictionary managed.
If you'll go for option b), then don't forget to use DESTROY=N (the default) when you import.
I think u meant Locally Managed... right ?Quote:
Originally posted by jmodic
b) manualy create the tablespaces that you had in your 8i database, only that in 9.2 they will be dictionary managed.
Yes, of course. My mistake.Quote:
Originally posted by adewri
I think u meant Locally Managed... right ?