ORA-39000, ORA-39143 dump file may be an original export dump file


Problem Description
E:\>impdp directory=test dumpfile=testexp_07_03_09.dmp userid=arju/a

Import: Release 10.2.0.1.0 – Production on Thursday, 07 May, 2009 10:07:00

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, OLAP and Data Mining options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39143: dump file “E:\oracle\Test\testexp_07_03_09.dmp” may be an original export dump file

Cause of the problem
The above problem happened whenever you try to use the Import Data Pump client (impdp) to import a dumpfile that was created with the original Export client (exp).

Though not related but similar error occured.
Like whenever you try to import from an empty file,
E:\>impdp directory=test dumpfile=testexp_07_03_09.dmp userid=arju/a

Import: Release 10.2.0.1.0 – Production on Thursday, 07 May, 2009 10:16:52
Continue reading

Advertisements

Oracle Data Pump Export 10g


What is Data Pump Export

Data Pump export is a new feature in Oracle 10g and provides enhanced functionality for the Export Utility (exp) available in previous version. The new Data Pump export provides improved features and better performance. Data pump utility is useful when you need to clone a database, refresh the development database or move the databases between different operating systems. Another big advantage is the network export feature, using which you can export the data to the target server directly using data filters and metadata filters; without having to create the dump files.

Data Pump Export basics

The Data Pump export utility can be invoked via two methods:

  • By using the expdp command from the command prompt
  • By using “Export to Export Files” under data movement section of the OEM.

The characteristic of the export operation is determined by the export parameters that you specify. These parameters can be specified either on the command line or in a parameter file.

Data Pump Export modes

There are 4 export modes available in the data pump.

  1. Database – Export the entire database
  2. Schemas – You can selectively export the required schema rather everything
  3. Tables – If you are looking to restore few sets of tables
  4. Tablespace – this option is same like Tables export but exports all of the tables from any tablespace. Continue reading

Overwrite The Destination Dump File If It Exists During Datapump Export


How to overwrite the destination dump file if it exists during datapump export?

We can overwrite the existing destination dump file using new 11g datapump export parameter REUSE_DUMPFILES (default (N) )
Test case:

–> The dumpfile “scott.dmp” is already exists in the destination path.
$ expdp scott/tiger directory=dir dumpfile=scott.dmp logfile=scott.log tables=emp

Export: Release 11.1.0.7.0 – 64bit Production on Monday, 30 March, 2009 4:23:40

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31641: unable to create dump file “/tmp/scott.dmp”
ORA-27038: created file already exists
Additional information: 1

Continue reading

Problem with data pump : ORA-39002: invalid operation


I want to do an export with the data pump :

expdp ogtest/ogtest@devseam DIRECTORY=dpump_ogtest DUMPFILE=expfull_ogtest.dmp FULL=y NOLOGFILE=y

Export: Release 10.2.0.3.0 – Production on Monday, 03 March, 2008 13:25:39 Copyright (c) 2003, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – Production With the Partitioning, OLAP and Data Mining options ORA-39002: invalid operation

ORA-39070: Unable to open the log file.

ORA-29283: invalid file operation

ORA-06512: at “SYS.UTL_FILE”, line 475

ORA-29283: invalid file operation
Sometimes I get the error: error 13: persmission denied…

the solution is :

Continue reading

Oracle export and import


Oracle export and import This is a featured page
Prerequisites/Requirements :

exp and imp are utilities present in the $ORACLE_HOME/bin directory and are installed when Oracle is installed. Their prime purpose is to move logical objects out of and into the database respectively – for example dumping all of the tables owned by a user to a single file is achieved using the exp utility. It is important to distinguish between dumping data in this manner and backing up the database which is normally achieved using the rman utility.
Starting with Oracle 10g these two utilities are deprecated and the Oracle recommended alternatives are the data pump versions of these utilities which provide a number of new features including the ability to disconnect and reconnect to an interactive session (so a direct logon to the server isn’t required) and improved performance. There are still a number of things that cannot be achieved with the data pump utilities – in particular exporting/importing across a named pipe which is a technique used by database administrators when the export file needs to be compressed on the fly or when one wishes to export directly to an import session.
Continue reading