CRSCTL command for RAC Cluster Service

crs_stat	crsctl check cluster -all crsctl stat res -t
crs_register	crsctl add resource crsctl add type crsctl modify resource crsctl modify type
crs_unregister	crsctl stop resource crsctl modify resource resource_name -attr "AUTO_START=never"
crs_start	crsctl start resource crsctl start crs crsctl start cluster
crs_stop	crsctl stop resource crsctl stop crs crsctl stop cluster
crs_getperm	crsctl getperm resource crsctl getperm type
crs_profile	crsctl add resource crsctl add type crsctl status resource crsctl status type crsctl modify resource crsctl modify type
crs_relocate	crsctl relocate resource
crs_setperm	crsctl setperm resource crsctl setperm type
crsctl check crsd	crsctl check crs
crsctl check cssd	crsctl check css
crsctl check evmd	crsctl check evm
crsctl debug res log resource_name:level	crsctl set log
crsctl set css votedisk	crsctl add css votedisk crsctl delete css votedisk crsctl query css votedisk crsctl replace css votedisk
crsctl start resources	crsctl start resource -all
crsctl stop resources	crsctl stop resource -all http://docs.oracle.com/cd/E11882_01/rac.112/e16794/crsref.htm#autoId61

Unix IPCS and Oracle Example

http://www.thegeekstuff.com/2010/08/ipcs-command-examples/

query For No of sql_id execution ans Hash Value in Oracle

set lines 155

col execs for 999,999,999
col avg_etime for 999,999.999
col avg_lio for 999,999,999.9
col begin_interval_time for a30
col node for 99999
break on plan_hash_value on startup_time skip 1

select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,nvl(executions_delta,0) execs,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = nvl('&sql_id','gzxwajs4zqpmb')
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
order by 1, 2, 3
/
select DBMS_SQLTUNE.REPORT_SQL_MONITOR(type=>'HTML',report_level=>'ALL',sql_id=>'gzxwajs4zqpmb') as report FROM dual;

SRVCTL Command Help in Oracle

gdcbs320%%=====$CRS_HOME/bin/srvctl -h


Usage: srvctl [-V]

Usage: srvctl add database -d -o [-m ] [-p ] [-A ip>/netmask] [-r {PRIMARY
PHYSICAL_STANDBY
LOGICAL_STANDBY}] [-s ] [-n ] [-y {AUTOMATIC
MANUAL}]

Usage: srvctl add instance -d -i -n

Usage: srvctl add service -d -s -r "" [-a ""] [-P ]

Usage: srvctl add service -d -s -u {-r ""
-a ""}

Usage: srvctl add nodeapps -n -o -A ip>/netmask[/if1[
if2
...]]

Usage: srvctl add asm -n -i -o [-p ]

Usage: srvctl config database

Usage: srvctl config database -d [-a] [-t]

Usage: srvctl config service -d [-s ] [-a] [-S ]

Usage: srvctl config nodeapps -n [-a] [-g] [-o] [-s] [-l]

Usage: srvctl config asm -n

Usage: srvctl config listener -n

Usage: srvctl disable database -d

Usage: srvctl disable instance -d -i ""

Usage: srvctl disable service -d -s "" [-i ]

Usage: srvctl disable asm -n [-i ]

Usage: srvctl enable database -d

Usage: srvctl enable instance -d -i ""

Usage: srvctl enable service -d -s "" [-i ]

Usage: srvctl enable asm -n [-i ]

Usage: srvctl getenv database -d [-t ""]

Usage: srvctl getenv instance -d -i [-t ""]

Usage: srvctl getenv service -d -s [-t ""]

Usage: srvctl getenv nodeapps -n [-t ""]

Usage: srvctl modify database -d [-n ] [-m ] [-p ] [-r {PRIMARY
PHYSICAL_STANDBY
LOGICAL_STANDBY}] [-s ] [-y {AUTOMATIC
MANUAL}]

