1 Administering Oracle Database

1.2.1 Oracle Database Environment Variables

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

directory1:directory2:directory3

1.2.2 UNIX Environment Variables

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

hostname:server[.screen]

135.287.222.12:0.0
bambi:0

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.

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.

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

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

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.

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.

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.

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=""

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.

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.

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.

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