Transcript Chapter 1

CHAPTER 3
Configuring an Efficient Environment
Introduction
• After you install 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.
• A database administrator who takes advantage of the
operating system and SQL has a clear advantage over a
DBA who doesn’t.
Leveraging OS Tools for Adminstration
Efficiency
• Operating system variables
• Shell aliases
• Shell functions
• Shell scripts
• SQL scripts
• Others?
Customizing OS Command Prompt
$ PS1='[\h:\u:${ORACLE_SID}]$ '
$ PS1='[\h:\u:\w:${ORACLE_SID}]$ '
$ PS1='[\u@${ORACLE_SID}@\h:\w]$ '
Customizing SQL Prompt
1.
2.
3.
Create a file named login.sql, and place in it the SET SQLPROMPT
command.
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
Create a file named login.sql in the HOME/scripts directory. Place
the following line in the file:
SET SQLPROMPT '&_USER.@&_CONNECT_IDENTIFIER.> '
Creating Shortcuts for OS Commands
• Operating system variables
• Operating system aliases
• Operating system functions
Using Aliases
alias l.='ls -d .*'
alias ll='ls -l'
alias lsd='ls -altr | grep ^d'
alias sqlp='sqlplus "/ as sysdba"'
alias shutdb='echo "shutdown immediate;" | sqlp'
alias startdb='echo "startup;" | sqlp'
Establishing Aliases on OS Logon
• Create file to contain aliases, named something like
dba_setup
• Place the file in a standard directory that you’ve creatd,
like $HOME/bin
• Call the dba_setup file from a startup file such as .bashrc
• For example, place this code in .bashrc
. $HOME/bin/dba_setup
Using Functions
#-----------------------------------------------------------#
# 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
}
#------------------------------------------
Establishing Functions on OS Logon
• Create file to contain function code, named something like
dba_fcns
• Place the file in a standard directory that you’ve created,
like $HOME/bin
• Call the dba_fcns file from a startup file such as .bashrc
• For example, place this code in .bashrc
. $HOME/bin/dba_fcns
Rerunning Operating System Commands
• Scrolling with the up and down arrow keys (bash)
• Using Ctrl+P and Ctrl+N
• Listing the command history
• Searching in reverse
• Setting the command editor
Setting Command Editor
set –o vi
• Sets your command line editor to be the OS vi text editing
utility
• Allows you to quickly use vi commands to search for and
re-run, or modify prior commands
Scripts for Efficiency
• Set common variables and aliases
• Setup commonly used functions
• Check for issues with your database environment
Two General Types of Scripts
• Operating system shell scripts that typically contain SQL
or PL/SQL. Shell scripts are usually run in an automated
fashion like from cron.
• SQL scripts used to troubleshoot various issues. Usually
the DBA runs these manually while checking various
aspects of the database.
Example Shell Script: conn.bsh
#!/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@INVPRD | grep success
if [[ $? -ne 0 ]]; then
echo "problem with $1" | mailx -s "db problem" [email protected]
else
echo "db ok"
fi
#
exit 0
Example SQL Script: top.sql
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;
Example SQL Script: 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;
Create Standard Directories for Scripts
• $HOME/bin for shell scripts
• $HOME/scripts for SQL scripts
• Doesn’t matter that much what the standard is, as long as
you have a consistent standard.
• The idea is that you can logon to any server and have
your standard set of variables, aliases, functions, shell
scripts, and SQL scripts that you can use to manage your
environment efficiently.
Summary
• You should know how to configure an efficient
•
•
•
•
environment.
This is especially important for DBAs that manage
multiple databases on multiple servers.
Regular maintenance and troubleshooting activities will
require your logon directly to the database server.
To promote efficiency and sanity, you should develop a
standard set of operating system tools and SQL scripts
that will help you maintain multiple environments.
You can leverage standard features of the OS to assist
with navigating, repeating commands, showing system
bottlenecks, quickly finding critical files, and so on.