We have a requirement to move to Oracle 10g from Oracle 9i. As I
understand, there are 2 options to do it:
Option-1)
--------- 
  - Execute Oracle 9i->10g upgrade steps. The data which was in Oracle
9i is now available in Oracle 10g database automatically.

Option-2) 
---------
  - Export the data contained in Oracle 9i, using Oracle 9i version of
'exp' tool
  - Clean-up the Oracle 9i database and create a new Oracle 10g
database.
  - Do an import of the previously exported data into new Oracle 10g
database, using Oracle 10g version of 'imp' tool.

Could anybody tell about in which scenarios each of the above
mechanism is preferable to the other one?

If db is small use imp/exp
If db is large use upgrade steps

On 18 Feb 2005 09:58:48 -0800, qazmlp1209@rediffmail.com (qazmlp)
wrote:

>We have a requirement to move to Oracle 10g from Oracle 9i. As I
>understand, there are 2 options to do it:
>Option-1)
>--------- 
>  - Execute Oracle 9i->10g upgrade steps. The data which was in Oracle
>9i is now available in Oracle 10g database automatically.
>
>Option-2) 
>---------
>  - Export the data contained in Oracle 9i, using Oracle 9i version of
>'exp' tool
>  - Clean-up the Oracle 9i database and create a new Oracle 10g
>database.
>  - Do an import of the previously exported data into new Oracle 10g
>database, using Oracle 10g version of 'imp' tool.
>
>Could anybody tell about in which scenarios each of the above
>mechanism is preferable to the other one?

........
We use Oracle 8.1.7.4 and 9.2.0.5 on Solaris 2.7 boxes
remove NSPAM to email

you've already tested your app code against a 10g database, right?
I've seen 2 gotchas posted on the oracle-l list in the past 2 days.
the 10.1.0.3 patchset has been out for awhile, but everyone's app code
.... and mileage will vary.

test your migration ... test your app code against the migrated
database(s) ... and then plan migrating the live database(s).

-bdbafh

bdbafh@gmail.com wrote:

>you've already tested your app code against a 10g database, right?
>I've seen 2 gotchas posted on the oracle-l list in the past 2 days.
>the 10.1.0.3 patchset has been out for awhile, but everyone's app code
>... and mileage will vary.
>
>test your migration ... test your app code against the migrated
>database(s) ... and then plan migrating the live database(s).
>
>-bdbafh
>

What is the oracle-1 list and where may it be accessed?

My shop is in the beginning stages of planning a migration from 8i to
10g, so I've been watching this thread to see if anyone mentions any
"gotchas" we may encounter.  We do plan to test just as you've
desccribe, and right now our DBA is simply trying to get 10g stood up
in an isolated environment.  I'm not sure our DBA would be aware of
the oracle-1 list you mention, so I'd like to tell him about it.

 
Being ordinary and nothing special is a full-time job.
jp_mcmahon@hotmail.com (Jim McMahon in real life)

jp_mcmahon@hotmail.com (Jim McMahon) wrote in 
news:42187153.219200924@nntp.charter.net:

> in an isolated environment.  I'm not sure our DBA would be aware of
> the oracle-1 list you mention, so I'd like to tell him about it.


http://www.freelists.org/webpage/oracle-l

The oracle-l mailing list was moved to freelists.org some time ago.

http://www.freelists.org/list/oracle-l

Its archives can be accessed here:

http://www.freelists.org/archives/oracle-l/

Much changed in the Oracle Cost Based Optimizer (CBO) between 8i and 9i
(more specifically, 8.1.7 to 9.2).
A change that affected my employer's code was transitivity.
A lack of explicit joins in 9.2 that were not required in 8.1.7 caused
nested loops plans to go to hash joins.

Wolfgang Breifling has a series of papers on his site that may be
helpful:
http://www.centrexcc.com/

the one that may be most relevant to you is "What is new in the Oracle
9i CBO ".

If you have Metalink support, I'd suggest that your DBA start with the
following notes:
Note:258945.1 - Upgrading from 8.1.X to 9.X - Subquery Issues -
Diagnosing and Resolving
Note:258167.1 - Upgrading from 8.1.X to 9.X - Potential Query Tuning
Related Issues

As always, Oracle provides its documentation online:
http://otn.oracle.com/pls/db10g/portal.portal_demo3?selected=1

Its "upgrade guide" can be found here:
http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10763/toc.htm

hth.

-bdbafh

NetComrade wrote:

> If db is small use imp/exp
> If db is large use upgrade steps

And not matter which you choose take a verifiably good backup
before proceeding.
-- 
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)

Thanks for the references.  I can't access Metalink but our DBA can,
so I'll pass those references on to him.


Being ordinary and nothing special is a full-time job.
jp_mcmahon@hotmail.com (Jim McMahon in real life)