Search

OakieTags

Who's online

There are currently 0 users and 42 guests online.

Recent comments

Affiliations

March 2011

Annie Hall

From time to time I am reminded by postings on the OTN database forum of a sequence  from the move Annie Hall which shows Diane Keaton and Woody Allen being interviewed (separately) by their therapists:


    DK’s Therapist: “Do you have sex often? “
    Diane Keaton:  ”Constantly, I’d say three times a week”

    WA’s Therapist: “How often do you sleep together?”
    Woody Allen:   “Hardly ever, maybe three times a week”

This dislocation appears in a similar fashion with Oracle – though the dialogue goes something like this:


    User: “This query takes forever to run.”
    DBA: “Can you give me a rough idea of how long ?”
    User: “Nearly 10 seconds”.

Advice to DBAs – remember to ask for facts, not opinions.

BYO Oracle RAC on EC2

Over the past year or so I’ve had a number of conversations about running Oracle RAC on Amazon’s EC2 cloud platform. Chet Justice had suggested a long time ago that I try it, but I never quite found the time. Last fall at the Oak Table Symposium in Michigan, Jeremiah Wilton told me he hadn’t yet done it and I spent the last night scheming with Charles Schultz about getting started. But it wasn’t until this week that I finally found the time to try. (To be fair, I’ve been quite busy lately — our first kid was born on February 2nd, a beautiful little girl!)

More Process Priority Levels for the Process Scheduler

This note started out as an idea for how to add more priority levels to the PeopleSoft Process Scheduler to improve control over prioritisation of processes in a complex batch. While testing I found some interesting behaviour that I had not expected. I will also explain how I did some of my tests, because they are easily reproducible.
More Priority Levels
By default, PeopleSoft process can be given priority 1 (low), 5 (medium) and 9 (high). If the Process Scheduler is too busy to start all the processes scheduled to be started, it starts the higher priority ones in preference to the lower priority.

That’s fine, but sometimes in a very complex batch environment 3 process levels are not enough, and it would be useful to have more priority levels so that it is possible to define the hierarchy of processing to a finer level.

More Process Priority Levels for the Process Scheduler

This note started out as an idea for how to add more priority levels to the PeopleSoft Process Scheduler to improve control over prioritisation of processes in a complex batch. While testing I found some interesting behaviour that I had not expected. I will also explain how I did some of my tests, because they are easily reproducible.
More Priority Levels
By default, PeopleSoft process can be given priority 1 (low), 5 (medium) and 9 (high). If the Process Scheduler is too busy to start all the processes scheduled to be started, it starts the higher priority ones in preference to the lower priority.

That’s fine, but sometimes in a very complex batch environment 3 process levels are not enough, and it would be useful to have more priority levels so that it is possible to define the hierarchy of processing to a finer level.

Index Rebuilds

A couple of days ago I found several referrals coming in from a question about indexing on the Russian Oracle Forum. Reading the thread I found a pointer to a comment I’d written for the Oracle-L list server a couple of years ago about Advanced Queueing and why you might find that it was necessary to rebuild the IOTs (index organized tables) that support AQ.

The queue tables are, of course, a perfect example of what I call the “FIFO” index so it’s not a surprise that they might need special consideration. Rather than rewrite the whole note I’ll just link to it from here. (One of the notes in the rest of the Oracle-L thread also points to MOS document 271855.1 which describes the whys and hows of rebuilding AQ tables.)

SQL Injection Attack

Marcel-Jan emailed me an article on arstechnica a few days ago and has now written a forum post titled " How Anonymous hacked HBGary ". This is intersting reading and shows that simple techniques can be used to abuse systems....[Read More]

Posted by Pete On 03/03/11 At 02:14 PM

Academ’s Fury…

Academ’s Fury is the second book in the Codex Alera series by Jim Butcher. The book starts two years on from where the Furies of Calderon ended.

I’m now totally invested in the characters. They are real to me. I’m already thinking I will miss them (as I do Harry Dresden) when I finish the series and there is another 4 books to go. If you like the first book you will love this. If not, then give it a miss.

Cheers

Tim…

Cluster callouts to create blackouts in EM

Finally I got around to providing a useful example for a cluster callout script. It is actually on the verge of taking too long-remember that scripts in the $GRID_HOME/racg/usrco/ directory should execute quickly. Before deploying this, you should definitely ensure that the script executes quickly enough-the “time” utility can help you with this. Nevertheless this has been necessary to work around a limitation of Grid Control: RAC One Node databases are not supported in GC 11.1 (I complained about that earlier).

