SA-DBA

Jul 14 2009   1:40PM GMT

Environmental consistency .. is that even a real phrase?

Richard Evans Richard Evans Profile: Richard Evans

Whether it is or isn’t doesn’t really matter… what I mean by environmental consistency is basically just making your life easier by installing, configuring, testing, developing, etc, the same way. This is one of the reasons why I love Response Files in Oracle. Another thing we do pretty well around here is configuring our environment variables the same.

I have a .kshrc and a .profile that I copy to every Oracle DB Zone and the only parameter I edit is the ORACLE_SID. I’m sure there is still room for improvement here — off the topic of my head, I guess I could grep the /var/opt/oracle/oratab to find the SID and put that in for ORACLE_SID — but here is the way we set things up.

[oracle: /oracle] cat .profile
stty istrip
stty erase

# Oracle Variables...
ORACLE_BASE=/oracle
ORACLE_SID=CTM1
ORACLE_HOME=/oracle/${ORACLE_SID}/102_64
NLS_DATE_FORMAT='DD-Mon-YYYY HH24:MI:SS'
NLS_LANG=american_america.US7ASCII
SQLPATH=/group/oracle/sql
PATH=/group/oracle/sh:$ORACLE_HOME/bin:/usr/bin:/usr/sbin:/etc:/usr/ccs/bin:/usr/ucb:/etc:$ORACLE_BASE/$ORACLE_SID/admin/scripts/sh:/usr/openwin/bin:$ORACLE_HOME/OPatch:/usr/local/bin:.

export ORACLE_BASE ORACLE_SID ORACLE_HOME SQLPATH NLS_LANG NLS_DATE_FORMAT PATH

# tell us what zone we're in when we login.
ZN=`uname -n`
echo "Zone name is ${ZN}"

# Display user, SID, and current working directory as the command prompt.
export PS1='[$LOGNAME: $PWD] '
. ./.kshrc

And we do the same thing with our .kshrc. We try to references environment variables whenever possible. The less we hardcode, the less we have to change when we setup a new machine!

[oracle: /oracle] cat .kshrc
export EDITOR=vi
alias l='ls -ltr'
alias bdump='cd /oracle/$ORACLE_SID/oratrace/bdump'
alias arch='cd /oracle/$ORACLE_SID/oraarch'
alias udump='cd /oracle/$ORACLE_SID/oratrace/udump'
alias scrp='cd /group/oracle/sh'
alias sql='cd /group/oracle/sql'
alias par='cd $HOME/scripts/parms'
alias out='cd /RMAN/scripts/logs/$ORACLE_SID'
alias dbs='cd $ORACLE_HOME/dbs'
alias net='cd $ORACLE_HOME/network/admin'
alias alert='vi /oracle/$ORACLE_SID/oratrace/bdump/alert_$ORACLE_SID.log'
alias oraenv='. $ORACLE_HOME/bin/oraenv'
alias ohome='cd $ORACLE_HOME'
alias tail_alert='tail -f /oracle/$ORACLE_SID/oratrace/bdump/alert_${ORACLE_SID}.log'
alias alert500='tail -500 /oracle/$ORACLE_SID/oratrace/bdump/alert_${ORACLE_SID}.log | more'
alias rdbms='cd $ORACLE_HOME/rdbms/admin'
alias sw='cd /group/software/oracle/stage/102_64'

My favorite part out of this whole mess is the SQLPATH variable. This is a great variable that lets you execute SQL scripts from a different location by simply using “@” as if it were in your current working directory.

According to the SQL*Plus User’s Guide and Reference, SQLPATH is:

Environment variable or Windows registry entry to specify the location of SQL scripts. SQL*Plus searches for SQL scripts, including login.sql, in the current directory and then in the directories specified by SQLPATH, and in the subdirectories of SQLPATH directories. SQLPATH is a colon separated list of directories. There is no default value set in UNIX installations.

In Windows, SQLPATH is defined in a registry entry during installation. For more information about the SQLPATH registry entry, see SQLPATH Registry Entry.

What I do is put my most-commonly used scripts into the directory defined in SQLPATH and then I can call it from anywhere. Obviously this has to be a directory that is accessible across zones (servers) :-)

Keep in mind Oracle will FIRST look at your current working directory and then query the SQLPATH. So if you have a script by the same name in each directory you’ll drive yourself nuts when it isn’t working as you’d expect! I speak from experience. ;-)

So here is an example of how I check current sessions:

[oracle: /oracle] ls -al get_session.sql
get_session.sql: No such file or directory
[oracle: /oracle] sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Jul 14 08:18:15 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options

SQL> @get_session
Sessions on database AD1
PID SID SER# BOX USERNAME OS_USER PROGRAM SQL_ADDRESS SQL_HASH_VALUE
-------- -------- -------- ------------- --------------- ------------ ----------------------------------- ---------------- --------------
12140 196 5604 oraad1 SYSMAN oracle emagent@oraad1 (TNS V1-V3) 00 0
12261 182 12004 omsapp DBSNMP OMS 00 0
12726 424 9074 oraad1 DBSNMP oracle emagent@oraad1 (TNS V1-V3) 00 0
13101 431 54 oraad1 DBSNMP oracle emagent@oraad1 (TNS V1-V3) 00 0
16456 396 29816 oraad1 SYS oracle rman@oraad1 (TNS V1-V3) 00 0

I’m sorry, the output isn’t quite as pretty as I’d like but I think it conveys my point. Using a couple quick variables and commands I can maintain a consistent environment.

The get_session.sql script is something I’ve “acquired” from years of DBA’ing via Google ;-)

Here it is:

SQL> l
1 select substr(a.spid,1,9) pid
2 , substr(b.sid,1,5) sid
3 , substr(b.serial#,1,5) ser#
4 , substr(b.machine,1,9) box
5 , substr(b.username,1,10) username
6 , substr(b.osuser,1,9) os_user
7 , substr(b.program,1,30) program
8 , b.sql_address sql_address
9 , b.sql_hash_value sql_hash_value
10 from v$session b, v$process a --, v$sqlarea c
11 where b.paddr = a.addr
12 and type='USER'
13* order by spid

I do the same thing with a user.sql script:

[oracle: /oracle] ls -al user.sql
user.sql: No such file or directory
[oracle: /oracle] sqlplus / as sysdba
SQL> @user.sql
Enter value for 1:
old 3: where username like upper('%&1%')
new 3: where username like upper('%%')

USERNAME PROFILE ACCOUNT_STATUS CREATED
------------ -------------- ------------------ --------------------
DBSNMP DEFAULT OPEN 23-Oct-2005 08:47:09
PERFSTAT DEFAULT OPEN 26-Sep-2008 16:32:34
RMAN DEFAULT OPEN 23-Oct-2005 08:47:10
SYS DEFAULT OPEN 15-Dec-2003 09:43:18
SYSMAN DEFAULT OPEN 11-Feb-2009 10:37:41
SYSTEM DEFAULT OPEN 15-Dec-2003 09:43:18
ORACLE_OCM DEFAULT EXPIRED & LOCKED 01-Jul-2009 14:06:09
OUTLN DEFAULT EXPIRED & LOCKED 23-Oct-2005 08:47:09
TSMSYS DEFAULT EXPIRED & LOCKED 28-Jun-2008 00:52:53
WMSYS DEFAULT EXPIRED & LOCKED 23-Oct-2005 08:47:10

SQL> l
1 select username, profile, account_status, created
2 from dba_users
3* where username like upper('%&1%')

What I like about this script is if you do not enter a username then it’ll display all of the users. The results are as narrow or as broad as you want. I have more columns in this script, like password hash, but that’s not information I care to give out. I think you understand ;-)

I hope this gives you an idea of what you can do with such a simple variable. The more simple and cleaner you can make your environment the better off you’ll be.

What do you do to simplify your environment? Are there variables and aliases that you setup that I’ve missed? I’d like to hear about them!

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: