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 " -
DB Sync Status Report " -
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
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 <
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;
***********************