OraFAQ Forum: Server Administration » Export and Import

Home » RDBMS Server » Server Administration » Export and Import

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Export and Import [message #213936]

Fri, 12 January 2007 19:24

plshelp
Messages: 205
Registered: January 2007

Senior Member

Hi,

I have 3 dmp files given to me and it has all the exported table info as below:
. . exporting table AES_EXPOSED_DEVICE_UPDATE 34 rows exported

I also have one file as import.par. I have to import using these files. Could anyone give me the steps what I should do.
I will appreciate it.

Thanks
Geetha.

[Updated on: Mon, 15 January 2007 17:21] by Moderator

Report message to a moderator

Re: export and import [message #213937 is a reply to message #213936]

Fri, 12 January 2007 20:53

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

You should Read The Fine Utilities Manual found at http://tahiti.oracle.com which completely documents the IMPORT (imp) utility

Report message to a moderator

Re: export and import [message #213948 is a reply to message #213936]

Fri, 12 January 2007 22:37

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

Hi,
. exporting table AES_EXPOSED_DEVICE_UPDATE 34 rows exported

Above message is only point "AES_EXPOSED_DEVICE_UPDATE" table is export with data.

Now you want to import all three files ?

also have one file as import.par
Can you post import.par file contents.

regards
Taj

Report message to a moderator

Re: export and import [message #214011 is a reply to message #213936]

Sat, 13 January 2007 09:38

plshelp
Messages: 205
Registered: January 2007

Senior Member

Hi,,

I have 3 files of dmp files and also one file as import.par.

"exporting table AES_EXPOSED_DEVICE_UPDATE 34 rows exported"
Above is one of an example contained in one dmp file.

Below is the content in the import.par file:

rows=Y

tables=(AES_ADDL_HISTORY_AUDIT,AES_ADDL_HISTORY_DATA,AES_ADDL_HISTORY_UPDATE,AES_ADDL_INFO_AUDIT,AES_ADDL_INFO_DATA,AES_ADDL_INFO_UPD ATE,AES_CEV_DRUG_AUDIT,AES_CEV_DRUG_DATA,AES_CEV_DRUG_UPDATE,AES_CLINICAL_EVENT_AUDIT,AES_CLINICAL_EVENT_DATA,AES_CLINICAL_EVENT_UPDA TE,AES_CODES_DEV_AUDIT,AES_CODES_DEV_DATA,AES_LAB_DATA,AES_LAB_UPDATE,AES_REPORTER_AUDIT,AES_REPORTER_DATA,AES_REPORTER_UPDATE,AES_RE PORT_AUDIT,AES_REPORT_DATA,AES_REPORT_UPDATE)

I am supposed to import with these files.
I would appreciate if you could tell me the steps I should follow.

Thanks,
geetha.

Report message to a moderator

Re: export and import [message #214016 is a reply to message #214011]

Sat, 13 January 2007 11:03

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

Hi,
exporting table AES_EXPOSED_DEVICE_UPDATE 34 rows exported"
Above is one of an example contained in one dmp file.

If above is content then it is not dump file it is log file.
because dump file is binary file and you cann't read easily in text editor.

e:\>exp system/oracle parfile=e:\>import.par log=path

regards
Taj

Report message to a moderator

Re: export and import [message #214017 is a reply to message #213936]

Sat, 13 January 2007 11:40

plshelp
Messages: 205
Registered: January 2007

Senior Member

Hi,

I have a cd with 3 dmp files ind_co.dmp, ind_mo.dmp, ind_po.dmp
and 3 text files ind_co,ind_mo,ind_po.
"exporting table AES_EXPOSED_DEVICE_UPDATE 34 rows exported"
This example is from one of the text file.

And along with these 6 files in the cd, there is also one import.par file as I mentioned earlier. So, if i am understanding you correctly, is ind_co(text file)is it a log file????

Since I have the export dmp files, my thinking is to ftp these dmp files from my system into the database and then import the same. Please correct me if i am wrong. Also, if you could provide me with the steps i would appreciate it.

Thank you very much
Geetha

Report message to a moderator

Re: export and import [message #214020 is a reply to message #214017]

Sat, 13 January 2007 12:03

Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

It looks like those "text" files really are export log files. If all of them have only one "exporting table TABLE_NAME n rows exported" line, it means that every export file contains only one table.

You can always check it; it is important that you have access to the database server (so that you could import data into); if you have access to the server itself, fine - you have IMP.EXE too. But, if your database is somewhere else, you'll have to have Oracle Client installation (which should include Oracle Utilities - one of them is Import Utility).

To see what is in any export file (.DMP), simply issue (at the operating system prompt) such a command:

OS> IMP username/password FILE=export_file.dmp SHOW=Y

SHOW parameter will only list contents of the export file. Now, if there are several tables, you can choose to import all of them, or some of them. First option is done by

OS> IMP username/password FILE=export_file.dmp

while only some of them are imported using

OS> IMP username/password FILE=export_file.dmp TABLES=(first_table, second_table, third_table)

That should be all; if you are about to FTP those DMPs, you must use BINARY mode (otherwise, import might not be able to read it).

Finally, no matter what we say, perhaps you should read the Export and Import (Data Pump) utilities user's guide (just like Anacedent already told you). That would be the right way to learn why and what to do.

Report message to a moderator

Re: export and import [message #214021 is a reply to message #213936]

Sat, 13 January 2007 12:03

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Which version of Oracle made the *dmp files?
Into which version of Oracle should the *dmp files get imported?
(Versions to 4 decimal places, please)

Report message to a moderator

Re: export and import [message #214027 is a reply to message #213936]

Sat, 13 January 2007 13:58

plshelp
Messages: 205
Registered: January 2007

Senior Member

Thanks very much all. Those info were really helpful.
The dmp files were generated by Oracle 8.1.7.4.
I have to imp the files in the same version.
Lastly one final question, do I have to know in which schema I have to import the data?

Thanks,
Geetha

Report message to a moderator

Re: export and import [message #214043 is a reply to message #214027]

Sat, 13 January 2007 22:09

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

Hi,
Lastly one final question, do I have to know in which schema I have to import the data?
It is depand on you.
before import any schema plz run catexp.sql scripts.
above script create neccassary view and grant priviliges.
-----------------------------------------------------------
first import all three dump file and after that you have also import.par parfile.
if your parfile
see my earilar post how you import parfile.
--------------------------------

first try if you getting any error plz post here.

remember for every import operation please create log file with log parameter.

regards
Taj

Report message to a moderator

Re: export and import [message #214046 is a reply to message #214027]

Sun, 14 January 2007 00:15

Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

plshelp

Do I have to know in which schema I have to import the data?

Imports are usually done for some reason. One of them is, for example, that you accidentally dropped a table and now you are restoring it from the export file.

In my opinion, yes - you should know where to import those tables(1). Schema is identified by user's name (it is "username/password", the part of the IMP command).

@Taj: as both export and import are using the same database version (8.1.7.4), is it necessary to run CATEXP.SQL? Wouldn't import work as it is?

_______
(1) ... unless it is a full database export, performed by a privileged user. Then you don't care what belongs to who, because IMP will do the job for you - create users, import objects, etc.

Even then, if a privileged user uses full database export but imports only one user, you should know it in order to specify FROMUSER and TOUSER parameters.

Report message to a moderator

Re: export and import [message #214048 is a reply to message #214046]

Sun, 14 January 2007 00:29

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

is it necessary to run CATEXP.SQL? Wouldn't import work as it is?

No,
forget to read some version.
sorry

regards
Taj

Report message to a moderator

Re: export and import [message #214076 is a reply to message #213936]

Sun, 14 January 2007 18:40

plshelp
Messages: 205
Registered: January 2007

Senior Member

Hi everybody,

Thanks for all the info. I am going to import the data tomorrow. I will keep it posted if i get any errors.

Thanks for your help
Geetha.

Report message to a moderator

Re: export and import [message #214266 is a reply to message #213936]

Mon, 15 January 2007 10:44

plshelp
Messages: 205
Registered: January 2007

Senior Member

Hi,

I am doing the import now.. when i ran the script, it got stuck in one table for 40mins now. I am not sure if this is normal and if I am not sure if i have to check something like space etc..
Please advise.

Thanks
Geetha

Report message to a moderator

Re: export and import [message #214267 is a reply to message #214266]

Mon, 15 January 2007 10:47

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

Hi,
check your alert.log file for any error.

regards
Taj

Report message to a moderator

Re: export and import [message #214270 is a reply to message #213936]

Mon, 15 January 2007 11:01

plshelp
Messages: 205
Registered: January 2007

Senior Member

Hi,

I dont see any error in the alert log. Below is what I see in the alert log:

Mon Jan 15 11:06:51 2007
Thread 1 advanced to log sequence 1076
Current log# 2 seq# 1076 mem# 0: /u03/oradata/eg/redo2a.log
Current log# 2 seq# 1076 mem# 1: /u04/oradata/eg/redo2b.log
Mon Jan 15 11:06:51 2007
ARC0: Beginning to archive log# 1 seq# 1075
ARC0: Completed archiving log# 1 seq# 1075

Please if you could tell me the command if I have to check the space if there is enough space available. I will really appreciate it.Thank you

Report message to a moderator

Re: export and import [message #214272 is a reply to message #213936]

Mon, 15 January 2007 11:05

plshelp
Messages: 205
Registered: January 2007

Senior Member

Hi,

When i try to login,
Enter user-name: system/pwd@eg
ERROR:
ORA-00020: maximum number of processes (150) exceeded

Please tell me how do I proceed further now. I wonder if this could be the reason for the import of one table taking over 1hr.

Report message to a moderator

Import [message #214303 is a reply to message #213936]

Mon, 15 January 2007 15:15

plshelp
Messages: 205
Registered: January 2007

Senior Member

Hi,

When I ran the import.sh script, it gets stuck with one table but I see all the tables are getting imported fine in the log file.. Please advise what the issue is...I appreciate it.
Thanks
Geetha

Report message to a moderator

Re: Import [message #214304 is a reply to message #214303]

Mon, 15 January 2007 15:18

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

>> When I ran the import.sh script
What might this import.sh script contain?
and post your import logfile

[Updated on: Mon, 15 January 2007 15:25]

Report message to a moderator

Re: Import [message #214315 is a reply to message #214303]

Mon, 15 January 2007 16:19

ebrian
Messages: 2794
Registered: April 2006

Senior Member

Why not continue with your original thread ?

Report message to a moderator

Re: Import [message #214317 is a reply to message #214315]

Mon, 15 January 2007 17:22

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Topics Merged.

Report message to a moderator

Re: Export and Import [message #214318 is a reply to message #213936]

Mon, 15 January 2007 17:35

plshelp
Messages: 205
Registered: January 2007

Senior Member

Hi,

I am sorry that I started with a new topic. I got the error as netowrk connection out, I figured out the problem is with the network it kicked me out of the session after a few minutes. But now I am trying to run the import.sh as

nohup import.sh &

By running the script at the background, will it take even longer???? When i typed this above command,it showed with the following line and never proceeded further for 20mins:

sending output to nohup.out

please give me your suggestions.
Thanks
Geetha

Report message to a moderator

Re: Export and Import [message #214324 is a reply to message #214318]

Mon, 15 January 2007 19:33

ebrian
Messages: 2794
Registered: April 2006

Senior Member

If you are running the import remotely and you are having network issues, then you need to resolve the unstable network prior to performing the import. If you are telneting to the remote box and kicking off the import with nohup, then the import should finish in the same time as if you telneted to the box and kicked the import off without the nohup.

How big is the dump file that you are importing?

Report message to a moderator

Re: Export and Import [message #214328 is a reply to message #213936]

Mon, 15 January 2007 20:43

plshelp
Messages: 205
Registered: January 2007

Senior Member

Hi,

I dont know how to find out how big my dump file is. But I am sure its pretty huge. Dmp file is the export of production backup. There is 16734669 rows in it. Now the import is half the way thro'.. but its been 1hr 15mins now since it started importing system_audit_trail table(has 12875459 rows in it)..
Not sure if it got stuck or if there is anything else wrong with it..there is no error in the alert log.. and in the nohup.out file i see as below

. . importing table "SYSTEM_AUDIT_TRAIL" [artee01]/appl/oracle/
admin/eg/export>

Please give me your suggestions.
Thanks,
Geetha.

Report message to a moderator

Re: export and import [message #214329 is a reply to message #214272]

Mon, 15 January 2007 22:15

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

Hi,
ORA-00020: maximum number of processes (150) exceeded

Increase you PROCESSES parameter. this parameter is not dynamic parameter you have to shutdown and startup your database like
---------------

shutdown immediate;
First edit in your pfile 
found >>> oraclehome/dbs/init<sid>.ora
increase PROCESSES parameter.
create spfile from pfile;
startup normal;

regards
Taj

Report message to a moderator

Re: Export and Import [message #214442 is a reply to message #213936]

Tue, 16 January 2007 08:21

plshelp
Messages: 205
Registered: January 2007

Senior Member

Thanks very much for all your help. I increased the processes parameter in init file and it works now.. Import is completed successfully.

Report message to a moderator

Re: Export and Import [message #214444 is a reply to message #214442]

Tue, 16 January 2007 08:23

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

Hi,
Great !!!!

regards
Taj

Report message to a moderator

Re: Export and Import [message #214735 is a reply to message #213936]

Wed, 17 January 2007 18:29

plshelp
Messages: 205
Registered: January 2007

Senior Member

Hi,

I have new issue now. I am trying to import one dmp file on my local machine.Below is what i did:

C:\oracle\exportDump>imp eman/eman file=c:\oracle\exportdump\
dumprt.dmp rows = y log = c:\oracle\imp.log

Import: Release 8.1.7.4.1 - Production on Wed Jan 17 19:22:00 2007

(c) Copyright 2000 Oracle Corporation. All rights reserved.

Connected to: Oracle8i Enterprise Edition Release 8.1.7.4.1 - Production
With the Partitioning option
JServer Release 8.1.7.4.1 - Production

Export file created by EXPORT:V08.01.07 via conventional path

Warning: the objects were exported by JDS, not by you

import done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set
. importing TYS's objects into EMAN
Import terminated successfully without warnings.

This is what i got. And its the same thing in the log file too.
But when i checked in sql*plus by login as eman,and when i did

select * from tab;
no rows selected

Please give me suggestions what to do.
Thank you.

Report message to a moderator

Re: Export and Import [message #214744 is a reply to message #213936]

Wed, 17 January 2007 19:45

plshelp
Messages: 205
Registered: January 2007

Senior Member

hi,

I have 3 dmp files with same tablenames but different number of rows exported for each table in all three files. I imported 2 dmp files to user eman. Imported successfully.
But the 3rd dmp file(with same table names as the other 2 dmp files but different number of rows exported),when I imported the 3rd last file, import completed successfully except 1 table

It gave the below error

..importing table "audit_event"
IMP-00058:ORACLE error 904 encountered
ORA-00904:invalid column name
Import terminated successfully with warnings.

What I wonder is, the same table got imported successfullly when I imported the 1st two files which had the same tablename.
Now I dont know why it is not importing when I import the 3rd dmp file.Please give me your suggestions.

Thank you.
Geetha

Report message to a moderator

Re: Export and Import [message #214754 is a reply to message #214744]

Wed, 17 January 2007 22:43

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

Hi,
For your first issue.
Import terminated successfully without warnings
Your import done successfully without any error.
because your table having 0 rows in table.


SQL> create table deni ( no number, name varchar2(10),ddate date);

Table created.

SQL> host
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

E:\>set oracle_sid=db02

E:\>exp scott/tiger file=d:\data\deni.dmp log=d:\data\deni.log tables=deni

Export: Release 9.0.1.1.1 - Production on Thu Jan 18 08:23:37 2007

(c) Copyright 2001 Oracle Corporation.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                           DENI          0 rows exported
Export terminated successfully without warnings.

IMP-00058:ORACLE error 904 encountered
Possibly table defination changed.
http://www.dbasupport.com/forums/archive/index.php/t-34251.html

regards
Taj

Report message to a moderator

Re: Export and Import [message #214899 is a reply to message #214744]

Thu, 18 January 2007 08:04

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

plshelp wrote on Wed, 17 January 2007 20:45

But the 3rd dmp file(with same table names as the other 2 dmp files but different number of rows exported)

Maybe the same table name, but surely a different structure.

Report message to a moderator

Re: Export and Import [message #214930 is a reply to message #213936]

Thu, 18 January 2007 11:54

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

$ imp help=yes
$ imp file=c:\oracle\exportdump\dumprt.dmp list
====================================================
post the results from the command immediately above

Report message to a moderator

Re: Export and Import [message #214963 is a reply to message #213936]

Thu, 18 January 2007 15:11

plshelp
Messages: 205
Registered: January 2007

Senior Member

Hi,

Yes. The table structure changed when the 3rd export was taken.
Thats the problem. Since I have already exported the same tablenames with rows, I am not going to worry about this dmp file anymore. Thanks for all your help guys.

Report message to a moderator

Previous Topic:	Multiple databases with different time zones
Next Topic:	Redo log Files and RAID5

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 06:24:35 CDT 2024