Release 2 (8.1.6) for Sun SPARC Solaris
A77184-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8i Administrator's Reference Release 2 (8.1.6) for Sun SPARC Solaris A77184-01 |
|
This section describes how to establish a common environment for your Oracle8i system.
To display the current value of an environment variable, use the env command. For example, to display the value of ORACLE_SID, enter:
$ env | grep ORACLE_SID
For the Bourne or Korn shell, enter:
$ ORACLE_SID=test $ export ORACLE_SID
For the C shell, enter:
% setenv ORACLE_SID test
where test is the value of the variable ORACLE_SID.
Oracle8i allows a DBA to set a common environment for all users. A common environment makes it easier for system administrators and database administrators to make changes to the physical Oracle8i system.
The oraenv (coraenv for the C shell) command file is created during installation. It contains values for Oracle environment variables and provides:
For example, 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. With oraenv, each user profile calls the oraenv command file.
Placing oraenv (or coraenv) and dbhome in a local bin directory, separate from the Oracle software home directory, ensures that these files are accessible to all users. It also ensures that oraenv (coraenv) continues to work even if you change the path to point to a different ORACLE_HOME. The local bin directory is specified by the root.sh script, which is run following installation. The default location for the local bin directory on Solaris is /usr/local/bin.
To switch from one database or instance to another, call the oraenv routine, and reply to the prompt with the sid of the desired database. Always provide the full path of the oraenv command file. For example:
$ . /usr/local/bin/oraenv ORACLE_SID= [default]? sid
In the following examples, it is assumed your local bin directory is called /usr/local/bin and your production database is called PROD. If you prefer not to be prompted for the ORACLE_SID at startup, set the ORAENV_ASK environment variable to no.
In the following examples, ORAENV_ASK is reset to the default, Yes, after oraenv is executed. This ensures that the system prompts you for a different ORACLE_SID the next time oraenv is executed.
If you have created a database manually instead of using Oracle Database Configuration Assistant, you must ensure the system configuration is reflected in the /var/opt/oracle/oratab file.
Add an entry for each server instance in the following format:
ORACLE_SID:ORACLE_HOME:{Y|N}
Y or N indicates whether you want to activate the dbstart and dbshut scripts. The Oracle Database Configuration Assistant automatically adds an entry for each database it creates.
For the Bourne or Korn shell, add or replace the following line in the.profile file:
. local_bin_directory/oraenv
with the following lines:
PATH=${PATH}:/usr/local/bin ORACLE_SID=PROD export PATH ORACLE_SID ORAENV_ASK=NO . oraenv ORAENV_ASK=
For the C shell, add or replace the following line in the.cshrc file:
source local_bin_directory/coraenv
with the following lines:
setenv PATH ${PATH}:/usr/local/bin setenv ORACLE_SID PROD setenv ORAENV_ASK NO source /usr/local/bin/coraenv unset ORAENV_ASK
For multiple instances, define the sid at startup.
For the Bourne or Korn shell:
#!/usr/bin/sh echo "The SIDs on this machine are:" cat /var/opt/oracle/oratab | awk -F: '{print $1}' | grep -v "#" ORAENV_ASK="YES" . /usr/local/bin/oraenv
For the C shell:
#!/usr/bin/csh echo "The SIDs on this machine are:" cat /var/opt/oracle/oratab | awk -F: '{print $1}' | grep -v "#" set ORAENV_ASK="YES" source /usr/local/bin/coraenv
This section describes the most commonly-used Oracle8i and UNIX environment variables.
Some of these variables must be defined before you install Oracle8i. They are listed in your Oracle8i Installation Guide.
Table 1-1 provides the syntax and examples for Oracle8i variables.
In Oracle8i files and programs, a question mark (?) represents the value of ORACLE_HOME. For example, Oracle8i expands the question mark in the following SQL statement to the full pathname of ORACLE_HOME:
alter tablespace TEMP add datafile '?/dbs/dbs2.dbf' size 2M
The @ sign represents $ORACLE_SID. For example, to indicate a file belonging to the current instance, enter:
alter tablespace tablespace_name add datafile 'dbsfile@.dbf'
Table 1-2 provides the syntax and examples for UNIX environment variables used with Oracle8i.
The TZ variable sets your time zone. It allows 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.
Initialization parameters allow you to configure and tune your system. This section describes:
initsid.ora file for the Oracle8i instance
There are many optional initialization parameters described in the generic Oracle8i documentation.
This section documents the default initsid.ora file provided with the Oracle8i software. The Oracle Universal Installer (OUI) creates it in the $ORACLE_BASE/admin/sid/pfile directory. You can modify it to customize your Oracle8i installation.
For a sample initsid.ora file, look in the $ORACLE_HOME/dbs directory. This file is provided by Oracle Corporation to assist in customizing your Oracle8i installation.
Table 1-3 lists default initialization parameter values on Solaris. 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.
To display the current values of these parameters on the system, use SQL*Plus to execute the statement SHOW PARAMETERS.
Table 1-4 lists the maximum and default values for parameters in a CREATE DATABASE or CREATE CONTROL FILE statement.
| Parameter | Default Value | Maximum Value |
|
MAXDATAFILES |
30 |
65534 |
|
MAXINSTANCES |
1 |
63 |
|
MAXLOGFILES |
16 |
255 |
|
MAXLOGMEMBERS |
2 |
5 |
|
MAXLOGHISTORY |
100 |
65534 |
The DBA should be familiar with special accounts required by the Oracle server and should make sure these accounts belong to the appropriate groups. UNIX accounts are described in Table 1-6; Oracle server accounts are described in Table 1-7. Special group accounts are described in Table 1-8.
Oracle8i release 8.1.6 includes native support for files greater than 2 GB on Solaris 2.6 and higher. Please see Table 1-5 for Oracle-specific file size limits.
Oracle8i 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 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 on the oracle program.
To ensure greater security for an Oracle8i database, create user groups at the operating system level. Groups are controlled by the UNIX file /etc/group. Oracle programs are divided into two sets for security purposes: those executable by all (other, in UNIX terms), and those executable by DBAs only. A recommended approach to security is:
dba group. The oracle account must have the dba group as a secondary group. The primary group for the oracle account should be the oinstall group. Use the Solaris groupadd or admintool utilities to create the dba group.
oinstall. The oinstall group will own the OUI oraInventory and is responsible for installing and upgrading the Oracle8i system. You can name the group anything, but this document refers to it as the oinstall group. All oracle accounts must have the oinstall group as their primary group. Use the Solaris groupadd or admintool utilities to create the oinstall group.
dba privileges can belong to the dba group, the only user account which should belong to the oinstall group is the oracle account.
If you do not have SQL*Plus, you can use Server Manager to make SQL queries. However, be careful how you assign access to Server Manager. The following system-privileged statements should not be accessible to anyone but the oracle software owner and the dba group users, as they grant special operating system privileges:
The user ID used to install Oracle8i should own the database files. The default user ID is the oracle software owner. Set the authorizations on these files to 0600: read/write (rw) by owner only, with no write authorizations for group or other users.
The oracle software owner should own the directories containing the database files. For added security, revoke read permission from group and other users.
To access the protected database files, the oracle program must have its set user ID (setuid) bit on.
The Oracle Universal Installer automatically sets the permissions of the oracle executable to:
-rwsr-s--x 1 oracle dba 443578 Mar 10 23:03 oracle
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 invoking it.
If you need to set this manually, enter:
$ chmod 6751 $ORACLE_HOME/bin/oracle
You can administer a database from a remote machine, such as a personal computer, without operating system accounts. User validation is accomplished 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 in the $ORACLE_HOME/dbs directory and contain the username and password information for a single database. If there are multiple $ORACLE_HOME directories on a machine, each has a separate password file. To allow the database to use the password file, set the initsid.ora parameter remote_login_passwordfile to exclusive.
The orapwd utility exists in $ORACLE_HOME/bin and is run by the oracle software owner. Invoke orapwd by entering:
$ orapwd file=filenamepassword=password entries=max_users
This syntax is described in Table 1-9:
$ orapwd file=/u01/app/oracle/product/8.1.6/dbs/orapwV816 password=V816pw entries=30
When there is an Oracle8i password file, networked PC users with DBA privileges can access this database as INTERNAL. Privileged users, who want to perform DBA functions on the database, can enter the appropriate SQL*Plus command from their PC, adding the dba user password. For example:
SQL> connect internal/dba_password@alias as {sysdba|sysoper}
The following initsid.ora parameters, shown in Table 1-10, control the behavior of remote connections through non-secure protocols:
|
REMOTE_OS_AUTHENT |
enables or disables |
|
OS_AUTHENT_PREFIX |
used by |
|
REMOTE_OS_ROLES |
enables or disables roles through remote connections |
You need to know Oracle8i's memory usage requirements before starting. Knowing the these requirements helps you determine the number of users you can have on your system, and helps you determine your physical memory and swap space requirement. To calculate the memory requirements, use the following formula:
<size of the oracle executable text>To determine the SGA size, see "Calculating the Size of the SGA".
For each client-server connection, use the following formula to estimate virtual memory requirements:
<size of oracle executable data section>
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 only counted once, no matter how many times the program is invoked, because all Oracle executable text is always shared.
To compute actual Oracle physical memory (background and shadow processes) usage while the database is up and users are connected to it, use the pmap command. Sum the shared sections (indicated by read/write/exec/shared and read/exec) for just the pmon process. Sum the private section (indicated by read/write/exec) for each shadow and background process, including pmon. Background process names begin with ora_, and end with the SID, i.e. ora_pmon_TEST. Shadow process names begin with oracleSID, i.e. oracleTEST.
Use the following script to show actual memory usage.
#!/usr/bin/sh # Copyright 1999 Oracle Corporation # # modification history: # date by comments # ---------- -------- ---------------- # 11/15/1999 rgulledg original program # usage() { echo "Usage: $0 [ SB ]" echo "Usage: $0 [ P <pid> ]" echo "Usage: $0 [ h ]" echo " " echo "specify 'S' for Oracle shadow processes" echo "specify 'B' for Oracle background processes (includes shared memory SGA)" echo "specify 'h' for help" echo " " } echo " " # # check usage # if [ $# = "0" ];then usage;exit 1 fi if [ $1 = "h" ];then echo "This script uses the Sun Solaris pmap command to determine memory usage" echo "for Oracle server [B]ackground processes and/or [S]hadow processes." echo "An individual [P]rocess can also be specified." echo " " echo "Although the Oracle server background processes memory usage should" echo "remain fairly constant, the memory used by any given shadow process" echo "can vary greatly. This script shows only a snapshot of the current" echo "memory usage for the processes specified." echo " " echo "The 'B' option shows the sum of memory usage for all Oracle server" echo "background processes, including shared memory like the SGA." echo " " echo "The 'S' option shows the sum of private memory usage by all" echo "shadow processes. It does not include any shared memory like the" echo "SGA since these are part of the Oracle server background processes." echo " " echo "The 'P' option shows memory usage for a specified process, broken" echo "into two categories, private and shared. If the same executable" echo "for this process was invoked again, only the private memory" echo "would be allocated, the rest is shared with the currently running" echo "process." echo " " usage;exit 1 fi echo $1|grep [SBP] > /dev/null ParmFound=$? if [ $ParmFound != "0" ];then usage;exit 1 fi echo $1|grep P > /dev/null ParmFound=$? if [ $ParmFound = "0" ];then if [ $1 != "P" ];then usage;exit 1 fi if [ "X$2" = "X" ];then usage;exit 1 fi echo $2|grep [0-9] > /dev/null ParmFound=$? if [ $ParmFound != "0" ];then usage;exit 1 fi PidOwner=`ps -ef | grep -v grep | grep $2 | grep -v $0 | awk '{print $1}'` CurOwner=`/usr/xpg4/bin/id -un` if [ "X$PidOwner" != "X$CurOwner" ];then echo "Not owner of pid $2, or pid $2 does not exist" echo " " usage;exit 1 fi else if [ "X${ORACLE_SID}" = "X" ];then echo "You must set ORACLE_SID first" usage;exit1 fi fi # # initialize variables # Pmap="/usr/proc/bin/pmap" SharUse="/tmp/omemuseS$$" PrivUse="/tmp/omemuseP$$" ShadUse="/tmp/omemuseD$$" PidPUse="/tmp/omemusePP$$" PidSUse="/tmp/omemusePS$$" TotalShad=0 TotalShar=0 TotalPriv=0 PidPriv=0 PidShar=0 # # shadow processes # echo $1|grep S > /dev/null ParmFound=$? if [ $ParmFound = "0" ];then ShadPrc="`ps -ef|grep -v grep|grep oracle$ORACLE_SID|awk '{print $2}'`" echo "" > $ShadUse for i in $ShadPrc;do $Pmap $i | grep "read/write" | grep -v shared | \ awk '{print $2}' | awk -FK '{print $1}' >> $ShadUse done for i in `cat $ShadUse`;do TotalShad=`expr $TotalShad + $i` done TotalShad=`expr $TotalShad "*" 1024` echo "Total Shadow (bytes) : $TotalShad" /bin/rm $ShadUse fi # # non-shared portion of background processes # echo $1|grep B > /dev/null ParmFound=$? if [ $ParmFound = "0" ];then OrclPrc="`ps -ef|grep -v grep|grep ora_|grep $ORACLE_SID|awk '{print $2}'`" BkgdPrc="`echo $OrclPrc|awk '{print $1}'`" echo "" > $PrivUse for i in $OrclPrc;do $Pmap $i | grep "read/write" | grep -v shared | \ awk '{print $2}' | awk -FK '{print $1}' >> $PrivUse done for i in `cat $PrivUse`;do TotalPriv=`expr $TotalPriv + $i` done TotalPriv=`expr $TotalPriv "*" 1024` echo "Total Private (bytes) : $TotalPriv" # # shared portion of background processes # echo "" > $SharUse $Pmap $BkgdPrc | grep "read/exec" | \ awk '{print $2}' | awk -FK '{print $1}' >> $SharUse $Pmap $BkgdPrc | grep "shared" | \ awk '{print $2}' | awk -FK '{print $1}' >> $SharUse for i in `cat $SharUse`;do TotalShar=`expr $TotalShar + $i` done TotalShar=`expr $TotalShar "*" 1024` echo "Total Shared (bytes) : $TotalShar" /bin/rm $SharUse $PrivUse fi # # non-shared portion of pid # echo $1|grep P > /dev/null ParmFound=$? if [ $ParmFound = "0" ];then echo "" > $PidPUse $Pmap $2 | grep "read/write" | grep -v shared | \ awk '{print $2}' | awk -FK '{print $1}' >> $PidPUse for i in `cat $PidPUse`;do PidPriv=`expr $PidPriv + $i` done PidPriv=`expr $PidPriv "*" 1024` echo "Total Private (bytes) : $PidPriv" # # shared portion of pid # echo "" > $PidSUse $Pmap $2 | grep "read/exec" | awk '{print $2}' | \ awk -FK '{print $1}' >> $PidSUse $Pmap $2 | grep "shared" | awk '{print $2}' | \ awk -FK '{print $1}' >> $PidSUse for i in `cat $PidSUse`;do PidShar=`expr $PidShar + $i` done PidShar=`expr $PidShar "*" 1024` echo "Total Shared (bytes) : $PidShar" /bin/rm $PidPUse $PidSUse fi # # Display grand total # Gtotal="`expr $TotalShad + $TotalPriv + $TotalShar + $PidPriv + $PidShar`" echo " -----" echo "Grand Total (bytes) : $Gtotal" echo " "
Do not use the ps -elf command as the SZ column repeats the shared portion of memory for each process shown, and makes it appear that Oracle is using much more memory than it actually is.
The ps command returns process size in pages; your system page size is architecture-dependent. Use the pagesize command to determine whether the size is 4096 or 8192 bytes. For each process, multiply the SZ value by the page size.
Finally, add the text size for the Oracle executable and every other Oracle tool executable running on the system to that subtotal. Remember to count executable sizes only once, regardless of how many times the executable was invoked.
Solaris 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 Solaris 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 Solaris system manager.
Disk quotas established for the oracle user can hinder the operation of the Oracle8i system. Confer with your Oracle8i database administrator and the Solaris system manager before establishing disk quotas.
The System Global Area (SGA) is the Oracle structure that resides in shared memory. It contains static data structures, locks, and data buffers. Sufficient shared memory must be available to each oracle process to address the entire SGA.
The maximum size of a single shared memory segment is specified by the Solaris parameter SHMMAX. The recommended value for SHMMAX is 4294967296 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), Oracle8i attempts to attach more contiguous segments to fulfill the requested SGA size. SHMSEG is the maximum number of segments that can be attached by a process.
The following initsid.ora parameters control the size of the SGA:
Use caution when setting values for these parameters. When values are set too high, too much of the machine's physical memory is devoted to shared memory, resulting in poor performance.
You can determine the SGA size in one of these ways:
(DB_BLOCK_BUFFERS x DB_BLOCK_SIZE)
+ SORT_AREA_SIZE
+ SHARED_POOL_SIZE
+ LOG_BUFFER
+ JAVA_POOL_SIZE
show sga command.
The address at which the SGA is attached affects the amount of virtual address space available for such things as database buffers in the SGA and cursors in the user's application data area.
tstshm executable included in this release of Oracle8i:
$ tstshm
In the output from tstshm, the lines "Lowest shared memory address" and "Highest shared memory address" indicate the valid address range.
tstshm to determine the valid virtual address boundaries at which a shared memory segment can be attached.
$ORACLE_HOME/rdbms/lib directory, and run genksms to generate the file ksms.s:
$ cd $ORACLE_HOME/rdbms/lib
$ $ORACLE_HOME/bin/genksms -b sgabeg > ksms.s
where sgabeg is the starting address of the SGA (which defaults to 0x80000000) and should fall within the range determined in step 2.
Never set sgebeg below 0x01000000. On most systems, this leaves about 7Mb for data segments. This amount must allow enough memory for such things as SORT_AREA_SIZE, etc.
With a start address of 0x1000000 you can achieve an overall SGA size of about 3.5GB.
You may receive the following error messages if you reduced the value of sgabeg:
ORA-4030: out of process memory when trying to allocate %s bytes (%s,%s)
ORA-7324: smpall: malloc error while allocating pga.
If this is the case, then you probably lowered the start address into an area which the PGA needs to extend into. Raise sgabeg, and try again.
oracle executable in the $ORACLE_HOME/rdbms/lib directory:
$ make -f ins_rdbms.mk ksms.o
$ make -f ins_rdbms.mk ioracle
Using ioracle:
oracle0)
oracle executable
$ORACLE_HOME/bin directory
The result is a new Oracle kernel that loads the SGA at the address specified by sgabeg.
SQL*Loader demonstrations require that:
scott/tiger has CONNECT and RESOURCE privileges
To create and run a demonstration:
ulcasen.sql script corresponding to the demonstration you want to run. As scott/tiger, invoke SQL*Plus from the command line:
$ sqlplus scott/tiger @ulcasen.sql
This step creates the objects used by this demonstration.
This step loads the demonstration data into the objects created in step 1, but does not run the demonstration.
As scott/tiger, run the SQL*Loader demonstrations in the following order:
ulcase1: Follow steps 1 - 2.
ulcase2: Follow step 2 to invoke the demonstration (you do not have to run the ulcase2.sql script).
ulcase3: Follow steps 1 - 2.
ulcase4: Follow steps 1 - 2.
ulcase5: Follow steps 1 - 2.
ulcase6: Run the ulcase6.sql script as scott/tiger, then enter the following at the command line:
$ sqlldr scott/tiger ulcase6 DIRECT=true
ulcase7: Run the ulcase7s.sql script as scott/tiger, then enter the following at the command line:
$ sqlldr scott/tiger ulcase7
After running the example, run ulcase7e.sql to drop the insert trigger and global variable package.
Oracle8i incorporates SQL*Loader functionality. 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" ]
If the file processing options are not selected, the information is processed by default as a stream of records (str). You might find that fix mode yields faster performance than the default str mode because it does not need to scan for record terminators.
When using the fix option to read a file containing fixed-length records, where each record is terminated by a newline, include the length of the newline (one character) when specifying the record length to SQL *Loader.
For example, to read the following file:
AAA newline BBB newline CCC newline
specify fix 4 instead of fix 3 to account for the additional newline character.
If you do not terminate the last record in a file of fixed records with a newline character, do not terminate the other records with a newline character either. Similarly, if you terminate the last record with a newline, terminate all records with a newline.
Use the position(x:y) function in the control file to discard the newlines from fixed length records rather than loading them. To do this, enter the following in your control file:
load data infile xyz.dat "fix 4" into table abc ( dept position(01:03) char )
When this is done, newlines are discarded because they are in the fourth position in each fixed-length record.
PL/SQL includes a number of sample programs you can load. Demonstration and message files are in the rdbms directory. Perform these steps with the Oracle8i database open and mounted:
scott/tiger:
$ cd $ORACLE_HOME/plsql/demo $ sqlplus scott/tiger
exampbld.sql from SQL*Plus:
SQL> @exampbld
Table 1-12 lists the kernel demonstrations.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Table 1-13 lists the precompiler demonstrations.
|
|
|
|
|
To run the PL/SQL demonstrations, invoke SQL*Plus to connect to the database, using the same user/password you used to create the demonstrations. Start the demonstration by typing an "at" sign (@) or the word start before the demonstration name. For example, to start the examp1 demonstration, enter:
$ sqlplus scott/tiger SQL> @examp1
To build the precompiler PL/SQL demonstrations, enter:
$ cd $ORACLE_HOME/plsql/demo $ make -f demo_plsql.mk demos
If you want to build a single demonstration, enter its name as the argument in the make command. For example, to make the examp9.pc executable, enter:
$ make -f demo_plsql.mk examp9
To start the examp9 demonstration from your current shell, enter:
$ ./examp9
To run the extproc demo, 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.6) (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 tests:
SQL> connect scott/tiger Connected. SQL> @extproc
You can manually relink your product executables with 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 performs manual relinking of Oracle product executables based on what has been installed in the ORACLE_HOME.
To relink, enter the following:
$ relink parameter
|
 Copyright © 1999 Oracle Corporation. All Rights Reserved. |
|