Friday, September 08, 2006

Don't Get Caught With Your GUI Down

In a recent wave of interviews I was amazed how little prospective DBA candidates knew about user-managed hot backups. Most could give the BEGIN and END backup stuff and convey that it causes more redo to be generated during this time. But, when asked to give a little more of their insight into the mechanics or performance implications, 9 in 10 had plenty to say - just nothing that was correct. 90% could not explain the significance of putting a tablespace in hot backup mode. That is, why do it? Why not just copy the file while the database is open and cooking? Of course, most understood that Oracle needs us to do this so that the backup is "good", but few knew how Oracle went about doing it. Moreover, few knew why the extra redo was generated. And most amazing, nearly all thought the data files were locked and changes were written to the redo logs and reapplied when the END BACKUP command was given. Where are DBA-types reading this? Irrespective, the DBA population is evolving.

I am not basing my opinion on one simple question concerning user-managed backups, but a host of other questions given as mental exercises. What are some of the Oracle wait events? What do they represent? How would you go about troubleshooting systemic response time degradation in your production database? What is extended SQL tracing and why use it? Time after time candidates struggled to give lucid, well thought out responses. A vast majority of responses could be summarized as, "I would go into OEM and check for A or B." I don't have a problem with using OEM, but usually the A’s and B’s had little relevance to the question.

The herd of available DBAs that are able to navigate the database using native SQL to get at critical performance diagnostic information has thinned dramatically. Sometimes I wonder what would happen to some of these shops being supported by some I interview if OEM, Database Control or Grid Control took the night off. When relegated to digging into the database and troubleshooting armed only with a SQL prompt, many appear to be lost. I certainly appreciate what the Oracle GUI database management tools bring to the table. I even like them. My point is, don't throw away your shovel just because you have a snow blower. The day will come when your GUI will fail you and it will be just you and your SQL prompt.

P.S.> Oracle does not lock the content of its data files during the course of a user-managed hot backup. Actually, Oracle only locks one thing, the master checkpoint SCN inside the file header. Some other constructs in the file header stay mutable. Blocks in data files being backed up can be modified as per normal database operation. The changes to blocks are indeed recorded in the redo, but they are not replayed when the END BACKUP is issued. More redo is possible because Oracle must accommodate the potential presence of fractured blocks.

9 Comments:

Anonymous RobH said...

I'm running into the OEM problem a lot lately. I've always used just a good old fashioned sql prompt, and maybe a few scripts I've built to do most of my work. I find that too many use OEM and don't get "what" is happening (or what OEM is doing).

I'm very concerned that most new DBA's memorize the exam questions, get certified and are experts all of a sudden.

I've never thought I was an expert and spend most of my time re-reading documentation and trying to "get" the database. There really is a lot to learn.

The worst part is that they see me using sql and are confused and laugh at me "why are you doing it the hard way?"

...hard way? Seems easier to me, but I guess its all perspective.

9/12/2006 9:34 AM  
Blogger Eric S. Emrick said...

Very well said RobH. Actually, I think you said what I was thinking better than I did. There is nothing wrong with knowing OEM, just don't use it as a crutch.

9/12/2006 9:22 PM  
Blogger Andy Campbell said...

I've always found doing interviews quite worrying - the strange answers you get on how Oracle works, I'd rather they just said they didn't know rather then bluff. I usually ask what causes "snapshot too old" errors, and not very often get a correct answer.

I think a lot of the OEM only problem is down to Oracle University. When I was doing DBA training OEM was 30min section at the end of the course. I believe now its basis of the course, with a "you can press that button to see the SQL its going to do".

9/13/2006 2:27 AM  
Blogger jason said...

It's fair what you say about OEM (I don't use it), but maybe your question was slightly on the theoretical side?

What I mean by that is, the most important thing is knowing you have to do the alter tablespace begin backup and then backing up, knowing the why's might not be a practical requirement for most dba's day to day.

The other point surely though, is that perhaps putting a tablespace into hot backup mode is old hat, everyone is using rman, right?

jason.

9/25/2006 3:27 PM  
Blogger Mark G said...

I've often thought about OEM and in it's current state. I really like a lot of the functionality/features that it provides.

I dont doubt for a minute that Oracle is simply providing a much better toolset to manage database.

My concern is really a subset of the concerns posted here. Yes it's fine and good to know the OEM gui. But what happens if OEM repository goes down? What happens if it becomes corrupted. What happens if you can only login to database at command prompt. What does one do then?
What happens if you have to do restore of database from command prompt using RMAN because OEM is down? What happens if you are new on job and organization is doing cold backups noarchivelog mode,database crashes and you have to restore database?


My fear is that many would be looking for a new job simply because they couldnt log into database from command prompt much less get database started from command prompt!

9/26/2006 1:46 PM  
Anonymous Anonymous said...

I must say that we had the same experience with the hot backup question. A number of people specified 'the data files were locked and changes were written to the redo logs'. We all could not understand why so many people answered the question wrong.

Then one day browsing the web I came across www.ora-600.net (Jeremiah Wilton) site. A certain pdf on the site highlighting certain database misconceptions reveals, that not only had a book preached the wrong answer about user managed hot backups, but Oracle Education had as well.

Case solved for us

9/27/2006 12:00 PM  
Blogger Eric S. Emrick said...

jason said

It's fair what you say about OEM (I don't use it), but maybe your question was slightly on the theoretical side?

...

The other point surely though, is that perhaps putting a tablespace into hot backup mode is old hat, everyone is using rman, right?


Eric said...

Indeed the question was on the theoretical side, by design. While I disagree that everyone is using RMAN, it has its merit. However, why use RMAN? Are there any compelling reasons to use RMAN other than a bunch of metadata that helps with restoration and recovery? Much of the drawbacks inherent to user-managed backups are remedied by RMAN. So, if you understand the mechanics of user-managed backups you then understand some of the benefits of RMAN. If, as you say, user-managed backups are old hat, that is all the more reason I would expect any DBA with a reasonable amount of experience to understand its mechanics. Just because CREATE INDEX is old hat I still want to understand how this activity might impact my database.

9/27/2006 7:38 PM  
Anonymous Jeremiah Wilton said...

The backup question is a very nice puzzler for interviews. For me, it doesn't matter if a candidate doesn't know the answer. I only care that they can reason it out to some degree and maybe put it all together. Both of the items mentioned here ('how do hot backups work','name ANY wait event') are great entry-level DBA questions. In my hundreds of interviews, many many DBAs with years of experience and six-figuire salaries can't even venture guesses to either. And every time I find it shocking.

10/30/2006 11:37 PM  
Blogger daspeac said...

I believe you have also heard about the access undelete program

12/06/2010 4:12 PM  

Post a Comment

<< Home