#!/usr/bin/ksh
# whatarchivelogs ()
#------------------------------------------------------------------------------#
# Module Name: whatarchivelogs #
# #
# Purpose: Report on the Oracle ArchiveLogs Groups and Members. #
# #
# Maintenance #
# Date Author Description #
# ----------- ------------ -------------------------------------------- #
# 10-Oct-2009 M.Barone Module design/creation #
#------------------------------------------------------------------------------#
{
 DIRNAM=$(dirname $0)
 ORASID=$(echo $CONTEXT_NAME | cut -f1 -d'_')
 HNAME=$(uname -n)

 clear
 echo
 echo " #--------------------------------------------------------#"
 echo " # whatarchivelogs: Oracle Database ArchiveLogs #"
 echo " #--------------------------------------------------------#"
 echo
 echo " USPS LogOn as oracle-account"
 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/whatarchivelogs_$$.txt

 CLEAR SCREEN

 COLUMN datevalue NOPRINT NEW_VALUE DATEVAR
 COLUMN timevalue NOPRINT NEW_VALUE TIMEVAR
 COLUMN group# FORMAT 999999 Heading 'Group#'
 COLUMN thread# FORMAT 9999999 Heading 'Thread#'
 COLUMN sequence# FORMAT 99999999999 Heading 'Sequence#'
 COLUMN archived FORMAT A3 Heading 'Archived'
 COLUMN status FORMAT A9 Heading 'Status'
 COLUMN ftime FORMAT A9 Heading 'FirstTime'
 COLUMN ltime FORMAT A9 Heading 'LastTime'
 COLUMN bytes FORMAT 999999999999 Heading 'Bytes'

 TTITLE CENTER 'Datbase ArchiveLogs' -
 LEFT DATEVAR -
 RIGHT 'Page:' FORMAT 999 SQL.pno skip 1 -
 LEFT TIMEVAR -
 CENTER 'whatarchivelogs -- Sorted by Group' skip 2

 SELECT TO_CHAR(sysdate, 'DD-MON-YY') datevalue,
 TO_CHAR(sysdate, 'HH24:MI:SS') timevalue,
 group#,
 thread#,
 sequence#,
 archived,
 status,
 TO_CHAR(first_time, 'DD-MON-YY HH24:MI:SS') ftime,
 TO_CHAR(next_time, 'DD-MON-YY HH24:MI:SS') ltime,
 bytes
 FROM v\$log
 ORDER BY group#, thread#, sequence#;

 EXIT;
END_FILE

print
print "Please see /tmp/whatarchivelogs_$$.txt"
print
}
