DB-3 Tips for Database Administrators Gus Björklund Wizard

DB-3: Tips for Database Administrators Gus Björklund Wizard, Progress Software Corporation

Ask questions as we go if I am not clear about something. Warning: there

Topics § Tip Nr. 0 x 00 §… § Tip Nr. 0 x 16

Tip Number 0 x 00 If it is not broken, don’t fix it !

Tip Number 0 x 01 If you do fix something, test your fix !

Tip Number 0 x 02 Read the man pages 6 DB-3: Tips for Database

Read the man pages § man [ -s section ] name § Read the

Tip Number 0 x 03 SSH is your friend! Like Linux, more great stuff

SSH § SSH replaces telnet, rlogin, rcp, rsh, etc. § All connections are encrypted

SSH § Don’t need username, password login • Can also use certificates for authentication § Can send output of • local commands to remote host • remote commands to local host § Can do secure file transfer • scp and sftp § Can tunnel other protocols (e. g. X windows) 10 DB-3: Tips for Database Administrators © 2007 Progress Software Corporation

Simple remote command execution gus@dirac: gus $ ssh bespin ls gus@bespin's password: 101 arkt 90 ajdk. txt GSI Mail News a. out a 7 adb. man gus@dirac: gus $ Nothing much to see here 11 DB-3: Tips for Database Administrators © 2007 Progress Software Corporation

Remote command output to local host gus@dirac: gus $ ssh bespin "ls; echo DONE” >ls. dat gus@bespin's password: gus@dirac: gus $ cat ls. dat 101 arkt 90 ajdk. txt GSI Mail News a. out a 7 adb. man DONE gus@dirac: gus $ 12 DB-3: Tips for Database Administrators © 2007 Progress Software Corporation

Local output piped to remote host gus@dirac: gus $ cd 4 gl; tar -cf - *. p | ssh bespin "cd foo; tar -xf -" gus@bespin's password: gus@dirac: 4 gl $ 13 DB-3: Tips for Database Administrators © 2007 Progress Software Corporation

Remote backup piped to local file gus@dirac: 4 gl $ ssh bespin "cd pdr; tar -cvf -. " >backup. tar gus@bespin's password: a. / 0 K a. /Makefile 1 K a. /pdrcache. c 131 K a. /pdrproc. c 23 K a. /pdrtrig. c 175 K a. /dsmcon. c 94 K a. /dsmrec. c 17 K a. /dsmseq. c 15 K gus@dirac: 4 gl $ 14 DB-3: Tips for Database Administrators © 2007 Progress Software Corporation

Turning off sshd password authentication in the file: /etc/sshd_config, do this: # Protocol 1, 2 Protocol 2. . . # Authentication: Password. Authentication no then save changes. restart daemon: # service sshd restart 15 DB-3: Tips for Database Administrators © 2007 Progress Software Corporation

Enabling SSH certificate authentication gus@dirac: gus $ ssh-keygen Generating public/private rsa key pair. Enter file in which to save the key (/Users/gus/. ssh/id_rsa): Enter passphrase (empty for no passphrase): Enter same passphrase again: Your identification has been saved in /Users/gus/. ssh/id_rsa. Your public key has been saved in /Users/gus/. ssh/id_rsa. pub. The key fingerprint is: 3 f: a 9: 2 c: a 6: 74: 5 e: 36: 59: 39: 7 f: ed: 3 f: 5 d: e 3: a 6: ee gus@dirac. bedford. progress. com gus@dirac: gus $ ssh-copy-id -i id_rsa gus@bespin gus@dirac: gus $ 16 DB-3: Tips for Database Administrators © 2007 Progress Software Corporation

