Shell Scripting

Linux/Unix Command for DBA
Solaris Command for DBA
SQL Query html report
Application csv report
Application compresslogs
Application Backupdownloader
convert classical dictionary to standrad xml format
Check RAC Database Instances
Find specific error in alertlog

Linux Command for DBA

su vs sudo

Both su and sudo are used to run commands with root permissions

Su switches you to the root user account and requires the root 
account’s password. Sudo runs a single command with root privileges 
– it doesn’t switch to the root user or require a separate root user password.
sudo - execute a command as another user 
Using sudo command, an user can execute root only commands.

more

nohup command
Most of the time you login into remote server via ssh. If you start a shell script or command and you exit (abort remote connection), the process / command will get killed. Sometime job or command takes a long time. If you are not sure when the job will finish, then it is better to leave job running in background. However, if you logout the system, the job will be stopped. What do you do?

use nohup utility which allows to run command./process or 
shell script that can continue running in the background 
after you log out from a shell:

$ ssh user@remote.server.com
# nohup pullftp.sh &
# exit

Create a tar.gz in a single command

      tar -cvzf my55.tar.gz getfileinfoconfigfile_3787663714670620523.txt

List the contents of a tar or tar.gz file

#List the contents of a tar file
$ tar -tvf file.tar

or
# List the contents of a tar.gz file
$ tar -ztvf file.tar.gz

http://www.cyberciti.biz/faq/list-the-contents-of-a-tar-or-targz-file/

Unix/Linux timestamps

Unix keeps 3 timestamps for each file: mtime, ctime, and atime. 
Most people seem to understand atime (access time), it is when the file was last read. 
There does seem to be some confusion between mtime and ctime though. 
ctime is the inode change time while mtime is the file modification time.

"-mtime -2" means files that are less than 2 days old, such as a file that is 0 or 1 days old.

"-mtime +2" means files that are more than 2 days old... {3, 4, 5, ...}

Linux Services

--start ftp service
service vsftpd start

Unix command for DBA

Solaris Command for DBA

