Skip to content

Datapump-Issues

March 24, 2012

1) With Expdp Version 10g when you execute  exp / schemas=…… fails as below, but works fine with 11g

(A) expdp / schemas=MALUDEV03 directory=REFRESH_DUMP dumpfile=expdp_MALUDEV03.dmpdp logfile=expdp_MALUDEV03.log

Export: Release 10.2.0.4.0 – 64bit Production on Tuesday, 06 March, 2012 1:45:06

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, Data Mining and Real Application Testing options
ORA-31626: job does not exist
ORA-31633: unable to create master table “ORACLE.SYS_EXPORT_SCHEMA_05”
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 95
ORA-06512: at “SYS.KUPV$FT”, line 871
ORA-01950: no privileges on tablespace ‘USERS’

(B) $ exp / schemas=SH,HR directory=exp_dump_dir dumpfile=expdp_schemas.dmp logfile=expdp_schemas.log
LRM-00101: unknown parameter name ‘schemas’

EXP-00019: failed to process parameters, type ‘EXP HELP=Y’ for help
EXP-00000: Export terminated unsuccessfully

Corrected commandline for error (A) and (B)  is as Below:

expdp \’/ as sysdba\’ schemas=SH,HR directory=exp_dump_dir dumpfile=expdp_schemas.dmp logfile=expdp_schemas.log

Actually, using expdp / was not strong enough as you had not all privileges as sysdba.
For more details, I would suggest to take a look at Note 50507.1 – SYSDBA and SYSOPER Privileges in Oracle.

2) Possible Corrupted Dump file .

Import: Release 10.2.0.2.0 – Production on Saturday, 24 March, 2012 21:53:33

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 – Production
With the Partitioning, OLAP and Data Mining options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31619: invalid dump file “/oradata1/exp_dump/expdp_schemas.dmp”

$ ls -lh /oradata1/exp_dump/expdp_schemas.dmp
-rw-r—–   1 oracle   dba          45M Mar 24 21:21 /oradata1/exp_dump/expdp_schemas.dmp

$ oerr ora 31619
31619, 00000, “invalid dump file \”%s\””
// *Cause:  Either the file was not generated by Export or it was corrupted.
// *Action: If the file was indeed generated by Export, report this as an
//          Import bug and submit the export file to Oracle Customer Support.

In My Case dump file was  corrupted , As I tried to get header information using below procedure its says invalid file. After performing export again , Import started successfully.

 3) How to Gather the Header Information and the Content of an Export Dumpfile ? [ID 462488.1]
#############################################################################

Show internal header information of export (DataPump) dumpfile.
——————————————————————————————
Every export dumpfile starts with a header (usually 4 kb in size) which contains details about the dumpfile.
Starting with Oracle10g Release 2 (10.2.0.1.0), this header information can be extracted from the dumpfile by calling the procedure DBMS_DATAPUMP.GET_DUMPFILE_INFO.The following example creates a user defined procedure SHOW_DUMPFILE_INFO in the schema SYSTEM of an Oracle10g Release 2 or higher database, and which can be called to obtain the export dumpfile header details.

Possible output based on an example procedure SHOW_DUMPFILE_INFO:

REM **************************Start Of Procedure *************************************

CREATE PROCEDURE show_dumpfile_info(
p_dir  VARCHAR2 DEFAULT ‘DATA_PUMP_DIR’,
p_file VARCHAR2 DEFAULT ‘EXPDAT.DMP’)
AS
— p_dir         = directory object where dumpfile can be found
— p_file        = simple filename of export dumpfile (case-sensitive)
v_separator   VARCHAR2(80) := ‘————————————–‘ ||
‘————————————–‘;
v_path        all_directories.directory_path%type := ‘?’;
v_filetype    NUMBER;                 — 0=unknown 1=expdp 2=exp
v_fileversion VARCHAR2(15);           — 0.1=10gR1 1.1=10gR2 2.1=11g
v_info_table  sys.ku$_dumpfile_info;  — PL/SQL table with file info
type valtype  IS VARRAY(22) OF VARCHAR2(2048);
var_values    valtype := valtype();
no_file_found EXCEPTION;
PRAGMA        exception_init(no_file_found, -39211);

BEGIN

— Show generic info:
— ==================

