Looking for something? Try here..

Tuesday, March 27, 2012

Datapump export with error ORA-28112


Today I was implementing datapump export to one of my database where I came across this error below.
. . exported "SYSMAN"."MGMT_TARGET_TYPE_VERSIONS"        54.52 KB     395 rows
. . exported "SYSMAN"."OCS_TARGET_ASSOC_DEFS"            70.78 KB     537 rows
. . exported "SYSMAN"."OCS_TEMPLATES_DEFS"               59.57 KB     316 rows
. . exported "SYSMAN"."AQ$_MGMT_ADMINMSG_BUS_S"          7.820 KB       3 rows
ORA-31693: Table data object "SYSMAN"."MGMT_IP_REPORT_DEF" failed to load/unload and is being skipped due to error:
ORA-28112: failed to execute policy function
. . exported "SYSMAN"."MGMT_TASK_QTABLE"                 19.21 KB      32 rows
. . exported "SYSMAN"."AQ$_MGMT_HOST_PING_QTABLE_S"      7.789 KB       2 rows
ORA-31693: Table data object "SYSMAN"."MGMT_JOB" failed to load/unload and is being skipped due to error:
ORA-28112: failed to execute policy function
. . exported "SYSMAN"."AQ$_MGMT_LOADER_QTABLE_S"         7.781 KB       2 rows
. . exported "SYSMAN"."AQ$_MGMT_NOTIFY_QTABLE_S"         7.781 KB       2 rows
. . exported "SYSMAN"."BHV_TARGET_ASSOC_DEFS"            11.68 KB      34 rows
. . exported "SYSMAN"."DB_USER_PREFERENCES"              7.671 KB      14 rows

This error happens when we do an export of grid control OMS database.
Running export as SYS or SYSTEM  may not be a problem and other exports may run without error.

User running the export might have the required privileges to run the export such as EXP_FULL_DATABASE, CONNECT, DBA, etc., but still we face the above error.

Users Running Export should have EXEMPT ACCESS POLICY privilege to export all rows as that user is then exempt from VPD policy enforcement.  SYS is always exempted from VPD or Oracle Label Security policy enforcement, regardless of the export mode, application, or utility that is used to extract data from the database.

So the workaround would be to grant the exempt access policy to the user running the export.
SQL> grant exempt access policy to USERNAME; -- replace with desired username

Grant succeeded.

Now the export comletes without any error. :-)