Create and Manage a tablespace that uses NFS mounted file system file

Direct NFS
Direct NFS is a new feature introduced with Oracle 11g and  is an optimized NFS (Network File System) client that provides faster and more scalable access to NFS storage located on NAS storage devices (accessible over TCP/IP). Direct NFS is built directly into the database kernel - just like ASM which is mainly used when using DAS or SAN storage.
Direct NFS provides faster performance that what can be provided by the operating system's NFS driver as Oracle bypasses the operating system and generates exactly the requests it needs (no user configuration or tuning required). Data is cached just once in user space, which saves memory (no second copy in kernel space). Performance is further improved by load balancing across multiple network interfaces (if available).
Direct NFS is provided as part of the database kernel, and is thus available on all supported database platforms - even those that don't support NFS natively, like Windows.

ON  Windows

Create the shared folder on some server in the network, set appropriate permissions using share and security tabs after getting the properties of some folder on some drive. Verify that shared folder is accessible

C:\Documents and Settings\inam>dir \\DB1\NFSFolder33
 Volume in drive \\DB1\NFSFolder33 has no label.
 Volume Serial Number is AC19-AA22
Directory of \\DB1\NFSFolder33
10/17/2011  02:49 PM   

          .
10/17/2011  02:49 PM              ..
               0 File(s)              0 bytes
               2 Dir(s)  153,517,715,456 bytes free

 

SQL> CREATE BIGFILE TABLESPACE bigtbs
  2  DATAFILE '\\DB1\NFSFolder33\bigtbs01.dbf' SIZE 10G
  3  /
CREATE BIGFILE TABLESPACE bigtbs
*
ERROR at line 1:
ORA-01119: error in creating database file
'\\DB1\NFSFolder33\bigtbs01.dbf'
ORA-27040: file create error, unable to create file
OSD-04002: unable to open file
O/S-Error: (OS 5) Access is denied.

SQL> CREATE BIGFILE TABLESPACE bigtbs
  2  DATAFILE '\\DB1\NFSFolder33\bigtbs01.dbf' SIZE 10G
  3  /
Tablespace created.
Elapsed: 00:04:50.22

SQL> create user nfs identified by nfs
  2  default tablespace bigtbs
  3  quota unlimited on bigtbs;
User created.
Elapsed: 00:00:00.06
SQL> grant connect ,resource to nfs;
Grant succeeded.
Elapsed: 00:00:00.03
SQL> connect nfs/nfs
Connected.
SQL> create table T(id number);
Table created.
Elapsed: 00:00:00.01
SQL> insert into t values(10);
1 row created.
Elapsed: 00:00:00.03
SQL> commit;
Commit complete.
Elapsed: 00:00:00.00
SQL>

 OBSN: 

i) Using security tab, set the property from allow to Deny, but still user will be able to insert data in the table "T"

ii) Table creation failed as below
SQL> create table t2(c number);
create table t2(c number)
*
ERROR at line 1:
ORA-01116: error in opening database file 9
ORA-01110: data file 9: '\\DB1\NFSFOLDER33\BIGTBS01.DBF'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 5) Access is denied.

 iii) insert/update/delete/commit successful after the error above.

SQL> insert into t values(50);
1 row created.
Elapsed: 00:00:00.00
SQL> commit;
Commit complete.
iv) set the security back as allowed but still creation of table failed.

v) set the "Do not share this folder" from sharing tab, but still Table "T" is being queried/DMLed.

vi)At this point instance crashed and upon restarting got the below error.
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1348508 bytes
Variable Size             188746852 bytes
Database Buffers          339738624 bytes
Redo Buffers                5828608 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 9 - see DBWR trace file
ORA-01110: data file 9: '\\DB1\NFSFOLDER33\BIGTBS01.DBF'
Alert log showed the following:
Mon Oct 17 15:37:52 2011
KCF: write/open error block=0x85 online=1
     file=9 \\DB1\NFSFOLDER33\BIGTBS01.DBF
     error=27070 txt: 'OSD-04016: Error queuing an asynchronous I/O request.
