Posted by: Monish | March 12, 2019

Effective UNIX commands for Oracle DBA

One of the most important skill set you require as an Oracle database administrator is UNIX scripting and good grip on UNIX commands. In this post I’m not going to explain all the basic UNIX commands; but some of them which find very useful during our day-to-day support.

 

Process Status:

This ps command will help you to identify the instances and/or databases process are up and running in a particular server.

ps –ef |grep –i pmon

ps –ef|grep –i ora

ps –ef|grep –i tns

 

Find big Directories:

When you receive an alert like one of the oracle owned filesystem has hit the threshold limit, below command will help you to identify the big directories need your attention first.

du -sk * | sort -nk 1 | tail

bigdir

 

Remove ^M from files

Sometimes you may have experience ^M character at the end of every line once you move some .sql or .ksh file from external source to your unix server and open it with your vi editor.  Use this command in your vi editor in command mode to replace this character with blank.

:%s/[ctrlkey+v ctrl-key+M]//g

viM

You can use this same command to replace any specific word with a new one. In this below example I have replace the word “TEST” with “ORACLE” in the same file.

 

Remove one week old logs

During any server housekeeping activity you have identified some old log files which are consuming some significant space. Now use this below script to remove them except the last 7 days files. The file type and the timestamp values can be changed based on your requirement.

find . -name "*.xml" -print -mtime +7 -exec rm -f {} \;

 

 

AIX Commands to collect server resource details

prtconf |more

prtconf | grep -i "Processor Type"

prtconf | grep -i "bit"

lparstat -i | grep CPU

 

FTP script to copy file

This below ftp shell script is an example to move export backup dumps.

HOST=<Server Name>
USER=<username>
PASSWD=<password>

exec 4>&1

ftp -nv >&4 2>&4 |&

print -p open $HOST
print -p user $USER $PASSWD
print -p cd /ora/dev/bkup
print -p lcd /opt/bkup/MB
print -p binary
print -p put expdp_325567.log
print -p binary
print -p put expdp_325567.dmp
print -p bye
wait
exit 0

 

Mailx Commands

Modify these mailx commands as per your need and use them with your shell script.

Show log in email body:
mailx -s "EXPORT LOG - $ORACLE_SID" $DBAS < $LOGFILE     

Attach log in email:
uuencode ${REPORT_FILE} ${REPORT_FILE} | mailx -m -s "Report for $ORACLE_SID" ${EMAIL_USER}

 

Find Memory size/details in different env:

meminfo  --> Linux

grep Physical /var/adm/syslog/syslog.log --> HP server

/usr/sbin/prtconf | grep -i mem  -->solaris

AIX  Top 15 memory consuming process:
svmon -Pt15 | perl -e 'while(<>){print if($.==2||$&&&!$s++);$.=0 if(/^-+$/)}'

 

SCP

In this below command we are moving an export dump file from our logged in source server to “Hostname01” server’s “/oradata01/bkp” directory.

this command prompt you for the password of the oracle user of hostname01 server.

scp -p /oracle/backups/exp/exp_tab_03112018.exp oracle@Hostname01:/oradata01/bkp

 

Some useful Solaris Commands:

# CHECK PROCESS STATUS
prstat

#CHECK CPU UTILIZATION
sar 2 5

# ORATAB
cat /var/opt/oracle/oratab

# 32/64 bit
isainfo -b

 

topas

The topas command reports selected statistics about the activity on the local system.

This command can be used to identify which process is consuming more resource and server resource status.

top

 

Hope this post will be helpful for you. Please leave a comment below with your thoughts. Let me know if you have any queries and suggestions, will try to reply ASAP.

Following are some of the great books to master this particular area. Use the below links to grab your copy and move up your level.

UNIX CONCEPTS AND APPLICATIONS

Unix for Oracle DBAs Pocket Reference: Command Syntax and Scripts 

Linux Command Line and Shell Scripting Bible

Learning Linux Shell Scripting

Thanks for your time!

Advertisements

Leave a Reply

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

WordPress.com Logo

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

Google photo

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

Twitter picture

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

Facebook photo

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

Connecting to %s

Categories

%d bloggers like this: