Oracle8i Administrator's Reference
Release 3 (8.1.7) 64 Bit for SGI IRIX

Part Number A87435_01
Go To Documentation Library
Library
Go To Product List
Product
Go To Table Of Contents
Contents
Go To Index
Index

Go to previous page Go to next page

1
Administering Oracle8i

This chapter describes tasks specific to administering Oracle8i on SGI IRIX. It contains the following sections:

Overview

You must set Oracle8i environment variables, parameters, memory, and user settings for Oracle8i to work. This chapter describes the various settings for Oracle8i on SGI IRIX.

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

ALTER TABLESPACE TEMP ADD DATAFILE '?/DBS/DBS2.DBF' SIZE 2M

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

ALTER TABLESPACE TABLESPACE_NAME ADD DATAFILE DBS2@DBF 

Environment Variables

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

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, enter:

$ env | grep ORACLE_SID


Note:

Use the env command to show the value of environment variables that have been exported to the environment. Bourne shell and Korn shell can set values without exporting them. 


You must define some of these variables before installing Oracle8i. The required variable settings are listed in the Oracle8i Installation Guide Release 3 (8.1.7) 64 Bit for SGI IRIX.

Oracle8i Environment Variables

Table 1-1 provides the syntax for, and examples of, environment variables used by Oracle8i.


Note:

Do not define environment variables with values that are identical to names of Oracle Server processes, for example: arch, pmon, and dbwr. 


Table 1-1 Oracle8i Environment Variables on UNIX  
Variable  Detail  Definition 

EPC_DISABLED 

Function 

Disables Oracle Trace. 

 

Syntax 

[true | false] 

NLS_LANG 

Function 

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

See the Oracle8i National Language Support Guide for a list of values.  

Syntax 

language_territory.characterset 

Example 

french_france.we8dec 

ORA_NLS33 

Function 

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

Syntax 

directory_path 

Example 

$ORACLE_HOME/ocommon/nls/admin/data 

ORACLE_BASE 

Function 

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

Syntax 

directory_path 

Example 

/u01/app/oracle 

ORACLE_HOME 

Function 

Specifies the directory containing the Oracle software. 

Syntax 

directory_path 

Example 

$ORACLE_BASE/product/8.1.7 

ORACLE_PATH 

Function 

Specifies the search path for files used by Oracle applications, such as *.sql (SQL*Plus), *.frm (Oracle Forms), and *.rpt (Oracle Reports). If the full path to the file is not specified, or is not in the current directory, the Oracle application uses ORACLE_PATH to locate the file. 

Syntax 

Colon-separated list of directories directory:directory:directory 

Example 

/u01/oracle/adhoc/8.1.7/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 characters that must begin with a letter. Oracle Corporation recommends a maximum of eight characters. For more information, see the Oracle8i Installation Guide Release 3 (8.1.7) 64 Bit for SGI IRIX.  

Example 

SAL1 

ORACLE_TRACE 

Function 

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

Range of Values 

T or not T. 

ORAENV_ASK 

Function 

Controls whether coraenv or oraenv prompt for the value of the ORACLE_SID or ORACLE_HOME environment variables. If set to NO, they do not prompt; otherwise they do. 

Syntax 

string 

Range of Values 

NO or not 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 directory:directory:directory 

 

Example 

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

TNS_ADMIN 

Function 

Specifies the directory containing the Net8 configuration files.  

Syntax 

directory_path 

Range of Values 

Any directory; for more information, see the Oracle8i Installation Guide Release 3 (8.1.7) 64 Bit for SGI IRIX. 

Example 

$ORACLE_HOME/network/admin 

TWO_TASK 

Function 

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

Syntax 

Any available network alias. 

Range of Values 

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

Example 

PRODDB_TCP 

UNIX Environment Variables

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

Table 1-2 UNIX Environment Variables Used with Oracle8i  
Variable  Detail  Definition 

CLASSPATH 

Function 

Used for Java applications. This variable differs with each Java application. See the product documentation for your Java application for more information. 

 

Syntax 

directory_path 

 

Example 

There is no default setting. CLASSPATH must include the JRE_Location, $ORACLE_HOME/product/jlib directory where JRE_Location is defined as $ORACLE_HOME/JRE

DISPLAY 

