Blog Host: Brian Peasland - Database Administrator, SGT, Inc. READ ENTIRE BIO

One of the reasons I get involved with Web sites like this one, SearchOracle.com's Ask The Experts and the Quest Pipelines is that it helps me become a better DBA. There are many times someone asks me a question, and I do not know the answer. But after some careful research, I am able to come up with the answer, help someone out and learn something new in the process. One great source of that research is the Oracle documentation, which is why I feel strongly that Oracle professionals should be very familiar with the documentation (see my earlier blog entry on this topic). Another thing happens when you try to explain a concept to someone: That concept becomes clearer in your own mind. I urge all Oracle professionals to get involved in various newsgroups and forums. Answer questions that you are able to answer. Ask questions when you do not know the answers. Read questions and answers that others have provided. All of this is a great way to enhance your career.

I just got done reading an article titled Oracle myths debunked, posted on SearchOracle.com by Mike Ault. It is a good article. So many people learn these myths and take them for granted that they are the truth. I've been guilty of it in the past. I know that many of the greatest Oracle professionals have been guilty of it at least once. All of us have been guilty of believing a myth at one point or another. It is always advantageous to test out the myth's hypotheses for yourself to see if the point being made is true or not. For instance, one such myth is that locally managed tablespaces using the AUTOALLOCATE extent allocation method will produce extents sized at 64 KB, 1 MB and 8 MB. It is possible to have a large number of different extent sizes. I stumbled upon this myth-buster in one of my databases many years ago. I queried DBA_EXTENTS on a tablespace that experienced a high number of extent allocations and de-allocations. I found more than 15 different extent sizes in that tablespace. So do not take everything at face value. Test it out for yourself. In the process, you will gain a better understanding of how things work along with great skills in testing.

One of the myths that Mike Ault talks about he coins the "Rules of Thumb" myth. In his article, he states "Many Oracle professionals believe that 'rules-of-thumb' (ROT) are very dangerous, and note that if a ROT can be shown as invalid, even in a single artificial test, the ROT is not scientifically valid and therefore useless." I am one of those professionals that believe ROTs can be dangerous. If taken on face value, a ROT ends up becoming a myth simply because people do not take time to understand the exceptions to the rule. I believe that you should know and understand the exceptions to the ROTs that are out there so that you will know when the ROT does not apply to you. So to some degree, I am disagreeing with Mike's premise here. If I am reading his myth correctly, Mike is trying to state that ROTs are valid. I would say that any ROT is valid under certain conditions, and it would be to your advantage to know what those conditions are. Under other conditions, the ROT is invalid and should not be used. Blind faith in a ROT becomes a potential myth to be busted.

Again, I would like to say thanks for letting me ramble on in this space for the last two weeks. Maybe some day you'll see me back again. In the meantime, should you ever need any assistance, feel free to visit me any of the other panel of experts on SearchOracle.com.

Editor's Note:
If you want to take Brian's advice and post to a forum to ask and answer questions to increase your knowledge and make you a better DBA, check out TechTarget's ITKnowledge Exchange. You can ask your question to set of IT professionals with specific skill sets.
Posted by Brian Peasland Oracle certification: Why bother?
05 MAY 2005 14:23 EDT (18:23, GMT)
There are many people out there who will be willing to tell you that it is not worth the effort to obtain certification on Oracle products. These individuals will say things like the following:

• I'd rather hire someone with experience and no certification than someone with certification and no experience.

• All certification proves is that you can memorize information enough to pass some tests. It does not prove that you can do the job.

• It is too easy to obtain certification. Everyone is doing it.

So why bother obtaining certification? If all of the above is true, why would I want to go through the trouble of becoming certified? There are many reasons. My reasons for obtaining certification are as follows:

• If you are competing with another individual for a job opening, and you both have identical experience and skill sets, certification may be the one thing that gives you an edge.

