Solved

how to use export/import for Oracle migration

Posted on 2015-01-18
33
325 Views
1 Endorsement
Last Modified: 2015-02-03
Dear all,

Right now will do Oracle migration from 10gR2 and 11gR2 using export/import, any step by steps guide on how to execute/implement this ?
1
Comment
Question by:marrowyung
  • 15
  • 9
  • 8
  • +1
33 Comments
 
LVL 34

Expert Comment

by:johnsone
ID: 40556390
Have you tried the documentation?  Upgrade guide for 11R2 is here -> http://docs.oracle.com/cd/E11882_01/server.112/e23633/toc.htm

There is an entire chapter devoted to doing the upgrade with exp/imp or expdp/impdp.  The guide also shows you which utilities to used based on the version of the source and destination.
0
 
LVL 23

Expert Comment

by:David
ID: 40556429
In like manner, one should remember to search the E-E knowledge base for past answers -- saves you points.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40556984
johnsone,

you mean the section : Moving Data Using Oracle Data Pump ?

as our company decided to use exp/imp to do this, so I better focus on this.

by the way, RMAN can't restore 10gR2 and 11gR2 content to 12c for migration purpose, right?
0
 
LVL 34

Expert Comment

by:johnsone
ID: 40557388
That section contains instructions for both export/import and expdp/impdp.  As you are moving from 10g to 11g, I believe that the documentation is going to recommend expdp/impdp (data pump).  They are the newer exp/imp utilities.  Different name, same results.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40557645
" As you are moving from 10g to 11g, I"

no, 11gR2/10gR2 to 12c

but exp/imp need a lot of manual works, which is diff from DBUA, right?
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 40557711
"but exp/imp ... is diff from DBUA, right?"
Correct.  These are different processes.  The DBUA process does not use exp/imp at all.

Usually, the DBUA approach will get you to the next Oracle version with the shortest amount of downtime.

The exp/imp approach will require more manual work, and will usually require more downtime.  But, this approach can result in a database with significantly better response times than if you use the DBUA approach.  This depends on your application and how it has used the database.  If you have some queue tables that have had lots of insert and deletes you may have a lot of wasted space in those tables now.  Or, if you have some tables that have had lots of updates that have have added lots of bytes per record, you may have lots of chained rows.  The exp/imp approach will clean up both of those problems.  The DBUA approach will not.
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 40557966
For how to use the exp/imp approach:
1. Create the new database using DBCA.
2. Manually create any non-default tablespaces that you need.
3. Manually create the user accounts (schemas) that you plan to import data for.
4. Manually create any other user accounts that you need.
5. Run the export(s) from the old system, and copy the *.dmp files to the new system.
6. In the new system, run an import for each *.dmp file.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40559090
markgeer,

"Usually, the DBUA approach will get you to the next Oracle version with the shortest amount of downtime."

yes, this is what i heard before! but the DB going upgrade need to have the version support by DBUA.

" But, this approach can result in a database with significantly better response times than if you use the DBUA approach"

I don't get it, why longest time but singificantly better response time? you mean during this time both old and new DB up and running at the same time but just new DB has very less data at the beginning, but getting more and more DURING exp/imp process, new DB is already serving from the beginning ?

""exp/imp approach will clean up both of those problems.  "

just because exp/imp do everythign once instead of keep deleting and updating ?

ok ,which one give less error once the migration is done when no other manual effect is needed?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40559378
any imcremental exp/imp for the data export? or we need to exp ALL data again if we see data in test database is out date ?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40559392
I am reading this :

http://docs.oracle.com/cd/E11882_01/server.112/e23633/expimp.htm#UPGRD12565

"8.Optionally, you can change the storage parameters from the source database.

You can pre-create tablespaces, users, and tables in the new database to improve space usage by changing storage parameters. When you pre-create tables using SQL*Plus, either run the database in the original database compatibility mode or make allowances for the specific data definition conversions that occur during import. When items have been pre-created, specify one of the following options:

•TABLE_EXISTS_ACTION=APPEND for Data Pump Import


•IGNORE=Y for original Import

"

where do I need to input those parameter?
0
 
LVL 34

Expert Comment

by:johnsone
ID: 40559521
This seems to be turning into a repeat of http://www.experts-exchange.com/Database/Oracle/Q_28581597.html

Again, the documentation that I posted will have the steps required for exp/imp or expdp/impdp if that is the method that you choose.

