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

You can create a syslog audit trail to track administrative activities.

See Also:

The "Using the Syslog Audit Trail to Audit System Administrators on UNIX Systems section in the Oracle Database Security Guide for more information about the syslog audit trail

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.we8iso8859p15

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 to use the small time zone file ($ORACLE_HOME/oracore/zoneinfo/timezone.dat) for data in the database. Oracle Database 11g 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 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/11.1.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/11.1.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 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
 

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. Oracle tools that are programmed in Java, such as Oracle Universal Installer and Database Configuration Assistant, may also use this variable to determine the language of their user interface. Refer to the operating system documentation for more information.

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)

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 the 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 the 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 keep the TZ value consistent among the processes of an Oracle database. This implies that the TZ value should be the same in the following environments:

  • A shell starting any instance of the database, the SQL*Plus startup command

  • A shell starting the Oracle Database Control Console, the emctl start dbconsole command

  • A shell starting the Enterprise Manager Management Agent on any of the database hosts, the emctl start agent command

  • A shell starting any listener connecting to the database, the lsnrctl start command

  • A shell in which a process connects to the database through the Bequeath protocol, for example, using the ORACLE_HOME and ORACLE_SID environment variables to address the database instance

If the time zone is not consistent among the above environments, the values of the SYSDATE and SYSTIMESTAMP functions may vary depending on the database session calling the functions. This is inconsistent with the semantics of the functions. If you want to work with different time zones in different database sessions, use the CURRENT_DATE, CURRENT_TIMESTAMP, or LOCALTIMESTAMP functions with an appropriate setting for the session time zone. Refer to the ALTER SESSION command for more details.

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 x86. It is 32 KB on other platforms.

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/*

Solaris

/dev/rdsk/*


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
HP-UX 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 OSASM, 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

OSASM

dba

The OSASM group is provided exclusively for Automatic Storage Management. Initially, only the user that installs Automatic Storage Management is a member of the OSASM group, if you use a separate operating system group for that privilege. However, you can add other users. Members of the OSASM group are authorized to connect using the SYSASM privilege and have full access to Automatic Storage Management, including administrative access to all disk groups that are managed by that Automatic Storage Management instance.

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 entries=max_users
    Enter password for SYS:
    

    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 When prompted, enter the appropriate SYS password
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.

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 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.5.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/diag/rdbms/mydb/mydb/trace/test_lgwr_1237.trc

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.5.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.