1
Administering Oracle9i

Oracle9i Environment Variables

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

Table 1-1 Oracle9i Environment Variables on UNIX

UNIX Environment Variables

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

Table 1-2 UNIX Environment Variables Used with Oracle9i

Setting a Common Environment

This section describes how to use the oraenv command to set a common UNIX environment. You can use the coraenv command for the C shell in the same way.

$ . /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 examples, 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 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.

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. ISM causes the physical memory for the entire shared memory segment to be locked automatically.

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.

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 shared memory segment is in use at startup or if the value of the SGA_MAX_SIZE parameter is equal to or smaller than the size required for all SGA components combined.

Regardless of whether Oracle9i uses ISM or DISM, it can always exchange the memory between dynamically sizable components such as the buffer cache, the shared pool, and the large pool 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 utility, oradism, to lock and unlock shared memory. If the LOCK_SGA parameter is set to TRUE, Oracle9i attempts to lock the entire SGA at startup. In this case, no locks or unlocks are performed when the SGA resizes.

With Oracle9i release 2 (9.2.0.1.0), the oradism utility is automatically set up during installation. You do not need to perform any configuration tasks to use dynamic SGA.

Shared Memory on AIX

On AIX, shared memory uses common virtual memory resources across processes. Processes share virtual memory segments through a common set of virtual memory translation resources, for example tables and cached entries, for improved performance. With Oracle9i on AIX, shared memory can be pinned to prevent paging and to reduce I/O overhead. To do this, set the LOCK_SGA parameter to TRUE. Additionally, starting with Oracle9i release 2 (9.2.0.1.0) on AIX 5.1, the same parameter activates the large page feature whenever the underlying hardware supports it.Enter the following command to make pinned memory available to Oracle9i on AIX systems:

$ vmtune -s 1

Enter the following command to set the maximum percentage of real memory available for pinned memory where percent_of_real_memory is the maximum percent of real memory that you want to set:

$ vmtune -M percent_of_real_memory

When using the -M flag, it is important that the amount of pinned memory exceeds the Oracle SGA size by at least 3 percent of the real memory on the system, allowing free pinnable memory for use by the kernel.

Use the AIX svmon command to monitor the use of pinned memory during the operation of the system. Oracle9i attempts to pin memory only if the LOCK_SGA parameter is set to TRUE.

On AIX Power4-based systems, you can enable large pages by:

• Using the -g and -L flags of the vmtune command. The -g flag specifies the size of the large pages, and should be set to 256 MB (-g 268435456) for Power4 processors. The -L flag specifies the number of large pages as specified by the -g flag to reserve for Oracle9i.

• Running the bosboot command and then rebooting the system.

Oracle Corporation recommends specifying enough large pages to contain the entire SGA. Starting with Oracle9i release 2 (9.2.0.1.0), the Oracle instance attempts to allocate large pages when the LOCK_SGA parameter is set to TRUE. If the SGA size exceeds the size of memory available for pinning, or large pages, the portion of the SGA exceeding these sizes is allocated to ordinary shared memory.

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

OSDBA, OSOPER, and ORAINVENTORY Groups

Table 1-6 describes the special UNIX groups required by Oracle9i.

Table 1-6 UNIX Groups

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 2 (9.2.0.1.0) for UNIX Systems for information on the appropriate permissions for database files.

External Authentication

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=""

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
   
   

   The following table describes the values that you must specify in this command:

Password Management

When the Database Configuration Assistant Summary window appears, users must change the SYS and SYSTEM account passwords. You cannot use the default CHANGE_ON_INSTALL and MANAGER passwords.

For security reasons, the 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 any locked accounts and change their passwords before logging into them.

To change the passwords, click the Password Management button in the 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;

Oracle HTTP Server Log Files

A number of log files are generated by the server. 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 

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:

Run demonstrations while logged in as the user SCOTT/TIGER. If the SCOTT/TIGER schema does not exist on your system, run the $ORACLE_HOME/rdbms/admin/utlsampl.sql script to create it.

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:

   $ sqlplus SCOTT/TIGER @ulcasen.sql
   
   

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.

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
   
   

   
   

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

   
   

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

$ make -f demo_plsql.mk examp9

$ ./examp9

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

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

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