Essential Unix Tips, Tricks, and Hints Every DBA Should Know

1. Essential UnixTips, Tricks, and Hints Every DBA Should Know GA Oracle Users Conference March 5-7, 2007 Kennesaw, GA Kristopher Cook Mirant Corporation Atlanta, GA kris.cook@mirant.com

2. Intended Audience • DBAs with less than 2 years experience • DBAs with more than 2 years experience who won’t get bored reviewing some basics

3. Ground Rules • Please hold questions until the end • Cell Phones, Pagers, etc - Quiet Mode Please!

4. Presentation Objectives • Goals • Learn some Unix tips/tricks • See some sample scripts • Cover 80+ slides < 1 hour • Non-Goals • Learn everything about Unix • Become a Unix Administrator

5. Speaker’s Environment • Mirant Corporation (NYSE:MIR) • Competitive energy company that produces and sells electricity in the U.S., the Caribbean, and the Phillippines. • Owns or leases more than 17,000 megawatts of electric generating capacity globally. • Operates an integrated asset management and energy marketing organization from our headquarters in Atlanta. • www.mirant.com

6. Speaker’s Environment(continued) • Technical • Primarily Solaris shop, ~17 DB servers • 200+ Oracle instances total; 50 in production • Sizes range from 1 to 150+ GB • 24x7 operations • Databases are local, remote, and at hotsite • Versions 10g (20%), 9i (75%) & 8i (5%) • Trading Systems, Power Scheduling, Plant Operations, Financials, HR, Intra/Inter-net • 6 full time DBAs

7. Speaker’s Background • 20+ years IT experience, primarily in Unix environments • Current position • Manager, Database Services • Previous positions (last 10 years) • Principal DBA • Database Administration Manager • Senior DBA • Advanced Systems Engineer

8. Topics of Discussion • Basic Concepts • Fundamental Unix Commands • Working with Multiple Servers • Scripts

9. 1. Basic Concepts • Unix vs UNIX • Terminology • Case Sensitivity • Unix Building Blocks • Wildcards

10. Basic Concepts Unix vs UNIX • By Unix we mean an operating system typically written in C, with a hierarchical file system, integration of file and device I/O, whose system call interface includes services such as fork(), pipe(), and whose user interface includes tools such as cc, troff, grep, awk, and a choice of shell.   • Note that UNIX was a registered trademark of USL (AT&T), now of X/Open, but will be used here in its generic sense. • Source: http://isc.faqs.org/faqs/unix-faq/faq/part6/

11. Basic Concepts Terminology • Unix Utilities • Unix Kernel • Unix Shell

12. Basic Concepts - Terminology Unix Utilities • The collection of various programs that individuals execute from the command prompt to perform a variety of tasks • Examples: • listing contents of a directory (ls) • Sorting (sort) • Displaying contents of a file (cat, more, head, tail) • Editing files (vi) • Printing (lp) • Searching files for a keyword (grep) • Changing directories (cd)

13. Basic Concepts - Terminology Unix Kernel • A program that starts running when the system is booted and runs continuously until the system is shutdown. • Functions of the kernel include file management, disk I/O, process scheduling and management, memory management, system accounting, and interrupt and error handling.

14. Basic Concepts - Terminology Unix Shell • The component that interacts with the user • A program that is executed on behalf of the user upon logon to the system and presents the command prompt (typically a $) to the user • Reads input typed into the command prompt, hands off information to the kernel, and delivers results back to the user’s screen • Environmental settings adjusted via variables • i.e. ORACLE_HOME=/u01/app/oracle

15. Basic Concepts - Terminology Unix Shell (continued) • env – Command to view environment variables • See man page for “environ” for more variables • Environment variables can be set at login time via .profile – a text file in home directory $ cat .profile export ORACLE_HOME=/u01/app/oracle/product/10202 export ORACLE_SID=orcl export LD_LIBRARY_PATH=/u01/app/oracle/product/10202/lib

16. Basic Concepts Unix Building Blocks • Pipes • Standard Output • Standard Error • Standard Input

17. Basic Concepts – Unix Building Blocks Pipes • Mechanism that allows the output from one command to be fed as the input to another • Vertical bar “|” character is the pipe operator • Example: $ cat oratab | cut –f1 –d: | sort dca drm dse dta

