NFS Configuration for Oracle RAC

Today I was going to Install/configure Oracle 11gR2 RAC without using shared storage on Red Hat Enterprise Linux. I had read about that before but never tested so I tried and failed first time but after some re-configuration, I was able to configure clusterware and was able to store OCR Voting and database files on NFS mount points.

Step 1
=========
On First node e.g. RAC node1, create the following directory. You can use any directory as your NFS source mount point.

mkdir /u01/shared_config
chown -R oracle:oinstall /u01/shared_config

Step 2
=========
Add the following lines to the /etc/exports file.

vi /etc/exports
------------------------------------------------
/u01/shared_config  *(rw,sync,no_wdelay,insecure_locks,no_root_squash)

Step 3
========
Run the following command to export the NFS shares.
Check that the NFS server will get started during boot of this server.

chkconfig --level 345 nfs on

Now, start the NFS server process on the NFS server.
service nfs start

Check, if the directory is exported correctly by issuing the exportfs –v command:
/usr/sbin/exportfs -v

Step 4
========
On both node1 and node2 create the directories in which the Oracle configuration files will be created.

mkdir /shared_config
chown -R oracle:oinstall /shared_config
chmod -R 775 /shared_config

Step 5
=======
Add the following lines to the "/etc/fstab" file.
---------------------------------------------------------------
racnode1:/u01/shared_config /shared_config  nfs  rw,bg,hard,nointr,tcp,vers=3,timeo=600,rsize=32768,wsize=32768,actimeo=0  0 0

Note: I used following Oracle recommended settings for nfs share and at the end my ocr and voting disks configuration process was failed:
racnode1:/u01/shared_config /shared_config  nfs  rw,bg,hard,intr,rsize=32768,wsize=32768,tcp,noac,vers=3,timeo=600 0 0

Step 6
=======
Mount the NFS shares on all servers.

mount /shared_config

Make sure the permissions on the shared directories are correct i.e oracle.
Check, if the NFS export is correctly mounted with the mount command.
# mount
It will give you output like this:
esidbapoc01:/sharedconfig on /shared_config type nfs (rw,bg,hard,nointr,tcp,nfsvers=3,timeo=600,rsize=32768,wsize=32768,actimeo=0,addr=192.168.1.3)

Hope this will help…………

Tracking & auditing changes in init.ora parameters

Auditing changes to init.ora parameters (via pfile or spfile) is an important DBA task.  Sometimes, users which have “alter system” privilege can make unauthorized changes to the initialization parameters in the spfile on a production database.  Hence, auditing changes to parameters is a critical DBA task.  Fortunately, it's quite simple to audit these changes by implementing the audit_sys_operations=true.

Here is a method to track changes to the initialization parameters.  In order to track all changes to parameters we can use audit for the alter system statement for any specific user

We should follow below steps to track changes to init.ora parameters:

1. ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;
2. SHUTDOWN IMMEDIATE
3. STARTUP
4. CREATE USER TEST IDENTIFIED BY TEST;
5. GRANT DBA TO TEST;
6. AUDIT ALTER SYSTEM BY test;
7. CONN TEST/TEST
8. ALTER SYSTEM SET AUDIT_TRAIL=db SCOPE=SPFILE;

9.    Create an alert script to notify the DBA when a parameter has changed.

Let's start by finding the action_name in the dba_audit_trail view for the alter system command:

SQL> select username, timestamp, action_name from dba_audit_trail;

USERNAME                       TIMESTAMP  ACTION_NAME
------------------------------ -------------   ----------------------------
TEST                               29-MAY-09    ALTER SYSTEM

STEP 1 - We can track changes made by SYS user by setting audit_sys_operations parameter to TRUE.

SQL> alter system set audit_sys_operations=true scope=spfile;
System altered.

STEP 2 - Next, we bounce the instance to make the change take effect:
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 92276304 bytes
Database Buffers 188743680 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.

Here we see our auditing parameters:
SQL> show parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /home/oracle/oracle/product/10 .2.0/db_1/admin/fkhalid/adump
audit_sys_operations boolean TRUE
audit_syslog_level string
audit_trail string DB

SQL> alter system set audit_trail=db scope=spfile;
System altered.

STEP 3 - Here we go to the adump directory and examine the audit files:
SQL> host
[oracle@localhost bin]$ cd /home/oracle/oracle/product/10.2.0/db_1/admin/kam/adump/

[oracle@localhost adump]$ ls
ora_5449.aud ora_5476.aud ora_5477.aud ora_5548.aud ora_5575.aud ora_5576.aud

[oracle@localhost adump]$ cat ora_5576.aud
Audit file /home/oracle/oracle/product/10.2.0/db_1/admin/kam/adump/ora_5576.aud
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /home/oracle/oracle/product/10.2.0/db_1/
System name:    Linux
Node name:      localhost.localdomain
Release:        2.6.18-92.el5
Version:        #1 SMP Tue Jun 10 18:49:47 EDT 2008
Machine:        i686
Instance name: kam
Redo thread mounted by this instance: 1
Oracle process number: 15
Unix process pid: 5576, image: oracle@localhost.localdomain (TNS V1-V3)
Fri May 29 02:38:30 2009
ACTION : 'alter system set audit_trail=db scope=spfile'
DATABASE USER: '/'
PRIVILEGE : SYSDBA
CLIENT USER: oracle
CLIENT TERMINAL: pts/2
STATUS: 0

STEP 4 - Now, create a crontab job to seek new entries in the adump directory. 
#******************************************************
# list the full-names of all possible adump files . . . .
#******************************************************
rm -f /tmp/audit_list.lst
find $DBA/$ORACLE_SID/adump/*.trc -mtime -1 -print >> /tmp/audit_list.lst
STEP 5 - When found, send the DBA an e-mail:
# If initialization paramneter has changed, send an e-mail
if [ -f /tmp/audit_list.lst]; then
then
    # Now, be sure that we don't clog the mailbox.
    # the following statement checks to look for existing mail,
    # and only sends mail when mailbox is empty . . . 
    if [ ! -s /var/spool/mail/oramy_sid ]
    then
       cat /oracle/MY_SID/scripts/oracheck.log | mail oramy_sid 
    fi
    sendmail  . . . 
fi

Please beware that using the auditing command imposes additional work on the production database.

Cheers!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!