Problems when importing on NT

[cadbilbao]

Hi!

I'm attempting to migrating my database from Unix to NT.

In Unix, my tablespaces are in '/u02/oradata/SID/rbs01.dbf'...
But in NT I've got my DataBase in 'd:\orant\'.

I succeed in export on Unix (I created a 'devexport.dmp' file), but
when trying to import on NT, I get this error:

----//----
Connected to: Oracle8 Release 8.0.5.0.0 - Production
PL/SQL Release 8.0.5.0.0 - Production

Export file created by EXPORT:V08.00.04 via conventional path
IMP-00017: following statement failed with ORACLE error 1119:
"CREATE TABLESPACE "RBS" DATAFILE '/u02/oradata/SID/rbs01.dbf' SIZE 15728640"
" DEFAULT STORAGE (INITIAL 131072 NEXT 131072 MINEXTENTS 2 MAXEXTENTS "
"121 PCTINCREASE 0) ONLINE PERMANENT"
IMP-00003: ORACLE error 1119 encountered
ORA-01119: error in creating database file '/u02/oradata/SID/rbs01.dbf'
ORA-27040: skgfrcre: create error, unable to create file
OSD-04002: unable to open file
....
....

Could anybody give any suggestion?

Thank you very much

 

[lfoata]

To fix this , you can create the tablespaces prior to importing the data either manually of by exporting your database again with option rows = no, then edit the export file (not supported by oracle but who cares) to keep only the create tablespaces and modify the datafiles path to match your need, then import that file in your new database.
Import your whole database export.

 

[cadbilbao]

Thank you very much for answering, but tablespaces were already created.

 

[cadbilbao]

Thank you very much for answering.

+ I create a database on my NT server.

+ I create the tablespaces with THE SAME NAME as the Unix Originals.

create tablespace SAMENAME datafile 'd:\foo\foo.dbf' size
10m autoextend on next 5m maxsize 50m;

+ And d:\foo\foo.dbf ? Must it be the same as Unix?

+ And the size? Must it be similar?

Thank you very much...

 

[lfoata]

The names of the datafiles should not matter, only the name of the tablespaces matters (on Unix anyway, not sure about NT version)
Have you tried ignore=y in your parameter file ? that will make import continue even if an error occurs, so that your import will continue even if the create tablespace gives an error.

size does not matter as long as it is big enough to store the data

 

[rcurva]

cad,

lfoata is right, set ignore=y, in fact, the procedure that i gave you in your initial posting contains such instruction.

6. Perform a full import with the parameter IGNORE=Y into the target database as a DBA user.

imp system/manager full=y ignore=y file=expdat.dmp

Using IGNORE=Y will tell Oracle to ignore any creation errors during the import, allowing the import to complete.

 

[carp]

Be aware that if you set IGNORE=Y, creation errors are not reported or logged (per Oracle documentation). Consequently, you will not be notified if there is a problem and your table doesn't get created. Fortunately, other db errors ARE reported, so if your table doesn't get created, subsequent INSERT commands will error out and be reported.

 

[cadbilbao]

Hi again!

After doing 'imp80 parfile=mypar.par 2>mylog.log' (including 'ignore=y'), I got this warning, and my NT tables are EMPTY:

----//---------
Export file created by EXPORT:V08.00.04 via conventional path
IMP-00017: following statement failed with ORACLE error 6550:
"BEGIN SYS.DBMS_DEFER_IMPORT_INTERNAL.QUEUE_IMPORT_CHECK('SID.WORLD','HP900"
"0/800-7.0-8.0.0'); END;"
IMP-00003: ORACLE error 6550 encountered
ORA-06550: line 1, column 40:
PLS-00302: component 'QUEUE_IMPORT_CHECK' must be declared
ORA-06550: line 1, column 9:
PL/SQL: Statement ignored
. . importing table "DEF$_AQCALL" 0 rows imported

...

ORA-01552: cannot use system rollback segment for non-system tablespace 'TOOLS'
. . importing table "REP$_AUD_COLUMN" 0 rows imported
. . importing table "REP$_COL_GROUP" 0 rows imported

...

About to enable constraints...
Import terminated successfully with warnings.
----------------------//-------------------

Any suggestion?

 

[raygg]

I agree - create the tablespaces anew on the NT box.

Unlike unix, oracle 8.0.x has a datafile limit of 4gb (it is documented in an alert by oracle), so if any individual tablespace > 4gb, make sure you have multiple datafiles defined.

I found in moving db by export that it is simpler to just move the users, and not the system schemas. Going between platforms I suspect there would be some differences between system schemas under the hood, if for no other reason than Oracle issues a different version for each platform.

Another thing you may run into is very large initial and next extents on some tables that you may want to correct on the move. To tackle this issue, import the schemas with rows=n to create only the structure without the data. Then cut and passte the table script using TOAD or some other tool, resize the table script for the offending tables, drop the tables, and reload the new rescripted tables. Then run the import with the data with the parameters rows=y and ignore = y.

 

[lfoata]

if the tables and indexes are created but without data, it may be that you exported with rows=no.

It also looks like your rollback segments are offline.

At databse create only rollback segment 0 is created and is in the SYSTEM tablespace. You have to create the other rollback segments (recommend 4 or +), put them online and then put segment 0 offline.
If you do not have a rollback segment valid, you cannot insert into tables.