The Smallest Details Are the Ones that Make You Better

Apr 5, 2013 / By Rene Antunez

Tags: ,

One of the things I love about my career (Oracle DataBase Administrator) , is how fragile it is on a day in and day out basis. I can say with certainty that every day I turn on my laptop to start working can be my last day working as a DBA. This is not because I want it to be; it’s because unlike many jobs out there, the smallest mistake can be my last  in my current job.

Believe me, I am not under appreciating other jobs or professions out there, but the level of stress that this job has at its peak moments is comparable to being a surgeon performing open heart surgery or an air traffic controller with two airplanes that are heading for a collision.

Just imagine this: If a mistake is made, a surgeon might not have the correct data needed for the surgery, or the traffic controller may not have the electric power to co-ordinate his or her maneuvers. Putting it more lightly, we are responsible for having your bank account statements up to date, basically your money. Of course we are the ones who have helped you maintain the history of  those online games you played, so you can claim yourself the king of Warcraft or Madden.

What I want to get to is that if you are starting in this job or have been doing it for so long, you are only as good as your last 5 minutes, because a single mistake can be your last. So what you want to do is take the necessary steps to avoid any mistake in the environment you are working. This is what we at Pythian call being a FIT-ACER DBA.

The best way that I have found to achieve or be closer to this is by doing the following in the tools I work with:

PUTTY

The following configuration will help you put your words in Red with a black background, helping you distinguish a Development and a Production environment

Windows --> Colours --> Default Foreground
Red ->255, GREEN ->0 , BLUE->0

Toad

This tool is a little more discreet , but the best thing you can do is go in your connection window, search for the “Color” column, and switch all your production connections to the color red.

SQLPLUS

The best thing that you can do is in the glogin.sql, which is in $ORACLE_HOME/sqlplus/admin. Add the following line.

SET SQLPROMPT '&_connect_identifier >'

You can also do what Uwe Hesse uses.

Put this in a script called whoami.sql

set serveroutput on
begin
dbms_output.put_line('USER: '||sys_context('userenv',-
'session_user'));
dbms_output.put_line('SESSION ID: '||sys_context('userenv',-
'sid'));
dbms_output.put_line('CURRENT_SCHEMA: '||sys_context('userenv', -
'current_schema'));
dbms_output.put_line('INSTANCE NAME: '||sys_context('userenv', -
'instance_name'));
dbms_output.put_line('DATABASE ROLE: '||sys_context('userenv', -
'database_role'));
dbms_output.put_line('OS USER: '||sys_context('userenv', -
'os_user'));
dbms_output.put_line('CLIENT IP ADDRESS: '||sys_context('userenv', -
'ip_address'));
dbms_output.put_line('SERVER HOSTNAME: '||sys_context('userenv', -
'server_host'));
dbms_output.put_line('CLIENT HOSTNAME: '||sys_context('userenv', -
'host'));
end;
/

And your result will be the following:

USER: SYS
SESSION ID: 23
CURRENT_SCHEMA: SYS
INSTANCE NAME: TESTDB
DATABASE ROLE: PRIMARY
OS USER: oracle
CLIENT IP ADDRESS:
SERVER HOSTNAME: servidor1.localdomain
CLIENT HOSTNAME: servidor1.localdomain

UNIX

For those of us who handle UNIX and Linux environments, one of our best friends to help identify where we are and which is the current ORACLE_SID we have set in our environment is the PS1 variable. With the example below, you can see several things which can help you identify where you are:

PS1="
`echo ${LOGNAME}`@`hostname` [${ORACLE_SID}] ${PWD}
`echo ${LOGNAME}` $ "
export PS1
  • Example without PS1 environment set as above
[pythian@servidor1.localdomain ~]$ echo "Hi"
Hi
  • Example with PS1 environment set as above
pythian@servidor1.localdomain [TESTDB] /home/pythian/working/antunez
pythian $ echo "Hi"
Hi

SQL Developer

This Oracle tool allows you to put all your connections into folders, which will allow you to easily identify which type of connections you have.

Right Click on the connection name --> Add To Folder

All of these tips might not seem like a lot, but believe me that when you are facing one of those stressful moments , you want to make sure that you are working where you should be working. Believe me: This little grain of salt will help you maintain your job at least for one more day. :)

6 Responses to “The Smallest Details Are the Ones that Make You Better”

  • Nice, but you can still improve your PS1, IMO: http://www.ludovicocaldara.net/dba/tips-bash-prompt-and-oracle/.
    Also, taking the format user@host:path will allow you to quickly copy&past paths for scp command lines. ;-)

    • Rene Antunez says:

      Hi

      IMHO there will be always be a better way, but what ever way makes you conscious of where you are working and helps you do your job better, that is what you should use

  • [...] was reading Rene Antunez’s post about some things you can do with tools you use to help make you aware when you are in Production. [...]

    • Rene Antunez says:

      Thank you for the mention, and as you said it is not bullet proof, but that is another way to make you aware of what you are doing

  • mdinh says:

    Nice one and how about some aliases?
    Warning, this does not work on AIX for prompt.

    Get rid of those colors for ls and vi since blue on blue or red on red does not work well.

    export PS1=”[\u@\h:\${ORACLE_SID}]\${PWD}\n$ ”
    export SQLPATH=/home/oracle/working/dinh
    export ORAENV_ASK=NO
    for DB in `cat /etc/oratab|grep -v \^#|grep -v \*|cut -d”:” -f1`
    do
    alias $DB=’export ORAENV_ASK=NO;export ORACLE_SID=’$DB’;. oraenv;sysresv’
    done
    alias osid=’ps -ef|grep [p]mon’
    alias otab=’cat /etc/oratab|grep -v \#|grep -v \*|cut -d”:” -f1′
    alias sysdba=’sqlplus “/ as sysdba”‘
    alias oh=’cd $ORACLE_HOME’
    alias dbs=’cd $ORACLE_HOME/dbs’

    Think SQL Prompt breaks patching using OEM. Have not confirm 100%. But noticed the ones that failed have SQL Prompt defined.

    select UPPER(SYS_CONTEXT(‘USERENV’,’SERVER_HOST’))||’:(&_USER@&_CONNECT_IDENTIFIER)’||’> ‘ pr from dual;

  • Uwe Hesse says:

    Thank you for mentioning my whoami.sql script in your posting :-)

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>