EXPDP in Oracle 12c (12.1.0.2) fails with below error.During performing Data Pump Export backup in a 2 node Oracle 12c RAC database, Data Pump job terminates with below error.


=============================================================

Export:Release12.1.0.2.0-ProductiononMonJan2310:16:152017Copyright(c)1982,2014,Oracleand/oritsaffiliates.Allrightsreserved.Connectedto:OracleDatabase12cEnterpriseEditionRelease12.1.0.2.0-64bitProductionWiththePartitioning,RealApplicationClusters,AutomaticStorageManagement,OracleLabelSecurity,OLAP,AdvancedAnalyticsandRealApplicationTestingoptionsORA-31626:jobdoesnotexistORA-31633:unabletocreatemastertable"SYSTEM.SYS_EXPORT_FULL_05"ORA-06512:at"SYS.DBMS_SYS_ERROR",line95ORA-06512:at"SYS.KUPV$FT",line1048ORA-06502:PL/SQL:numericorvalueerror:characterstringbuffertoosmall

=============================================================

Note: - Master table for Data Pump job was not being created hence it was terminating the Data pump job at its initial startup. In above error we can see ORA-06502 that comes most of the time if we have not set streams_pool_size parameter value to enough one but in my case it was sized enough set as below to perform Data Pump Operations.

NAMETYPEVALUE--------------------------------------------------------------------------------------------------streams_pool_sizebiginteger128M

Further we decided to clear any data pump orphaned job left in the database from earlier executions.

--locateDataPumpmastertables:SQL>SELECTo.status,o.object_id,o.object_type,o.owner||'.'||object_name"OWNER.OBJECT"FROMdba_objectso,dba_datapump_jobsjWHEREo.owner=j.owner_nameANDo.object_name=j.job_nameANDj.job_nameNOTLIKE'BIN$%'ORDERBY4,2;STATUSOBJECT_IDOBJECT_TYPEOWNER.OBJECT--------------------------------------------------------------------------------VALID1434366TABLESYS.SYS_EXPORT_FULL_01VALID1434139TABLESYS.SYS_EXPORT_SCHEMA_01VALID1434144TABLESYS.SYS_EXPORT_SCHEMA_02VALID1434149TABLESYS.SYS_EXPORT_SCHEMA_03VALID1434160TABLESYS.SYS_EXPORT_SCHEMA_04VALID1434377TABLESYS.SYS_EXPORT_SCHEMA_05VALID1432335TABLESYSTEM.SYS_EXPORT_FULL_01VALID1434155TABLESYSTEM.SYS_EXPORT_FULL_02VALID1434339TABLESYSTEM.SYS_EXPORT_FULL_03VALID1434344TABLESYSTEM.SYS_EXPORT_FULL_04VALID1434349TABLESYSTEM.SYS_EXPORT_FULL_05VALID1434354TABLESYSTEM.SYS_EXPORT_FULL_06VALID1434360TABLESYSTEM.SYS_EXPORT_FULL_07VALID1434372TABLESYSTEM.SYS_EXPORT_FULL_08VALID1434392TABLESYSTEM.SYS_EXPORT_FULL_09VALID1434408TABLESYSTEM.SYS_EXPORT_FULL_10VALID1434983TABLESYSTEM.SYS_EXPORT_FULL_11--BelowweclearedallOrphanedDataPumpJobs.SQL>droptableSYS.SYS_EXPORT_FULL_01;Tabledropped.SQL>droptableSYS.SYS_EXPORT_SCHEMA_01;Tabledropped.SQL>droptableSYS.SYS_EXPORT_SCHEMA_02;Tabledropped.SQL>droptableSYS.SYS_EXPORT_SCHEMA_03;Tabledropped.SQL>droptableSYS.SYS_EXPORT_SCHEMA_04;Tabledropped.SQL>droptableSYS.SYS_EXPORT_SCHEMA_05;Tabledropped.SQL>droptableSYSTEM.SYS_EXPORT_FULL_01;Tabledropped.SQL>droptableSYSTEM.SYS_EXPORT_FULL_02;Tabledropped.SQL>droptableSYSTEM.SYS_EXPORT_FULL_03;Tabledropped.SQL>droptableSYSTEM.SYS_EXPORT_FULL_04;Tabledropped.SQL>droptableSYSTEM.SYS_EXPORT_FULL_05;Tabledropped.SQL>droptableSYSTEM.SYS_EXPORT_FULL_06;Tabledropped.SQL>droptableSYSTEM.SYS_EXPORT_FULL_07;Tabledropped.SQL>droptableSYSTEM.SYS_EXPORT_FULL_08;Tabledropped.SQL>droptableSYSTEM.SYS_EXPORT_FULL_09;Tabledropped.SQL>droptableSYSTEM.SYS_EXPORT_FULL_10;Tabledropped.SQL>droptableSYSTEM.SYS_EXPORT_FULL_11;Tabledropped.

