Move Oracle database to different platform

[lyang]

I am trying to move oracle database from HP (Oracle 9i) to Windows (Oracle 10G). I created database and tablespaces on Windows, copied the exported file on Windows machine, did the full database import. The result is 'Import terminated successfully with warnings'. There are lots of the error messages in the import log like below. Any idea?

IMP-00003: ORACLE error 29339 encountered
ORA-29339: tablespace block size 4096 does not match configured block sizes
IMP-00017: following statement failed with ORACLE error 29339:
"CREATE TABLESPACE "INDX" BLOCKSIZE 4096 DATAFILE '/v02/oradata/oseptst/ind"
"x01.dbf' SIZE 26214400 AUTOEXTEND ON NEXT 1310720 MAXSIZE 16383M EXT"
"ENT MANAGEMENT LOCAL AUTOALLOCATE ONLINE PERMANENT NOLOGGING SEGMENT SPA"
"CE MANAGEMENT AUTO"

Thanks!

[davey23uk]

if you already created the tablespace then it doesnt matter.

but the error is that the block sizes are different between os's which is ok.

it would have failed anyway because of the datafile paths

[lyang]

The user_objects count is 4000 in the sourcce databse, it is 20,000 in the target database. Should any package be executed after the import?

[davey23uk]

what?

[pareshjavkar]

Hi,

How come your object count increased after importing ????

Please, recheck.

Is it object count or INVALID object count ????

[lyang]

Yes, the object counts in the target database have much more than source database. I was coping a database from one machine (9i) to another machine (10g) by doing the full database import, then recompiled the invalid objects after the import. Did I miss anything before/after import?

[davey23uk]

well 10g has way more objects - but YOUR data should be the same

[yxez]

hi dear

can u run this script on both db as system user....

select object_type,count(*) from dba_objects group by object_type;

I'm really curious what allien object's got into ur 10g db

[davey23uk]

it will be sysman stuff

[lyang]

Here is the layout from the original system.
OBJECT_TYPE COUNT(*)
------------------ ----------
CLUSTER 10
CONSUMER GROUP 4
CONTEXT 1
DATABASE LINK 2
EVALUATION CONTEXT 1
FUNCTION 159
INDEX 977
INDEX PARTITION 24
LIBRARY 63
LOB 51
OPERATOR 2

OBJECT_TYPE COUNT(*)
------------------ ----------
PACKAGE 323
PACKAGE BODY 314
PROCEDURE 43
QUEUE 8
RESOURCE PLAN 3
SEQUENCE 128
SYNONYM 1963
TABLE 1221
TABLE PARTITION 27
TRIGGER 7
TYPE 485

OBJECT_TYPE COUNT(*)
------------------ ----------
TYPE BODY 21
VIEW 2319

24 rows selected.

The layout from new system.
OBJECT_TYPE COUNT(*)
------------------- ----------
INDEX 2159
JOB CLASS 2
CONTEXT 5
TYPE BODY 170
INDEXTYPE 10
PROCEDURE 94
RESOURCE PLAN 3
RULE 4
JAVA CLASS 16423
SCHEDULE 1
TABLE PARTITION 216

OBJECT_TYPE COUNT(*)
------------------- ----------
WINDOW 2
WINDOW GROUP 1
JAVA RESOURCE 772
TABLE 2296
TYPE 1885
VIEW 3846
LIBRARY 150
FUNCTION 384
TRIGGER 164
PROGRAM 12
DATABASE LINK 2

OBJECT_TYPE COUNT(*)
------------------- ----------
CLUSTER 10
SYNONYM 20375
PACKAGE BODY 787
QUEUE 23
CONSUMER GROUP 5
EVALUATION CONTEXT 12
RULE SET 15
DIRECTORY 3
UNDEFINED 6
OPERATOR 57
JAVA DATA 306

OBJECT_TYPE COUNT(*)
------------------- ----------
SEQUENCE 196
LOB 528
PACKAGE 843
JOB 6
INDEX PARTITION 232
LOB PARTITION 1
XML SCHEMA 25

40 rows selected.

There are a lot different object counts in the new databse. What's the best way to move a database from Server A(9i) to Server B(10G) with database users and their schemas. Is there steps by using Full database import or Transportable tablespace? How to move the database users/password to another server?

Thanks!