#!/usr/bin/ksh
# whatarchivelogdetails ()
#------------------------------------------------------------------------------#
# Module Name: whatarchivelogdetails #
# #
# Purpose: Report on the Oracle ArchiveLogs Details. #
# #
# 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 " # whatarchivelogdetails: 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/whatarchivelogdetails_$$.txt

 CLEAR SCREEN

 COLUMN datevalue NOPRINT NEW_VALUE DATEVAR
 COLUMN timevalue NOPRINT NEW_VALUE TIMEVAR
 COLUMN rtime FORMAT A9 Heading 'ResetLog|Time'
 COLUMN ftime FORMAT A9 Heading 'FirstTime'
 COLUMN ltime FORMAT A9 Heading 'LastTime'
 COLUMN ctime FORMAT A9 Heading 'Completion|Time'
 COLUMN sequence# FORMAT 99999999999 Heading 'Sequence#'
 COLUMN name FORMAT A26 Heading 'ArchiveLog Name'

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

 SELECT TO_CHAR(sysdate, 'DD-MON-YY') datevalue,
 TO_CHAR(sysdate, 'HH24:MI:SS') timevalue,
 TO_CHAR(resetlogs_time, 'DD-MON-YY HH24:MI:SS') rtime,
 TO_CHAR(first_time, 'DD-MON-YY HH24:MI:SS') ftime,
 TO_CHAR(next_time, 'DD-MON-YY HH24:MI:SS') ltime,
 TO_CHAR(completion_time, 'DD-MON-YY HH24:MI:SS') ctime,
 sequence#,
 name
 FROM V_\$ARCHIVED_LOG
 ORDER BY sequence#;

 EXIT;
END_FILE

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