Impdp Ora-29913 Error In Executing Odciexttableopen Callout
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 ora-29913: error in executing odciexttablepopulate callout expdp hit this error from one of my customers during import of a schema:
Ora-31693 Ora-29913 Ora-02291
ORA-31693: Table data object "SH"."SALES_REG1_Q1" failed to load/unload and is being skipped due to error: ORA-29913: error in executing ODCIEXTTABLEOPEN callout
Ora-31693 Ora-29913 Ora-00600
After some searching on the internet, I 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
Oracle Bug 8393456
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. 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: ora-31693 ora-29913 ora-02291 impdp Post Comments (Atom) Blog Archive ► 2016 (40) ► October (2) ► September (2) ► 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
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 ora-31693: table data object failed to load/unload and is being skipped due to error being skipped due to error: ORA-29913: error in executing ODCIEXTTABLEOPEN callout . ora-29913: error in executing odciexttablefetch callout . imported "TEST"."CI_TAB1_K_T" 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 ora-29913 ora-29400 12:04:50 ORA-31693: Table data 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 http://minimalistic-oracle.blogspot.com/2014/03/i-recently-hit-this-error-from-one-of.html same can be 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 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 http://amitksri14.blogspot.com/2015/01/impdp-error-ora-29913-error-in.html Amit Kumar Srivastava (Oracle 11g Certified Master - OCM11g) 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 1
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. http://www.acehints.com/2012/11/oracle-11gr2-impdp-dataoptions.html DATA_OPTIONS = [DISABLE_APPEND_HINT | 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 http://facedba.blogspot.com/2013/10/export-or-import-taking-consistent.html be enabled in impdp. If 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 error in This page I wanted to elaborate 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 error in executing for some records. It will import the records which satisfies the 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 Appli
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 notes that the system change number (SCN) that most closely matches the specified time is found, and this SCN is used to enable the Flashback utility. The export operation is performed with data that is consistent up to this SCN. $cat mybackup2.par full=y directory=data_pump dumpfile=oraprod%U.dmp logfile=oraprod_exp.log p