Полезная информация

Oracle8 Administrator's Reference
Release 8.0.5 for Intel-LINUX

A66585-02

Library

Product

Contents

Index

Prev Next

2
Administering Oracle8 on LINUX

Customizing the initsid.ora File

This section documents the default initsid.ora file provided with the Oracle8 distribution. The Oracle Installer creates it in the $ORACLE_BASE/admin/db_name/pfile directory. You can modify it to customize your Oracle8 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 2-1 suggests the approximate SGA sizes corresponding to the three scenarios provided for in the initsid.ora file.

Table 2-1 Block and SGA Sizes for Sample initsid.ora File
Installation/Database Size  
Block Size   Small   Medium   Large  

2 KB

 

4500 KB

 

6800 KB

 

17000 KB

 

4 KB

 

5500 KB

 

8800 KB

 

21000 KB

 

Sample initsid.ora File

This file is provided by Oracle Corporation to assist in customizing the RDBMS installation. Some parameter settings are generic to any size installation. For parameters that require different values in different size installations, three scenarios are provided: SMALL, MEDIUM and LARGE. Any parameter that needs to be tuned according to installation size will have three settings, each one commented according to installation size.


# replace DEFAULT with your database name
db_name=DEFAULT

db_files = 80                                                         # SMALL  
# db_files = 400                                                      # MEDIUM 
# db_files = 1000                                                     # 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 = 8192                                                     # SMALL
# log_buffer = 32768                                                  # MEDIUM
# log_buffer = 163840                                                 # LARGE

sequence_cache_entries = 10                                           # SMALL  
# sequence_cache_entries = 30                                         # MEDIUM 
# sequence_cache_entries = 100                                        # LARGE  

sequence_cache_hash_buckets = 10                                      # SMALL  
# sequence_cache_hash_buckets = 23                                    # MEDIUM 
# sequence_cache_hash_buckets = 89                                    # 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 line 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/10 = 4:
# transactions = 40 
# transactions_per_rollback_segment = 10 

# 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

Setting the Environment

Displaying and Setting Environment Variables

To display the 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

Oracle8 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 Oracle Server 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:

For example, a database needs to move from /usr/oracle to /usr1/oracle. Without a common environment-setting routine, user startup files would need to be updated individually. With oraenv, each user profile calls the oraenv command file and the changes must be made only to that file.

Local bin Directory

Placing oraenv (or coraenv) and dbhome in the 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.

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 examples below, ORAENV_ASK is reset to the default, Yes, after oraenv is executed. This ensures that the system prompts 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
set 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}' /etc/oratab\Q
echo "SIDS on this machine are $SIDLIST"
ORAENV_ASK=
oraenv

For the C shell:

