Oracle Interconnect RAC

Cluster Private Interconnect

The cluster interconnect is a high bandwidth, low latency communication facility that connects each node to other nodes in the cluster and routes messages among the nodes. It is a key component in building the RAC system.

In case of RAC database, the cluster interconnect is used for the following high-level functions:

Monitoring Health, Status, and Synchronize messages
Transporting lock management or resource coordination messages
Moving the Cache Buffers (data blocks) from node to node.

High performance database computing involves distributing the processing across an array of cluster nodes. It requires that the cluster interconnect provide high-data rates and low-latency communication between node processes.

Here's a few ways to find information about interconnect and troubleshoot any issues...

1. select * from gv$cluster_interconnects ;

2. Using the clusterware command oifcfg:
$ oifcfg getif
eth2 100.100.90.0 global public
eth0 192.168.10.0 global cluster_interconnect
eth1 192.168.11.0 global cluster_interconnect

3. Using oradebug ipc:
sqlplus “/ as sysdba”
SQL> oradebug setmypid
SQL> oradebug ipc Information written to trace file.

The above command would dump a trace to user_dump_dest. The last few lines of the trace would indicate the IP of the cluster interconnect. Below is a sample output.
From the trace file on node1:

SSKGXPT 0×5edf558 flags SSKGXPT_READPENDING socket no 9 IP 192.168.11.1 UDP 18852
From the trace file on node2:

SSKGXPT 0×5edf558 flags SSKGXPT_READPENDING socket no 9 IP 192.168.10.2 UDP 38967

Who is Locking my Object ?

Find out using this script....

SELECT a.sid,a.serial#, a.username,c.os_user_name,a.terminal,c.process,
b.object_id,substr(b.object_name,1,40) object_name
from v$session a, dba_objects b, v$locked_object c
where a.sid = c.session_id
and b.object_id = c.object_id
/

How to find Database Import Speed

SELECT
SUBSTR(sql_text, INSTR(sql_text,'INTO "'),30) table_name
, rows_processed
, ROUND( (sysdate-TO_DATE(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes
, TRUNC(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_minute
FROM
sys.v_$sqlarea
WHERE
sql_text like 'INSERT %INTO "%'
AND command_type = 2
AND open_versions > 0;

Oracle Security White Papers

Nice Reading about Oracle Security

www.ngssoftware.com/research/papers

How to find "user commits" during a user session

You can find number of "user commits" during a particular user session. Just get the SID of the user for which you want to track the number of commits.


set lines 120
set pages 1000
col name format a25
select a.*,b.* from sys.v_$sesstat a, sys.v_$statname b
where a.STATISTIC#=b.STATISTIC#
and b.name like '%user%'
and a.sid=&input_sid
/

Basic VCS Commands

BASIC VCS COMMANDS

SERVICE GROUPS AND RESOURCE OPERATIONS

Configuring service groups hagrp –add|-delete|-online|-offline group_name
Modifying resources hares –add|-delete res_name type group
hares –online|-offline res_name –sys system_name
Modifying agents haagent –start|-stop agent_name –sys system_name
BASIC CONFIGURATION OPERATIONS
Service Goups hagrp -modify group_name attribute_name value
hagrp –list group_name
hagrp –value attribute_name
hares -modify res_name attribute_name value
hares -link res_name res_name
Agents haagent -display agent_name –sys system_name
hatype –modify
VCS ENGINE OPERATIONS
Starting had hastart –force|–stale system_name
hasys –force system_name
Stopping had hastop –local|-all|-force|-evacuate
hastop –sys system_name
Adding Users hauser –add user_name
STATUS AND VERIFICATION
Group Status/Verification hagrp -display group_name|–state|–resource group_name
Resources Status/Verification hares -display res_name
hares –list
hares -probe res_name –sys system_name
Agents Status/Verification haagent –list
haagent -display agent_name –sys system_name
ps –ef|grep agent_name
VCS Status hastatus –group
LLT Status/Verification lltconfig –a list
lltstat|lltshow|lltdump
GAB Status/Verification gabconfig –a
gabdiskhb –l
COMMUNICATION
Starting and Stopping LLT lltconfig –c|U
Starting and Stopping GAB gabconfig –c –n #seed number
gabconfig –U
ADMINISTERATION
Administering Group Services hagrp –clear|-flush|-switch group_name –sys system_name
Administering Resources hares –clear|-probe res_name –sys system_name
Administering Agents haagent -list
haagent -display agent_name –sys system_name
Verify Configuration hacf –verify

How to find name of your cluster in RAC environment

To find out the value of your CLUSTER_NAME from CRS (OCR), do the following from the CRS_HOME:

$CRS_HOME/bin/cemutlo -n

==>> OR

cd $CRS_HOME/bin
./ocrdump

=> this will create a text file called OCRDUMPFILE
open that file and look for this entry
[SYSTEM.css.clustername]
ORATEXT : crs_cluster

In this case, "crs_cluster" is the cluster name.