Expdp failing to export SYS Objects with ORA-01031: insufficient privileges in 12c

Issue  :   While taking expdp backup using system user in 12c Databases.. we are getting following errors..


 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

Fix/Solution Implemented as per Oracle (Doc ID 18844843.8): 


 Grant flashback privileges directly to the expdp user for the eight failing Views until you can apply the patch

 SYS@JDEPRD > grant flashback on "SYS"."KU$_USER_MAPPING_VIEW"  to system;

 Grant succeeded.

 SYS@JDEPRD > grant flashback on "SYS"."FGA_LOG$FOR_EXPORT" to system;

 Grant succeeded.

 SYS@JDEPRD > grant flashback on  "SYS"."AUDTAB$TBS$FOR_EXPORT" to system;

 Grant succeeded.

 SYS@JDEPRD > grant flashback on "SYS"."DBA_SENSITIVE_DATA"  to system;

 Grant succeeded.

 SYS@JDEPRD > grant flashback on  "SYS"."DBA_TSDP_POLICY_PROTECTION" to system;

 Grant succeeded.

 SYS@JDEPRD > grant flashback on  "SYS"."NACL$_ACE_EXP" to system;

 Grant succeeded.

 SYS@JDEPRD > grant flashback on  "SYS"."NACL$_HOST_EXP"to system;

 Grant succeeded.

 SYS@JDEPRD > grant flashback on "SYS"."NACL$_WALLET_EXP"  to system;

 Grant succeeded.
==============================================

  Reference:  Doc ID 18844843.8
Bug 18844843 - EXPDP Full with Flashback_scn or Flashback_time specified fails with ORA-31693 ORA-1031 on some objects in 12c - superseded (Doc ID 18844843.8)                

Bug 18844843  EXPDP Full with Flashback_scn or Flashback_time specified fails with ORA-31693 ORA-1031 on some objects in 12c - superseded

 This note gives a brief overview of bug 18844843.
 Affects:

 Product (Component)   Oracle Server (Rdbms)
Range of versions believed to be affected           Versions >= 12.1 but BELOW 12.2
Versions confirmed as being affected  
12.1.0.1 (Base Release)
Platforms affected          Generic (all / most platforms affected)

 Note that this fix has been superseded by the fix in Bug:19238926
Fixed:

 This fix has been superseded - please see the fixed version information for Bug:19238926 . The box below only shows versions where the code change/s for 18844843 are first included - those versions may not contain the later improved fix.

 The fix for 18844843 is first included in  
12.2 (Future Release)
Symptoms:

 Related To:

 Performance Of Certain Operations Affected
Error May Occur
ORA-31693 / ORA-1031
Flashback
Datapump Export/Import
This fix has been superseded
Description

 This bug is only relevant when using Datapump Export/Import
A full database datapump export with either FLASHBACK_TIME or FLASHBACK_SCN for privileged expdp user
specified will encounter ORA-31693 and ORA-01031 "insufficient privileges" errors on the
following eight views in the SYS schema:
 "KU$_USER_MAPPING_VIEW"
"FGA_LOG$FOR_EXPORT"
"AUDTAB$TBS$FOR_EXPORT"
"DBA_SENSITIVE_DATA"
"DBA_TSDP_POLICY_PROTECTION"
"NACL$_ACE_EXP"
"NACL$_HOST_EXP"
"NACL$_WALLET_EXP"
Rediscovery Notes

 ORA-01031 "insufficient privileges" on SYS-owned objects when having specified
one of DataPump's two flashback parameters: FLASHBACK_SCN or FLASHBACK_TIME.

 Example error output excerpt:
expdp tc/tc directory=my_dir dumpfile=expdp_f.dmp logfile=expdp_f.log
reuse_dumpfiles=y full=y flashback_time=systimestamp
Export: Release 12.1.0.1.0 - Production on Tue May 27 10:25:38 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 -
64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing
options
Starting "TC"."SYS_EXPORT_FULL_01":  tc/******** directory=my_dir
dumpfile=expdp.dmp logfile=expdp.log reuse_dumpfiles=y full=y
flashback_time=systimestamp
Estimate in progress using BLOCKS method...
Processing object type
DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
...
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
.....
Workaround

 Don't use flashback. (we are using flashback_scn)
or
Grant flashback privileges directly to the expdp user for the eight failing Views until you can apply the patch:

 grant flashback on SYS.KU$_USER_MAPPING_VIEW TO <expdp username>;
grant flashback on ORDDATA.ORDDCM_DOCS to <expdp username>;
grant flashback on SYS.FGA_LOG$FOR_EXPORT to <expdp username>;
grant flashback on SYSTEM.SCHEDULER_JOB_ARGS to <expdp username>;
grant flashback on SYSTEM.SCHEDULER_PROGRAM_ARGS to <expdp username>;
grant flashback on SYS.AUDTAB$TBS$FOR_EXPORT to <expdp username>;
grant flashback on SYS.DBA_SENSITIVE_DATA to <expdp username>;
grant flashback on SYS.DBA_TSDP_POLICY_PROTECTION to <expdp username>;
grant flashback on SYS.NACL$_ACE_EXP to <expdp username>;
grant flashback on SYS.NACL$_HOST_EXP to <expdp username>;
grant flashback on SYS.NACL$_WALLET_EXP to <expdp username>;
grant flashback on WMSYS.WM$EXP_MAP to <expdp username>;
Note:
This fix has been superseded by the fix in bug 19238926. For interim patches use that fix instead of this one.

Comments

Post a Comment

Popular posts from this blog

ORA-28086: The data redaction policy expression has an error

Image
Error: ERROR at line 1: ORA-28086: The data redaction policy expression has an error. ORA-06512: at "SYS.DBMS_REDACT_INT", line 3 ORA-06512: at "SYS.DBMS_REDACT", line 42 ORA-06512: at line 1 When we try to run same as sys user it is working perfectly fine, but as a normal user it is failing with below errors. No Privileges issue because AVO user had DBA Privilege .. Versions  confirmed  as being affected 12.1.0.2 (Server Patch Set) 11.2.0.4 Fixed: The fix for 20693579 is first included in 12.2.0.1 (Base Release) Cause : Bug 20693579 - DBMS_REDACT.ADD_POLICY fails with ORA-28086 when CURSOR_SHARING is force (Doc ID 20693579.8) To Bottom Bug 20693579  DBMS_REDACT.ADD_POLICY fails with ORA-28086 when CURSOR_SHARING is force  This note gives a brief overview of bug 20693579.  The content was last updated on: 17-FEB-2017   Click  here  for details of each of the sections below. Aff...
Read more

ORA-65139: Mismatch between XML metadata file and data file

Error: CDB$ROOT@EBTUPRDC> CREATE PLUGGABLE DATABASE EBTUSPRDPDB1 using '/home/oracle/EBTUSPRDC.xml' nocopy tempfile reuse; CREATE PLUGGABLE DATABASE EBTUSPRDPDB1 using '/home/oracle/EBTUSPRDC.xml' nocopy tempfile reuse * ERROR at line 1: ORA-65139: Mismatch between XML metadata file and data file /u05/odb/ORADATA_1/EBTUSPRDC/datafile/system.1531.807800993 for value of fcpsb (2230502002 in the plug XML file, 2230505492 in the data file) Fix: Don't open Non-CDB database until we complete pdb creation other wise we will get above error. Shutdown immediate; startup mount; alter database open read only; create xml file. shutdown immediate; Connect to CDB and Create Pluggable database .
Read more

TFA-00002 : Oracle Trace File Analyzer (TFA) is not running

TFA Failed to start listening for commands on one of the Rac Node : As a root user /etc/init.d/init.tfa  start Starting TFA.. start: Job is already running: oracle-tfa Waiting up to 100 seconds for TFA to be started.. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Successfully started TFA Process.. . . . . . TFA-00002 : Oracle Trace File Analyzer (TFA) is not running TFA Failed to start listening for commands Solution : run synctfanodes.sh  if you have root password . if no root password copy below files to TFA Failed node and try to restart TFA .. Please check whether these 4 files exist on all nodes are similar. TFA_HOME/server.jks TFA_HOME/client.jks TFA_HOME/internal/ssl.properties   TFA_HOME/internal/ portmapping.txt If not similar just copy these files to TFA Failing node and try TFA Restart ..It Worked for me .   [root@host01]# ./tfactl  -v "debug" start Starting TFA...
Read more