ACFS & external tables on RAC

Oracle external tables are a great way to read text file and use them as tables. But if we are using this feature in a RAC database, any instance can access this external table. It will also expect underlying data files present on the local node. Without having a CFS (Cluster File System), one would create local file system on every node in the cluster and place the data files in them. Well, that really doesn’t sound like the way we want it to be.

With oracle release of ACFS, we can now create a CFS (ACSF) and handle this problem very easily.

Steps to create ACFS & external table

Start asmca  and navigate to  volums tab

Select  the diskgroup, where you want to create the volume

Specify the  purpose of the ACFS filesystem

Should see confirmation that filesystem is  mounted.




*** Verify the ASM volume

# ls -al /dev/asm/vol*
brwxrwx--- 1 root asmadmin 252, 159745 Jul 11 17:13 
/dev/asm/vol1-312

*** Verify FS mount from cluster nodes

grid@db1:~> df -h | grep asm
/dev/asm/vol1-312 1.0G 73M 952M 8% /u01/app/grid/acfsmounts/asmfs

grid@db2:/dev/asm> df -h | grep asm
/dev/asm/vol1-312 1.0G 73M 952M 8% /u01/app/grid/acfsmounts/asmfs
*** Check filesystem mount options 
# mount | grep asm 
/dev/asm/vol1-312 on /u01/app/grid/acfsmounts/asmfs type acfs (rw)

*** Register mount point option does not register it to fstab

cat /etc/fstab | grep asm | wc -l
0
*** Check acfs registry

*** Check acfs registry

# /sbin/acfsutil registry -l
Device : /dev/asm/vol1-312 : Mount Point : 
/u01/app/grid/acfsmounts/asmfs : Options : none : 
Nodes : all : Disk Group : TESTDG : Volume : VOL1 

# acfsutil registry
Mount Object:
 Device: /dev/asm/vol1-312
 Mount Point: /u01/app/grid/acfsmounts/asmfs
 Disk Group: TESTDG
 Volume: VOL1
 Options: none
 Nodes: all

Create a directory object using ACFS path

SQL> create directory test_dir as 
'/u01/app/grid/acfsmounts/asmfs';
Directory created.
SQL> select * from dba_directories where 
DIRECTORY_NAME ='TEST_DIR';
OWNER   DIRECTORY_NAME  DIRECTORY_PATH
------- --------------- ------------------------------
SYS     TEST_DIR        /u01/app/grid/acfsmounts/asmfs

Create an external table using the directory object

SQL> CREATE TABLE emp_load
(
 employee_number CHAR(5),
 employee_last_name CHAR(20),
 employee_first_name CHAR(15)
)
ORGANIZATION EXTERNAL
 (
 TYPE ORACLE_LOADER
 DEFAULT DIRECTORY TEST_DIR
 ACCESS PARAMETERS
 (
 RECORDS DELIMITED BY NEWLINE 
 FIELDS TERMINATED BY ' ' (employee_number CHAR(5),
 employee_last_name CHAR(18),
 employee_first_name CHAR(11)
 )
 )
LOCATION ('info.dat')
);
Table created.

Access the table from all the instances & verify

— NODE 1

SQL> select instance_name, host_name from v$instance;
INSTANCE_NAME    HOST_NAME
---------------- ------------
RACDB1           db1

SQL> select * from emp_load;
EMPLO EMPLOYEE_LAST_NAME   EMPLOYEE_FIRST_
----- -------------------- ---------------
101   L1                   F1
102   L2                   F2
103   L3                   F3
104   L4                   F4
105   L5                   F5
6 rows selected.

— From NODE 2

SQL> select instance_name, host_name from v$instance;
INSTANCE_NAME     HOST_NAME
---------------- ----------
RACDB2           db2

SQL> select * from emp_load;
EMPLO EMPLOYEE_LAST_NAME   EMPLOYEE_FIRST_
----- -------------------- ---------------
101   L1                   F1
102   L2                   F2
103   L3                   F3
104   L4                   F4
105   L5                   F5
6 rows selected.
Advertisements
This entry was posted in ACFS, Oracle 11.2.0.x, Oracle 11g Enhancements, Oracle Automatic Storage Management, Oracle External Tables, Oracle Real Application Cluster and tagged . Bookmark the permalink.

One Response to ACFS & external tables on RAC

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