Tuesday, May 31, 2005
Hold tight...
I'm working on another backup/recovery entry that outlines a specific backup/recovery strategy for OLTP databases. At the same time, I'm digging into dbms_stats (due to a weekend mishap) to tweak a statistics gathering process. I hope to be reporting on both topics shortly...
Thursday, May 26, 2005
Interviewing
I'm still in the process of interviewing for an open DBA position in my company. As the potential candidate, you are trying to appear calm, cool, and collected but sometimes things come out that just don't make sense. Some of the best answers so far:
me: I see you've been a DBA on Linux, Solaris, and Windows. What percentage of time do you think you spend on each OS?
interviewee: About 50% on Solaris, 50% on Linux, and 50% on Windows.
me: What exactly is HP-AIX?
interviewee: It's HP's version of AIX that runs on HP-Superbowl.
me: Explain what happened on your last production recovery.
interviewee: My databases are setup so I never have to recover.
me: Oh? How is that.
interviewee: We use RAID 5.
I'll add more as I go.
me: I see you've been a DBA on Linux, Solaris, and Windows. What percentage of time do you think you spend on each OS?
interviewee: About 50% on Solaris, 50% on Linux, and 50% on Windows.
me: What exactly is HP-AIX?
interviewee: It's HP's version of AIX that runs on HP-Superbowl.
me: Explain what happened on your last production recovery.
interviewee: My databases are setup so I never have to recover.
me: Oh? How is that.
interviewee: We use RAID 5.
I'll add more as I go.
Wednesday, May 25, 2005
I don't know
When I first got into this business I had an incredible mentor, Perry. His South Carolina common sense advice guided me both in and out of the business world for years. He always stands up for what he believed, admitted when he was wrong, and treats others with respect. These values became the cornerstone of the consulting practice we created together.
During one of our first client meetings, the client asked us whether we could publish MS Access reports to the web (this was in 1996, mind you). We both looked at each other with a puzzled look for a second and then Perry spoke up. "I don't know", he said. I thought for sure we lost that project. Then he tacked on, "but we'll research it and get back to you tomorrow." We poured over manuals that night, tinkered with some code, and put together a simple report demo. The next day we made an appointment with the client and showed them our demo. After we finished the project, the client told us they signed the contract because we did what we said we would do and told them the truth.
I have been interviewing for a DBA position that is open in my company. I typically ask the interviewee tough technical questions on purpose so I can see how they might handle a situation under stress. Those people that say "I don't know" and then shutdown won't be able to handle the pressure. When they say "I don't know, but I would..." I know I have a keeper.
During one of our first client meetings, the client asked us whether we could publish MS Access reports to the web (this was in 1996, mind you). We both looked at each other with a puzzled look for a second and then Perry spoke up. "I don't know", he said. I thought for sure we lost that project. Then he tacked on, "but we'll research it and get back to you tomorrow." We poured over manuals that night, tinkered with some code, and put together a simple report demo. The next day we made an appointment with the client and showed them our demo. After we finished the project, the client told us they signed the contract because we did what we said we would do and told them the truth.
I have been interviewing for a DBA position that is open in my company. I typically ask the interviewee tough technical questions on purpose so I can see how they might handle a situation under stress. Those people that say "I don't know" and then shutdown won't be able to handle the pressure. When they say "I don't know, but I would..." I know I have a keeper.
Tuesday, May 24, 2005
Backup Top 11
I've got to admit, I'm pretty anal about backup and recovery. I've done some things without a proper backup in place and gotten burned because something went wrong. You only have to do that a couple times before you understand why you need a proper backup/recovery methodology. Before I let a new hire on my production systems, I make sure they understand how my backup/recovery works. In addition, I make them prove they know it by having them diagnose and recover a development database that I have crashed. I don't know everything about backup and recovery, but these are some lessons I have learned.
11. Send it off site
If you're going through the trouble of backing your data up to magnetic media, send it off site. In case of a fire (or god forbid another type of disaster) in your main data center you will be able to recover to a point in time. The best method is to duplicate your media and send the duplicate off site. If you can't duplicate your media, send it off site to a company that can return it to you 24x7. Yes, it's a pain duplicating your tapes and sending them off site, but when your power goes out at 16:04 (like in 8/2003 in the US) the banks will be closed and you won't be able to get to your media until the next business day.
10. Export/import is not a primary backup methodology.
Export and Import are fine tools for transferring data between databases as long as you can prevent changes from occurring. People usually setup export as their backup for one of two reasons; they feel "safe" because they have a copy of their data on disk or because it is easy to configure. The drawback is that using export for backup will only give you a single point-in-time to which you can recover. It is very unlikely that your database will crash the moment the export finishes. There are a couple of valid reasons why you want to use export as a secondary backup method, but as your primary backup methodology, forget it. If you do use exp/imp for a secondary backup method, make sure you use the consistent=y option to get a consistent copy of your data and use compress=n to make sure all your data doesn't go into one extent.
9. The needs of the business will dictate the backup methodology and frequency.
The rules under which your business operates will dictate the appropriate backup methodology. If your database must be up 24x7, there is no doubt you will need to run some sort of online backup (hot backup). If you have a short window for your Mean Time To Recovery (MTTR) it may mean a full backup at least once a day.
8. Have at least two backups available at all times.
I have talked to so many people that recycle their backup media every day. They stick the tape in the drive and backup their database. The tape remains in the drive for the next day when the backup overwrites the previous day's backup. When the database crashes during the backup, you have no way to recover.
7. Use RMAN over in-house developed scripts.
In-house developed scripts are error prone. Sure, there are a number of scripts out there that work in certain circumstances. RMAN works in every environment. Learn it, understand it, use it. Yes, it's different. The ability to run incremental backups alone makes the learning worthwhile.
6. Use a recovery catalog over the controlfile.
Both your recovery catalog and your control files store metadata about your database. The amount of history you can keep in the controlfile is limited by the control_file_keep_time init.ora parameter. In addition, the controlfile only stores information about the current incarnation of the database. If you ever need to restore before a resetlogs, you must use a recovery catalog. Last but not least, a recovery catalog lets you store your backup and recovery scripts whereas the controlfile does not.
5. Know how to do basic restore scenarios.
Your restore/recover methodology must include three basic recovery scenarios; recover the entire database, recover a tablespace, and recover a datafile. Most other recoveries will be a modification of these three basic types.
4. Not running in archivelog mode is a disaster waiting to happen.
When your database runs in archivelog mode you have two advantages. First and foremost, you can recover to any point in time. Second, you can recover from user DML errors by using logminer.
3. Cold backups are worthless (on a production system).
Cold (offline) backups offer several disadvantages but no advantages to a backup plan. The database has to be closed for a cold backup. The cache is empty when the database comes back up. All the SQL has to be reparsed. There are times you may want a to use a cold backup for maintenance reasons (major upgrade, moving database to a new host), but they offer no advantages in a daily backup plan.
2. Write it down.
Who knows what kind of disaster you will have. Maybe your building will be the next WTC. Maybe you got hit by a bus at 02:12 while you were coming in to do the restore. Maybe you've been at work for 18 hours and your brain is fried. Your thinking during a recovery should be limited. Write down your recovery scenarios so your manager could perform them.
1. Practice, Practice, Practice.
Practice your restore scenarios. You can be confident in your recovery because you just did it three months ago. If your test recovery doesn't work, it's better to know before you need it.
11. Send it off site
If you're going through the trouble of backing your data up to magnetic media, send it off site. In case of a fire (or god forbid another type of disaster) in your main data center you will be able to recover to a point in time. The best method is to duplicate your media and send the duplicate off site. If you can't duplicate your media, send it off site to a company that can return it to you 24x7. Yes, it's a pain duplicating your tapes and sending them off site, but when your power goes out at 16:04 (like in 8/2003 in the US) the banks will be closed and you won't be able to get to your media until the next business day.
10. Export/import is not a primary backup methodology.
Export and Import are fine tools for transferring data between databases as long as you can prevent changes from occurring. People usually setup export as their backup for one of two reasons; they feel "safe" because they have a copy of their data on disk or because it is easy to configure. The drawback is that using export for backup will only give you a single point-in-time to which you can recover. It is very unlikely that your database will crash the moment the export finishes. There are a couple of valid reasons why you want to use export as a secondary backup method, but as your primary backup methodology, forget it. If you do use exp/imp for a secondary backup method, make sure you use the consistent=y option to get a consistent copy of your data and use compress=n to make sure all your data doesn't go into one extent.
9. The needs of the business will dictate the backup methodology and frequency.
The rules under which your business operates will dictate the appropriate backup methodology. If your database must be up 24x7, there is no doubt you will need to run some sort of online backup (hot backup). If you have a short window for your Mean Time To Recovery (MTTR) it may mean a full backup at least once a day.
8. Have at least two backups available at all times.
I have talked to so many people that recycle their backup media every day. They stick the tape in the drive and backup their database. The tape remains in the drive for the next day when the backup overwrites the previous day's backup. When the database crashes during the backup, you have no way to recover.
7. Use RMAN over in-house developed scripts.
In-house developed scripts are error prone. Sure, there are a number of scripts out there that work in certain circumstances. RMAN works in every environment. Learn it, understand it, use it. Yes, it's different. The ability to run incremental backups alone makes the learning worthwhile.
6. Use a recovery catalog over the controlfile.
Both your recovery catalog and your control files store metadata about your database. The amount of history you can keep in the controlfile is limited by the control_file_keep_time init.ora parameter. In addition, the controlfile only stores information about the current incarnation of the database. If you ever need to restore before a resetlogs, you must use a recovery catalog. Last but not least, a recovery catalog lets you store your backup and recovery scripts whereas the controlfile does not.
5. Know how to do basic restore scenarios.
Your restore/recover methodology must include three basic recovery scenarios; recover the entire database, recover a tablespace, and recover a datafile. Most other recoveries will be a modification of these three basic types.
4. Not running in archivelog mode is a disaster waiting to happen.
When your database runs in archivelog mode you have two advantages. First and foremost, you can recover to any point in time. Second, you can recover from user DML errors by using logminer.
3. Cold backups are worthless (on a production system).
Cold (offline) backups offer several disadvantages but no advantages to a backup plan. The database has to be closed for a cold backup. The cache is empty when the database comes back up. All the SQL has to be reparsed. There are times you may want a to use a cold backup for maintenance reasons (major upgrade, moving database to a new host), but they offer no advantages in a daily backup plan.
2. Write it down.
Who knows what kind of disaster you will have. Maybe your building will be the next WTC. Maybe you got hit by a bus at 02:12 while you were coming in to do the restore. Maybe you've been at work for 18 hours and your brain is fried. Your thinking during a recovery should be limited. Write down your recovery scenarios so your manager could perform them.
1. Practice, Practice, Practice.
Practice your restore scenarios. You can be confident in your recovery because you just did it three months ago. If your test recovery doesn't work, it's better to know before you need it.
Sunday, May 22, 2005
Who really cares about WEP?
When I bought my WAP for my home a couple years ago, I thought to myself "Why would I ever need WEP?" At the time, I was barely able to get a connection from my back deck to my network on the second floor. After all, I was on 3 acres and if someone is sitting in my driveway to steal bandwidth, then maybe they deserve it.
Fast forward to yesterday when we finally got everything unpacked. I didn't have a chance to setup of the office stuff yet, so I decided to dial in to my ISP and check mail. I flipped open the laptop and before I finished dialing I was connected to a network called "linksys". Cool. I clicked on my wireless icon, and then on "View Wireless Networks". A quick sampling yielded three unsecured WAPs ("linksys", "linksys-h", and "wireless") and two secured networks ("gassnet" and "SMC"). I got on "linksys" and "linksys-h" with pretty decent signal and browsed to a couple of my favorite sites.
Wait a second. If I can get on their network, they can get on mine. Not cool. Hmm, I have to think about this.
I got around to setting up my network today. First, I shut off the cable modem. Then I brought up the router and the WAP. I configured my WAP to only accept MAC addresses that I knew (my wife's laptop & mine) and I enabled WEP and picked a passphrase. Since my wife's laptop and mine were different versions of XP, the setup was a little bit different, but overall pretty easy once I figured out you don't use the passphrase at the client, but you use the hex string generated by the passphrase.
Both of us have become so dependant on wireless in our home. My next big wireless challenge will be getting another WAP and getting it to communicate with the existing WAP as a bridge so I can hookup my legacy equipment in the basement. Sometimes I long for those days when 56kbps dialup was fast enough.
Fast forward to yesterday when we finally got everything unpacked. I didn't have a chance to setup of the office stuff yet, so I decided to dial in to my ISP and check mail. I flipped open the laptop and before I finished dialing I was connected to a network called "linksys". Cool. I clicked on my wireless icon, and then on "View Wireless Networks". A quick sampling yielded three unsecured WAPs ("linksys", "linksys-h", and "wireless") and two secured networks ("gassnet" and "SMC"). I got on "linksys" and "linksys-h" with pretty decent signal and browsed to a couple of my favorite sites.
Wait a second. If I can get on their network, they can get on mine. Not cool. Hmm, I have to think about this.
I got around to setting up my network today. First, I shut off the cable modem. Then I brought up the router and the WAP. I configured my WAP to only accept MAC addresses that I knew (my wife's laptop & mine) and I enabled WEP and picked a passphrase. Since my wife's laptop and mine were different versions of XP, the setup was a little bit different, but overall pretty easy once I figured out you don't use the passphrase at the client, but you use the hex string generated by the passphrase.
Both of us have become so dependant on wireless in our home. My next big wireless challenge will be getting another WAP and getting it to communicate with the existing WAP as a bridge so I can hookup my legacy equipment in the basement. Sometimes I long for those days when 56kbps dialup was fast enough.
Thursday, May 19, 2005
Moving Day
Today is moving day. You don't realize how fast technology in the home has changed until you have to pack and move it! When we moved to our current house five years ago my wife and I shared a Dell 133 Mhz Windows 2000 box with 128M of RAM. I just upgraded to a 56K modem and had two phone lines installed so I could always get on the internet.
I spent the past two evenings packing my Sun Sparc 5, 2Ghz Linux Dell box, my Wife's 2.4Ghz Windows 2000 box, her Dell Lattitude, two Dell UltraSharp flat panel monitors, and a 17" Trinitron (from the Dell 133, no less). I am waiting for the last day to tear down the Linksys WAP, Router, cable modem, and my laptop just in case I need to "dial-in" to work.
The Dell 133 has since moved on to Mom & Dad's to help them discover the "new" world of the internet. When we move again in 364 days, the Sparc 5 and Trinitron will probably find themselves in the dumpster. Just more casualties in the grand technology upgrade.
I spent the past two evenings packing my Sun Sparc 5, 2Ghz Linux Dell box, my Wife's 2.4Ghz Windows 2000 box, her Dell Lattitude, two Dell UltraSharp flat panel monitors, and a 17" Trinitron (from the Dell 133, no less). I am waiting for the last day to tear down the Linksys WAP, Router, cable modem, and my laptop just in case I need to "dial-in" to work.
The Dell 133 has since moved on to Mom & Dad's to help them discover the "new" world of the internet. When we move again in 364 days, the Sparc 5 and Trinitron will probably find themselves in the dumpster. Just more casualties in the grand technology upgrade.
Monday, May 16, 2005
BBB, Part Deux
Well, DB has spoken. I received an email from Mr. Burleson last night. Below is the text of the email with the profanity edited out:
Bernard Mannes Baruch, possibly the last century's greatest stockbroker said it best:
"During my eighty-seven years I have witnessed a whole succession of technological revolutions. But none of them has done away with the need for character in the individual or the ability to think."
Hi Jeff,
I see that you think that you are cute, posting nonsense in the Oracle forum.
I've had it with motherf***ers like you:
Please, crawl back in to your hole, you c**ksucking f**got . . .
Bernard Mannes Baruch, possibly the last century's greatest stockbroker said it best:
"During my eighty-seven years I have witnessed a whole succession of technological revolutions. But none of them has done away with the need for character in the individual or the ability to think."
BBB
I wasn't really sure how I was going to start this blog out. I've been banging around some
I regularly post answers to questions in the forums on DBASupport.com. I believe one of the ways to become a better DBA is to try to solve problems encountered in everyday situations. By exposing yourself to other peoples problems, you gain diagnostic skills you might need in your own environment. In addition, you might just learn something in the process.
Some of my contemporaries have had issues posting on Burleson Consulting's Forums. I've met DB at the NC Oracle User’s Group a few years back and thought he was a reasonable fellow. Surely, I thought my contemporaries were exaggerating the owner's heavy handed administration of the forums. So I joined on and started answering questions. Later that day I got a personal email from DB. I answered a few dozen
Then I disagreed with DB and was promptly banned.
Subscribe to:
Posts (Atom)