Function 

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

Syntax 

hostname:display where the hostname is your system name (either IP address or alias); display is the monitor number. If you have a single monitor, the number is 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 the Oracle8i Installation Guide Release 3 (8.1.7) 64 Bit for SGI IRIX.  

LPDEST 

Function 

Specifies the user's default printer.  

Syntax 

printer_name 

Example 

doc_printer 

LD_LIBRARY64_PATH 

Function 

Specifies a search path for 64-bit shared libraries. Set LD_LIBRARY64_PATH to include the $ORACLE_HOME/lib and $ORACLE_HOME/javavm/admin directories. This environment variable is required if you are using Oracle products that use 64-bit shared libraries. 

 

Syntax 

Colon-separated list of directories directory:directory:directory 

 

Example 

/usr/dt/lib:$ORACLE_HOME/lib:$ORACLE_HOME/javavm/admin 

LD_LIBRARYN32_PATH 

Function 

Specifies a search path for 32-bit shared libraries. Set LD_LIBRARYN32_PATH to include the $ORACLE_HOME/lib32 directory. This environment variable is required if you are using Oracle products that use 32-bit shared libraries. 

 

Syntax 

Colon-separated list of directories directory:directory:directory 

 

Example 

/usr/dt/lib:$ORACLE_HOME/lib32 

LD_LIBRARY_PATH 

Function 

Used by the shared library loader (ld.so.1) at runtime to find shared object libraries. See the ld.so.1 man pages 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; must 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. 

SGI_ABI 

Function 

Ensures that the compiler and linker run in 64-bit mode whenever you compile or relink. 

 

Syntax 

String (shell variable) 

 

Example 

-64 

PRINTER 

Function 

Selects the default printer. 

 

Syntax 

printer_name 

 

Example 

doc_printer 

SHELL  

Function 

Specifies the command interpreter used during a HOST command. 

 

Syntax 

shell_path 

 

Range of Values 

/bin/sh or /bin/csh or /bin/ksh or any other command interpreter supplied with SGI IRIX. 

 

Example 

/bin/sh 

SRCHOME 

Function 

Specifies the source directory for the installation. SRCHOME should be undefined when you run Oracle Universal Installer. If SRCHOME is defined, Oracle Universal Installer defaults to the location it specifies as the source of software to install. 

 

Syntax 

directory_path 

 

Example 

$ORACLE_HOME 

TERM 

Function 

Used by Oracle Toolkit II character mode tools and other UNIX tools to determine terminal types.  

 

Example 

vt100 

THREADS_FLAG 

Function 

Set the THREADS_FLAG environment variable to native. The default is green. SGI IRIX does not support mixing native and green threads. 

 

Syntax 

String 

 

Example 

$THREADS_FLAG= native 

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 a Common Environment

The following section describes how to set a common UNIX environment.

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:

You might find yourself frequently adding and removing databases from your development system or your users might be switching between several different Oracle databases installed on the same system. With oraenv each user shell startup file profile calls the oraenv command file.


Note:

The C shell uses the coraenv command instead of the oraenv command.  


Local bin Directory

Place oraenv (or coraenv) and dbhome scripts in a local bin directory, separate from the Oracle software home directory, to ensure that these files are accessible to all users. Doing this also ensures that the oraenv script continues to work even if you change the path to specify a different Oracle home directory. The local bin directory is specified by the root.sh script, which you run after you install Oracle8i. The default location for the local bin directory on SGI IRIX is /usr/local/bin.

Switching Between Databases

To switch from one database or database instance to another, call the oraenv routine. Reply to the prompt with the value of the ORACLE_SID environment variable of the database to which you are switching. Always provide the full path of the oraenv command file. For example:

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

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

Use the env command to show the environment variable values that have been exported to the environment. The Bourne shell and Korn shell can set values without exporting them.

For the Bourne or Korn shell, enter:

$ ORACLE_SID=test
$ export ORACLE_SID

For the C shell, enter:

% setenv ORACLE_SID test

In the preceding example, test is the value of the ORACLE_SID environment variable.

Setting the System Time

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 system date setting.

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

Product Executables

You can manually relink your product executables using a relink shell script located in the $ORACLE_HOME/bin directory. Relinking is necessary after applying any operating system patches or after an operating system upgrade.

The relink script manually relinks Oracle product executables depending on the products that have been installed in the Oracle home directory.

To relink product executables, enter the following command, where parameter is one of the values listed in Table 1-3:

$ relink parameter

Table 1-3 Relink Script Parameters  
Value  Description 

all 

Every product executable that has been installed 

oracle 

Oracle database executable only 

network 

net_client, net_server, nau, cman, cnames 

client 

net_client, otrace, plsql, client_sharedlib 

interMedia 

ctx, ordimg, ordaud, ordvir, md 

precomp 

All precompilers that have been installed 

utilities 

Utilities 

oemagent 

oemagent, odg 


Note:

Shut down Oracle Intelligent Agent and other Oracle programs in this Oracle home directory before relinking the database executable. 


System Global Area

The System Global Area (SGA) is the Oracle structure that is stored 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.

The maximum size of a single shared memory segment is specified by the SHMMAX parameter. For example, if SHMMAX is 132 MB and the SGA is 528 MB, the SGA requires four shared memory segments. This setting is located in the /var/sysgen/stune directory. The recommended value for SHMMAX is 4,294,967,296 regardless of the actual memory installed on the system.

If the size of the SGA exceeds the maximum size of a shared memory segment (SHMMAX), the value of the SHMSEG parameter is the maximum number of segments that can be attached by a process.


Note:

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


Set the following initialization file parameters to control the size of the SGA:

Use caution when setting values for these parameters. When values are set too high, too much of the computer'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 the following ways:

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

To relocate the SGA, perform the following steps:

  1. Enter the following command:

    $ tstshm
    
    
    

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

  2. Check the "segment boundaries" output of the tstshm command to determine the valid virtual address boundaries at which a shared memory segment can be attached.

  3. Change directory to the $ORACLE_HOME/rdbms/lib directory and enter the following command to generate the ksms.s file:

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

    In this example, sgabeg is the starting address of the SGA (which defaults to 0x20000000). It should fall within the range determined in step 2.

    Never set the starting address below 0x01000000. On most systems, this leaves approximately 7 MB for data segments. This amount must allow enough memory for the SORT_AREA_SIZE parameter and similar items.

    With a start address of 0x1000000 you can achieve an overall SGA size of about 3.5 GB.

    You might receive the following error messages if you reduced the value of the starting address:

    • ORA-4030: out of process memory when trying to allocate %s bytes (%s,%s)

    • ORA-7324: smpall: malloc error while allocating sga.

    If you receive one of these messages, then you probably lowered the starting address into an area which the SGA needs. Increase the starting address, and try again.

  4. Shut down the existing Oracle instance.

  5. Enter the following command to 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 the ioracle target:

    • 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 when generating the ksms.s file in step 3.

    See Also::

    For more information on how the use of Java in the database affects SGA calculations, see the README file in the $ORACLE_HOME/javavm/doc directory. 

Oracle8i Memory Requirements and Usage

Calculate memory usage requirements to determine the number of users that the system can support. This also helps to determine the physical memory and swap space requirements.

  1. To calculate the memory requirements, use the following formula:

size of the Oracle executable text
+ size of the SGA
+ n * ( size of tool executables private data section
+ size of the Oracle executables uninitialized data section
+ 8192 bytes for the stack
+ 2048 bytes for the processes user area) size of the Oracle executable data section
+ size of the Oracle executables uninitialized data section
+ 8192 bytes for the stack
+ 2048 bytes for processes user area
+ cursor area required for the application
  1. Use the size command to estimate an executable's text size, private data section size, and uninitialized data section size (or DSS).

    Program text is counted only once, no matter how many times the program is run, because the Oracle executable text is shared.

Server Resource Limits

SGI IRIX 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 SGI IRIX 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 SGI IRIX system manager.

Disk quotas established for the oracle user can hinder the operation of the Oracle8i system. Confer with the SGI IRIX system manager before establishing disk quotas.

Database Limits

Table 1-4 lists the maximum and default values for parameters in a CREATE DATABASE or CREATE CONTROLFILE statement.


Note:

Interdependencies between these parameters can affect allowable values. 


Table 1-4 Determining the Size of Control Files  
Parameter  Default Value  Maximum Value 

MAXDATAFILES 

30 

3000 

MAXINSTANCES 

63 

MAXLOGFILES 

16 

255 

MAXLOGHISTORY 

100 

65534 

MAXLOGMEMBERS 

Table 1-5 Oracle-Specific File Size Limits  
File Type  Maximum Size 

Datafiles where DB_BLOCK_SIZE is 2048 

8,589,932,544 

Datafiles where DB_BLOCK_SIZE is 4096 

17,179,865,088 

Datafiles where DB_BLOCK_SIZE is 8192 

34,359,730,176 

Datafiles where DB_BLOCK_SIZE is 16384 

68,719,460,352 

Datafiles where DB_BLOCK_SIZE is 32768 

137,438,920,704 

Special Accounts and Groups

Table 1-6 describes the special UNIX accounts required by the Oracle server.

Table 1-6 Special UNIX Accounts  
Account  Description 

oracle 

The Oracle software owner represents the account that owns the Oracle8i software. This maintenance account requires DBA privileges to CREATE, STARTUP, SHUTDOWN, and CONNECT to the database as the INTERNAL user. The Oracle software owner must never be the root user. 

root 

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

Table 1-7 describes the special Oracle Server accounts required by the Oracle server.

Table 1-7 Oracle Server Accounts  
Account  Description 

SYS 

This is a standard Oracle8i 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 is a standard Oracle8i 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 1-8 describes the special group accounts required by the Oracle server.

Table 1-8 Special Group Accounts  
Group  Description 

dba 

The Oracle software owner is the only required member of the dba group. You can add any other UNIX user to the dba group. Members of this group have access to SQL*Plus specially privileged functions. If your account is not a member of the dba group, you must enter a password to connect as INTERNAL or gain access to the other administrative functions of SQL*Plus. The default OSDBA group is dba

oinstall 

All users installing Oracle8i in any Oracle home directory must belong to the same UNIX group. The Oracle Universal Installer inventory is shared by all Oracle home directories on a system and is group writable. Oracle recommends installing with oinstall as the primary group. 

oper 

This is an optional UNIX group. Members have database OPERATOR privileges. OPERATOR privileges are a restricted set of DBA privileges.  

root 

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

Security

Oracle8i uses 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 in the Oracle program.

See Also:

For more information on security issues, see the Oracle8i Administrator's Guide

Groups and Security

Oracle programs are divided into two sets for security purposes: those executable by all users (other, in UNIX terms), and those executable by DBAs only. To improve security, Oracle Corporation recommends:

Security for Server Manager Commands

Oracle Corporation recommends that you restrict access to Server Manager. Only the Oracle software owner and dba group members should have access to the system privileges for STARTUP, SHUTDOWN, and CONNECT AS INTERNAL.


Caution:

System-privileged statements can damage your database if used incorrectly. Note that users who are not in the dba group can connect as INTERNAL if they have the password. 


Security for Database Files

The user ID used to install Oracle8i owns the database files. The default user ID is oracle. Set the authorizations on these files to read/write by owner, and read-only 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 permissions of the Oracle executable to:

-rwsr-s--x 1 oracle dba 

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

If you must manually set the permissions on the Oracle executable, enter:

$ chmod 6751 $ORACLE_HOME/bin/oracle

Remote Passwords

You can administer a database from a remote computer such as a PC without having an operating system account. In this case, users are validated 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 located in the $ORACLE_HOME/dbs directory and contain the user name and password information for a single database. If there are multiple Oracle home directories on a system, each has a separate password file. To enable the database to use the password file, set the initialization file parameter REMOTE_LOGIN_PASSWORDFILE to EXCLUSIVE.

Remote Authentication

The initialization file parameters shown in Table 1-9 control the behavior of remote connections through non-secure protocols:

Table 1-9 Parameters for Controlling Remote Connections  
Parameter  Description 

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 

See Also:

For information on resource limits, see getrlimit(2) in your operating system documentation. 

Running the orapwd Utility

The orapwd utility, in the $ORACLE_HOME/bin directory, must be run by the Oracle software owner. The syntax of this command is:

$ orapwd file=filename password=password entries=max_users

Table 1-10 describes this syntax:

Table 1-10 Syntax for Executing orapwd  
Variable  Description 

filename 

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 

