ORACLE DATABASE ADMINISTRATION
Configuring an Efficient Environment
ORACLE DATABASE ADMINISTRATION SERIES
S.N STEPHEN N NJOROGE
LinkedIn: www.linkedln.com/in/stephen-njoroge
SENIOR DATABSE ADMINISTRATOR | MSSQL | ORACLE| PostgreSQL |OCI |AWS |
DATABASE ADMINISTRATION SERIES |
Configuring an Efficient Environment
After installing the Oracle binaries and create a database, you should configure your environment to enable you to
operate efficiently.
Regardless of the functionality of graphical database administration tools, DBAs still need to perform many tasks from
the operating system command line and manually execute SQL statements.
This module lays the foundation for efficient use of the OS and SQL to manage your databases.
Customizing Operating System Command Prompt
Run the following types of commands to identify your current working environment:
$ hostname -a
$ id
$ who am i
$ echo $ORACLE_SID
$ pwd
Configure command prompt to display information regarding its environment such as the machine name and database
SID by running :
$ PS1='[\h:\u:${ORACLE_SID}]$ '
Whereas:
\h specifies the hostname, \u specifies the current operating system user and $ORACLE_SID contains the current
setting for the Oracle instance identifier.
Output:
[Itopsrv:oracle:itopdb]$
To configure it to prompt automatically when you log in, edit the .bashrc file and add the following line of code in
.bashrc:
PS1='[\h:\u:${ORACLE_SID}]$ '
Common Options to configure information displayed at the command line prompt
Variable Description
\a ASCII bell character
\d Date in “weekday month date” format
\h Hostname
\e ASCII escape character
\j Number of jobs managed by the shell
\l Base name of the shell’s terminal device
\n Newline
\r Carriage return
\s Name of the shell
\t Time in 24-hour HH:MM:SS format
\T Time in 12-hour HH:MM:SS format
\@ Time in 12-hour a.m./p.m. format
\A Time in 24-hour HH:MM format
\u Current shell
2
Configuring an Efficient Environment
\v Version of the Bash shell
\V Release of the Bash shell
\w Current working directory
\W Base name of the current working directory
\! History number of command
\$ If the effective UID is 0, then displays #; otherwise, displays $
Customizing SQL Prompt
DBAs often use SQL*Plus to perform daily administrative tasks. Often, you’ll work on servers that contain multiple
databases. Obviously, each database contains multiple user accounts.
When connected to a database, run the following commands to verify your username and database connection:
SQL> show user;
SQL> select name from v$database;
A more efficient way to determine your username and SID is to set SQL prompt to display that information.
A more efficient way to configure your SQL prompt is to have it automatically run the SET SQLPROMPT command
when you log in to SQL*Plus.
SQL> SET SQLPROMPT '&_USER.@&_CONNECT_IDENTIFIER.> '
Steps to automate this setting:
1) Create a file named login.sql, and place in it the SET SQLPROMPT command.
2) Set your SQLPATH operating system variable to include the directory location of login.sql. In this example, the
SQLPATH operating system variable is set in the .bashrc operating system file, which is executed each time a new shell is
logged in to or started. Here is the entry:
export SQLPATH=$HOME/scripts
3) Create a file named login.sql in the HOME/scripts directory. Place the following
line in the file:
SET SQLPROMPT '&_USER.@&_CONNECT_IDENTIFIER.> '
$ . ./.bashrc
Now, log in to SQL.
SYS@itopdb>
Below is a list of Predefined SQL*Plus Variables
Variable Description
Connection identifier, such as the Oracle
_CONNECT_IDENTIFIER
SID
_DATE Current date
_EDITOR Editor used by the SQL EDIT command
_O_VERSION Oracle version
_O_RELEASE Oracle release
Privilege level of the current connected
_PRIVILEGE
session
_SQLPLUS_RELEASE SQL*Plus release number
_USER Current connected user
3
Configuring an Efficient Environment
Creating Shortcuts for Frequently Used Commands
In Linux/Unix environments, you can use two common methods to create shortcuts to other
commands:
Using Aliases
If you often need to navigate to various directories on a server. To navigate to this directories, you have to type
something similar to this:
$ cd /ora01/app/oracle/admin/itop/bdump
Use the alias command to create a shortcut to accomplish the same task.
$ alias bdump='cd /ora01/app/oracle/admin/$ORACLE_SID/bdump'
Now you can type bdump, which does the same thing as changing your current working directory to the Oracle
background dump directory.
To show all aliases that have been defined:
$ alias
Some common examples of alias definitions:
alias l.='ls -d .*'
alias lsd='ls -altr | grep ^d'
alias bdump='cd /ora01/app/oracle/admin/$ORACLE_SID/bdump'
alias sqlp='sqlplus "/ as sysdba"'
alias shutdb='echo "shutdown immediate;" | sqlp'
alias startdb='echo "startup;" | sqlp'
To remove an alias lsd definition from the current environment:
$ unalias lsd
Using a Function
You can also use a function to create command shortcuts. The following line of code creates a simple
function named bdump:
$ function bdump { cd /ora01/app/oracle/admin/$ORACLE_SID/bdump; }
Using functions is usually preferable over using aliases. Functions are more powerful than aliases
because of features such as the ability to operate on parameters passed in on the command line, and
allowing for complex coding.
Sample Demo:
To demonstrate the power of functions, consider a scenario in which you have different versions of
databases installed on a server and the background-dump destination differs depending on the version.
With a function, you can build in the logic that checks to see which version of the database you’re using
and navigates accordingly:
4
Configuring an Efficient Environment
function bdump {
echo $ORACLE_HOME | grep 11 >/dev/null
if [ $? -eq 0 ]; then
lower_sid=$(echo $ORACLE_SID | tr '[:upper:]' '[:lower:]')
cd $ORACLE_BASE/diag/rdbms/$lower_sid/$ORACLE_SID/trace
else
cd $ORACLE_BASE/admin/$ORACLE_SID/bdump
fi
} # bdump
Example of functions to find largest file and directory as well as alert log.
#-----------------------------------------------------------#
# find largest files below this point
function flf {
find . -ls | sort -nrk7 | head -10
}
#-----------------------------------------------------------#
# find largest directories consuming space below this point
function fld {
du -S . | sort -nr | head -10
}
#------------------------------------------
# view alert log
function valert {
echo $ORACLE_HOME | grep 11 >/dev/null
if [ $? -eq 0 ]; then
lower_sid=$(echo $ORACLE_SID | tr '[:upper:]' '[:lower:]')
view $ORACLE_BASE/diag/rdbms/$lower_sid/$ORACLE_SID/trace/alert_$ORACLE_SID.log
else
view $ORACLE_BASE/admin/$ORACLE_SID/bdump/alert_$ORACLE_SID.log
fi
} # valert
#-----------------------------------------------------------#
If you ever wonder whether a shortcut is an alias or a function, use the type command to verify a
command’s origin. This example verifies that bdump is a function:
$ type bdump
Here is the output:
5
Configuring an Efficient Environment
bdump is a function
bdump ()
{
echo $ORACLE_HOME | grep 11;
if [ $? -eq 0 ]; then
lower_sid=$(echo $ORACLE_SID | tr '[:upper:]' '[:lower:]');
cd $ORACLE_BASE/diag/rdbms/$lower_sid/$ORACLE_SID/trace;
else
cd $ORACLE_BASE/admin/$ORACLE_SID/bdump;
fi
}
Rerunning Commands Quickly
One timesaving feature of the Bash shell is that it has several methods for editing and rerunning
previously executed commands. The following list highlights several options available for manipulating
previously typed commands:
Listing the command history
Searching in reverse
Setting the command editor
Each of these techniques is described briefly in the following sections.
Listing the Command History
Use the history command to display commands that the use previously entered:
$ history
You can limit the output to the last n number of commands by providing a number with the command.
$ history 5
To run a previously listed command in the output, use an exclamation point (!, sometimes called
the bang) followed by the history number.
$ !276
To run the last command you ran, use !!, as shown here:
$ !!
Searching in Reverse
Press Ctrl+R, and you’re presented with the Bash shell reverse-search utility:
$ (reverse-i-search)`':
Setting the Command Editor
You can use the set -o command to make your command-line editor be either vi or emacs. This example
sets the command-line editor to be vi:
$ set -o vi
6
Configuring an Efficient Environment
Developing Standard Scripts
dba_setup
This script helps a dba to set so variables and alias to start and shutdown databases.
# set prompt
PS1='[\h:\u:${ORACLE_SID}]$ '
#
export EDITOR=vi
export VISUAL=$EDITOR
export SQLPATH=$HOME/scripts
set -o vi
#
# list directories only
alias lsd="ls -p | grep /"
# show top cpu consuming processes
alias topc="ps -e -o pcpu,pid,user,tty,args | sort -n -k 1 -r | head"
# show top memory consuming processes
alias topm="ps -e -o pmem,pid,user,tty,args | sort -n -k 1 -r | head"
#
alias sqlp='sqlplus "/ as sysdba"'
alias shutdb='echo "shutdown immediate;" | sqlp'
alias startdb='echo "startup;" | sqlp'
dba_functions
Use this script to store operating system functions that help you navigate and operate in your database
environment.
7
Configuring an Efficient Environment
#-----------------------------------------------------------#
# show environment variables in sorted list
function envs {
if test -z "$1"
then /bin/env | /bin/sort
else /bin/env | /bin/sort | /bin/grep -i $1
fi
} # envs
#-----------------------------------------------------------#
# login to sqlplus
function sp {
time sqlplus "/ as sysdba"
} # sp
#-----------------------------------------------------------#
# find largest files below this point
function flf {
find . -ls | sort -nrk7 | head -10
}
#-----------------------------------------------------------#
# find largest directories consuming space below this point
function fld {
du -S . | sort -nr | head -10
}
#-----------------------------------------------------------#
# cd to bdump
function bdump {
echo $ORACLE_HOME | grep 11 >/dev/null
if [ $? -eq 0 ]; then
lower_sid=$(echo $ORACLE_SID | tr '[:upper:]' '[:lower:]')
cd $ORACLE_BASE/diag/rdbms/$lower_sid/$ORACLE_SID/trace
else
cd $ORACLE_BASE/admin/$ORACLE_SID/bdump
fi
} # bdump
#-----------------------------------------------------------#
# view alert log
function valert {
echo $ORACLE_HOME | grep 11 >/dev/null
if [ $? -eq 0 ]; then
lower_sid=$(echo $ORACLE_SID | tr '[:upper:]' '[:lower:]')
view $ORACLE_BASE/diag/rdbms/$lower_sid/$ORACLE_SID/trace/alert_$ORACLE_SID.log
else
view $ORACLE_BASE/admin/$ORACLE_SID/bdump/alert_$ORACLE_SID.log
fi
} # valert
#-----------------------------------------------------------#
8
Configuring an Efficient Environment
tbsp_check.bsh
This script checks to see if any tablespaces are surpassing a certain fullness threshold.
#!/bin/bash
#
if [ $# -ne 2 ]; then
echo "Usage: $0 SID threshold"
exit 1
fi
# either hard code OS variables or source them from a script.
# see Chapter 2 for details on the oraset script
# source oracle OS variables
. /var/opt/oracle/oraset $1
#
crit_var=$(
sqlplus -s <<EOF
system/foo
SET HEAD OFF TERM OFF FEED OFF VERIFY OFF
COL pct_free FORMAT 999
SELECT (f.bytes/a.bytes)*100 pct_free,'% free',a.tablespace_name||','
FROM
(SELECT NVL(SUM(bytes),0) bytes, x.tablespace_name
FROM dba_free_space y, dba_tablespaces x
WHERE x.tablespace_name = y.tablespace_name(+)
AND x.contents != 'TEMPORARY' AND x.status != 'READ ONLY'
AND x.tablespace_name NOT LIKE 'UNDO%'
GROUP BY x.tablespace_name) f,
(SELECT SUM(bytes) bytes, tablespace_name
FROM dba_data_files
GROUP BY tablespace_name) a
WHERE a.tablespace_name = f.tablespace_name
AND (f.bytes/a.bytes)*100 <= $2
ORDER BY 1;
EXIT;
EOF)
if [ "$crit_var" = "" ]; then
echo "space okay"
else
echo "space not okay"
echo $crit_var
echo $crit_var | mailx -s "tbsp getting full on $1" dbalerts@gmail.com
fi
exit 0
conn.bsh
This script checks to see if a connection can be established to the database. If a connection can’t be established, an e-
mail is sent.
9
Configuring an Efficient Environment
The script requires that the ORACLE_SID be passed to it. For example:
$ conn.bsh itop
Here are the contents of the conn.bsh script:
#!/bin/bash
if [ $# -ne 1 ]; then
echo "Usage: $0 SID"
exit 1
fi
# either hard code OS variables or source them from a script.
# see Chapter 2 for details on the oraset script
# source oracle OS variables
. /var/opt/oracle/oraset $1
#
echo "select 'success' from dual;" | sqlplus -s darl/foo@itop | grep success
if [[ $? -ne 0 ]]; then
echo "problem with $1" | mailx -s "db problem" dbalerts@gmail.com
else
echo "db ok"
fi
#
exit 0
filespace.bsh
Use the following script to check for an operating mount point that is filling up.
10
10
Configuring an Efficient Environment
#!/bin/bash
mntlist="/home /backups /arch /u01 /oradump01 /"
for ml in $mntlist
do
echo $ml
usedSpc=$(df -h $ml | awk '{print $5}' | grep -v capacity | cut -d "%" -f1 -)
BOX=$(uname -a | awk '{print $2}')
#
case $usedSpc in
[0-9])
arcStat="relax, lots of disk space: $usedSpc"
;;
[1-7][0-9])
arcStat="disk space okay: $usedSpc"
;;
[8][0-9])
arcStat="space getting low: $usedSpc"
echo $arcStat | mailx -s "space on: $BOX" dbalerts@gmail.com
;;
[9][0-9])
arcStat="warning, running out of space: $usedSpc"
echo $arcStat | mailx -s "space on: $BOX" dbalerts@gmail.com
;;
[1][0][0])
arcStat="update resume, no space left: $usedSpc"
echo $arcStat | mailx -s "space on: $BOX" dbalerts@gmail.com
;;
*)
arcStat="huh?: $usedSpc"
esac
#
BOX=$(uname -a | awk '{print $2}')
echo $arcStat
#
done
#
exit 0
login.sql
Use this script to customize aspects of your SQL*Plus environment. When logging in to SQL*Plus, in
Linux/Unix, the login.sql script is automatically executed if it exists in a directory contained within the
SQLPATH variable. If the SQLPATH variable hasn’t been defined, then SQL*Plus looks for login.sql in the
current working directory from which SQL*Plus was invoked.
For example, here’s how the SQLPATH variable is defined in my environment:
11
11
Configuring an Efficient Environment
$ echo $SQLPATH
/home/oracle/scripts
Create the login.sql script in the /home/oracle/scripts directory.
It contains the following lines:
-- set SQL prompt
SET SQLPROMPT '&_USER.@&_CONNECT_IDENTIFIER.> '
Now, when I log in to SQL*Plus, my prompt is automatically set:
$ sqlplus / as sysdba
SYS@itop>
top.sql
The following script lists the top CPU-consuming SQL processes. It’s useful for identifying problem SQL
statements.
Place this script in a directory such as HOME/scripts:
select * from(select sql_text,buffer_gets,disk_reads,sorts,cpu_time/1000000 cpu_sec,executions,rows_processed
from v$sqlstats
order by cpu_time DESC)
where rownum < 11;
Here’s how you execute this script:
SQL> @top
lock.sql
This script displays sessions that have locks on tables that are preventing other sessions from
completing work.
Here are the contents of lock.sql:
select s1.username blkg_user, s1.machine blkg_ws, s1.sid blkg_sid,
s2.username wait_user, s2.machine wait_ws, s2.sid wait_sid,
lo.object_id blkd_obj_id, do.owner, do.object_name
from v$lock l1, v$session s1, v$lock l2, v$session s2,
v$locked_object lo, dba_objects do
where s1.sid = l1.sid
and s2.sid = l2.sid
and l1.id1 = l2.id1
and s1.sid = lo.session_id
and lo.object_id = do.object_id
and l1.block = 1
and l2.request > 0;
12
12
Configuring an Efficient Environment
You can run this script from SQL*Plus as follows:
SQL> @lock.sql
users.sql
This script displays information about when users were created and whether their account is locked
Place it in a directory such as HOME/scripts. Here is a typical users.sql script to display user account information:
SELECT username,account_status,lock_date,created
FROM dba_users
ORDER BY username;
You can execute this script from SQL*Plus as follows:
SQL> @users.sql
13
13
Configuring an Efficient Environment