In terms of RMAN restore, you can restore the original instance onto a  system with 12 and then use dbua to do the upgrade.  If you are changing system architecture or OS, there may be additional steps to convert the data files, but these are outlined in the documentation.
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 40559718
"But, this [exp/imp] approach can result in a database with significantly better response times than if you use the DBUA approach"

"I don't get it, why longest time but singificantly better response time?"

The longest time refers to the amount of application down time that will be required for the actual data move if you use the exp/imp approach.  The better response time refers to the performance of the upgraded system *AFTER* the exp/imp or DBUA process is complete.  This better response time (every day) may help you for years.  The DBUA approach is usually easier and faster (for the upgrade process itself), but it will not give you the long-term performance gains that the exp/imp approach can give you.  And, as you are aware, the DBUA approach is limited to just certain DB versions.

Of course, to get faster response times, you can simply buy more or bigger database servers and pay Oracle more license fees every year.  But, if your application has caused lots of chained rows or lots of fragmented free space in tables, you may be able to get a large performance gain by using the exp/imp approach.  This depends on your database and your application though!  Maybe the exp/imp approach won't give you much of a performance improvement at all.  You will have to analyze your tables and examine the results to see if you have either of these problems now.  And, depending on the size of your database, maybe the exp/imp approach is not realistic for you.  If your total DB size is less than 200Gb, I think the exp/imp approach should work very well.  If your total DB size is over 5TB, you should try to use DBUA.  If your DB size is somewhere between those numbers, then you have to decide what will be best for you.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40561249
johnsone,

"In terms of RMAN restore, you can restore the original instance onto a  system with 12 and then use dbua to do the upgrade. "

the RMAN restore only restore data but not oracle system files, right? we are thinking if RMAN works, then why need exp/imp for that, right?

can't understandy why a system with 12 installed already can have the original instance restore to it? or you mean restore the whole oracle 10g/11g instance binary file and data to that new server, and then upgrade that instance to 12c without DBUA OR exp/imp again?



markgeer,

back to the last comment you post.

" If you have some queue tables that have had lots of insert and deletes you may have a lot of wasted space in those tables now.  Or, if you have some tables that have had lots of updates that have have added lots of bytes per record, you may have lots of chained rows.  The exp/imp approach will clean up both of those problems.  The DBUA approach will not. "

why then has a lot of chain rows? just because the inserted but deleted also record also goes to the 12c once upgraded?

can't see why exp/imp can't handle this then, it just expert all records and function, right?

yesterday I try on the test server it takes a lot of time for only 2 MB export files !

"but it will not give you the long-term performance gains that the exp/imp approach can give you.  And, as you are aware, the DBUA approach is limited to just certain DB versions.
"

you mean DBUA only applicatabile to the version that is in the direct upgrade path list ?

if long -term perfomrance gains by exp/imp is bigger than DBUA , I cna't see why people keep using DBUA more and less on exp/imp.

any why better performance then ? just because "lots of chained rows or lots of fragmented free space in tables" ?

any doc. to compare DBUA and exp/imp on this issue ? I just don't understand why it takes care the chain rows.

"If your total DB size is less than 200Gb, I think the exp/imp approach should work very well.  If your total DB size is over 5TB, you should try to use DBUA.  If your DB size is somewhere between those numbers, then you have to decide what will be best for you. "

ok, for large size, DBUA ..
0
 
LVL 34

Expert Comment

by:johnsone
ID: 40561767
RMAN should be backing up everything except the software binaries.  You don't need the 10 or 11 software to do the upgrade, on the 12 software.  Again, you need to look at the RMAN documentation to be sure additional steps are not required due to differences in machine architecture and/or OS.

This would still be doing the upgrade with dbua.  Which it seems like you have already decided you aren't doing.

exp/imp (or expdp/impdp) method will compact rows and remove any space left by deleted rows.  This is because you are starting with an empty table and inserting all the rows again.  dbua is just upgrading the system catalog and leaving the data files untouched.

Chained rows cannot be fixed by exp/imp or expdp/impdp.  By definition those are rows that are larger than a database block and there is no way that they can be changed.  What we are talking about are migrated rows (unfortunately called chained rows in a lot of locations).  A migrated row is a row that was updated and no longer fits in the block that it currently resides.  In this case the row is moved to another block and a pointer is left in its original location to the new location.  If you have a large number of these, it can affect performance as two I/O operations are required to retrieve them.  Since exp/imp is inserting the rows into an empty table there will no longer be any migrated rows as no updates are occurring.

