Tips for database administrators

1
Tips for database administrators
Gus Björklund (gus_at_progress.com)
Wizard, Progress Software Corporation
2
Ask questions as we goif I am not being clear
about something.Warning there is a mistake
somewhere in these slides.
3
Topics

• Tip Nr. 0x00
• Tip Nr. 0x16

numbered for easy reference
4
Tip Number 0x00
If it is not broken, dont fix it !
5
Tip Number 0x01
If you do fix something, test your fix !
6
Tip Number 0x02
Read the man pages
7
Read the man pages

• man man
• man -s section name
• Read the man pages!

8
Tip Number 0x03
SSH is your friend!Like Linux, great software
fromFinland
9
SSH

• SSH replaces telnet, rlogin, rcp, rsh, etc.
• All connections are encrypted
• Use for remote access over Internet

10
SSH

• Dont 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)

11
Simple remote command execution
gus_at_diracgus ssh bespin ls gus_at_bespin's
password 101arkt 90ajdk.txt GSI Mail News a.out
a7 adb.man gus_at_diracgus
Nothing much to see here
12
Remote command output to local host
gus_at_diracgus ssh bespin "ls echo DONE
ls.dat gus_at_bespin's password gus_at_diracgus
cat ls.dat 101arkt 90ajdk.txt GSI Mail News a.out
a7 adb.man DONE gus_at_diracgus
Output of remote commands written to local file
13
Local output piped to remote host
gus_at_diracgus cd 4gl tar -cf - .p ssh
bespin "cd foo tar -xf -" gus_at_bespin's password
gus_at_dirac4gl
Backup files in a local directory to a remote
directory
14
Remote backup piped to local file
gus_at_dirac4gl ssh bespin "cd pdr tar -cvf - ."
backup.tar gus_at_bespin's password a ./ 0K a
./Makefile 1K a ./pdrcache.c 131K a ./pdrproc.c
23K a ./pdrtrig.c 175K a ./dsmcon.c 94K a
./dsmrec.c 17K a ./dsmseq.c 15K gus_at_dirac4gl
Backup files in a remote directory to a local
directory
15
Turning off sshd password authentication
in the file /etc/ssh/sshd_config, do this
Protocol 1,2 Protocol 2 . . .
Authentication PasswordAuthentication no then
save changes. restart daemon service sshd
restart
16
Enabling SSH certificate authentication
gus_at_diracgus 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 3fa92ca6745e3659397f
ed3f5de3a6ee
gus_at_dirac.bedford.progress.com gus_at_di
racgus gus_at_diracgus ssh-copy-id -i id_rsa
gus_at_bespin gus_at_diracgus
17
Enabling SSH certificate authentication 2
gus_at_diracgus cat /Users/gus/.ssh/id_rsa.pub. ss
h-rsa AAAAB3NzaC1yc2EAAAABIwAAAQEApwswruyeETRbQ/T7
ZHdNxtXIC5J41XpnJwWND4HB3WTuXPLx/Qf2S83Y3VPSgGoc9
YqmLOc/hfs/gQryTQC9jGIPVYtW/E5PPvhh/HxTIKVc0eYMmp
KlxFCjfcv0KyDcAwnd7r/wczPqw2TpurzZcXfA0c2upqufJZbn
weXZtDrcs7bUwdQXskrqDj1EDBxACuH1/omZa2M/PdfFzyrRtb
RMeyrfIWAfoL5RNOFsPYYOxWJxfp11tuSF7OiBUQtcbXAfS8cd
HsQkJNNzSZqM6B69LvK28mlHZ8m2b5cXICA27aUyDZfsSlMLR
6rlAo3UByL8GZQBLUmhMsuRQ gus_at_dirac.bedford.pro
gress.com gus_at_diracgus
18
More about SSH
www.openssh.com http//www.ietf.org/rfc/rfc4251.t
xt
19
Tip Number 0x04
Windows can do SSH too
20
SSH for Windows
Client use Putty http//www.chiark.greenend.org.
uk/sgtatham/putty/ Server get Cygwin
http//www.cygwin.com
21
Tip Number 0x05
After-image journalling is your friend!
22
After Imaging

