1 Administering Oracle Database

This chapter provides information about administering Oracle Database on UNIX-based operating systems. It contains the following sections:

See Also:

The appropriate appendix in this guide for platform-specific information about administering Oracle Database

1.1 Overview

You must set Oracle Database environment variables, parameters, and user settings for Oracle Database to work. This chapter describes the various settings for Oracle Database.

In Oracle Database files and programs, a question mark (?) represents the value of the ORACLE_HOME environment variable. For example, Oracle Database expands the question mark in the following SQL statement to the full path of the Oracle home directory:

SQL> ALTER TABLESPACE TEMP ADD DATAFILE '?/dbs/temp02.dbf' SIZE 200M

Similarly, the at sign (@) represents the ORACLE_SID environment variable. For example, to indicate a file belonging to the current instance, run the following command:

SQL> ALTER TABLESPACE tablespace_name ADD DATAFILE tempfile@.dbf

1.2 Environment Variables

This section describes the most commonly used Oracle Database and operating system environment variables. You must define some of these environment variables before installing Oracle Database.

To display the current value of an environment variable, use the env command. For example, to display the value of the ORACLE_SID environment variable, run the following command:

$ env | grep ORACLE_SID

To display the current value of all environment variables, run the env command as follows:

$ env | more

1.2.1 Oracle Database Environment Variables

Table 1-1 describes the environment variables used with Oracle Database.

Table 1-1 Oracle Database Environment Variables

Variable Detail Definition

NLS_LANG

Function

Specifies the language, territory, and character set of the client environment. The character set specified by NLS_LANG must match the character set of the terminal or terminal emulator. The character set specified by NLS_LANG can be different from the database character set, in which case the character set is automatically converted.

Refer to Oracle Database Globalization Support Guide for a list of values for this variable.

 

Syntax

language_territory.characterset

 

Example

french_france.we8dec

ORA_NLS10

Function

Specifies the directory where the language, territory, character set, and linguistic definition files are stored.

 

Syntax

directory_path

 

Example

$ORACLE_HOME/nls/data

ORA_TZFILE

Function

Specifies the full path and file name of the time zone file. You must set this environment variable if you want to use the small time zone file ($ORACLE_HOME/oracore/zoneinfo/timezone.dat) for data in the database. Oracle Database 10g uses the large time zone file by default ($ORACLE_HOME/oracore/zoneinfo/timezlrg.dat). This file contains information about more time zones than the small time zone file.

All databases that share information must use the same time zone file. You must stop and restart the database if you change the value of this environment variable.

 

Syntax

directory_path

 

Example

$ORACLE_HOME/oracore/zoneinfo/timezlrg.dat

ORACLE_BASE

Function

Specifies the base of the Oracle directory structure for Optimal Flexible Architecture (OFA) compliant installations.

 

Syntax

directory_path

 

Example

/u01/app/oracle

ORACLE_HOME

Function

Specifies the directory containing the Oracle software.

 

Syntax

directory_path

 

Example

$ORACLE_BASE/product/10.2.0/db_1

ORACLE_PATH

Function

Specifies the search path for files used by Oracle applications such as SQL*Plus. If the full path to the file is not specified, or if the file is not in the current directory, then the Oracle application uses ORACLE_PATH to locate the file.

 

Syntax

Colon-separated list of directories:

directory1:directory2:directory3
 

Example

/u01/app/oracle/product/10.2.0/db_1/bin:.

Note: The period adds the current working directory to the search path.

ORACLE_SID

Function

Specifies the Oracle system identifier.

 

Syntax

A string of numbers and letters that must begin with a letter. Oracle recommends a maximum of 8 characters for system identifiers. For more information about this environment variable, refer to Oracle Database Installation Guide.

 

Example

SAL1

ORACLE_TRACE

Function

Enables the tracing of shell scripts during an installation. If it is set to T, then many Oracle shell scripts use the set -x command, which prints commands and their arguments as they are run. If it is set to any other value, or no value, then the scripts do not use the set -x command.

 

Syntax

T or not T

 

Example

T

ORAENV_ASK

Function