• If passing the certification exams is as easy as some suggest, then obtaining certification shouldn't be that much work. So there is little effort on your part.

• When you study for the certification exams, you will be exposed to a wide range of concepts concerning Oracle. It is highly unlikely that the daily duties in your current job would expose you to this breadth of information.

• Exposure to all of these different areas will enhance your career. At some time in the future you will encounter a technical challenge and you will remember something from your certification studies that you can use to solve that challenge.

• Certification shows that you are serious about your craft. Your employer may appreciate the extra effort on your part.

Admittedly, I am not the most knowledgeable person when it comes to Oracle. There are others out there who know much more than I do. And it is impossible to know everything about Oracle. The product is just too complex. That being said, I do feel that I know quite a bit about the Oracle RDBMS and how it works. When I have a problem, I tend to hit all of the simple things first, and also delve into some of the more complex solutions. Before I ever file a TAR, you can bet I've spent quite a bit of time researching all through Metalink to see if I can solve the problem without the assistance provided me through a TAR. When I do fill out that TAR, I tend to provide as much background information as possible. The following hypothetical example illustrates my typical, albeit exaggerated, experiences with Oracle Support.

Me: I have a problem. When creating a Materialized View (MV), I get the ORA-00942, "table or view does not exist" error. I am the owner of the underlying table and the owner of the MV. I looked at the documentation and verified that I have the CREATE MATERIALIZED VIEW and CREATE TABLE system privileges. I have traced my session containing the error and have uploaded this trace file to Metalink.

Oracle Support (OS): In order to be able to create a Materialized View, you must have the CREATE MATERIALIZED VIEW system privilege. Consult the documentation for further information.

Me: As I clearly stated, I have the required system privileges. Can you please have a look at my trace file?

OS: Have you verified that you have the required object privileges on the underlying tables?

Me: Of course I have the object privileges on the underlying table to the MV! The MV and the underlying table are owned by the same user! Therefore, I have the required object privileges. I own the table! Can you please look at that trace file?

OS: Before I can look at your trace file, I will need to you upload a copy of your Alert Log at the time this error occurred.

Me: What do you expect the Alert Log to show? ORA-00942 errors do not show up in the Alert Log. The Alert Log will show you nothing. But since you asked, I uploaded it anyway.

OS: After reviewing your trace file, I see that you are experiencing a known bug. Download patch number 123456.

Ugh! Notice that every question was answered if the OS person would have taken time to fully read and understand my information when I created the TAR. My example is an exaggeration, but only a slight one. Don't believe me? Let me give you some exact quotes from a recent TAR I filled out a week ago, but first, some introductory information is needed.

I was experiencing a problem with my database I needed assistance with. I filled out a TAR. The person who helped me with the TAR indicated that I hit a known bug. Since I was running Oracle 9.2.0.6 and the bug was not fixed until 10g, I would need a backport of the bug fix, which would be provided to me if I needed it. I indicated that I would be upgrading to 10g in the very near future. We both decided that I would not need the backport of the bug fix, but if my upgrade plans were delayed, I could request the backport. Well, my upgrade plans were delayed and I wanted the backport of the bug fix. You cannot reopen a closed TAR, so you are only left with filling out a new TAR, which I did. In my TAR description, I included the following:

I originally noted this problem in TAR 4312318.994 and it was found that a known bug would be contributing to this error. At the time of the TAR, I was planning on upgrading to 10g in the very near future. However, our upgrade plans have been put on hold for the next few months. The errors cause unwanted downtime and have been occurring once a week. At this time, I am requesting backport patch 3901424 for my Oracle 9.2.0.6 (32-bit) on Solaris database.

Since a backport does not already exist for the bug on your platform, I will need proof that you are indeed encountering this bug. Please upload your alert log and trace file.

As I stated in my TAR description, I have a previous TAR #4312318.994 which was opened concerning this problem. If you review this TAR, I'm sure you will obtain all of the proof that you need, as my Alert Log, RDA output and trace files are all part of that TAR.

