Pro Oracle Database 11g Administration (12 page)
Oracle provides a mechanism for automatically setting the required OS variables. Oracle’s approach relies on two files: oratab and oraenv.
Understanding oratab
You can think of the entries in the oratab file as a registry of what databases are installed on a box and their corresponding Oracle home directories.
The oratab file is automatically created for you when you install the Oracle software. On Linux boxes, oratab is usually placed in the /etc directory. On Solaris servers, the oratab file is placed in the
/var/opt/oracle directory. If for some reason the oratab file isn’t automatically created, you can manually create the directory and file.
The oratab file is used in Linux/Unix environments for the following:
• Automating the sourcing of required OS variables
• Automating the start and stop of Oracle databases on the server
The oratab file has three columns with the following format:
26
CHAPTER 2 ■ IMPLEMENTING A DATABASE
The Y or N indicates whether you want Oracle to automatically restart on reboot of the box; Y
indicates yes, and N indicates no. Automating the startup and shutdown of your database is covered in detail in Chapter 21, “Automating Jobs.”
Comments in the oratab file start with a pound sign (#). Here’s a typical oratab file entry:
# 11g prod databases
O11R2:/oracle/app/oracle/product/11.2.0/db_1:N
ORC11G:/oracle/app/oracle/product/11.2.0/db_1:N
Several Oracle-supplied utilities use the oratab file:
• oraenv uses oratab to set the operating system variables.
• dbstart uses it to automatically start the database on server reboots (if the third field in oratab is Y).
• dbstop uses it to automatically stop the database on server reboots (if the third field in oratab is Y).
The oraenv tool is discussed in the next section.
Using oraenv
If you don’t properly set the required OS variables for an Oracle environment, then utilities such as SQL*Plus, Oracle Recovery Manager (RMAN), Data Pump, and so on won’t work correctly. The oraenv utility automates the setting of required OS variables (such as ORACLE_HOME, ORACLE_SID, and PATH) on an Oracle database server. This utility is used in Bash, Korn, and Bourne shell environments (if you’re in a C
shell environment, there is a corresponding coraenv utility).
The oraenv utility is located in the ORACLE_HOME/bin directory. You can run it manually like this: $ . oraenv
Note that the syntax to run this from the command line requires a space between the dot (.) and the oraenv tool. You’re prompted for ORACLE_SID and ORACLE_HOME values:
ORACLE_SID = [oracle] ?
ORACLE_HOME = [/home/oracle] ?
You can also run the oraenv utility in a non-interactive way by setting OS variables before you run it.
This is useful for scripting when you don’t want to be prompted for input: $ export ORACLE_SID=oracle
$ export ORAENV_ASK=NO
$ . oraenv
My Approach to Setting OS Variables
I don’t use Oracle’s oraenv file to set the OS variables (see the previous section for details of Oracle’s approach). Instead, I use a script named oraset. The oraset script depends on the oratab file being in the correct directory and of the expected format:
As mentioned in the previous section, the Oracle installer should create an oratab file for you in the correct directory. If it doesn’t, then you can manually create and populate the file. In Linux, the oratab 27
CHAPTER 2 ■ IMPLEMENTING A DATABASE
file is usually created in the /etc directory. On Solaris servers, the oratab file is located in
/var/opt/oracle. Here is an example:
O11R2:/ora01/app/oracle/product/11.2.0/db_1:N
DEV1:/ora02/app/oracle/product/11.2.0/db_1:N
The names of the databases on the previous lines are O11R2 and DEV1. The path of each database’s Oracle home directory is next on the line (separated from the database name by a colon [:]). The last column contains Y or N and indicates whether you want the databases to automatically be restarted when the system reboots.
Next, use a script that reads the oratab file and sets the operating system variables. Here is an example of an oraset script that reads the oratab file and presents a menu of choices (based on the database names in the oratab file):
#!/bin/bash
# Why: Sets Oracle environment variables.
# Setup: 1. Put oraset file in /var/opt/oracle
# 2. Ensure /var/opt/oracle is in $PATH
# Usage: batch mode: . oraset
# menu mode: . oraset
#====================================================
if [ -z $1 ]; then
SIDLIST=$(grep -v '#' ${OTAB} | cut -f1 -d:)
# PS3 indicates the prompt to be used for the Bash select command.
PS3='SID? '
select sid in ${SIDLIST}; do
if [ -n $sid ]; then
HOLD_SID=$sid
break
fi
done
else
if grep -v '#' ${OTAB} | grep -w "${1}:">/dev/null; then HOLD_SID=$1
else
echo "SID: $1 not found in $OTAB"
fi
shift
fi
#
export ORACLE_HOME=$(grep -v '#' $OTAB|grep -w $ORACLE_SID:|cut -f2 -d:) export ORACLE_BASE=${ORACLE_HOME%%/product*}
export TNS_ADMIN=$ORACLE_HOME/network/admin
export ADR_HOME=$ORACLE_BASE/diag/rdbms/$(echo $HOLD_SID|tr A-Z a-z)/$HOLD_SID
export PATH=$ORACLE_HOME/bin:/usr/ccs/bin:/opt/SENSsshc/bin/\
:/bin:/usr/bin:.:/var/opt/oracle
export LD_LIBRARY_PATH=/usr/lib:$ORACLE_HOME/lib
You can run the oraset script either from the command line or from a startup file (such as .profile,
.bash_profile, or .bashrc). To run oraset from the command line, place the oraset file in a standard location like /var/opt/oracle and run as follows:
$ . /var/opt/oracle/oraset
28
CHAPTER 2 ■ IMPLEMENTING A DATABASE
Note that the syntax to run this from the command line requires a space between the dot (.) and the rest of the command. When you run oraset from the command line, you should be presented with a menu like this:
1) O11R2
2) DEV1
In this example, you can now enter 1 or 2 to set the OS variables required for whichever database you want to use. This allows you to interactively set up OS variables regardless of the number of database installations on the server.
You can also call the oraset file from an operating system startup file. Here is a sample entry in the
.bashrc file:
. /var/opt/oracle/oraset
Now, every time you log on to the server, you’re presented with a menu of choices that you can use to indicate the database for which you want the OS variables set.
Creating a Database
This section explains how to manually create an Oracle database with the SQL*Plus CREATE DATABASE
statement. Listed next are the steps required to create a database:
1. Set the operating system variables.
2. Configure the initialization file.
3. Create the required directories.
4. Create the database.
5. Create a data dictionary.
Each of these steps is covered in the following subsections.
Step 1. Set the Operating System Variables
Before you run SQL*Plus (or any other Oracle utility), you must set several OS variables:
• ORACLE_HOME
• PATH
• ORACLE_SID
• LD_LIBRARY_PATH
The ORACLE_HOME variable defines the default directory location for the initialization file, which is ORACLE_HOME/dbs on Linux/Unix. On Windows, this directory is usually ORACLE_HOME\database. The ORACLE_HOME variable is also important because it defines the directory location of the Oracle binary files (such as sqlplus) that are in ORACLE_HOME/bin.
The PATH variable specifies which directories are looked in by default when you type a command from the operating system prompt. In almost all situations, you require ORACLE_HOME/bin (the location of the Oracle binaries) to be included in your PATH variable.
The ORACLE_SID variable defines the default name of the database you’re attempting to create.
ORACLE_SID is also used as the default name for the initialization file, which is init
29
CHAPTER 2 ■ IMPLEMENTING A DATABASE
The LD_LIBRARY_PATH variable is important because it specifies where to search for libraries on Linux/Unix boxes. The value of this variable is typically set to include ORACLE_HOME/lib.
Step 2: Configure the Initialization File
Oracle requires that you have an initialization file in place before you attempt to start the instance. The initialization file is used to configure features such as memory and to control file locations. You can use two types of initialization files:
• Server parameter binary file (spfile)
• init.ora text file
Oracle recommends that you use an spfile for reasons such as these:
• You can modify the contents of the spfile with the SQL ALTER SYSTEM statement.
• You can use remote-client SQL sessions to start the database without requiring a local (client) initialization file.
These are good reasons to use an spfile. However, some shops still use the traditional init.ora file.
The init.ora file also has advantages:
• You can directly edit it with an operating system text editor.
• You can place comments in it that contain a history of modifications.
When I first create a database, I find it easier to use an init.ora file. This file can be easily converted later to an spfile if required (via the CREATE SPFILE FROM PFILE statement). Here are the contents of a typical Oracle Database 11
g
init.ora file:
db_name=O11R2
db_block_size=8192
memory_target=800M
memory_max_target=800M
processes=200
control_files=(/ora01/dbfile/O11R2/control01.ctl,/ora02/dbfile/O11R2/control02.ctl) job_queue_processes=10
open_cursors=300
fast_start_mttr_target=500
undo_management=AUTO
undo_tablespace=UNDOTBS1
remote_login_passwordfile=EXCLUSIVE
Ensure that the initialization file is named correctly and located in the appropriate directory. When starting your instance, Oracle looks in the default location for a binary initialization file named spfile
On Linux/Unix systems, the initialization file (either a text init.ora or binary spfile) is by default located in the ORACLE_HOME/dbs directory. On Windows, the default directory is ORACLE_HOME\database.
Table 2–1 lists best practices when configuring an Oracle initialization file.
30
CHAPTER 2 ■ IMPLEMENTING A DATABASE
Table 2–1.
Initialization File Best Practices
Best Practice
Reasoning
Oracle recommends that you use a binary server
Use whichever type of initialization
parameter file (spfile). However, I still use the old text
parameter file you’re comfortable with. If
init.ora files in some cases.
you have a requirement to use an spfile,
then by all means implement one.
In general, don’t set initialization parameters if you’re
Setting initialization parameters can have
not sure of their intended purpose. When in doubt, use
far-reaching consequences in terms of
the default.
database performance. Only modify
parameters if you know what the resulting
behavior will be.
For 11
g
, set the memory_target and memory_max_target
Doing this allows Oracle to manage all
initialization parameters.
memory components for you.
For 10
g
, set the sga_target and sga_target_max
Doing this lets Oracle manage most memory
initialization parameters.
components for you.
For 10
g
, set pga_aggregate_target and
Doing this allows Oracle to manage the
workarea_size_policy.
memory used for the sort space.
Starting with 10
g
, use the automatic UNDO feature. This is
Doing this allows Oracle to manage most
set using the undo_management and undo_tablespace
features of the UNDO tablespace.
parameters.
Set open_cursors to a higher value than the default. I
The default value of 50 is almost never
typically set it to 500. Active online transaction
enough. Even a small one-user application
processing (OLTP) databases may need a much higher
can exceed the default value of 50 open
value.
cursors.
Name the control files with the pattern
This deviates slightly from the Optimal
/
opposed to being located under
ORACLE_BASE.
Use at least two control files, preferably in different
If one control file becomes corrupt, it’s
locations using different disks.
always a good idea to have at least one other
control file available.
31
CHAPTER 2 ■ IMPLEMENTING A DATABASE
Step 3: Create the Required Directories
Any directories referenced in the initialization file or CREATE DATABASE statement must be created on the server before you attempt to create a database. For example, in the previous section’s initialization file, the control files are defined as