1
Administering Oracle8i

• Setting the Environment

• Environment Variables for Oracle8i

• Initialization Parameters

• Database Limits

• Managing Special Accounts and Groups

• Managing Security

• Administering Login Home Directories

• Estimating Oracle8i Memory Usage

• Server Resource Limits

• Controlling the System Global Area

• Building and Running Demonstrations

• Relinking Network Executables

Setting the Environment

This section describes how to establish a common environment for your Oracle8i system.

Displaying Environment Variables

To display the current value of an environment variable, use the echo command. For example, to display the value of ORACLE_SID, enter:

$ echo $ORACLE_SID

Setting and Exporting the Value of a Variable in a Current Session

For the Bourne or Korn shell, enter:

$ ORACLE_SID=test
$ export ORACLE_SID

For the C shell, enter:

% setenv ORACLE_SID test

where test is the value of the variable ORACLE_SID.

Setting a Common Environment

Oracle8i allows a DBA to set a common environment for all users. A common environment makes it easier for system administrators and database administrators to make changes to the physical Oracle8i system.

The oraenv Command File

The oraenv (coraenv for the C shell) command file is created during installation. It contains values for Oracle environment variables and provides:

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

• a mechanism for switching back and forth between Oracle8i databases

For example, you need to move the database from /usr/oracle to /usr1/oracle. Without a common environment-setting routine, you need to update user startup files individually. With oraenv, each user profile calls the oraenv command file.

Local bin Directory

Placing oraenv (or coraenv) and dbhome in a local bin directory, separate from the Oracle software home directory, ensures that these files are accessible to all users. It also ensures that oraenv (coraenv) continues to work even if you change the path to point to a different ORACLE_HOME. The local bin directory is specified by the root.sh script, which is run following installation. The default location for the local bin directory on Solaris is /usr/local/bin.

Moving Between Databases

To switch from one database or instance to another, call the oraenv routine, and reply to the prompt with the sid of the desired database. Always provide the full path of the oraenv command file. For example:

$ . /usr/local/bin/oraenv
ORACLE_SID= [default]? sid

Database Examples

In the following examples, it is assumed your local bin directory is called /usr/local/bin and your production database is called PROD. If you prefer not to be prompted for the ORACLE_SID at startup, set the ORAENV_ASK environment variable to no.

In the following examples, ORAENV_ASK is reset to the default, Yes, after oraenv is executed. This ensures that the system prompts you for a different ORACLE_SID the next time oraenv is executed.

Single Instance

For the Bourne or Korn shell, add or replace the following line in the.profile file:

. local_bin_directory/oraenv

with the following lines:

PATH=${PATH}:/usr/local/bin
ORACLE_SID=PROD
export PATH ORACLE_SID
ORAENV_ASK=NO
. oraenv
ORAENV_ASK=

For the C shell, add or replace the following line in the.cshrc file:

source local_bin_directory/coraenv

with the following lines:

setenv PATH ${PATH}:/usr/local/bin
setenv ORACLE_SID PROD
setenv ORAENV_ASK NO
source /usr/local/bin/coraenv
unset ORAENV_ASK

Multiple Instances

For multiple instances, define the sid at startup.

For the Bourne or Korn shell:

PATH=${PATH}:/usr/local/bin
ORACLE_SID=PROD
export PATH ORACLE_SID
SIDLIST= `awk -F: '/^[^#]/ {printf "%s ", $1}' /var/opt/oracle/oratab
echo "SIDS on this machine are $SIDLIST"
ORAENV_ASK=
./usr/local/bin/oraenv

For the C shell:

setenv PATH ${PATH}:/usr/local/bin
setenv ORACLE_SID PROD
setenv SIDLIST `awk -F: '/^[^#]/ {printf "%s ", $1}' /var/opt/oracle/oratab
echo "SIDS on this machine are $SIDLIST"
unset ORAENV_ASK
source /usr/local/bin/coraenv

Environment Variables for Oracle8i

This section describes the most commonly-used Oracle8i and UNIX environment variables.

Some of these variables must be defined before you install Oracle8i. They are listed in your Oracle8i Installation Guide.

Oracle Environment Variables on UNIX

Table 1-1 provides the syntax and examples for Oracle8i variables.

Table 1-1 Oracle8i Environment Variables on UNIX