Initial password you selected for INTERNAL and SYS. Change this password after you create the database using an ALTER USER statement. This parameter is mandatory. 

max_users 

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. Set max_users to a higher number than you expect to require because if you must exceed this value, you must create a new password file. 

Example of the orapwd Utility

The following is an example of the orapwd utility:

$ orapwd file=/u01/app/oracle/product/8.1.7/dbs/orapwV817
password=V817pw entries=30

Customizing the initsid.ora File

The default initsid.ora file is provided with the Oracle8i software. The Oracle Universal Installer creates it in the $ORACLE_BASE/admin/sid/pfile directory. You can modify or customize the Oracle8i installation. A sample of the initsid.ora file is located in the $ORACLE_HOME/dbs directory.

Table 1-11 lists default initialization parameter values on SGI IRIX. 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.

Use the SQL*Plus command SHOW PARAMETERS to display the current values of these parameters on the system.

Table 1-11 Initialization Parameters  
Parameter  Default Value  Range of Values 

BACKGROUND_DUMP_DEST 

?/rdbms/log 

Valid directory name 

BITMAP_MERGE_AREA_SIZE 

1048576 

65536 to unlimited 

COMMIT_POINT_STRENGTH 

0 to 255 

CONTROL_FILES 

?/dbs/cntrloracle_sid.dbf 

Valid file names 

CREATE_BITMAP_AREA_SIZE 

8388608 

65536 to unlimited 

DB_BLOCK_BUFFERS 

100 MB of buffers 

4 MB to unlimited 

DB_BLOCK_SIZE 

2048 

2 KB to 16 KB 

DB_FILES 

200 

1 to 2000000 

DB_FILE_DIRECT_IO_COUNT 

64 

0 to 1048576/block size 

DB_FILE_MULTIBLOCK_READ_COUNT 

1 to min(DB_BLOCK_BUFFERS/4, 1048576/DB_BLOCK_SIZE) 

DISTRIBUTED_TRANSACTIONS 

1/4 TRANSACTIONS 

0 to unlimited 

HASH_AREA_SIZE 

2*SORT_AREA_SIZE 

0 to unlimited 

HASH_MULTIBLOCK_IO_COUNT 

0 (self-tuned) 

0 to min(127, DB_BLOCK_BUFFERS/4, 1048576/DB_BLOCK_SIZE) 

JAVA_POOL_SIZE 

20000000 

1000000 to 1000000000 

LOCK_SGA 

FALSE 

TRUE, FALSE 

LOG_ARCHIVE_DEST 

null 

Valid directory names 

LOG_ARCHIVE_FORMAT 

"%t_%s.dbf" 

Valid file names 

LOG_BUFFER 

512KB or 128KB multiplied by the number of systems, whichever is larger 

66560 to unlimited 

LOG_CHECKPOINT_INTERVAL 

0 to unlimited 

MTS_MAX_DISPATCHERS 

The value of MTS_DISPATCHERS to the value of PROCESSES 

MTS_MAX_SERVERS 

2*MTS_SERVERS, if MTS_SERVERS > 20, else 20 

The value of MTS_SERVERS to the value of PROCESSES 

MTS_SERVERS 

1, if MTS_DISPATCHERS is specified, else 0 

1 to the value of PROCESSES 

MTS_LISTENER_ADDRESS 

ADDRESS=address  

 

NLS_LANGUAGE 

AMERICAN 

Valid language names 

NLS_TERRITORY 

AMERICA 

Valid territory names 

OBJECT_CACHE_MAX_SIZE_PERCENT 

10 

0 to unlimited 

OBJECT_CACHE_OPTIMAL_SIZE 

102400 

10 KB to unlimited 

OPEN_CURSORS 

50 

1 to unlimited 

OS_AUTHENT_PREFIX 

ops$ 

Arbitrary string 

PROCESSES 

30, if not PARALLEL_AUTOMATIC_TUNING 

6 to unlimited 

SHARED_POOL_SIZE 

64 MB on 64-bit systems, 8 MB on 32-bit systems  

300000 to unlimited 

SORT_AREA_SIZE 

65536 

0 to unlimited 

See Also:

For information on initialization parameters see Oracle8i Server Reference, Oracle8i Administrator's Guide and Oracle8i Tuning