Enabling SSH certificate authentication 2 gus@dirac: gus $ cat /Users/gus/. ssh/id_rsa. pub. ssh-rsa AAAAB 3 Nza. C 1 yc 2 EAAAABIw. AAAQEApwswruye. ETRb. Q/T 7 ZHd. Nxt. XIC 5 J 41 Xpn. Jw. WND 4 HB 3 WTu. XPLx/Qf 2 S 83 Y 3 VPSg. Goc+9 Yqm. LOc/hfs/g. Qry +TQC 9 j. GIPVYt. W/E 5 PPvhh/Hx. TIKVc 0 e. YMmp. Klx. FCjfcv 0 Ky. Dc. Awnd 7 r/wc z. Pqw 2 Tpurz. Zc. Xf. A 0 c 2 upquf. JZbnwe. XZt. Drcs 7 b. Uwd. QXskrq. Dj 1 EDBx. ACu H 1/om. Za 2 M/Pdf. Fzyr. Rtb. RMeyrf. IWAfo. L 5 RNOFs. PYYOx. WJxfp 11 tu. SF 7 Oi BUQtcb. XAf. S 8 cd. Hs. Qk. JNNz. SZq. M 6 B 69 Lv. K 28 ml. HZ 8 m 2 b 5+c. XICA 27 a. Uy DZfs. Sl. MLR 6 rl+Ao 3 UBy. L 8 G+ZQBLUmh. Msu. RQ== gus@dirac. bedford. progress. com gus@dirac: gus $ 17 DB-3: Tips for Database Administrators © 2007 Progress Software Corporation

More about SSH www. openssh. com http: //www. ietf. org/rfc 4251. txt 18 DB-3:

Tip Number 0 x 04 Windows can do SSH too 19 DB-3: Tips for

SSH for Windows Client: use Putty http: //www. chiark. greenend. org. uk/~sgtatham/putty/ Server: get Cygwin http: //www. cygwin. com 20 DB-3: Tips for Database Administrators © 2007 Progress Software Corporation

Tip Number 0 x 05 After-image journalling is your friend! 21 DB-3: Tips for

After Imaging § Do you use after-image journalling? • YOU SHOULD § AI records all database changes § Intended for • disaster recovery • hot standby on another machine 22 DB-3: Tips for Database Administrators © 2007 Progress Software Corporation

AI Basics § Add AI extents to database § Back up the database § Enable after-imaging § Archive ai extents as they become full • 10. 1 has an automatic archiver § Open. Edge DBA guide has details 23 DB-3: Tips for Database Administrators © 2007 Progress Software Corporation

AI basics 2 § A failure occurs and you lose your database …. . § Fix the failure • replace disk, memory, machine, etc. § § Restore the backup copy of your database Roll forward all ai extents you archived • You do know where you put them, don’t you? • I hope they weren’t on the failed disk When roll forward done, start database Now you are back to where you were 24 DB-3: Tips for Database Administrators © 2007 Progress Software Corporation

Tip Number 0 x 06 Specify scratch space for index rebuild 25 DB-3: Tips

Index rebuild scratch space Command line looks like this: proutil dbname -C idxbuild table customer -SS dbname. srt -TB 31 -TM 32 -B 1000 Scratch space spec file (dbname. srt) looks like this: 300 /user 2/db 1/first/ 400 /user 3/junk/ 0 /user 4/last/ note multiple drives in this example 26 DB-3: Tips for Database Administrators © 2007 Progress Software Corporation

Tip Number 0 x 07 Tools you should know about 27 DB-3: Tips for

Tools § § § § § uptime find top lsof ifconfig vmstat iostat netstat

Tools: uptime $ uptime 11: 26 am up 17 day(s), 9: 16, 163 users,

Tools: top gus@dirac: gus $ top -l 1 Processes: 65 total, 3 running, 62 sleeping. . . 183 threads 10: 51: 24 Load Avg: 0. 04, 0. 06, 0. 07 CPU usage: 11. 1% user, 44. 4% sys, 44. 4% idle Shared. Libs: num = 170, resident = 37. 0 M code, 5. 30 M data, 6. 44 M Link. Edit Mem. Regions: num = 6460, resident = 166 M + 16. 3 M private, 164 M shared Phys. Mem: 161 M wired, 758 M active, 879 M inactive, 1. 76 G used, 248 M free VM: 9. 47 G + 102 M 230499(0) pageins, 16746(0) pageouts PID COMMAND %CPU TIME #TH #PRTS #MREGS RPRVT RSHRD RSIZE VSIZE 606 top 0. 0% 0: 00. 05 1 17 19 276 K 684 K 748 K 26. 9 M 586 bash 0. 0% 0: 00. 02 1 14 16 224 K 1. 05 M 844 K 27. 1 M 585 login 0. 0% 0: 00. 01 1 16 40 172 K 768 K 636 K 26. 9 M 583 Terminal 0. 0% 0: 01. 56 5 130 197 2. 64 M 11. 9 M 8. 93 M 364 M 563 Text. Edit 0. 0% 0: 00. 23 1 67 149 2. 05 M 8. 94 M 5. 80 M 355 M 558 Te. XShop 0. 0% 0: 03. 18 2 109 238 4. 49 M 20. 2 M 25. 6 M 365 M 467 firefox-bi 0. 0% 1: 31. 55 6 96 389 48. 1 M 29. 4 M 67. 4 M 428 M 30 DB-3: Tips for Database Administrators © 2007 Progress Software Corporation

Tools: find § § find files modified more than 30 days ago • find. -mtime +30 -print delete files modified more than 30 days ago • find. -mtime +30 -exec rm {} ; § § find files bigger than 16 k • find. -size +16384 c -print • find. -size +32 -print delete files bigger than 16 k • find. -size +16384 c -print | xargs -i rm ; Read the man page! 31 DB-3: Tips for Database Administrators © 2007 Progress Software Corporation

Tools: lsof _progres _progres 26240 26240 bleicher /usr/librt. so. 1 bleicher /usr/libsocket. so. 1 bleicher /usr/libintl. so. 1 bleicher /usr/lib/ld. so. 1 bleicher /usr/libdl. so. 1. . . 26240 bleicher /tools/solaris/dlc 100 b/promsgs 26240 bleicher /tools/solaris/dlc 100 b/bin/_progres ftp: //vic. cc. purdue. edu/pub/ 32 DB-3: Tips for Database Administrators © 2007 Progress Software Corporation

Tools: ifconfig 1 -bespin-gus-> /usr/sbin/ifconfig -a lo 0: flags=1000849<UP, LOOPBACK, RUNNING, MULTICAST, IPv 4> mtu 8232 index 1 inet 127. 0. 0. 1 netmask ff 000000 ce 0: flags=1000843<UP, BROADCAST, RUNNING, MULTICAST, IPv 4> mtu 1500 index 2 inet 172. 16. 7. 168 netmask ffff 0000 broadcast 172. 16. 255 33 DB-3: Tips for Database Administrators © 2007 Progress Software Corporation

Tools: ifconfig gus@dirac: gus $ ifconfig en 0: flags=8863<UP, BROADCAST, SMART, RUNNING, SIMPLEX, MULTICAST> mtu 1500 tunnel inet --> inet 6 fe 80: : 217: f 2 ff: fecc: b 686%en 0 prefixlen 64 scopeid 0 x 4 inet 172. 16. 114. 99 netmask 0 xffff 0000 broadcast 172. 16. 255 ether 00: 17: f 2: cc: b 6: 86 media: autoselect (1000 base. T <full-duplex, flow-control>) status: active supported media: autoselect 10 base. T/UTP <half-duplex> 10 base. T/UTP <full-duplex>10 base. T/UTP <full-duplex, hw-loopback> 10 base. T/UTP <full-duplex flow-control> 100 base. T<half-duplex> 100 base. TX <full-duplex, hw-loopback> 100 base. TX <full-duplex, flow-control> 1000 base. T <full-duplex, hw-loopback> 1000 base. T <full-duplex, flow-control> none vlan: 0 parent interface: <none> bond interfaces: <none> 34 DB-3: Tips for Database Administrators © 2007 Progress Software Corporation

Tools: netstat 1 -bespin-gus-> netstat TCP: IPv 4 Local Address Remote Address Swind Send-Q Rwind Recv-Q State -------------------- ------bespin. 1023 rdlserv. nfsd 26280 0 49640 116 ESTABLISHED bespin. 32880 bespin. 6015 49152 0 ESTABLISHED bespin. 32879 bespin. 6014 49152 0 ESTABLISHED bespin. 6014 bespin. 32879 49152 0 ESTABLISHED bespin. 6015 bespin. 32880 49152 0 CLOSE_WAIT. . . 35 DB-3: Tips for Database Administrators © 2007 Progress Software Corporation

