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

Useful OEM Queries to get Target details from OEM Repository

List Targets with TNS Listener ports configured : SELECT mgmt$target.host_name , mgmt$target.target_name , mgmt$target.target_type , mgmt$target_properties.property_name , mgmt$target_properties.property_value FROM mgmt$target , mgmt$target_properties WHERE ( mgmt$target.target_name = mgmt$target_properties.target_name ) AND ( mgmt$target.target_type = mgmt$target_properties.target_type ) and ( mgmt$target.target_type = 'oracle_listener' ) and ( mgmt$target_properties.property_name = 'Port' ); Devora02       LISTENER_ora02                       oracle_listener Port 1529 Devora01       LISTENER_ora01                       oracle_listener Port 1529 Devora04       LISTENER_ora04                       oracle_listener Port 1529 List Machine_Names, CPU Count & Database Verion for Licensing SELECT mgmt$target.host_name , mgmt$target_properties.property_name , mgmt$target_properties.property_value FROM mgmt$target , mgmt$target_properties WHERE ( mgmt$
Read more

WARNING: Subscription for node down event still pending' in Listener Log

Image
These messages are related to the Oracle TNS Listener's default subscription to the Oracle Notification Service (ONS). In a non-RAC environment it is recommended to disable this subscription.   This feature was introduced in Oracle 10g. SOLUTION Set the following parameter in the listener.ora: SUBSCRIBE_FOR_NODE_DOWN_EVENT_<listener_name>=OFF Where <listener_name> should be replaced with the actual listener name configured in the LISTENER.ORA file. SUBSCRIBE_FOR_NODE_DOWN_EVENT_<listener_name> parameter is to be placed by itself on an empty line. It will be necessary to restart or reload the listener following the addition of this parameter. This will prevent the messages from being written to the log file and may also prevent the TNS Listener from hanging periodically.  See (10g only) NOTE 340091.1 Intermittent TNS Listener Hang, New Child Listener Process Forked Please Note: Setting SUBSCRIBE_FOR_NODE_DOWN_<listener_n
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