Pythian has been a rapidly expanding company over the ten months I’ve been here. About a dozen new employees have come on during that time, making the total 40. I have a few observations on habits and qualities new DBAs can bring to their jobs.

Take on-call every day during business hours. During my first months, while I was not in the regular rotation schedule for evenings and weekends, I would make it my responsibility to take on-call during business hours as soon as I arrived at the office for every business day. As a new DBA, I didn’t necessarily know how to respond, but there was a whole team behind me that could help me out if I didn’t find an answer in our groupware. The practice of regularly taking on-call allowed me to learn the different client environments quickly.

Cultivate Judgment. Very useful. Unfortunately it is not something measurable. As a DBA in an environment new to you, you have to be conscious of your limitations. If you have the slightest doubt of what you are doing, confirm it with another DBA. When working on a client’s production system, precaution is better than error (if only we applied the same respect to our ecosystems). Judgment will also come in handy for evaluating whether you have researched all possible solutions before calling for help in the middle of the night. (A checklist can come in handy here, e.g., groupware, Metalink, Google, et cetera).

Keep a journal of acquired knowledge. The more methods you use (voice, writing, reading, doing) to try to learn something, the better you will recall it. Therefore, keep a journal of stuff you learned during the day. Better, ask your boss if you can keep two journals, one with client-sensitive data, another one without it which you can keep with you beyond your time with the company in question.

Do Linux. If you know GNU Linux (or BSD), and you are comfortable with Vim (or Emacs), shell scripting, SSH, Perl, et cetera, you have a great head start. Why? With your Linux experience, you will be able to quickly adapt to enterprise systems, of which many will be Unix variants with these other tools.

Master Backups. If you feel any uncertainty about the recoverability of backups, it is an emergency. In the world of on-line 24×7 service (a no-sleep world — doesn’t that sound mad to you?), backups are the spine that holds the rest. Think of them as potential time bombs. If they are not looked after — if recovery planning and testing are not done — they will blow up in your face. Maybe tomorrow, maybe in a month from now, maybe in six months. But they will blow up, resulting in someone (probably you) losing a job.

I think these above items, especially the on-call aspect, will greatly help your entry into any DBA environment.

One of our clients found a bug with dbms_scheduler: it logs a job as successful when a ORA-01403 is thrown.

SQL> create or replace procedure x_no_data_found is
  v_dummy varchar2(1);
    begin
      select dummy into v_dummy
        from dual
        where dummy != dummy;
    end;
  / 

Procedure created.

SQL> exec x_no_data_found;
BEGIN x_no_data_found; END;

*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "JULIEN.X_NO_DATA_FOUND", line 4
ORA-06512: at line 1

SQL> begin
2 dbms_scheduler.create_job(
3   job_name => 'test_no_data_found',
4   job_type => 'plsql_block',
5   job_action => 'x_no_data_found;',
6   repeat_interval => 'freq=minutely',
7   enabled => true,
8   comments => '');
9 end;
10 /

PL/SQL procedure successfully completed.

SELECT LOG_DATE, STATUS
FROM DBA_SCHEDULER_JOB_RUN_DETAILS
WHERE OWNER = 'JULIEN';

LOG_DATE	STATUS
22-JAN-07 12.17.49.768139 PM -05:00	SUCCEEDED
22-JAN-07 12.18.49.849485 PM -05:00	SUCCEEDED

Our current work-around is to throw an application error.

(more…)

Doug Burns posted photos with his new teammates on his blog, and I felt they deserved a mention here too.

See Team 2 and some other Pythian people — (Darrin, not of Team 2, had to pop in unexpectedly in the last photo with Paul) — for yourself:
http://oracledoug.com/serendipity/index.php?/archives/1175-Team-2.html.

IT programs only teach you the programming and software architecture perspective. In DBA work, even when scripting, you have to consider the data as a tool. I’ll show you what I mean.

Recently, during a data center migration, my team and I found that one of our important client’s warehouse statistics were not being calculated. On top of that, we learned that they wanted the stats calculated differently than originally implemented, so we had to recalculate statistics for the previous month with a slightly modified script. But on top of that, some of those statistics were totals that could not be modified because they could only be calculated on the day the stats were collected (I’ll call these date-sensitive attributes in this post).

The team decided to keep the date-sensitive attributes as they were calculated before, but recalculate the attributes gathered after the day in question (date-insensitive attributes).

As someone who enjoys crafting a script or isolating a bug, my first thought was to hack the script so that it would ignore the values that must be ignored and modify only those that must be modified. But this would have meant I had to go through each statistic collected (there are 152 of them, for each day), and for each, modify the script to either keep the value already created or recalculate it. A long process, but more importantly, one very prone to error and bugs.

The more I modified the code or the data, the more I would be prone to error. Since copying is the thing computers do best, the other way to see it is: the more data I restore, the less I am prone to error. That’s when it clicked: make a backup copy of the table, modify the script to make calculations on all values according to the new requirements, and then restore the date-sensitive values.

The result: less coding, less risk of bugs. The lesson: you’re a DBA, so let the data — rather than the logic — do some work.

One bug that took me some time to squish was a lack of implicit date conversion between date and char data types. But that’s probably the subject of another post.