Shell script to send multiple SQL queries output in HTML format

Shell script to send multiple SQL queries output in HTML format

shell script content
***********************
#!/usr/bin/ksh
export ORACLE_BASE=/optware/oracle
ORACLE_HOME=/optware/oracle/11.2.0.4/db_1
export ORACLE_HOME
ORACLE_SID=PROD3
export ORACLE_SID
PATH=$PATH:$ORACLE_HOME/bin
export PATH
ORAENV_ASK=NO

sqlplus /nolog <connect sys/password as sysdba

set markup HTML ON HEAD " -
" -
TABLE "border='1' width='90%' align='center'" -
ENTMAP ON SPOOL ON

set pages 10000
spool /home/oracle/scripts/sync_status_detailed.html
@/home/oracle/scripts/dg_check.sql
spool off
exit
EOF

echo "Please find the attached detailed report, showing data about the DB Sync Status." | mutt -a "/home/oracle/scripts/sync_status_detailed.html" -s "DB Sync Status" abc@xyz.com
rm /home/oracle/scripts/sync_status_detailed.html

***********************

dg_check.sql contents

***********************

connect system/password@PROD3_P

col DATABASE_ROLE for a20

col host_name for a25

set pages 1000

SET NEWPAGE NONE

set lines 180

prompt

prompt ++++++++++++++++++++++++++++++++++++

prompt + Primary Database details

prompt ++++++++++++++++++++++++++++++++++++

select name "DB Name",host_name "Host Name",database_role "Database Role" from v$database,v$instance;

prompt

prompt

prompt ****************Last Sequence generated in Primary****************

SELECT THREAD# "Thread",max(SEQUENCE#)as  "Last Sequence Generated"

FROM GV$ARCHIVED_LOG group by THREAD# ORDER BY 1

/

conn / as sysdba

prompt

prompt

set lines 180

set pages 130

SET NEWPAGE NONE

col DATABASE_ROLE for a20

col host_name for a35

col status for a15

prompt ++++++++++++++++++++++++++++++++++++

prompt + Standby Database details

prompt ++++++++++++++++++++++++++++++++++++

select name "DB Name",host_name "Host Name",database_role "Database Role" from v$database,v$instance;

prompt

Prompt *************Last Sequence received/applied/difference in Standby**********

SELECT distinct ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"

FROM

(SELECT THREAD# ,max(SEQUENCE#) as SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#) group by THREAD#) ARCH,

(SELECT THREAD# ,max(SEQUENCE#) as SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#) group by THREAD#) APPL

WHERE

ARCH.THREAD# = APPL.THREAD#

ORDER BY 1

/

Prompt *************Last Sequence received/applied time in Standby**********

set pages 0

SET NEWPAGE NONE

select 'Last Sequence applied Time: ' Logs, to_char(next_time,'DD-MON-YY HH:MI:SS AM') Time    from v$archived_log

where sequence# = (select max(sequence#) from v$archived_log where applied='YES')

union

select 'Last Sequence received Time: ' Logs, to_char(next_time,'DD-MON-YY HH:MI:SS AM') Time    from v$archived_log   where sequence# = (select max(sequence#) from v$archived_log) order by 1 desc;

***********************