Resolving ORA-29913 in RAC environment

Problem: Select from external table returns following error stack

ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file TEST.TXT in TEST_DIR not found
ORA-06512: at “SYS.ORACLE_LOADER”, line 19

Let’s simulate the problem

$ sqlplus / as sysdba

SQL>

DROP DIRECTORY TEST_DIR
/
CREATE OR REPLACE DIRECTORY TEST_DIR AS ‘/home/oracle/testdir’
/
GRANT READ, WRITE ON DIRECTORY TEST_DIR TO TESTUSER
/

SELECT * FROM DBA_DIRECTORIES
/
OWNER, DIRECTORY_NAME, DIRECTORY_PATH
SYS, TEST_DIR, /home/oracle/testdir

$ sqlplus testuser

SQL>

DROP TABLE TEST_EXTERNAL PURGE
/

CREATE TABLE TEST_EXTERNAL
(
COL1 NUMBER,
COL2 NUMBER
)
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY TEST_DIR
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
BADFILE TEST_DIR:’TEST_EXT.BAD’
LOGFILE TEST_DIR:’TEST_EXT.LOG’
SKIP 1
FIELDS TERMINATED BY WHITESPACE
OPTIONALLY ENCLOSED BY ‘”‘
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(
COL1,
COL2
)
)
LOCATION (TEST_DIR:’TEST.TXT’)) REJECT LIMIT 0
/

Table created

SQL> SELECT * FROM TEST_EXTERNAL
/

ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file TEST.TXT in TEST_DIR not found
ORA-06512: at “SYS.ORACLE_LOADER”, line 19

In RAC environment, when we connect to a service, depending on the instance load connection will be made to least loaded instance.

So in a 2-node cluster, if we connect to an instance & physical data file (TEST.TXT in our example) is present on other node, we’ll get ORA-29913 error.

There are multiple solutions for this problem

1. Create physical directories on all the nodes of cluster. Copy the physical data files in all locations.

2. Use an OS level Cluster file system, visible from all nodes. This is normally an expensive option, as additional license will be required.

3. While presenting the storage from SAN / NAS, we can present it as NFS. This will also require separate license from the storage provider.

4. Connect to a specific instance, while accessing the external tables, although we loose the advantages of using the services.

Advertisements
This entry was posted in Oracle External Tables and tagged . Bookmark the permalink.

2 Responses to Resolving ORA-29913 in RAC environment

  1. Orlando says:

    Hi mister Bhandarkar!
    Have you any experience with Windows ? Probably not. Here we’re thinking about using an ocfs drive we have to put directories for UTL_FILE_DIR, what would be a commom location for the files.
    Also, to create a service that uses 1 node out of the 3 we have (as Preferred, and another one as Available) looks like a solution. Can you comment about ?

  2. ybhandarkar says:

    Any CFS (Cluster File System, OCFS in this case) can be used.

    Not sure, about the context of service here.

    -Yogesh

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s