Oracle9i Administration

 


 

Oracle9i Environment Variables

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

SQL> 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:

SQL> ALTER TABLESPACE tablespace_name ADD DATAFILE dbsfile@.dbf 

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

$ echo $ORACLE_SID

Other environment variables include...

EPC_DISABLED Function Disables Oracle Trace.

Syntax TRUE |FALSE

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

See the Oracle9i Globalization Support Guide for a list of values.


Syntax language_territory.characterset

Example french_france.we8dec

ORA_NLS33 Function Directory where language, territory, character set, and linguistic definition files are stored.

Syntax directory_path

Example $ORACLE_HOME/ocommon/nls/admin/data

ORA_TZFILE Function Full pathname to the time zone file. Set this environment variable if you want to use a time zone from the large time zone file...

ORACLE_HOME/oracore/zoneinfo/timezlrg.dat

...for data in the database. The large time zone file contains information on more time zones than the default time zone file...

$ORACLE_HOME/oracore/zoneinfo/timezone.dat

All databases that share information must use the same time zone file. You must stop and restart the database when you change the value of this environment variable.


Syntax directory_path

Example $ORACLE_HOME/oracore/zoneinfo/timezlrg.dat

ORACLE_BASE Function Base of the Oracle directory structure for Optimal Flexible Architecture (OFA) compliant databases.

Syntax directory_path

Example /u01/app/oracle

ORACLE_HOME Function Directory containing the Oracle software.

Syntax directory_path

Example $ORACLE_BASE/product/9.0.1

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

Syntax Colon-separated list of directories:

directory1:directory2:directory3

Example /u01/app/oracle/product/9.0.1/bin:.


The period adds the current working directory to the search path.

ORACLE_SID Function Oracle system identifier.

Syntax A string of numbers and letters that must begin with a letter. Oracle Corporation recommends a maximum of eight characters for system identifiers.

Example SAL1

ORACLE_TRACE Function Enables the tracing of shell scripts during an installation. If this environment variable is set to T, many Oracle shell scripts use the set -x command, which prints commands and their arguments as they are run.

Syntax T or not T.

ORAENV_ASK Function Controls whether the coraenv or oraenv script prompts for ORACLE_SID or ORACLE_HOME. If the value is NO, the scripts do not prompt; otherwise they do.

Syntax string

Example NO or not NO.

SQLPATH Function Directory or list of directories that SQL*Plus searches for a login.sql file.

Syntax Colon-separated list of directories:

directory1:directory2:directory3

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

TNS_ADMIN Function Directory containing the Oracle Net configuration files.

Syntax directory_path

Range Any directory.

Example $ORACLE_HOME/network/admin

TWO_TASK Function Default Oracle Net connect string descriptor alias defined in the tnsnames.ora file.

Syntax Any available network alias.

Range Any valid Oracle Net alias defined in the tnsnames.ora file.

Example PRODDB_TCP

 

UNIX Environment Variables Used with Oracle9i

Variable Detail Definition
ADA_PATH Function Directory containing the Ada compiler (Solaris and AIX only).

Syntax directory_path

Example /usr/lpp/powerada
CLASSPATH Function Used with Java applications. The setting for this variable differs with each Java application. See the product documentation for your Java application for more information.

Syntax Colon-separated list of directories:

directory1:directory2:directory3

Example There is no default setting. CLASSPATH must include the following directories:

$ORACLE_HOME/JRE/lib:$ORACLE_HOME/product/jlib
DISPLAY Function Used by X-based tools. Display device used for input and output. See the X Windows documentation of the vendor for details.

Syntax hostname:display
where the hostname is the computer name (either IP address or alias) and 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.

Syntax directory_path

Example /home/oracle
LANG or LANGUAGE Function Language and character set used by the operating system for messages and other output. See the operating system documentation and the Oracle9i Installation Guide Release 1 (9.0.1) for UNIX Systems for more information.
LD_OPTIONS Function Default linker options. See the ld man pages for more information.
LPDEST Function Name of the default printer (Solaris only).

