Oracle DBA – A lifelong learning experience

Posts Tagged ‘dba_recyclebin’

Resolving two errors on Datapump export – parameter cluster=N/Y

Posted by John Hallas on December 5, 2016

I attempt to export a table with 76,000 rows using a relatively standard datapump command

expdp schemas=ODI_TEMP cluster=y directory=MCB content=ALL dumpfile=OEC_%U.dmp include=table:"IN('ORDM_ERROR_COUNTS')" parallel=8 logfile=OEC.log

It fails with a fatal error and also produces a warning

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management and OLAP options
Starting “ODI_TEMP”.”SYS_EXPORT_SCHEMA_03″:  odi_temp/******** schemas=ODI_TEMP cluster=y directory=MCB content=ALL dumpfile=OEC_%U.dmp include=table:IN(‘ORDM_ERROR_COUNTS’) parallel=8 logfile=OEC.log
Estimate in progress using BLOCKS method…
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 41 MB
>>> DBMS_AW_EXP: Ignoring BIA_RTL.MRDW_SLS_MRGN_AW
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-31693: Table data object “ODI_TEMP”.”ORDM_ERROR_COUNTS” failed to load/unload and is being skipped due to error:
ORA-31617: unable to open dump file “/zfs/exports2/mcb/dp/OEC_01.dmp” for write
ORA-19505: failed to identify file “/zfs/exports2/mcb/dp/OEC_01.dmp”
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
>>> DBMS_AW_EXP: BIN$IrRPsbepeYXgU60IBArnYQ==$0 not AW$
>>> DBMS_AW_EXP: BIN$IrRPsbereYXgU60IBArnYQ==$0 not AW$
>>> DBMS_AW_EXP: BIN$IrRPsbeteYXgU60IBArnYQ==$0 not AW$
>>> DBMS_AW_EXP: BIN$IrRPsbeveYXgU60IBArnYQ==$0 not AW$
>>> DBMS_AW_EXP: BIN$IrRPsbexeYXgU60IBArnYQ==$0 not AW$
>>> DBMS_AW_EXP: BIN$IrRPsbezeYXgU60IBArnYQ==$0 not AW$
ORA-31694: master table “ODI_TEMP”.”SYS_EXPORT_SCHEMA_03″ failed to load/unload
ORA-31617: unable to open dump file “/zfs/exports2/mcb/dp/OEC_01.dmp” for write
ORA-19505: failed to identify file “/zfs/exports2/mcb/dp/OEC_01.dmp”
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-19505: failed to identify file “/zfs/exports2/mcb/dp/OEC_01.dmp”
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

 

The fatal error is obviously a problem that needs resolving and I attempt that with all sorts of things, changing the directory permissions, ensuring I can write a file into the directory, trying a variety of directories, all ending with the same failure message. What is even more odd is that 2 of the 8 dump files I want are being created.

-rw-r-----+ 1 oracle dba            4096 Dec  3 14:58 OEC_01.dmp
-rw-r-----+ 1 oracle dba           24576 Dec  3 14:59 OEC_02.dmp
-rw-r--r--+ 1 oracle dba            2200 Dec  3 14:59 OEC.log

I must mention at this point that I am using an 8 node RAC cluster. You may have noticed the cluster=y parameter in my expdp command. I don’t normally ever enter that as it is the default anyway, I just used it to demonstrate the issue.

The answer is of course that the directory I am trying to write into does not exist across all 8 nodes. In fact I have jumped onto node 1 as I normally do and the directory does not actually exist on any of the other 7 nodes. So the failure message is entirely accurate but quite hard to actually track down. I re-run the command with cluster=N, indicating it will still attempt to write 8 parallel files but all on the current node.
Master table “ODI_TEMP”.”SYS_EXPORT_SCHEMA_04″ successfully loaded/unloaded
******************************************************************************
Dump file set for ODI_TEMP.SYS_EXPORT_SCHEMA_04 is:
/zfs/exports2/mcb/dp/OEC_01.dmp
/zfs/exports2/mcb/dp/OEC_02.dmp
Job “ODI_TEMP”.”SYS_EXPORT_SCHEMA_04″ successfully completed at Sat Dec 3 15:12:46 2016

Excellent – the job has worked. But it still only created 2 dump files, how can that be. I mentioned the table only had 76K rows in it so Oracle knows it is overkill to run 8 streams and tones it all down.

The warning error >>> DBMS_AW_EXP: BIN$IrRPsbepeYXgU60IBArnYQ==$0 not AW$ is normally caused by objects in the dba recyclebin

Starting “SYS”.”SYS_EXPORT_SCHEMA_01″:  /******** AS SYSDBA schemas=ODI_TEMP_TST1 cluster=N directory= content=ALL dumpfile=JH%U.dmp include=table:IN(‘E$_DWB_POINTS_TRANSACTION’) parallel=4 logfile=jh.log
Estimate in progress using BLOCKS method…
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 800 MB
>>> DBMS_AW_EXP: BIN$IrRPsbepeYXgU60IBArnYQ==$0 not AW$
>>> DBMS_AW_EXP: BIN$IrRPsbereYXgU60IBArnYQ==$0 not AW$
>>> DBMS_AW_EXP: BIN$IrRPsbeteYXgU60IBArnYQ==$0 not AW$
>>> DBMS_AW_EXP: BIN$IrRPsbeveYXgU60IBArnYQ==$0 not AW$
>>> DBMS_AW_EXP: BIN$IrRPsbexeYXgU60IBArnYQ==$0 not AW$
>>> DBMS_AW_EXP: BIN$IrRPsbezeYXgU60IBArnYQ==$0 not AW$
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
>>> DBMS_AW_EXP: Ignoring BIA_RTL_MCU1.MRDW_SLS_MRGN_AW
>>> DBMS_AW_EXP: BIN$IrRPsbepeYXgU60IBArnYQ==$0 not AW$
>>> DBMS_AW_EXP: BIN$IrRPsbereYXgU60IBArnYQ==$0 not AW$
>>> DBMS_AW_EXP: BIN$IrRPsbeteYXgU60IBArnYQ==$0 not AW$
>>> DBMS_AW_EXP: BIN$IrRPsbeveYXgU60IBArnYQ==$0 not AW$
>>> DBMS_AW_EXP: BIN$IrRPsbexeYXgU60IBArnYQ==$0 not AW$
>>> DBMS_AW_EXP: BIN$IrRPsbezeYXgU60IBArnYQ==$0 not AW$
. . exported “ODI_TEMP_TST1″.”E$_DWB_POINTS_TRANSACTION”  696.8 MB 3213090 rows
Master table “SYS”.”SYS_EXPORT_SCHEMA_01″ successfully loaded/unloaded

select count(*) from dba_recyclebin

--640 objects

purge dba_recyclebin;

re-run the export.  (Note the datapump command is different from the one at the beginning of the port. I just run this on a test environment just to prove the point and  to negate the need to raise a change)

 

Starting “SYS”.”SYS_EXPORT_SCHEMA_01″:  /******** AS SYSDBA schemas=ODI_TEMP_TST1 cluster=N directory= content=ALL dumpfile=JH%U.dmp include=table:IN(‘E$_DWB_POINTS_TRANSACTION’) parallel=4 logfile=jh.log
Estimate in progress using BLOCKS method…
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 800 MB
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported “ODI_TEMP_TST1″.”E$_DWB_POINTS_TRANSACTION”  696.8 MB 3213090 rows
Master table “SYS”.”SYS_EXPORT_SCHEMA_01″ successfully loaded/unloaded

Job “SYS”.”SYS_EXPORT_SCHEMA_01″ successfully completed at Mon Dec 5 08:37:04 2016

 

All completed successfully. Having spent so much time trying to fix the parallel issue and having written this blog entry up, as an aide memoire to myself as much as anything else, I am unlikely to forget how to address ORA-31617 next time I see it.

 

Advertisements

Posted in Oracle | Tagged: , , , , , , | 1 Comment »