Solved

Oracle migration from 9i to 11g with Exp utility

Posted on 2012-12-20
10
2,704 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
  • 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 76

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
 
LVL 76

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 76

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 76

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

758 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now