Chapter 3

1. Chapter 3 Configuring an Efficient Environment

2. 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.

3. Leveraging OS Tools for Adminstration Efficiency • Operating system variables • Shell aliases • Shell functions • Shell scripts • SQL scripts • Others?

4. Customizing OS Command Prompt $ PS1='[\h:\u:${ORACLE_SID}]$ ' $ PS1='[\h:\u:\w:${ORACLE_SID}]$ ' $ PS1='[\u@${ORACLE_SID}@\h:\w]$ '

5. Customizing SQL Prompt • 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.> '

6. Creating Shortcuts for OS Commands • Operating system variables • Operating system aliases • Operating system functions

7. 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'

8. 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

9. 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 } #------------------------------------------

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_fcnsfile from a startup file such as .bashrc • For example, place this code in .bashrc . $HOME/bin/dba_fcns

11. 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

12. 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

13. Scripts for Efficiency • Set common variables and aliases • Setup commonly used functions • Check for issues with your database environment

14. 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.

15. 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" dkuhn@sun.com else echo "db ok" fi # exit 0

16. 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_timeDESC) where rownum < 11;

17. 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_idblkd_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;

18. 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.

19. 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.