Oracle: Environmental Variables and Scripting

9 min read

(For more resources on Oracle, see here.)

Unix scripting will be one of the most powerful tools in your arsenal and only with constant use will you become proficient. Your standards for scripting need to produce code that is robust, modular, consistent, and scalable. This article won’t cover everything you need to know about scripting. There are many excellent lessons available from your operating system vendor. See the following documents for more information specific to how Oracle products behave in a Unix environment:

  • Note: 131207.1 Subject: How to Set Unix Environment Variables
  • Note: 1011994.6 Subject: How do Applications Act in Different Unix Shells.
  • How to Integrate the Shell, SQL*Plus Scripts and PL/SQL in any Permutation? [ID 400195.1] (Excellent document)

It is suggested that the reader should read the Oracle Database Concepts 11g Release 2 (11.2) of the documentation, the book every DBA should start with.

Usually cron is used for scheduling in Unix and the AT command with Windows.

For Oracle utilities that run outside the database (or which must only run when the database is down), Unix shell scripting is best used. A well-written script would parse /etc/oratab for a list of ORACLE_SIDs on a single server for tasks such as parsing log files, consistent backups, exports, dbverify, and RMAN. If you have a solitary database, then DBMS_SCHEDULER can be used with a combination of SQL and PL/SQL integrated with OS commands.

Occasionally, DBAs rename the oraenv located in $ORACLE_HOME/bin when they have added custom code to the /usr/local/bin/oraenv (default location on Linux), so that they can make sure they know exactly which one is executed at run time. If you have any problems related to running a modified oraenv file, check which one is being executed and adjust the $PATH variable as needed.

The following is a list of some of the Oracle-provided Unix commands with a quick synopsis of their purpose. Most of them are located in $ORACLE_HOME/bin:

  • wrap—encrypts stored procedures for advanced use
  • oerr—displays oracle errors. Syntax: oerr ora 12154
  • Sysresv—instance and shared memory segments
  • Tkprof—formats output trace file into readable format
  • Dbshut—shell script to shut down all instances
  • dbstart—shell script to start up all instances at boot
  • Dbhome—sets ORACLE_HOME
  • Oraenv—sets environmental variables for ORACLE_SID
  • trcasst—trace assistant

Guidelines for scripting

These are general suggestions for someone with some experience with a Unix operating system. You will need more background information to understand this section. The best sources of information will be the software vendor of your operating system, because there are small differences between the different versions and flavors of Unix. As with any code, you must test on a non-production system first, as inexperience may lead to unexpected results.

Separating the configuration file

Use the OS-provided default profile for environmental variables, but use a separate configuration file to set the $ORACLE_SID variable. There are several key environmental variables that are commonly used with Oracle software, all of which are found in the documentation specific to the operating system. Optimal Flexible Architecture (OFA) mentions setting the $ORACLE_SID in the profile, but if you have more than one database or Oracle product installed on a system, it is best done interactively. A default profile is loaded when you first log in to Unix. So if the $ORACLE_SID is loaded when you log on, what happens when you want to change ORACLE_SID(s)? This is when the environment becomes mixed. It just keeps appending the $PATH variable each time you execute the oraenv script. Which set of executables will you find first? It will be those executables for which you ran oraenv the first time.

At this point I wanted to execute SQL*Plus out of the 11g directory, but was able to determine that the 10gR2 client was first in the $PATH. How did I know that? Use the which Unix command to find out.

Oracle: Environmental Variables and Scripting

It is also a good practice to use a separate terminal window for each ORACLE_HOME. Normally, you will operate with multiple windows open, one for each ORACLE_HOME or ORACLE_SID in different colors to visually remind you which one is production.

The example profile is provided in the code: example_profile.txt. The profile sets the entire user environment at first logon. This one is specific to the ksh or korn shell on the Linux operating system and will also work for bash. Differences in bash include that the line history is scrollable with the up and down arrows instead of vi commands.

To set the ORACLE_SID and activate all of the variables located in profile, source the file oraenv (bash, Bourne, or korn shell) or coraenv (C shell). Source means the variables will be in effect for the entire session and not just the current line in the command window. You indicate source by putting a ‘.’ (dot) in front of the file. As the oraenv file is located in /usr/local/bin (on Linux) and this location is in the $PATH, typing it at the command line works. Putting key Oracle files, such as oraenv, oratab, and oraInst.loc, in locations that ORACLE_HOMEwill not be affected by standard Oracle installations is also part of the OFA. The oraenv script is installed into /usr/local/ bin/ automatically when running .runInstaller for the first time. Notice the prompt that you will see if you use the command as in the profile listed above:

Oracle: Environmental Variables and Scripting

A note about prompts: Every person will want to customize their own prompt so; look around for various versions that tickle your fancy. This one is better than most examples to compare to. Notice the small difference in the prompt before and after? ORACLE_SID is now defined because oraenv is executed, which also runs dbhome (also located in /usr/local/bin), but these scripts require a valid entry in /etc/oratab. If you type the ORACLE_SID incorrectly on Unix, this will be case sensitive. It will ask where the ORACLE_HOME is if there is no entry in oratab. Making an ORACLE_SID lowercase conforms to the OFA standard (see the install guide for your OS for more information). Some DBAs use an uppercase ORACLE_SID because it makes it more prominent for any type of naming convention and is meant to reduce human error.

You can use an ORACLE_SID entry in the oratab file to reference other Oracle products such as the Grid Control Intelligent Agent. The ea, which is an ORACLE_SID in the following oratab example, is what I use to indicate the Enterprise Manager Agent. The third letter after the ORACLE_HOME (N) indicates when Oracle-supplied utilities (like db_shut and db_start) are not to execute against this ORACLE_HOME. I personally use the N for my own scripting to indicate which utilities shouldn’t run against this ORACLE_HOME. What this does is take advantage of Oracle-provided files—oratab and oraenv—to accomplish other types of tasks. This is only a suggested use. There are other ways of setting environmental variables for non-database products.

Oracle: Environmental Variables and Scripting

You will need to create a test database to work through all of the examples and practice scenarios. How should you create the test database? Use the Oracle-provided Database Configuration Assistant (DBCA) utility to create a test database. There are default templates provided that will work for most of the tasks outlined in this article. If you are interested in duplicating some of the advanced tasks (like Data Guard), then it will require the installation of the Enterprise Edition of Oracle Database. All tasks in this article were done with version of Oracle Database with some references to, which had just been released.

Host commands relative location

This will be important as you begin scripting. Host commands are relative to the location of the executable. As a general rule, you should execute database-specific utilities (imp, exp, datapump, RMAN, and so forth) on the server where the database is located in the correct ORACLE_HOME. This reduces the amount of issues such as core dumps and version compatibilities. This is different from what is usually thought of as a client utilities such as SQL*Plus.

There are exceptions to this rule, for it is recommended to run a compiled code (C, C++, Cobol) on a separate server rather than a database. See the following document for setting the TWO_TASK variable when using a separate node for compiled programs. TWO_TASK is an environmental variable. Subject: How to Customize Pro*C and Pro*Cobol Makefiles And On Linux/Unix [Doc ID: 602936.1].

Notice the WARNING! message that is set using the new 11g sqlnet.ora parameter SEC_USER_UNAUTHORIZED_ACCESS_BANNER. The sqlnet.ora file is part of the SQL*Net components of Oracle RDBMS, which handle the communication between clients and the database.

oracle@nodename:/u01/app/oracle/admin/newdb[newdb]> sqlplus /nolog
SQL*Plus: Release - Production on Thu Nov 5 19:00:29
Copyright (c) 1982, 2008, Oracle. All rights reserved.
@> connect / as sysdba
WARNING! This computer system is the property of YOUR
and may be accessed only by authorized users.
Unauthorized use of this system is strictly prohibited and may be
subject to criminal prosecution.

If you wanted to execute something that is available on the operating system level, then you would use a host command (either Windows or Unix), or on Unix the ! symbol. The output below shows that I am logged into the newdb as sys and lists (ls command) the files located in the $ORACLE_HOME/sqlplus/admin directory:

Oracle: Environmental Variables and Scripting

Notice how the SQL prompt is populated with the ORACLE_SID and the username that is connected to the database. This is done by adding a line to the glogin.sql file, which can be done within SQL*Plus as shown below (I used the text editor vi):

Oracle: Environmental Variables and Scripting

Host commands work based on the location of SQL*Plus. If you want to execute these same commands from a Windows desktop connecting to a remote Unix database, then it would require a Windows equivalent command like Notepad or another editor. If you have Cygwin installed and configured on the Windows desktop, then it would allow you to run Unix-equivalent commands like vi.

Separating the variable part of the script into its own configuration file

There are scripts that will need some variables set, but you don’t necessarily want to use the profile to set a variable at every login. The variables may need to contain commands specific to applications such as RMAN, SQL*Plus or specific to certain tasks. This is where a configuration file comes in handy, which is a personal preference as to what you call them. Be sure not to use reserved keywords for variables, as that leads to unexpected results. In the example below, we use emal instead of the word email.

To call this configuration file, you source it starting with the prerequisite . (dot) within a script. This file can also be used in the custom code section of the ioraenv file.

Oracle: Environmental Variables and Scripting


Please enter your comment!
Please enter your name here