| Importing an 8i dump file into a 9i database [message #111443] |
Wed, 16 March 2005 07:52  |
jharper
Messages: 2
Registered: March 2005
|
Junior Member |
|
|
We are working to upgrade a database from 8i to 9i. I have created an empty 9i database with the same tablespace schema as the 8i database. I import the dump file as normal but receive a bunch of errors. I list them below.
Here is my import command:
imp system/manager@EU2TST04 BUFFER=2048000 FULL=Y COMMIT=Y INDEXES=Y GRANTS=Y CONSTRAINTS=Y ROWS=Y IGNORE=Y FILE=E:\IMPORT\EU2TST04\expEU2PRD01.dmp LOG=C:\oracle\admin\EU2TST04\Import\import.log
This is the same import that I use to transfer prod databases to test.
From the import.log file:
1) Why does it try to create tablespaces? I have already pre-defined an empty database with tablespaces according to the EU2PRD01 schema.
MP-00017: following statement failed with ORACLE error 1119:
"CREATE TABLESPACE "ARCHIVE_DATA" DATAFILE 'E:\ORACLE\ORADATA\EU2PRD01\ARCH"
"IVE_DATA.DBF' SIZE 104857600 AUTOEXTEND ON NEXT 104857600 MAXSIZE 32"
"767M DEFAULT STORAGE(INITIAL 40960 NEXT 40960 MINEXTENTS 1 MAXEXTENTS 505 "
"PCTINCREASE 50) ONLINE PERMANENT EXTENT MANAGEMENT DICTIONARY"
IMP-00003: ORACLE error 1119 encountered
ORA-01119: error in creating database file 'E:\ORACLE\ORADATA\EU2PRD01\ARCHIVE_DATA.DBF'
ORA-27040: skgfrcre: create error, unable to create file
OSD-04002: unable to open file
O/S-Error: (OS 3) The system cannot find the path specified.
2) How to handle the rollback segments? In the 9i databases, we use automatic undo segments.
IMP-00017: following statement failed with ORACLE error 30019:
"CREATE ROLLBACK SEGMENT RBS2 STORAGE(INITIAL 1048576 NEXT 1064960 MINEXTENT"
"S 20 MAXEXTENTS 505) TABLESPACE "ROLLBACK""
IMP-00003: ORACLE error 30019 encountered
ORA-30019: Illegal rollback Segment operation in Automatic Undo mode
3) Why is it giving "no privileges" errors on the table create statements? I am running the import as system/manager as I always do.
IMP-00017: following statement failed with ORACLE error 1950:
"CREATE TABLE "ABC_CLASS_TAB" ("ABC_CLASS" VARCHAR2(1) NOT NULL ENABLE, "ABC"
"_PERCENT" NUMBER(2, 0) NOT NULL ENABLE, "ROWVERSION" DATE) PCTFREE 10 PCTU"
"SED 40 INITRANS 1 MAXTRANS 255 LOGGING STORAGE(INITIAL 16384 NEXT 204800 MI"
"NEXTENTS 1 MAXEXTENTS 505 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFE"
"R_POOL DEFAULT) "
IMP-00003: ORACLE error 1950 encountered
ORA-01950: no privileges on tablespace 'IFSAPP_DATA'
|
|
|
|
|
|
| Re: Importing an 8i dump file into a 9i database [message #111453 is a reply to message #111452] |
Wed, 16 March 2005 08:47  |
jharper
Messages: 2
Registered: March 2005
|
Junior Member |
|
|
Thanks for the quick reply, Mahesh. But there has to be an easier way! I have a hundred users - which means I have to create 100 import scripts. Can't be....
Some comments to your answers:
1) I use this same import script to create test dbs from prod dbs (all 9i). These NEVER attempt to create the tablespaces. Why is it doing it now? How do I get around this?
2) HOW do I ignore rollback segments in the import of 8i to 9i?
3) Quotas? Again, I am using the same import script and procedures to create this database as I do my other test databases. Why is it now giving this error when I do 8i into 9i? Here is my create db script. I have sized all the tablespaces to be slightly larger than the original 8i database tablespaces. There is no space problem.
CREATE DATABASE EU2TST04
LOGFILE 'C:\oracle\oradata\EU2TST04\redo01.log' SIZE 10240K,
'D:\oracle\oradata\EU2TST04\redo02.log' SIZE 10240K,
'E:\oracle\oradata\EU2TST04\redo03.log' SIZE 10240K
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXLOGHISTORY 1
DATAFILE 'E:\oracle\oradata\EU2TST04\system01.dbf' SIZE 500M AUTOEXTEND ON NEXT 100M
UNDO TABLESPACE UNDOTBS1 DATAFILE 'D:\oracle\oradata\EU2TST04\UNDOTBS01.dbf' SIZE 1000M AUTOEXTEND ON NEXT 100M
MAXDATAFILES 254
MAXINSTANCES 1
CHARACTER SET WE8MSWIN1252
NATIONAL CHARACTER SET AL16UTF16;
create tablespace IFSAPP_DATA
datafile 'E:\oracle\oradata\EU2TST04\IFSAPP_DATA.dbf'
size 7000M reuse autoextend on next 100M;
create tablespace IFSAPP_INDEX
datafile 'E:\oracle\oradata\EU2TST04\IFSAPP_INDEX.dbf'
size 7000M reuse autoextend on next 100M;
create tablespace IFSAPP_REPORT_DATA
datafile 'E:\oracle\oradata\EU2TST04\IFSAPP_REPORT_DATA.dbf'
size 700M reuse autoextend on next 10M;
create tablespace IFSAPP_REPORT_INDEX
datafile 'E:\oracle\oradata\EU2TST04\IFSAPP_REPORT_INDEX.dbf'
size 100M reuse autoextend on next 10M;
create tablespace IFSAPP_ARCHIVE_DATA
datafile 'E:\oracle\oradata\EU2TST04\IFSAPP_ARCHIVE_DATA.dbf'
size 50M reuse autoextend on next 10M;
create tablespace IFSAPP_ARCHIVE_INDEX
datafile 'E:\oracle\oradata\EU2TST04\IFSAPP_ARCHIVE_INDEX.dbf'
size 50M reuse autoextend on next 10M;
create tablespace USERS
datafile 'E:\oracle\oradata\EU2TST04\USERS01.dbf'
size 10M reuse autoextend on next 1M;
create tablespace TOOLS
datafile 'E:\oracle\oradata\EU2TST04\TOOLS01.dbf'
size 10M reuse autoextend on next 1M;
create tablespace DAMIEN
datafile 'E:\oracle\oradata\EU2TST04\DAMIEN01.dbf'
size 10M reuse autoextend on next 1M;
create temporary tablespace TEMP
tempfile 'D:\oracle\oradata\EU2TST04\TEMP01.dbf'
size 500M reuse autoextend on next 10M;
|
|
|
|
| Re: Importing an 8i dump file into a 9i database [message #111462 is a reply to message #111443] |
Wed, 16 March 2005 09:28 |
 |
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
>>I have a hundred users - which means I have to create 100 import scripts.
Ok.
Then you can use FULL IMPORT.
First understand how this import works.
export creates a dmp file.
import reads this dmp file and creates everything in this dmp file in the same order.
try this.
this will show the exact steps the import will try to do ( without actually imporing it.). Look into somelog.sql file.
$imp dba/user@target full=y file=myfiledmp show=y log=somelog.sql
>>
1) I use this same import script to create test dbs from prod dbs (all 9i). These NEVER attempt to create the tablespaces. Why is it doing it now? How do I get around this?
because you use ignore=y, it may have ignore the error due to existence of object.
if the object (tablespace) does not exist, then it will try to create and when unable to create , import will whine!.
>>2)HOW do I ignore rollback segments in the import of 8i to 9i?
just ignore it.
i mean during import it fails trying to create the RBS.
just ignore this message. Do nothing.
>> 3)
It should do it.
But if the tablespace is not properly created , the quota's will not be set.! quotas are not related to available space.
it is allocated space for a user on a tablespace.
if the user is created with resource role, by default he gets unlimited quota on all existing tablespaces.
else if NO resource role is allocated, the user needs quota to be assigned for each tablesepace that he can writeto.
Just try this .. you can demystify the sequence in which import is done.
$imp dba/user@target full=y file=myfiledmp show=y log=somelog.sql
you can see that in the somelog.sql file
these entries exist.
"CREATE TABLESPACE "ARCHIVE_DATA" DATAFILE 'E:\ORACLE\ORADATA\EU2PRD01\ARCH"
"IVE_DATA.DBF' SIZE 104857600 AUTOEXTEND ON NEXT 104857600 MAXSIZE 32"
"767M DEFAULT STORAGE(INITIAL 40960 NEXT 40960 MINEXTENTS 1 MAXEXTENTS 505 "
during import with full=y oracle tries to recreate it.
since a possible change in directory structure this operation would have failed.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