18. Basic Concepts – Unix Building Blocks Standard Output • a.k.a Standard Out, stdout, filehandle 1 • Default location is terminal screen • Redirect stdout to a new file with the redirect operator “>” • Append to an existing file with the concatenation operator “>>” • Redirect stdout to the input of another command with the pipe operator “|”

19. Basic Concepts – Unix Building Blocks Standard Output • Examples: ls –l *log date > rmlogfiles.txt echo “removing log files” >>logfile.txt ls –l *.log >>logfile.txt rm *.log cat oratab | cut –f1 –d: | sort 1> sids.txt

20. Basic Concepts – Unix Building Blocks Standard Error • a.k.a stderr, filehandle 2 • Default location is terminal screen • Redirect stderr to a new file with the redirect operator “2>” • Append to an existing file with the concatenation operator “2>>” • Redirect stderr to the same location as stdout using “2>&1” • Pipes typically not used with stderr

21. Basic Concepts – Unix Building Blocks Standard Error • Example: $ cat sids.txt bogusfile 1>stdout.txt 2>stderr.txt $ cat stdout.txt dca drm dse dta $ cat stderr.txt cat: cannot open bogusfile

22. Basic Concepts – Unix Building Blocks Standard Input • a.k.a Standard In, stdin, filehandle 0 • Default location is keyboard input • Redirect stdin from a new file with the redirect operator “<” • Examples: mail krc <email.txt mail krc 0<email.txt

23. Basic Concepts Wildcards • A means of matching patterns; usually in file names or when searching contents of a text file • Sometimes referred to as regular expressions • Examples: “*” An asterisk – matches any number of letters or digits “?” Question mark – matches a single letter or digit “[ ]” Square brackets – matches range of letters or digits

24. Basic Concepts Wildcard Examples (cont) • list all temp tablespace data files that contain two alphanumeric characters after the word temp in the name. i.e. temp01.dbf, tempAB.dbf ls temp??.dbf • list all files that start with a letter followed by a digit followed by any combination of letters or characters and ends with .log; i.e. a2test.log ls [a-z][0-9]*.log

25. Topics of Discussion • Basic Concepts • Fundamental Unix Commands • Working with Multiple Servers • Scripts

26. 2. Fundamental Unix Commands • Commands That Provide Information • Commands That “Do” Something

27. Fundamental Unix Commands Commands that Provide Information • Online Documentation (man command) • Examining Contents of Files • File and Directory Information • System Information

28. Fundamental Unix Commands – that provide information man command • Syntax: man command_name or man –k command_name • -k option used for keyword searches • Optional arguments specified within [ ] • Multiple arguments followed by … • Read entire man page – towards the end is list of bugs, related commands, related files, exit codes, or other information

29. Fundamental Unix Commands – that provide information Examining Contents of Files • head • tail • more • wc • view • grep

30. Fundamental Unix Commands – that provide information Examining Contents of Files head • Displays only first few lines of files. Useful in scripts to extract specified number of lines • Default is 10 lines • Syntax: head [-number] [file…] • Examples: head listener.log head –24 listener1.log listener2.log X=`cat output.txt | head -1`

31. Fundamental Unix Commands – that provide information Examining Contents of Files tail • Syntax: tail [+-number] [-f] [file…] +number – start relative to beginning of file -number – start relative to end of file -f keeps file open and continuously updates; cancel with <ctrl-C> • Examples: tail -f listener.log cat output.txt | tail +30 tail –24 alert_orcl.log

32. Fundamental Unix Commands – that provide information Examining Contents of Files more • Displays text file one page at a time • Use space bar to advance • Type “q” to quit • Type “h” for help on more options • Syntax: more [file…] • Examples: more listener.log cat listener.log | more

33. Fundamental Unix Commands – that provide information Examining Contents of Files wc • Word Count – Counts lines, words, and chars • Syntax: wc [ -lwc ] file… • Default is all • Example: (shows how many trace files) ls –l *.trc | wc -l

34. Fundamental Unix Commands – that provide information Examining Contents of Files view • Read-only version of vi editor • Same as “vi –R” command • Syntax: view file… • Type “:q” to exit

35. Fundamental Unix Commands – that provide information Examining Contents of Files grep • Global Regular Expression Print • Finds patterns within a file • Syntax: grep [-cilnv] pattern file… -c returns count of lines matching pattern -i ignores case -l only prints names of files with matches -n precede each matching line with its line # -v prints all lines except those matching pattern

