Can’t connect remotely to ASM as SYSASM remotely

If you can’t because of “insufficient privileges” you probably just need to enable remote access to SYSASM, see here…

Dealing with Oracle timestamp with timezone conversion

The Oracle scheduler stores dates as “timestamp with time zone”. This can cause some people to get confused when querying the data based on time. Here’s how you can help them.

Oracle RAC install reboot after Clusterware install

As a matter of course I’ve always stopped clusterware and rebooted each node after doing the clusterware install. I do this just to know that everything restarts properly before I move on with the rest of the installs. I was in a rush on a R&D system and I skipped that step. I ran into countless unusual issues that I’d never seen before from nodes not showing up in the node list for rdbms install, db installer reporting listeners weren’t there, and other odd things. Finally at dbca db create I hit and error about the listeners that I just couldn’t get past. As a last resort I stopped clusterware and rebooted. Then dbca worked fine. I will never skip that step again, in fact, I’m wondering if I’d just forgotten that is a required step (might have to check the docs).

Creating an Oracle job that runs an OS executable

There are several sites that explain quickly how to run an external OS job from the Oracle Job Scheduler, but I found several things that none of them covered that I needed for it to work in the real world. Click here to see what I did and some extras.

Report on OEM Alert metrics

My director asked for metrics on alerts that our team gets from Oracle Enterprise Manager Grid Control. Oracle definitely has an interesting, if not obfuscated, schema design in the Enterprise Manager repository. I was able to find enough information to gather a fair bit of useful information and what I needed for the request. Read more…

How to reference a shell variable named by another shell variable

Have you ever had the need to get the value of a shell variable, but the name was all, or partly, determined by another shell variable. That happened to me this week and once you know how to do it it is pretty simple, but it is hard to figure it out.

I’ll try to clarify. I have a shell variable that has been set with a password. To find that variable I have another variable. The second variable is the User Name and is called USER. The first is the password and is called USERPW. (Note: I’m not saying this is the best way to handle passwords, it is what I had to work with.)

In a given situation I’ll need the password for a user that is provided by the variable USER. I thought I could get this in a variety of ways:

$${USER}pw}
${${USER}PW}

etc…

To end the story and give you what you came looking for. Here is the solution (in a test script):

->cat deref.ksh
#!/bin/bash
export USER=MYUSER
export USERPW=
. /somewhere/pwds.sh # this script sets environment variables with passwords
echo echo deference
eval USERPW=\$${USER}PW
echo ${USER}
echo ${USERPW}
sqlplus -s ${USER}/${USERPW} << eof
show user
eof

Here is the output that shows it worked. The last line is the output from the Oracle database.

->./deref.ksh
echo deference
MYUSER 
mysupersecret
USER is "NDTC_DBA"

Oracle Shrink Space, don’t do it just once

I’ve always noticed shrinking a table more than once can benefit you, this was the first I’ve seen it makes things worse, and then better, read on here…