#!/bin/sh
# set -xv
# program : smenu_rman_show_bk.ksh e Author  : bernard Polarski
# Date    : 03-Nov-2005


# ----------------------------------------------------------------------------------
help()
{
  cat <<EOF

     
     lsbk  
     lsbk   -lp 
     lsbk   -bs <BS_KEY>
         
          -r : max row to display, default is 30
         -cf : show configuration
          -c : List backup of controlfiles
          -s : List backup of spfiles

EOF
  exit 127
}
# ----------------------------------------------------------------------------------
ROWNUM=30
while [ ! -z "$1" ];do
  case "$1" in
          -r ) ROWNUM=$2 ; shift ;;
         -lp ) CHOICE=BACKUP_PIECE;;
         -cf ) CHOICE=CONF;;
          -c ) CHOICE=CONTROLFILE;;
          -s ) CHOICE=SPFILE;;
         -bs ) CHOICE=BACKUP_PIECE ; BS=$2; shift ;;
          *  ) help ;;
    
  esac
  shift
done

if [ -z "$CONNECT_STRING" ];then
   . $SBIN/scripts/passwd.env
   . ${GET_PASSWD} 
   if [  "x-$CONNECT_STRING" = "x-" ];then
      echo "could no get a the password of $S_USER"
      exit 0
   fi
fi


if [ "$CHOICE"  = "SPFILE" ];then

#-- +----------------------------------------------------------------------------+
#-- |                          Jeffrey M. Hunter                                 |
#-- |                      jhunter@idevelopment.info                             |
#-- |                         www.idevelopment.info                              |
#-- |----------------------------------------------------------------------------|
#-- |      Copyright (c) 1998-2007 Jeffrey M. Hunter. All rights reserved.       |
#-- |----------------------------------------------------------------------------|
#-- | DATABASE : Oracle                                                          |
#-- | FILE     : rman_spfiles.sql                                                |
#-- | CLASS    : Recovery Manager                                                |
#-- | PURPOSE  : Provide a listing of automatically backed up SPFILEs.           |
#-- | NOTE     : As with any code, ensure to test this script in a development   |
#-- |            environment before attempting to run it in production.          |
#-- +----------------------------------------------------------------------------+


sqlplus -s "$CONNECT_STRING" <<EOF1



SET LINESIZE 145
SET PAGESIZE 9999

COLUMN bs_key                 FORMAT 9999     HEADING 'BS|Key'
COLUMN piece#                 FORMAT 99999    HEADING 'Piece|#'
COLUMN copy#                  FORMAT 9999     HEADING 'Copy|#'
COLUMN bp_key                 FORMAT 9999     HEADING 'BP|Key'
COLUMN spfile_included        FORMAT a11      HEADING 'SPFILE|Included?'
COLUMN completion_time        FORMAT a20      HEADING 'Completion|Time'
COLUMN status                 FORMAT a9       HEADING 'Status'
COLUMN handle                 FORMAT a65      HEADING 'Handle'

BREAK ON bs_key

prompt
prompt Available automatic SPFILE files within all available (and expired) backup sets.
prompt 

select * from (
SELECT
    bs.recid                                               bs_key
  , bp.piece#                                              piece#
  , bp.copy#                                               copy#
  , bp.recid                                               bp_key
  , sp.spfile_included                                     spfile_included
  , TO_CHAR(bs.completion_time, 'DD-MON-YYYY HH24:MI:SS')  completion_time
  , DECODE(   status
            , 'A', 'Available'
            , 'D', 'Deleted'
            , 'X', 'Expired')                              status
  , handle                                                 handle
FROM
    v\$backup_set                                           bs
  , v\$backup_piece                                         bp
  ,  (select distinct
          set_stamp
        , set_count
        , 'YES'     spfile_included
      from v\$backup_spfile)                                sp
WHERE
      bs.set_stamp = bp.set_stamp
  AND bs.set_count = bp.set_count
  AND bp.status IN ('A', 'X')
  AND bs.set_stamp = sp.set_stamp
  AND bs.set_count = sp.set_count
ORDER BY
    bs.recid desc , piece#
) where ROWNUM <= $ROWNUM
/

EOF1

elif [ "$CHOICE"  = "CONTROLFILE" ];then

#-- +----------------------------------------------------------------------------+
#-- |                          Jeffrey M. Hunter                                 |
#-- |                      jhunter@idevelopment.info                             |
#-- |                         www.idevelopment.info                              |
#-- |----------------------------------------------------------------------------|
#-- |      Copyright (c) 1998-2007 Jeffrey M. Hunter. All rights reserved.       |
#-- |----------------------------------------------------------------------------|
#-- | DATABASE : Oracle                                                          |
#-- | FILE     : rman_controlfiles.sql                                           |
#-- | CLASS    : Recovery Manager                                                |
#-- | PURPOSE  : Provide a listing of automatically backed up control files.     |
#-- | NOTE     : As with any code, ensure to test this script in a development   |
#-- |            environment before attempting to run it in production.          |
#-- +----------------------------------------------------------------------------+

sqlplus -s "$CONNECT_STRING" <<EOF1

SET LINESIZE 145
SET PAGESIZE 9999

COLUMN bs_key                 FORMAT 9999     HEADING 'BS|Key'
COLUMN piece#                 FORMAT 99999    HEADING 'Piece|#'
COLUMN copy#                  FORMAT 9999     HEADING 'Copy|#'
COLUMN bp_key                 FORMAT 9999     HEADING 'BP|Key'
COLUMN controlfile_included   FORMAT a11      HEADING 'Controlfile|Included?'
COLUMN completion_time        FORMAT a20      HEADING 'Completion|Time'
COLUMN status                 FORMAT a9       HEADING 'Status'
COLUMN handle                 FORMAT a65      HEADING 'Handle'

BREAK ON bs_key


prompt
prompt Available automatic control files within all available (and expired) backup sets.
prompt 

select * from (
SELECT
    bs.recid                                               bs_key
  , bp.piece#                                              piece#
  , bp.copy#                                               copy#
  , bp.recid                                               bp_key
  , DECODE(   bs.controlfile_included
            , 'NO', '-'
            , bs.controlfile_included)                     controlfile_included
  , TO_CHAR(bs.completion_time, 'DD-MON-YYYY HH24:MI:SS')  completion_time
  , DECODE(   status
            , 'A', 'Available'
            , 'D', 'Deleted'
            , 'X', 'Expired')                              status
  , handle                                                 handle
FROM
    v\$backup_set    bs
  , v\$backup_piece  bp
WHERE
      bs.set_stamp = bp.set_stamp
  AND bs.set_count = bp.set_count
  AND bp.status IN ('A', 'X')
  AND bs.controlfile_included != 'NO'
ORDER BY
    bs.recid desc , piece#
) where ROWNUM <= $ROWNUM
/
EOF1
elif [ "$CHOICE"  = "CONF" ];then

sqlplus -s "$CONNECT_STRING" <<EOF1
SET LINESIZE 145
SET PAGESIZE 9999

COLUMN name     FORMAT a48   HEADING 'Name'
COLUMN value    FORMAT a55   HEADING 'Value'

prompt 
prompt All RMAN Configuration Settings that are not default
prompt 

SELECT
    name
  , value
FROM
    v\$rman_configuration
ORDER BY
    name
/
EOF1

elif [ "$CHOICE"  = "BACKUP_PIECE" ];then

if [ -n "$BS" ];then
   AND_BS=" and bp.recid = $BS "
fi


sqlplus -s "$CONNECT_STRING" <<EOF1
-- +----------------------------------------------------------------------------+
-- |                          Jeffrey M. Hunter                                 |
-- |                      jhunter@idevelopment.info                             |
-- |                         www.idevelopment.info                              |
-- |----------------------------------------------------------------------------|
-- |      Copyright (c) 1998-2007 Jeffrey M. Hunter. All rights reserved.       |
-- |----------------------------------------------------------------------------|
-- | DATABASE : Oracle                                                          |
-- | FILE     : rman_backup_pieces.sql                                          |
-- | CLASS    : Recovery Manager                                                |
-- | PURPOSE  : Provide a listing of all RMAN Backup Pieces.                    |
-- | NOTE     : As with any code, ensure to test this script in a development   |
-- |            environment before attempting to run it in production.          |
-- +----------------------------------------------------------------------------+

SET LINESIZE 145
SET PAGESIZE 9999

COLUMN bs_key              FORMAT 9999          HEADING 'BS|Key'
COLUMN piece#              FORMAT 99999         HEADING 'Piece|#'
COLUMN copy#               FORMAT 9999          HEADING 'Copy|#'
COLUMN bp_key              FORMAT 9999          HEADING 'BP|Key'
COLUMN status              FORMAT a9            HEADING 'Status'
COLUMN handle              FORMAT a65           HEADING 'Handle'
COLUMN start_time          FORMAT a17           HEADING 'Start|Time'
COLUMN completion_time     FORMAT a17           HEADING 'End|Time'
COLUMN elapsed_seconds     FORMAT 999,999       HEADING 'Elapsed|Seconds'
COLUMN deleted             FORMAT a8            HEADING 'Deleted?'

BREAK ON bs_key