Again, I am going to suggest that you spend some time with the Concepts manual.  You really cannot learn how to be a DBA from a question and answer site.
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 40561896
"If long-term performance gains by exp/imp is bigger than DBUA , I can't see why people keep using DBUA more and [use] ... exp/imp less" .  That is because DBUA is easier (more automated) and faster (for the actual upgrade process) and because on larger systems, this may be the only practical option.

When I used the term "chained rows" I was only thinking of the ones that johnsone described as "migrated rows", since these are indicated as "chained rows" by Oracle if you analyze your tables with the older SQL "analyze table..." command and explicitly ask for "chained rows" to be counted.  Oracle does not count or indicate "migrated rows" separately from "chained rows".

True, any rows that are actually longer than a single database block will remain chained and exp/imp cannot make these unchained (unless you use a larger block size in your new database).  But any of the rows that are what johnsone called "migrated rows" *WILL* be fixed by an exp/imp process.  Some tables in some Oracle systems have a large number of these rows, and these can add a big performance penalty.  (These rows will *NOT* be adjusted by a DBUA process.  They will all remain as they were.)

I'm a bit surprised by this statement: "a lot of time for only 2 MB export files!".  We did a database upgrade recently on a smaller system (about 100Gb) that we used the exp/imp approach for and the total process took only a few hours.  We have a larger system (around 8Tb) that we plan to use the DBUA approach on later this year.

But, yes, the DBUA approach is only applicable to a version that is in the direct upgrade path.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40563857
johnsone

"RMAN should be backing up everything except the software binaries.  You don't need the 10 or 11 software to do the upgrade, on the 12 software.  Again, you need to look at the RMAN documentation to be sure additional steps are not required due to differences in machine architecture and/or OS."

so instlal the oralce 12c on the new server and the RMAN everthing from 10gR2/11gR2 t othe new server and everything should be there and why still need exp/imp?

out team responsible for this upgrade use RMAN to copy everything from 10gR2/11gR2 old server to new 10gR2/11gR2 new server and they said only this method don't give the existing production 10gR2 down time.

I don't see exp/imp give down time too ! correct me if I am wrong.

"exp/imp (or expdp/impdp) method will compact rows and remove any space left by deleted rows.  This is because you are starting with an empty table and inserting all the rows again.  dbua is just upgrading the system catalog and leaving the data files untouched."

excellent! tks.

system catalog mean all metadata and oralce 12c binary file ?

 "Chained rows cannot be fixed by exp/imp or expdp/impdp.  By definition those are rows that are larger than a database block and there is no way that they can be changed. "

then why it can exist before migration ?

"  In this case the row is moved to another block and a pointer is left in its original location to the new location. "

by using DBUA , right ? so when running DBUA on new oracle 12c server, it create a pointer from old 10gR2 server to point to the new 12c server?
 
what if we remove the 10fR2 server later on and the point gone ? we can't access the 12c data quickly as index/point gone ?

I don't get it .

"In this case the row is moved to another block and a pointer is left in its original location to the new location."

the pointer and block are on the new server and the block is a new block on the new server which as larger size like from 64K to 256K?

markgeer,

"I'm a bit surprised by this statement: "a lot of time for only 2 MB export files!".  We did a database upgrade recently on a smaller system (about 100Gb) that we used the exp/imp approach for and the total process took only a few hours.  We have a larger system (around 8Tb) that we plan to use the DBUA approach on later this year."

I have the felling now.
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.

 
LVL 34

Assisted Solution

by:johnsone
johnsone earned 250 total points
ID: 40564031
RMAN will only restore the data files.  After you do that, you still need to upgrade either with dbua or exp/imp or expdp/impdp.  It isn't magic, it only restores.  Downtime is still required.

Catalog is just that.  The system catalog.  It would essentially include all database objects owned by SYS and probably some owned by SYSTEM.  It does not include the software binaries.

Chained rows are rows that are larger than 1 database block.  There is no way to fit 10K of data in an 8K block.  It physically is not possible.  If you do exp/imp (or expdp/impdp), there is no way those utilities can put 10K in an 8K block either.  They have nothing to do with the upgrade.

Migrated rows are different.  They are rows that were updated, due the update they are larger than they were (think updating a varchar to a longer value), and no longer fit in the block they were in.  exp/imp (or expdp/impdp) will correct these because these utilities start with an empty table and insert the records.  Any pointers are internal to the data files and do not cross databases or versions.  They have nothing to do with the upgrade.

