Experiences of a DBA
A quick little command to print sum of the sizes of all files in a directory that match a pattern for a given date:
ls -ltr *.gz | grep "Nov 16" | awk '{sum = sum + $5} END {print ((sum/1024)/1024) " mb"}'
The sum of size all files in the current directory that have an extension of “.gz” and were created on “Nov 16” will be returned. The size, in mb, is a close approximation.
A simple script to monitor the alert log for errors via x$dbgalertext rather than the text error log. This script assumes a four node RAC. The script runs on each of the four nodes via CRON. The instance name is passed in as a parameter.
Call with
Check_alert_log.ksh instance_name1/2/3/4
The script will mail out the last 24 hours of the alert log at the 15:00 hour. All other executions will only look back 90 minutes and mail only if an error is found.
#!/bin/ksh
#
#-----------------------------------------------------------------------------------------
#
# Name : Monitor_alert_log.ksh
# Purpose : check the alert log
#
#-----------------------------------------------------------------------------------------
export PATH=$PATH:Oracle_Home/bin
export ORACLE_BASE=/opt/oracle/product
export ORACLE_HOME=Oracle_Home
function run_sql
{
echo "Entering function run_sql `date`"
sqlplus -s / as sysdba << EOF > $ReportFile
set pagesize 100
set linesize 200
set tab off
set wrap off
col mt for a16 head 'Time (MT)'
col gmt for a16 head 'Time (GMT)'
col problem_key for a10 head 'Error'
col message_text for a40 head 'Message Text' wrap
select TO_CHAR(new_time(originating_timestamp,'GMT','MDT'), 'MM-DD-YYYY HH24:MI') as MT
,TO_CHAR(originating_timestamp,'MM-DD-YYYY HH24:MI') as GMT
,problem_key
,MESSAGE_TEXT
from x\$dbgalertext a
where originating_timestamp > sysdate - interval '$look_back' minute
and MESSAGE_TEXT LIKE '%ORA-%'
order by originating_timestamp desc
;
exit
EOF
echo "Exiting function run_sql `date`"
}
function decide_to_email
{
egrep -i "no rows selected" $ReportFile > /dev/null
if [ $? -eq 0 ]
then
if [ $mandatory_mail == "Y" ]
then
echo 'Mailing out report based on time'
mail -s "Alert Log monitoring $ORACLE_SID" "Email_id" < $ReportFile
fi
else
echo 'Mailing out report based on errors found'
mail -s "Alert Log monitoring $ORACLE_SID" "Email_id" < $ReportFile
fi
}
#
## start of script
#
sid_parm=$1
export ORACLE_SID=$sid_parm
case $ORACLE_SID in
instance_name1)
export ReportFile=/opt/oracle/product/diag/rdbms/database_name/instance_name1/database_name_monitor_alert_log.report
;;
instance_name2)
export ReportFile=/opt/oracle/product/diag/rdbms/database_name/instance_name2/database_name_monitor_alert_log.report
;;
instance_name3)
export ReportFile=/opt/oracle/product/diag/rdbms/database_name/instance_name3/database_name_monitor_alert_log.report
;;
instance_name4)
export ReportFile=/opt/oracle/product/diag/rdbms/database_name/instance_name4/database_name_monitor_alert_log.report
;;
esac
echo 'Oracle base is : ' $ORACLE_BASE
echo 'Oracle home is : ' $ORACLE_HOME
echo 'Oracle SID is : ' $ORACLE_SID
echo 'Path is : ' $PATH
echo 'Report file is : ' $ReportFile
#
## If the hour is "15" then look back 24 hours and mail out unconditionally
#
TimeOfDay=`date +"%H"` if [ $TimeOfDay == 15 ]
then
mandatory_mail="Y"
look_back=1440
else
mandatory_mail="N"
look_back=90
fi
echo 'Mandatory mail is set to : ' $mandatory_mail
echo 'Look back period is set to : ' $look_back
#
## Call routine to run SQLs
#
echo 'Script started at ' `date`
run_sql
#
## Decide if an email is required or not # decide_to_email
#
## End of script
#
echo 'Script ended at ' `date`
I needed to code a script that would read through a text file and process some data. The format of the file was as follows:
Item Id|Item Name 01|Car 02|Bus 03|Plane
The code was:
#!/usr/bin/ksh
for parm_line in `cat dean_test.txt`
do
export item_id=$(echo $parm_line | cut -f1 -d\|)
export item_name=$(echo $parm_line | cut -f2 -d\|)
if [ $item_id != "Item Id" ]; then
echo $parm_line
fi
done
My expectation was that it would skip the header line and process the three data lines. The output looked like:
Item Id|Item Name 01|Car 02|Bus 03|Plane
This was being caused by the fact that the header row had spaces in the names of the fields. I had to change the code as follows and add the IFS:
#!/usr/bin/ksh
IFS=$'\n'
for parm_line in `cat dean_test.txt`
do
export item_id=$(echo $parm_line | cut -f1 -d\|)
export item_name=$(echo $parm_line | cut -f2 -d\|)
if [ $item_id != "Item Id" ]; then
echo $parm_line
fi
done
This change fixed the issues and I got the correct output:
01|Car 02|Bus 03|Plane
The short snippet of code was part of a much larger script. I could not use a WHILE loop as I was going to be issuing ssh commands.
We were encountering issues writing to a mount point on a server:
[test-server] ls > dean -bash: dean: No space left on device
The file system was 18% free:
[test-server] df -h Filesystem Size Used Avail Use% Mounted on /file-system 99G 77G 18G 82% /mount-point
The issue was caused by the inodes being used up:
[test-server] df -i /mount-point Filesystem Inodes IUsed IFree IUse% Mounted on /file-system 6553600 6553600 0 100% /mount-point
Working on the assumption that the largest directory would have the largest number of files
du -h /mount=point | grep '[0-9]G' | sort -nr -k1
I identified the mount point and cleaned up the files.
I was attempting to identify who had modified one of my script files. I used the stat command to identify when the modifications occurred:
[prodsrvr-PRODDB01] stat check_status.ksh File: `check_status.ksh' Size: 1117 Blocks: 8 IO Block: 4096 regular file Device: 17c0fc01h/398523393d Inode: 473 Links: 1 Access: (0644/-rw-r--r--) Uid: (24718/ oracle) Gid: (24718/ dba) Access: 2017-01-11 19:03:35.972217538 -0500 Modify: 2016-12-19 15:10:07.556675538 -0500 Change: 2016-12-19 15:10:07.000000000 -0500
The above indicates that the modification occurred on December 12, 2016 at 15:10.
I then issued the last command and grepped for Dec to see who was logged on at that date/time:
[prodsrvr-PRODDB01] last | grep -i Dec user01 pts/1 144.28.20.203 Tue Dec 20 19:18 - 19:33 (00:15) user02 pts/0 10.69.96.57 Tue Dec 20 19:10 - 22:22 (1+03:11) user03 pts/0 139.49.5.29 Mon Dec 19 15:06 - 21:41 (06:34) user05 pts/0 144.28.21.98 Mon Dec 19 11:54 - 13:20 (01:26)
This indicates a very strong possibility that user03 performed the modification. While not 100% accurate it is a reasonable way to narrow the list of users who may have made the modification.
Sends the first 25 lines of the top output to a file in batch mode. The “-n 1” runs the top command one time
top -b -n 1 | head -25 > top_data.txt
The mpstat command display activities for each available processor, Continue reading “Some Unix commands to check the performance of a server”
#!/bin/ksh
ary_ndx=1
ary_val[$ary_ndx]="First"
ary_ndx=2
ary_val[$ary_ndx]="Second"
a=0
while [ $a -le $ary_ndx ]
do
echo ${ary_val[$a]}
a=`expr $a + 1`
echo $a
done
Displays comparison of paging; 1 second apart; 2 times.
[prodsrvr] sar -p 1 2 Linux 2.6.32-642.3.1.el6.x86_64 (prodsrvr) 11/22/2016 _x86_64_ (24 CPU) 01:50:21 PM CPU %user %nice %system %iowait %steal %idle 01:50:22 PM all 1.43 0.00 0.97 4.16 0.00 93.44 01:50:23 PM all 1.26 0.00 1.35 0.97 0.00 96.43 Average: all 1.35 0.00 1.16 2.56 0.00 94.93
The mpstat command display activities for each available processor, processor 0 being the first one. Global average activities among all processors are also reported. It also shows the linux version.
[prodserver] mpstat -P ALL Linux 2.6.32-642.3.1.el6.x86_64 (prodsrvr) 11/22/2016 _x86_64_ (24 CPU) 01:43:12 PM CPU %usr %nice %sys %iowait %irq %soft %steal %guest %idle 01:43:12 PM all 10.29 0.05 1.68 5.06 0.00 0.09 0.00 0.00 82.83 01:43:12 PM 0 14.39 0.22 3.17 24.52 0.00 0.40 0.00 0.00 57.30 01:43:12 PM 1 13.98 0.20 3.42 24.63 0.00 0.56 0.00 0.00 57.22 01:43:12 PM 2 12.93 0.07 2.64 25.44 0.00 0.25 0.00 0.00 58.68 01:43:12 PM 3 12.78 0.07 2.10 2.03 0.00 0.04 0.00 0.00 82.98 01:43:12 PM 4 13.17 0.04 1.70 1.93 0.00 0.06 0.00 0.00 83.11 01:43:12 PM 5 12.06 0.03 1.61 0.91 0.00 0.03 0.00 0.00 85.37 01:43:12 PM 6 8.90 0.02 1.43 2.79 0.00 0.12 0.00 0.00 86.73 01:43:12 PM 7 9.85 0.03 1.83 24.87 0.00 0.19 0.00 0.00 63.24