#

#! /bin/ksh

cd /home/orapfpr

. .profile

export ts=`date +%y%m%d_%H%M%S`

export SPACE_MGMT_HOME=/home/orapfpr/scripts/space_mgmt

export PHYWRT_HOME=$SPACE_MGMT_HOME/config/physical_writes

export PHYWRT_DIFF_HOME=$SPACE_MGMT_HOME/config/physical_write_diffs

export NEXT_EXTENT_HOME=$SPACE_MGMT_HOME/config/next_extent_data

export NNE_HOME=$SPACE_MGMT_HOME/config/no_next_extent

export DIFF_HOME=$SPACE_MGMT_HOME/config/diff_files

export FREE_XTN_HOME=$SPACE_MGMT_HOME/config/free_extent_data

export ALLOC_XTN_HOME=$SPACE_MGMT_HOME/config/alloc_extent_data

export LOGFILE_HOME=$SPACE_MGMT_HOME/log_files

export DBRHome=$SPACE_MGMT_HOME/config/db_read

export CONFIG_HOME=$SPACE_MGMT_HOME/table_files

export HISTORY_HOME=$SPACE_MGMT_HOME/table_history_files

export LOGFILE1=$SPACE_MGMT_HOME/config/table_master_list/table_master_list

export LOGFILE2=$SPACE_MGMT_HOME/config/alloc_extent_data/sorted_extents2

export LOGFILE3=$SPACE_MGMT_HOME/config/free_extent_data/free_extent_data

export LOGFILE4=$SPACE_MGMT_HOME/config/db_read/db_read_errors

export LOGFILE5=$SPACE_MGMT_HOME/config/temp/table_scan_list

export TABLE_CONFIG_FILES_T1=$CONFIG_HOME

export TABLE_CONFIG_FILES_T2=$CONFIG_HOME

export interval=60

export linediff=250

export TESTFILE=$SPACE_MGMT_HOME/check_test

export SCANFILE=$LOGFILE_HOME/table_scan.sql

export HOME=/home/orapfpr/scripts

export DBA3="gs1114@stl.rural.usda.gov"

export DBA2="lm1076@stl.rural.usda.gov"

export DBA1="lb1088@stl.rural.usda.gov"

export LOC1="Mitchell.Clem@Unisys.com"

export PAGER1="3142450893@archwireless.net"

touch $SPACE_MGMT_HOME/scripts/recent_changes

rm $SPACE_MGMT_HOME/scripts/recent_changes

echo "set linesize 100

set heading off

set pagesize 0

set echo off

set feedback off

select name||';'||to_char(created, 'DD-MON-YY HH24:MI:SS')

from v\$database;

quit

" | sqlplus -s "/ as sysdba" >> $LOGFILE4.$ts

# Check for Cloning Activity

touch $SPACE_MGMT_HOME/config/db_read/db_read_errors.txt

rm $SPACE_MGMT_HOME/config/db_read/db_read_errors.txt

date >> $SPACE_MGMT_HOME/config/db_read/db_read_errors_history.txt

more $LOGFILE4.$ts >> $SPACE_MGMT_HOME/config/db_read/db_read_errors_history.txt

more $LOGFILE4.$ts | tail -1 | read db_name_and_date

more $LOGFILE4.$ts | tail -1 | cut -f1 -d';' | read db_name

more $LOGFILE4.$ts | tail -1 | cut -f2 -d';' | read db_creation_date

echo $db_name_and_date >> $SPACE_MGMT_HOME/config/db_read/db_name_and_date.txt

more $SPACE_MGMT_HOME/config/db_read/db_name_and_date.txt | tail -2 | head -1 | read previous_db_name_and_date

echo $previous_db_name_and_date | cut -f1 -d';' | read previous_db_name

echo $previous_db_name_and_date | cut -f2 -d';' | read previous_db_creation_date

more $LOGFILE4.$ts | grep -c 'ORA-01034' | read db_availability

if [ "$db_availability" -ne 0 ]

then

date >> $SPACE_MGMT_HOME/config/db_read/db_read_errors.txt

#more $LOGFILE4.$ts >> $SPACE_MGMT_HOME/config/db_read/db_read_errors.txt

echo 'Space Mgmt Scan: DB '$db_name' is DOWN' >> $SPACE_MGMT_HOME/config/db_read/db_read_errors.txt

echo $db_name | read subject_of_email

mailx -m -s "Database is Down" $DBA3 $DBA2 $DBA1 $LOC1 $PAGER1 < $SPACE_MGMT_HOME/config/db_read/db_read_errors.txt

exit

elif [ "$other_errors_found" -gt 0 ]

then

date >> $SPACE_MGMT_HOME/config/db_read/db_read_errors.txt

#more $LOGFILE4.$ts >> $SPACE_MGMT_HOME/config/db_read/db_read_errors.txt

echo 'Space Mgmt Scan: Error on Select: Scan Cancelled Until Select Completes' >> $SPACE_MGMT_HOME/config/db_read/db_read_errors.txt

