11gR2 Clusterware Startup Sequence (RAC)

This description is also from the MOS note stated above

Short summary of the startup sequence: INIT spawns init.ohasd (with respawn) which in turn starts the OHASD process (Oracle High Availability Services Daemon).  This daemon spawns 4 processes.

Level 1: OHASD Spawns:

    cssdagent         - Agent responsible for spawning CSSD.
    orarootagent     - Agent responsible for managing all root owned ohasd resources.
    oraagent         - Agent responsible for managing all oracle owned ohasd resources.
    cssdmonitor        - Monitors CSSD and node health (along wth the cssdagent).
   

Level 2: OHASD rootagent spawns:
    CSDD (ora.cssd)     - Cluster Synchronization Services
    CRSD(ora.crsd)     - Primary daemon responsible for managing cluster resources.
    CTSSD(ora.ctssd)     - Cluster Time Synchronization Services Daemon
    Diskmon(ora.diskmon)
    ACFS (ASM Cluster File System) Drivers

Level 2: OHASD oraagent spawns:

    MDNSD(ora.mdnsd)     - Used for DNS lookup
    GIPCD(ora.gipcd)     - Used for inter-process and inter-node communication
    GPNPD(ora.gpnpd)     - Grid Plug & Play Profile Daemon
    EVMD(ora.evmd)     - Event Monitor Daemon
    ASM(ora.asm)     - Resource for monitoring ASM instances

Level 3: CRSD spawns:

    orarootagent     - Agent responsible for managing all root owned crsd resources.
    oraagent         - Agent responsible for managing all oracle owned crsd resources.

Level 4: CRSD rootagent spawns:

    Network resource     - To monitor the public network
    SCAN VIP(s)     - Single Client Access Name Virtual IPs
    Node VIPs         - One per node
    ACFS Registery     - For mounting ASM Cluster File System
    GNS VIP (optional)     - VIP for GNS

Level 4: CRSD oraagent spawns:

    ASM Resouce     - ASM Instance(s) resource
    Diskgroup         - Used for managing/monitoring ASM diskgroups.
    DB Resource     - Used for monitoring and managing the DB and instances
    SCAN Listener     - Listener for single client access name, listening on SCAN VIP
    Listener         - Node listener listening on the Node VIP
    Services         - Used for monitoring and managing services
    ONS         - Oracle Notification Service
    eONS         - Enhanced Oracle Notification Service
    GSD         - For 9i backward compatibility
    GNS (optional)     - Grid Naming Service - Performs name resolution

How to create BITMAP index in Oracle 11G

How to create BITMAP index in Oracle 11G

Example - Create bitmap index on the historyABC column of the PO table

SYNTEX -  

CREATE BITMAP INDEX 
emp_bitmap_idx
ON index_demo (gender);


SQL>CREATE BITMAP INDEX  PO_HISTORYFLAG_IDX  ON MXPRE.PO(historyABC);

SQL>execdbms_stats.gather_index_stats(OWNNAME=>'SYS',INDNAME=>'PO_HISTORYFLAG_IDX'); 

How to Check the Locked Object details

Lock related queries :

SELECT XIDUSN,OBJECT_ID,SESSION_ID,ORACLE_USERNAME,OS_USER_NAME,PROCESS from gv$locked_object;

SELECT d.OBJECT_ID, substr(OBJECT_NAME,1,20), l.SESSION_ID, l.ORACLE_USERNAME, l.LOCKED_MODE

from   v$locked_object l, dba_objects d

where  d.OBJECT_ID=l.OBJECT_ID;

SELECT ADDR, KADDR, SID, TYPE, ID1, ID2, LMODE, BLOCK from gv$lock;

SELECT a.sid, a.saddr, b.ses_addr, a.username, b.xidusn, b.used_urec, b.used_ublk

FROM   v$session a, gv$transaction b

WHERE  a.saddr = b.ses_addr;

SELECT s.sid, l.lmode, l.block, substr(s.username, 1, 10), substr(s.schemaname, 1, 10), 

       substr(s.osuser, 1, 10), substr(s.program, 1, 30), s.command

FROM   v$session s, v$lock l

WHERE  s.sid=l.sid;

SELECT  p.spid, s.sid, p.addr,s.paddr,substr(s.username, 1, 10), substr(s.schemaname, 1, 10), 

        s.command,substr(s.osuser, 1, 10), substr(s.machine, 1, 10) 

FROM    v$session s, v$process p

WHERE   s.paddr=p.addr

SELECT sid, serial#, command,substr(username, 1, 10), osuser, sql_address,LOCKWAIT, 

       to_char(logon_time, 'DD-MM-YYYY;HH24:MI'), substr(program, 1, 30)

FROM   v$session;

SELECT sid, serial#,  username, LOCKWAIT from v$session;

SELECT v.SID, v.BLOCK_GETS, v.BLOCK_CHANGES, w.USERNAME, w.OSUSER, w.TERMINAL

FROM   v$sess_io v, V$session w

WHERE  v.SID=w.SID ORDER BY v.SID;

SELECT * from dba_waiters;

SELECT waiting_session, holding_session, lock_type, mode_held

FROM   dba_waiters;

SELECT 

  p.spid                      unix_spid,

  s.sid                       sid, 

  p.addr,

  s.paddr,

  substr(s.username, 1, 10)   username, 

  substr(s.schemaname, 1, 10) schemaname, 

  s.command                   command,

  substr(s.osuser, 1, 10)     osuser, 

  substr(s.machine, 1, 25)    machine

FROM   v$session s, v$process p

WHERE  s.paddr=p.addr

ORDER BY p.spid;

ref -http://www.dbatodba.com/oracle/how-to-check-the-locked-object-details/

TOP 10 SQL STATEMENTS WITH LARGE NO. OF DISK READS

set pagesize 1000
col username format a10
col terminal format a10
col program format a15
col sql_text format a30
select a.username, a.terminal, a.program,b.sql_text from v$session a, v$sqltext b where a.sql_hash_value = b.hash_value;

Script to kill the ACTIVE/INACTIVE session in RAC

Check the INACTIVE /ACTIVE Sessions 

select inst_id,sid,serial#,username,status,last_call_et,program,LOGON_TIME,SQL_EXEC_START,machine,SQL_ID from gv$session where nvl(trim(username),'SYS') not in ('SYS','PUBLIC','DBSNMP') and status='INACTIVE' and type!='BACKGROUND' and TRUNC(logon_time)<=to_date('17-JAN-2018','DD-MON-YYYY')
order by LOGON_TIME; 



Creating the script to kill the INACTIVE /ACTIVE session

select 'alter system kill session ''' || sid || ',' || serial# ||',@' || inst_id || ''' immediate;' From gv$session s where nvl(trim(s.username),'SYS') not in ('SYS','PUBLIC','DBSNMP') and s.status='ACTIVE' and type!='BACKGROUND' and TRUNC(logon_time)<=to_date('26-JAN-2018','DD-MON-YYYY')
order by LOGON_TIME;