Tools: netstat -s gus@dirac: 4 gl $ netstat -s tcp: 794419 packets sent 37468 data packets (22991001 bytes) 51 data packets (11242 bytes) retransmitted 0 resends initiated by MTU discovery 430311 ack-only packets (196294 delayed) 0 URG only packets 0 window probe packets 322074 window update packets 4516 control packets 1308690 packets received 33675 acks (for 22967429 bytes) 2270 duplicate acks 0 acks for unsent data 1248341 packets (1521010141 bytes) received in-sequence 385 completely duplicate packets (343882 bytes) 0 old duplicate packets 41 packets with some dup. data (28512 bytes duped) 33202 out-of-order packets (38094369 bytes) 1 packet (376 bytes) of data after window 36 DB-3: Tips for Database Administrators © 2007 Progress Software Corporation

vmstat 3 50 procs ------memory----- ---swap-- -----io---- --system-- ----cpu---r b swpd free buff cache si so bi bo in cs us sy id wa 0 7 3656 226920 16528 5294120 0 0 360 636 281 823 4 4 49 42 0 6 3656 205504 16584 5302288 0 0 2688 903 1066 4675 7 6 11 75 2 0 3656 186088 16584 5310512 0 0 2656 684 1010 4687 6 6 8 80 22 28 3656 165196 16600 5319748 0 0 2887 871 1096 4623 8 6 8 78 0 94 3656 138524 16648 5331008 0 0 3909 815 1292 3455 8 6 6 79. . . 37 DB-3: Tips for Database Administrators © 2007 Progress Software Corporation

iostat gus@dirac: gus $ iostat 5 5 disk 0 cpu KB/t tps MB/s us sy id 18. 07 4 0. 07 7 3 90 0. 00 2 1 97 0. 00 0 0. 00 4 1 95 0. 00 0 0. 00 2 2 96 0. 00 0 0. 00 3 1 95 38 DB-3: Tips for Database Administrators © 2007 Progress Software Corporation

Tools: bzip 2 $ ls -l tips_for_dbas_00. ppt -rw-r--r-- 1 gus staff 644096 Apr 23 09: 18 tips_for_dbas_00. ppt $ bzip 2 tips_for_dbas_00. ppt $ ls -l tips_for_dbas_00. ppt. bz 2 -rw-r--r-- 1 gus staff 246356 Apr 23 09: 18 tips_for_dbas_00. ppt. bz 2 39 DB-3: Tips for Database Administrators © 2007 Progress Software Corporation

Tools: Windows tabbed console 40 DB-3: Tips for Database Administrators © 2007 Progress Software

Tools: windows tabbed console http: //sourceforge. net/projects/console/ 41 DB-3: Tips for Database Administrators ©

Tools: More Windoze Stuff http: //www. microsoft. com/technet/sysinternals Some useful stuff you can get there: Handle: Who has what files open Process Monitor: Lots of information about processes and disk activity Autorun: what programs get started automatically Regmon: monitors registry activity Tcpview: shows all active TCP and UDP endpoints Ps. Tools: list processes, system info, who is logged on Zoomit: zoom in on parts of screen Lots more 42 DB-3: Tips for Database Administrators © 2007 Progress Software Corporation

Tools: Still More Windoze Stuff http: //www. baremetalsoft. com/baretail 43 DB-3: Tips for Database

Tools: Solaris pldd List the dynamic libraries linked into each process pfiles Report fstat(2) and fcntl(2) information for all open files in each process. pmap print information about the address space of a process plimit get or set the resource limits of running processes pargs print process arguments, environment variables preap force a defunct process to be reaped by its parent pkginfo list what packages are installed 44 DB-3: Tips for Database Administrators © 2007 Progress Software Corporation

Tools: AIX svmon -G -I size inuse free memory 1048576 425275 pg space 262144 31995 pin in use Page. Size s 4 KB L 16 MB work pers clnt 46041 0 0 129600 275195 pin virtual 623301 66521 159191 0 Pool. Size inuse pgsp pin virtual - 404795 31995 46041 159191 5 0 0 5 0 List top 15 memory consumers svmon -Pt 15 | perl -e ’while(<>){print if($. ==2||$&&&!$s++); $. =0 if(/^-+$/)}’ 45 DB-3: Tips for Database Administrators © 2007 Progress Software Corporation

Tools: AIX vmstat -v 1048576 memory pages 1002054 lruable pages 478136 free pages 1 memory pools 95342 pinned pages 80. 1 maxpin percentage 20. 0 minperm percentage 80. 0 maxperm percentage 36. 1 numperm percentage 362570 file pages. . . Use vmo command to adjust minperm, maxperm, numperm as needed 46 DB-3: Tips for Database Administrators © 2007 Progress Software Corporation

Tip Number 0 x 08 Windows scripting 47 DB-3: Tips for Database Administrators ©

Windows scripting Windows. bat scripts are brain damaged § Cygwin • provides lots of Unix tools and a shell for Windows. Also SSH server • Download from: www. cygwin. com § Windows Services for UNIX Version 3. 5 • From Micro$oft, but free • Lots of goodies: Korn, C, bash shells, 350 UNIX commands, NFS gateway, telent, telnetd • Download from http: //technet. microsoft. com/enus/interopmigration/bb 380242. aspx 48 DB-3: Tips for Database Administrators © 2007 Progress Software Corporation

Tip Number 0 x 09 how to turn off Windows indexing service 49 DB-3:

Stop Windows indexing service § Go to the Start menu, pick run. • Type services. msc, click OK. • Services dialog will show. § Right-click on indexing service to show § § properties dialog, if service running, click stop. select disabled. click apply close. 50 DB-3: Tips for Database Administrators © 2007 Progress Software Corporation

Tip Number 0 x 0 a creating Windows services 51 DB-3: Tips for Database

User SRVANY to run Windows Services § Lets you create “user defined” Windows § § services Instructions and download from http: //www. tacktech. com/display. cfm? ttid=197 Warning: • This is not for n 00 bs. • Study before using • Read http: //support. microsoft. com/kb/137890 52 DB-3: Tips for Database Administrators © 2007 Progress Software Corporation

Tip Number 0 x 0 b How to telnet to Windows 53 DB-3: Tips

Enable Windows telnet login by name/passwd § NTLM authentication by default § To fix: • • • run TLNTADMN take menu pick no 3 (display/change …) pick 7 (NTLM) set value to 1 take menu pick no 4 (restart service) § BUT: don’t use telnet 54 DB-3: Tips for Database Administrators © 2007 Progress Software Corporation

Tip Number 0 x 0 c scripting Progress backup 55 DB-3: Tips for Database

$Scripting backup bkupdev="${DB_bkupdir}/${DB_name}. bkup" rm -f ${bkupdev}* 2>&1 # echo "Backing up database $DB_dir/$DB_name$

Scripting backup bkupdev="${DB_bkupdir}/${DB_name}. bkup" rm -f ${bkupdev}* 2>&1 # echo "Backing up database $DB_dir/$DB_name to $bkupdev" # $DLC/bin/probkup online $DB_dir/$DB_name ${bkupdev}01 -vs 250000 -bf 40 <<EOF ${bkupdev}02 ${bkupdev}03. . . ${bkupdev}12 ${bkupdev}_overflow EOF # source: PEG, from Jared Middleton 56 DB-3: Tips for Database Administrators © 2007 Progress Software Corporation

Tip Number 0 x 0 d scripting promon 57 DB-3: Tips for Database Administrators

Scripting promon Set page size, capture list of IO operations by process $DLC/bin/promon $DBNAME > promon. log <<- "EOF" 2> /dev/null R&D 5 1 9999 p 3 2 x EOF 58 DB-3: Tips for Database Administrators © 2007 Progress Software Corporation

Tip Number 0 x 0 e scripting after imaging 59 DB-3: Tips for Database

