One of my customer is using Oracle RAC (11.2.0.3) on Windows 2012. This is might not be the most ideal setup I’ve ever seen, but it works and we’ll leave it by that.

One of the side effects of using Oracle RAC on Windows is that some of the basic things I am used to do when using RAC on Linux (for example) is behaving differently when it comes to windows. Here is a quick example for that.

I was asked by the customer to create an external table using a fixed-record file. This should be easy enough, right? Well, yeah – but we need to consider that we might connect to the database from either node so we need to put the file on a shared storage.

The customer is using ASM for his RAC so what better solution we have than using ACFS configured on top of the ASM?

When using ACFS with Windows, the ACFS mount is configured by Oracle as a symbolic link (which means everything will look the same, but not quite). When we try to configure the external table like this:

CREATE TABLE ext_optin_load
(
odac CHAR(20),
adc CHAR(20),
allowed CHAR(10),
updated_date char(30)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY ORACLEDP
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE skip 2
FIELDS
(odac             position(1:20) CHAR(20),
adc              position(22:41) CHAR(20),
allowed          position(43:53) CHAR(10),
updated_date     position(55:77) CHAR(30)
)
)
LOCATION ('optin_records.txt')
);

CREATE TABLE ext_optin_load
(
odac CHAR(20),
adc CHAR(20),
allowed CHAR(10),
updated_date char(30)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY ORACLEDP
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE skip 2
FIELDS
(odac             position(1:20) CHAR(20),
adc              position(22:41) CHAR(20),
allowed          position(43:53) CHAR(10),
updated_date     position(55:77) CHAR(30)
)
)
LOCATION ('optin_records.txt')
);

We get this error:

ERROR at line 1:
ORA-12801: error signaled in parallel query server P000, instance ol6-112-rac1.localdomain:RAC1 (1)
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-29400: data cartridge error
KUP-04027: file name check failed optin_records.txt

ERROR at line 1:
ORA-12801: error signaled in parallel query server P000, instance ol6-112-rac1.localdomain:RAC1 (1)
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-29400: data cartridge error
KUP-04027: file name check failed optin_records.txt

This means that something is wrong with the file name – but what’s wrong? Why isn’t this working?

Solution

My first reaction to that was this is probably a bug so I went looking this up on MOS, and indeed – I found a bug note on that: #14045247.
At the end of the note – I had a surprise. This was not a bug:

Not a bug.

An ACFS directory on the MS-Windows platform is implemented as a JUNCTION,
and is therefore a symbolic link. Therefore, DISABLE_DIRECTORY_LINK_CHECK
needs to be used, or a non-ACFS directory.

Ah, that’s nice – so all I had to do is change my external table and use the DISABLE_DIRECTORY_LINK_CHECK like this:

CREATE TABLE ext_optin_load
(
odac CHAR(20),
adc CHAR(20),
allowed CHAR(10),
updated_date char(30)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY ORACLEDP
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE skip 2
DISABLE_DIRECTORY_LINK_CHECK
FIELDS
(odac             position(1:20) CHAR(20),
adc              position(22:41) CHAR(20),
allowed          position(43:53) CHAR(10),
updated_date     position(55:77) CHAR(30)
)
)
LOCATION ('optin_records.txt')
);

CREATE TABLE ext_optin_load
(
odac CHAR(20),
adc CHAR(20),
allowed CHAR(10),
updated_date char(30)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY ORACLEDP
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE skip 2
DISABLE_DIRECTORY_LINK_CHECK
FIELDS
(odac             position(1:20) CHAR(20),
adc              position(22:41) CHAR(20),
allowed          position(43:53) CHAR(10),
updated_date     position(55:77) CHAR(30)
)
)
LOCATION ('optin_records.txt')
);

…and the problem was solved.