Showing posts with label Oracle 10g/11g Data Pump. Show all posts
Showing posts with label Oracle 10g/11g Data Pump. Show all posts

Wednesday 12 November 2014

DataPump Import (IMPDP) Fails With Errors ORA-39001 ORA-39000 ORA-31640 ORA-27037

APPLIES TO:

Oracle Database - Enterprise Edition - Version 10.1.0.2 to 11.2.0.4 [Release 10.1 to 11.2]
Information in this document applies to any platform.


ERROR SYMPTOMS:

DataPump import fails with the following errors:
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31640: unable to open dump file "/u01/bkups/exports/EXPORT.dmp" for read
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

The parameters used are:

userid=system/
DIRECTORY=my_dir
DUMPFILE=EXPORT.dmp
LOGFILE=my_logdir:EXPORT.log
CHANGES

DataPump export with parameters:
userid=username/
DIRECTORY=my_dir
DUMPFILE=EXPORT.DMP
LOGFILE=EXPORT.log
content=metadata_only
VERSION=10.2.0

was successful:
Master table "EXPORT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for EXPORT.SYS_EXPORT_SCHEMA_01 is:
/spare/clone/EXPORT.DMP
Job "EXPORT"."SYS_EXPORT_SCHEMA_01" successfully completed at 20:41:12

CAUSE:

At first glance, this appears to be an exact match to the note 784566.1 How to Prevent ORA-39000 ORA-31640 ORA-27037 Errors When
Performing Data Pump Export/Import

If you have already read that note, tried the solution and are still getting the errors, then the problem may be with the actual
export.dmp file.

In this case, as you can see from the export par file and import par file the name of the export dump is not the same:

Export: DUMPFILE=EXPORT.DMP
Import: DUMPFILE=EXPORT.dmp

SOLUTION:

Once the import parameter was changed to DUMPFILE=EXPORT.DMP the import completes successfully.


Reference : Doc ID 1228194.1

Sunday 7 September 2014

Export/Import specific tablespace using Data Pump in Oracle 10g Database


1.  Tablespace Export

expdp system/ TABLESPACES=USERS,UNDOTBS1 directory=DATA_PUMP_DIR dumpfile=test.dmp LOGFILE=exp.log    parallel=2  

In the above example, expdp takes a backup of the contents of USERS,UNDOTBS1. The expdp also runs quickly because of the parallel=2 option (provided there are more CPUs available in the database server). 

2. Transportable tablespace

expdp system/ transport_tablespaces=test_user_tbs transport_full_check=y directory= DATA_PUMP_DIR dumpfile=test.dmp logfile=exp.log

Transportable tablespaces export and import is manageable across platforms and only Meta data will be exported. In Cross platform transportable tablespace the data movement is simpler and faster.

This mode requires that you have the EXP_FULL_DATABASE role.

Please note that

1. source and target database must use the same character set/national character set
2. You cannot transport a tablespace to a target database which already exists.
3. Transportable tablespace exports cannot be restarted once stopped
4. Target database must at same or higher release level as the source database.

Transportable tablespace export and import on same endian platforms 
Step 1: Find the Operating system byte order on Source and Target Database
SQL > select * from v$transportable_platform order by platform_id;

1 Solaris[tm] OE (32-bit) Big
2 Solaris[tm] OE (64-bit) Big
3 HP-UX (64-bit) Big
4 HP-UX IA (64-bit) Big
5 HP Tru64 UNIX Little
6 AIX-Based Systems (64-bit) Big
7 Microsoft Windows IA (32-bit) Little
8 Microsoft Windows IA (64-bit) Little
9 IBM zSeries Based Linux Big
10 Linux IA (32-bit) Little
11 Linux IA (64-bit) Little
12 Microsoft Windows 64-bit for AMD Little
13 Linux 64-bit for AMD Little
15 HP Open VMS Little
16 Apple Mac OS Big
17 Solaris Operating System (x86) Little

18 IBM Power Based Linux Big


3. Tablespace Import

impdp system/ TABLESPACES=USERS directory=DATA_PUMP_DIR dumpfile=test.dmp LOGFILE=imp.log

Above example imports all tables that have data in tablespaces USERS and it assumes that the tablespaces already exist.