#!/usr/bin/ksh
#set -xv
# author  : B. Polarski
# program : smenu_stream_aq.ksh
# date    : 28 Mars 2006

ROWNUM=30
HOST=`hostname`
HOST=`echo $HOST | awk '{ printf ("%-+15.15s",$1)  }'`

# -------------------------------------------------------------------------------------
function help
{
   cat <<EOF

         Logical dataguard

         dg -l -c -p -d -m -e -a -s -err
 
          -a : Logstandby apply status
          -m : monitor log apply and log transport services
          -e : Errors messages
          -l : Log standby status
        -lerr: log ship error to standby 
          -c : Log standby Coordinator Status
          -p : Logical standby SCN progress
          -d : Logical standby archive destination status
          -e : Error message on the logical apply

         
        -par : Logical dataguard parameters
          -h : This help

     -rn <n> : Limit display to first <n> rows


EOF
exit
}
# -------------------------------------------------------------------------------------
function do_execute
{
$SETXV
sqlplus -s "$CONNECT_STRING" <<EOF
ttitle skip 2 'MACHINE $HOST - ORACLE_SID : $ORACLE_SID '  right 'Page:' format 999 sql.pno skip 2
column nline newline
set pagesize 66 linesize 100 termout on pause off embedded on verify off heading off
select 'Date              -  '||to_char(sysdate,'Day Ddth Month YYYY  HH24:MI:SS'),
       'Username          -  '||USER  nline, '$TTITLE (dg -h for help)' nline
from sys.dual
/
set head on
COLUMN STATUS FORMAT A50
COLUMN TYPE FORMAT A12
COLUMN NAME FORMAT A30
COLUMN VALUE FORMAT A30
COLUMN SEQUENCE# FORMAT 9999999 head "SEQ#"
COLUMN DICT_BEGIN format A5 head "Dict|Begin"
col guard_status head "Guard|Status"
col SUPPLEMENTAL_LOG_DATA_MIN format A8 head "Suplement|Log" justify c
col SUPPLEMENTAL_LOG_DATA_PK format A8 head "PK in|Sup Log" justify c
col SUPPLEMENTAL_LOG_DATA_FK format A8 head "FK in|Sup Log" justify c
col SUPPLEMENTAL_LOG_DATA_ALL format A10 head "Fixed|Length in|Sup Log"
col FLASHBACK_ON format A9 head "Flash|Back on" justify c
$BREAK
set linesize 125
prompt
$SQL
EOF
}
# -------------------------------------------------------------------------------------
#                    Main
# -------------------------------------------------------------------------------------

if [ -z "$1" ];then
   help
fi

while [ -n "$1" ]
do
  case "$1" in
       -a ) ACTION=APPLY ; EXECUTE=YES ; TTITLE="Logstandby apply status" ;;
       -l ) ACTION=LGBY ; EXECUTE=YES ; TTITLE="Logstandby status" ;;
       -c ) ACTION=COORD ; EXECUTE=YES ; TTITLE="Logstandby Coordinator Status" ;;
       -p ) ACTION=PROG ; EXECUTE=YES ; TTITLE="Show Logical standby SCN progress " ;;
       -d ) ACTION=ARCH_STAT ; EXECUTE=YES ; TTITLE="Show Logical standby archive " ;;
       -m ) ACTION=MANAGED ; EXECUTE=YES ; TTITLE="monitor log apply and log transport services" ;;
       -e ) ACTION=ERROR ; EXECUTE=YES ; TTITLE="Error message on the logical apply" ;;
    -lerr ) ACTION=LOG_SHIP_ERROR ;  EXECUTE=YES ; TTITLE="log ship error to standby" ;;
       -par ) ACTION=PARAM ; EXECUTE=YES ; TTITLE="Logical dataguard parameters" ;;
       -h ) help ;;
       -r ) ROWNUM=$2 ; shift ;;
       -v ) SETXV="set -xv";;
       -x ) EXECUTE=YES;;
        * ) echo "What is $1 ? "; help ;;
  esac 
  shift
done

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

if [ "$ACTION" = "LOG_SHIP_ERROR" ];then

   SQL="

column  Status    format a10      heading "Status"
column  destination    format a35      heading "Destination"
prompt
 SELECT DEST_ID "ID",
   STATUS "DB_status",
   DESTINATION ,
   ERROR "Error"
   FROM V\$ARCHIVE_DEST
/
Prompt
prompt Check for archive log gaps:
Prompt ===========================
prompt
select * from v\$archive_gap
/
column  Status    format a20      heading "Status"
Prompt Check Managed standby processes:
Prompt ================================
prompt
select process,status from v\$managed_standby
/
"

elif [ "$ACTION" = "LGBY" ];then
   SQL="SELECT TYPE, HIGH_SCN, STATUS FROM V\$LOGSTDBY;

column  Status    format a10      heading 'Status'
column  Message    format a56      heading 'Message'
column  error_code    format 999999      heading 'Error| Code'
prompt Type 'dg -l -rn 50' to see 50 lines, default is 30
prompt
select   facility, severity,The_date, message,error_code from (
select facility, severity, to_char(timestamp,'DD/MM/YYYY HH24:MI:SS') The_date, message,
       error_code from v\$dataguard_status order by message_num desc
) where ROWNUM <= $ROWNUM;
"
elif [ "$ACTION" = "COORD" ];then
   SQL="SELECT NAME, VALUE FROM V\$LOGSTDBY_STATS WHERE NAME = 'coordinator state';"
elif [ "$ACTION" = "MANAGED" ];then
   SQL="col status format A20
        SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V\$MANAGED_STANDBY;"

elif [ "$ACTION" = "PARAM" ];then
     SQL="select GUARD_STATUS, DATABASE_ROLE, PROTECTION_MODE, SWITCHOVER_STATUS,
          SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK, 
          SUPPLEMENTAL_LOG_DATA_FK, SUPPLEMENTAL_LOG_DATA_ALL, 
          FLASHBACK_ON  from v\$database ;"

elif [ "$ACTION" = "ERROR" ];then
    SQL="COLUMN EVENT FORMAT A20
   COLUMN STATUS FORMAT A60
   COLUMN fdate FORMAT A19 head "Date"
   SELECT to_char(EVENT_TIME,'DD-MM-YYYY HH24:MI:SS') fdate, 
          STATUS, EVENT FROM DBA_LOGSTDBY_EVENTS ORDER BY EVENT_TIME, COMMIT_SCN;"

elif [ "$ACTION" = "PROG" ];then
   SQL="SELECT APPLIED_SCN, NEWEST_SCN FROM DBA_LOGSTDBY_PROGRESS;"

elif [ "$ACTION" = "ARCH_STAT" ];then
   SQL="col DESTINATION format a40 
     col status format A20
     SELECT DESTINATION, STATUS, ARCHIVED_THREAD#, ARCHIVED_SEQ# FROM V\$ARCHIVE_DEST_STATUS 
     where STATUS <> 'INACTIVE';"

elif [ "$ACTION" = "APPLY" ];then
     SQL="COLUMN DICT_BEGIN FORMAT A10;
         COLUMN FILE_NAME FORMAT A60;
         SELECT FILE_NAME, SEQUENCE#, FIRST_CHANGE#, NEXT_CHANGE#,
                TIMESTAMP, DICT_BEGIN, DICT_END FROM DBA_LOGSTDBY_LOG ORDER BY SEQUENCE#;"
fi

if [ "$EXECUTE" = "YES" ];then
   do_execute
else
  echo "$SQL"
fi