Solved

Oracle migration from 9i to 11g with Exp utility

Posted on 2012-12-20
10
3,052 Views
Last Modified: 2012-12-31
Hi:

I am utilizing the exp and imp utility to migrate from an Oracle 9i database on a datacenter server to Oracle 11g on Amazon RDS. I can see the tables getting imported fine. However, after the last table, the process seems stuck. I confirmed through TOAD tool that the entries are there in several tables. Some tables have millions of rows.

In an earlier round, it went through this stage but gave me errors on Create Index and a tablespace. So I created a tablespace using the CREATE TABLESPACE command and then tried it one more time but it is stuck for over 10 hours .

Please help. Thank you.
0
Comment
Question by:docram
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
10 Comments
 
LVL 23

Expert Comment

by:David
ID: 38710315
Provide the command used, and the last hundred or so lines from the log.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38710326
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?
0
 

Author Comment

by:docram
ID: 38710685
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
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38710865
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.

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.

Open in new window


Remove to objects
SQL> drop table tab1 purge;

Table dropped.

SQL> drop procedure myproc;

Procedure dropped.

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:\>

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>

Open in new window

0
 
LVL 23

Expert Comment

by:David
ID: 38711043
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
0
 

Author Comment

by:docram
ID: 38711583
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.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38711595
>> 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.
0
 

Accepted Solution

by:
docram earned 0 total points
ID: 38722276
For whatever reason, I find all the tables, synonyms, procedures, indexes etc. come through but still the program hung and did not exit cleanly. I am going to accept this condition for now and call it success.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38724692
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.
0
 

Author Closing Comment

by:docram
ID: 38732214
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.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Create Index on a Materialized View 5 60
Oracle function to insert records? 15 81
capture vmstat info and insert it into an oracle table 31 93
oracle query 3 34
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question