Controls whether the oraenv or coraenv script prompts or does not prompt for the value of the ORACLE_SID environment variable. If it is set to NO, then the scripts do not prompt for the value of the ORACLE_SID environment variable. If it is set to any other value, or no value, then the scripts prompt for a value for the ORACLE_SID environment variable.

 

Syntax

NO or not NO

 

Example

NO

SQLPATH

Function

Specifies the directory or list of directories that SQL*Plus searches for a login.sql file.

 

Syntax

Colon-separated list of directories: directory1:directory2:directory3

 

Example

/home:/home/oracle:/u01/oracle

TNS_ADMIN

Function

Specifies the directory containing the Oracle Net Services configuration files.

 

Syntax

directory_path

 

Example

$ORACLE_HOME/network/admin

TWO_TASK

Function

Specifies the default connect identifier to use in the connect string. If this environment variable is set, then do not specify the connect identifier in the connect string. For example, if the TWO_TASK environment variable is set to sales, then you can connect to a database by using the CONNECT username/password command rather than the CONNECT username/password@sales command.

 

Syntax

Any connect identifier.

 

Range of Values

Any valid connect identifier that can be resolved by using a naming method, such as a tnsnames.ora file or a directory server.

 

Example

PRODDB_TCP


Note:

To prevent conflicts, do not define environment variables with names that are identical to the names of Oracle Database server processes, for example ARCH, PMON, and DBWR.

1.2.2 UNIX Environment Variables

Table 1-2 describes UNIX environment variables used with Oracle Database.

Table 1-2 Environment Variables Used with Oracle Database

Variable Detail Definition

ADA_PATH (AIX only)

Function

Specifies the directory containing the Ada compiler.sm

 

Syntax

directory_path

 

Example

/usr/lpp/powerada

CLASSPATH

Function

Used with Java applications. The required setting for this variable depends on the Java application. Refer to the product documentation for your Java application for more information.

 

Syntax

Colon-separated list of directories or files: directory1:directory2:file1:file2

 

Example

There is no default setting. CLASSPATH must include the following directories:

$ORACLE_HOME/JRE/lib:$ORACLE_HOME/jlib

DISPLAY

Function

Used by X-based tools. Specifies the display device used for input and output. Refer to the X Window System documentation for information.

 

Syntax

hostname:server[.screen]

where hostname is the system name (either IP address or alias), server is the sequential code number for the server, and screen is the sequential code number for the screen. If you use a single monitor, then use the value 0 for both server and screen (0.0).

Note: If you use a single monitor, then screen is optional.

 

Example

135.287.222.12:0.0
bambi:0

DYLD_LIBRARY_PATH (Mac OS X only)

Function

Specifies the list of directories that the shared library loader searches to locate shared object libraries at run time. See the dyld man page for information about this environment variable.

 

Syntax

Colon-separated list of directories: directory1:directory2:directory3

 

Example

/usr/lib:$ORACLE_HOME/lib

HOME

Function

The home directory of the user.

 

Syntax

directory_path

 

Example

/home/oracle

LANG or LANGUAGE

Function

Specifies the language and character set used by the operating system for messages and other output. Refer to the operating system documentation for more information.

Note: This environment variable is not used on Mac OS X.

LD_OPTIONS

Function

Specifies the default linker options. Refer to the ld man page for more information about this environment variable.

LPDEST (Solaris only)

Function

Specifies the name of the default printer.

 

Syntax

string

 

Example

docprinter

LD_LIBRARY_PATH (All platforms except AIX and Mac OS X.)

Function

Specifies the list of directories that the shared library loader searches to locate shared object libraries at run time. Refer to the ld man page for information about this environment variable.

On HP-UX, specifies the path for 64-bit shared libraries.

 

Syntax

Colon-separated list of directories: directory1:directory2:directory3

 

Example

/usr/dt/lib:$ORACLE_HOME/lib

LD_LIBRARY_PATH_64 (SPARC systems only)

Function

Specifies the list of directories that the shared library loader searches to locate specific 64-bit shared object libraries at run time. Refer to the ld man page for information about this environment variable.

 

Syntax

