Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
Oracle migration from 9i to 11g with Exp utility
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.
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.
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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?
Are you seeing a lot of disk i/o on the server?
The command I used was like this
imp somelogin/somepassword@AOV
21218.dmp log=some_20121218_for_2012
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
imp somelogin/somepassword@AOV
21218.dmp log=some_20121218_for_2012
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
Do the export
Remove to objects
Do a full import
Verify the table/index/procedure exist after import
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.
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.
Remove to objects
SQL> drop table tab1 purge;
Table dropped.
SQL> drop procedure myproc;
Procedure dropped.
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:\>
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>
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
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.
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 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.
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.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial