prWaits.ksh

--------------

#!/bin/ksh
#
# prWaits.ksh gathers wait event data for the MRP (managed recovery process) and PR0n (parallel recovery slaves).
# The output is written to one file in the following format: __.
# By default the view data is sampled every 15 seconds.
#
if [ $# -eq 0 ]; then
  $0 5        # Run every 5 secs
elif [ $# -ne 1 ]; then
  echo "Usage: $0 "
  exit 1
fi
#
run_interval=$1
#
while [ 1 ]; do
sqlplus -s '/ as sysdba' << eof >> prWaits_`uname -n`_${ORACLE_SID}
set lines 200 pages 2000
col process format a8
col spid format a8
col event format a50 tru
col SIW format 999999
select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') "Current time"
       ,s.process
       , p.spid
       , substr(s.program, -6) PROC
       , s.event
       , s.p1
       , s.p2
       , s.p3
       , s.seconds_in_wait SIW
       , s.seq#
from v\$session s, v\$process p
where p.addr = s.paddr and (s.program like '%MRP%' or s.program like '%PR0%' or s.program like '%DBW%' or s.program like '%CKPT%')
order by s.process
/
eof
sleep ${run_interval}
done

recStats.ksh

--------------

#!/bin/ksh
#
# RecStats.ksh gathers data from the following V$ views at defined sample intervals:
# - V$SYSTEM_EVENT
# - V$EVENT_HISTOGRAM
# - V$SYSSTAT
# The output is written to 3 files (one per view) in the following format: __.
# By default the view data is sampled every 15 seconds.
#
if [ $# -eq 0 ]; then
  $0 15 # Run every 15 secs
elif [ $# -ne 1 ]; then
  echo "Usage: $0 "
exit 1
fi
#
run_interval=$1
#
while [ 1 ]; do
sqlplus -s '/ as sysdba' << eof >> system_event_`uname -n`_${ORACLE_SID}
set linesize 250 pagesize 2000 numwidth 25
col event format a60
select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') "Current time"
     , event
     , total_waits
     , total_timeouts
     , time_waited
     , average_wait
  from v\$system_event
order by time_waited
/
eof

sqlplus -s '/ as sysdba' << eof >> event_histogram_`uname -n`_${ORACLE_SID}
set linesize 250 pagesize 2000 numwidth 25
col event format a60
select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') "Current time"
     , event
     , wait_time_milli
     , wait_count
  from v\$event_histogram
order by event, wait_time_milli
/
eof
sqlplus -s '/ as sysdba' << eof >> sysstat_`uname -n`_${ORACLE_SID}
set linesize 250 pagesize 2000 numwidth 25
col name format a60
select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') "Current time"
     , name
     , value
  from v\$sysstat
/
eof
sleep ${run_interval}
done

rs.ksh

-------

#!/bin/ksh
if [ $# -eq 0 ]; then
  $0 30        # Run every 30 secs
elif [ $# -ne 1 ]; then
  echo "Usage: $0 "
  exit 1
fi
#
run_interval=$1
#
while [ 1 ]; do
sqlplus -s '/ as sysdba' << eof >> redoSize_`uname -n`_${ORACLE_SID}.out
set linesize 150 pagesize 2000
col value format 9999999999999999
select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') "Current time", value
from v\$sysstat
where name = 'redo size'
/
eof
sleep ${run_interval}
done

stbyStats.ksh

---------------------
#!/bin/ksh
if [ $# -eq 0 ]; then
  $0 15        # Run every 15 secs
elif [ $# -ne 1 ]; then
  echo "Usage: $0 "
  exit 1
fi
#
run_interval=$1
#
while [ 1 ]; do
sqlplus -s '/ as sysdba' << eof >> recoveryData_`uname -n`_${ORACLE_SID}.out
set linesize 200 pagesize 2000
col units format a20
col comments format a15
select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') "Current time"
     , ITEM
     , SOFAR
     , TOTAL
     , UNITS
     , to_char(TIMESTAMP,'DD-MON-YYYY HH24:MI:SS') "Timestamp"
from v\$recovery_progress
where total = 0
/
col name format a30
col value format a18
col unit format a35
col time_computed format a22
select
       NAME
     , VALUE
     , UNIT
     , DATUM_TIME
     , TIME_COMPUTED
from v\$dataguard_stats
order by time_computed
/
col inst_id format 99
select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') "Current time"
     , INST_ID
     , PROCESS
     , PID
     , CLIENT_PROCESS
     , STATUS
     , THREAD#
     , SEQUENCE#
     , BLOCK#
     , BLOCKS
from gv\$managed_standby
order by inst_id
/
select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') "Current time"
     , GROUP#
     , THREAD#
     , SEQUENCE#
     , USED
from v\$standby_log
where STATUS = 'ACTIVE'
/
eof
sleep ${run_interval}
done