Colon separated list of directories: directory1:directory2:directory3

 

Example

/usr/dt/lib:$ORACLE_HOME/lib64

LIBPATH (AIX only)

Function

Specifies the list of directories that the shared library loader searches to locate shared object libraries at run time. Refer to the ld man page for information about this environment variable.

 

Syntax

Colon-separated list of directories: directory1:directory2:directory3

 

Example

/usr/dt/lib:$ORACLE_HOME/lib

PATH

Function

Used by the shell to locate executable programs; must include the $ORACLE_HOME/bin directory.

 

Syntax

Colon-separated list of directories: directory1:directory2:directory3

 

Example

/bin:/usr/bin:/usr/local/bin:/usr/bin/X11:$ORACLE_HOME/bin: $HOME/bin:.

Note: The period adds the current working directory to the search path.

PRINTER

Function

Specifies the name of the default printer.

 

Syntax

string

 

Example

docprinter

SHLIB_PATH (HP-UX 32-bit libraries only)

Function

Specifies the list of directories that the shared library loader searches to locate shared object libraries at run time. Refer to the ld man page for information about this environment variable.

 

Syntax

Colon-separated list of directories: directory1: directory2: directory3

 

Example

/usr/dt/lib:$ORACLE_HOME/lib32

TEMP, TMP, and TMPDIR

Function

Specifies the default directories for temporary files; if set, tools that create temporary files create them in one of these directories.

 

Syntax

directory_path

 

Example

/u02/oracle/tmp

XENVIRONMENT

Function

Specifies a file containing X Window System resource definitions. Refer to the X Window System documentation for more information.


1.2.3 Setting a Common Environment

This section describes how to set a common operating system environment by using the oraenv or coraenv scripts, depending on your default shell:

  • For the Bourne, Bash, or Korn shell, use the oraenv command.

  • For the C shell, use the coraenv command.

oraenv and coraenv Script Files

The oraenv and coraenv scripts are created during installation. These scripts set environment variables based on the contents of the oratab file and provide:

  • A central means of updating all user accounts with database changes

  • A mechanism for switching between databases specified in the oratab file

You may find yourself frequently adding and removing databases from your development system or your users may be switching between several different Oracle Databases installed on the same system. You can use the oraenv or coraenv script to ensure that user accounts are updated and to switch between databases.

Note:

Do not call the oraenv or coraenv script from the Oracle software owner (typically oracle) user's shell startup script. Because these scripts prompt for values, they can prevent the dbstart script from starting a database automatically when the system starts.

The oraenv or coraenv script is usually called from the user's shell startup file (for example .profile or.login). It sets the ORACLE_SID and ORACLE_HOME environment variables and includes the $ORACLE_HOME/bin directory in the PATH environment variable setting. When switching between databases, users can run the oraenv or coraenv script to set these environment variables.

Note:

To run one of these scripts, use the appropriate command:
  • coraenv script:

    % source /usr/local/bin/coraenv
    
  • oraenv script:

    $ . /usr/local/bin/oraenv
    

Local bin Directory

The directory that contains the oraenv, coraenv, and dbhome scripts is called the local bin directory. All database users must have read access to this directory. Include the path of the local bin directory PATH environment variable setting for the users. When you run the root.sh script after installation, the script prompts you for the path of the local bin directory and automatically copies the oraenv, coraenv, and dbhome scripts to the directory that you specify. The default local bin directory is /usr/local/bin. If you do not run the root.sh script, then you can manually copy the oraenv or coraenv and dbhome scripts from the $ORACLE_HOME/bin directory to the local bin directory.

1.2.4 Setting the System Time Zone

The TZ environment variable sets the time zone. It enables you to adjust the clock for daylight saving time changes or different time zones. The adjusted time is used to time-stamp files, produce the output of the date command, and obtain the current value of SYSDATE.

Oracle recommends that you do not change your personal TZ value. Using different values of TZ, such as GMT+24, may change the date a transaction is recorded. This changed date affects Oracle applications that use SYSDATE. To avoid this problem, use sequence numbers to order a table instead of date columns.

1.3 Initialization Parameters

