Advertisement

Thursday, October 22, 2015

Oracle Database - 12c - Data Pump Error during ORA-1031 during export of SYS tables

 

Issue occurs when you do a full export of your 12c (mine is 12.1.0.2) database with export of few SYS tables. 
This is when you do a consistent export using flashback_time or flashback_scn parameter
Even if you have the role DATAPUMP_EXP_FULL_DATABASE

when you scan your log file, you will see multiple errors similar to below -

ORA-31693: Table data object "SYS"."KU$_USER_MAPPING_VIEW" failed to load/unload and is being skipped due to error:
ORA-01031: insufficient privileges
ORA-31693: Table data object "SYS"."FGA_LOG$FOR_EXPORT" failed to load/unload and is being skipped due to error:
ORA-01031: insufficient privileges
ORA-31693: Table data object "SYS"."AUDTAB$TBS$FOR_EXPORT" failed to load/unload and is being skipped due to error:
ORA-01031: insufficient privileges
ORA-31693: Table data object "SYS"."DBA_SENSITIVE_DATA" failed to load/unload and is being skipped due to error:
ORA-01031: insufficient privileges
ORA-31693: Table data object "SYS"."DBA_TSDP_POLICY_PROTECTION" failed to load/unload and is being skipped due to error:
ORA-01031: insufficient privileges
ORA-31693: Table data object "SYS"."NACL$_ACE_EXP" failed to load/unload and is being skipped due to error:
ORA-01031: insufficient privileges
ORA-31693: Table data object "SYS"."NACL$_HOST_EXP" failed to load/unload and is being skipped due to error:
ORA-01031: insufficient privileges
ORA-31693: Table data object "SYS"."NACL$_WALLET_EXP" failed to load/unload and is being skipped due to error:

ORA-01031: insufficient privileges

The workaround is to grant flashback to the tables reporting errors 

SQL> grant flashback on "SYS"."KU$_USER_MAPPING_VIEW"  to system;
Grant succeeded.

SQL> grant flashback on "SYS"."FGA_LOG$FOR_EXPORT" to system;
Grant succeeded.

SQL> grant flashback on  "SYS"."AUDTAB$TBS$FOR_EXPORT" to system;
Grant succeeded.

SQL> grant flashback on "SYS"."DBA_SENSITIVE_DATA"  to system;
Grant succeeded.

SQL> grant flashback on  "SYS"."DBA_TSDP_POLICY_PROTECTION" to system;
Grant succeeded.

SQL> grant flashback on  "SYS"."NACL$_ACE_EXP" to system;
Grant succeeded.

SQL> grant flashback on  "SYS"."NACL$_HOST_EXP"to system;
Grant succeeded.

SQL> grant flashback on "SYS"."NACL$_WALLET_EXP"  to system;
Grant succeeded.


The issue and workaround is reported in Bug - 18844843 

2 comments:
Write comments
  1. Pretty Post! It is really interesting to read from the beginning & I would like to share your blog to my circles, keep your blog as updated.
    Regards,
    Oracle Training in Chennai|Oracle Training Institutes in Chennai

    ReplyDelete
  2. Thanks Vineet I faced the same exact issue and found your post. For anyone landing here please note that bug 19238926 now includes a fix for this

    ReplyDelete