dbms_output.put_line(v_separator);
dbms_output.put_line(‘Purpose..: Obtain details about export ‘ ||
‘dumpfile.        Version: 19-MAR-2008’);
dbms_output.put_line(‘Required.: RDBMS version: 10.2.0.1.0 or higher’);
dbms_output.put_line(‘.          ‘ ||
‘Export dumpfile version: 7.3.4.0.0 or higher’);
dbms_output.put_line(‘.          ‘ ||
‘Export Data Pump dumpfile version: 10.1.0.1.0 or higher’);
dbms_output.put_line(‘Usage….: ‘ ||
‘execute show_dumfile_info(”DIRECTORY”, ”DUMPFILE”);’);
dbms_output.put_line(‘Example..: ‘ ||
‘exec show_dumfile_info(”MY_DIR”, ”expdp_s.dmp”)’);
dbms_output.put_line(v_separator);
dbms_output.put_line(‘Filename.: ‘ || p_file);
dbms_output.put_line(‘Directory: ‘ || p_dir);

— Retrieve Export dumpfile details:
— =================================

SELECT directory_path INTO v_path FROM all_directories
WHERE directory_name = p_dir
OR directory_name = UPPER(p_dir);

dbms_datapump.get_dumpfile_info(
filename   => p_file,       directory => UPPER(p_dir),
info_table => v_info_table, filetype  => v_filetype);

var_values.EXTEND(22);
FOR i in 1 .. 22 LOOP
BEGIN
SELECT value INTO var_values(i) FROM TABLE(v_info_table)
WHERE item_code = i;
EXCEPTION WHEN OTHERS THEN var_values(i) := ”;
END;
END LOOP;

— Show dumpfile details:
— ======================
— For Oracle10g Release 2 and higher:
—    dbms_datapump.KU$_DFHDR_FILE_VERSION        CONSTANT NUMBER := 1;
—    dbms_datapump.KU$_DFHDR_MASTER_PRESENT      CONSTANT NUMBER := 2;
—    dbms_datapump.KU$_DFHDR_GUID                CONSTANT NUMBER := 3;
—    dbms_datapump.KU$_DFHDR_FILE_NUMBER         CONSTANT NUMBER := 4;
—    dbms_datapump.KU$_DFHDR_CHARSET_ID          CONSTANT NUMBER := 5;
—    dbms_datapump.KU$_DFHDR_CREATION_DATE       CONSTANT NUMBER := 6;
—    dbms_datapump.KU$_DFHDR_FLAGS               CONSTANT NUMBER := 7;
—    dbms_datapump.KU$_DFHDR_JOB_NAME            CONSTANT NUMBER := 8;
—    dbms_datapump.KU$_DFHDR_PLATFORM            CONSTANT NUMBER := 9;
—    dbms_datapump.KU$_DFHDR_INSTANCE            CONSTANT NUMBER := 10;
—    dbms_datapump.KU$_DFHDR_LANGUAGE            CONSTANT NUMBER := 11;
—    dbms_datapump.KU$_DFHDR_BLOCKSIZE           CONSTANT NUMBER := 12;
—    dbms_datapump.KU$_DFHDR_DIRPATH             CONSTANT NUMBER := 13;
—    dbms_datapump.KU$_DFHDR_METADATA_COMPRESSED CONSTANT NUMBER := 14;
—    dbms_datapump.KU$_DFHDR_DB_VERSION          CONSTANT NUMBER := 15;
— For Oracle11gR1:
—    dbms_datapump.KU$_DFHDR_MASTER_PIECE_COUNT  CONSTANT NUMBER := 16;
—    dbms_datapump.KU$_DFHDR_MASTER_PIECE_NUMBER CONSTANT NUMBER := 17;
—    dbms_datapump.KU$_DFHDR_DATA_COMPRESSED     CONSTANT NUMBER := 18;
—    dbms_datapump.KU$_DFHDR_METADATA_ENCRYPTED  CONSTANT NUMBER := 19;
—    dbms_datapump.KU$_DFHDR_DATA_ENCRYPTED      CONSTANT NUMBER := 20;
— For Oracle11gR2:
—    dbms_datapump.KU$_DFHDR_COLUMNS_ENCRYPTED   CONSTANT NUMBER := 21;
—    dbms_datapump.KU$_DFHDR_ENCPWD_MODE         CONSTANT NUMBER := 22;