Scripting ai archiving EXTENT_NAME=`_rfutil $DB_DIR/$DB -C aimage extent full` # EXTENT_DATE=`date +%Y%m%d-%H%M%S` # like this: 20070516 -140856 tar -czvf /anotherdisk/aiarchives/$EXTENT_DATE $EXTENT_NAME rfutil $DB_DIR/$DB -C aimage extent empty $EXTENT_NAME 60 DB-3: Tips for Database Administrators © 2007 Progress Software Corporation

Tip Number 0 x 0 f How much space is being used by storage

How much space is being used? for each _Area. Status where ( not _Area. Status-Areaname matches "*After Image Area*" ) no-lock: display _Area. Status-Areanum format ">>>" column-label "Num" _Area. Status-Areaname format "x(20)" column-label "Area Name" _Area. Status-Totblocks column-label "Tot blocks" _Area. Status-Hiwater column-label "High water mark" _Area. Status-Hiwater / _Area. Status-Totblocks * 100 column-label "% use" _Area. Status-Extents format ">>>" column-label "Num Extents". end. from PEG: Dmitri Levin 62 DB-3: Tips for Database Administrators © 2007 Progress Software Corporation

Tip Number 0 x 10 List tables by storage area 63 DB-3: Tips for

List tables by storage area for each _Area, each _Storageobject where (_Storageobject. _Area-number = _Area-number), each _File where (_File-Number = _Storageobject. _Object-number) and (_File-Number > 0) break by _File-name: display _Area-name _File-name. end. 64 DB-3: Tips for Database Administrators © 2007 Progress Software Corporation

Listing of tables by storage area Area-name Schema Area Schema Area 65 DB-3: Tips

List tables by storage area for each _Area, each _Storageobject where (_Storageobject. _Area-number = _Area-number), each _File where (_File-Number = _Storageobject. _Object-number) and (_File-Number > 0) break by _File-name: display _Area-name _File-name. end. 66 DB-3: Tips for Database Administrators © 2007 Progress Software Corporation

List tables by storage area 2 for each _Area, each _Storageobject where (_Storageobject. _Area-number = _Area-number), each _File where (_File-Number = _Storageobject. _Object-number) and (_File-Number > 0) and (_Storage. Object. _Object-type eq 1) break by _File-name: display _Area-name _File-name. end. 67 DB-3: Tips for Database Administrators © 2007 Progress Software Corporation

Tip Number 0 x 11 List indices by storage area and table 68 DB-3:

List indexes by storage area and table for each _Area, each _Storageobject where (_Storageobject. _Area-number = _Area-number), each _Index where (_Index. _Idx-num = _Storageobject. _Object-number) and (_Storage. Object. _Object-type eq 2) : find _File of _Index. if (_File-number > 0) then display _Area-name _File-name _Index-name. end. 69 DB-3: Tips for Database Administrators © 2007 Progress Software Corporation

Tip Number 0 x 12 Learn to use vi 70 DB-3: Tips for Database

vi cheat sheet 71 DB-3: Tips for Database Administrators © 2007 Progress Software Corporation

Tip Number 0 x 13 Tom Bascom’s protop 72 DB-3: Tips for Database Administrators

protop § Tom Bascom’s Open Source Progress 4 GL VST § § § based performance monitoring program Displays tons of useful information Study source code to learn how it is done Download from http: //www. greenfieldtech. com/articles/protop. shtml 73 DB-3: Tips for Database Administrators © 2007 Progress Software Corporation

Tip Number 0 x 14 Adam Backman’s DBA scripts 74 DB-3: Tips for Database

Adam Backman’s dba scripts § § § Available on PEG: http: //www. peg. com/utilities. html Been aroud awhile, but very good Stuff they do: • • • § backup check ai status check db status look for blocked clients more … Study code to learn how it is done 75 DB-3: Tips for Database Administrators © 2007 Progress Software Corporation

Tip Number 0 x 15 Where to get more tips 76 DB-3: Tips for

Where to learn more § § Open. Edge Database Administration Guide PSDN: www. psdn. progress. com § Classes (not the only ones) • Bravepoint: bravepoint. com • White Star Software: wss. com • Progress DBA classes http: //www. progress. com/openedge/services/education/index. ssp § PSC Tech Support Knowledge Center 77 DB-3: Tips for Database Administrators © 2007 Progress Software Corporation