-- Now no orphaned data pump job left in the system.

SQL>SELECT*FROMuser_datapump_jobs;norowsselected

SQL>SELECTowner_name,job_name,rtrim(operation)"OPERATION",rtrim(job_mode)"JOB_MODE",state,attached_sessionsFROMdba_datapump_jobsWHEREjob_nameNOTLIKE'BIN$%'ORDERBY1,2;2345norowsselected

-- As, we are clean at this step so tried to run expdp job again.

Export:Release12.1.0.2.0-ProductiononWedJan1819:33:402017Copyright(c)1982,2014,Oracleand/oritsaffiliates.Allrightsreserved.Connectedto:OracleDatabase12cEnterpriseEditionRelease12.1.0.2.0-64bitProductionWiththePartitioning,RealApplicationClusters,AutomaticStorageManagement,OracleLabelSecurity,OLAP,AdvancedAnalyticsandRealApplicationTestingoptionsORA-31626:jobdoesnotexistORA-31633:unabletocreatemastertable"SYSTEM.SYS_EXPORT_FULL_05"ORA-06512:at"SYS.DBMS_SYS_ERROR",line95ORA-06512:at"SYS.KUPV$FT",line1048ORA-06502:PL/SQL:numericorvalueerror:characterstringbuffertoosmall

Terrible, it failed again with same error.We then decided to re-load Data Pump packages even our Catalog status was in VALID state in database registry.

--Decidedtore-loaddatapumppackages1.Catproc.sql

SQL>@$ORACLE_HOME/rdbms/admin/catproc.sql

2.Torecompileinvalidobjects,ifanySQL>@$ORACLE_HOME/rdbms/admin/utlrp.sql

Note:- you need to follow proper steps to run catproc.sql script in Oracle RAC database.
=============================================================Tried to fire Data Pump Export job again but failed with same error……LèTried to trace the data pump session to diagnose it in depth. You should try this step earlier than reloading data pump packages. We set below event tracing and fired Data Pump job again and it generated trace file for that point in time. Make sure you turn off the event after you are done with tracing.Set event 6502 to trap ORA-6502 and dump a stack trace

SQL>altersystemsetevents'6502tracenameerrorstacklevel3';SQL>altersystemsetevents'sql_trace{process:pname=dw,pname=dm}level=12';

After reviewing the trace file generated, we could see, there was a TRIGGER(MONITORING_DDL) which wascausing our export job to fail at every attempt.

SQL>selectowner,object_name,object_type,statusfromdba_objectswherelower(object_name)like'%monitoring_ddl';OWNEROBJECT_NAMEOBJECT_TYPESTATUS---------------------------------------------------------------------------SYSTEMMONITORING_DDLTRIGGERVALIDSYSTEMMONITORING_DDLTABLEVALID3rowsselected.

-- We Disabled the Trigger..............

We decided to disable to trigger as it was preventing DDLs operations to be performed other than SYS and SYSTEM users.

SQL>altertriggersystem.MONITORING_DDLdisable;Triggeraltered.

Finally, tried to run Data Pump Export Job again and it went fine.

Export:Release12.1.0.2.0-ProductiononMonJan2312:37:532017Copyright(c)1982,2014,Oracleand/oritsaffiliates.Allrightsreserved.Connectedto:OracleDatabase12cEnterpriseEditionRelease12.1.0.2.0-64bitProductionWiththePartitioning,RealApplicationClusters,AutomaticStorageManagement,OracleLabelSecurity,OLAP,AdvancedAnalyticsandRealApplicationTestingoptionsFLASHBACKautomaticallyenabledtopreservedatabaseintegrity.Starting"SYSTEM"."SYS_EXPORT_FULL_01":system/********directory=EXPORT_DIRdumpfile=expdp_rac1_2017-01-23.dmplogfile=expdp_rac1_2017-01-23.logfull=ymetrics=yStartuptook8secondsEstimateinprogressusingBLOCKSmethod...ProcessingobjecttypeDATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATAEstimated18894TABLE_DATAobjectsin357secondsTotalestimationusingBLOCKSmethod:120.9GBProcessingobjecttypeDATABASE_EXPORT/TABLESPACECompleted175TABLESPACEobjectsin18seconds

FROMhttp://rajkumar-dba.blogspot.com/2017/01/ora-31633-unable-to-create-master-table.html