— For Oracle10gR2: KU$_DFHDR_MAX_ITEM_CODE       CONSTANT NUMBER := 15;
— For Oracle11gR1: KU$_DFHDR_MAX_ITEM_CODE       CONSTANT NUMBER := 20;
— For Oracle11gR2: KU$_DFHDR_MAX_ITEM_CODE       CONSTANT NUMBER := 22;

dbms_output.put_line(‘Disk Path: ‘ || v_path);

IF v_filetype = 1 OR v_filetype = 2 THEN
— Get characterset name:
BEGIN
SELECT var_values(5) || ‘ (‘ || nls_charset_name(var_values(5)) ||
‘)’ INTO var_values(5) FROM dual;
EXCEPTION WHEN OTHERS THEN null;
END;
IF v_filetype = 2 THEN
dbms_output.put_line(
‘Filetype.: ‘ || v_filetype || ‘ (Original Export dumpfile)’);
dbms_output.put_line(v_separator);
SELECT DECODE(var_values(13), ‘0’, ‘0 (Conventional Path)’,
‘1’, ‘1 (Direct Path)’, var_values(13))
INTO var_values(13) FROM dual;
dbms_output.put_line(‘…Characterset ID.: ‘ || var_values(5));
dbms_output.put_line(‘…Direct Path…..: ‘ || var_values(13));
dbms_output.put_line(‘…Export Version..: ‘ || var_values(15));
ELSIF v_filetype = 1 THEN
dbms_output.put_line(
‘Filetype.: ‘ || v_filetype || ‘ (Export Data Pump dumpfile)’);
dbms_output.put_line(v_separator);
SELECT SUBSTR(var_values(1), 1, 15) INTO v_fileversion FROM dual;
SELECT DECODE(var_values(1),
‘0.1’, ‘0.1 (Oracle10g Release 1: 10.1.0.x)’,
‘1.1’, ‘1.1 (Oracle10g Release 2: 10.2.0.x)’,
‘2.1’, ‘2.1 (Oracle11g Release 1: 11.1.0.x)’,
‘3.1’, ‘3.1 (Oracle11g Release 2: 11.2.0.x)’,
var_values(1)) INTO var_values(1) FROM dual;
SELECT DECODE(var_values(2), ‘0’, ‘0 (No)’, ‘1’, ‘1 (Yes)’,
var_values(2)) INTO var_values(2) FROM dual;
SELECT DECODE(var_values(14), ‘0’, ‘0 (No)’, ‘1’, ‘1 (Yes)’,
var_values(14)) INTO var_values(14) FROM dual;
SELECT DECODE(var_values(18), ‘0’, ‘0 (No)’, ‘1’, ‘1 (Yes)’,
var_values(18)) INTO var_values(18) FROM dual;
SELECT DECODE(var_values(19), ‘0’, ‘0 (No)’, ‘1’, ‘1 (Yes)’,
var_values(19)) INTO var_values(19) FROM dual;
SELECT DECODE(var_values(20), ‘0’, ‘0 (No)’, ‘1’, ‘1 (Yes)’,
var_values(20)) INTO var_values(20) FROM dual;
SELECT DECODE(var_values(21), ‘0’, ‘0 (No)’, ‘1’, ‘1 (Yes)’,
var_values(21)) INTO var_values(21) FROM dual;
SELECT DECODE(var_values(22),
‘1’, ‘1 (Unknown)’,
‘2’, ‘2 (None)’,
‘3’, ‘3 (Password)’,
‘4’, ‘4 (Dual)’,
‘5’, ‘5 (Transparent)’,
var_values(22)) INTO var_values(22) FROM dual;