setenv PATH ${PATH}:/usr/local/bin
setenv ORACLE_SID PROD
set sidlist = `awk -F: '/^[^#]/ {printf "%s ", $1}' /etc/oratab\Q
echo "SIDS on this machine are $sidlist"
unset ORAENV_ASK
source /usr/local/bin/coraenv

Environment Variables for Oracle8

Certain variables in the LINUX environment must be set prior to installation of the Oracle system.

See Also:

Oracle8 Installation Guide.

 

Oracle Environment Variables on LINUX

Table 2-2 provides the syntax and examples for Oracle8 variables.

Table 2-2 Oracle8 Environment Variables on LINUX
Variable   Detail   Definition  

EPC_DISABLED

 

Function

 

Disables oracle trace

 

 

Syntax

 

true or false

 

 

Example

 

true

 

NLS_LANG

 

Function

 

Specifies the language and character set used for output. See the Oracle8 Installation Guide for Intel-LINUX for a range of values.

 

Syntax

 

language_territory.characterset

 

Example

 

french_france.we8dec

 

ORA_NLS33

 

Function

 

Points to the directory where languages and character sets are stored.

 

Set to

 

$ORACLE_HOME/ocommon/nls/admin/data

 

ORACLE_BASE

 

Function

 

Specifies the base of the Oracle directory structure for OFA-compliant databases.

 

Syntax

 

directory_path

 

Example

 

/mount_point/app/oracle

 

ORACLE_HELP

 

Function

 

Specifies the directory containing help files.

 

Syntax

 

directory_path

 

Example

 

$ORACLE_HOME/help/admin/resource

 

ORACLE_HOME

 

Function

 

Specifies the directory containing the Oracle software distribution.

 

Syntax

 

directory_path

 

Example

 

/mount_point/app/oracle/product/release_number

 

ORACLE_PATH

 

Function

 

Specifies the search pathname for files used by Oracle applications, such as SQL*Plus. If not specified, the application reads from and writes to the current directory.

 

Syntax

 

colon-separated list of directories directory:directory:directory

 

Example

 

/u01/oracle/adhoc/sqlplus:.
Note: The period adds the current working directory to the search path.

 

ORACLE_SID

 

Function

 

Specifies the Oracle System Identifier.

 

Syntax

 

The string of numbers and characters must begin with a letter. For more information, see the Oracle8 Installation Guide for Intel-LINUX.

 

Example

 

SAL1

 

ORACLE_TERM

 

Function

 

Specifies the terminal type identifier. Used by the Installer and Oracle products to determine the correct Toolkit II (.res) resource file. If not set, the value of the operating-system variable TERM is used.

 

Syntax

 

string of characters

 

Range of Values

 

The value of this variable must be set such that the pattern tk2c${ORACLE_TERM}.res corresponds to valid resource files in the Toolkit II resource directory or directories. See the Oracle8 Installation Guide for Intel-LINUX for a list of valid values.

 

Example

 

vt100

 

ORACLE_TERMINAL

 

Function

 

Specifies an additional directory to search for Toolkit II (.res) resource files.

 

Syntax

 

directory_name

 

Example

 

$ORACLE_HOME/guicommon/tk21/admin/terminal

 

ORACLE_TRACE

 

Function

 

Turns on tracing of Bourne shell scripts during install. If set to T, many Oracle shell scripts run with set-x flag on.

 

Range of Values

 

T or anything else.

 

ORAENV_ASK

 

Function

 

Controls whether (c)oraenv prompts for ORACLE_SID or ORACLE_HOME. If set to NO (c)oraenv does not prompt and, if set to anything else, it does.

 

Syntax

 

string

 

Range of Values

 

NO or anything else.

 

TNS_ADMIN

 

Function

 

Sets the directory containing the Oracle Net8 configuration files.

 

Syntax

 

directory_path

 

Range of Values

 

Any directory; for more information, see the Oracle8 Installation Guide for Intel-LINUX.

 

Example

 

$ORACLE_HOME/network/admin

 

TWO_TASK

 

Function

 

Sets the default Oracle Net8 connect string descriptor alias defined in the tnsnames.ora file.

 

Syntax

 

available network alias

 

Range of Values

 

Any valid Oracle Net8 alias defined in the tnsnames.ora file.

 

Example

 

PRODDB_TCP

 


Note:

Environment variables should not be defined with names that are identical to names of Oracle Server processes, for example: arch, pmon, and dbwr.

 

Abbreviations for ORACLE_HOME and ORACLE_SID

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

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

The @ sign represents $ORACLE_SID. For example, to indicate that a file belongs to an instance, enter:

alter tablespace tablespace_name add datafile 'dbsfile@.ora'

LINUX Environment Variables Used with Oracle8

Table 2-3 provides the syntax and examples for LINUX environment variables used with Oracle8.

Table 2-3 LINUX Environment Variables Used with Oracle8
Variable   Detail   Definition  

ADA_PATH

 

Function

 

Specifies the directory containing the Ada compiler.

 

DISPLAY

 

Function

 

Used by X-based tools. Specifies the display device used for input and output. See vendor's X Windows documentation for details.

 

Syntax

 

hostname:display
Hostname is the network identifier for the display device; display is a number which is almost always 0.

 

Example

 

135.287.222.12:0
bambi:0

 

HOME

 

Function

 

The user's home directory.

 

LANG or LANGUAGE

 

Function

 

Specifies the language and character set used by the operating system for messages and other output. See the operating system documentation, and your Oracle8 Installation Guide for Intel-LINUX.

 

LDOPTS

 

Function

 

Specifies the default linker options on some platforms. See man pages on ld for details.

 

LPDEST

 

Function

 

Specifies the user's default printer for System V-based systems.

 

Syntax

 

printer_name

 

Example

 

docqms

 

LDPATH

 

Function

 

Default directories used by the linker to find shared object libraries. See man pages on ld for details.

 

LD_LIBRARY_PATH

 

Function

 

Used on some platforms by the shared library loader (ld.so) at runtime to find shared object libraries. See man pages on ld.so for details.

 

Syntax

 

colon-separated list of directories directory:directory:directory

 

Example

 

/usr/dt/lib:$ORACLE_HOME/lib

 

PATH

 

Function

 

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

 

 

Syntax

 

colon-separated list of directories directory:directory:directory

 

 

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

 

Selects the user's default printer for LINUX systems.

 

 

Syntax

 

printer_name

 

 

Example

 

docqms

 

SHELL

 

Function

 

Specifies the command interpreter used during a host command.

 

 

Syntax

 

shell pathname

 

 

Range of Values

 

/bin/sh or /bin/csh or /bin/ksh or any other command interpreter supplied with LINUX

 

 

Example

 

/bin/sh

 

TERM

 

Function

 

Used by Oracle Toolkit II character mode tools to determine terminal types; also used by other LINUX tools for the same purpose.

 

 

Example

 

vt100

 

TMPDIR

 

Function

 

Specifies the default directory for temporary disk files; if set, tools that create a temporary files do so in this directory.

 

 

Syntax

 

directory_path

 

 

Example

 

/u02/oracle/tmp

 

XENVIRONMENT

 

Function

 

Specifies a file containing X Windows system resource definitions. See your X Windows documentation for more information.

 

Setting the System Time

The TZ variable sets your time zone. Check your Intel-LINUX documentation to see if your operating system uses this environment variable.

It allows a user 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.


WARNING:

Users are discouraged from changing their personal TZ value. Using different values of TZ such as GMT+24 may change the day a transaction is recorded. This affects Oracle applications that use SYSDATE, such as Oracle Financials. Use sequence numbers to order a table instead of date columns to avoid this problem.

 

Estimating Oracle8 Server Memory Usage

Before starting the Oracle8 Server, virtual memory requirements can be estimated using this formula:

<size of the oracle executable text>
+ <size of the SGA>
+ n * ( <size of tool executables private data section>
+ <size of oracle executables uninitialized data section>
+ <8192 bytes for the stack>
+ <2048 bytes for the processes user area>)

where n = number of background processes.

For each Oracle back-end connection, use the following formula to estimate virtual memory requirements:

<size of oracle executable data section>
+ <size of oracle executables uninitialized data section>
+ <8192 bytes for the stack>
+ <2048 bytes for processes user area>
+ <cursor area needed for the application>

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 command. Look for all the front end, server, and background Oracle process entries. For each entry, add the "real size of process" columns for the resident memory use subtotal. Now 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.

See Also:

Refer to your Intel-LINUX man pages or documentation for a list of available switches for the ps command.

 

Calculating Cluster Size and Index Size

Calculating Cluster Size

Use size guidelines in Table 2-4 to calculate cluster size using the formula in Appendix A of the Oracle8 Administrator's Guide

Table 2-4 Cluster Size Values
Type   Size  

Fixed header size

 

68 bytes

 

Variable transaction header

 

24*INITRANS value for the table

 

Row directory

 

4 bytes per row of a clustered table

 
.

Calculating Index Size

Use Table 2-5 to calculate the size required by an index using the formula in Appendix A of the Oracle8 Administrator's Guide.

Table 2-5 Index Size Values
Type   Size  

Fixed header size

 

113 bytes

 

Variable transaction header

 

24*INITRANS value for the index

 

Entry header

 

5 bytes

 

Server Resource Limits

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

Disk quotas established for the Oracle dba user ID may hinder the operation of the Oracle8 system. Confer with your Oracle8 database administrator and the LINUX system manager before establishing disk quotas.

Initialization Parameters

Initialization parameters can be modified in the initsid.ora file for the Oracle8 Server instance.

See Also:

Oracle8 Administrator's Guide.

 

Default Initialization Parameter Values

Table 2-6 lists default initialization parameter values on LINUX. All Oracle8 Server 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 Server Manager to execute the SQL statement SHOW PARAMETERS.

See Also:

Oracle8 Server Reference.

 

Table 2-6 Default Initialization Parameters
Parameter   Default Value  

BACKGROUND_DUMP_DEST

 

$ORACLE_BASE/admin/sid/bdump

 

BITMAP_MERGE_AREA_SIZE

 

1048576

 

COMMIT_POINT_STRENGTH

 

1

 

CONTROL_FILES

 

$ORACLE_HOME/dbs/ctrl@.dbf
(where @ represents ORACLE_SID)

 

CREATE_BITMAP_AREA_SIZE

 

8388608

 

DB_BLOCK_BUFFERS

 

200

 

DB_BLOCK_SIZE

 

2048

 

DB_FILES

 

80 (maximum of 2000000)

 

DB_FILE_DIRECT_IO_COUNT

 

64 (maximum of 1048576)

 

DB_FILE_MULTIBLOCK_READ_COUNT

 

8 (range of 1-128, but should not exceed one quarter of DB_BLOCK_BUFFERS)

 

DISTRIBUTED_TRANSACTIONS

 

16

 

HASH_AREA_SIZE

 

0

 

HASH_MULTIBLOCK_IO_COUNT

 

1

 

LOCK_SGA

 

FALSE

 

LOCK_SGA_AREAS

 

0

 

LOG_ARCHIVE_BUFFER_SIZE

 

64

 

LOG_ARCHIVE_BUFFERS

 

4 (maximum of 128)

 

LOG_ARCHIVE_DEST

 

$ORACLE_HOME/dbs/arch/

 

LOG_ARCHIVE_FORMAT

 

"%t_%s.dbf"

 

LOG_BUFFER

 

8192

 

LOG_CHECKPOINT_INTERVAL

 

10000

 

LOG_SMALL_ENTRY_MAX_SIZE

 

80

 

MTS_MAX_DISPATCHERS

 

5

 

MTS_MAX_SERVERS

 

20

 

MTS_SERVERS

 

0

 

MTS_LISTENER_ADDRESS

 

ADDRESS=address (See Chapter 6)

 

NLS_LANGUAGE

 

AMERICAN

 

NLS_TERRITORY

 

AMERICA

 

OBJECT_CACHE_MAX_SIZE_PERCENT

 

10

 

OBJECT_CACHE_OPTIMAL_SIZE

 

102400

 

OPEN_CURSORS

 

50

 

OS_AUTHENT_PREFIX

 

ops$

 

PROCESSES

 

50

 

SHARED_POOL_SIZE

 

3500000

 

SORT_AREA_SIZE

 

65536

 

SORT_READ_FAC

 

5

 

SORT_SPACEMAP_SIZE

 

512

 

USER_DUMP_DEST

 

$ORACLE_BASE/admin/sid/udump

 

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 region is specified by the LINUX parameter SHMMAX. An SGA that is 2048 KB can use four shared memory regions of 512 KB each.

If the size of the SGA exceeds the maximum size of a shared memory segment (SHMMAX), Oracle8 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.


Note:

Intimate Shared Memory (ISM) may cause problems when SHMMAX is smaller than the database SGA size.

 

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

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. As a guideline, the total of all instance's SGA sizes should be no more than one-third of the total physical RAM.

Calculating the Size of the SGA

The approximate size of an instance's SGA can be calculated with this formula:

(DB_BLOCK_BUFFERS \xb0 DB_BLOCK_SIZE)
+ SORT_AREA_SIZE
+ SHARED_POOL_SIZE
+ 1MB

To display the size of the SGA for a running database in bytes, use the Server manager show sga command. This command displays the size of the SGA in bytes.

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 (in the resulting tstshm display, the lines "Lowest shared memory address" and "Highest shared memory address" indicate the valid range):
    $ tstshm
    


    Note:

    The system may experience problems when executing tstshm while using Intimate Shared Memory (ISM).

     

  1. Check the "Segment boundaries" output of tstshm to determine the valid virtual address boundaries at which a shared memory segment can be attached.
  2. Determine the size of your SGA. SGA size is displayed next to the heading Total System Global Area when your database system starts.
  3. Move to the $ORACLE_HOME/rdbms/lib directory, and run genksms to generate the file ksms.s:
    $ cd $ORACLE_HOME/rdbms/lib
    $ $ORACLE_HOME/bin/genksms -b sgabeg > ksms.s

where sgabeg is the starting address of the SGA (which defaults to 0x80000000), and should fall within the range determined in step 2.

  1. Shut down the existing Oracle database.
  2. Rebuild the oracle executable in the $ORACLE_HOME/rdbms/lib directory:
    $ make -f ins_rdbms.mk ksms.o
    $ make -f ins_rdbms.mk ioracle

Using ioracle:

  • backs up the old executable (oracle0)
  • assigns the correct privileges to the new oracle executable
  • moves the new executable into the $ORACLE_HOME/bin directory

The result is a new Oracle kernel that loads the SGA at the address specified by sgabeg.

genksms -b

The genksms -b utility is used to adjust the starting point of fixed SGA.

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. The following section describes special user accounts. LINUX accounts are described in Table 2-7, Oracle server accounts are described in Table 2-8.

Table 2-7 LINUX Accounts

oracle

 

The oracle software owner represents the account that owns the Oracle8 software. This maintenance account requires DBA privileges in order to CREATE, STARTUP, SHUTDOWN, and CONNECT as INTERNAL to the database. The oracle software owner must never be the superuser.

 

root

 

The root user is a special LINUX account with maximum privileges (called superuser privileges). This account is used to configure the LINUX kernel, configure and install networking software, and create user accounts and groups.

 
Table 2-8 Oracle Server Accounts

SYS

 

This is a standard Oracle8 account with DBA privileges automatically created during installation. The SYS account owns all the base tables for the data dictionary. This account is used by the DBA.

 

SYSTEM

 

This account is also a standard Oracle8 account, with DBA privileges automatically created during installation. Additional tables or views can be created by the SYSTEM user. DBAs may log in as SYSTEM to monitor or maintain databases.

 

.

Table 2-9 Special Group Accounts

dba group

 

The oracle software owner is the only required member of the dba group. You can add the root user, or any other LINUX user, to the dba group. Members of this group have access to Server Manager specially privileged functions. If your account is not a member of the dba group, you must enter a password in order to connect as INTERNAL or gain access to the other administrative functions of Server Manager. The default group ID is dba.

 

oper group

 

This is an optional LINUX group you can create. Members have database OPERATOR privileges. OPERATOR privileges are a restricted set of dba privileges.

 

root group

 

Only the root user should be a member of the root group.

 

Managing Security

Oracle8 uses several features of the LINUX 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 Oracle8 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 on an Oracle8 database, create user groups at the operating system level. Groups are controlled by the LINUX file /etc/group. Oracle programs are divided into two sets for security purposes: those executable by all (other, in LINUX terms), and those executable by DBAs only. A recommended approach to security is:

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 change this group name, the Oracle Installer relinks the kernel automatically during Installation. 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. These restrictions do not apply to the group name for ordinary users (known as the oracle group).


WARNING:

Even though both the oracle software owner and root user should belong to the dba group, the oracle software owner should not be a member of the root group. The root user should be the only member of the root group.

 

Security for Oracle Server Utilities

Protect the Oracle8 executables from unauthorized use. The method you use depends on your environment and whether you use single-task utilities. These are suggestions for protecting Oracle8 executables:

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:

Security for Database Files

The user ID used to install Oracle8 should own the database files. The default user ID is the oracle software owner. Set the authorizations on these files to permission 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. To set this bit, enter:

$ chmod 6751 $ORACLE_HOME/bin/oracle

This sets the authorization for the oracle program to:

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

Setting the User ID

The Oracle Installer automatically sets the user ID. 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.

Network Security

Using Passwords on the Network

Remote users on the network can enter their passwords in clear or encrypted text. When you use clear text, passwords can be picked up by unauthorized users, resulting in a breach of security. Oracle Net8 supports encrypted passwords.

DBA Privileges Over the Network

To control DBA privileges over the network choose one of the following options:

Automatic (ops$) Logins

Oracle8 supports automatic logins (operating system authorized logins) over the network.

LINUX treats a dollar sign ($) as the beginning of an environment variable. Therefore, when you specify an operating system authorized (ops$) login on the command line or in a script, first escape the $ with a backslash (\). For example, user ID scott should specify ops\$scott when logging in remotely.

Automatic logins are not allowed for the root user ID.


Note:

Automatic logins by PC, Apple MacIntosh, and OS/2 users are not secure. Anyone can edit the Oracle configuration file and change their user ID. For security reasons, if users of these systems are logging in over the network, Oracle Corporation strongly recommends you disable the ops$ logins in the listener.ora file.

 

Enabling Automatic Logins for Oracle Net8

Automatic and remote DBA logins are not controlled by Oracle Net8. They are controlled by the Oracle8 Server and configured using parameters in the initsid.ora file. Although automatic logins are supported, they are disabled by default. To enable them, set the REMOTE_OS_AUTHENT initialization parameter to true, then start up the database.

Because oracle controls these logins, it is not necessary to run the Oracle Net8 listener as setuid to root.

See Also:

Configuring Oracle Net8 is described in Chapter 6.

 

To perform an automatic login with Oracle Net8, create a user called daemon in your /etc/passwd file. The daemon user must not have an ops$ account in any of the local databases, nor be in any of the DBA groups. That is, there should be no ops$daemon account that would allow an outside user to intrude into your local database.

DBA Group ID Keywords

Table 2-10 describes the keywords used in the /var/opt/oracle/listener.ora file to enable and control remote logins:

Table 2-10 Keywords Used to Control Remote Logins

DBA_GROUP

 

Use this keyword if the name is constant for all instances serviced by the listener.

 

DBA_GROUP_sid

 

Use this keyword for each ORACLE_SID if the listener services more than one $ORACLE_HOME, and the group IDs are different.

 

OPS_DOLLAR_LOGIN_ALLOWED
OPS_DOLLAR_LOGIN_DENIED

 

Use these keywords to control remote login. OPS_DOLLAR_LOGIN_DENIED is the default.

 

REMOTE_DBA_OPS_ALLOWED
REMOTE_DBA_OPS_DENIED

 

Use these keywords to control remote DBA access. REMOTE_DBA_OPS_DENIED is the default.

 

If the DBA group ID for the database accessed is not the default name (dba), you can specify a non-default name.

Set remote login and remote DBA access parameters to the individual ORACLE_SIDs of databases on the network, or specify all sids at once. For example, either of the following statements are valid:

PARAMETER=ALL_SIDS
PARAMETER=sid1[, sidn...]

To see which privileges are assigned to the sids, enter:

$ lsnrctl status

Checking Order

The system checks remote login parameters in the following order:

  1. parameters that deny access
  1. parameters that permit access
  2. the default value (denied)

These privileges are implemented by manipulating the user ID and group ID of the shadow process forked by the Oracle Net8 listener. For example:

Security and Remote Passwords

You can access or administer a database from a remote machine, such as a personal computer, without operating system accounts. User validation is accomplished by using an Oracle8 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=$ORACLE_HOME/dbs/orapwsid password=password entries=max_users

This syntax is described in Table 2-11:

Table 2-11 Syntax for Executing orapwd

file

 

is the name of the file where password information is written. The name of the file must be orapwsid, and you must supply the full pathname. Its contents are encrypted and not user-readable. This parameter is mandatory.

 

password

 

is the initial password you selected for INTERNAL and SYS. You can change this password after you create the database using an ALTER USER statement. This parameter is mandatory.

 

entries

 

is the maximum number of users allowed to connect to the database as SYSDBA or SYSOPER. This parameter is mandatory only if you want this password file to be EXCLUSIVE.

 


Note:

You must create a new password file if you ever need to increase the maximum number of users. Therefore, set max_users to a higher number than you expect to require.

 

orapwd Example

$ orapwd file=/u01/app/oracle/product/8.0.5/dbs/orapwV805 \
password=manager entries=30

See Also:

Oracle8 Server Administrator's Guide.

 

Shared Password File for Multiple Databases

The default password file /dbs/orapwd should be used when the initialization parameter REMOTE_LOGIN_ PASSWORDFILE is set to SHARED for multiple databases. There is no sid specific password file for multiple databases.

Access to a Database from a Remote PC

When there is an Oracle8 password file, networked PC users can access this database as INTERNAL. Non-privileged users can connect to the database by invoking an Oracle application that uses the database. Privileged users who want to perform DBA functions on the database can enter the appropriate Server Manager command from their PC, adding the dba user password. For example:

SVRMGR> connect internal/dba_password

To connect as OPERATOR, use the same command with the OPERATOR password.

Remote Authentication

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

Table 2-12 Parameters For Controlling Remote Connections

REMOTE_OS_AUTHENT

 

enables or disables ops$ connection

 

OS_AUTHENT_PREFIX

 

used by ops$ accounts

 

REMOTE_OS_ROLES

 

enables or disables roles through remote connections

 


Note:

If REMOTE_OS_AUTHENT is set to true, users who are members of the dba group on the remote machine are able to connect as INTERNAL without a password.

 

User-Visible Effects of the Shutdown Mechanism

Clients connected to an Oracle instance while a shutdown takes place will receive one of the following error messages upon subsequent SQL operations.

ORA-03113: end-of-file on communication channel
ORA-12571: TNS:packet writer failure

Administering Login Home Directories

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

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:

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


Note:

Local general-purpose utilities such as these should be stored in the /var/opt/bin directory.

 

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

Building and Running Demonstrations

Loading PL/SQL Demonstrations

PL/SQL includes a number of sample programs you can load. Demonstration and message files are in the rdbms directory. Perform these steps with the Oracle8 Server open and mounted:

  1. Invoke Server Manager and connect with the user/password scott/tiger:
    $ cd $ORACLE_HOME/plsql/demo
    $ svrmgrl
    SVRMGR > connect scott/tiger
  1. To load the demonstrations, invoke exampbld.sql from Server Manager:
    SVRMGR > @exampbld


    Note:

    Build the demonstrations under any Oracle account with sufficient permissions. Run the demonstrations under the same account you used to build them.

     

Running PL/SQL Demonstrations

Table 2-13 lists the kernel demonstrations.

Table 2-13 Kernel Demonstrations
 

examp1.sql

 

examp5.sql

 

examp11.sql

 

sample1.sql

 

examp2.sql

 

examp6.sql

 

examp12.sql

 

sample2.sql

 

examp3.sql

 

examp7.sql

 

examp13.sql

 

sample3.sql

 

examp4.sql

 

examp8.sql

 

examp14.sql

 

sample4.sql

 

extproc.sql

 

 

 

 

Table 2-14 lists the precompiler demonstrations.

Table 2-14 Precompiler Demonstrations
 

examp9.pc

 

examp10.pc

 

sample5.pc

 

sample6.pc

 

To run the kernel PL/SQL demonstrations, invoke SQL*Plus to connect to the kernel, using the same user/password you used to create the demonstrations. Start the demonstration by typing an "at" sign (@) or the word start before the demonstration name. For example, to start the examp1 demonstration, enter:

$ sqlplus scott/tiger
SQLPLUS > @examp1

To build the precompiler PL/SQL demonstrations, enter:

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

If you want to build a single demonstration, enter its name as the argument in the make command. For example, to make the examp9.pc executable, enter:

$ make  -f demo_plsql.mk examp9

To start the examp9 demonstration from your current shell, enter:

$ examp9

In order to run the extproc demo, you first have to add the following line to the file, tnsnames.ora:

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


and the following line to the file, listener.ora:

SC=(SID_NAME=extproc)(ORACLE_HOME=/vobs/oracle)(PROGRAM=extproc))

then from your current shell, enter:

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

Then finally, to run the tests:

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

SQL*Loader Demonstrations

SQL*Loader demonstrations require that:

To create and run a demonstration:

  1. Connect to the database as the user/password scott/tiger from Server Manager (line mode).
  1. Run the ulcasen.sql corresponding to the demonstration you want to run.
  2. As scott/tiger, invoke the demonstration from the command line:
    $ sqlldr scott/tiger ulcasen

As scott/tiger, run the SQL*Loader demonstrations in the following order:

Administering SQL*Loader

Oracle8 Server incorporates SQL*Loader functionality. Demonstration and message files are in the rdbms directory.

File Processing Option

The SQL*Loader release 1.1 control file includes the following additional file processing option strings, the default being str, which takes no argument:

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

(the default) specifies a stream of records, each terminated by a newline character, which are read in one record at a time.

fix

indicates that the file consists of fixed-length records, each of which is n bytes long, where n is an integer value.

var

indicates that the file consists of variable-length records, each of which is n bytes long, where n is an integer value specified in the first five characters of the record.

If the file processing options are not selected, the information is processed by default as a stream of records (str). You might find that fix mode yields faster performance than the default str mode because it does not need to scan for record terminators.

Newlines in Fixed Length Records

When using the fix option to read a file containing fixed-length records, where each record is terminated by a newline, include the length of the newline (one character) when specifying the record length to SQL *Loader.

For example, to read the following file:

AAA newline
BBB newline
CCC newline

specify fix 4 instead of fix 3 to account for the additional newline character.

If you do not terminate the last record in a file of fixed records with a newline character, do not terminate the other records with a newline character either. Similarly, if you terminate the last record with a newline, terminate all records with a newline.


WARNING:

Certain text editors, such as vi, automatically terminate the last record of a file with a newline character. This leads to inconsistencies if the other records in the file are not terminated with newline characters.

 

Removing Newlines

Use the position(x:y) function in the control file to discard the newlines from fixed length records rather than loading them. To do this, enter the following in your control file:

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

When this is done, newlines are discarded because they are in the fourth position in each fixed-length record.

Oracle Security Server

See Also:

For information on the Oracle Security Server, see the Oracle Security Server Guide.

 

Oracle8 Server SQL Reference

CREATE CONTROLFILE Parameters

Use the parameter values in Table 2-15 to determine the size of control files for a database.

Table 2-15 Determining the Size of Control Files
Parameter   Default Value   Maximum Value  
MAXDATAFILES   30   65534  
MAXINSTANCES   1   63  
MAXLOGFILES   16   255  
MAXLOGMEMBERS   2   5  
MAXLOGHISTORY   100   65534  





Prev

Next
Oracle
Copyright © 1999 Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index