Embedded PL/SQL Gateway

The embedded PL/SQL gateway is a gateway embedded in the Oracle8i server to provide native support for deploying database applications written is PL/SQL on the Web. The embedded PL/SQL gateway is implemented as an Oracle Servlet Engine (OSE) servlet, and relies upon the existence and configuration of both the OSE and mod_ose, the Apache module which supports the OSE. The following instructions provide information on how to install and configure the gateway.

Overview

Two Apache modules, mod_ose and mod_plsql, support web applications developed with PL/SQL.

The mod_ose module acts as a request router for an OSE running within an Oracle8i instance. mod_ose enables stateful OSE applications by routing stateful requests through the middle tier and back to a specified OSE/Oracle8i instance. Because the embedded PL/SQL gateway is implemented as an OSE servlet running in the Oracle8i server, it is able to host stateful, as well as stateless, PL/SQL web applications. A stateful PL/SQL web application is one in which all database session states (for example, package and transaction) is preserved between requests.

The mod_plsql module is the name given to the PL/SQL gateway running within an Apache module in the middle tier server and executing PL/SQL procedures in a backend Oracle server using OCI. mod_plsql currently supports only stateless PL/SQL web applications.

See Also:

For information on developing web applications with PL/SQL, refer to Using mod_plsql which is generic PL/SQL gateway documentation. 

Installing the Embedded PL/SQL Gateway

