[root@WebWapDev /oracle/dba_script]cat op_db_webdb_chk.sh
#!/bin/csh
set today=`date +%Y%m%d.%H%M%S`
set host_name=`hostname`
set s_sharp='#############################################################
###########################'
set file_name=report.${host_name}.$ORACLE_SID.$today
set filename=${ORACLE_HOME}/dba_script/dba_logs/report.${host_name}.$ORACL
E_SID.$today
# set ORACLE_ARCH=/DMBDB/archive

echo "" > $filename
echo $s_sharp >> $filename
echo "#  Host Name  :" $host_name >> $filename
echo "#  Oracle SID :" $ORACLE_SID >> $filename
echo "#  Date      :" `date` >> $filename
echo "#  Content    : A report for Oracle Database Information" >> $filename
echo $s_sharp >> $filename
echo "" >> $filename

echo "----------------------------- Disk Usage ----------------------------------------" >> $filename
df -k >> $filename
echo "" >> $filename

echo "----------------------------- Process Check ----------------------------------------" >> $filename
ps -ef | grep ora_ >> $filename
ps -ef | grep tns >> $filename
echo "" >> $filename

echo "----------------------------- Background Dump File Check -----------------------------" >> $filename
echo $ORACLE_BASE/admin/WEBDB/bdump >> $filename
ls -lt $ORACLE_BASE/admin/WEBDB/bdump >> $filename

echo "----------------------------- Core Dump File Check -----------------------------" >> $filename
ls -lt $ORACLE_BASE/admin/WEBDB/cdump >> $filename

echo "----------------------------- User Dump File Check -----------------------------" >> $filename
ls -lt $ORACLE_BASE/admin/WEBDB/udump >> $filename

echo "----------------------------- Control Trace File Number -----------------------------" >> $filename
echo "BDUMP Trace Files : " `ls -l $ORACLE_BASE/admin/WEBDB/bdump/*.trc | grep -c '.trc'` >> $filename
echo "UDUMP Trace Files : " `ls -l $ORACLE_BASE/admin/WEBDB/udump/*.trc | grep -c '.trc'` >> $filename

echo "----------------------------- Archive Log Files -----------------------------" >> $filename
# echo $ORACLE_ARCH >> $filename
# ls -lt $ORACLE_ARCH >> $filename
echo "---------------------------------------------------------------------
----------------" >> $filename
echo "" >> $filename

sqlplus '/as sysdba' << EOF >> temp.txt

-- SET PAUSE OFF
-- SET VERIFY OFF
SET ECHO ON
-- SET NEWPAGE 0
-- SET SPACE 0
SET FEEDBACK OFF
-- SET HEADING OFF

set lines 100;
set pages 900;
-- spool temp.txt

---------------------------------- DATABASE Configuration ------------------------------------
show sga;
        col HOST_NAME format a15;
select HOST_NAME, INSTANCE_NAME, VERSION, STATUS, ARCHIVER
        from V\$INSTANCE;
select BANNER "Oracle Version"          from V\$VERSION;
        col "Data file Name" format a70;
select NAME "DB Name", to_char(CREATED, 'YYYY-MM-DD') "Date Created", LOG_MODE, OPEN_MODE
        from V\$DATABASE;

---------------------------------- Redo log Information ------------------------------------
        col "Log Group Members" format a70;
select GROUP#, STATUS, MEMBER "Log Group Members"      from V\$LOGFILE;
select GROUP#, MEMBERS, STATUS, ARCHIVED, SEQUENCE#, BYTES
                                        from V\$LOG;

---------------------------------- Control file Information ------------------------------------
select NAME "Control file Name"        from V\$CONTROLFILE;

---------------------------------- Tablespace Usage ------------------------------------

SELECT
        A.TABLESPACE_NAME,
        round(SUM(A.BYTES)/(1024*1024), 0) AS TOTAL_SPACE,
        round(SUM(A.BYTES)/(1024*1024), 0) - NVL(round(SUM(FREE_SIZE), 0), 0) as USED_SPACE,
        NVL(round(SUM(FREE_SIZE), 0), 0) AS FREE_SPACE,
        NVL( round(( 1. - SUM(FREE_SIZE)/(SUM(A.BYTES)/(1024.*1024.))) * 100, 0), 100) AS "USAGE %"
FROM DBA_DATA_FILES A,
  ( SELECT
                FILE_ID,
                SUM(BYTES)/(1024.*1024.) AS FREE_SIZE
    FROM DBA_FREE_SPACE
    GROUP BY FILE_ID ) B
WHERE A.FILE_ID = B.FILE_ID (+)
GROUP BY A.TABLESPACE_NAME
-- ORDER BY TOTAL_SPACE DESC;
ORDER BY TABLESPACE_NAME;

---------------------------------- Datafile Usage ------------------------------------
col file_name for a35;
SELECT
          A.FILE_NAME,
          round(A.BYTES/(1024*1024), 0) AS TOTAL_SPACE,
          round(A.BYTES/(1024*1024), 0) - NVL(round(FREE_SIZE, 0), 0) as USED_SPACE,
          NVL(round(FREE_SIZE, 0), 0) AS FREE_SPACE,
          NVL( round(( 1. - FREE_SIZE/(A.BYTES/(1024.*1024.))) * 100, 0), 100) AS "USAGE %"
FROM DBA_DATA_FILES A,
    ( SELECT
                  FILE_ID,
                  SUM(BYTES)/(1024.*1024.) AS FREE_SIZE
      FROM DBA_FREE_SPACE
      GROUP BY FILE_ID ) B
WHERE A.FILE_ID = B.FILE_ID (+);

exit;

EOF

awk '$1 ~ /SQL>/ { print "" } $1 !~ /SQL>/ { print $0 } ' temp.txt >> $filename
rm /oracle/dba_script/temp.txt