Syntax string

Example docprinter
LDPATH Function Default directories used by the linker to find shared object libraries. See the ld man pages for more information on this environment variable (Solaris only).
LD_LIBRARY_PATH Function List of directories that the shared library loader searches to locate shared object libraries at runtime. See the ld man page for information on this environment variable.

Syntax Colon-separated list of directories:

directory1:directory2:directory3

Example /usr/dt/lib:$ORACLE_HOME/lib
LIBPATH Function List of directories that the shared library loader searches to locate shared object libraries at runtime. See the ld man page for information on this environment variable (AIX only).

Syntax Colon-separated list of directories:

directory1:directory2:directory3

Example /usr/dt/lib:$ORACLE_HOME/lib
PATH Function Used by the shell to locate executable programs; must include the $ORACLE_HOME/bin directory.

Syntax Colon-separated list of directories:

directory1:directory2:directory3

Example /bin:/usr/bin:/usr/local/bin:
/usr/bin/X11:$ORACLE_HOME/bin:$HOME/bin:.
PRINTER Function Defines the name of the default printer.

Syntax string

Example docprinter
SHELL Function Command interpreter used during a host command.

Syntax shell_path

Range /bin/sh, /bin/csh, /bin/ksh, or any other command interpreter supplied with UNIX.

Example /bin/sh
SHLIB_PATH Function List of directories that the shared library loader searches to locate shared object libraries at runtime. See the ld man page for information on this environment variable (HP only).

Syntax Colon-separated list of directories:

directory1:directory2:directory3

Example /usr/dt/lib:$ORACLE_HOME/lib
TMP and TMPDIR Function Default directory for temporary files; if set, tools that create temporary files create them in this directory.

Syntax directory_path

Example /u02/oracle/tmp
XENVIRONMENT Function File containing X-Windows system resource definitions. See your X-Windows documentation for more information.

To prevent conflicts, do not define environment variables with names that are identical to the names of Oracle Server processes, such as: ARCH, PMON, and DBWR.

 

Set a common environment using oraenv

The oraenv (or coraenv) script 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 between Oracle9i databases

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

Place the 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 Oracle9i. The default location for the local bin directory on UNIX is /usr/local/bin.

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

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.

 

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

Oracle Corporation recommends that you do not change your personal TZ value. Using different values of TZ such as GMT+24 might change the date a transaction is recorded. This changed date 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.

 

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

Shut down Oracle Intelligent Agent, and other Oracle programs in this Oracle home directory before relinking executables. 

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:

$ relink parameter

 

Relink Script Parameters  

Value Description
all Every product executable that has been installed
oracle Oracle server 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 All utilities that have been installed
oemagent oemagent, odg

 

System Global Area

The System Global Area (SGA) is the Oracle structure that is located 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 kernel parameter (SHM_MAX on Tru64). The following table shows the recommended value for this parameter, depending on your platform:

Platform Recommended Value
AIX 2,000,000,000 bytes (regardless of the physical memory installed on the system)
HP The size of the physical memory
Solaris and Tru64 4,294,967,296 bytes (regardless of the physical memory installed on the system)
Linux Half the size of the physical memory installed on the system

If the size of the SGA exceeds the maximum size of a shared memory segment (SHMMAX or SHM_MAX), Oracle9i attempts to attach more contiguous segments to fulfill the requested SGA size. The SHMSEG kernel parameter (SHM_SEG on Tru64) specifies the maximum number of segments that can be attached by any process.

On Solaris, Intimate Shared Memory (ISM) can cause problems when the value of the SHMMAX parameter is smaller than the database SGA size. If the SGA is defined over more than one segment, ISM performance is affected. To fix this problem, make sure that the value of the SHMMAX parameter is larger than the database SGA size. 

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

  • DB_BLOCK_BUFFERS
  • DB_BLOCK_SIZE
  • SHARED_POOL_SIZE
  • JAVA_POOL_SIZE

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.

Set the value of the DB_BLOCK_BUFFERS parameter to at least 1024. 

 

Determining the Size of the SGA

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

  • Enter the following SQL*Plus command to display the size of the SGA for a running database:

    SQL> SHOW SGA
    

    The result is shown in bytes.

  • Determine the size of the SGA when you start your database instance. The SGA size is displayed next to the heading Total System Global Area.

 

Intimate Shared Memory (Solaris Only)

On Solaris systems, Oracle9i uses Intimate Shared Memory (ISM) for shared memory segments because it shares virtual memory resources among Oracle processes. On Solaris 2.6 and Solaris 7, Oracle9i uses ISM by default. If you use ISM on Solaris, the physical memory for the entire shared memory segment is automatically locked.

On Solaris 8, dynamic/pageable ISM (DISM) is available. This enables Oracle9i to share virtual memory resources among processes sharing the segment, and at the same time enables memory paging. The operating system does not have to lock down physical memory for the entire shared memory segment. Using DISM causes a small loss in performance compared to using ISM.

Oracle9i automatically decides at startup whether to use ISM or DISM, based on the following criteria:

  • Oracle9i uses DISM if it is available on the system, and if the value of the SGA_MAX_SIZE initialization parameter is larger than the size required for all SGA components combined. This allows Oracle9i to lock only the amount of physical memory that is used.

  • Oracle9i uses ISM if the entire memory segment is in use at startup or if the value of the SGA_MAX_SIZE parameter is smaller than the size required for all SGA components combined.

Regardless of whether Oracle9i uses ISM or DISM, it can resize the dynamic SGA components after it starts an instance. Oracle9i can relinquish memory from one dynamic SGA component and allocate it to another component.

Because shared memory segments are not implicitly locked in memory, when using DISM, Oracle9i explicitly locks shared memory that is currently in use at startup. When a dynamic SGA operation uses more shared memory, Oracle9i explicitly performs a lock operation on the memory that comes in use. When a dynamic SGA operation releases shared memory, Oracle9i explicitly performs an unlock operation on the memory that is freed, so that it becomes available to other applications. Oracle9i uses a new command, oradism, to lock and unlock shared memory.

You must log in as the root user to lock or unlock memory. The Solaris 8 user_attr and exec_attr databases grant appropriate attributes to the user for this purpose. If these attributes are not set correctly, the oradism command fails to perform the lock and unlock operations. If this happens, Oracle9i continues to run, but performance might be degraded because the SGA memory is not locked.

 

Oracle9i Memory Requirements

Calculate the Oracle9i memory requirements to determine the number of users that the system can support. This calculation also helps to determine the physical memory and swap space requirements. To calculate the memory requirements, follow these steps:

Use the size command to determine the size of the text section, data section, and uninitialized data section (or bss) for the oracle executable. The text section size is included only once, because the oracle executable text section is shared.  

  1. Use the following formula to calculate the total memory requirement of the Oracle9i background processes:

    text + SGA + (n * (data + uninitialized_data + 8192 + 2048) )

    The following table describes the variables and values in this formula:

    Value Description
    text Size in bytes of the text section of the oracle executable
    SGA Size in bytes of the SGA
    n Number of Oracle background processes
    data Size in bytes of the data section of the oracle executable
    uninitialized_data Size in bytes of the uninitialized data section (bss) of the oracle executable
    8192 Size in bytes of the stack for the process
    2048 Size in bytes of the user area for the process

    Background process names have the format ora_process_sid, where process is the process name and sid is the value of the ORACLE_SID environment variable. For example, the log writer (LGWR) process for the SAL1 instance is named ora_lgwr_SAL1.

  2. Use the following formula to calculate the additional memory requirement of each Oracle9i shadow process:

    data + uninitialized_data + 8192 + 2048 + cursor_area

    In this formula, cursor_area is the size in bytes of the application cursor area. The other variables and values have the same meaning as in step 1.

    Shadow process names have the format oraclesid, where sid is the value of the ORACLE_SID environment variable.

  3. To estimate the maximum possible memory requirement, multiply the value from step 2 by the maximum number of concurrent shadow processes you expect, then add the value from step 1.

 

