Home » RDBMS Server » Server Utilities » Export/ Import from 9i to 10g ( Oracle,Solaris 10)
Export/ Import from 9i to 10g [message #358845] Wed, 12 November 2008 12:01 Go to next message
dbasupport
Messages: 11
Registered: November 2008
Location: dallas
Junior Member
Hi All,

I need to do a full database export from my server which is oracle 9i i just need the structure i dont need the rows. and import it to another server which has oracle 10g.

What is the best way to do it. The db size is arround 200g.

Thanks,

Report message to a moderator

Re: Export/ Import from 9i to 10g [message #358848 is a reply to message #358845] Wed, 12 November 2008 12:15 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Use the smaller version to export and same version to import.
In your case, use 9i to export and 9i version of imp tool to import into 10g database.
During export, use rows=n and compress=n .

Report message to a moderator

Re: Export/ Import from 9i to 10g [message #358851 is a reply to message #358845] Wed, 12 November 2008 12:31 Go to previous messageGo to next message
dbasupport
Messages: 11
Registered: November 2008
Location: dallas
Junior Member
Thanks for the quick reply Mahesh.

When i use the import utility in 10g how do i ensure that it will use 9i version of imp tool to import into 10g database. and is there any other consideration before exporting the database and before importing it. how can i expedite the export and import process in my case.

Thanks,

Report message to a moderator

Re: Export/ Import from 9i to 10g [message #358855 is a reply to message #358851] Wed, 12 November 2008 12:52 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>When i use the import utility in 10g how do i ensure that it will use 9i version of imp tool to import into 10g database

No. Please read my response above.
You do not want to use import utility in 10g.
Use imp utility in 9i to import into 10g database.

From 9i machine, export the local 9i database.

exp user/pass@local9i .....


From same 9i machine import into 10g database.

imp user/pass@remote10g ...



>>how can i expedite the export and import process in my case.
Cannot comment without knowing "your case" and your environment.
You are not exporting/importing data. So 200g database makes a small impact.
You are importing only the structure(tables/indexes/procedures/objects).
I would precreate the tablespaces in target.
Precreate the users
Import selectively using fromuser/touser clause (if there are less users) as it offers more control.
If exporting only tables/indexes would use dbms_metadata to extract ddl and apply in target.

[Updated on: Wed, 12 November 2008 12:58]

Report message to a moderator

Re: Export/ Import from 9i to 10g [message #359490 is a reply to message #358855] Sun, 16 November 2008 21:19 Go to previous messageGo to next message
dbasupport
Messages: 11
Registered: November 2008
Location: dallas
Junior Member
If i exp full =y rows=n, aren't the users in the database also exported from where i am running the utility?

Report message to a moderator

Re: Export/ Import from 9i to 10g [message #359498 is a reply to message #359490] Sun, 16 November 2008 23:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, they are.

Regards
Michel

Report message to a moderator

Re: Export/ Import from 9i to 10g [message #359516 is a reply to message #359498] Mon, 17 November 2008 01:15 Go to previous messageGo to next message
dbasupport
Messages: 11
Registered: November 2008
Location: dallas
Junior Member
i am able to do a full database export without warning with option full=y rows=n statistics=none, but when i do an import
full=y ignore=y i am not able to see objects structures that where in the database i exported from. The message i got at the end of my import is : Import Terminated successfully with warnings and when i check the log i have lots of Imp-0003,ora-30371,Imp-00017. Can some one suggest. the exact syntax i used for import is :imp \'/ as sysdba\' file=file.dmp ignore=y full=y


Thankyou for you suggestions.

Report message to a moderator

Re: Export/ Import from 9i to 10g [message #359517 is a reply to message #359516] Mon, 17 November 2008 01:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you think it is not relevant to post the log file?
With what you posted the only thing we do say is:
ORA-30371: column cannot define a level in more than one dimension
 *Cause: A column was used in the definition of a level after it had
         already been used to define a level in a different dimension.

 *Action: Reorganize dimension levels and hierarchies into a single
          dimension such that no column is used to define levels in
          different dimensions.  There is no limit on the number of
          levels or hierarchies you can place in a dimension.  A column can
          be used to define any number of levels provided all such levels
          are in the same dimension and provided no two levels contain
          identical sets of columns.

Regards
Michel

Report message to a moderator

Re: Export/ Import from 9i to 10g [message #359609 is a reply to message #359517] Mon, 17 November 2008 10:19 Go to previous messageGo to next message
dbasupport
Messages: 11
Registered: November 2008
Location: dallas
Junior Member
Thanks for the reply, but i am not able to interpreate it. can you please suggest what should i do so that i will have a clean import without errors. Is the imp command i am using not proper or is there some thing else causing the warning and errors.

Report message to a moderator

Re: Export/ Import from 9i to 10g [message #359611 is a reply to message #359609] Mon, 17 November 2008 10:39 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
You should use the 10g version of the import utility.

Report message to a moderator

Previous Topic: Errors in impdp command on oracle 10g database
Next Topic: Export terminated successfully with warnings
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Wed May 01 03:15:59 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.