SQL Query html report
#!/bin/sh
. /export/home/oracle/.profile
sqlplus /nolog << EOF
CONNECT scottrpt/*******@XE
set serveroutput on
set markup HTML ON entmap off
set lines 180
set pages 9999
spool on
spool `date '+rpt_myquery_%d%m%y%H%M%S'`.html
SELECT CIRCLE,BILLPLAN,COUNT FROM TBLBILLHIST
WHERE to_char(DAT_TRAN, 'dd-mon-yyyy') = to_char(SYSDATE, 'dd-mon-yyyy') ORDER BY CIRCLE,BILLPLAN; SPOOL OFF
EXIT;
EOF

Application csv report
#!/bin/bash
cdrfile=`date '+applicationcdr_%d%m%y%H%M%S'`.csv
sqlplus -s /nolog << EOF
connect scott/tiger@XE
EXECUTE dump_table_to_csv('VWTBLRADIUSCDR','CSV_DIR','$cdrfile');
EOF
#HEADER FORMAT REMOVE THE DOUBLE QUOTE
sed 's/"//g' $cdrfile > tempcsv.csv && mv tempcsv.csv $cdrfile

compresslogs

#!/bin/sh
#Script Compress the logs of APP1 APP2 and TOMCAT
#Cleanup the logs of APP1 APP2 and TOMCAT which are older than 30 days
APP1_LOGs=/opt/APP1/logs
APP2_LOGs=/opt/APP2/logs
PCAP_LOGs=/var/tmp/APP
TOMCAT_LOGs=/opt/Tomcat/apache-tomcat-6.0.35/logs
for log_location in ${APP1_LOGs} ${APP2_LOGs} ${TOMCAT_LOGs}
do
cd ${log_location}
# Compress all the log file which has been rolled over
/usr/bin/gzip *20*.log
#ls *20*.log
done
# CleanUp the files older than 30 days
/usr/bin/find ${APP1_LOGs} -type f -mtime +30 -name "*.gz" -exec APP2 -rf {} \;
/usr/bin/find ${APP2_LOGs} -type f -mtime +30 -name "*.gz" -exec APP2 -rf {} \;
/usr/bin/find ${PCAP_LOGs} -type f -mtime +30 -name "*.gz" -exec APP2 -rf {} \;
/usr/bin/find ${TOMCAT_LOGs} -type f -mtime +30 -name "*.gz" -exec APP2 -rf {} \;

Backupdownloader

#!/bin/bash
#Script will download the logs and cdrs of APP RM and TOMCAT and stored inside the storage
USERNAME="sysadm"
PASSWORD="******"
SERVER1="10.104.140.X"
SERVER2="10.104.140.Y"
APP_LOCAL_DIR=/app/MY_Backup
APP1_RMT_DIR=/opt/app1
APP2_RMT_DIR=/opt/app2
TOMCAT_RMT_DIR=/opt/Tomcat/apache-tomcat-6.0.35
PCAP_RMT_DIR=/var/tmp/eliteAPP
CDR_RMT_DIR=/opt/data/csvfiles/Archive_CDR/NO_Interim
for SERVER_IP in ${SERVER1} ${SERVER2}
do
/usr/bin/ftp -n $SERVER_IP <<EOF
user $USERNAME $PASSWORD
bin
prompt off
lcd ${APP_LOCAL_DIR}/APP_${SERVER_IP}/APP_Logs
cd ${APP1_RMT_DIR}/logs
mget *.gz
lcd ${APP_LOCAL_DIR}/APP_${SERVER_IP}/RM_Logs
cd ${RM1_RMT_DIR}/logs
mget *.gz
lcd ${APP_LOCAL_DIR}/APP_${SERVER_IP}/TOMCAT_Logs
cd ${TOMCAT_RMT_DIR}/logs
mget *.gz
lcd ${APP_LOCAL_DIR}/APP_${SERVER_IP}/PCAP_Logs
cd ${PCAP_RMT_DIR}
mget *.gz
lcd ${APP_LOCAL_DIR}/APP_${SERVER_IP}/APP_CDRs/10.0.62.A
cd ${CDR_RMT_DIR}/10.0.62.A
mget *.csv
lcd ${APP_LOCAL_DIR}/APP_${SERVER_IP}/APP_CDRs/10.0.62.Z
cd ${CDR_RMT_DIR}/10.0.62.Z
mget *.csv
lcd ${APP_LOCAL_DIR}/APP_${SERVER_IP}/APP_CDRs/10.44.255.Y
cd ${CDR_RMT_DIR}/10.44.255.Y
mget *.csv
lcd ${APP_LOCAL_DIR}/APP_${SERVER_IP}/APP_CDRs/10.44.255.X
cd ${CDR_RMT_DIR}/10.44.255.x
mget *.csv
quit
EOF
done

Script to convert classical dictionary to standrad xml format
#function hello world defined

TMP_FILE=/tmp/tmp.txt

clear
######################HEADER######################################
echo “Script To Convert Classical Radius Dictioanry to Standrad .xml Format :”
echo “_______________________________________________________________________”
echo -e “”
zero=0
########################READ FILE FUNCTION#########################
readSfile()
{
while [ 1 ]
do
echo -n “Enter Name of File :”;
read source_file
if [ -f $source_file ]
then
echo “File $souce_file available”
return
else
echo “File $source_file Not available”
fi
done
}
readDfile()
{
while [ 1 ]
do
echo -n “Enter name of .xml file : ”
read dest_file
if [ -f $dest_file ]
then
echo ” Filename : $dest_file is in use Please choose different namea:”
else
return
fi

done
}

###############READING CLASSICAL FILE##############################
findValue()
{
grep $1 $source_file | grep VALUE > $TMP_FILE
if [ $? -eq 0 ]
then
echo “” >> $dest_file

while read line
do
if [ ! -z “$line” ]
then
echo $line | awk ‘{print “”}’ >> $dest_file
fi
done < $TMP_FILE

echo “”>> $dest_file
fi
}

readSfile #Source File Read Function
readDfile #Destination file Function

echo “$dest_file created” >> file_created # To track converted number of file
echo “” >> $dest_file #Initial xml tag insertion

while read line
do
var=`echo $line | awk ‘{print $1}’`
strt_tlv=`echo $line | grep “tlv” | awk ‘{print $4}’`
end_tlv=`echo $line | grep “END-TLV” | awk ‘{print $1}’`
VENDORNAME=`echo $line | awk ‘{print $2}’`
VENDORID_TLVID=`echo $line | awk ‘{print $3}’`
VENDORTYPE=`echo $line | awk ‘{print $4}’`
if [ “$var” = ‘VENDOR’ ]; then
echo “” >> $dest_file
elif [ “$var” = ‘ATTRIBUTE’ ]; then
if [ “$strt_tlv” = ‘tlv’ ]; then
echo “” >> $dest_file
# elif [ “$strt_tlv” != ‘tlv’ ]; then
else
echo “” >> $dest_file
fi

findValue $VENDORNAME

elif [ “$end_tlv” = ‘END-TLV’ ]; then
echo “” >> $dest_file
fi
done < $source_file

###########FINAL CONVERTED FILE#########################################
echo “” >> $dest_file
clear
if [ -f $dest_file ]
then
echo “$dest_file.xml Genrated”
else
echo “File Not Genrated”
fi
echo -e “_________________________________________________________________________________”

#DO the clean up
rm $TMP_FILE

 

Check RAC Database Instances

How to find the database in which particular service is attached to when you have a large number of databases running in the server, you cannot check one by one manually

Write a shell script to read the database name from oratab and iterate the loop taking inpt as DB name in srvctl to get the result.

#!/bin/ksh
ORACLE_HOME=
PATH=$ORACLE_HOME/bin:$PATH
LD_LIBRARY_PATH=${SAVE_LLP}:${ORACLE_HOME}/lib
export TNS_ADMIN ORACLE_HOME PATH LD_LIBRARY_PATH
for INSTANCE in `cat /etc/oratab|grep -v "^#"|cut -f1 -d: -s`
do
export ORACLE_SID=$INSTANCE
echo `srvctl status service -d $INSTANCE -s $1| grep -i "is running"`
done

Q How to export and import crs resources while migrating Oracle RAC to new server.

Below script generate svrctl add script for database, instance, service and 11G listeners from OCR from current RAC.
Save the result of the script and run it at new RAC.

for DBNAME in $(srvctl config database)
do

# Generate DB resource

srvctl config database -d $DBNAME -a | awk -v dbname="$DBNAME" \
'BEGIN { FS=":" }
$1~/Oracle home/ || $1~/ORACLE_HOME/ {dbhome = "-o" $2}
$1~/Spfile/ || $1~/SPFILE/ {spfile = "-p" $2}
$1~/Disk Groups/ {dg = "-a" $2}
END { if (avail == "-a ") {avail = ""}; printf "%s %s %s %s %s\n", "srvctl add database -d ", dbname, dbhome, spfile, dg }'

# Generate Instance resource

srvctl status database -d $DBNAME | awk -v dbname="$DBNAME" \
'$4~/running/ { printf "%s %s %s %s %s %s\n", "srvctl add instance -d ",dbname, " -i ", $2 ," -n ", $7 }
$5~/running/ { printf "%s %s %s %s %s %s \n", "srvctl add instance -d ",dbname, " -i ", $2 ," -n ", $8 }'

# Modify instance for 10G - ASM dependency

if [ $(echo $ORACLE_HOME | grep "1020" | wc -l ) -eq 1 ]
then
srvctl status database -d $DBNAME | awk -v dbname="$DBNAME" \
'$2~/1$/ { printf "%s %s %s %s %s \n", "srvctl modify instance -d ",dbname, " -i ", $2 ," -s +ASM1" }
$2~/2$/ { printf "%s %s %s %s %s \n", "srvctl modify instance -d ",dbname, " -i ", $2 ," -s +ASM2" }
$2~/3$/ { printf "%s %s %s %s %s \n", "srvctl modify instance -d ",dbname, " -i ", $2 ," -s +ASM3" }
$2~/4$/ { printf "%s %s %s %s %s \n", "srvctl modify instance -d ",dbname, " -i ", $2 ," -s +ASM4" }'
fi

echo "srvctl start database -d $DBNAME"

# Generate Service resource

snamelist=$(srvctl status service -d $DBNAME | awk '{print $2}')

for sname in $snamelist
do
srvctl config service -d $DBNAME -s $sname| awk -v dbname="$DBNAME" -v sname=$sname \
'BEGIN { FS=":"}
$1~/Preferred instances/ {pref = "-r" $2}
$1~/PREF/ {pref = "-r" $2; sub(/AVAIL/, "", pref) }
$1~/Available instances/ {avail = "-a" $2}
$2~/AVAIL/ {avail = "-a" $3}
$1~/Failover type/ {ft = "-e" $2}
$1~/Failover method/ {fm = "-m" $2}
$1~/Runtime Load Balancing Goal/ {g = "-B" $2}
END { if (avail == "-a ") {avail = ""}; printf "%s %s %s %s %s %s %s %s %s %s\n", "srvctl add service -d ",dbname, "-s ", sname, pref, avail ,ft, fm,g, "-P BASIC"}'
echo "srvctl start service -d $DBNAME -s $sname"
done
done

# Listener at 11G Home. 10G listener can't ba added with srvctl.

srvctl config listener | awk \
'BEGIN { FS=":"; state = 0; }
$1~/Name/ {lname = "-l" $2; state=1};
$1~/Home/ && state == 1 {ohome = "-o" $2; state=2;}
$1~/End points/ && state == 2 {lport = "-p " $3; state=3;}
state == 3 {if (ohome != "-o ") {printf "%s %s %s %s\n", "srvctl add listener ", lname, ohome, lport;} state=0;}'

Find specific error in alertlog

grep 'Global Enqueue Services Deadlock' /u01/app/oracle/diag/rdbms/orcl/orcl2/trace/alert_orcl2.log -a5 -b5

grep 'Global Enqueue Services Deadlock' /u01/app/oracle/diag/rdbms/orcl/orcl1/trace/alert_orcl1.log -a5 -b5
Advertisement

Leave a Reply

Fill in your details below or click an icon to log in:

Gravatar
WordPress.com Logo

You are commenting using your WordPress.com account. ( Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. ( Log Out /  Change )

Cancel

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.