31 January 2007


This is an example of how not to get help in #oracle. spinal4ever just doesn't get it, and I can only excuse so much on the broken English. Many F-bombs were dropped. I'm ashamed I offered what tidbits that I did.

18 January 2007

The Four Options of the SHUTDOWN

One thing that's always good to know is that there are actually 4 different options to the Oracle SHUTDOWN command. They are:
Now allow me to cover the differences.
This is the default option for the SHUTDOWN command. Once a SHUTDOWN NORMAL is issued, no new user connections will be allowed. However, all existing connections are allowed to continue for as long as they like, and the SHUTDOWN will simply wait for them. So not until the last user (voluntarily) logs off will the database actually shut down. It's pretty much a useless option.

As with NORMAL, a SHUTDOWN TRANSACTIONAL command will tell the database to not allow any new user connections. Also, any existing sections that are not currently in a transaction are terminated. Sessions that are in a transaction (eg: INSERT, UPDATE, DELETE or PL/SQL block) are kindly allowed to finish that transaction and will be terminated immediately after. As before, when all user sessions are gone, the database will shut down.

Like before, no new connections allowed. However now ALL sessions are terminated. Any active transactions are rolled back. After all the sessions terminated and rollbacks complete (which could take a bit of time), then the database shuts down. This is usually the way you'll want to shutdown your database.

This one differs from the other 3 in that it results in an inconsistent shutdown. SHUTDOWN ABORT is basically the equivalent of a power cut, or someone killing the SMON process. The Oracle instance terminates immediately. No effort is made to write out dirty blocks to disk or redo logs. Upon the following startup, SMON will need to perform instance recovery to bring the database (controlfiles and datafiles) to a consistent state. This type of shutdown should only be performed in the direst of situations.
As I mentioned, a SHUTDOWN ABORT leaves the database in an inconsistent state. Usually, with any of the other three options, Oracle will perform a consistent, or "clean" shutdown. This entails:
  1. Incomplete transactions get rolled back
  2. A checkpoint is issued that tells the database writer (DBWn) to write updated blocks from the db buffer cache to the datafiles on disk
  3. LGWR (the logwriter) flushes any change vectors from the redo log buffer to the online redo logs.
  4. Then the file headers are updated (so that the SCN in the controlfiles and datafiles match), and the filehandles are closed.
So let's just keep things nice and easy, and use SHUTDOWN IMMEDIATE, alright?

16 January 2007

The Worth of Certifications

NOTE: Howard has since taken his site offline while he finds a suitable webhost, so the first link probably won't work

Howard Rogers recently touched on the knowledgeset (or lack thereof) of some interviewees with OCPs. We've all heard this before, and the common theme is that just because someone managed to pass the OCA and OCP exams, they still might not know nearly enough to be put in charge of your precious (and expensive) Oracle RDBMS. I've heard tale of people asking questions on oracle-l or the OTN forums like "What does ROWNUM mean?", while proclaiming their OCP title in their signature. So, yes, it is probably best not to just hire an OCP without verifying that they have the knowledge that you seek. "Trust, but verify" is an oft-used quote in the computing world, and it applies to hiring as well.

Now, as you may know, I am seeking the OCA/OCP certifications, after 5 years of learning on-the-job. I just completed the Oracle 10gR2 DBA Workshop I last week, which is a suggested course for the OCA exam, and I felt pretty comfortable with the majority of the material. It was nice to play with EM DB Control, which I don't (yet) have set up at my shop. Now I plan to go through the exam guide and review the course-provided books, then take the practice exam(s) provided with the exam guide. Then hopefully I'll be prepared to take that exam.

Why do I want the certification if so many gurus think it is practically worthless nowadays? Why not just be content in having the knowledge, without spending (or wasting, as they'd see it) time preparing for the exams? Probably for the same reason I finished school to get my bachelor's degree (which some people also say is watered down now): a sense of completion, mostly for myself. I don't expect the OCA or OCP to get me anything that I couldn't already achieve. However there may be a case down the road when having those certifications gets me a second look or a foot in the door with some non-technical HR staffer. One never knows how these things will fall out.

09 January 2007

sqlplus commit-on-exit?

ACHTUNG: Did you know that SQL*Plus issues a COMMIT when you exit? This has nothing to do with the AUTOCOMMIT setting, which will automatically commit after every statement in sqlplus.

If you issue a graceful exit (via the "exit" or "quit" command), sqlplus will always issue a commit. However, if you were to be ungracefully disconnected, for example by closing your terminal window, then PMON will issue a rollback like it does with any other disconnected session.

Here's a small oracle-l thread about it as well. Google also turns up an Eddie Awad post on it. Of course it turns out that this is a documented "feature":

"Regardless of the AUTOCOMMIT setting, changes are committed when you exit SQL*Plus successfully."

Temporary Tables and REDO Logs

Today in DBA class, the instructor matter-of-factly rattled off some characteristics on global temporary tables. Among these I just barely noticed him state that they generate REDO logging. I sat upright and asked him to confirm that I had heard him correctly, and indeed that is what he said. He acknowledged that it runs contrary to what you'd think about an object that holds session-specific data that cannot be recovered anyway.

Well I just did some googling and found out exactly what the case is, from Oracle 10gR2 Database Concepts:

"DML statements on temporary tables do not generate redo logs for the data changes. However, undo logs for the data and redo logs for the undo logs are generated."

So it is sort of an indirect redo log generation. Still, something to be aware of. The instructor recalled how he once had a user filling up the archived log destination disk by running a lot of activity just on a global temporary table.

08 January 2007

Track yer block changes

If you're using RMAN to take your backups, and are running Oracle 10g, do yourself a favor and enable block change tracking now. Prior to doing so, my level 1 incremental backups were scanning my entire database (~830 GB) and took over 5 hours. With BCT, level 1 incremental backups only look at around 45 GB (according to v$rman_backup_job_details) and often take less than 30 minutes.

The one caveat is to watch for the overhead incurred, but we haven't seen any noticeable performance hit.

Of course my level 0 still takes over 9 hours, but I'm hoping to (re)implement incremental merge and only take one level 0 to start and never look back.


I'm just tired. I got up at 4 AM this morning and drove from Manitowoc to greater Madison, to be here in plenty of time for an 8 AM training session that it turns out started at 9 AM. Anyway now it's 10:40 PM and I'm pretty much over tired and can't sleep. Perhaps it's the bright street light shining in my hotel room since the "curtain" is a translucent cheesecloth.

05 January 2007

selecting count(1) vs count(*)

Count me among the many who naively assumed that using count(1) instead of count(*) was somehow faster or less resource-intensive when counting the number records in a select query. Herod over at Yet Another Oracle DBA provides some more evidence on top of Tom Kyte's previous smackdown.

One of these things is not like the others


The interloper is my old college buddy Ryan, who graciously scanned his yearbook page for me.

04 January 2007

Happy New Year

I've got a backlog of Oracle stuffs to blog. In the words of my Data Structures professor at UW, "moer later."