Abbreviations for ORACLE_HOME and ORACLE_SID

In Oracle8i files and programs, a question mark (?) represents the value of ORACLE_HOME. For example, Oracle8i expands the question mark in the following SQL statement to the full pathname of ORACLE_HOME:

alter tablespace TEMP add datafile '?/dbs/dbs2.dbf' size 2M

The @ sign represents $ORACLE_SID. For example, to indicate a file belonging to the current instance, enter:

alter tablespace tablespace_name add datafile 'dbsfile@.dbf'

UNIX Environment Variables Used with Oracle8i

Table 1-2 provides the syntax and examples for UNIX environment variables used with Oracle8i.

Table 1-2 UNIX Environment Variables Used with Oracle8i

Setting the System Time

The TZ variable sets your time zone. Check your Sun SPARC Solaris documentation to see if your operating system uses this environment variable.

It allows 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 SYSDATE.

Initialization Parameters

Initialization parameters allow you to configure and tune your system. This section describes:

• customizing initialization parameters in the initsid.ora file for the Oracle8i instance

• pre-set default initialization parameters

There are many optional initialization parameters described in the generic Oracle8i documentation.

Customizing the initsid.ora File

This section documents the default initsid.ora file provided with the Oracle8i software. The Oracle Universal Installer (OUI) creates it in the
$ORACLE_BASE/admin/sid/pfile directory. You can modify it to customize your Oracle8i installation.

