Re: Oracle RAC and external tables
Date: Fri, 18 Dec 2009 11:09:00 +0100
external tables are accessed by an oracle instance through oracle database object DIRECTORY's which is just a mapping between a filesystem directory and an oracle object. You can put the external table on a shared filesystem (e.g. OCFS2 or asmfs if available) mounted on the same mountpoint on all your rac nodes.. As long as you make sure the file is accessible by the oracle processes of all instances of the RAC cluster using the same directory path using external tables in RAC should work fine. It maybe a good idea to use a clustered database service for this (srvctl add service -s <exttablesrvc> ..) which can be set to run on a preferred instance or one of the available instances.
2009/12/18 Steven Rebello <Steven.Rebello_at_mastek.com>
> Hi list members,
> Need your inputs to help understand how RAC and external tables work.
> We are planning to use external tables to upload large volumes of data from
> a flat file, which is FTP'ed into the database server.
> Currently, in the DEV environment we have a single-node Oracle instance
> (non-RAC) which reads the files from the local disk and uploads the data.
> The upload process is triggered from a web application which calls a PLSQL
> proc doing the external table creation and loading. Everything works fine.
> But now, in the planned production Oracle RAC, since there 2 nodes having
> separate disks, it is possible that the file is FTP'ed into node 1 but the
> web application invokes the proc on node 2 due to load balancing. As node2
> doesn't have the file on its local disk, the processing fails.
> With this background, can you help with the queries below :
> - First thought was to place the directory containing the files on the
> shared SAN, but had been advised against it with the reason that OCFS does
> not support non-DBF files. Is this a valid point? Couldn't find any
> reference document for this.
> - Planned to use NFS share and point the directory object to that, but then
> NFS server becomes the single point of failure L Any workarounds for this?
> - If we cannot share flat files on RAC, does that mean that RAC and
> external tables don't gel together?