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 #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.
|
|
|
|
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
|
|
|
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.
|
|
|
|
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
|
|
|
|
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.
|
|
|
|
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.
|
|
|
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
|
|
|
|
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
|
|
|
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.
|
|
|
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
|
|
|
|
|
|
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
|
|
|
|
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.
|
|
|
|
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.
|
|
|
|
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.
|
|
|
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
|
|
|
|
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.
|
|
|
|
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.
|
|
|