The following sections provide information about Oracle Database initialization parameters:

1.3.1 DB_BLOCK_SIZE Initialization Parameter

The DB_BLOCK_SIZE initialization parameter specifies the standard block size for the database. This block size is used for the SYSTEM tablespace and by default in other tablespaces.

The maximum value to which you can set the DB_BLOCK_SIZE is 16 KB on Linux, and Solaris. It is 32 KB on AIX, HP-UX, Tru64 UNIX, and Mac OS X.

Note:

You cannot change the value of the DB_BLOCK_SIZE initialization parameter after you create a database.

1.3.2 ASM_DISKSTRING Initialization Parameter

Note:

Only Automatic Storage Management instances support the ASM_DISKSTRING initialization parameter.

The syntax for assigning a value to the ASM_DISKSTRING initialization parameter is as follows:

ASM_DISKSTRING = 'path1'[,'path2', . . .]

In this syntax, pathn is the path to a raw device. You can use wildcard characters when specifying the path.

Table 1-3 lists the platform-specific default values for the ASM_DISKSTRING initialization parameter.

Table 1-3 Default Values of the ASM_DISKSTRING Initialization Parameter

Platform Default Search String

AIX

/dev/rhdisk*

HP-UX

/dev/rdsk/*

Linux

/dev/raw/*

Solaris

/dev/rdsk/*

Tru64 UNIX

/dev/rdisk/*


See Also:

The glob(7) man page for platform-specific information about the wildcard character patterns that you can use when specifying the path of a raw device

1.3.3 LOG_ARCHIVE_DEST_n Initialization Parameter

The maximum value that you can set for ASYNC in the LOG_ARCHIVE_DEST_n initialization parameter differs on UNIX platforms as listed in the following table.

Platform Maximum Value
zSeries Linux 12800
HP-UX and Tru64 UNIX 51200
Other operating systems 102400

1.4 Operating System Accounts and Groups

This section describes the following special operating system accounts and groups that are required by Oracle Database:

1.4.1 Oracle Software Owner Account

The Oracle software owner account, usually named oracle, is the account that you use to install the Oracle software. You can use different Oracle software owner accounts to install the software in separate Oracle home directories. However, for each Oracle home directory, you must use the same account that installed the software for all subsequent maintenance tasks on that Oracle home directory.

Oracle recommends that the Oracle software owner have the Oracle Inventory group as its primary group and the OSDBA group as its secondary group.

1.4.2 OSDBA, OSOPER, and Oracle Inventory Groups

Table 1-4 describes the special operating system groups required by Oracle Database.

Table 1-4 Operating System Groups

Group Typical Name Description

OSDBA

dba

Operating system accounts that are members of the OSDBA group have special database privileges. Members of this group can connect to the database using the SYSDBA privilege. The Oracle software owner is the only required member of this group. You can add other accounts as required.

OSOPER

oper

The OSOPER group is an optional group. Operating system accounts that are members of the OSOPER group have special database privileges. Members of this group can connect to the database using the SYSOPER privilege.

Oracle Inventory

oinstall

All users installing Oracle software must belong to the same operating system group. This group is called the Oracle Inventory group. It must be the primary group of the Oracle software owner during installations. After the installation, this group owns all the Oracle files installed on the system.


See Also:

Oracle Database Administrator's Guide and Oracle Database Installation Guide for more information about the OSDBA group and SYSDBA privileges, and the OSOPER group and SYSOPER privileges

Oracle Database uses several features of the UNIX operating system to provide a secure environment for users. These features include file ownership, group accounts, and the ability of a program to change its user ID during processing.

The two-task architecture of Oracle Database improves security by dividing work (and address space) between the user program and the oracle program. All database access is achieved through the shadow process and special authorizations in the oracle program.

See Also:

Oracle Database Administrator's Guide for more information about security issues

1.4.3 Groups and Security

Oracle programs are divided into two sets for security purposes: those that can be run by all (other in UNIX terms), and those that can be run by DBAs only. Oracle recommends that you take the following approach to security:

  • The primary group for the oracle account must be the oinstall group.

  • The oracle account must have the dba group as a secondary group.

  • Although any user account that requires the SYSDBA privilege can belong to the dba group, the only user accounts that should belong to the oinstall group are the Oracle software owner accounts. For example, the oracle user.

1.4.4 External Authentication

If you choose to use external authentication, then you must use the value of the OS_AUTHENT_PREFIX initialization parameter as a prefix for Oracle user names. If you do not explicitly set this parameter, then the default value on UNIX is ops$, which is case-sensitive.

To use the same user names for both operating system and Oracle authentication, set this initialization parameter to a null string:

OS_AUTHENT_PREFIX=""

See Also:

Oracle Database Administrator's Guide for more information about external authentication

1.4.5 Running the orapwd Utility

You can use a password file to identify users that can use the SYSDBA and SYSOPER privileges when connecting to the database. If you use Oracle Database Configuration Assistant to create a database, then the assistant creates a password file for the new database. If you create the database manually, then create the password file for it as follows:

  1. Log in as the Oracle software owner.

  2. Use the orapwd utility to create the password file as follows:

    $ $ORACLE_HOME/bin/orapwd file=filename password=password entries=max_users
    

    The following table describes the values that you must specify in this command.

Value Description
filename The name of the file in which password information is written

The name of the file must be orapwsid, and you must supply the full path name. Its contents are encrypted. Typically, the password file is created in the $ORACLE_HOME/dbs directory.

password The password for the SYS user

If you use an ALTER USER statement to change the password for the SYS user after you connect to the database, then both the password stored in the data dictionary and the password stored in the password file are updated. This parameter is mandatory.

max_users Sets the maximum number of entries permitted in the password file. This is the maximum number of distinct users permitted to connect to the database simultaneously with either the SYSDBA or the SYSOPER privilege.

See Also:

Oracle Database Administrator's Guide for more information about using the orapwd utility

1.4.6 Password Management

When using Oracle Database Configuration Assistant to create a database, users must change the SYS and SYSTEM account passwords. You cannot use the default CHANGE_ON_INSTALL and MANAGER passwords.

For security reasons, Oracle Database Configuration Assistant locks most Oracle user accounts after it creates the database. It does not lock the SYS or SYSTEM accounts. You must unlock any locked accounts and change their passwords before using them. To do this, you can use one of the following methods:

  • To change the passwords by using Oracle Database Configuration Assistant, click Password Management in the Database Configuration Assistant Summary window.

  • Alternatively, use SQL*Plus to connect to the database as SYS and run the following command to unlock an account and reset its password:

    SQL> ALTER USER username IDENTIFIED BY passwd ACCOUNT UNLOCK;
    

1.4.7 Creating Additional Operating System Accounts

If required, create additional operating system accounts. Users must be members of the OSDBA or OSOPER groups to connect to the database with administrator privileges.

1.4.8 Configuring the Accounts of Oracle Users

Update the startup files of the oracle user and the operating system accounts of Oracle users, specifying the appropriate environment variables in the environment file.

For the Bourne, Bash, or Korn shell, add the environment variables to the .profile file, or the .bash_profile file for the Bash shell on Red Hat Enterprise Linux and Mac OS X.

For the C shell, add the environment variables to the .login file.

Note:

You can use the oraenv or coraenv script to ensure that Oracle user accounts are updated.

1.5 Using Raw Devices

The following sections provide information about using raw devices (raw partitions or raw volumes):

See Also:

The appendix corresponding to your platform in this guide for additional raw device tuning information

1.5.1 Guidelines for Using Raw Devices

Raw devices (raw partitions or raw volumes) have the following potential disadvantages:

  • Raw devices may not solve problems with file size writing limits.

    To display current file size limits, run one of the following commands:

    • Bourne, Bash, or Korn shell:

      $ ulimit -a
      
    • C shell:

      % limit
      
  • If a particular disk drive has intense I/O activity and performance would benefit from movement of an Oracle data file to another drive, then it is likely that no acceptably sized partition or volume exists on a drive with less I/O activity. It may not be possible to move files to other disk drives if you are using raw devices.

  • Raw devices are more difficult to administer than data files stored on a file system or in an Automatic Storage Management disk group.

Consider the following issues when deciding to use raw devices:

  • Raw disk partition availability

    Use raw partitions for Oracle files only if you have at least as many raw disk partitions as Oracle data files. If disk space is a consideration and the raw disk partitions are already created, then match data file size to partition size as closely as possible to avoid wasting space.

    You must also consider the performance implications of using all the disk space on a few disks as opposed to using less space on more disks.

  • Logical volume manager

    Logical volume managers manage disk space at a logical level and hide some of the complexity of raw devices. With logical volumes, you can create logical disks based on raw partition availability. The logical volume manager controls fixed-disk resources by:

    • Mapping data between logical and physical storage

    • Enabling data to span multiple disks and to be discontiguous, replicated, and dynamically expanded

    For Oracle RAC, you can use logical volumes for drives associated with a single system, as well as those that can be shared with more than one system of a cluster. Shared drives enables all files associated with an Oracle RAC database to be placed on these shared logical volumes.

  • Dynamic performance tuning

    To optimize disk performance, you can move files from disk drives with high activity to disk drives with low activity. Most hardware vendors who provide the logical disk facility also provide a graphical user interface (GUI) that you can use for tuning.

  • Mirroring and online disk replacement

    You can mirror logical volumes to protect against loss of data. If one copy of a mirror fails, then dynamic resynchronization is possible. Some vendors also provide the ability to replace drives online in conjunction with the mirroring facility.

1.5.2 Raw Device Setup

When creating raw devices, ensure that:

  • The owner is the Oracle software owner user (oracle) and the group is the OSDBA group (dba).

  • The size of an Oracle data file created in a raw partition is at least two Oracle block sizes smaller than the size of the raw partition.

    See Also:

    The operating system documentation for more information about creating raw devices

1.5.3 Raw Device Data Files on AIX and Tru64 UNIX Systems

On AIX and Tru64 UNIX systems, data files on raw logical volumes may have offsets for the first block of the Oracle data. This offset is required by the logical volume manager.

You can use the $ORACLE_HOME/bin/offset utility to determine the offset value. You may need to do this, for example, if you want to transfer the data file to a different device.

See Also:

Appendix A for more information about creating a raw logical volume on AIX, which enables you to use a zero offset. The zero offset is recommended for raw logical volumes on AIX.

1.5.4 Raw Device Support on Linux Systems

You can use both raw character devices and block devices as raw volumes in creating a database. Because block devices are supported, the kernel level limitation on the maximum number of raw devices is removed and you can configure additional raw volumes to meet your requirements.

1.6 Using Trace and Alert Files

This section describes the trace (or dump) and alert files that Oracle Database creates to help you diagnose and resolve operating problems. It includes the following sections:

1.6.1 Trace Files

Each server and background process writes to a trace file. When a process detects an internal error, it writes information about the error to its trace file. The file name format of a trace file is sid_processname_unixpid.trc, where:

  • sid is the instance system identifier

  • processname is a three or four-character abbreviated process name identifying the Oracle Database process that generated the file (for example, pmon, dbwr, ora, or reco)

  • unixpid is the operating system process ID number

The following is a sample trace file name:

$ORACLE_BASE/admin/TEST/bdump/test_lgwr_1237.trc

All trace files for background processes are written to the destination directory specified by the BACKGROUND_DUMP_DEST initialization parameter. If you do not set this initialization parameter, then the default directory is $ORACLE_HOME/rdbms/log.

All trace files for user processes are written to the destination directory specified by the USER_DUMP_DEST initialization parameter. If you do not set this initialization parameter, then the default directory is $ORACLE_HOME/rdbms/log. Set the MAX_DUMP_FILE initialization parameter to at least 5000 to ensure that the trace file is large enough to store error information.

1.6.2 Alert Files

The alert_sid.log file stores information about significant database events and messages. Events that affect the database instance or database are recorded in this file. This file is associated with a database and is located in the directory specified by the BACKGROUND_DUMP_DEST initialization parameter. If you do not set this initialization parameter, then the default directory is $ORACLE_HOME/rdbms/log.