As with all OSE servlets, the embedded PL/SQL gateway must be loaded and published. To load and publish the embedded PL/SQL gateway servlet:

  1. Enter the following command to load the servlet, connect to SQL*Plus as sys, and run the initplgs.sql SQL script:

    SQL> @rdbms/admin/initplgs.sql
    
    
  2. The name of the embedded PL/SQL gateway servlet is oracle.plsql.web.PLSQLGatewayServlet. To publish the servlet, enter the following command:

    % $ORACLE_HOME/jis/bin/unix/sess_sh -s http://<OSE machine name>:<OSE port \ 
    number -u sys/change_on_install -c "publishservlet -virtualpath \ 
    pls/*/webdomains/contexts/default plsGatewway \ 
    SYS:oracle.plsql.web.PLSQLGatewayServlet" 
    
    
    

    This command publishes the gateway servlet as plsGateway with a default context. The servlet can be accessed using the virtual path /pls. The following example shows a URL that might access a gateway servlet:

    http://dlsun240/pls/dadname/hello_world 
    
    

    See Also:

    For more information on using and publishing servlets, see the Oracle Servlet Engine User's Guide

Configuring Oracle PL/SQL Embedded Gateways

Configuration procedures for configuring the Apache server/mod_ose and the embedded PL/SQL gateway are beyond the scope of this reference.

See Also:

For information on configuring the Apache server/mod_ose, see Oracle Servlet Engine User's Guide

Demonstrations Files

This section describes how to build and run the SQL*Loader and PL/SQL demonstration programs installed with Oracle8i.

SQL*Loader Demonstrations

The following SQL*Loader demonstration files are included with Oracle8i. Run the demonstrations in numerical order:

Table 1-12 SQL*Loader Demonstration Files
ulcase1  ulcase3  ulcase5  ulcase7  ulcase9 

ulcase2 

ulcase4 

ulcase6 

ulcase8 

 

To create and run a demonstration

Run demonstrations while logged in as the user SCOTT/TIGER. Ensure that:

In the following steps, n represents the demonstration number, listed in the previous section. To create and run a demonstration:

  1. Run the ulcasen.sql script corresponding to the demonstration you want to run.

    $ sqlplus SCOTT/TIGER @ulcasen.sql
    
    
  2. Load the demonstration data into the objects:

    $ sqlldr SCOTT/TIGER ulcasen.ctl  
    
    
    • For the ulcase2 demonstration, you do not have to run the ulcase2.sql script.

    • For the ulcase6 demonstration, run the ulcase6.sql script, then enter the following at the command line:

      $ sqlldr SCOTT/TIGER ulcase6 DIRECT=true
      
      
    • For the ulcase7 demonstration, run the ulcase7s.sql script, then enter the following at the command line:

      $ sqlldr SCOTT/TIGER ulcase7
      
      
      

      After running the demonstration, run ulcase7e.sql to drop the insert trigger and global variable package.

Administering SQL*Loader

Oracle8i incorporates SQL*Loader functions. Demonstration and message files are in the rdbms directory.

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

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

String  Description 

str 

Specifies a stream of records, each terminated by a newline character, which are read in one record at a time. This is the default. 

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 you do not select the file processing option, 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 scan for record terminators.

Newline Characters 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 character, include the length of the newline (one character) when specifying the record length to SQL *Loader.

For example, to read the following file specify FIX 4 instead of FIX 3 to account for the additional newline character:

AAA newline
BBB newline
CCC newline

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 character, terminate all records with a newline character.


Caution:

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 Newline Characters

Use the position(x:y) function in the control file to discard the newline characters from fixed length records rather than loading them. For example, enter the following in your control file to discard newline characters from the fourth position:

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

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

PL/SQL Demonstrations

PL/SQL includes a number of sample programs that you can load. Demonstration and message files are in the rdbms directory. The Oracle8i database must be open and mounted to work with the sample programs:

  1. Run SQL*Plus and connect with the user/password SCOTT/TIGER:

    $ cd $ORACLE_HOME/plsql/demo
    $ sqlplus scott/tiger
    
    
  2. To load the demonstrations, run exampbld.sql from SQL*Plus:

    SQL> @exampbld.sql
    


    Note:

    Build the demonstrations in any Oracle account with sufficient permissions. Run the demonstrations in the same account as they were built. 


The following kernel demonstrations are available:

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

To run the PL/SQL demonstrations, run SQL*Plus to connect to the database, using the same user/password 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
SQL> @examp1

To build the precompiler PL/SQL demonstrations, enter:

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

Table 1-13 lists the precompiler demonstrations.

Table 1-13 Precompiler Demonstrations

examp9.pc 

examp10.pc 

sample5.pc 

sample6.pc 

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

$ make  -f demo_plsql.mk examp9

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

$ ./examp9

To run the extproc demonstration, first 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=/u01/app/oracle/product/8.1.7) 
(PROGRAM=extproc))

then from your SQL*Plus session, enter:

SQL> CONNECT SYSTEM/MANAGER
Connected.
SQL> GRANT CREATE LIBRARY TO SCOTT;
Grant succeeded.
SQL> CONNECT SCOTT/TIGER
Connected.
SQL> CREATE LIBRARY DEMOLIB AS
'$ORACLE_HOME/plsql/demo/extproc.so';
Library created.

Finally, to run the demonstrations:

SQL> CONNECT SCOTT/TIGER
Connected.
SQL> @extproc

Database Examples

In the following examples, it is assumed that the local bin directory is /usr/local/bin and the production database is called PROD. In addition, ORAENV_ASK is reset to the default, Yes, after oraenv is executed. This ensures that the system prompts for a different ORACLE_SID environment variable the next time that the oraenv command is executed.


Note:

Set the ORAENV_ASK environment variable to no to not prompt for the ORACLE_SID at startup.  


If you have created a database manually instead of using Oracle Database Configuration Assistant, you must ensure that the system configuration is reflected in the /var/opt/oracle/oratab file.

For each server instance, add an entry in the following format:

ORACLE_SID:ORACLE_HOME:{Y|N}

The values Y or N indicate whether or not you want to activate the dbstart and dbshut scripts. The Oracle Database Configuration Assistant automatically adds an entry for each database it creates.

Example of 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
export ORAENV_ASK
. oraenv
unset 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

Example of Multiple Instances

For multiple database instances, define the sid at startup.

For the Bourne or Korn shell, enter:

#!/usr/bin/sh 
echo "The SIDs on this machine are:" 
cat /var/opt/oracle/oratab | nawk-F: '{print $1}' | grep -v "#" 
ORAENV_ASK="YES" 
.  /usr/local/bin/oraenv 

For the C shell, enter:

#!/usr/bin/csh 
echo "The SIDs on this machine are:" 
cat /var/opt/oracle/oratab | nawk -F: '{print $1}' | grep -v "#" 
set ORAENV_ASK="YES" 
source /usr/local/bin/coraenv

Go to previous page Go to next page
Oracle
Copyright © 2001 Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Library
Go To Product List
Product
Go To Table Of Contents
Contents
Go To Index
Index