Errors from Using DATAPUMP

Datapump moves data on block level and is faster than Export/Import. It took about 4 hours to export 360GB dump data, and took 13 and half hours to import it on AIX servers with 4 physical CUPs.

Here are some errors I ran into, and the fixes:

- The OS folder can not be recognized by datapump:

ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31641: unable to create dump file "/images/DB_full.dmp"
ORA-27054: NFS file system where the file is created or resides is not mounted with correct options

Fix (Note 370513.1): Mount the disk by using the recommended mounting options which are: (hard,bg,proto=tcp,suid,rsize=32768,wsize=32768,noac)

- Datapump can not locate the directory:

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

Fix: Make sure to include "/" at the beginning (or the end) of the path, such as '/home/oracle/dumdir' when creating the DIRECTORY in the database.

- On a RAC environment, got errors when running datapump on one of the nodes:

ORA-31626: job does not exist
ORA-31650: timeout waiting for master process response

Wed Mar 18 19:58:06 2009
kwqbcsrmsg: Timeout contacting inst 2 for buffered oper 1,
IPC ret status=36
queue = SYS KUPC$S_1_20090318195629
kupprdp: master process DM00 started with pid=38, OS id=799160
to execute - SYS.KUPM$MCP.MAIN('SYS_EXPORT_FULL_01', 'SYSTEM', 'KUPC$C_1_20090318195629', 'KUPC$S_1_20090318195629', 0);
Wed Mar 18 19:59:00 2009

Fix (Note 454639.1): "alter system set aq_tm_processes=1"

SQL> select value from v$parameter
where name = 'aq_tm_processes';
VALUE
------
0
SQL> alter system set aq_tm_processes=1 scope=both;
SQL> select value from v$parameter
where name = 'aq_tm_processes';
VALUE
------
1

- Got below errors from datapump, and ORA-600 in alert log:

UDE-00008: operation generated ORACLE error 31623
ORA-31623: a job is not attached to this session via the specified handle
ORA-06512: at "SYS.DBMS_DATAPUMP", line 2745
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3712
ORA-06512: at line 1

ORA-00600: internal error code, arguments: [kwqbgqc: bad state], [1], [1], [], [], [], [], []

Fix (Note 754401.1): Re-create the SYS.KUPC$DATAPUMP_QUETAB queue.

SQL> startup restrict
SQL> exec dbms_aqadm.drop_queue_table(queue_table=>'SYS.KUPC$DATAPUMP_QUETAB',force=>TRUE);

PL/SQL procedure successfully completed.

SQL> BEGIN
2 dbms_aqadm.create_queue_table(queue_table => 'SYS.KUPC$DATAPUMP_QUETAB',
3 multiple_consumers => TRUE,
4 queue_payload_type =>'SYS.KUPC$_MESSAGE',
5 comment => 'DataPump Queue Table',
6 compatible=>'8.1.3');
7 EXCEPTION
8 WHEN OTHERS THEN
9 IF SQLCODE = -24001 THEN NULL;
10 ELSE RAISE;
11 END IF;
12 END;
13 /

PL/SQL procedure successfully completed.