O/S-Error: (OS 64) The specified network name is no longer available.'
Errors in file c:\oracle\prod\db\tech_st\11.1.0\log\diag\rdbms\ocm1\ocm1\trace\ocm1_dbw0_15580.trc:
ORA-01242: data file suffered media failure: database in NOARCHIVELOG mode
ORA-01114: IO error writing block to file 9 (block # 133)
ORA-01110: data file 9: '\\DB1\NFSFOLDER33\BIGTBS01.DBF'
ORA-27070: async read/write failed
OSD-04016: Error queuing an asynchronous I/O request.
O/S-Error: (OS 64) The specified network name is no longer available.
DBW0 (ospid: 15580): terminating the instance due to error 1242
Mon Oct 17 15:37:52 2011
Errors in file c:\oracle\prod\db\tech_st\11.1.0\log\diag\rdbms\ocm1\ocm1\trace
\ocm1_q002_14540.trc:
ORA-01242: data file suffered media failure: database in NOARCHIVELOG mode
Instance terminated by DBW0, pid = 15580
vii) See the point iii , why we don't' get the error while creating the table even if we have taken the privs back from Everyone windows group, because DB has not performed the checkpoint. If you want to sure the scenario then issue the following statement
SQL> alter system checkpoint;  --- at this point instance will crash
alter system checkpoint
*
ERROR at line 1:
ORA-01242: data file suffered media failure: database in NOARCHIVELOG mode

Finding:

I found that when we play with the folder properties , the file properties are also get impacted. After making the setting again on folder , does not make the settings back for the folder. I just changed the properties of the file BIGTBS01.DBF (right click->properties->securityTab), added the everyone group with full control and tried to open the database and it went with success.

SQL> alter database open;
Database altered.
Elapsed: 00:00:03.60
I tested again table creation as below
SQL> conn nfs/nfs
Error accessing PRODUCT_USER_PROFILE
Warning:  Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
Connected.
SQL> create table T2(c number);
Table created.
Elapsed: 00:00:00.10
SQL> insert into t2 values(1);
1 row created.
Elapsed: 00:00:00.00
SQL> commit;
Commit complete.
Elapsed: 00:00:00.00
SQL>

UNIX  (to be tested) 

To enable Oracle Database to use Direct NFS, the NFS file systems must be mounted and available over regular NFS mounts. The mount options used are not relevant, as Direct NFS manages settings after installation.

You can use optional oranfstab (in eighter/etc or to $ORACLE_HOME/dbs.) to specify additional paths for a mount point.   

Direct NFS determines mount point settings to NFS storage devices based on the configurations in /etc/mtab, which are changed with configuring the /etc/fstab file.
Direct NFS searches for mount entries in the following order:

1. $ORACLE_HOME/dbs/oranfstab
2. /etc/oranfstab
3. /etc/mtab

Direct NFS uses the first matching entry found.

Direct NFS can use up to four network paths defined in the oranfstab file for an NFS server. The Direct NFS client performs load balancing across all specified paths. If a specified path fails, then Direct NFS reissues I/O commands over any remaining paths
Complete the following procedure to enable Direct NFS:

1. Create an oranfstab file
2. use an ODM library, libnfsodm11.so, to enable Direct NFS

1. Sample oranfstab file

server:  MyDataServer1
path:  132.34.35.12
path:  132.34.35.13
export: /vol/oradata1 mount: /mnt/oradata1
 
server: MyDataServer2
path:  NfsPath1
path:  NfsPath2
path:  NfsPath3
path:  NfsPath4
export: /vol/oradata2 mount: /mnt/oradata2
export: /vol/oradata3 mount: /mnt/oradata3
export: /vol/oradata4 mount: /mnt/oradata4
export: /vol/oradata5 mount: /mnt/oradata5

2. Steps to use ODM NFS Library.

Change directory to $ORACLE_HOME/lib.
Enter the following commands:  
cp libodm11.so libodm11.so_stub
ln -s libnfsodm11.so libodm11.so