Usage: srvctl modify instance -d -i -n

Usage: srvctl modify instance -d -i {-s
-r}

Usage: srvctl modify service -d -s -i -t [-f]

Usage: srvctl modify service -d -s -i -r [-f]

Usage: srvctl modify service -d -s -n -i [-a ] [-f]

Usage: srvctl modify asm -n -i [-o ] [-p ]

Usage: srvctl relocate service -d -s -i -t [-f]

Usage: srvctl remove database -d [-f]

Usage: srvctl remove instance -d -i [-f]

Usage: srvctl remove service -d -s [-i ] [-f]

Usage: srvctl remove nodeapps -n "" [-f]

Usage: srvctl remove asm -n [-i ] [-f]

Usage: srvctl setenv database -d {-t =[,=,...]
-T =}

Usage: srvctl setenv instance -d [-i ] {-t "=[,=,...]"
-T "="}

Usage: srvctl setenv service -d [-s ] {-t "=[,=,...]"
-T "="}

Usage: srvctl setenv nodeapps -n {-t "=[,=,...]"
-T "="}

Usage: srvctl start database -d [-o ]

Usage: srvctl start instance -d -i "" [-o ]

Usage: srvctl start service -d [-s "" [-i ]] [-o ]

Usage: srvctl start nodeapps -n

Usage: srvctl start asm -n [-i ] [-o ]

Usage: srvctl start listener -n [-l ]

Usage: srvctl status database -d [-f] [-v] [-S ]

Usage: srvctl status instance -d -i "" [-f] [-v] [-S ]

Usage: srvctl status service -d [-s ""] [-f] [-v] [-S ]

Usage: srvctl status nodeapps -n

Usage: srvctl status asm -n

Usage: srvctl stop database -d [-o ]

Usage: srvctl stop instance -d -i "" [-o ]

Usage: srvctl stop service -d [-s "" [-i ]] [-f]

Usage: srvctl stop nodeapps -n [-r]

Usage: srvctl stop asm -n [-i ] [-o ]

Usage: srvctl stop listener -n [-l ]

Usage: srvctl unsetenv database -d -t ""

Usage: srvctl unsetenv instance -d [-i ] -t ""

Usage: srvctl unsetenv service -d [-s ] -t ""

Usage: srvctl unsetenv nodeapps -n -t ""

Find Session Generating a lot of REDO in Oracle

Archive logs [ID 167492.1]


To find sessions generating lots of redo, you can use either of the following

methods. Both methods examine the amount of undo generated. When a transaction

generates undo, it will automatically generate redo as well.



The methods are:

1) Query V$SESS_IO. This view contains the column BLOCK_CHANGES which indicates

how much blocks have been changed by the session. High values indicate a

session generating lots of redo.



The query you can use is:

SQL> SELECT s.sid, s.serial#, s.username, s.program,

2 i.block_changes

3 FROM v$session s, v$sess_io i

4 WHERE s.sid = i.sid

5 ORDER BY 5 desc, 1, 2, 3, 4;



Run the query multiple times and examine the delta between each occurrence

of BLOCK_CHANGES. Large deltas indicate high redo generation by the session.



2) Query V$TRANSACTION. This view contains information about the amount of

undo blocks and undo records accessed by the transaction (as found in the

USED_UBLK and USED_UREC columns).



The query you can use is:

SQL> SELECT s.sid, s.serial#, s.username, s.program,

2 t.used_ublk, t.used_urec

3 FROM v$session s, v$transaction t

4 WHERE s.taddr = t.addr

5 ORDER BY 5 desc, 6 desc, 1, 2, 3, 4;



Run the query multiple times and examine the delta between each occurrence

of USED_UBLK and USED_UREC. Large deltas indicate high redo generation by

the session.



You use the first query when you need to check for programs generating lots of

redo when these programs activate more than one transaction. The latter query

can be used to find out which particular transactions are generating redo.