The Problem

To work around the problem I wrote a script which can alleviate one of the arising problems: when using srvctl relocate database, another instance (usually called dbName_2) will be started to allow existing sessions to survive the failover operation if they use TAF or FAN/FCF.

This poses a big problem to Grid Control though-the second instance didn’t exist when you registered the database as a target, hence GC doesn’t know about it. Subsequently you may get paged that the database is down when in reality it is not. Receiving one of the “false positive” alarms is annoying at best at 02:00 AM in the morning. Actually, Grid Control is right in assuming that the database is down: although detected as a cluster database target, it only consists of 1 instance. If that’s down, it has to be assumed that the whole cluster database is down. In a perfect world we wouldn’t have this problem-GC was aware that the RON database moved to another node in the cluster and update its configuration accordingly. This is planned for the next major release sometime later in 2011. Apparently dbconsole has the ability to deal with such a situation.Now with the background explained, management had to weigh the possibilities-either not register the RAC One database in Grid Control and have no monitoring at all or to bite the bullet and have monitoring only when the initial instance is started on the primary node. The decision was made to have (limited) monitoring. To prevent the DBA from being woken up I developed the simple script below to automatically create a blackout in GC if the “_2″ instance starts. Subsequently, the blackout is taken off when the “_1″ instance starts.

Room for improvement: if the script assumes that a RON database can only have a maximum of 2 member servers-if your database can run on more than 2 nodes then you should use the relocate_target if the _1 instance comes up on a different node from what GC expects.

The Script

My algorithm checks for cluster events, and if an instance dbName_2 starts, I create a blackout on the initial instance to prevent being paged until Oracle have come up with a better solution (we are flying blind once the 2nd database instance has started).

The script assumes that you have deployed emcli on each cluster node (or ACFS). EMCLI is the Enterprise Manager Command Line Lnterface, it’s located on your OMS together with the installation instructions. This is the default location:: https://oms.example.com:7799/em/console/emcli/download – 7799 is the default port for Grid Control.

Let’s have a look at the script:

#!/bin/bash

# enable debugging if needed
set -x
exec >> /tmp/autoBlackout.log 2>&1

EVENTTYPE=$1

# only SERVICEMEMBER populates instance, database and service as needed
# for the blackout section below.
if [ "$EVENTTYPE" != "SERVICEMEMBER" ]; then
 exit 0
fi

# adjust to your needs or set to the empty string if you are not using db_domain
# assumes that both database and service have the same domain
DOMAIN=example.com

# bail out if there are too many instances of this script running. Inform the
# admin via email
ME=`basename $0`
RUNNING=`ps -ef | grep -v grep | grep $ME | wc -l`
if [ $RUNNING -ge 6 ]; then
 echo Too many instances of this script running, aborting
 echo Too many instances of $ME running, aborting |
 mail -s "$RUNNING instances of $ME detected on `hostname`" admin@example.com
fi

# set up for emcli (emcli requires jdk 1.6)
JAVA_HOME=/shared/acfs/emcli/jdk1.6.0_24
PATH=$JAVA_HOME/bin:$PATH
EMCLI=/shared/acfs/emcli/emcli
export JAVA_HOME PATH

# turn off debugging for a moment - the below parsing of the command line
# parameters is very verbose.
set +x

# read the parameters passed to us-modified version of a script
# found at rachelp.nl
for ARGS in $*;
 do
 PROPERTY=`echo $ARGS | /bin/awk -F"=|[ ]" '{print $1}'`
 VALUE=`echo $ARGS | /bin/awk -F"=|[ ]" '{print $2}'`
 case $PROPERTY in
 VERSION|version)    VERSION=$VALUE ;;
 SERVICE|service)    SERVICE=$VALUE ;;
 DATABASE|database)    DATABASE=$VALUE ;;
 INSTANCE|instance)    INSTANCE=$VALUE ;;
 HOST|host)        HOST=$VALUE ;;
 STATUS|status)        STATUS=$VALUE ;;
 REASON|reason)        REASON=$VALUE ;;
 CARD|card)        CARDINALITY=$VALUE ;;
 TIMESTAMP|timestamp)    LOGDATE=$VALUE ;;
 ??:??:??)        LOGTIME=$VALUE ;;
 esac
 done

