So you want to be an Oracle DBA? What does a DBA do? What does my week look like?
Frequently my wife asks me what my days are like and just what do I do at work. Well, I’m going to start telling you. My normal answer is a DBAs’ job is extreme boredom punctuated with moments of start terror. Lately there has been more terror then boredom.
So this week started off good enough. I was in the middle of recovering a standby database when I discovered there were gaps in the archive logs. I spent most of the weekend trying to get the standby database restored then realized I would have to ship down the archive logs from the primary site. The problem centered on control_file_record_keep_time being set too low. Well the archive logs had aged out of the control file. I had to do a couple of things.
1) extract the archive logs from ASM. This can be a trick, then I discovered the package dbms_file_transfer. This package allows you to pull files out of ASM so you can deal with them as real file. All I had to do was create two directories. One for the ASM source and the second directory the target.
2) The second part of the problem was getting the files down to the standby database. Well port 22 is closed and I have to get the SAN Administrator to replicate the files and then get the unix admin to mount the LUN then go to change control. Bottom line, someone else needed to transfer some files so I was asked to defer transferring the data until the other person is done. I hope the transfer will be complete by Monday and I can finish fixing the standby database. So far over a week to fix something that should have took me less then one day.
The following code extracted the logs that I needed to the file system.
declare
x number := 214;
fname varchar2(256);
begin
while x < 440
loop
x := x+1;
fname := ‘1_’ || to_char(x) || ‘_773923456.dbf’;
dbms_output.put_line(fname);
dbms_file_transfer.copy_file (‘ARCHIVE_SOURCE’, fname, ‘ARCHIVE_TARGET’, fname);
end loop;
end;
/
Next issue for the week. This is what I really love to do. Tune the database. I got a call from an ole’ friend who’s query was taking a while to execute. He is a sharp unix admin and knows enough about Oracle and SQL to generate the reports that he needs. Well his email was short and sweet. Here’s the code, it’s slow, fix it. I spent about an hour re-writing his query and took his report from executing in over 2 days down to less then two minutes. It is important to understand access paths and how the optimizer works.
Part of my job is to also deal with external customers. There are some customers who are software vendors that provide services to my customers customer. (My customer is a very small government agency. Their customers are banks and other government agencies.) I don’t know how it happened but if the help desk can not answer the question or address the issue, me and another friend gets the call based on the problem. All data transfer issues go to me. All organization issues go to me. Custom reports go to me. korn shell scripts got to my friend. Thank goodness for small favors.
So this week I spent a lot of time on the phone on conference calls with large external customers. Their issues are simple; we have to get the information securely, accurately and reliably. I’m happy to say, the issues are getting addressed most of the issues are from the processes that all the organizations have in place. I sometimes have to remind myself to respect the process. But there are times when the process is broke and needs to be addressed.
Oh that brings up another problem, I’m not a LAN engineer and I don’t have access to setup VPN tunnels. So if there is an issue setting up a VPN tunnel then I send that on to someone else. Ya’ need to understand that. Well one of the vendors called me and said that one of their customers has been having problems with their VPN tunnel for months. I sent it on to our LAN engineer and asked him to look into the problem. I get a terse email back saying “all I can do is open a ticket with <redacted>.” That kinda rubs me the wrong way. Pick up the phone, call the customer, find out what the problem is? This does a few things. One it lets the customer know they are not being ignored. Two you have an understanding of what the problems are. This way when someone asks what’s going on with <redacted> then you can answer it with some degree of intelligence.
And that brings up another issue. A couple weeks ago I found out about a customer who has been having troubles sense last January. It took quite a bit but I pulled together all the resources to address the issue the same day. But the problem is still dragging on. I have emailed <redacted> network engineer again today to get a status and he says, “working on it, I’ll let you know.” Well there are a lot of stressed out people who want the problem fixed. I’m not going to say <redacteds’> network engineer does not know what they are doing but I will say one of our top engineers has serious questions regarding this person’s ability to address the issue. No one wants him to look bad in front of his boss, so I’m trying to be subtle and have one of our senior engineers help him out with his issues.
Where was I going, oh yea’ the life of a DBA. You will find you will get tasked with things that seem outside of your core skills. Embrace them and do the best job you can.
Today I was asked to install Oracle 11.2.0.3 on a blade for our development group. I got started after the unix group stood up the blade and the problems job kept coming. Packages were not installed, accounts were not setup correctly. Even logged in as oracle I could not copy the oracle binaries to the blade. I’m still not done, I’m hoping the unix group will finish installing the required packages and make the changes to /etc/system so I can finish setting up this database. Am I frustrated with this, yea’ I am. Normally I can have a database setup and running in very short order, but having to go back and forth is irritating. There is a baseline install that gets done for blades. Perhaps I will ask the unix group to provide me with a copy of the document and make some edits to include setting up for oracle databases. This way I know what to expect when I get the server.
Other than that, there were many meetings; Change control is an important meeting and you really need to understand what is going on and what the impact of changes are. There is the daily MMM, some folks call it the morning stand up meeting . The most important meeting had to do with the technology refresh. We discussed configuring RAC for one of our critical systems to deal with surge in processing that happens once a year. Our old configuration has three servers. One is the primary database server, a second is a standby database located in another state. The third is a standby database in the rack with the primary database. We set it up that way because I would always prefer to have an extra set of data files in my back pocket in case something happened. This will be changing to configure RAC for performance.
Of course there was the request to get a cold backup of a production database off the standby database to refresh a test environment. I’ve cloned databases from rman, but now I’m going to figure out if I can create a clone off a standby database.