Ora-29913 Error In Executing Odciexttableopen Callout While Impdp
Contents |
is to provide simple examples, so that they can be easily adapted to other situations. Thursday, March 6, 2014 How to deal with impdp error ORA-29913: error in executing ODCIEXTTABLEOPEN callout I recently hit this error from one ora-29913: error in executing odciexttablepopulate callout expdp of my customers during import of a schema: ORA-31693: Table data object "SH"."SALES_REG1_Q1" failed
Ora-31693 Ora-29913 Ora-02291
to load/unload and is being skipped due to error: ORA-29913: error in executing ODCIEXTTABLEOPEN callout After some searching on the internet, I ora-31693 ora-29913 ora-00600 found evidence of bug 8393456, and it is indeed confirmed on the Oracle Support website. The problem seems to be related to cases where you use both PARALLEL (>1) and REMAP_SCHEMA at the same time. Oracle
Oracle Bug 8393456
versions affected are 11.2.0.1, 11.1.0.7 and 10.2.0.4. I implemented the workaround by setting PARALLEL=1 and I can confirm that it solves the problem. Posted by Name: Vegard Kåsa at Thursday, March 06, 2014 Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest Labels: Errors, Oracle Utilities No comments: Post a Comment Newer Post Older Post Home Subscribe to: Post Comments (Atom) Blog Archive ► 2016 (40) ► October (2) ► September (2) ► ora-31693: table data object failed to load/unload and is being skipped due to error August (6) ► July (5) ► June (4) ► May (1) ► April (4) ► March (5) ► February (5) ► January (6) ► 2015 (68) ► December (9) ► November (10) ► October (6) ► September (4) ► August (4) ► July (5) ► June (1) ► May (8) ► April (8) ► March (2) ► February (4) ► January (7) ▼ 2014 (135) ► December (9) ► November (4) ► October (8) ► September (3) ► August (6) ► July (7) ► June (25) ► May (12) ► April (1) ▼ March (18) What are nodeapps in an Oracle RAC environment? How to use the dbms_metadata package to generate D... How to use the dbms_metadata package to generate D... How to perform a clone from active database How to set up ssh connection between two servers f... What is the difference between "in line" and "out ... How to use the different options for DATE datatype... What are AWR and ADDM and how are they related? How to find the AWR snapshots currently available ... How to check that an Oracle database parameter is ... How to receive user response in a korn shell unix ... How to gather histograms on a column of a table How to set up an alias th
AS SYSDBA dumpfile=exp_DC_OCM_2tbs_29Jan2015_1150_%U.dmp directory=DATAPUMP_DIR3 logfile=imp_exp_DC_OCM_2tbs_29Jan2015_1150.log table_exists_action=replace remap_schema=test:test parallel=24 Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/TABLE_DATA ORA-31693: Table data object "TEST"."CI_TAB2_T" failed to load/unload and is being skipped due
Ora-29913: Error In Executing Odciexttablefetch Callout
to error: ORA-29913: error in executing ODCIEXTTABLEOPEN callout . . imported "TEST"."CI_TAB1_K_T"
Ora-29400 Data Cartridge Error
5.003 MB 1241230 rows Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Job "SYS"."SYS_IMPORT_FULL_02" completed with 1 error(s) at 12:04:50 ORA-31693: Table data impdp table_exists_action object "TEST"."CI_TAB2_T" failed to load/unload and is being skipped due to error: ORA-29913: error in executing ODCIEXTTABLEOPEN callout This is because of bug 8393456, and same can be confirmed on the http://minimalistic-oracle.blogspot.com/2014/03/i-recently-hit-this-error-from-one-of.html Oracle Support website. The problem seems to be related to cases where you use both PARALLEL (>1) and REMAP_SCHEMA at the same time. Oracle versions affected are 11.2.0.1, 11.1.0.7 and 10.2.0.4. Workaroundimplementedby setting PARALLEL=1 (even you can user remap_schema) clause Solution: Do not user PARALLEL >1 with REMAP_SCHEMA clause in datapump (impdp). Posted by Amit Kumar Srivastava (Oracle 11g Certified Master - OCM11g) http://amitksri14.blogspot.com/2015/01/impdp-error-ora-29913-error-in.html at 10:35 AM Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest No comments: Post a Comment Newer Post Older Post Home Subscribe to: Post Comments (Atom) Total Pageviews About Me Amit Kumar Srivastava (Oracle 11g Certified Master - OCM11g) Oracle 11g Certified Masters (OCM) having Sound Expertise in (Exadata,Golden Gate, RAC,Dataguard, RMAN, Performance Tuning, Warehouse, Encryption,Audit and DB Security, Wallet ....etc View my complete profile Blog Archive ▼ 2015 (24) ► July (4) ► June (2) ► February (8) ▼ January (10) impdp: error ORA-29913: error in executing ODCIEXT... Failed to establish dependency between database DB... GOLDEN GATE SETUP - DML/DDL Golden Gate Setup on ASM Golden Gate: ERROR OGG-01224 Address already in u... opiodr aborting process unknown ospid as a result ... OCSSD starts if voting disk & OCR resides in ASM D... Oracle Kernel RAC: ON and OFF GoldenGate - Error ORA-20782 while deleting Golden... GoldenGate - Overview ► 2014 (7) ► December (7) ► 2012 (1) ► January (1) OCM11g http://education.oracle.com/education/otn/AmitKSrivastava.htm Visitors DISCLAIMER Home Oracle 11gR2 ASM Installation... Automatic Stats for Large Table with 100% RUNNING STATS DETAILS Disclaimer Simple template. Powered by Blogger.
import. You have to invoke it specifically to handle some special kind of data during the import operations. There are 2 options for this parameter. DATA_OPTIONS = [DISABLE_APPEND_HINT | http://www.acehints.com/2012/11/oracle-11gr2-impdp-dataoptions.html SKIP_CONSTRAINT_ERRORS] Value 1: DISABLE_APPEND_HINT This vale tells the impdp not to use the APPEND hint during the import operation. By default APPEND hint will be enabled in impdp. If http://facedba.blogspot.com/2013/10/export-or-import-taking-consistent.html you disable this hint by using this option will help you to access the importing tables by other sessions concurrently. Value 2: SKIP_CONSTRAINT_ERRORS This page I wanted to elaborate error in more on this parameter value SKIP_CONSTRAINT_ERRORS. In normal case the impdp utility will rollback the entire table import in case any constraint error encounters on that particular table. If you use SKIP_CONSTRAINT_ERRORS as the parameter value, it will tell the import operation to continue even there is constraint errors for some records. It will import the records which satisfies the error in executing constraints. See example for more details. I have a table called EMP with 12 number of records. SQL> select count(*) from emp; COUNT(*) ---------- 12 Adding a unique constraint to the table SQL> alter table emp add constraint emp_ename unique(ename) ; Table altered. I have taken the export of this table and I deleting 2 records from that. SQL> delete from emp where rownum<3; 2 rows deleted. SQL> commit; Commit complete. Importing the dump with table_exists_action=append. This operation is getting failed with constraint error. It will not insert any of the rows from the export dump. It will rollback the entire table import operation. $ impdp job_name=schemaexp1 full=y dumpfile=exp_emp.dmp logfile=imp_exp_emp.log directory=EXP_DIR table_exists_action=append Import: Release 11.2.0.2.0 - Production on Wed Nov 7 02:22:58 2012 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Username: / as sysdba Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options Master table "SYS"."SCHEMAEXP1" successfully loaded/unloaded Starting "SYS"."SCHEMAEXP1": /******** AS S
backup using expdp ( RAC / Non-RAC) ~ Applied to: Oracle 10g/11g About Document:Some times management asks to create pre-prod database environment to test some scenarios/ to train users / patch movement. But prod and UAT/ Pre-PROD may not be same platform. So that you can't restore from RMAN backup ( ENDIAN_FORMAT should support. Check using v$transportable_platform view). So you have choice to restore using EXPDP ( logical backup). Then you need a consistent backup to restore. So using EXPDP you can take backup upto a snap shot time ( SCN), you can follow the below method: Note: You can use Oracle golden gate to migrate database across platform. Other third party tools can be used also. expdp full=y directory=DATA_PUMP dumpfile=oraprod_full.dmp logfile=oraprod_full_exp.log exclude=statistics FLASHBACK_SCN=25330457434 COMPRESSION=ALL -- To find FLASHBACK_SCN sql> select name,current_scn from v$database; Note: COMPRESSION=ALL can be used to take compressed backup. Same can be ignore. This is 11g new feature. -- Find currently export executing jobs sql> select * from dba_datapump_jobs where state='EXECUTING'; -- Current Status of export /import sql> select sid,serial#,sofar,totalwork,start_time, sysdate,time_remaining,message from v$session_longops where opname like '%IMPORT%'; Sample output: Note: Use CLUSTER=N in expdp/ impdp while exporting/importing from/to multitude RAC database. You can add PARALLEL clause to make the EXPDP/ IMPDP faster. "flashback_time" can be used for consistent backup using "expdp": Flashback_time at current time: The flashback_time=systimestamp syntax: is the current time. $ cat mybackup1.par full=y directory=data_pump dumpfile=oraprod%U.dmp logfile=oraprod_exp.log parallel=4 filesize=20G exclude=statistics flashback_time=systimestamp $ expdp parfile=mybackup.par Flashback_time at specific time: Instead of the current time, you can specify any time which is possible within flashback capabilities (undo log sizes). using the to_timestamp argument. Oracle not