dbms_output.put_line(‘…File Version….: ‘ || var_values(1));
dbms_output.put_line(‘…Master Present..: ‘ || var_values(2));
dbms_output.put_line(‘…GUID…………: ‘ || var_values(3));
dbms_output.put_line(‘…File Number…..: ‘ || var_values(4));
dbms_output.put_line(‘…Characterset ID.: ‘ || var_values(5));
dbms_output.put_line(‘…Creation Date…: ‘ || var_values(6));
dbms_output.put_line(‘…Flags………..: ‘ || var_values(7));
dbms_output.put_line(‘…Job Name……..: ‘ || var_values(8));
dbms_output.put_line(‘…Platform……..: ‘ || var_values(9));
IF v_fileversion >= ‘2.1’ THEN
dbms_output.put_line(‘…Instance……..: ‘ || var_values(10));
END IF;
dbms_output.put_line(‘…Language……..: ‘ || var_values(11));
dbms_output.put_line(‘…Block size……: ‘ || var_values(12));
dbms_output.put_line(‘…Metadata Compres: ‘ || var_values(14));
IF dbms_datapump.KU$_DFHDR_MAX_ITEM_CODE > 15 THEN
dbms_output.put_line(‘…Data Compressed.: ‘ || var_values(18));
dbms_output.put_line(‘…Metadata Encrypt: ‘ || var_values(19));
dbms_output.put_line(‘…Data Encrypted..: ‘ || var_values(20));
dbms_output.put_line(‘…Column Encrypted: ‘ || var_values(21));
dbms_output.put_line(‘…Encrypt.pwd. mod: ‘ || var_values(22));
IF v_fileversion = ‘2.1’ or v_fileversion = ‘3.1’ THEN
dbms_output.put_line(‘…Master Piece Cnt: ‘ || var_values(16));
dbms_output.put_line(‘…Master Piece Num: ‘ || var_values(17));
END IF;
END IF;
IF v_fileversion >= ‘1.1’ THEN
dbms_output.put_line(‘…Job Version…..: ‘ || var_values(15));
END IF;
dbms_output.put_line(‘…Max Items Code..: ‘ ||
dbms_datapump.KU$_DFHDR_MAX_ITEM_CODE);
END IF;
ELSE
dbms_output.put_line(‘Filetype.: ‘ || v_filetype);
dbms_output.put_line(v_separator);
dbms_output.put_line(‘ERROR….: Not an export dumpfile.’);
END IF;
dbms_output.put_line(v_separator);

EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line(‘Disk Path: ?’);
dbms_output.put_line(‘Filetype.: ?’);
dbms_output.put_line(v_separator);
dbms_output.put_line(‘ERROR….: Directory Object does not exist.’);
dbms_output.put_line(v_separator);
WHEN no_file_found THEN
dbms_output.put_line(‘Disk Path: ‘ || v_path);
dbms_output.put_line(‘Filetype.: ?’);
dbms_output.put_line(v_separator);
dbms_output.put_line(‘ERROR….: File does not exist.’);
dbms_output.put_line(v_separator);
END;
/
REM **************************End Of Procedure *************************************

Execute Below statement for Header Information

SET serveroutput on SIZE 1000000
exec show_dumpfile_info(p_dir=> ‘EXP_DUMP_DIR’, p_file=> ‘expdp_schemas.dmp’)

———————————————————————————————————–

Purpose..: Obtain details about export dumpfile.        Version: 19-MAR-2008
Required.: RDBMS version: 10.2.0.1.0 or higher
.          Export dumpfile version: 7.3.4.0.0 or higher
.          Export Data Pump dumpfile version: 10.1.0.1.0 or higher
Usage….: execute show_dumfile_info(‘DIRECTORY’, ‘DUMPFILE’);
Example..: exec show_dumfile_info(‘MY_DIR’, ‘expdp_s.dmp’)
—————————————————————————-
Filename.: expdp_schemas.dmp
Directory: EXP_DUMP_DIR
Disk Path: /oradata1/exp_dump/
Filetype.: 1 (Export Data Pump dumpfile)
—————————————————————————-
…File Version….: 1.1 (Oracle10g Release 2: 10.2.0.x)
…Master Present..: 1 (Yes)
…GUID…………: BC01433F206F08AFE044080027A81741
…File Number…..: 1
…Characterset ID.: 31 (WE8ISO8859P1)
…Creation Date…: Sat Mar 24 22:44:39 2012
…Flags………..: 2
…Job Name……..: “SYS”.”SYS_EXPORT_SCHEMA_01″
…Platform……..: Sun386i/SunOS-4.0-8.1.0
…Language……..: WE8ISO8859P1
…Block size……: 4096
…Metadata Compres: 1 (Yes)
…Job Version…..: 10.02.00.02.00
…Max Items Code..: 15
—————————————————————————-

PL/SQL procedure successfully completed.

Advertisements

From → Oracle

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: