Oracle joke

Sometimes it's really funny to read logfiles (and yes, some sarcasm helps a lot!)

my todays joke is this one:

ALTER DATABASE ENABLE BLOCK CHANGE TRACKING
Fri Oct 29 11:28:04 2010
Block change tracking file is current.
Fri Oct 29 11:28:30 2010
Starting background process CTWR
Fri Oct 29 11:28:30 2010
CTWR started with pid=58, OS id=9457
Fri Oct 29 11:28:55 2010
ORA-1013 signalled during: ALTER DATABASE ENABLE BLOCK CHANGE TRACKING...
Stopping background process CTWR
Fri Oct 29 11:29:26 2010
Background process CTWR not dead after 30 seconds
Killing background process CTWR
Fri Oct 29 11:29:26 2010
PMON (ospid: 4322): terminating the instance due to error 487
Fri Oct 29 11:29:26 2010
ORA-1092 : opitsk aborting process
Fri Oct 29 11:29:26 2010

What was going here?

• Someone tried to enable Block Change Tracking.


• The process CTWR started.


• (for some reason) the DBA hit ^C before the operation finished.


• The instance tried to stop the CTWR.


• CTWR did not stop within 30 seconds - therefore the instance tried to kill it.


• PMON found CTWR dead (ORA-487 "CTWR process terminated with error"), considered this a critical issue and terminated the instance. (ORA-1092 "ORACLE instance terminated. Disconnection forced")

For me this is one if these bugs which are not worth investigating.

back to the future

Last month I walked through some boxes full of my old books at my parents house. There I also found my first school book about information technology. It was printed in 1990. So for me it covers almost the beginning of my life with IT.

To my surprise, the first page is not about bits, bytes or ICs. It's about communication:

With my todays background I scanned through the book and searched if there is anything about SQL or at least databases:
3 types of databases where mentioned there:

• hierarchical databases
• network databases
• relational databases

Especially relational databases where described as slow, but flexible ;-)

SQL was not mentioned within this book, but available database systems where listed:

dBASE III PLUS, Everyman, MS-R:Base, Informix, Dataflex (no Oracle or DB2, sorry).

As the first pichture in the book is still valueable, so is also the last:

Sometimes it's worth to look into his own history, and books I've read seem to be a valuable method. Many things have changed, some expectations did not come true. But it's worth to know about the own foundation.

debugging MOS

If you ever wanted to know a little bit more about the Flash Client of My Oracle Support, you can enable it's debug mode:

Hold down the CTRL key and click to Oracle logo.

I have limited use of these informations, but maybe someone with some more knowledge of Flash can use them?

are my resources in favored place?

On a 11gR1 Oracle Cluster I had an issue with some resources not in their favored place.
While investigating, I had to get the affected resources fast (at least faster than reading a huge list of resources manually and think if they match the rules).

This brought me to a small script:

crs_stat -f | awk ' BEGIN { FS="=" 
                            c=0}
/^NAME=/{n=$2}
/TYPE=/{t=$2}
/HOSTING_MEMBERS=/{hm=$2}
/PLACEMENT=/{p=$2}
/TARGET=/{g=$2}
/STATE=/{s=$2; 
  ch=s
  gsub("ONLINE on ", "",ch)
  if( (p=="favored") && !( index(hm,ch)>0 ) ) {
    printf("%-30s%-20s%-17s%-15s%-30s\n", n, " should be located on ", hm, " but has STATE: ", s) ;
    c+=1 ;
    }
} 
END { exit c }'

It prints out a line for every resource which has a favored placement, but currently is not on any HOSTING_MEMBERS.

I'm sure it's not the best awk-code, but it's fine for me.

To use it in other scripts (e.g. monitoring) I can use | wc -l to get the number of suboptimal located resources or just get the return value $?

developers joke