36. Fundamental Unix Commands – that provide information Examining Contents of Files grep - Examples • grep –c ORA-1650 alert_orcl.log 1 • grep –i “^ora-1650” alert_orcl.log ORA-1650: unable to extend rollback segment R01 by 128 in tablespace RBS • grep –l ORA alert*.log alert_orcl.log alert_orcl2.log alert_orcl.old.log • ps –ef | grep –v oracle Shows all non-oracle owned processes

37. Fundamental Unix Commands – that provide information File and Directory Information • ls • find • du • fuser

38. Fundamental Unix Commands – that provide information File and Directory Information ls - List directory contents • Syntax: ls [-aAdlqrR1] [file…] -a lists all files including those beginning with “.” -A same as –a minus directories “.” and “..” -d if argument is a directory, only list its name -l long format including owner, group, size, time -q prints non-printable characters as a “?” -r reverse order sort -R recursively list subdirectories and their contents -1 prints only one entry per line

39. Fundamental Unix Commands – that provide information File and Directory Information find • Recursively descends directory tree to find files • Syntax: find path… options path – directory to begin searching • Options: -print prints file names found -ls prints long listing of file names found -mtime n prints file names modified n days ago -mtime +n prints file names modified >n days ago -mtime –n prints file names modified <n days ago

40. Fundamental Unix Commands – that provide information File and Directory Information find • Options (continued) -name pattern prints file names matching pattern -perm mode prints file names matching permissions -size +nc prints file names >n bytes -size –nc prints file names <n bytes -user username prints files owned by username -type c prints files of type c where c is: “l” link “f” text file “d” directory

41. Fundamental Unix Commands – that provide information File and Directory Information find Examples: • Show files greater than 2 GB find /u02/oradata –size +200000000c –ls • Show files less than 2 GB find /u02/oradata –size -200000000c –ls • Find files modified in the last 24 hours find /u01/app/oracle –type f –mtime –1 -ls • Remove trace files older than 30 days find /u01/app/oracle/admin –name “*.trc” –mtime +30 \ –exec rm {} \;

42. Fundamental Unix Commands – that provide information File and Directory Information find Examples (continued) • Find scripts where new users are created find $ORACLE_HOME –type f –name “*.sql” \ –exec grep –il “identified by” {} \; • Find scripts that might have passwords find $ORACLE_HOME –type f -exec grep –il “sqlplus” \; • Find files owned by oracle that are world readable find / -user oracle –perm –o+r –ls • Find files owned by oracle that are world writeable find / -user oracle –perm –o+w –ls

43. Fundamental Unix Commands – that provide information File and Directory Information find Examples (continued) • Find linked files find $ORACLE_HOME –type l -ls • Compress export files that are larger than 1MB and older than 30 days find $ORACLE_HOME –name “*.dmp” –size +1048576c \ -mtime +30 –exec compress {} \;

44. Fundamental Unix Commands – that provide information File and Directory Information du – Disk Usage • Syntax: du [-sk] [file…] • Options: -s sum of all files and subdirectories -k results in K bytes rather than blocks • Example: cd /u02/oradata du –sk * | sort -nr

45. Fundamental Unix Commands – that provide information File and Directory Information fuser • Identifies processes using a file • Use just before deleting a file • Syntax: fuser [-u] [file…] -u shows username too • Example: (note, 9i and above use “and datafiles”) fuser –u user_data.dbf user_data.dbf: 708o(oracle) ps –ef | grep 708 oracle 708 1 0 Jul 26 ? 35:11 ora_smon_drm

46. Fundamental Unix Commands – that provide information System Information • ps • crontab • w • who • ipcs

47. Fundamental Unix Commands – that provide information System Information ps – List info about processes • Syntax: ps [-ef] [-o options] • Options*: -e list info about every process -f full listing -o info on specific attributes like mem, cpu *lots more options available, consult man page

48. Fundamental Unix Commands – that provide information System Information ps Examples • Show %CPU, Userid, ProcessID, and command ps –eo pcpu,user,pid,comm • Show Memory, %Memory, Userid, Process ID and command ps –eo vsz,pmem,user,pid,comm | sort –nr | head -5

49. Fundamental Unix Commands – that provide information System Information crontab – Scheduled Job execution • To list crontab file crontab –l • To edit crontab file crontab –e See man page for format Must have environment variable EDITOR set, typically, vi

50. Fundamental Unix Commands – that provide information System Information System uptime • Four similar commands: • uptime shows how long system is up • w shows uptime and load average • who –b shows date/time of last reboot • whodo –l same as w command