The way the process works is I will need an alert log and trace file showing the error tied to this tar. I can use 4312318.994 as a reference, but this TAR was closed on March 30th. Therefore, I will need recent traces and a recent view of the alert log showing the error.

So how did I get the backport of the bug fix? Did I reproduce my work? No way! I simply uploaded the same exact trace files and Alert Log I uploaded for the first TAR. I received the backport for my bug fix. Somewhere, I hear a blonde spikey-haired woman screaming "STOP THE INSANITY!!"

Editor's Note:
Do you have Metalink stories of woe? Share them and I'll give away an Oracle book to the best story that comes in to me by June 1.

--Dana
Posted by Brian Peasland Random musings
03 MAY 2005 17:20 EDT (21:20, GMT)
This week is the annual IOUG Live! Conference in Orlando, Fla. Hopefully, you are there. I am one of the unfortunate ones who was not able to make it to the conference this year. But it is definitely one of my favorites, along with being in my favorite conference location.

My last blog entry talked about using the free Expect programming language to automate many tasks in your database administration activities. Another free programming tool that is making many inroads into the enterprise is PHP, a server-side programming language used to create dynamic Web pages. In this vein, PHP is similar to JSP, ASP and Perl. Oracle Corp. has recognized PHP's pervasiveness in the enterprise and responded by showcasing Oracle/PHP resources on Technet. In case you missed it, Oracle Corp. sent out a warning for those who recently applied the April 2005 cumulative security patch to their Oracle 9.2.0.5 and 9.2.0.6 databases. Apparently, the patch causes problems with the issue addressed by Security Alert 65. If you have not applied the patch Security Alert 65, then you will need to do so as soon as possible. Simply go to Metalink and download patch 2701372. If you are unsure if you ever applied the patch for Security Alert 65, please do so just to be safe.

In today's questions, I describe what I think the biggest challenges of a DBA are. This could have been an interesting blog entry, but I'll just point to it instead.
Posted by Brian Peasland Expect the unexpected
02 MAY 2005 11:58 EDT (15:58, GMT)
Many years ago, a sys admin I worked with showed me a wonderful programming language called Expect. I've found that this programming language is very useful for a number of situations.

Expect is freely available. So the cost is very attractive to everyone! You can find information on this language here, along with downloads of the language's interpreter and other resources.

Basically, with Expect, you launch a tool and your Expect program is told what to expect! When an event happens that the program is told to expect, the program will respond with the given response. I realize that this may not make sense, so let's follow a simple example that everyone should be familiar with. Let's assume you perform the following command line operation:

C:\>sqlplus system

SQL*Plus: Release 10.1.0.2.0 - Production on Sat Apr 30 13:39:36 2005

Copyright (c) 1982, 2004, Oracle.  All rights reserved.

Enter password:

The Expect programming language is a great tool for you to automate interactions with any utility on your system. Expect works great for command line tools as well as GUI software. I'll give you a few example of how I've used Expect to make my life better.

One of the first uses I've seen for Expect was to automate changing of passwords. My friendly sys admin had written a program to automate changing the root password on multiple Unix servers. Prior to Expect, when this sys admin had to change the root password on 40 Unix servers, it meant signing on (interactively) to each server, issuing the passwd command, entering the values, signing off and repeating the process for the rest of the servers. Expect can perform a password change on all 40 Unix servers in under three minutes! No more mundane work to be performed over and over again.

Taking this example, I wrote an Expect program that I use to change the SYS or SYSTEM password. A portion of my Expect program used to change the passwords in an Oracle database can be seen below, just to give you a feel of how it works:

# Set up list of remote databases
# Four databases in this list so far. 

set remotedb { orcl orcltest orcldev orclprod }

# Step through list of databases and change password
for {set i [expr [llength $remotedb]-1]} {$i>=0} {incr i -1} {

   # Get remote database from list
   set remote_db [lindex $remotedb $i]

   # Invoke SQL*Plus. The spawn command will start a program
   spawn sqlplus $username@$remote_db
   # After envoking sqlplus with the username and remote db, 
   # expect a password to be asked for. 
   expect "password:"
   # send the current password
   send "$old_password\r"

   # Check if signon was correct.
   # If correct, change password then exit sqlplus
   # If incorrect (ORA-01017), issue CTL-C to break out
   # If other errors, issue CTL-C to break out
   #    and issue the error message returned

   expect {
      "SQL>" { send "ALTER USER $username IDENTIFIED BY
 $new_password1;r"
               expect "SQL>"
               send "exit\r" }
      "ORA-01017" { send -break
                    puts "Invalid password for $remote_db"
                    puts " "}
      -re "ORA-(.*):" { send -break
                    puts "Error for $remote_db: $expect_out(0,string)"
                    puts " "}
      } ;# expect
   }    ;# for

I've written other Expect programs for other uses as well. For instance, I might have a SQL script I want to run on all of my instances. I have an Expect program that uses SQL*Plus to connect to all of my databases and then run the supplied script. The script name is a variable to the program so I can use the Expect program over and over again for many different scripts. Another use that I've found is to FTP patches to all of my database servers. I would download a patch to my workstation from Metalink. I used to FTP this patch to each and every database server. With Expect, I can automate invoking the FTP command line utility and put the patch on my database server.

You may already be thinking of other uses for Expect. I hope that you will find this little gem of a programming language useful in your environment.
Posted by Brian Peasland To the moon, Alice!
29 APR 2005 20:28 EDT (00:28, GMT)
In my first blog entry, I talked about backup and recovery. In that blog entry, I said "A DBA should practice recovery from a number of possible failure scenarios. Practicing these recovery scenarios not only ensures your backup methodology is sound, but it also helps when a real-life recovery situation is presented." Today on SearchOracle.com, a Q&A piece was posted titled Advanced backup and recovery. In this article, Tim Quinlan is quoted as saying "Even seasoned DBAs need to continually practice backups and recoveries to keep their skills sharp and to ensure that the procedures being used still work. There is nothing worse than having to perform a critical recovery when you aren't sure it will work." I like to hammer the point home about backup AND recovery often. This probably won't be the last you hear from me on the subject. And as you can see, I'm not the only one. Tim's Q&A article is a good read if you haven't already seen it.

Have you registered for your chance to go to space?!?! In case you missed it, the grand prize in the Oracle Space Sweepstakes is a "suborbital space flight. And if you can ever think of a valid reason for a Java developer to be in space, please let me know. The only thing I can think of is…write once, run everywhere…even in space.
Posted by Brian Peasland Coincidences of time
28 APR 2005 13:26 EDT (17:26, GMT)
Ever find yourself thinking "Hey! I was just saying that!"?

My blog entry yesterday talked about security for your Oracle database. This has become a very hot topic for all Oracle DBAs. Just today, I saw a posting titled Learning Guide: Oracle security addressing this issue on my daily newsletter from SearchOracle.com. This article contains many helpful links to resources on Oracle database security. I think they were reading my daily blog...or is the timeliness of this just a coincidence?

As many of you may be aware, Oracle Support released their quarterly patch on April 12th. This patch is available on Metalink if you have an Oracle Support contract. SearchSecurity.com discusses this patch in this article.

I am planning on installing this patch on many of my database servers this weekend. Like many people, I do not get the chance to install the patch the moment it comes out. I have to wait for a maintenance window to open so as to minimize the impact of downtime on our end users. This patch contains fixes 89 security vulnerabilities. Since this patch is cumulative, it is possible that you have already fixed many of these. And most of this may be old news to many of you by now.
Posted by Brian Peasland Security: It's becoming my biggest headache
26 APR 2005 23:09 EDT (03:09, GMT)
I think that many DBAs that have been around for a while have been cognizant of the many aspects of database security. I've been dealing with security in the database for many, many years. But lately, security has become my biggest headache as a DBA. Today's DBA needs to be even more knowledgeable of security in the database. It is not enough to know about users, passwords, roles and object and system privileges. You now need to know about firewalls, global application contexts, single sign-on, data encryption and the list goes on and on.

