#!/bin/bash
# whatrmanwaits ()
#------------------------------------------------------------------------------#
#    Module Name:  whatrmanwaits                                               #
#                                                                              #
#    Purpose:      Report on the RMan Channel Waits/Activity.                  #
#                                                                              #
#    Maintenance                                                               #
#    Date          Author        Description                                   #
#    -----------   ------------  --------------------------------------------  #
#    05-Dec-2012   M.Barone      Module design/creation                        #
#------------------------------------------------------------------------------#
{
        DIRNAM=$(dirname $0)
        ORASID=$(echo $CONTEXT_NAME | cut -f1 -d'_')
        HNAME=$(uname -n)
                
        clear
        echo
        echo  " #--------------------------------------------------------#"
        echo  " # whatrmanwaits:      Oracle RMan Channel Waits/Activity #"
        echo  " #--------------------------------------------------------#"
        echo
        echo  " Connecting as SYSDBA"
        sleep 2

        clear
        ORANAM=${ORASID}
        
#------------------------------------------------------------------------------#
#  SQL*Plus ("Here-Document")                                                  #
#------------------------------------------------------------------------------#
sqlplus  -s  /nolog  <<-END_FILE

        CONNECT / AS SYSDBA

        clear   BREAKS
        clear   BUFFER
        clear   COLUMNS
        clear   COMPUTES
        clear   SCREEN
        clear   SQL
        clear   TIMING

        set     feedback        off
        set     flush           on
        set     heading         on
        set     pause           off
        set     space           1
        set     termout         on
        set     verify          on
        set     linesize        80
        set     pagesize        999

        SPOOL /tmp/whatrmanwaits_$$.txt

        COLUMN  datevalue       NOPRINT NEW_VALUE       DATEVAR
        COLUMN  timevalue       NOPRINT NEW_VALUE       TIMEVAR

        COLUMN  rmsid  FORMAT  999         HEADING 'RMan|Proc|SID'
        COLUMN  rspid  FORMAT  A5          HEADING 'RMan|Process|SPID'
        COLUMN  rinfo  FORMAT  A12         HEADING 'RMan Process Wait Info'
        COLUMN  revnt  FORMAT  A12         HEADING 'RMan WaitEvent Details'
        COLUMN  rsecs  FORMAT  9999        HEADING 'Wait|Time|Secs'
        COLUMN  p1     FORMAT  999999999999 HEADING 'WaitEvent|Param1|(Decimal)'
        COLUMN  p2     FORMAT  999999999999 HEADING 'WaitEvent|Param2|(Decimal)'
        COLUMN  p3     FORMAT  999999999999 HEADING 'WaitEvent|Param3|(Decimal)'

        TTITLE  CENTER  'Oracle RMAN Backup Activity v$SESSION_LONGOPS' -
                LEFT    DATEVAR                                         -
                RIGHT   'Page:'         FORMAT 999 SQL.pno      skip 1  -
                CENTER  'whatrmanwaits -- RMan Backup Activity'        -
                LEFT    TIMEVAR                                 skip 2  - 

        SELECT  TO_CHAR(sysdate, 'DD-MON-YY')   datevalue,
                TO_CHAR(sysdate, 'HH24:MI:SS')  timevalue,
                sid                             rmsid,
                spid                            rspid,
                client_info                     rinfo,
                event                           revnt,
                seconds_in_wait                 rsecs,
                p1, p2, p3
          FROM  V\$PROCESS p,
                V\$SESSION s
        WHERE   p.ADDR = s.PADDR
          AND   CLIENT_INFO like 'rman channel=%';

        EXIT;

END_FILE

echo
echo "Please see /tmp/whatrmanwaits_$$.txt"
echo
}

