Release 1 (9.0.1) for UNIX Systems: AIX-Based Systems, Compaq Tru64 UNIX, HP 9000 Series HP-UX, Linux Intel and Sun SPARC Solaris
Part Number A90347-02
 Contents |
 Index |
| Oracle9i Administrator's Reference Release 1 (9.0.1) for UNIX Systems: AIX-Based Systems, Compaq Tru64 UNIX, HP 9000 Series HP-UX, Linux Intel and Sun SPARC Solaris Part Number A90347-02 |
|
This chapter provides information on administering Oracle9i on AIX, HP, Linux, Solaris, and Tru64. It contains the following sections:
You must set Oracle9i environment variables, parameters, and user settings for Oracle9i to work. This chapter describes the various settings for Oracle9i on AIX, HP, Linux, Solaris, and Tru64.
In 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
This section describes the most commonly-used Oracle9i and UNIX environment variables. You must define some environment variables before installing Oracle9i. These environment variables are listed in the Oracle9i Installation Guide Release 1 (9.0.1) for UNIX Systems.
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
Table 1-1 provides the syntax for, and examples of, environment variables used by Oracle9i.
Table 1-2 provides the syntax for, and examples of, UNIX environment variables used with Oracle9i.
This section describes how to use the oraenv (coraenv for the C shell) command to set a common UNIX environment.
The oraenv (or coraenv) script is created during installation. It contains values for Oracle environment variables and provides:
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.
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.
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
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:
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.
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.
You can determine the SGA size in one of the following ways:
SQL> SHOW SGA
The result is shown in bytes.
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:
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.
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:
text + SGA + (n * (data + uninitialized_data + 8192 + 2048) )
The following table describes the variables and values in this formula:
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.
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.
Table 1-4 lists the default and maximum values for parameters in a CREATE DATABASE or CREATE CONTROLFILE statement.
| Parameter | Default | Maximum Value |
|---|---|---|
|
MAXLOGFILES |
16 |
255 |
|
MAXLOGMEMBERS |
2 |
5 |
|
MAXLOGHISTORY |
100 |
65534 |
|
MAXDATAFILES |
30 |
65534 |
|
MAXINSTANCES |
1 |
63 |
Table 1-5 lists the Oracle9i file size limits specific to UNIX.
| 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 |
Special operating system accounts and groups are required by Oracle9i, as follows:
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, you must 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.
Table 1-6 describes the special UNIX groups required by Oracle9i.
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.
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:
oracle account should be the oinstall group.
oracle account must have the dba group as a secondary group.
dba privileges can belong to the dba group, the only user account which should belong to the oinstall group is the oracle account.
See the Oracle9i Installation Guide Release 1 (9.0.1) for UNIX Systems for information on the appropriate permissions for database files.
If you choose to use external authentication, you must 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=""
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:
$ORACLE_HOME/bin/orapwd utility, which has the following syntax:
$ orapwd file=filenamepassword=password entries=max_users
Table 1-7 describes this syntax:
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;
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.
Table 1-8 lists default initialization parameter values on UNIX. 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.
|
See Also:
For Tru64, see "TRU64_IPC_NET Initialization Parameter". For more information on initialization parameters see the Oracle9i Database Reference, Oracle9i Database Administrator's Guide, and the Oracle9i Performance Guide and Reference. |
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.
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.
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:
SQL> @$ORACLE_HOME/rdbms/admin/initplgs.sql
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
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
If you modify the configuration, you must 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.
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:
http://ServerName:7777/
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
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
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
This section describes how to build and run the SQL*Loader and PL/SQL demonstration programs installed with Oracle9i.
The following SQL*Loader demonstration files are included with Oracle9i in the $ORACLE_HOME/rdbms/demo directory. Run the demonstrations in numerical order:
|
|
|
|
|
|
|
|
|
|
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:
ulcasen.sql script corresponding to the demonstration you want to run:
$ sqlplus SCOTT/TIGER @ulcasen.sql
$ sqlldr SCOTT/TIGER ulcasen.ctl
The following list provides additional information on the ulcase2, ulcase6, and ulcase7 demonstrations:
ulcase2 demonstration, you do not have to run the ulcase2.sql script.
ulcase6 demonstration, run the ulcase6.sql script, then enter the following command:
$ sqlldr SCOTT/TIGER ulcase6 DIRECT=true
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.
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 n" | "var n" ]
The following table describes these processing options:
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.
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.
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 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:
$ cd $ORACLE_HOME/plsql/demo
$ sqlplus SCOTT/TIGER
SQL> @exampbld.sql SQL> @examplod.sql
The following PL/SQL kernel demonstrations are available:
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
To compile and run the exampn.sql or samplen.sql PL/SQL kernel demonstrations:
$ cd $ORACLE_HOME/plsql/demo $ sqlplus SCOTT/TIGER
demoname.sql is the name of the demonstration:
SQL> @demoname
To run the extproc.sql demonstration:
tnsnames.ora file, similar to the following:
EXTPROC_CONNECTION_DATA.domain=(DESCRIPTION =(ADDRESS_LIST =(ADDRESS=(PROTOCOL = IPC)( KEY = EXTPROC)))(CONNECT_DATA =(SID = PLSExtProc)))
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) ) )
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
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 /
SQL> @extproc
The following precompiler demonstrations are available:
|
|
|
|
|
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
|
 Copyright © 2001 Oracle Corporation. All Rights Reserved. |
|