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.
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.
posted by Eric S. Emrick at 9/08/2006
6 Comments:
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.
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.
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
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.
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.
I believe you have also heard about the access undelete program
Post a Comment
<< Home