?
Solved

Oracle migration from 9i to 11g with Exp utility

Posted on 2012-12-20
10
Medium Priority
?
3,257 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
Technology Partners: 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!

 
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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

777 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