• Do you use after-image journalling?
• YOU SHOULD
• AI records
• all database changes after a backup
• Intended for
• disaster recovery
• hot standby on another machine

23
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
• OpenEdge DBA guide has details

24
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, dont you?
• I hope they werent on the failed disk
• When roll forward done, start database
• Now you are back to where you were

25
Tip Number 0x06
Specify scratch space for index rebuild
26
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
/user2/db1/first/ 400 /user3/junk/ 0 /user4/last/
note multiple drives in this example
27
Tip Number 0x07
Tools you should know about
28
Tools

• uptime
• find
• top
• lsof
• ifconfig
• vmstat
• iostat
• netstat
• bzip2

29
Tools uptime
uptime 1126am up 17 day(s), 916, 163
users, load average 3.09, 3.36, 3.76
30
Tools top
gus_at_diracgus top -l 1 Processes 65 total, 3
running, 62 sleeping... 183 threads
105124 Load Avg 0.04, 0.06, 0.07 CPU
usage 11.1 user, 44.4 sys, 44.4
idle SharedLibs num 170, resident 37.0M
code, 5.30M data, 6.44M LinkEdit MemRegions num
6460, resident 166M 16.3M private, 164M
shared PhysMem 161M wired, 758M active, 879M
inactive, 1.76G used, 248M free VM 9.47G
102M 230499(0) pageins, 16746(0) pageouts
PID COMMAND CPU TIME TH PRTS MREGS
RPRVT RSHRD RSIZE VSIZE 606 top
0.0 000.05 1 17 19 276K 684K
748K 26.9M 586 bash 0.0 000.02 1
14 16 224K 1.05M 844K 27.1M 585
login 0.0 000.01 1 16 40 172K
768K 636K 26.9M 583 Terminal 0.0
001.56 5 130 197 2.64M 11.9M 8.93M
364M 563 TextEdit 0.0 000.23 1 67
149 2.05M 8.94M 5.80M 355M 558 TeXShop
0.0 003.18 2 109 238 4.49M 20.2M
25.6M 365M 467 firefox-bi 0.0 131.55
6 96 389 48.1M 29.4M 67.4M 428M
31
Toolsfind

• 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 16k
• find . -size 16384c -print
• find . -size 32 -print
• delete files bigger than 16k
• find . -size 16384c -print xargs -i rm
  \Read the man page for lots more!