Again, I recommend spending some time with the concepts manual and/or getting to some formal training.  All of these concepts are covered there.
0
 
LVL 34

Accepted Solution

by:
Mark Geerlings earned 250 total points
ID: 40564148
Neither an RMAN backup nor an export on your existing production system will cause any downtime there.  The downtime happens when you have to shut down your existing system, so no more transactions happen there, while you get the new system ready to accept transactions.  This downtime window will be shorter if you use RMAN and DBUA than if you use exp/imp.

The problem of migrated rows can be dealt with separately from the upgrade, if you will use DBUA for the upgrade.  Also, this can be done on just table at a time, either before or after your upgrade.  You most likely have just a few tables where these migrated rows cause problems, so you don't need to try to to do an exp/imp (or "alter table ... move..." command) for every table in your system!  You may want to plan to rebuild one or a few of these tables regularly (like every quarter, or once a year) if you know that your application regularly causes migrated rows.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40565913
johnsone,

"RMAN will only restore the data files.  After you do that, you still need to upgrade either with dbua or exp/imp or expdp/impdp.  It isn't magic, it only restores.  Downtime is still required."

so in the new machine , I have to install the same version of Oracle instance first and then restore by RMAN, thne ugprade using dbua or exp/imp, right?

the RMAN we backup the existing oracle's data, so downtime should not be here for existing system?

but for the new instance on new hardware, we will install the binary and then restore using RMAN, this one will have down time for sure, right?

"dbua is just upgrading the system catalog and leaving the data files untouched."

so what make sure that the new 12c can see the data files format from older database? DBUA already handle this ? that;s why it only upgrade sytsem schema (system catalog) ?

"If you do exp/imp (or expdp/impdp), there is no way those utilities can put 10K in an 8K block either."

so it means exp/imp only insert record one by one and by batches so it doesn't matter if it is chained block or not ? record redistributed anyway ?

markgeer,

"This downtime window will be shorter if you use RMAN and DBUA than if you use exp/imp."

the point is can RMAN restore 10gR2/11gR2 to 12c, if yes, who bother DBUA and pump exp/imp ?

then all upgrade can be done by only RMAN and oracle shouldn't implement DBUA at all, agree ?

"You may want to plan to rebuild one or a few of these tables regularly (like every quarter, or once a year) if you know that your application regularly causes migrated rows. "

this means table design from time to time chagned and no change management on this and therefore some time a table will then have chained record ?
0
 
LVL 34

Expert Comment

by:johnsone
ID: 40566162
If you are using RMAN to move the files from one machine to another, then your upgrade option is dbua.  You wouldn't use exp/imp.  Downtime would be required on both sides because you would want a cold consistent backup on the source side.  You wouldn't need the 10 or 11 software on the new machine, just 12.

RMAN is just a way to get the data files onto the new machine.  It does not modify the files and do some magical upgrade.

Also, I will mention again that depending on the architecture and OS of the old and new machines, additional steps may be required in RMAN to convert the files to the new machine.  Again, these are detailed in the documentation.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40570160
"If you are using RMAN to move the files from one machine to another, then your upgrade option is dbua.  You wouldn't use exp/imp."

what I mean is as RMAN don't back und restore binary data except data, so on the new target machine, I need to instla lthe same version of oracle instance as the backup data version, and then run DBUA on that new Oralce instnace to upgrade it to 12c ?

waht if the oracle instance we use RMAN from do not have an instance under the direct ugprade path of DBUA ? there are some instance like that.

"Also, I will mention again that depending on the architecture and OS of the old and new machines, additional steps may be required in RMAN to convert the files to the new machine.  Again, these are detailed in the documentation. "

I knew, as I am gooing to read htat.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40570164
please anser this:

"dbua is just upgrading the system catalog and leaving the data files untouched."

 so what make sure that the new 12c can see the data files format from older database? DBUA already handle this ? that;s why it only upgrade sytsem schema (system catalog) ?

 "If you do exp/imp (or expdp/impdp), there is no way those utilities can put 10K in an 8K block either."

 so it means exp/imp only insert record one by one and by batches so it doesn't matter if it is chained block or not ? record redistributed anyway ?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40570173
any pre upgrade procedure?
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 40570432
This isn't exactly correct:
"dbua is just upgrading the system catalog and leaving the data files untouched."

