Oracle migration from 9i to 11g with Exp utility

Provide the command used, and the last hundred or so lines from the log.

It might be creating the indexes and enabling constraints.  This is the next step after the tables have been imported.

Are you seeing a lot of disk i/o on the server?

The command I used was like this

imp somelogin/somepassword@AOVRDSSTAGORCL full=Y file=some_201
21218.dmp log=some_20121218_for_20121219.log statistics=recalculate

Here is the output of the last few lines. I have indicated the last table.
http://screencast.com/t/770UxvNZ4Exx

I looked at the write and IO. It has been increasing for about 45 minutes, just before I write this, but it then dropped back to normal. I tried to get a snapshot here
http://screencast.com/t/4ciedoRG1

It is likley building the indexes.

There aren't any messages stating that it is 'creating indexes'.  You can confirm this with a quick exp/imp of a small table.  I've shown this on my dev system in the code blocks below.

Check the docs:
http://docs.oracle.com/cd/E11882_01/server.112/e22490/original_import.htm#i1023560

Table Objects: Order of Import
 
Table objects are imported as they are read from the export dump file. The dump file contains objects in the following order:
 1. Type definitions
 2. Table definitions
 3. Table data
 4. Table indexes
 5. Integrity constraints, views, procedures, and triggers
 6. Bitmap, function-based, and domain indexes



set up the user

SQL> create user bob identified by bob;

User created.

SQL> grant create session, resource to bob;

Grant succeeded.

SQL> conn bob/bob

SQL> create table tab1(col1 char(1));

Table created.

SQL> create index tab1_idx on tab1(col1);

Index created.

SQL> create or replace procedure myproc is begin null; end;
  2  /

Procedure created.

Select all Open in new window

Do the export

C:\>exp bob/bob file=bob owner=bob

Export: Release 11.2.0.3.0 - Production on Thu Dec 20 14:57:50 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit
Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing option
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user BOB
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user BOB
About to export BOB's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export BOB's tables via Conventional Path ...
. . exporting table                           TAB1          0 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.

Select all Open in new window

Remove to objects

SQL> drop table tab1 purge;

Table dropped.

SQL> drop procedure myproc;

Procedure dropped.

Select all Open in new window

Do a full import

C:\>imp bob/bob file=bob full=y

Import: Release 11.2.0.3.0 - Production on Thu Dec 20 14:58:26 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit
Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing option

Export file created by EXPORT:V11.02.00 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing BOB's objects into BOB
. . importing table                         "TAB1"          0 rows imported
Import terminated successfully without warnings.

C:\>

Select all Open in new window

Verify the table/index/procedure exist after import

SQL> select object_name,object_type from user_objects;

OBJECT_NAM OBJECT_TYPE
---------- -------------------
MYPROC     PROCEDURE
TAB1_IDX   INDEX
TAB1       TABLE

SQL>

Select all Open in new window

Spot on there...  Another trick of the trade (during an import) is to periodically query the view sys.dba_datafiles for changes in space consumed.  In like manner, the process that's running the statement should show I/O being consumed.

HTH

I did an imp on the same dump file with a SHOW=Y. This does not do the actual import but shows the commands that it will go through. The resulting log file was about 35K lines. There were creation of procedures, functions and indexes after the table imports. Through TOAD I found that the last command which was a CREATE INDEX has also been created. I checked several other creations prior to this and they were all found. So, why did it not come out cleanly with a success message?

I counted the CREATE INDEX calls in the log file. They were over 1400. The actual database had an additional 1000+ indexes.

>> The actual database had an additional 1000+ indexes.

Make sure you were looking at the specific users indexes.

Also some constraints are enforced by the creation of indexes.

>>So, why did it not come out cleanly with a success message?

Maybe it's working on the constraints now?


As long as you are seeing CPU and/or disk i/o, it is still doing something.

Make sure all your indexes are 'valid'.  I still say it was likely building indexes.

Some indexes, like Text indexes, can take quite a while to rebuild.

The data on examination seemed fine even though there was no clean exit and the process hung there for ever. So, I am going to accept this.