32
Toolslsof
_progres 26240 bleicher /usr/lib/librt.so.1 _prog
res 26240 bleicher /usr/lib/libsocket.so.1 _progr
es 26240 bleicher /usr/lib/libintl.so.1 _progres
26240 bleicher /usr/lib/ld.so.1 _progres 26240
bleicher /usr/lib/libdl.so.1 . . . _progres
26240 bleicher /tools/solaris/dlc100b/promsgs _pro
gres 26240 bleicher /tools/solaris/dlc100b/bin/_p
rogres
ftp//vic.cc.purdue.edu/pub/
33
Toolsifconfig
1-bespin-gus- /usr/sbin/ifconfig -a lo0
flags1000849
mtu 8232 index 1 inet 127.0.0.1 netmask ff000000
ce0 flags1000843,IPv4 mtu 1500 index 2 inet 172.16.7.168
netmask ffff0000 broadcast 172.16.255.255
34
Toolsifconfig
gus_at_diracgus ifconfig en0 en0
flags8863ICAST mtu 1500 tunnel inet --
inet6 fe80217f2fffeccb686en0 prefixlen 64
scopeid 0x4 inet 172.16.114.99 netmask
0xffff0000 broadcast 172.16.255.255 ether
0017f2ccb686 media autoselect
(1000baseT ) status
active supported media autoselect
10baseT/UTP 10baseT/UTP 10baseT/UTP
10baseT/UTP
100baseT 100baseTX
100baseTX
100baseTX 1000baseT
1000baseT
1000baseT none
vlan 0 parent interface bond
interfaces
35
Toolsnetstat
1-bespin-gus- netstat TCP IPv4 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 49152
0 ESTABLISHED bespin.32879 bespin.6014
49152 0 49152 0 ESTABLISHED bespin.6
014 bespin.32879 49152 0 49152
0 ESTABLISHED bespin.6015
bespin.32880 49152 0 49152 0
CLOSE_WAIT . . .
36
Tools netstat -s
gus_at_dirac4gl 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
37
vmstat
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
. . .
38
iostat
gus_at_diracgus iostat 5 5 disk0
cpu KB/t tps MB/s us sy id 18.07 4 0.07
7 3 90 0.00 0 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
39
Toolsbzip2
ls -l tips_for_dbas_00.ppt -rw-r--r-- 1 gus
staff 644096 Apr 23 0918 tips_for_dbas_00.ppt
bzip2 tips_for_dbas_00.ppt ls -l
tips_for_dbas_00.ppt.bz2 -rw-r--r-- 1 gus
staff 246356 Apr 23 0918 tips_for_dbas_00.ppt.bz
2
40
Tools Windows tabbed console
41
Tools windows tabbed console
http//sourceforge.net/projects/console/
42
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 PsTools list processes, system info,
who is logged on Zoomit zoom in on parts of
screen Lots more
43
Tools Still More Windoze Stuff
http//www.baremetalsoft.com/baretail
44
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
45
Tools AIX svmon
svmon -G -I size inuse
free pin virtual memory 1048576
425275 623301 66521 159191 pg
space 262144 31995 work
pers clnt pin 46041
0 0 in use 129600 275195
0 PageSize PoolSize inuse
pgsp pin virtual s 4 KB -
404795 31995 46041 159191 L 16
MB 5 0 0 5
0
List top 15 memory consumers svmon -Pt15 perl
-e while()print if(.2!s).0
if(/-/)
46
Tools AIX vmstat
vmstat -v 1048576 memory pages
1002054 lruable pages 478136 free
pages 1 memory pools
95342 pinned pages 80.1 maxpin
percentage 10.0 minperm percentage
50.0 maxperm percentage
36.1 numperm percentage 362570 file
pages . . . Use vmo command to
adjust minperm, maxperm, numperm as needed
http//users.ca.astound.net/baspence/AIXtip/AIX5_I
nitial_Tuning.htm
47
Tip Number 0x08
Windows scripting
48
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 Microoft, but free
• Lots of goodies Korn, C, bash shells, 350 UNIX
  commands, NFS, NFS gateway, telent, telnetd
• Download from http//technet.microsoft.com/en-us/i
  nteropmigration/bb380242.aspx
• UWIN
• http//www.research.att.com/sw/tools/uwin/

49
Tip Number 0x09
how to turn off Windows indexing service
50
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.

51
Tip Number 0x0a
What not to do
52
What Not To Do
53
Tip Number 0x0b
How to telnet to Windows
54
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 dont use telnet