Some initsid.ora parameter settings are generic to any size installation. For those parameter settings requiring different values for different size installations, three scenarios are provided: small, medium, and large. In the sample initsid.ora file, parameters dependent on installation size are shown for each setting. You can comment out settings that do not apply to your installation by inserting a number sign (#) at the beginning of a line.

Table 1-3 suggests the approximate SGA sizes corresponding to the three scenarios provided in the initsid.ora file.

Table 1-3 Block and SGA Sizes for Sample initsid.ora File

Sample initsid.ora File

This file is provided by Oracle Corporation to assist in customizing your Oracle8i installation.

# replace DEFAULT with your database name
db_name=DEFAULT

db_files = 80                                                         # SMALL  
# db_files = 400                                                      # MEDIUM 
# db_files = 1500                                                     # LARGE  

db_file_multiblock_read_count = 8                                     # SMALL  
# db_file_multiblock_read_count = 16                                  # MEDIUM 
# db_file_multiblock_read_count = 32                                  # LARGE  

db_block_buffers = 100                                                # SMALL  
# db_block_buffers = 550                                              # MEDIUM 
# db_block_buffers = 3200                                             # LARGE  

shared_pool_size = 3500000                                            # SMALL
# shared_pool_size = 5000000                                          # MEDIUM
# shared_pool_size = 9000000                                          # LARGE

log_checkpoint_interval = 10000

processes = 50                                                        # SMALL  
# processes = 100                                                     # MEDIUM 
# processes = 200                                                     # LARGE  

parallel_max_servers = 5                                              # SMALL
# parallel_max_servers = 4 x (number of CPUs)                         # MEDIUM
# parallel_max_servers = 4 x (number of CPUs)                         # LARGE

log_buffer = 32768                                                    # SMALL
# log_buffer = 32768                                                  # MEDIUM
# log_buffer = 163840                                                 # LARGE

# audit_trail = true            # if you want auditing
# timed_statistics = true       # if you want timed statistics
max_dump_file_size = 10240      # limit trace file size to 5 Meg each

# Uncommenting the lines below will cause automatic archiving if archiving has
# been enabled using ALTER DATABASE ARCHIVELOG.
# log_archive_start = true      
# log_archive_dest = disk$rdbms:[oracle.archive]
# log_archive_format = "T%TS%S.ARC"

# If using private rollback segments, place lines of the following
# form in each of your instance-specific init.ora files:
# rollback_segments = (name1, name2)   

# If using public rollback segments, define how many
# rollback segments each instance will pick up, using the formula
#   # of rollback segments = transactions / transactions_per_rollback_segment
# In this example each instance will grab 40/5 = 4:
# transactions = 40 
# transactions_per_rollback_segment = 5 

# Global Naming -- enforce that a dblink has same name as the db it 
# connects to:
# global_names = TRUE

# Edit and uncomment the following line to provide the suffix that will be
# appended to the db_name parameter (separated with a dot) and stored as the
# global database name when a database is created.  If your site uses 
# Internet Domain names for e-mail, then the part of your e-mail address after
# the '@' is a good candidate for this parameter value.

# db_domain = us.acme.com 	
# global database name is db_name.db_domain

#_db_block_cache_protect = true                       # memory protect buffers
# event = "10210 trace name context forever, level 2" # data block checking
# event = "10211 trace name context forever, level 2" # index block checking
# event = "10235 trace name context forever, level 1" # memory heap checking
# event = "10049 trace name context forever, level 2" # memory protect cursors

# define parallel server (multi-instance) parameters
# ifile = ora_system:initps.ora

# define two control files by default
control_files = (ora_control1, ora_control2)

# Uncomment the following line if you wish to enable the Oracle Trace product
# to trace server activity.  This enables scheduling of server collections
# from the Oracle Enterprise Manager Console.  
# Also, if the oracle_trace_collection_name parameter is non-null, 
# every session will write to the named collection, as well as enabling you 
# to schedule future collections from the console.

# oracle_trace_enable = TRUE

# Uncomment the following line if you want to use some of the new 8.1
# features. Please remember that using them may require some downgrade 
# actions if you later decide to move back to 8.0.

# compatible = 8.1.0

Default Initialization Parameter Values

Table 1-4 lists default initialization parameter values on Solaris. All Oracle8i instances assume these values if you do not specify different values for them in the initsid.ora file. Oracle Corporation recommends that you include in the initsid.ora file only those parameters that differ from the default initialization parameter values.

To display the current values of these parameters on the system, use SQL*Plus to execute the statement SHOW PARAMETERS.

Table 1-4 Default Initialization Parameters

Database Limits

Table 1-5 lists the maximum and default values for parameters in a CREATE DATABASE or CREATE CONTROL FILE statement.

Table 1-5 Create Control File Parameters

Managing Special Accounts and Groups

The DBA should be familiar with special accounts required by the Oracle server and should make sure these accounts belong to the appropriate groups. UNIX accounts are described in Table 1-6; Oracle server accounts are described in Table 1-7. Special group accounts are described in Table 1-8.

Table 1-6 UNIX Accounts

Table 1-7 Oracle Server Accounts

Table 1-8 Special Group Accounts

Managing Security

Oracle8i 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 upon execution.

The two-task architecture of Oracle8i 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 on the oracle program.

Groups and Security

To ensure greater security for an Oracle8i database, create user groups at the operating system level. Groups are controlled by the UNIX file /etc/group. Oracle programs are divided into two sets for security purposes: those executable by all (other, in UNIX terms), and those executable by DBAs only. A recommended approach to security is:

• Before installing Oracle8i, create a database administrators' group (dba) and assign the root and oracle software owner IDs to this group. Programs executable only by the dba group have permission 710.

• Create a group named oinstall. The oinstall group will own the OUI oraInventory and are responsible for installing and upgrading the Oracle8i system. All oracle accounts must belong to this group.

• Add another group of users to allow a subset of UNIX users limited access to Oracle8i. Publicly executable programs and Oracle utilities, such as SQL*Plus, should be executable by this group. Set the permissions on the utilities to 751 to grant execute permissions to this group, but not other. If you create such a user group, members of the dba group must also be included in it.

• Grant permission 711 to programs executable by other. Restrict this permission to programs that do not affect database security.

Although you can assign any name to the database administrators' group, dba is the default group name and the convention used in this document. If you have multiple databases with the same ORACLE_HOME (a configuration which Oracle Corporation strongly discourages), they should have the same database administrators' group.

Security for Server Manager Commands

If you do not have SQL*Plus, you can use Server Manager to make SQL queries. However, be careful how you assign access to Server Manager. The following system-privileged statements should not be accessible to anyone but the oracle software owner and the dba group users, as they grant special operating system privileges:

• STARTUP

• SHUTDOWN

• CONNECT INTERNAL

Security for Database Files

The user ID used to install Oracle8i should own the database files. The default user ID is the oracle software owner. Set the authorizations on these files to 0600: read/write (rw) by owner only, with no write authorizations for group or other users.

The oracle software owner should own the directories containing the database files. For added security, revoke read permission from group and other users.

To access the protected database files, the oracle program must have its set user ID (setuid) bit on.

The Oracle Universal Installer automatically sets the user ID of the oracle executable to:

-rwsr-s--x 1 oracle dba  443578 Mar 10 23:03 oracle

The s in the user execute field means when you execute the oracle program, it has an effective user ID of oracle, regardless of the actual user ID of the person invoking it.

If you need to set this manually, enter:

$ chmod 6751 $ORACLE_HOME/bin/oracle

Security and Remote Passwords

You can administer a database from a remote machine, such as a personal computer, without operating system accounts. User validation is accomplished by using an Oracle8i password file, created and managed by the orapwd utility. You can also use password file validation on systems that support operating system accounts.

Local password files are in the $ORACLE_HOME/dbs directory and contain the username and password information for a single database. If there are multiple $ORACLE_HOME directories on a machine, each has a separate password file.

Running orapwd

The orapwd utility exists in $ORACLE_HOME/bin and is run by the oracle software owner. Invoke orapwd by entering:

$ orapwd file=filename password=password entries=max_users

This syntax is described in Table 1-9:

Table 1-9 Syntax for Executing orapwd

orapwd Example

$ orapwd file=/u01/app/oracle/product/8.1.5/dbs/orapwV815 \

 password=rsdb3t4 entries=30

Access to a Database from a Remote PC

When there is an Oracle8i password file, networked PC users with DBA privileges can access this database as INTERNAL. Privileged users who want to perform DBA functions on the database can enter the appropriate SQL*Plus command from their PC, adding the dba user password. For example:

SQL> connect internal/dba_password@alias as {sysdba|sysoper}

Remote Authentication

The following initsid.ora parameters, shown in Table 1-10, control the behavior of remote connections through non-secure protocols:

Table 1-10 Parameters for Controlling Remote Connections

Administering Login Home Directories

To add or move login home directories without modifying programs that refer to them, you must:

• refer to explicit path names in files designed to store them, for example: /etc/passwd and /var/opt/oracle/oratab

• refer to group memberships in the /etc/group file

It is not necessary to record a pathname except in a central reference file, because a user's home directory can be derived in either of the following ways:

• C shell and Korn shell users can use ~login to refer to a user's home directory.

• Bourne shell users can construct a simple program to do this. See the sample lhd script later in this section.

Similarly, group memberships are computed from /etc/group. See the sample grpx script later in this section.

Sample lhd Script

#!/bin/sh
#
# lhd - print login home directory name for a given user
#
# SYNTAX
# lhd [login]
#
prog=`basename $0`
if [ $# -eq 0 ] ; then
    login=`whoami`

elif [ $# -eq 1 ] ; then
    login=$1
else
    echo "Usage: $prog login" >&2

    exit 2
fi
awk -F: '$1==login {print $6}' login=$login /etc/passwd

Sample grpx Script

#!/bin/sh
# grpx - print the list of users belonging to a given group
#
prog=`basename $0`
if [ $# -ne 1 ] ; then
    echo "Usage: $prog group" >&2
    exit 2
fi
g=$1
# calculate group id of g
gid=`awk -F: '$1==g {print $3}' g=$g /etc/group`
# list users whose default group id is gid
u1=`awk -F: '$4==gid {print $1}' gid=$gid /etc/passwd`
# list users who are recorded members of g
u2=`awk -F: '$1==g {gsub(/,/," "); print $4}' g=$g /etc/group`
# remove duplicates from the union of the two lists
echo $u1 $u2 | tr " " "\012" | sort | uniq | tr "\012" " "
echo

Example 1-1 Using lhd and grpx Scripts

This example shows how the administrator can propagate a skeleton.profile file to the home directory for each member of a group. If the membership list of the clerk group changes, the code does not require modification.

$ for u in `grpx clerk` ; do
> cp /etc/skel/.profile `lhd $u`
> done

Estimating Oracle8i Memory Usage

You need to know Oracle8i's memory usage before starting. Knowing the memory usage requirements helps you determine the number of users you can have on your system, and helps you determine your physical memory and swap space requirement. To calculate the memory requirements, use the following formula:

where n = number of background processes.

To determine the SGA size, see "Calculating the Size of the SGA".

For each client-server connection, use the following formula to estimate virtual memory requirements:

Use the size command to estimate an executable's text size, private data section size, and uninitialized data section size (or bss). Program text is only counted once, no matter how many times the program is invoked, because all Oracle executable text is always shared.

To compute actual Oracle physical memory usage while the database is up and users are connecting to it, use the ps -elf command. Look for all the front end, server, and background Oracle process entries. For each entry, total the "SZ" columns.

The ps command returns process size in pages; your system page size is architecture-dependent. Use the pagesize command to determine whether the size is 4096 or 8192 bytes. For each process, multiply the SZ value by the page size.

Finally, add the text size for the Oracle executable and every other Oracle tool executable running on the system to that subtotal. Remember to count executable sizes only once, regardless of how many times the executable was invoked.

Server Resource Limits

Solaris inherits resource limits from the parent process (see getrlimit(2) in your operating system documentation). These limits apply to the Oracle8i shadow process that executes for user processes. The Solaris default resource limits are high enough for any Oracle8i shadow or background process. However, if these limits are lowered, the Oracle8i system could be affected. Discuss this with your Solaris system manager.

Disk quotas established for the oracle user ID may hinder the operation of the Oracle8i system. Confer with your Oracle8i database administrator and the Solaris system manager before establishing disk quotas.

Controlling the System Global Area

The System Global Area (SGA) is the Oracle structure that resides in shared memory. It contains static data structures, locks, and data buffers. Sufficient shared memory must be available to each oracle process to address the entire SGA.

Size Limits of the SGA

The maximum size of a single shared memory segment is specified by the Solaris parameter SHMMAX. For example, if SHMMAX is 512 KB and the SGA is 2048 KB, the SGA requires four segments.

If the size of the SGA exceeds the maximum size of a shared memory segment (SHMMAX), Oracle8i attempts to attach more contiguous segments to fulfill the requested SGA size. SHMSEG is the maximum number of segments that can be attached by a process. To attach the segments at contiguous addresses, SHMMAX must be set to its maximum value on systems where its size is limited.

The following initsid.ora parameters control the size of the SGA:

• DB_BLOCK_BUFFERS

• DB_BLOCK_SIZE

• SORT_AREA_SIZE

• SHARED_POOL_SIZE

Use caution when setting values for these parameters. When values are set too high, too much of the machine's physical memory is devoted to shared memory resulting in poor performance.

Calculating the Size of the SGA

You can determine the SGA size in one of these ways:

• The approximate size of an SGA per instance can be calculated with this formula:

  (DB_BLOCK_BUFFERS × DB_BLOCK_SIZE)
  + SORT_AREA_SIZE
  + SHARED_POOL_SIZE
  + LOG_BUFFER

• To display the size of the SGA for a running database, in bytes, use the SQL*Plus show sga command.

• You can also find the size of the SGA when you start your database system. The SGA size is displayed next to the heading Total System Global Area.

Relocating the SGA

The address at which the SGA is attached affects the amount of virtual address space available for such things as database buffers in the SGA and cursors in the user's application data area.

1. Determine the valid virtual address range for attaching shared memory segments. Use the tstshm executable included in this release of Oracle8i:

   $ tstshm
   

In the output from tstshm, the lines "Lowest shared memory address" and "Highest shared memory address" indicate the valid address range.

$ cd $ORACLE_HOME/rdbms/lib

$ $ORACLE_HOME/bin/genksms -b sgabeg > ksms.s

$ make -f ins_rdbms.mk ksms.o

$ make -f ins_rdbms.mk ioracle

[ "str" | "fix n" | "var n" ]

AAA newline
BBB newline
CCC newline

load data
infile xyz.dat "fix 4"
into table abc
( dept position(01:03) char )

$ sqlplus scott/tiger
SQL > @examp1

$ cd $ORACLE_HOME/plsql/demo
$ make  -f demo_plsql.mk demos

$ make  -f demo_plsql.mk examp9

$ ./examp9

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=plsff))(CONNECT_DATA=(SID=extproc)))

SC=(SID_NAME=extproc)(ORACLE_HOME=/u01/app/oracle/8.1.5)(PROGRAM=extproc))

SQL> connect system/manager
Connected.
SQL> grant create library to scott;
Statement processed.
SQL> connect scott/tiger
Connected.
SQL> create library demolib as
'$ORACLE_HOME/plsql/demo/extproc.so';
Statement processed.

SQL> connect scott/tiger
Connected.
SQL> @extproc