echo $db_name | read subject_of_email

mailx -m -s "$subject_of_email" $DBA3 $DBA2 $DBA1 $LOC1 $PAGER1 < $SPACE_MGMT_HOME/config/db_read/db_read_errors.txt

exit

elif [ "$db_name" != "$previous_db_name" ]

then

date >> $SPACE_MGMT_HOME/config/db_read/db_read_errors.txt

#more $LOGFILE4.$ts >> $SPACE_MGMT_HOME/config/db_read/db_read_errors.txt

echo 'Space Mgmt Scan: '$db_name' Scanning Resumed after DB Down Condition' >> $SPACE_MGMT_HOME/config/db_read/db_read_errors.txt

echo $db_name | read subject_of_email

mailx -m -s "$subject_of_email" $DBA3 $DBA2 $DBA1 $LOC1 $PAGER1 < $SPACE_MGMT_HOME/config/db_read/db_read_errors.txt

exit

elif [ "$db_creation_date" != "$previous_db_creation_date" ]

then

rm $LOGFILE_HOME/extent_data.*

rm $SPACE_MGMT_HOME/table_files/table_config_files_T1/*

rm $SPACE_MGMT_HOME/table_history_files/*

echo 'Space Mgmt Scan: '$db_name' Recreated or Cloned'$db_creation_date' :Re-scanning All' >> $SPACE_MGMT_HOME/config/db_read/db_read_errors.txt

echo $db_name | read subject_of_email

mailx -m -s "$subject_of_email" $DBA3 $DBA2 $DBA1 $LOC1 $PAGER1 < $SPACE_MGMT_HOME/config/db_read/db_read_errors.txt

else

echo 'All checks OK' >/dev/null

#touch $NNE_HOME/sendfile

#rm $NNE_HOME/sendfile

#let p=1

#touch $NNE_HOME/untables.lst

#ls $NNE_HOME/untables.* | wc -l | read q

#while [ "$p" -le "$q" ]

#do

#ls $NNE_HOME/untables.* | cut -f"$p" -d' ' | read filename

#echo $filename$p | read filename_no

#more $filename_no >> $NNE_HOME/sendfile

#let p=$p+1

#done

#touch $NNE_HOME/sendfile

#more $NNE_HOME/sendfile | wc -l | read table_count

#if [ "$table_count" -gt 0 ]

#then

#mailx -m -s "Urgent! ORA-1653 No-Next-Extent Error Imminent!" $DBA3 < $NNE_HOME/sendfile

#mailx -m -s "Urgent! ORA-1653 No-Next-Extent Error Imminent!" $DBA3 $DBA2 $DBA1 $LOC1 $PAGER1 < $NNE_HOME/sendfile

#fi

more $DBRHome/db_name_and_date.txt | wc -l | read lines_in_file

let lines_to_delete=$lines_in_file-100

if [ "$lines_to_delete" -gt 100 ]

then

more $DBRHome/db_name_and_date.txt | sed "1,$lines_to_delete"d > $DBRHome/db_name_and_date.temp

mv $DBRHome/db_name_and_date.temp $DBRHome/db_name_and_date.txt

more $DBRHome/db_read_errors_history.txt | wc -l | read lines_in_file

let lines_to_delete=$lines_in_file-100

if [ "$lines_to_delete" -gt 100 ]

then

more $DBRHome/db_read_errors_history.txt | sed "1,$lines_to_delete"d > $DBRHome/db_read_errors_history.temp

mv $DBRHome/db_read_errors_history.temp $DBRHome/db_read_errors_history.txt

date '+%y' | read logfile_removal

rm "$LOGFILE4"."$logfile_removal"*

echo "set linesize 200

set heading off

set pagesize 0

set echo off

set feedback off

select

FILE_ID||':'||

TABLESPACE_NAME||':'||

BYTES||':'||

BLOCK_ID

from dba_free_space;

quit

" | sqlplus -s "/ as sysdba" >> $LOGFILE3.$ts

sort -r $LOGFILE3.$ts > $SPACE_MGMT_HOME/config/free_extent_data/sorted_free_extents.$ts

rm $LOGFILE3.$ts

cd $FREE_XTN_HOME

ls $FREE_XTN_HOME/sorted_free_extents.0[3456789]* | wc -l | read file_count

let dc="$file_count-2"

ls $FREE_XTN_HOME/sorted_free_extents.0[3456789]* | head -"$dc" | sed 's/^/rm /' > $SPACE_MGMT_HOME/scripts/d3.sh

chmod 700 $SPACE_MGMT_HOME/scripts/d3.sh

$SPACE_MGMT_HOME/scripts/d3.sh

#echo 'file_count='$file_count

# Determine which tablespaces have had change in free space condition

# Check to be sure all free_extent files are nonzero in size

# Check to be sure all free_extent_files are not too great a difference in size

wc -l $FREE_XTN_HOME/sorted_free_extents.* | cut -f1 -d' ' | head -1 | read previous_file_lines

#echo 'previus_file_lines='$previous_file_lines

wc -l $FREE_XTN_HOME/sorted_free_extents.* | cut -f1 -d' ' | head -2 | tail -1 | read current_file_lines

wc -l $FREE_XTN_HOME/sorted_free_extents.* | cut -f2 -d' ' | head -2 | tail -1 | read current_extent_file_name

#echo 'current_file_lines='$current_file_lines

let delta1="$previous_file_lines"-"$current_file_lines"

#echo 'delta1='$delta1

if [ "$delta1" -lt 0 ]

then

let delta1=0-"$delta1"

#echo 'delta1='$delta1

if [ "$current_file_lines" -eq 0 ]

then

#mailx -m -s "Zero-byte Free Extent File Detected - Scan Aborted" $DBA3

#ls -l "$SPACE_MGMT_HOME"/config/free_extent_data/"$current_extent_file_name"

#rm "$SPACE_MGMT_HOME"/config/free_extent_data/"$current_extent_file_name"

#echo "file lines was zero"

exit

if [ "$delta1" -gt "$linediff" ]

then

#echo 'delta1 was greater than '$linediff

#mailx -m -s "Large Free Extent File Difference Detected - DBA Verify Status" $DBA3

exit

#echo 'Continue on to compute differences file...'

ls -l $SPACE_MGMT_HOME/config/free_extent_data/sorted_free_extents.* | wc -l | read free_xts_baseline

#echo 'free_xts_baseline='$free_xts_baseline

if [ "$free_xts_baseline" -gt 1 ]

then

ls "$SPACE_MGMT_HOME"/config/free_extent_data/sorted_free_extents.* | tail -2 | head -1 | read previous_free_xtn_scan

#echo $previous_free_xtn_scan

cd "$SPACE_MGMT_HOME"/config/free_extent_data

ls "$SPACE_MGMT_HOME"/config/free_extent_data/sorted_free_extents."$ts" | tail -1 | read current_free_xtn_scan

#echo $current_free_xtn_scan

diff "$previous_free_xtn_scan" "$current_free_xtn_scan" > "$DIFF_HOME"/f_xt_dif."$ts"

wc -l "$DIFF_HOME"/f_xt_dif."$ts" | cut -f1 -d' ' | read free_xtn_diffs_found

#echo 'free_xtn_diffs_found='$free_xtn_diffs_found

if [ $free_xtn_diffs_found -gt 0 ]

then

more "$DIFF_HOME"/f_xt_dif."$ts" | grep ':' | cut -f2 -d':' | sort -ur > "$LOGFILE_HOME"/ts_list

#mailx -m -s "free space changed" "$DBA3" < "$LOGFILE_HOME"/ts_list

else

exit >/dev/null

echo "set linesize 200

set heading off

set pagesize 0

set echo off

set feedback off

select a.file#||':'||

b.tablespace_name||':'||

a.phywrts||':'||

a.phyblkwrt

from v\$filestat a, dba_data_files b

where a.file# = b.file_id

order by a.phyblkwrt;

quit

" | sqlplus -s "/ as sysdba" >> $PHYWRT_HOME/physical_write_data.$ts

echo "set linesize 200

set heading off

set pagesize 0

set echo off

set feedback off

select

OWNER||'.'||

SEGMENT_NAME||':'||

TABLESPACE_NAME||':'||

NEXT_EXTENT

from dba_segments;

quit

" | sqlplus -s "/ as sysdba" >> $NEXT_EXTENT_HOME/next_extents.$ts

echo "set linesize 200

set heading off

set pagesize 0

set echo off

set feedback off

select

EXTENT_ID||':'||

OWNER||'.'||

SEGMENT_NAME||':'||

TABLESPACE_NAME||':'||

BYTES||':'||

FILE_ID||':'||

BLOCK_ID

from dba_extents;

quit

" | sqlplus -s "/ as sysdba" >> "$LOGFILE2"."$ts"

cd $ALLOC_XTN_HOME

ls $ALLOC_XTN_HOME/sorted_extents2."$logfile_removal"* | wc -l | read file_count

let dc="$file_count-1"

ls $ALLOC_XTN_HOME/sorted_extents2."$logfile_removal"* | head -"$dc" | sed 's/^/rm /' > $SPACE_MGMT_HOME/scripts/d2.sh

chmod 700 $SPACE_MGMT_HOME/scripts/d2.sh

$SPACE_MGMT_HOME/scripts/d2.sh

touch $SPACE_MGMT_HOME/scripts/d2.sh

rm $SPACE_MGMT_HOME/scripts/d2.sh

touch $SPACE_MGMT_HOME/scripts/d3.sh

rm $SPACE_MGMT_HOME/scripts/d3.sh

#head -2 $SPACE_MGMT_HOME/scripts/version_table | tail -1 | read HIST_MGR

#export HIST_MGR

#echo "$HIST_MGR"

#"$HIST_MGR" "$ts"

$SPACE_MGMT_HOME/scripts/history_mgr_v4.sh $ts

exit > /dev/null