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.

RAC Views

Advanced Queuing

------------------------
gv$aq gv$persistent_publishers gv$qmon_server_stats

gv$aq1 gv$persistent_qmn_cache gv$qmon_task_stats

gv$buffered_publishers gv$persistent_queues gv$qmon_tasks

gv$buffered_queues gv$persistent_subscribers gv$queue

gv$buffered_subscribers gv$qmon_coordinator_stats gv$queueing_mth


Advisors

gv$advisor_current_sqlplan gv$pga_target_advice

gv$advisor_progress gv$pga_target_advice_histogram

gv$java_pool_advice gv$px_buffer_advice

gv$mttr_target_advice


Archive Logging
==========
gv$archive v$proxy_archivelog_details

gv$archive_dest v$proxy_archivelog_summary

gv$archive_dest_status

gv$archive_gap

gv$archive_processes

gv$archived_log

gv$proxy_archivedlog

ASH
======
gv$ash_info gv$active_session_history gv$max_active_sess_target_mth

ASM
====
gv$asm_acfs_encryption_info gv$asm_disk gv$asm_operation

gv$asm_acfs_security_info gv$asm_disk_iostat gv$asm_template

gv$asm_acfssnapshots gv$asm_disk_stat gv$asm_user

gv$asm_acfsvolumes gv$asm_diskgroup gv$asm_usergroup

gv$asm_alias gv$asm_diskgroup_stat gv$asm_usergroup_member

gv$asm_attribute gv$asm_file gv$asm_volume

gv$asm_client gv$asm_filesystem gv$asm_volume_stat


Backup and Recovery (RMAN)
=======================

gv$backup v$backup_archivelog_details

gv$backup_async_io v$backup_archivelog_summary

gv$backup_datafile v$backup_controlfile_details

gv$backup_device v$backup_controlfile_summary

gv$backup_piece v$backup_copy_details

gv$backup_redolog v$backup_copy_summary

gv$backup_set v$backup_corruption

gv$backup_spfile v$backup_datafile_details

gv$backup_sync_io v$backup_datafile_summary

gv$database_incarnation v$backup_files

gv$datafile_copy v$backup_piece_details

gv$db_transportable_platform v$backup_set_details

gv$deleted_object v$backup_set_summary

gv$fast_start_servers v$backup_spfile_details

gv$fast_start_transactions v$backup_spfile_summary

gv$instance_recovery v$block_change_tracking

gv$proxy_datafile gv$copy_corruption

gv$recover_file gv$database_block_corruption

gv$recovery_area_usage v$proxy_copy_details

gv$recovery_file_status v$proxy_copy_summary

gv$recovery_log v$recovery_file_dest

gv$recovery_progress v$rman_backup_job_details

gv$recovery_status v$rman_backup_subjob_details

gv$rman_output v$rman_backup_type

gv$tsm_sessions v$rman_compression_algorithm

v$rman_configuration

v$rman_encryption_algorithms

v$rman_status

v$unusable_backupfile_details

RAC query

http://www.morganslibrary.org/reference/rac.html

http://www.morganslibrary.org/reference/kill_session.html