Get orphaned AWR snapshots record count from tables like WRH% in the SYSAUX tablespace
Script : get_orphaned_awr_snapshots.sh
#!/usr/bin/bash
#
#????Name:??????get_orphaned_awr_snapshots.sh
#????Purpose:????Get orphaned AWR snapshots record count from tables like WRH% in the SYSAUX tablespace
#????Author :????Ravin Bharath Maharaj
#????Date??:????23 Nov 2021
###############################################################################################################
if [ "$1" ]; then
?export ORACLE_SID=$1
?typeset -l dbse=${ORACLE_SID}
?typeset -u UDBSE=${ORACLE_SID}
else
?echo "The database name must be passed as a parameter."
?exit
fi
if [ "$2" ]; then
?db_nme=$2
?typeset -l dbnme=${db_nme}
?typeset -u DBNME=${db_nme}
?dbnme_reverse=`echo ${dbnme} | awk '{ for(i=length;i!=0;i--)x=x substr($0,i,1);}END{print x}'`
else
?echo "The database name must be passed as a parameter."
?exit
fi
table_list?()
{
sqlplus -s /@<dbnme>_system <<_eof > ${LogDir}/${dbnme}_lista_rec_cnt.tmp
set heading off
set echo off
set feedback off
set pages 100
rem select replace(SEGMENT_NAME,'$','\$')
select SEGMENT_NAME
from dba_segments
where TABLESPACE_NAME like 'SYSAUX'
and??SEGMENT_NAME like 'WRH%'
and??SEGMENT_TYPE like '%TABLE%'
group by OWNER, SEGMENT_NAME, SEGMENT_TYPE
having sum(BYTES)/1024/1024 > 10
order by sum(BYTES)/1024/1024;
_eof
sed '1d' ${LogDir}/${dbnme}_lista_rec_cnt.tmp > ${listoftbls_logfle}
rm ${LogDir}/${dbnme}_lista_rec_cnt.tmp
}
#Main Program Starts here
#------------------------
领英推荐
DBRUNNING=`ps -ef | grep pmon_${ORACLE_SID} | grep -v grep`
if [ -n "${DBRUNNING}" ]
then
??ORACLE_HOME=`cat /var/opt/oracle/oratab|grep -v ^# | grep -i "${ORACLE_SID}[^A-z]" |awk -F: '{print $2}'`
??GRID_HOME=`cat /var/opt/oracle/oratab|grep -v ^# | grep -i "ASM[^A-z]" |awk -F: '{print $2}'`
??TNS_ADMIN=${GRID_HOME}/network/admin
??PATH=$ORACLE_HOME/bin:/usr/ccs/bin:/usr/local/bin:/usr/lib:/usr/bin:$PATH:.
??export ORACLE_HOME PATH TNS_ADMIN
??export SHLIB_PATH=$ORACLE_HOME/lib
??export LD_LIBRARY_PATH=$ORACLE_HOME/lib
??export ORAENV_ASK=no
??hstname=`hostname`
??ScriptDir="/dbworks/scripts/housekeeping/sysaux_housekeeping"
??BaseDir="/export/home/oracle/${dbnme}"
??LogDir="${BaseDir}/logs"
#?Execute sub-rotine/function to get table names like WRH%
#?---------------------------------------------------------
??listoftbls_logfle=${LogDir}/${dbnme}_listoftbls_of_orphaned_awr_snap_rec_cnt_`date +%Y%m%d`.txt
??table_list
??tbl_snapshot="WRM\$_SNAPSHOT"
??tbl_databse="V\$DATABASE"
??for tbl in `cat ${listoftbls_logfle} | grep -v ^#`
??do
???tbl_row_cnt=`sqlplus -s /@<dbnme>_system <<EOF
???set verify off pages 0 echo off
???col how_many for 999999999999999999
???select count(1) how_many
???from sys.${tbl} a
???WHERE NOT EXISTS (SELECT 1
????????????FROM sys.${tbl_snapshot} b
????????????WHERE b.snap_id = a.snap_id
????????????AND??dbid=(SELECT dbid FROM ${tbl_databse})
????????????AND??b.dbid = a.dbid
????????????AND??b.instance_number = a.instance_number);
???exit
EOF`
???if [ ${?} -eq 0 ]
???then
????if [ ${tbl_row_cnt} -gt 0 ]
????then
?????echo "${tbl} ${tbl_row_cnt}" >> ${LogDir}/${dbnme}_get_orphan_recs_`date +%Y%m%d_%H`.lst
????fi
???fi
??done
else
??printf "Unable to get orphaned awr snapshot record count for SID ${ORACLE_SID} Database Instance down \n"
fi