Home » RDBMS Server » Server Administration » migrate 8i to 10gr2
migrate 8i to 10gr2 [message #191325] Tue, 05 September 2006 14:13 Go to next message
alanm
Messages: 282
Registered: March 2005
Senior Member

hello All,
Has anyone got any first hand experience of migrating from 8i to 10gr2. I have to investigate a migration of a 450GB database. any information is most welcome.

regards

Alan.
Re: migrate 8i to 10gr2 [message #191339 is a reply to message #191325] Tue, 05 September 2006 15:36 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
Migrate or upgrade in place?
For migration, I'd say the only way is to export and import. and be sure to export with the 8i export utility and import with the 10g import utility.
Re: migrate 8i to 10gr2 [message #191344 is a reply to message #191325] Tue, 05 September 2006 16:15 Go to previous messageGo to next message
kimant
Messages: 201
Registered: August 2006
Location: Denmark
Senior Member
Also make sure that You gather statistics using dbms_stats.
This is one of the major mistakes, I see often when going from 8i to 9i/10g.

Always test, test and test first!

Br
Kim
Re: migrate 8i to 10gr2 [message #191615 is a reply to message #191325] Thu, 07 September 2006 05:37 Go to previous messageGo to next message
alanm
Messages: 282
Registered: March 2005
Senior Member

thanks for the response guys.
Re: migrate 8i to 10gr2 [message #192811 is a reply to message #191339] Wed, 13 September 2006 13:50 Go to previous messageGo to next message
Henry_VA
Messages: 12
Registered: September 2006
Location: Northern VA
Junior Member
If I might jump in, can we do an export in 8i and use Data Pump Import in 10g? Would that work?
Re: migrate 8i to 10gr2 [message #192812 is a reply to message #192811] Wed, 13 September 2006 13:53 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
exp and expdp/impdp are different.
It wont work.
Re: migrate 8i to 10gr2 [message #192957 is a reply to message #192812] Thu, 14 September 2006 06:52 Go to previous messageGo to next message
goudelly
Messages: 52
Registered: August 2006
Location: India
Member

Hi,

exp and expdp are same but version different.

Note for you --> Mahesh Rajendran
Do give the incompelete answer.

If you dont know ask some one.


Thanks,


Mohan Reddy.

Re: migrate 8i to 10gr2 [message #192961 is a reply to message #192957] Thu, 14 September 2006 07:12 Go to previous messageGo to next message
tarundua
Messages: 1080
Registered: June 2005
Location: India
Senior Member

@Mohan

exp and expdp are entirely different and have entirely different ways of working.

if you wish, read this

http://www.oracle-base.com/articles/10g/OracleDataPump10g.php

10g supports both exp and expdp...

[Updated on: Thu, 14 September 2006 07:13]

Report message to a moderator

Re: migrate 8i to 10gr2 [message #192973 is a reply to message #192961] Thu, 14 September 2006 07:35 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
goudelly apparently wrote:
>> Do give the incompelete answer.
>> If you dont know ask some one.
Even if you insist, i prefer NOT TO GIVE incomplete answer. Smile
I always try my best. Sometimes i do post/think as a blithering idiot.
Everyone has their 'bad days'. At such times, i seek the advice
from the fine minds here in orafaq.

The question asked here is
>>If I might jump in, can we do an export in 8i and use Data Pump Import in 10g? Would that work?
My response was
>>exp and expdp/impdp are different.
But you claim,
>>exp and expdp are same but version different.

Fact is they are different even in the same version.
Here you go.
I took dumps with both exp and expdp.
I tried to use impdp to import dump created using exp.
As documentation clearly say, it will not work.

oracle@dubbel:/u02/expdir> exp scott/tiger owner=scott file=using_exp.dmp

Export: Release 10.2.0.1.0 - Production on Thu Sep 14 01:08:19 2006

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SCOTT
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SCOTT
About to export SCOTT's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SCOTT's tables via Conventional Path ...
. . exporting table                           DEPT          4 rows exported
. . exporting table                            EMP         14 rows exported
. . exporting table                     PLAN_TABLE          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.

oracle@dubbel:/u02/expdir> expdp scott/tiger schemas=scott directory=expdir dumpfile=using_expdp.dmp

Export: Release 10.2.0.1.0 - Production on Thursday, 14 September, 2006 1:10:34

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning and Data Mining options
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01":  scott/******** schemas=scott directory=expdir dumpfile=using_expdp.dmp
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
. . exported "SCOTT"."DEPT"                              5.609 KB       4 rows
. . exported "SCOTT"."EMP"                               7.726 KB      14 rows
. . exported "SCOTT"."PLAN_TABLE"                            0 KB       0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
  /u02/expdir/using_expdp.dmp
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 01:11:14


oracle@dubbel:/u02/expdir> ls -lrt
total 208
-rw-r--r--  1 oracle oinstall  16384 2006-09-14 01:08 using_exp.dmp
-rw-r--r--  1 oracle oinstall   1623 2006-09-14 01:11 export.log
-rw-r-----  1 oracle oinstall 192512 2006-09-14 01:11 using_expdp.dmp

oracle@dubbel:/u02/expdir> sqlplus -s scott/tiger@primary <<EOF
> drop table emp;
> drop table dept;
> drop table plan_table;
> exit;
> EOF

Table dropped.


Table dropped.


Table dropped.


oracle@dubbel:/u02/expdir> impdp scott/tiger@primary directory=expdir dumpfile=using_exp.dmp remap_schema=scott:scott

Import: Release 10.2.0.1.0 - Production on Thursday, 14 September, 2006 1:17:21

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning and Data Mining options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39143: dump file "/u02/expdir/using_exp.dmp" may be an original export dump file


oracle@dubbel:/u02/expdir> impdp scott/tiger@primary directory=expdir dumpfile=using_expdp.dmp remap_schema=scott:scott

Import: Release 10.2.0.1.0 - Production on Thursday, 14 September, 2006 1:19:09

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning and Data Mining options
Master table "SCOTT"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_FULL_01":  scott/********@primary directory=expdir dumpfile=using_expdp.dmp remap_schema=scott:scott
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"SCOTT" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."DEPT"                              5.609 KB       4 rows
. . imported "SCOTT"."EMP"                               7.726 KB      14 rows
. . imported "SCOTT"."PLAN_TABLE"                            0 KB       0 rows
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Job "SCOTT"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 01:19:17

[Updated on: Thu, 14 September 2006 07:36]

Report message to a moderator

Re: migrate 8i to 10gr2 [message #192983 is a reply to message #192973] Thu, 14 September 2006 08:44 Go to previous messageGo to next message
goudelly
Messages: 52
Registered: August 2006
Location: India
Member

Hi,

Mahesh Rajendran

See i can give these kind of examples.

That is version diferent and added more futures in 10g. and also parallely you run the expdp. And for ex. if you stop the exporting in between later on you can also resume the rest of the export.

You started the pinching

Thanks,

Mohan Reddy G



Re: migrate 8i to 10gr2 [message #192986 is a reply to message #192983] Thu, 14 September 2006 08:50 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Quote:

That is version diferent and added more futures in 10g. and also parallely you run the expdp. And for ex. if you stop the exporting in between later on you can also resume the rest of the export.
You started the pinching

I am sorry.
I do not understand what you are talking here.
EXP and EXPDP/IMPDP are different.
The dumpfiles created are not exchangeable IN ANY VERSION .
You cannot use IMPDP to import the dumpfile exported using exp.
Did i fail to say these before?
Re: migrate 8i to 10gr2 [message #192990 is a reply to message #192986] Thu, 14 September 2006 08:58 Go to previous messageGo to next message
goudelly
Messages: 52
Registered: August 2006
Location: India
Member

Hi,

You can't directly import the oracle8i database to 10g database. You have to 1st import the oracle 9i and then 10g databse. In init.ora you have to mention the version compatablity parameter. Then it will support.

Thanks,

Re: migrate 8i to 10gr2 [message #192992 is a reply to message #192990] Thu, 14 September 2006 09:08 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
You are now flip flopping.
What does these have to do with what you said before?
>> exp and expdp are same but version different.
and
>>You have to 1st import the oracle 9i and then 10g databse
We are talking about expdp/impdp.
NOT about export/import.
Meanwhile,
I am out of this very ***sensible*** conversation. Waste of my time.


Re: migrate 8i to 10gr2 [message #193003 is a reply to message #192992] Thu, 14 September 2006 09:28 Go to previous messageGo to next message
goudelly
Messages: 52
Registered: August 2006
Location: India
Member

Hi,

Read the artical :

Dont missguide to others.. The flop is "U" means "U" Mahesh Rajendran

Reorganize tablespaces using Oracle 10g Data Pump
by Bob Watkins

Oracle's export and import utilities were re-engineered in Release 10g. Buried among the more flashy features, such as the ability to disconnect and reconnect to a running export or import job, are a number of smaller changes that help the DBA reorganize tablespaces. Here are three of them.

Export tablespaces as a unit

In the past, the export (exp) and import (imp) utilities had three modes: You could export a single table and its dependent objects such as indexes; you could export all objects owned by a specific user; or you could export the entire database. But tablespaces were a problem. Objects owned by many different users could be stored in a given tablespace, but some of their objects might be stored in other tablespaces. So, the only solution was to query the data dictionary to find the exact list of tables and their owners and use table-mode export to export the objects individually.
In Oracle 10g, the Data Pump version of export (expdp) lets you directly export all the objects in a tablespace. The TABLESPACES parameter lets you specify which tablespace(s) you want to export.

TABLESPACES=name [,...]

This is particularly useful if you've inherited a database with a lot of dictionary-based tablespaces, and you want to reduce fragmentation by recreating the tablespaces as locally managed, and then re-import the contents.

Rename datafile names during import

When migrating a database from one platform to another prior to 10g, the DBA was required to pre-create the tablespaces and their datafiles before importing. Why? Because the dump file created by export contained datafile pathnames in the format of the original database's operating system. These pathnames would cause errors if used with a different operating system on import.
In the 10g Data Pump version of import (impdp), the REMAP_DATAFILE parameter can be used to rename these datafiles on the fly. The format is:

REMAP_DATAFILE=source_datafile:target_datafile

This option is used with FULL imports only, and the userID you specify must have the IMP_FULL_DATABASE role.
Change tablespace names during import

The impdp utility also lets you load objects into different tablespaces than they came from originally. Before 10g, the way to do this was complex. First, you had to remove your quota on the original tablespace so that you had no privileges to write there. Then, you set your default tablespace to the desired one. During the import, objects that were in the original tablespace would be stored in the default tablespace for the user. Then you had to remember to set everything back again when you were done.
In 10g import, the REMAP_TABLESPACE parameter makes this a lot easier. You still need to have quota on the destination tablespace, but no other preparation is required. Simply add the parameter:

REMAP_TABLESPACE=source_tablespace:target_tablespace

Objects will be automatically sent to the new tablespace.

Thnaks,

Original article
[mod-edit: added source and author]

[Updated on: Sat, 16 September 2006 02:49] by Moderator

Report message to a moderator

Re: migrate 8i to 10gr2 [message #193176 is a reply to message #193003] Fri, 15 September 2006 03:05 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Mohan,

1. Give credit where credit is due. A copy of an entire article that is not by your hand without mentioning the source/author is impolite. Update your message to include the author (and eventually the link to the original article).
2. Read posts before blindly replying: Mahesh just stated that EXP and EXPDP are different. That's all. And you know what? He is right: they are different. Don't feel personally attacked when facts are presented.
3. Have respect for other forum members. Don't start a flame war.
4. "U" is not a forum member. Use plain English and be polite.
5. Take a deep breath and relax Wink

MHE
Re: migrate 8i to 10gr2 [message #193264 is a reply to message #193176] Fri, 15 September 2006 08:40 Go to previous messageGo to next message
Henry_VA
Messages: 12
Registered: September 2006
Location: Northern VA
Junior Member
Dear friends,
From not knowing enough and inexperience in Oracle technology, I apologize for asking the newbie question (about using 'exp' and 'impdp' for data migration here) that seems to initiate these intense, heated exchanges here under this topic.

But I agree with Maaher's latest posting. Let's continue to express ourselves in ways that we'd like to see others express themselves to us. That is with calmness and self respect and respect for others!
After all, we've learned a lot about ourselves, our short comings... only when we are facing difficult and stressful situations, or when encounter strong disagreements from others.

Again I apologize and thank you all for participated in the discussion of this topic and please continue to do so.
Henry,
Re: migrate 8i to 10gr2 [message #193287 is a reply to message #192990] Fri, 15 September 2006 10:05 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
goudelly wrote on Thu, 14 September 2006 08:58

You can't directly import the oracle8i database to 10g database. You have to 1st import the oracle 9i and then 10g databse. In init.ora you have to mention the version compatablity parameter. Then it will support.


goudelly, as joy_division indicated above, exporting DIRECTLY from 8i to 10g IS SUPPORTED !! As others have expressed in other posts, you should really try to validate your comments BEFORE you post. You have a post count of 40 and a fair percentage of those include INCORRECT suggestions/statements. I realize that not everyone is right all of the time and facts can be misconstrued even after the due diligency of researching a solution to a problem, but I really think you should respect the other members (especially senior members) of the forum.
Previous Topic: Proben in schemas - urgent
Next Topic: send SNMP trap in case of a job failure
Goto Forum:
  


Current Time: Wed Dec 07 04:58:13 CST 2016

Total time taken to generate the page: 0.09950 seconds