| migrate 8i to 10gr2 [message #191325] |
Tue, 05 September 2006 14:13  |
alanm
Messages: 284
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  |
joy_division
Messages: 4963
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 |
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 #192973 is a reply to message #192961] |
Thu, 14 September 2006 07:35 |
 |
Mahesh Rajendran
Messages: 10707
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. 
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 #193003 is a reply to message #192992] |
Thu, 14 September 2006 09:28 |
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 #193264 is a reply to message #193176] |
Fri, 15 September 2006 08:40 |
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 |
 |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
