11G  orcle RAC INSTALLATION STEPS.....

  PRE-REQUESTES


**************************11G R2 ORACLE RAC INSTALLATION*********************

NOTE : Each step run in Each node.

STEP 1: Creating the required no.of groups
_______


# userdel -r oracle

groupdel  oinstall

groupdel dba

groupadd -g  501  oinstall

groupadd -g  502   dba

groupadd -g   503  oper

groupadd -g   504   asmadmin

groupadd  -g  506   asmdba

groupadd  -g  507   asmoper


useradd -u 501 -g oinstall  -G asmadmin,asmdba,asmoper  grid

useradd  -u 502   -g oinstall  -G  dba,asmdba   oracle


passwd  grid

passwd   oracle


id oracle


id  grid




step 2 : Creating a direcotry structure for grid based
_______

#] mkdir -p  /u01/app/grid

chown -R  grid:oinstall  /u01/app/grid/

chmod -R  775     /u01/app/grid/


step 3: Creating a directory for grid home
______

(grid base should not be under grid home)

#]mkdir -p  /u01/product/11.2.0/grid_home

chown  -R  grid:oinstall  /u01/product/11.2.0/grid_home/

chmod  -R  775  /u01/product/11.2.0/grid_home/


step 4: creating  a directory for oracle base
______

#] mkdir -p  /u01/app/oracle

chown  -R  oracle:oinstall  /u01/app/oracle

chmod -R  775   /u01/app/oracle


step 5: creating directory structure for oracle home
______

#]mkdir  -p  /u01/app/oracle/product/11.2.0/db_home

chown -R  oracle:oinstall /u01/app/oracle/product/11.2.0/db_home/

chmod -R  775  /u01/app/oracle/product/11.2.0/db_home/


step 6: creating directory for oracle inventory
_____

#] mkdir -p /u01/app/oraInventory

chown -R  grid:oinstall /u01/app/oraInventory

chmod -R  775  /u01/app/oraInventory


step 7: Configure Kernel parameters
_______

(in OEL is n't Recomended but in other
  flavors of operating system we need configure as for installation doc)



step  8:  set shell limits for oracle user accoutn
_______

#]vi /etc/security/limits.conf

  insert bellow
   |
   ^
  oracle harad stack  32768

  (bellow)
   
 grid  soft nproc  2047

 grid  hard nproc  16384

 grid  soft nofile  1024

 grid  hard  nofile  65536


(scp this file to another node)

 scp  limits.conf   lnx02:/etc/security/limits.conf






step 9: configuring /etc/hosts
_______

(if DNS server is not recommended to configure otherwise we
 need to place the following entries)


 #] vi /etc/hosts


 (d'nt change local host)

 #############################DNS SERVER###############

192.168.233.244  dns  dns.oracle.com

#############################public ips###############

192.168.233.40   lnx01  lnx01.oracle.com

192.168.233.41    lnx02   lnx02.oracle.com

########################priv ips#####################

192.168.233.50     lnx01-priv

192.168.233.51      lnx02-priv

############################vips#########################

192.168.233.60    lnx01-vip

192.168.233.61     lnx02-vip


##########################scan ips#########################

192.168.233.101     cluster_scan

192.168.233.102     cluster_scan

192.168.233.103     cluster_scan


( scp to another node)


step 10: create required no.of partitions
______



#partprobe
#partprobe (execute same steps another node)


step 11: downaload and install asm related & storage related rpms
_______

#] cd /opt
opt]     rpms ( cvqudisk , asm*)

step 12: configuring asm lib
_______

  #]oracleasm configure -i

   ]oracleasm init
   

(execute two steps into another node)


step 13: labeling disk for asm usage
_______

 #]oracleasm   createdisk   VOL1   /dev/sdb5

 #]oracleasm   createdisk   VOL2   /dev/sdb6

 #]oracleasm   createdisk   VOL3   /dev/sdb7

 #]oracleasm  scandisks

 #]oracleasm   listdisks

 (execute same steps on second node also)


STEP 14 :
_________
        Install grid.

step 15 :
_________
         Install oracle.

  (NXT POST I WILL UPDATE WITH pictures installation of GRID and ORACLE S/W ).

STEPS TO FIND A BLOCKING SESSIONS



Written By :- Harish Ramakkagari

Tested By  :- Harish Ramakkagari 

Contact :-  harish.ramakkagari@attra.com.au   +919591792886  or harishr227@gmail.com  +918880598503




