Unix « Oracle DBA – Tips and Techniques

Unix – tar and gzip commands

tar and compress a bunch of datafiles and then untar and uncompress them

cd /u02/oradata/test- (area of database files )

Now tar and compress and copy the files to backup area – /u02/oradata/test_bkup and name the file as test.tar.gz

tar cvf – * |gzip -c >/u02/oradata/test_bkup/test.tar.gz –

cd /u02/oradata/test_bkup > ls -lrt

-rw-r–r– 1 ofsad1 dba 105952962 Feb 26 11:31 test.tar.gz

Now to untar and uncompress the files back to the original area -

cd /u02/oradata/test

gzip -dc < /u02/oradata/test_bkup/test.tar.gz | tar xvf -

Unix for the Oracle DBA – Part 1

Locating Files under a particular directory

find . -print |grep -i test.sql

Using AWK in UNIX

To remove a specific column of output from a UNIX command – for example to determine the UNIX process Ids for all Oracle processes on server (second column)

ps -ef |grep -i oracle |awk ‘{ print $2 }’

Changing the standard prompt for Oracle Users

Edit the .profile for the oracle user

PS1=”`hostname`*$ORACLE_SID:$PWD>”

Display top 10 CPU consumers using the ps command

/usr/ucb/ps auxgw | head –11

Show number of active Oracle dedicated connection users for a particular ORACLE_SID

ps -ef | grep $ORACLE_SID|grep -v grep|grep -v ora_|wc –l

Display the number of CPU’s in Solaris

psrinfo -v | grep “Status of processor”|wc –l

Display the number of CPU’s in AIX

lsdev –C | grep Process|wc –l

Display RAM Memory size on Solaris

prtconf |grep -i mem

Display RAM memory size on AIX

First determine name of memory device

lsdev -C |grep mem

then assuming the name of the memory device is ‘mem0’

lsattr -El

Continue reading Unix for the Oracle DBA – Part 1

Performance Tuning Tips and Techniques

Checks to be performed at the machine level (note the example is Red Hat Linux specific)

run queue should be ideally not more than the number of CPU’s on the machine

At the maximum it should never be more than twice the number of CPU’s.

This is denoted by the column ‘r’ in the vmstat output shown below

vmstat – 5

face:/opt/oracle>vmstat 5
procs memory swap io system cpu
r b swpd free buff cache si so bi bo in cs us sy id wa
4

Continue reading Performance Tuning Tips and Techniques

Data Guard Log Shipping Report

If we have a number of Data Guard physical standby database environments to manage, the following report can help us quickly identify the log transported and log applied status of the Primary and Standby databases in our environment and if any standby database is lagging behind the primary as well.

The script can also be customised so as to email an alert notification if the standby and the primary go out of sync by say 5 log files. Drop me a line if you need that customisation script.

This report is based on a Unix shell script (check_logship.sh) which in turn calls a SQL script (check_logship.sql).

The script requires a user MONITOR to be created in each target database with the CONNECT and SELECT ANY DICTIONARY privileges. We also have a config file (in our case bw_dg.lst) which will contain the list of all the TNS aliases of the Primary databases which

Continue reading Data Guard Log Shipping Report

Add color to the Unix PS1 prompt

This has been tested on an AIX box -

To customize the colour of the message on login by updating the profile called on login for a user into a unix machine update the profile with the following -

Red=”33[31m"
RedBG="33[41m"
LightRed="33[1;31m"
Green="33[32m"
GreenBG="33[42m"
Yellow="33[33m"
YellowBG="33[43m"
Blue="33[34m"
BlueBG="33[44m"
Purple="33[35m"
PurpleBG="33[45m"
BoldON="33[1m"
BoldOFF="33[22m"
NC="33[0m" # No Color
MESSAGE=`echo "$Green$BoldON[DEV]$BoldOFF$NC”`
export MESSAGE
Bold=$(tput smso)
Normal=$(tput sgr0)
export PS1=”$MESSAGE \${ORACLE_SID}:\$PWD > ”

This entry will show a message which points to the machine being a DEV machine and in Green -

Example – The DEV below will show up in colour GREEN -

[DEV] ofsad1:/u01/ofsad1

Customizing the Unix prompt (PS1)

We can customize the .profile on Unix to make the prompt display (variable PS1) more meaningful as well as provide some useful shortcuts.

For example, this is the .profile we use on one of the production AIX machines.

This results in the prompt display as shown below where we display the type of the machine using a color combination (in this case Red is used for all Production machines) , the current ORACLE_SID and the present working directory.

[PROD] rcatp:/u01/oracle

stty erase ^?
export ORACLE_SID=rcatp
alias sql=”sqlplus sys/xxx as sysdba”
set -o vi
export EDITOR=/usr/bin/vi
LOGS=/u01/local/bwdba/backup/logs;export LOG
export SCRPT=/u01/local/bwdba/scripts
Green=”33[32m"
Red="33[31m"
Yellow="33[33m"
BoldON="33[1m"
BoldOFF="33[22m"
NC="33[0m" # No Color
MESSAGE=`echo "$Red$BoldON[PROD]$BoldOFF$NC”`
export MESSAGE
Bold=$(tput smso)
Normal=$(tput sgr0)
export PS1=”$MESSAGE \${ORACLE_SID}:\$PWD > ”

Including aliases and shortcuts make navigation very easy and enables us to quickly locate files when required.

For example to check the alert log we just need to do

$ cd $BDUMP

Or, to check the days RMAN backup logs, we just need to do

$ cd $LOG

Data Guard Switchover Unix shell script

The following Unix shell scripts can be used to automate the Data Guard Switchover process of a physical standby database.

It is very important that the scripts are run in the correct order and on right machine.

These scripts are based on a few customisations. The *.sh scripts are located in a directory called “/var/opt/oracle/dataguard” and there is another file called “set$ORACLE_SID” which is located under “/var/opt/oracle/cronjobs”. This script sets the environment for the individual Oracle instance like the $ORACLE_SID and the $ORACLE_HOME.

On the machine where the Primary Database is running we need to run the following scripts

$ cd /var/opt/oracle/dataguard
$ ./pre_switchover_check.sh
$ ./make_me_standby.sh

On the machine where the Standby Database is running we need to run the following script:

$ cd /var/opt/oracle/dataguard
$ ./make_me_primary.sh

After the switchover is completed, we need to run the following script on the machine where the former Primary (now new Standby) database is running:

$ cd /var/opt/oracle/dataguard
$ ./start_recovery.sh

pre_switchover_check.sh

!/bin/ksh

if [ "$1" = "" ]
then
echo

Continue reading Data Guard Switchover Unix shell script

Unix – remove file with unseen characters

This works on AIX – Please test on other systems.

If a file name is vague and can’t be removed.

Ex – a file name has some hidden characters which prevents it’s removal as the name is not recognized by the rm command.

ls -lai sqlnet.log
19462 -rw-r—– 1 oraofsap dba 7741 Jun 4 13:14 sqlnet.log

look for number against file and rm number .

find . -inum 19462 -exec rm {} \;

List and remove all files older than 30 days

find /u01/local/logs -mtime +30 -exec ls -l {} \;

find /u01/local/logs -mtime +30 -exec rm {} \;

How to find files based on size in Unix

For example – to find all files > 100 MB in size
find . -size +200000 -exec ls -l {} \;

Oracle DBA – Tips and Techniques

That’s Me

Feedback

Categories

Archives

Recent Posts

Administration