It is true that DBUA upgrades the system catalog (basically most objects that are in the SYSTEM tablespace).  And, DBUA doesn't do much with the other data files, but it does at least update the header of each data file to make sure they are compatible with the new database version.

An exp/imp process will insert each record individually into the new database, so it will clean up any migrated rows (those that were split across database blocks) as long as they are not larger than an indiviudal data block can accomodate.  Any records that were chained (because they are too large for a single database block) will remain chained after an exp/imp process.

One thing to check before starting an upgrade process is for invalid views and/or PL\SQL objects in the database.  Try to re-validate any/all of these before starting an upgrade.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40574689
is system catalog just like system schema ? which is you mean the owner is the systme itself, which contain version and system table structure ?

"Any records that were chained (because they are too large for a single database block) will remain chained after an exp/imp process.
"

but we discussed that the chained will be solved, right?

"Try to re-validate any/all of these before starting an upgrade. "

how to do this?
0
 
LVL 34

Expert Comment

by:johnsone
ID: 40574958
Most things in the SYS schema would be considered system catalog.  There may be a few things in the SYSTEM schema that could be considered catalog.

No, chained rows will not be corrected by exp/imp.  Migrated rows will.
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 40575340
How to revalidate PL\SQL objects and views?

Here is a script to identify them and give you the SQL commands that you need to run to re-compile them:

select 'alter '||object_type||' '||owner||'.'||object_name||' compile;' "Invalid objects"
from dba_objects
where status = 'INVALID'
and object_type in ('FUNCTION','PROCEDURE')
and object_name not like 'BIN$%'
union all
select 'alter package '||owner||'.'||object_name||' compile body;'
from dba_objects
where status = 'INVALID'
and object_type = 'PACKAGE BODY'
and object_name not like 'BIN$%'
union all
select 'alter package '||owner||'.'||object_name||' compile;'
from dba_objects
where status = 'INVALID'
and object_type = 'PACKAGE'
and object_name not like 'BIN$%'
union all
select 'alter view '||owner||'.'||object_name||' compile;'
from dba_objects
where object_type = 'VIEW'
and status = 'INVALID'
union all
select 'alter index '||owner||'.'||index_name||' rebuild nologging;'
from dba_indexes where status = 'UNUSABLE'
and index_name not like 'BIN$%'
union all
select 'alter index '||index_owner||'.'||index_name||' rebuild partition '||partition_name||' nologging;'
from dba_ind_partitions where status = 'UNUSABLE'
and index_name not like 'BIN$%';
0
 
LVL 34

Expert Comment

by:johnsone
ID: 40575359
If you want to just compile invalid object (basically everything markgeer posted without the index rebuilds), a script is supplied to do that.

Log in to the database as "/ as sysdba" and then run this:

@?/rdbms/admin/utlrp
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40577003
so revalidate  mean recompile ?

why we need that BEFORE the upgrade? not after upgrade you sure ?
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 40577480
Yes, you may need this after the upgrade.  But, it is best to at least check for invalid objects before the upgrade and try to compile them before the upgrade.  Ideally there are none before the upgrade.  But, if you don't check you don't know.  If you spot some invalid objects after the upgrade, you won't know if these were caused by the upgrade, or not, if you didn't check first.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40583481
"Yes, you may need this after the upgrade"

So this is ok if we do it BEFORE or AFTER, right?

"But, it is best to at least check for invalid objects before the upgrade and try to compile them before the upgrade.  "

"One thing to check before starting an upgrade process is for invalid views and/or PL\SQL objects in the database.  Try to re-validate any/all of these before starting an upgrade. "

when shoudl be do it? when using pump export, exp/imp or DBUA ? or any of this situation?

why it become invaid ?  so re-vaildate it can make it vaild again ? I am not sure what situtation it can be? file corrupted ?

""If you spot some invalid objects after the upgrade, you won't know if these were caused by the upgrade, or not, if you didn't check first. "

excellent !
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 40583842
Checking for and fixing invalid PL\SQL objects or views is independent of a database upgrade (and this is something you should be doing regularly).  But, if there are invalid objects, that can sometimes prevent a data base upgrade, or cause additional problems during a database upgrade.

How do these objects become invalid?  That can happen when someone applies a database (or possibly application) patch or if any developers or DBAs modify any of the database tables that PL\SQL objects or views use.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40586024
ok, tks.
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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
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…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

706 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

16 Experts available now in Live!

Get 1:1 Help Now