enables you to telnet into a Windows machine
55
Tip Number 0x0c
scripting Progress backup
56
Scripting backup
bkupdev"DB_bkupdir/DB_name.bkup" rm -f
bkupdev 21 echo "Backing up database
DB_dir/DB_name to bkupdev" DLC/bin/probkup
online DB_dir/DB_name bkupdev01 \ -vs 250000
-bf 40 . bkupdev12 bkupdev_overflow EOF
source PEG, from Jared Middleton
57
Tip Number 0x0d
scripting promon
58
Scripting promon
Set page size, capture list of IO operations by
process
DLC/bin/promon DBNAME promon.log \ "EOF" 2 /dev/null RD 5 1 9999 p 3 2 x EOF
59
Tip Number 0x0e
scripting after imaging
60
Scripting ai archiving
EXTENT_NAME_rfutil DB_DIR/DB -C aimage extent
full EXTENT_DATEdate Ymd-HMS like
this 20070516-140856, but change to suit tar
-czvf /anotherdisk/aiarchives/EXTENT_DATE
EXTENT_NAME md5 -q /anotherdisk/aiarchives/EXTE
NT_DATE EXTENT_NAME \ /anotherdisk/aiarchives/
EXTENT_DATE EXTENT_NAME.md5 rfutil
DB_DIR/DB -C aimage extent empty EXTENT_NAME
or use ssh to send archived extents to another
machine
61
Tip Number 0x0f
How much space is being used by storage areas?
62
How much space is being used?
for each _AreaStatus where ( not
_AreaStatus-Areaname matches "After Image Area"
) no-lock display _AreaStatus-Areanum
format "" column-label "Num"
_AreaStatus-Areaname format "x(20)" column-label
"Area Name" _AreaStatus-Totblocks
column-label "Tot blocks" _AreaStatus-Hiwater
column-label "High water mark"
_AreaStatus-Hiwater / _AreaStatus-Totblocks 100
column-label " use" _AreaStatus-Extents
format "" column-label "Num Extents"
. end.
from PEG Dmitri Levin
63
Tip Number 0x10
List tables by storage area
64
List tables by storage area
for each _Area, each _Storageobject
where (_Storageobject._Area-number
_Area._Area-number), each _File
where (_File._File-Number _Storageobject._Objec
t-number) and
(_File._File-Number 0) break by
_File._File-name display _Area._Area-name
_File._File-name. end.
65
Listing of tables by storage area
Area-name
File-Name Schema Area
agedar Schema Area
agedar Schema Area
customer Schema Area
customer Schema Area
item Schema Area item Schema
Area monthly Schema Area
monthly
Why are the areas listed twice?
66
List tables by storage area
for each _Area, each _Storageobject
where (_Storageobject._Area-number
_Area._Area-number), each _File
where (_File._File-Number _Storageobject._Objec
t-number) and
(_File._File-Number 0) break by
_File._File-name display _Area._Area-name
_File._File-name. end.
67
List tables by storage area 2
for each _Area, each _Storageobject
where (_Storageobject._Area-number
_Area._Area-number), each _File
where (_File._File-Number _Storageobject._Objec
t-number) and
(_File._File-Number 0) and
(_StorageObject._Object-type eq 1) break by
_File._File-name display _Area._Area-name
_File._File-name. end.
68
Tip Number 0x11
List indices by storage area and table
69
List indexes by storage area and table
for each _Area, each _Storageobject
where (_Storageobject._Area-number
_Area._Area-number), each _Index
where (_Index._Idx-num _Storageobject._Object-
number) and (_StorageObject._Obj
ect-type eq 2) find _File of _Index.
if (_File._File-number 0) then
display _Area._Area-name _File._File-name
_Index._Index-name. end.
70
Tip Number 0x12
Learn to use vi
71
vi cheat sheet
72
Tip Number 0x13
Tom Bascoms protop
73
protop

• Tom Bascoms Open Source Progress 4GL VST-based
  performance monitoring program
• A promon written in the 4GL
• Displays tons of useful information
• Study source code to learn how it is done
• Download from
• http//www.greenfieldtech.com/articles/protop.shtm
  l

74
Tip Number 0x14
Adam Backmans DBA scripts
75
Adam Backmans dba scripts

• Available on PEG http//www.peg.com/utilities.htm
  l
• Been around 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

76
Tip Number 0x15
Where to get more tips
77
Where to learn more

• OpenEdge Database Administration Guide
• OpenEdge RDBMS Performance Tuning Made Simple
  on PSDN or PEG
• 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/educatio
  n/index.ssp
• PSC Tech Support Knowledge Center

78
Tip Number 0x16
Join the PEG www.peg.com Ask questions there
79
Questions
?