prompt
prompt Available backup pieces contained in the control file.
prompt Includes available and expired backup sets.
prompt 

select * from (
SELECT
    bs.recid                                            bs_key
  , bp.piece#                                           piece#
  , bp.copy#                                            copy#
  , bp.recid                                            bp_key
  , DECODE(   status
            , 'A', 'Available'
            , 'D', 'Deleted'
            , 'X', 'Expired')                           status
  , handle                                              handle
  , TO_CHAR(bp.start_time, 'mm/dd/yy HH24:MI:SS')       start_time
  , TO_CHAR(bp.completion_time, 'mm/dd/yy HH24:MI:SS')  completion_time
  , bp.elapsed_seconds                                  elapsed_seconds
FROM
    v\$backup_set    bs
  , v\$backup_piece  bp
WHERE
      bs.set_stamp = bp.set_stamp
  AND bs.set_count = bp.set_count
  AND bp.status IN ('A', 'X') $AND_BS
ORDER BY
    bs.recid desc , piece#
) where rownum <=$ROWNUM
/
EOF1

else # now runnig DEFAULT

sqlplus -s "$CONNECT_STRING" <<EOF1
-- +----------------------------------------------------------------------------+
-- |                          Jeffrey M. Hunter                                 |
-- |                      jhunter@idevelopment.info                             |
-- |                         www.idevelopment.info                              |
-- |----------------------------------------------------------------------------|
-- |      Copyright (c) 1998-2007 Jeffrey M. Hunter. All rights reserved.       |
-- |----------------------------------------------------------------------------|
-- | DATABASE : Oracle                                                          |
-- | FILE     : rman_backup_sets.sql                                            |
-- | CLASS    : Recovery Manager                                                |
-- | PURPOSE  : Provide a listing of all RMAN Backup Sets.                      |
-- | NOTE     : As with any code, ensure to test this script in a development   |
-- |            environment before attempting to run it in production.          |
-- +----------------------------------------------------------------------------+

SET LINESIZE 145
SET PAGESIZE 9999

COLUMN bs_key                 FORMAT 999999                 HEADING 'BS|Key'
COLUMN backup_type            FORMAT a13                    HEADING 'Backup|Type'
COLUMN device_type            FORMAT a8                     HEADING 'Device|Type'
COLUMN controlfile_included   FORMAT a11                    HEADING 'Controlfile|Included?'
COLUMN spfile_included        FORMAT a9                     HEADING 'SPFILE|Included?'
COLUMN incremental_level      FORMAT 999999                 HEADING 'Inc.|Level'
COLUMN pieces                 FORMAT 9,999                  HEADING '# of|Pieces'
COLUMN start_time             FORMAT a17                    HEADING 'Start|Time'
COLUMN completion_time        FORMAT a17                    HEADING 'End|Time'
COLUMN elapsed_seconds        FORMAT 999,999                HEADING 'Elapsed|Seconds'
COLUMN tag                    FORMAT a19                    HEADING 'Tag'
COLUMN block_size             FORMAT 99999                  HEADING 'Block|Size'

prompt
prompt Available backup sets contained in the control file.
prompt Includes available and expired backup sets.
prompt 

select * from (
SELECT
    bs.recid                                              bs_key
  , DECODE(backup_type
           , 'L', 'Archived Logs'
           , 'D', 'Datafile Full'
           , 'I', 'Incremental')                          backup_type
  , device_type                                           device_type
  , DECODE(   bs.controlfile_included
            , 'NO', null
            , bs.controlfile_included)                    controlfile_included
  , sp.spfile_included                                    spfile_included
  , bs.incremental_level                                  incremental_level
  , bs.pieces                                             pieces
  , TO_CHAR(bs.start_time, 'mm/dd/yy HH24:MI:SS')         start_time
  , TO_CHAR(bs.completion_time, 'mm/dd/yy HH24:MI:SS')    completion_time
  , bs.elapsed_seconds                                    elapsed_seconds
  , bp.tag                                                tag
  , bs.block_size                                         block_size
FROM
    v\$backup_set                           bs
  , (select distinct
         set_stamp
       , set_count
       , tag
       , device_type
     from v\$backup_piece
     where status in ('A', 'X'))           bp
 ,  (select distinct
         set_stamp
       , set_count
       , 'YES'     spfile_included
     from v\$backup_spfile)                 sp
WHERE
      bs.set_stamp = bp.set_stamp
  AND bs.set_count = bp.set_count
  AND bs.set_stamp = sp.set_stamp (+)
  AND bs.set_count = sp.set_count (+)
ORDER BY
    bs.start_time desc, bs.recid
) where rownum <=$ROWNUM
/
EOF1
fi