Simple, But Effective

I thought I’d posted something about this before, but looking back it appears not.

This post details something that I only discovered in the last year, and was left with a feeling that it is something so fundamental that I should have known it for years. However, as it was something new for the team I was working with at the time and doesn’t appear to be common knowledge in my current environment, it seems that it could be something worth sharing…

Note: The example below is only going to be of value for a RAC database; even then, only if footnote 1 applies. Also, to some degree it has been made redundant by the introduction of ADRCI in 11g, but there are many other use aside from the example.

As DBAs we deal with some common environment variables all that time, for example: $ORACLE_HOME, $ORACLE_SID, $ORACLE_BASE… One variable that isn’t set by “oraenv”, but would be useful in a RAC environment is something along the lines of “$ORACLE_DB” or “$ORA_DB_NAME”, as a variable holding the database name.

Why would I want this variable? I wanted to create an alias called “alert” that would run “tail -f” on the alert log for the database that I currently had the environment set for. My problem was that either I create individual aliases for all the databases on a given server or I hack oraenv to set a new environment variable that I would then use in something along the lines of:

alias alert_10g='tail -f ${ORACLE_BASE}/admin/${ORACLE_DB}/bdump/alert_${ORACLE_SID}.log'

The problems with individual aliases are obvious, especially when new databases were popping up on servers all the time. I’d end up with aliases as alert_db1, alert_db2, etc.

The problem with hacking oraenv is that even if I thought it was a great idea, which I didn’t, then it was definitely going to be hard to sell it to the others in the team, so I didn’t even try.

I needed something else. It was pretty simple really. I just needed a way to remove the digits off the end of $ORACLE_SID1… After a bit of searching and a bit of testing I found what’s shown below:

sh-3.2$ ORACLE_SID=testdb1
sh-3.2$ echo $ORACLE_SID
testdb1
sh-3.2$ echo ${ORACLE_SID%[1-9]}
testdb
sh-3.2$ 

I’ll forgive you for not being excited immediately, but this allow me to modify my alias to:

alias alert_10g='tail -f ${ORACLE_BASE}/admin/${ORACLE_SID[1-9]}/bdump/alert_${ORACLE_SID}.log'

I’d eliminated need for a new variable. Note that we were managing RAC databases with only 2 nodes, but if you’re luck enough to play with 10+ node RAC databases then you can always modify the code as demonstrated:

sh-3.2$ ORACLE_SID=testdb10
sh-3.2$ echo $ORACLE_SID
testdb10
sh-3.2$ echo ${ORACLE_SID%[1-9][0-9]}
testdb
sh-3.2$

Clearly there are limitation and you’d be wise to test out what you do with this, but it allowed us to come up with generic commands that could be run with the desired affect simply after setting the Oracle environment via oraenv.

I hope you find this useful. If you already knew it then I’m sorry for wasting your reading time, but that would suggest that you have some other tricks up your sleeve that I don’t know about, so please share them :-)

1 This assumes that the instances in the clustered database are named in what I believe is the logical way, i.e. DB_NAME with instance number as a suffix… I recently found myself working on a database where this wasn’t the case, so I feel it’s appropriate to state that as always, “Your mileage may vary.”

About these ads

3 thoughts on “Simple, But Effective

  1. I wonder what your alias name is :)

    my selections are

    I use “talert” for tail -f
    Was using “al” for bdump but now I am using “oradiag” for diag directory

    By the way thanks for sharing the solution Martin. Hope to see you in London more

  2. Pingback: RAC/Exadata Shell Aliases « ORAganism

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s