Yesterday evening I had to deal with the product of a really funny Oracle developer:
I had requested a CRS patch for Bug:8328904 under really high preassure (Management involved). With a target date of 3rd week of March 2010, I got the patch on 19-Mar-2010 about 2pm local time. Just in time!
So I had the fun to patch a test system on Sunday evening. As allways, it's not that easy as it sounds:
after a slow, but successful preparation of one node and a really easy patch, at restart of the CRS I got

/etc/init.d/init.crs: [[: not found

Ok. I knew this Patch was knitted with really hot needles, so after some checking we applied this diff:

 72c72
< if [[ "$CMD" = "init.crs" && "$PARENT_CMD" != "startpar" ]]; then
---
> if [ "$CMD" = "init.crs" ] && [ "$PARENT_CMD" != "startpar" ]; then

All the patching ended at Monday, 2am.

Of course, I updated my SR on Monday morning with this slightly detail.
The answer was overwhelming:

/etc/init.d/init.crs: [[: not found encountered while startup is a known issue and bug is already filed.( bug# 9131555).

The problem is with the 11.1.0.7. bundle 2 . Since the patch was created on top of 11.1.0.7 bundle 2 hence the same error got passed on. The workaround is correct and is same as mentioned in the above mentioned bug. So no problem it is a supported workaround.

What a funny developer!
delivering a patch for a escalated but just in time, delivering another known within it.

Who can tell me why i was not laughing?

Job, hobby or passion?

In my last post I summarized the answers I got on a mailing list for the short question:

what ist the fastest SQL you can create?

maybe the most remarkable answer was

I think y'all need a hobby- and one that doesn't include oracle!

Well, maybe I have to answer. Which is not that easy.
Let's try to confine a little bit: Let's start with Job. I see a job as a work which I get paid for. So i need a job to make a living.
At first view a hobby is a totally contradiction to a Job: I do it at my own will and possible even spend money to do it. A hobby is something I do to have fun.
But sometimes it's not that easy.
Even if job and hobby are a contradiction in money and choice, they are not imperatively a contradiction in fun.
Assume, you have a job which at the same time is fun. Why not enjoy this: instead of paying for fun, you get paid for! Maybe it's ok if I call it an avocation?
Of course, it's not that easy all the time, but nothing is that easy, not even a hobby ;-)
I see one more bonus in this situation: I assume I'm quite good in the tasks I get paid for. And at least at the moment I get a fair salary. I hope, both will at least stay at this level for a long time.

This all leads me to an answer:

I have a job in IT, especially Oracle. Why do I need a hobby?

fastest SQL?

Recently I asked what ist the fastest SQL you can create? on oracle-l mailing list.
It was a kind of challenge. But not a real clever one.
I was more interrested in the way people think and react to this question than in a particular SQL or result.

Here are some summaries for those who are interrested:
There where 33 replies (until now), these can be grouped by some categories (some mails belong to more than one):

select 1 from dual; was suggested by Niall Litchfield and Kevin Lidh.
Don Granaman showed an imprived version with a view on sys.v_dual, but Niall and Norman Dunbar argued, that's what fast_dual was introduced for.

Niall and Wolfgang Breitling asked for better specifications for the quest, which I left open by intention.

Another approach started with exit; by Daniel Fink. Robert Freeman called this not a SQL, but Grant Allen (--) and Jaromir D.B. Nemec ("empty statement") tried the same direction.
Job Miller suggested the qhole quest as a kind of trick question With the answer You look at the statement/business logic and decide that you don't need to run that statement at all. and Daniel gave a real world example for this effect.


Toon Koppelaars and Andre van Winssen both suggested query result cache. I tried to test this but could not find a suficient measurement. Brandon Allen raised the method by checking elapsed_time down to microseconds from v$sql/v$sqlstats.

Wolfgang did one step further with his client side cache.

A discussion about other products like purple and Mauve by Jack C. Applewhite, Christopher Boyle, John Piwowar and Jason Heinrich.

I think y'all need a hobby- and one that doesn't include oracle! by Kellyn Pedersen and Kathy Duret seems to be worth a seperate blog!