Experiences of a DBA
If you need to send an email from the command line or a script that contains both a mail body and an attachment you can use the below command:
mailx -s "Subject of this email " -r "sent-from@somewhere.com" -a file_name.txt sent-to@somewhere.com << EOF This will be the first line of the body of the email. 2nd line. 3rd line. EOF
I had a file similar to the one shown below
$ more dean.txt 2017-12-02 18:16:57 17279 [ERROR] ** TEST ERROR ** Error_code: 1007
When I ran the below command from the command line, it displays the correct information:
$ cat dean.txt | grep -i error_code 2017-12-02 18:16:57 17279 [ERROR] ** TEST ERROR ** Error_code: 1007
However, when I set up an environment variable as:
$ DEAN=`cat dean.txt | grep -i error_code`
And I run the command via the environment variable:
$ echo $DEAN 2017-12-02 18:16:57 17279 [ERROR] 0 bin COPYING COPYING.AGPLv3 COPYING.GPLv2 COPYING-jemalloc dean.txt docs include lib log_archives man my.cnf my.cnf.bak.20171121 my.cnf.dean.bak mysql-files mysql-test PATENTS README.md README.MySQL share support-files TEST ERROR 0 bin COPYING COPYING.AGPLv3 COPYING.GPLv2 COPYING-jemalloc dean.txt docs include lib log_archives man my.cnf my.cnf.bak.20171121 my.cnf.dean.bak mysql-files mysql-test PATENTS README.md README.MySQL share support-files Error_code: 1007
It lists all the files in the directory because it is interpreting the “*” in the file’s contents as a listing of all files.
Change the file contents to:
$ more dean.txt 2017-12-02 18:16:57 17279 [ERROR] TEST ERROR Error_code: 1007
And reset the environment variable:
$ DEAN=`cat dean.txt | grep -i error_code`
And execute the command
$ echo $DEAN 2017-12-02 18:16:57 17279 [ERROR] TEST ERROR Error_code: 1007
The workaround is to put the variable in quotes as shown below.
$ more dean.txt 2017-12-02 18:16:57 17279 [ERROR] ** TEST ERROR ** Error_code: 1007 $ DEAN=`cat dean.txt | grep -i error_code` $ echo "$DEAN" 2017-12-02 18:16:57 17279 [ERROR] ** TEST ERROR ** Error_code: 1007
I was performing an scp from an older server to a newer server with the command:
scp local_file remote_user@remote_server.something.com:/remote_directory
and got an error message:
no matching cipher found: client 3des-cbc,blowfish-cbc,cast128-cbc server aes128-ctr,aes192-ctr,aes256-ctr,aes256-cbc lost connection
I was ble to get around this by explicitly providing the cipher:
scp -o Ciphers=aes256-ctr local_file remote_user@remote_server.something.com:/remote_directory
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.
My daughter asked me to create a process that would log on to her high school grade website, collect her current grades and calculate the current grade-point average. I used this as an opportunity to become familiar with Selenium. You can find more information about Selenium here:
I coded this in Python and the below blog posting contains the relevant parts of the script with explanation. I was running this on Kali Linux as root.
Assuming that you already have Python installed on your system, you can get Selenium as follows:
install selenium
After you have installed Selenium, you have to install the correct driver for the browser that you intend to use. For this example, I was using the Firefox browser which needs the geckodriver. Additional information on this can be found at:
https://developer.mozilla.org/en-US/docs/Mozilla/QA/Marionette/WebDriver
The geckodriver location has to be added to the PATH variable. As an example:
export PATH=$PATH:/path/to/geckodriver
At the top of the program, after the usual imports for Python, add in the Selenium commands:
#!/usr/bin/python # import sys import time import string from decimal import * from selenium import webdriver from selenium.webdriver.common.keys import Keys
This routine will set up the username and password and then open the browser:
def read_web_page():
user_name = 'user name'
pass_word = 'password'
#
## Open the browser
#
driver = webdriver.Firefox()
Navigate to the web page with:
driver.get("https:website name.jsp?status=login")
Confirm that you at the correct web page with:
assert "Campus Parent Portal Login" in driver.title
temp = driver.current_url
print 'Now on page ' + temp
Now that we are on the login page, find the elements username and password and supply the information from the variables created previously:
elem = driver.find_element_by_name("username")
elem.send_keys(user_name)
elem = driver.find_element_by_name("password")
elem.send_keys(pass_word)
Click on the sign in button and wait for 30 seconds for the website to respond:
driver.find_element_by_css_selector("input[src*=btn_sign_in]").click()
time.sleep(30)
Confirm that we are at the grades web page:
#
## click on grades
#
temp = driver.current_url
print 'Now on page ' + temp
This web page had multiple frames. The following command switches to the frame detail page, finds the element grades and clicks on it. After the click is complete wait for 20 seconds for the website to respond:
driver.switch_to_frame("frameDetail")
driver.find_element_by_link_text("Grades").click()
time.sleep(20)
After the website has responded (i.e. the grades are being displayed), grab the contents of the web page (i.e. page source) and convert all characters to “printable characters”:
#
## get the grades
#
temp = driver.page_source
printable = set(string.printable)
temp2 = filter(lambda x: x in printable, temp)
Write the page source to a file as text:
grades_file = open('/dean/python/grades_file_raw.txt','w')
grades_file.write(temp2)
grades_file.close()
Sign out of the web site:
#
## Sign out
#
#driver.switch_to_frame("frameDetail")
driver.find_element_by_link_text("Sign Out").click()
time.sleep(10)
This particular website had an alert window that pops up with the question “Do you really want to log off?”. The below command switch to the alert window and accept the alert indicating consent to log off:
alert = driver.switch_to_alert()
alert.accept()
time.sleep(10)
driver.quit()
All of the above processing involves the selenium driver. Now that the information is available as text in a file, I was able to parse it with the regular Python commands. Some of these are shown below as examples:
grades_file_raw = open('/dean/python/grades_file_raw.txt','r')
grades_file_fin = open('/dean/python/grades_file_fin.txt','w')
prev_teacher = ""
for raw_line in grades_file_raw:
printable = set(string.printable)
temp = filter(lambda x: x in printable, raw_line)
raw_line = temp
if 'div id="studentName"' in raw_line:
student = cut_str(2, '>', '<', raw_line)
out_line = student grades_file_fin.write('%-60s\n' %(out_line))
out_line = '-' * len(student) grades_file_fin.write('%-60s\n' %(out_line))
Additional code removed for brevity.
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`
Sometimes in JCL, there is a need to have a more flexible logic to see condition codes. Examples include: day of week, day of month, hour, datacenter, etc. The attached Rexx program gives a format to build IF logic based on anything. We have also used it set condition codes if a task/job is running or if a task/job is not running.
REX Set JCL CC based on REXX IF (eg. day, hour, etc)
Author: Jim Poole
Mainframe database administrator at a large telecommunications company. Over 40 years experience.
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.
I had a need to look for certain commands in a list of Oracle audit logs what had been tarred and zipped (*.tar.gz format). I created a list with an ls command:
ls *.gz > dean.txt
I then used the below python code to read through the zipped tar balls looking for certain strings:
#!/usr/bin/python
import tarfile,os
import sys
list_of_tar = open('dean.txt', 'r')
for tar_name in list_of_tar:
tar_name = tar_name.rstrip()
print tar_name
tar = tarfile.open(tar_name,'r:gz')
for member in tar.getnames():
file_name=tar.extractfile(member)
for line in file_name:
line = line.lower()
if 'alter ' in line:
if ' system ' in line:
if 'kill' in line:
print tar_name, member , line
tar.close()
This was a quick and dirty python to fulfill an immediate need. With more time the search condition could be improved.
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.