EXPDP encountered ora-31693&ora-00922
Execution EXPDP encountered the following error:
Ora-31693:table data Object "Hq_x1". " B3901_p "failed to Load/unload and was being skipped due to error:
ora-00922:missing or Invalid option
Ora-31693:table data Object "Hq_x1". " B3903 "failed to Load/unload and was being skipped due to error:
ora-00922:missing or Invalid option
Ora-31693:table data Object "Hq_x1". " B3904 "failed to Load/unload and was being skipped due to error:
ora-00922:missing or Invalid option
Ora-31693:table data Object "Hq_x1". " B3906 "failed to Load/unload and was being skipped due to error:
ora-00922:missing or Invalid option
Ora-31693:table data Object "Hq_x1". " B3905 "failed to Load/unload and was being skipped due to error:
ora-00922:missing or Invalid option
The EXPDP command is:
Nohup EXPDP hq_x1/hq_x1 dumpfile=hq_x1%u.dmp directory=dir1 parallel=8 flashback_scn=10838324803 &
An official document gives an explanation:
A DataPump Export fails with:
Ora-31693:table data Object "Scpomgr". " FCST "failed to Load/unload and was being skipped due to error:
ora-00922:missing or Invalid option
Changes
The use of the PARALLEL command line option triggers this issue.
Cause
The cause of this problem have been identified in:
bug:8734963-ora-31693 and ORA-922 occurs in EXPDP with PARALLEL PARAMETER.
Closed as duplicate of:
BUG:9243068-EXPDP RETURNS ora-00922:missing OR INVALID OPTION when USING consistent=true
The cause of the issue is a incorrect PARALLEL value for a external table to being exported when using deferred segment CRE ation.
bug:9243068 is a fixed in RDBMS 12.1.
Solution
To implement a solution for bug:9243068, please execute any of the below alternative solutions:
Upgrade to 12.1 on which bug:9243068 is fixed.
OR
Apply Patchset release 11.2.0.4 in which bug:9243068 is fixed.
OR
Download and apply interim patch:9243068, if available for your platform and RDBMS release. To check for conflicting patches, use the MOS Patch Planner Tool. If no patch is available, the file a Service Request through My Oracle support for your specific Oracle version and platform.
OR
The PARALLEL command line option.
BUG8734963 caused the error, if you want to avoid error, or disable parallel, or hit patch, first put down the patch down, found that this patch can be online apply:
<instance_shutdown>false</instance_shutdown>
<instance_shutdown_message></instance_shutdown_message>
<online_rac_installable>true</online_rac_installable>
The patch simply updates a PLD file and updates a package. To draw a system idle time, apply patch:
[Email protected] 9243068]$/u01/app/oracle/product/11.2.0/db_1/opatch/opatch apply
Invoking Opatch 11.1.0.6.6
Oracle Intermediate Patch Installer version 11.1.0.6.6
Copyright (c), Oracle Corporation. All rights reserved.
Oracle Home directory:/u01/app/oracle/product/11.2.0/db_1
Main Product List:/u01/app/orainventory
From:/etc/orainst.loc
Opatch version: 11.1.0.6.6
OUI version: 11.2.0.1.0
OUI Location:/u01/app/oracle/product/11.2.0/db_1/oui
Log file location:/u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2015-06-23_22-55-59 pm. Log
Patch history File:/u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch_history.txt
Applysession Apply the middle patch ' 9243068 ' to OH '/u01/app/oracle/product/11.2.0/db_1 '
Running Prerequisite Checks ...
Opatch the non-clustered Oracle home directory is detected from the product list, only patches are made to the local system.
Backing up files and product listings for Oracle home directories (not for automatic fallback)
Backing up files affected by patch ' 9243068 ' for restore. This operation will take some time ...
Backing up files affected by patch ' 9243068 ' for fallback. This operation will take some time ...
Oracle.rdbms for component, 11.2.0.1.0 patch ...
Copy files to "/U01/APP/ORACLE/PRODUCT/11.2.0/DB_1/RDBMS/ADMIN/PRVTBPW.PLB"
Oracle.rdbms.dbscripts for component, 11.2.0.1.0 patch ...
Copy files to "/U01/APP/ORACLE/PRODUCT/11.2.0/DB_1/RDBMS/ADMIN/PRVTBPW.PLB"
Applysession Add the Intermediate patch ' 9243068 ' to the product list
Verifying the update ...
Inventory Check Ok:patch ID 9243068 is registered in Oracle Home Inventory with proper meta-data.
Files check ok:files from Patch ID 9243068 is present in Oracle Home.
Opatch succeeded.
[Email protected] 9243068]$
After patch apply, PRVTBPW.PLB was executed with SYSDBA.
Once again EXPDP, no more error, problem solved.
EXPDP encountered ora-31693&ora-00922
