Import .dmp log file contents

Posted on 2009-05-06
Last Modified: 2013-12-18
We are using Oracle 11g.
Recently, Config. Management Team imported a wrong dump (previous version) but we could not track it which dump file is being imported from the log file.  The log file only mentions the import errors but not the file being imported

Is there anyway to record all the commands or the file name to import in text/log file?
Question by:ajexpert
  • 4
  • 2
  • 2
  • +1
LVL 40

Expert Comment

ID: 24316338
imp/impdp are external programs and can be run over the network, so the Oracle instance itself may not know about the source of the data (ie. dmp filename), just the data that is being fed to it from imp/impdp.

If it was run from a UNIX shell it might be in the .history of that user's shell. Try "history" command or "history | grep imp"

history is not totally reliable because it can be cleared by the user. However, if you force the load to be done via sudo, then sudo can log it.

I would look to the OS for this sort of auditing.
LVL 14

Author Comment

ID: 24316348
We are not using impdp, conventional imp command via windows
LVL 48

Accepted Solution

schwertner earned 90 total points
ID: 24316809
The conventional Export is depricated in 11g and conventional Import is supported only for compatibility with previos versions.

If you switch to Data Pump you can use the package DBMS_DATA_PUMP.
It can help you to switch from direct invocation of Data Pump to invocation through the above mentioned API. One of the parameter you give is the name of the file. The good news is that the API has a command that is intendent to write character strings in thze log file.
So if you try to switch to the new technology you will benefit.

Do not hesitate to ask me how to do things - I use this method from 2 years and it is a great improvements.
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

LVL 14

Author Comment

ID: 24316839
Can imp be done from Oracle Client machine?
We do import export from Oracle client
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 80 total points
ID: 24316967
Yes, an Oracle client machine can do an import *IF* the client version is the same version of Oracle as the database version, and if the database utlities were installed on the client.  A default Oracle Client install will *NOT* include these utilities.

Is it a good idea to run import from a client?  Usually not.  The performance penalty of running import from a client machine (over a network) usually makes the import take *MUCH* longer than if the same import could be run directly on the server.  If the number of records being imported ithis way s small, this may not be big problem though.
LVL 14

Author Comment

ID: 24337882
Config Managment folks maintain 10g and 11g versions, so its difficult to mainitain consistency and so we use imp rather than impdp
Well, I have figured out the solution.
The only way it can be done is to execute this command after imp and append the history of commands in import log file.
doskey /h >>logfilenameofdump
LVL 40

Assisted Solution

mrjoltcola earned 80 total points
ID: 24338279
>>Config Managment folks maintain 10g and 11g versions, so its difficult to mainitain consistency and so we use imp rather than impdp

The original Oracle imp tool will be maintained forever, or at least for the foreseeable future. I'm 99.99% sure you will never see it go away, so don't worry about "deprecation" unless you are moving to new 11g+ features. There are new 11g features that export will not export, but that does not mean you cannot use it.

I've been using exp/imp since 1993, I will not stop using it now until Oracle officially says it is going away.

Why will import never go away? If it did, Oracle 5.0 - 9, where there was no data pump, would no longer be importable into newer versions, and this is something that Oracle has always been good for.

I don't just make this up, see the Oracle Data Pump FAQ on OTN.
LVL 48

Expert Comment

ID: 24342819
Because you maintain 10g and 11g and if 10g is Release 2 I will strongly recommend to move to Data Pump and even to use the package DBMS_DATA_PUMP.
There is a procedure that adds line(s) to the log file.

I am working for years with DBMS_DATA_PUMP. It dosn't implement
all parameters (features) of Data Pump, but works fine.

Of course Data Pump is a new product and still has some bugs.
E.g. character set transformation doesnt work via Data Pump,
But Oracle is aware ...
LVL 14

Author Comment

ID: 24358038
Thanks Guys, Moving to data pump means educating CM folks which is not big issue but It will take lot of time.
However, they are used to imp/exp and I believe using doskey command is the temporary fix to see the dump file being imported.
I will grant ponts to all of you equally even if I am not implementing solution.

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Oracle Pivot Question 8 71
Oracle - SQL Where clause causing Invalid Number Error 4 46
Oracle sql query 7 75
Retreiving column names in Windows but not in Unix 11 60
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question