Database Limits

Interdependencies among these parameters may affect allowable values. 

 

CREATE CONTROLFILE and CREATE DATABASE Parameters  

Parameter Default Maximum Value
MAXLOGFILES 16 255
MAXLOGMEMBERS 2 5
MAXLOGHISTORY 100 65534
MAXDATAFILES 30 65534
MAXINSTANCES 1 63

 

File Size Limits  

File Type Maximum Size
Datafiles 4,194,303 multiplied by the value of the DB_BLOCK_SIZE parameter
Import/Export file 2,147,483,647
SQL*Loader file 2,147,483,647

 

Operating System Accounts and Groups

Special operating system accounts and groups are required by Oracle9i, as follows:

  • Oracle software owner account
  • OSDBA, OSOPER, and ORAINVENTORY groups

 

Oracle Software Owner Account

The Oracle software owner account, usually named oracle, is the account that you use to install the Oracle software. You can use different Oracle software owner accounts for separate installations of the software. However, use the same account that installed the software for all subsequent maintenance tasks on that installation.

Oracle Corporation recommends that the Oracle software owner has the ORAINVENTORY group as its primary group and the OSDBA group as its secondary group.

 

OSDBA, OSOPER, and ORAINVENTORY Groups

 

UNIX Groups  

Group Typical Name Description
OSDBA dba Operating system accounts that are members of the OSDBA group have special database privileges. Members of this group can connect to the database using the SYSDBA privilege. The Oracle software owner is the only required member of this group. You can add other accounts as required.
OSOPER oper The OSOPER group is an optional group. Operating system accounts that are members of the OSOPER group have special database privileges. Members of this group can connect to the database using the SYSOPER privilege.
ORAINVENTORY oinstall All users installing Oracle software on a UNIX system must belong to the same UNIX group, called the ORAINVENTORY group. This group must be the primary group of the Oracle software owner during installations. After the installation, this group owns all of the Oracle files installed on the system.

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

 

Groups and Security

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

  • The primary group for the oracle account should be the oinstall group.
  • The oracle account must have the dba group as a secondary group.
  • Although any user account which requires dba privileges can belong to the dba group, the only user account which should belong to the oinstall group is the oracle account.

 

Security for Database Files

See the Oracle9i Installation Guide Release 1 (9.0.1) for UNIX Systems for information on the appropriate permissions for database files.

 

External Authentication

If you choose to use external authentication, use the value of the OS_AUTHENT_PREFIX initialization parameter as a prefix for Oracle usernames. If you do not explicitly set this parameter, the default value on UNIX is ops$, which is case sensitive.

To use the same usernames for both operating system and Oracle authentication, set this initialization parameter to a null string, as follows:

OS_AUTHENT_PREFIX=""

 

Running the orapwd Utility

You can use a password file to identify users that can use the SYSDBA and SYSOPER privileges when connecting to the database. To create the password file:

  1. Log in as the Oracle software owner.

  2. Use the $ORACLE_HOME/bin/orapwd utility, which has the following syntax:

    $ orapwd file=filename password=password entries=max_users

Value Description
filename Name of the file where password information is written. The name of the file must be orapwsid, and supply the full pathname. Its contents are encrypted and not user-readable. This parameter is mandatory. The password file is typically created in the $ORACLE_HOME/dbs directory.
password This parameter sets the password for the SYS user. If you use an ALTER USER statement to change the password for the SYS user after you connect to the database, both the password stored in the data dictionary and the password stored in the password file are updated. This parameter is mandatory.
max_users Maximum number of entries that you require the password file to accept.

 

Password Management

For security reasons, the Oracle Database Configuration Assistant locks most Oracle user accounts after it creates the database. It does not lock the SYS, SYSTEM, or SCOTT accounts. You must unlock these accounts and change their passwords before logging in to them. To change the passwords, click the Password Management button in the Oracle Database Configuration Assistant Summary window. Alternatively, use SQL*Plus to connect to the database as SYSDBA and enter the following command:

SQL> ALTER USER username IDENTIFIED BY passwd ACCOUNT UNLOCK;

 

Customizing the Initialization File

The default initialization file (initsid.ora) is provided with the Oracle9i software. The Oracle Universal Installer creates it in the $ORACLE_BASE/admin/sid/pfile directory. A sample initialization file is located in the $ORACLE_HOME/dbs directory.

All Oracle9i 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 SHOW PARAMETERS command in SQL*Plus to display the current values of these parameters on the system.

 

Initialization Parameters  

Parameter Default Range
BACKGROUND_DUMP_DEST ?/rdbms/log Valid directory name
BITMAP_MERGE_AREA_SIZE 1048576 65536 to unlimited
COMMIT_POINT_STRENGTH 1 0 to 255
CONTROL_FILES ?/dbs/cntrlsid.dbf Valid filenames
CREATE_BITMAP_AREA_SIZE 8388608 65536 to unlimited
DB_BLOCK_SIZE 2048 2048 to 16384 (Linux, Solaris)

2048 to 32768 (AIX, HP, Tru64)

DB_CACHE_SIZE 8 MB 8 MB to unlimited
DB_FILES 200 1 to 2000000
DB_FILE_DIRECT_IO_COUNT 64 0 to 1048576/block size
DB_FILE_MULTIBLOCK_READ_COUNT 8 1 to the smaller of the following values:

  • The value of DB_BLOCK_BUFFERS divided by 4
  • 1048576 divided by the value of DB_BLOCK_SIZE
DISTRIBUTED_TRANSACTIONS The value of TRANSACTIONS divided by 4 0 to unlimited
HASH_AREA_SIZE The value of SORT_AREA_SIZE multiplied by 2 0 to unlimited
HASH_MULTIBLOCK_IO_COUNT 0 (self-tuned) 0 to the smallest of the following values:
  • 127
  • The value of DB_BLOCK_BUFFERS divided by 4
  • 1048576 divided by the value of DB_BLOCK_SIZE
JAVA_POOL_SIZE 24 MB 1000000 to 1000000000
LOCK_SGA FALSE TRUE, FALSE
LOG_ARCHIVE_DEST NULL Valid directory names
LOG_ARCHIVE_FORMAT "%t_%s.dbf" Valid filenames
LOG_BUFFER 512 KB or (128 KB multiplied by the value of CPU_COUNT, which ever is higher) 66560 to unlimited
LOG_CHECKPOINT_INTERVAL 0 0 to unlimited
MAX_DISPATCHERS 5 1 to maximum number of processes that can be opened by your operating system.
MAX_SHARED_SERVERS 2 multiplied by the value of SHARED_SERVER, if the value of SHARED_SERVERS is greater than 20, otherwise 20 Between the value of SHARED_SERVERS and the value of PROCESSES
SHARED_SERVERS 1, if DISPATCHERS is specified, else 0 Between 1 and PROCESSES
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 100 KB 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 4194304 to unlimited
SORT_AREA_SIZE 65536 0 to unlimited

 

Embedded PL/SQL Gateway

The embedded PL/SQL gateway is a gateway embedded in the Oracle9i server to provide native support for deploying PL/SQL-based database applications 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 using PL/SQL.

The mod_ose module acts as a request router for an OSE running within an Oracle9i instance. Due to its routing abilities, mod_ose enables stateful OSE applications by routing stateful requests through the middle tier and back to a specified OSE and Oracle9i instance. Because the embedded PL/SQL gateway is implemented as an OSE servlet running in the Oracle9i 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) are preserved between requests.

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

 

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. To load the servlet, connect to SQL*Plus as SYS, and run the following script:

    SQL> @$ORACLE_HOME/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/bin/sess_sh -s http://OSE_host_name:port -u SYS/SYS_passwd \
    -c "publishservlet -virtualpath pls/* /webdomains/contexts/default \ 
    plsGateway  SYS:oracle.plsql.web.PLSQLGatewayServlet" 
    
    

    In the preceding example, SYS_passwd is the password of the Oracle user SYS. The default password is CHANGE_ON_INSTALL.

    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://hostname/pls/dadname/hello_world
    
    

 

Oracle HTTP Server powered by Apache

The Oracle HTTP Server is based on the Apache HTTP Server. Administration tasks for the server require access to the local system on which the server is running, and in some cases, requires root access.

The Oracle HTTP Server starts automatically on the default port 7777 after installation. To verify that the server is running, enter the following command:

$ ps -elf | grep httpd 

 

Starting and Stopping the Oracle HTTP Server

If you modify the configuration, restart the server. You must be logged in as the root user to start the server with SSL enabled.

To stop the server, enter the following commands:

$ cd $ORACLE_HOME/Apache/Apache/bin 
$ su root 
# ./apachectl stop 

To restart the server, enter the following commands:

$ cd $ORACLE_HOME/Apache/Apache/bin 
$ su root 
# ./apachectl {start|startssl} 

Use the start flag to start a non-SSL enabled server or use the startssl flag to start an SSL enabled server. If you start an SSL enabled server, the default ports are 80 and 443.

 

Accessing the Default Initial Static Page

The default initial static page contains links to online documentation for Apache as well as demonstrations for each of the components. To access the initial static page, use an internet browser to view one of the following URLs:

  • For servers without SSL enabled:

    http://ServerName:7777/ 
    
  • For servers with SSL enabled:

    http://ServerName/
    

In the preceding example, ServerName is configured in the Apache server configuration file httpd.conf. To locate the appropriate value in the configuration file, enter:

$ grep ServerName $ORACLE_HOME/Apache/Apache/conf/httpd.conf 

 

Oracle HTTP Server Status

The Oracle HTTP Server provides the following status pages:

http://ServerName/server-status 
http://ServerName/server-info 
http://ServerName/perl-status 

For security reasons, server status is disabled in the default server configuration files. To enable server status, edit the $ORACLE_HOME/Apache/Apache/conf/httpd.conf configuration file.

The Oracle HTTP Server also provides the following Jserv status page, that you can enable by editing the $ORACLE_HOME/Apache/Jserv/etc/conf/jserv.conf configuration file:

http://ServerName/jserv

 

Oracle HTTP Server Log Files

A number of log files are generated by the server. It is important to check them periodically to make sure that the server is working correctly. By default, the error log level is set to warn in the configuration files. You can change the default error level by editing the appropriate configuration file and restarting the server.

The following log files are generated by the server:

$ORACLE_HOME/Apache/Apache/logs/access_log 
$ORACLE_HOME/Apache/Apache/logs/error_log 
$ORACLE_HOME/Apache/Apache/logs/ssl_engine_log 
$ORACLE_HOME/Apache/Jserv/logs/jserv.log 
$ORACLE_HOME/Apache/Jserv/logs/mod_jserv.log 

 

Demonstration Files

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

 

SQL*Loader Demonstrations

The following SQL*Loader demonstration files are included with Oracle9i in the $ORACLE_HOME/rdbms/demo directory. Run the demonstrations in numerical order:

ulcase1 ulcase3 ulcase5 ulcase7
ulcase2 ulcase4 ulcase6

 

To Create and Run a Demonstration

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

  • The user SCOTT/TIGER has CONNECT and RESOURCE privileges

  • The EMP and DEPT tables exist

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 
    

The following list provides additional information on the ulcase2, ulcase6, and ulcase7 demonstrations:

  • 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 command:

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

    $ sqlldr SCOTT/TIGER ulcase7
    

    After running the demonstration, run the ulcase7e.sql script to drop the trigger and package used by this demonstration.

 

Administering SQL*Loader

SQL*Loader is used by both database administrators and Oracle9i users. It loads data from standard operating system files into Oracle database tables.

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

[ "str" | "fix  var   ]

The following table describes these processing options:

String Description
"str" Stream of records, each terminated by a newline character, which are read in one record at a time. This option 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, with the length of each record specified in the first n characters. If you do not specify a value of n, SQL*Loader assumes a value of 5.

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 the "fix" option yields faster performance than the default "str" option 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 character (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 include the additional newline character:

AAA<cr>
BBB<cr>
CCC<cr>

If you do not terminate the last record in a file of fixed-length 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.

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 lines 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 )

Using these lines, SQL*Loader discards newline characters because they are in the fourth position in each fixed-length record.

 

PL/SQL Demonstrations

PL/SQL includes a number of demonstration programs that you can load. The Oracle9i database must be open and mounted to work with the demonstration programs.

You must build database objects and load sample data before using these programs. To build the objects and load the sample data:

  1. Change directory to the PL/SQL demonstrations directory:

    $ cd $ORACLE_HOME/plsql/demo
    
  2. Start SQL*Plus and connect as SCOTT/TIGER:

    $ sqlplus SCOTT/TIGER
    
  3. Enter the following commands to build the objects and load the sample data:

    SQL> @exampbld.sql
    SQL> @examplod.sql
    


    Build the demonstrations as any Oracle user with sufficient privileges. Run the demonstrations as the same Oracle user. 

 

PL/SQL Kernel Demonstrations

The following PL/SQL 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 compile and run the exampn.sql or samplen.sql PL/SQL kernel demonstrations:

  1. Start SQL*Plus and connect as SCOTT/TIGER:

    $ cd $ORACLE_HOME/plsql/demo
    $ sqlplus SCOTT/TIGER
    
  2. Enter a command similar to the following to run a demonstration, where demoname.sql is the name of the demonstration:

    SQL> @demoname
    

To run the extproc.sql demonstration:

  1. If necessary, add an entry for external procedures to the tnsnames.ora file, similar to the following:

    EXTPROC_CONNECTION_DATA.domain =
       (DESCRIPTION =
           (ADDRESS_LIST = 
              (ADDRESS=(PROTOCOL = IPC)( KEY = EXTPROC))
           )
           (CONNECT_DATA = 
              (SID = PLSExtProc)
           )
        )
    
  2. If necessary, add an entry for external procedures to the listener.ora file, similar to the following:

    SID_LIST_LISTENER = 
      (SID_LIST = 
         (SID_DESC=
            (SID_NAME=PLSExtProc)
            (ORACLE_HOME=/u01/app/oracle/product/9.0.1) 
            (PROGRAM=extproc)
         )
       )
    


    The value that you specify for SID_NAME in the listener.ora file must match the value that you specify for SID in the tnsnames.ora file. 

  3. Enter the following command to create the extproc.so shared object, build the required database objects, and load the sample data:

    $ make -f demo_plsql.mk extproc.so exampbld examplod
    

    Alternatively, if you have already built the database objects and loaded the sample data, enter the following command:

    $ make -f demo_plsql.mk extproc.so
    
  4. From SQL*Plus, enter the following commands:

    SQL> CONNECT SYSTEM/MANAGER
    SQL> GRANT CREATE LIBRARY TO SCOTT;
    SQL> CONNECT SCOTT/TIGER
    SQL> CREATE OR REPLACE LIBRARY demolib IS
      2  '$ORACLE_HOME/plsql/demo/extproc.so';
      3  /
    
  5. To run the demonstration, enter the following command:

    SQL> @extproc
    

 

PL/SQL Precompiler Demonstrations


The make commands shown in this section build the required database objects and load the sample data in the SCOTT schema. 

The following precompiler demonstrations are available:

examp9.pc examp10.pc sample5.pc sample6.pc

To build all of the PL/SQL precompiler demonstrations, enter the following commands:

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

To build a single demonstration, enter its name as the argument in the make command. For example, to build the examp9 demonstration, enter:

$ make -f demo_plsql.mk examp9

To run the examp9 demonstration, enter the following command:

$ ./examp9