Steps:-


   1) select instance_name||' - '||status||' - '||startup_time||' - '||host_name||' - '||sysdate from gv$instance;

   2) select sid from gv$lock where block=1;

   3) select sql_text from gv$sqltext where hash_value=(select prev_hash_value from gv$session where sid=&sid);
   
   4) select sid||' - '||serial#||' - '||osuser||' - '||username||' - '||machine||' - '||status||' - '||logon_time
      from gv$session where sid=&sid;

   5)set pagesize 100
     select decode(request,0,'Holder: ','Waiter: ')||sid sess, id1, id2, lmode, request, type from gv$lock
     where (id1, id2, type) IN (SELECT id1, id2, type from gv$lock where request>0) ORDER BY id1, request;
      
      (Hear we identify Holders & waiters)


   6) alter system kill session '&sid,&serial' immediate;

TO FIND A DEAD LOCKS 

Written By : Harish Ramakkagari

Purpose : To Detect Application Dead Locks.

Tested By : Harish Ramakkagari

Contact : harish.ramakkagari@attra.com.au  +919591792886  or harishr227@gmail.com   +918880598503



What is Dead Lock ?
 
   Suppose there are two friends A and B. A has an apple and want a mango and B has a mango and wants apple,
   and none of them is ready to leave what they have and want what other have. Now, this  is called as Deadlock.
   Same happens in case of Database Sessions, One session holds lock on an row and want to another,
   one which is hold by second one and both need others rows which cause a deadlock.

Steps to Reproduce Application Deadlock :
-----------------------------------------
   I opened two database session from same database user scott and update same row in both sessions,
   without commit and generate a dead lock situation.

  Session #1
  ----------
   SQL> desc dept;

   Name                                      Null?    Type
   ----------------------------------------- -------- ----------------------------
   DEPTNO                                    NOT NULL NUMBER(2)
   DNAME                                              VARCHAR2(14)
   LOC                                                VARCHAR2(13)

SQL> select deptno,dname from dept;
 
DEPTNO DNAME
---------- --------------
        10 ACCOUNTING
        20 RESEARCH
        30 SALES
        40 OPERATIONS

SQL> update dept set dname='operation' where deptno=40;
1 row updated.


  Session #2
  -----------

SQL> update dept set dname='sale' where deptno=30;
1 row updated.

SQL> update dept set dname='operation_new' where deptno=40;

     Above update statement is hang and not responding has generated a Deadlock.
     DBA cross check alert log file of database which is not showing any error message
     like 'ORA-0060 Deadlock Detected'.

 Steps to Resolve Apllication Deadlock:
 --------------------------------------
    1. Find Dead lock Session:
         
       SQL>COLUMN username FORMAT a10;
       SQL>COLUMN WAIT_CLASS FORMAT a15;
       SQL>set linesize 200
       SQL> select sid, username,command,status,program,sql_id,
            BLOCKING_INSTANCE,BLOCKING_SESSION,WAIT_CLASS from gv$session where BLOCKING_SESSION is not null;

        (Here we find from BLOCKING_INSTANCE and BLOCKING_SESSION values)

   2. Blocked Session Details
     
      QL> select * from v$sqltext where sql_id='2rbwgj3zdsnus';


   3. Blocking Session Details :
     
      SQL>select sid,username,command,status,program,sql_id,BLOCKING_INSTANCE,BLOCKING_SESSION,
          WAIT_CLASS from gv$session where sid=34;
 
     
      SQL> select * from v$sqltext where sql_id='17d40vwcct4g6';

   4. Find the sql statment from blocking session

       SQL>select SQL_ID,SQL_OPNAME,PLSQL_OBJECT_ID from v$active_session_history where SESSION_ID='34';
   5.  SQL> select * from v$sqltext where sql_id in ('dtbhjabjx3v1u','g4y6nw3tts7cc');

             (Database Administrator finds an update command on same table in this result.
              Here the culprit command  is "update dept set dname=:"SYS_B_0" where deptno=:"SYS_B_1".
              Session 34 executes this command which was causing session 47 to hang.
              Now next step to find out user details.)
 
   6.  Find which user is executing Clurpit command.
           
      SQL> select sid,username,command,status,program,sql_id,TERMINAL,PROGRAM from gv$session where sid=34;

   7. Finall Solution is
        DBA has to ask either network admin to find user having terminal,
        pts/1 or he has to directly ask user to either commit or terminate his session.
        I execute commit in session 1 and it automatically release locks and session 2 also got his row updated.
   8.

       Session #1
       SQL> commit;
       Commit complete.

       Session #2
       SQL> update dept set dname='operation_new' where deptno=40;
       1 row updated.
      SQL>

       Let's cross check with v$session view.
     
      SQL>select sid,username,command,status,program,sql_id,BLOCKING_INSTANCE,BLOCKING_SESSION,
          WAIT_CLASS from gv$session where BLOCKING_SESSION is not null;
       
          no rows selected.
     
        Note :
             
            The problem is solved now. Though this is a small test case, you might face different scenario,
            but for situation where a particular user session is not performing well.
            This could be the initial approach to investigate.
            If, This doesn't help to troubleshoot, you can generate AWR Report and Analyze for any other blocking events.