For prospective employers who wish to read more details about my recent experience, this page provides a fair sampling, but not all, of the work I have done in the last several years.
Please read these, the hiring notes and my resume carefully. Many typical questions are answered here. Contact information can be found below and on my Contact Page.
Current Position - Oracle Database Architect
Contact Information (also see my Contact Page)
Phil Otken (please do obvious substitutions below, "o" 4 "0", etc -
spam inhibit)
p0tken -at- yah00 -d0t- c0m
philip -d0t- 0tken -at- hyper-ad d0t c0m
(512) 832-9386
Design and implementation of new product databases
Initial build script development and maintenance
Creation and maintenance of test schema
Conversion of text file (initial load) data (including use of Pervasive Data Junction tool)
Implementation into production database
Database migration Oracle 8.1.7.4 to 10.2.0.1, including (manual) scripting for conversion of all database objects.
Reverse engineering of all database objects
Schema comparisons via TOAD
Optimization of long-running processes by parallel processing
Support of Oracle database servers v9.2.x, 10.x, 10g RAC
Troubleshooting and 2nd-level support for database application issues, including
Remote administration of Unix OS: HP-UX, Solaris, Linux
Backup and recovery via RMAN scripts and troubleshooting
Monitoring of Oracle database; automated error notification
Implementation of Oracle Transparent Data Encryption (TDE)
SQL statement analysis and tuning
Storage analysis and allocation
Online index rebuilds and storage recovery
Development DBA, Texas Children's Health
Insurance Program
Affiliated Computer Services, Inc., Austin, TX.
4/2004 - 11/2005
Oracle Administration / Programming:
I currently support production, testing, development and training
databases.
The databases are on the order of hundreds of GB with total traffic to
the
production database on the order of several GB per month.
The database supports transaction traffic from several large
departments, an external
read-only application, a web application used by clients to look up
their status, and
an online client phone system. Typical peak load is in the hundreds of
active connections.
Production database is Oracle running on a Windows platform with a
failover HA solution. The facility employs a multi-TB SAN storage
solution.
Database is backed up nightly via a script I wrote that uses advanced
SAN
functionality and is extremely rapid.
A number of client applications access the same production database.
Some do
transaction or lookup actions only, some do batch processing. There are
a
variety of scripts for large scale batch processing that run
periodically. In addition
to supporting production, I provide all database support for the
application
developers and business analysts who design and test the application
software.
Experience with Oracle 9i and 10g:
I run Oracle 9.2.0 on Linux and Oracle 10g on Solaris 10 (x86) at
home.
These are for my own education uses, since we run an older version of
Oracle
at work. I built the computers myself, maintain them, installed all
software and
databases. Part of a continuing self-education program I
maintain.
Routine activities (in order of frequency):
1) Database software installs
2) Replication of databases for testing,
development, training
3) Monitor and allocate storage use
4) Monitor database metrics. I wrote the
software that gathers this data from Oracle.
5) Assist in debugging batch processes
6) Run after-hours installs, batch processes,
maintenance scripts
7) I have written most of the database
maintenance programming including the
backup script,
described below.
8) Long-term programming projects to automate
tasks now done by hand.
Database-side software install
automation
I wrote a large program in Perl
(AUTO_INSTALLER) to automate database software installs:
Purpose of program:
Run unattended installs of database software; respond correctly to error
conditions; back out install in case of fatal error; auto-notify DBA of fatal error.
Basic functionality
of program:
Runs a series of install files, executed from several other programs
Creates log file as it runs; checks exit status for each step and
responds appropriately to errors, including backout of install
in case of fatal errors.
Features of program:
* Controlled by a small set of text-only config files
* Run from the command line, suitable for scheduling with any
scheduling tool (such as cron in UNIX or Windows Scheduler in Windows)
* Intended for database installs, but suitable for use (unmodified)
running any batch process or any set of commands executable from the
command line. In other words, this is a general automation tool.
* Tested and works in both UNIX (Cygwin) and Windows environments
* Provides DETAILED log files, including logs of each step. Log
file are set up to allow rapid debugging in case a late-night
process encounters a fatal error.
* Knows a large list of Oracle errors based on experience. Includes
a function to throw a fatal error in case it encounters new Oracle errors.
* Development environment for this program has been broken into
individual subroutines which are edited and versioned independently.
These subroutines are available for use in other Perl programs and
are deliberately written to be as robust and general as possible.
* New versions of the AUTO-INSTALLER are built from pieces
to allows rapid maintenance of the program, as well as making it easy
to generalize for use in controlling other programs.
* Program is network-aware; can be run from a workstation to load
an install from files in a file server to a database in a database
server (i.e., each role can be a different computer).
* Program can be used recursively (can call instances of itself) to
launch parallel runs on any number of computers. Can be used to
control and monitor almost arbitrarily complex operations.
* Performance of the program is excellent and is limited only by
the performance of the programs it calls
Documentation
* Program has simple internal help file that shows basic operation
in concise format
* Accompanying documentation thoroughly explains theory and operation,
including how to write config files, includes several different examples
of varying complexity.
Tech talks,
examples
I have given three tech talks on this program to the developers.
We have gone over examples, talked about exactly what the program does
and why, and integrated the action of this program with the more routine
database install scripts they are required to produce for each release.
I wrote additional examples of install scenarios and placed them in the
file server where the development team could use them. I created a set
of templates for all the configuration files and a set of commonly
used scripts to aid the developers in creating these files.
Perl programming
In addition to the AUTO-INSTALLER, I wrote the
following Perl programs
that
are currently in production use:
FILE-REAPER -
periodically, this program deletes archive log files
of a given age (or greater) and makes a log file listing files deleted.
Online documentation is included in the program.
LOG-TRIMMER -
periodically, this program makes a date-time stamped copy
of several log files, and then deletes the contents of the file, preventing
creation of huge log files and facilitating debugging when there is a problem.
Online documentation is included.
Other Perl programs in progress or planned for
near future
REBOOTER - Since we run
the Windows platform, we have to reboot the nodes
periodically, as recommended by Microsoft. This operation is complicated by
the presence of failover high-availability software. The program will shut down
the database safely, reboot the nodes one at a time, restart the database, then run
a series of health checks to verify that the cluster is operating nominally.
CLONER - This program
automates an operation I perform regularly -
replicating a database from another database. Network-aware. Will allow
scheduled, unattended database replication or restoration when completed.
Note that a large
number of common tasks (especially error handling, parsing of both
text strings and files, and log file generation) are already coded in the form of my
subroutine library. New programs make use of previous code wherever possible. The
majority of code for the above two unfinished projects is already written, much
of the individual functionality has been tested. The remaining steps are largely
assembly of the different pieces and testing.
Reverse engineering
I have a collection of scripts that generate
create table scripts, create tablespace scripts,
generate move scripts to move tables and
indexes from one tablespace in Oracle to another. These
have been used in the past for re-arrangements
of the database to conserve space, moving tables
around during upgrades and copying a particular
set of tables (and their indexes) from production
to test environments for debugging.
SQL profiler: I evaluated several last year,
but the large price tag and lack of noticeable
performance issues with our database indicated
the purchase was not cost-effective. I was
authorized to start a home-grown version of
this program, written in C++. I made some
progress with it before it was placed on the
back burner, and still have the partially written code.
The basic idea is to parse detailed trace files
and summarize the results by SQL statement.
This allows triage for the tuning process,
resulting in much better prioritization of effort.
Oracle Designer implementation
I installed our current version of Oracle
Designer and set up the repository for it.
Oracle Database Administrator,
Texas Children's Health Insurance Program
Affiliated Computer Services, Inc., Austin, TX.
4/2001 - 3/2004
Oracle 8i Administration / Programming
(Windows platform)
High-volume production database
support
I respond to production problems first. In the
past, issues with the database were fairly
frequent. After almost four years of work, we
rarely have any issues with the database.
I have:
managed database issues
relating to two major upgrades
provided evidence of
application problems and how to solve them
I currently:
monitor and administer
storage
monitor database
metrics
create SQL scripts for
special purposes as requested
assist developers with
database and SQL issues
assist in the monthly
reboot of the servers
schedule and perform
database installs during production off-hours
run some of the less
stable batch processes
advise management about
issues relating to the database
do capacity planning
for infrastructure upgrades
design and enforce
database coding standards (particularly software install standards)
automate or assist in
automating processes
archive old data
extract and mothball
old projects
monitor and manage the
database side of the backup strategy
refresh data in the
test, training and development servers
respond to special
requests related in any way to the database
I am also partially responsible for software
security of the database, a recent addition to my
duties.
I spent a lot of time working on making maximum
use of available RAM in our servers so that
the database does very little paging. I wrote a
C++ program to probe Oracle for all available
use of memory structures and compared this to
the memory footprint reported in the operating
system. I found a fairly large discrepancy. I
improved the method and was able to account
for about 98% of the reported memory footprint
(the working set metric). This allowed
me to set parameters to use almost all
available RAM, and made the database extremely fast.
Microsoft Cluster Server Implementation
Oracle Fail Safe implementation
We have used a failover HA solution for several
years. I implemented the original
installation, and adapted it when we went to a
large SAN storage solution. I also
manage the failover software.
Batch processing
I used to run most of the database batch
scripts myself, and still run some of our more problem
prone scripts. As these scripts have matured, I
have gradually offloaded running of these to
our production team. When a script breaks, I
evaluate what happened and create a fix script,
often in consultation with our production team
or the developers. When possible we try to solve
problems permanently.
Backup plans and implementation
Our SAN allows multiple copies of the logical
drives we use to store the database files.
I wrote the backup script to take advantage of
this feature, and reduced the down
time for backups from hours to minutes.
Recovery operations are done routinely to refresh
test, development and training databases. These
processes have been improved so that
they can be done in as little as an hour; in
the past they took up to two days.
Performance monitoring and tuning
I wrote all the database monitoring software in
C++ / Oracle Call Interface using a third-party
header file called Oracle Template Library that
encapsulates OCI calls into a fairly nice, consistent
C++ interface. Currently, these programs
monitor:
Oracle Database Buffer
Cache hit ratio
Oracle Library Cache
hit ratio
Oracle Dictionary cache
hit ratio
Oracle Database Disk
Sort ratio
Oracle Sessions and
Processes
Oracle Disabled
Constraints
Oracle (User) Locks
Oracle Database Process
memory use
Operating system memory
use
Notable gains in performance were had by:
Improving applications
and scripts
Hardware improvements
Improving the logical
structure of the database
Upgrading the version
of the database
Upgrading storage
solutions
I have completely rebuilt our production
database twice, taking advantage of the
opportunity to make some infrastructure
improvements as listed above. Performance and
usable uptime increased dramatically on both
upgrades.
Client-side
interface design and programming (Tcl/Tk, C++)
I designed and wrote the prototype to make our
interdepartmental change request system
paperless and proved that the concept could be
made to work. Most of the prototype was
written in Tcl/Tk, which provided an
inexpensive way to rapidly develop prototype Windows
applications that access the database. The
project was then turned over to the developers and
is now used routinely.
I also wrote a program in Tcl/Tk to do load
testing on the databases. This application mimicked
typical client program activity by making
random transactions to the database. Many instances
of the application could be launched to test
how well the database ran under high stress. We have
run enough simultaneous instances of this
program against a database during tests to slow it to a crawl.
The test application was used to identify a
problem with our main application. I loaded the database
up with many instances of the test application,
until it began to slow noticeably, then started
a single instance of our main application. The
errors we wanted to isolate showed up
immediately, showing that application issues
were causing the problems.
I have since written a more sophisticated,
command-line version of this test loading application
in C++/OCI, and have used it for loading a
database for testing purposes. The new program does
much of the same actions as the old program,
without the Windows overhead, and a single instance of
this newer version loads the database more
intensely than the old version. I have used it for
experiments with performance testing, when I
needed to make a test database behave as
though it was being hit by multiple
transactions over a period of time. I suspect it will prove
very useful in the future when we again need to
again simulate transactions in a test environment.
Process automation
I have assisted both development and production
teams with gradually making our batch processes
more robust and usable. Specific things I have
done towards this end:
1) Created and promoted
a script standard that required every script make a log
showing results of processing for every step, as well as log the database
instance, timings for various steps and the user who ran the script
2) Created a DOS batch
file that allows scheduling of a SQL script and takes a log
file of the run
3) Developed a general
program (the auto-installer described above) that runs other
programs and is particularly good at running database scripts and code
4) I developed the
"Productionalization" initiative.
This is a process of writing a script so that a relatively unknowledgeable person,
such as one of our managers, can read simple online documents written like a
recipe and correctly run one of the production scripts. Some of our scripts
have been productionalized, some have not (I usually run the worst of these).
The main result is that the production team members can run each other's scripts
and processes with relatively little preparation. This has been useful, but
further development is needed before we can just bring up an application, press
a button, and run our critical processes without intervention by a knowledgeable
person (me).
C/C++ programming: Oracle Call
Interface
See above re: Performance monitors and test
loading application. SQL profiler in development
also written in
C++/OCI.
Data cleansing, extraction and
monitoring
I assist the develoment and production teams
when they have a data issue they can't solve
by themselves. This includes scripts to fix
erroneous data, extract data to text files, monitor
tables for the presence of any of a long list
of keywords.
Oracle Logminer
We used this once to mine the redo archives
when an employee intentionally entered inappropriate
entries in clients' accounts. We were able to
identify the perpetrator and sent a report to
management. I have recently had to
demonstrate proficiency in use of this tool.
Database replication
Use of backups to replicate and refresh test
and training databases with fresh copies of
production data. Done often and
routinely. Process used to take all one night and most of
the next day; with changes to infrastructure,
we have worked this process down to
about an hour or two. Clones are now so easy
that we don't even plan for them
anymore; the testing team simply requests a
refresh when they need it.
SQL Scripting
I create utility SQL scripts all the time,
including, but not limited to:
tablespace creation
table and index
creation
finishing processes
that failed
cleaning up space
allocation issues (moving tables and indexes to new tablespaces)
checking tables in the
database for improper entries
counting rows to verify
process results
special purpose queries
(usually from management)
In addition, I act as the resident SME for SQL
when the developers need help with complex
queries.
PL/SQL Scripting
Most of the application PL/SQL at our shop is
written by the developers. I debug PL/SQL when problems
with scripts arise, analyze other's PL/SQL code
for review, download and use utility scripts in PL/SQL
frequently, and have written a number of
utility scripts from scratch (large range of issues, see list of SQL
scripts above for a sampling of subjects).
While I do not have the same familiarity with PL/SQL that
everyday use would bring, I have a good grasp
of the basics and can write well-written PL/SQL. If
a job requires PL/SQL scripting, I will be able
to bring this skill up to expected speed quickly.
SAMBA File Server implementation
I maintain a set of files for database installs
separate from the developer's versions on a SAMBA
server / RedHat9 Linux box that I built and
maintain myself. This file server is extremely reliable,
has now run for years with at most a handful of
reboots, and is mapped to all the databases so
I can guarantee that I am installing the exact
same code to each database. I also maintain
the only comprehensive history of what has
happened over the years to our database in the same
file system, including detailed and dated log
files for every single software install made since
I built the system.
CVS implementation
On the same server I built a CVS version
control system. I still maintain the CVS system
for use with my programs.
Last Updated: Sunday, October 30, 2011 09:56:36 -0500