# and turn debugging on again
set -x

# targets are reported in lower case :( Someone please suggest a better
# way to get a lower case string to upper case
DATABASE=`echo $DATABASE | tr "[a-z]" "[A-Z]"`

# targets affected are rac_database and the oracle_database (instance)
# not using emcli here as it has to be quick. A rac_database target is a
# composite target, consisting of multiple oracle_database targets. In
# RAC One Node there is only one 1 instance - see output from GC below:
# $ emcli get_targets | grep "RAC"
# 0       Down           oracle_database  RAC.example.com_RAC_1
# 0       Down           rac_database     RAC.example.com

# define what we want to black out (only ever the primary instance!)
BLACKOUT_NAME=blackout_${DATABASE}
BLACKOUT_TARGETS="$DATABASE.${DOMAIN}:rac_database;${DATABASE}.${DOMAIN}_${DATABASE}_1:oracle_database"

# create a blackout if the secondary instance is up (we only ever register the _1 instance)
# the blackout duration is indefinite-it will be stopped and lifted automatically. You may
# want to limit this to a few hours to raise visibility.
if [[ $STATUS == "up" && ${INSTANCE: -2} == "_2" ]]; then
 echo create blackout
 $EMCLI login -username=user -password=supersecretpassword
 $EMCLI create_blackout -name=${BLACKOUT_NAME} -add_targets=${BLACKOUT_TARGETS} \
 -reason="auto blackout" -schedule="frequency:once;duration:-1"
fi

# disable the blackout if instance *_1 starts
# this is where the script could be improved if the RON database can run on more
# than 2 nodes. You could use emcli relocate_target to relocate the target to another
# node
if [[ $STATUS == "up" && ${INSTANCE: -2} == "_1" ]]; then
 echo remove blackout
 $EMCLI login -username=user-password=supersecretpassword
 $EMCLI stop_blackout -name=${BLACKOUT_NAME}
 $EMCLI delete_blackout -name=${BLACKOUT_NAME}
fi

I tried to add a lot of comments to the script, which should make it easy for you to adjust it. I recommend you store it in ACFS and mount that directory on all cluster nodes. Create a symbolic link from the ACFS to $GRID_HOME/racg/usrco/ to make maintenance easier. You could enable log rotation for the logfile in /tmp if you liked, otherwise keep an eye on it so it doesn’t grow to gigabytes.

Trivadis Performance Days 2011

Trivadis Performance Days 2011
Event date: 
Tue, 2011-05-24 - Wed, 2011-05-25

 Trivadis is very pleased to organize in Zurich another high class seminar with top guest speakers in 2011. This year’s focus will be on Oracle system performance. Since performance is not simply a product one can buy but rather the results of an accurate planning and a correct implementation, the seminar will present valuable techniques not only for identifying and solving performance problems, but also to avoid them in first place.
 
 
Detailed schedule
The working day will be from 9am to 5pm; and in the evening of 24th there will be an opportunity to share your experiences and opinions with the presenters and other participants in a less format atmosphere as we meet over drinks.
Agenda of May 24:
09:00 – 09:10 Welcome
09:10 – 10:30 Thinking Clearly About Performance – Part 1, Cary Millsap
10:30 – 11:00 Break
11:00 – 12:20 Thinking Clearly About Performance – Part 2, Cary Millsap
12:20 – 13:50 Lunch
13:50 – 15:10 How to Design Efficient SQL, Jonathan Lewis
15:10 – 15:40 Break
15:40 – 17:00 Brewing Benchmarks, James Morle
17:00 – 19:00 Let’s meet over drinks
 
Agenda of May 25:
09:00 – 10:20 Sane SAN, James Morle
10:20 – 10:50 Break
10:50 – 12:10 Exadata Performance Features Explained, Christian Antognini
12:10 – 13:40 Lunch
13:40 – 15:00 Challenges and Chances of the 11g Query Optimizer, Christian Antognini
15:00 – 15:30 Break
 
 
 

Oracle Database Time Model Viewer in Excel 3

March 3, 2011 (Updated March 5, 2011) (Back to the Previous Post in the Series) (Forward to the Next Post in the Series) In the previous articles of this series we started building a solution in Microsoft Excel that duplicates some of the functionality in one of my programs – the Oracle Database Time Model viewer.  So far, [...]