I currently work as a contractor in a federal government facility. Our particular branch of government has grown increasingly concerned with the security of its IT infrastructure. This is a good thing. In the past, security was pretty lax with holes big enough to drive a virtual school bus through. This branch of the government is now taking a really strict stance on IT security. We have to go through a rigorous certification process for all systems connected to the Internet. We are constantly being proactively scanned for various security holes. Once security holes are found, we have to scramble to close those holes or risk having our servers shut down and out of business. This activity certainly keeps us hopping! While this type of approach is aggressive, it helps to keep your systems as secure as possible. Many companies would be well-served by employing a third-party resource to proactively scan their network looking for any vulnerabilities a hacker could exploit.

Security issues are becoming much more diverse as well. The security-related information the Oracle DBA needs to understand is very wide-range and diverse. The first resource for the Oracle DBA is the Security Guide in the Oracle documentation. One of the best Oracle security Web sites is by a gentleman named Pete Finnigan in the UK. This Web site is definitely worth reading.

Just last week, I had to deal with a SQL Injection vulnerability. At the time, I was only cursorily familiar with SQL Injection. Pete Finnigan's Web site had a lot of useful information. In my facility, as soon as people heard "SQL," they immediately assumed that the vulnerability was with the database. SQL Injection is more of a problem for the application, not the database. You cannot simply apply a patch to the Oracle database and be covered. Rather, the application must be coded to not simply pass any and all data from the application to the database and pass any and all results, including error codes, back to the user. With a SQL Injection exploit, the hacker can use error messages to learn a great deal about your database. If you have not had a chance to learn about SQL Injection, I would highly recommend learning as much as you can.
Posted by Brian Peasland Documentation: The painful truth
26 APR 2005 14:49 EDT (18:49, GMT)
I thought for my second blog entry that I would talk about a topic that most people really don't like to discuss: Oracle documentation. There are two major problems with the Oracle documentation.

1. The material is very bland and boring too read.
2. The set of documentation is very voluminous, often making it difficult to find what you are looking for.

During my Oracle career, I have been a frequent contributor to SearchOracle.com's Ask The Experts as well as the Quest DBA Pipelines. A great majority of the questions asked and answered would never be asked if the poster would have taken the time to read the Oracle documentation. If you've ever visited the Usenet newsgroups devoted to Oracle (comp.databases.oracle.*), you may find that the answers to many questions are "RTFM." For a translation, visit the Acronym Finder. For the easy questions where the answers are already documented, you would save yourself tons of time if you would quickly look up the answer rather than post a question to a forum or newsgroup, and then wait patiently for some kind soul to provide the solution.

Aside from the potential to save you time, the Oracle documentation will do one more thing for you. It will help you advance your Oracle career. I promise. All of the great DBAs out there have read the Oracle documentation set. This is not an accident. If you ever receive a "RTFM" response from a question you post, the person providing this response is urging to you start using this valuable resource -- for your own good. Most of the DBAs I've met who use "RTFM" as a response to questions in forums and newsgroups mean this in the most polite way possible. So please take their suggestion and read the manuals. When you read the documentation, you do not have to commit everything to memory at that time. Rather, read for understanding of the topic being discussed, and then remember where this topic is discussed in the documentation. This way, when you come across the topic in the future, you can say, "Oh yeah, that was discussed in the Backup and Recovery Guide. Let me look it up again."

The Oracle documentation is found on the Web at Tahiti. You will be asked to register for a technet account, but it's free! Currently, Tahiti has documentation for the Oracle 8i, 9i, and 10g versions. I highly recommend that the following manuals are read in their entirety:

• Concepts
• Administrator's Guide
• Application Developer's Guide – Fundamentals
• 2 Day DBA
• Backup and Recovery Basics
• Performance Tuning Guide

After you've been reading these manuals for a while, you won't find their breadth of subjects so daunting. The more you become familiar with the documentation, the easier it will be to find the information you need. The Master Index is another good resource for finding information. You can look up a word in the Master Index (like "table") and find links to all the documentation that contains information on that word.

I can't really help you with how dry and boring the documentation can be at times, other than to take them in small doses. But if you haven't been reading the Oracle documentation, then I strongly urge you to start doing so. The more you read and understand about the Oracle database, the better your Oracle career will be. It is no accident that the great Oracle experts around the world have all read the documentation. Reading the documentation alone will not make one a great Oracle DBA, but it is a start.
Posted by Brian Peasland Backups: Live them. Learn them. Love them.
25 APR 2005 06:16 EDT (10:16, GMT)
I thought for my first blog entry, that I would talk about the most important task for any database administrator. As a SearchOracle.com Ask The Expert member specializing in database backup and recovery, backups of Oracle databases have become near and dear to my heart. But as an Oracle DBA, they should be very, very important to you as well.

Imagine that you have a failure in your database server. The disk units in this database server all fail and are no longer accessible. The only option left is to replace the disk units on your database server. You can always reinstall and reconfigure the operating system on this server. You can always reinstall and reconfigure the Oracle RDMBS software as well. Most administrators would have a backup of the OS and the RDBMS software so that they would not have to remember exactly how they configured the system.

But how do you get your data back? Your company relies heavily on this data. If your company did not need this data, then you might as well turn off the database and go home. I'm assuming that since you are employed as a DBA, that your company needs access to this data to make the business run. Without it, your company's business would be affected to some degree. In many cases, it is very difficult if not impossible to recreate the data. If your database supports a Web site allowing customers to order products from your company, and you lose their orders, how are you going to get those orders back? You can't ask everyone in the world if they have recently ordered products from your company and to resubmit their order. Your customers would most likely look towards one of your competitors and take their business there. In some situations, you have access to the data in another form. For instance, I work on a 17 TB database that serves satellite imagery to the general public. I could reload from the source data, but this process would take me years to complete! Even though I have access to the data, a backup of the database is still in order. Safeguarding your company's data in the database is your most important job as a DBA. If you lose your company's data, it is highly likely that your company will be looking for your replacement. So implement a sound and tested backup strategy.

Backups are of no use without being able to recover from them. If you have a failure and you have your backup, you might as well toss it in the trash can if you do not know how to recover from your failure with that backup. Backups and recovery go hand-in-hand. One is no good without the other. It is vitally important that the backup strategy is tested under a variety of recovery scenarios. Does your backup support the recovery of a lost control file? Does your backup support the recovery of just a single data file? Does your backup allow you to roll forward any changes since that backup was taken? A DBA should practice recovery from a number of possible failure scenarios. Practicing these recovery scenarios not only ensures your backup methodology is sound, but it also helps when a real-life recovery situation is presented.

There are many sources for learning Oracle backup and recovery techniques. A good place to start is the Oracle documentation. All Oracle DBAs should have read the Oracle Database 10g Backup and Recovery Basics documentation as well as the Oracle Database 10g Backup and Recovery Advanced User's Guide. I always recommend the Oracle Backup and Recovery Handbook by Velpuri and Adkoli on Oracle Press. One of the nice features of this book are step-by-step instructions on how to recover from a variety of scenarios. Many times I am asked to recover a database, I simply reach for this book and turn to the exact scenario I am experiencing. The recovery steps are presented for me. Oracle 9i RMAN Backup and Recovery by Freeman (also on Oracle Press) is a very good book for learning Oracle's Recovery Manager (RMAN) tool for backing up and restoring your Oracle database.
Posted by Brian Peasland