This chapter provides information about administering Oracle Database 10g on HP OpenVMS. It contains the following sections:
You must set HP OpenVMS logical names, parameters, and user settings for Oracle Database to work. This chapter describes the various settings for Oracle Database on HP OpenVMS.
This section contains the following topics:
Oracle Database 10g code consists of several object libraries that form the Oracle Database 10g image during installation. This code also consists of a shared Oracle Database Client image linked during installation.
See Also:
Oracle Database Installation Guide for HP OpenVMS for more information about shared Oracle Database Client imageOracle Database 10g code is built to use 64-bit pointers to support very large System Global Areas (SGAs). The code for client software, however, is built to use 32-bit pointers to maintain compatibility with existing client code. There are, therefore, both 32-bit and 64-bit versions of the installed object and sharable libraries. Oracle only supports 32-bit clients. Client applications may not be built with 64-bit pointers.
An Oracle Database 10g instance is a combination of Oracle Database processes and memory buffers.
Because many instances can exist on one system or in one HP OpenVMS Cluster, you must assign a unique, one-to-six character system ID (SID) to every instance. During the installation procedure, you create an instance when you create the initial database. The SID that you assign to this instance becomes the default value of logical ORACLE_SID
.
Before performing any operations on the database instance, the correct Oracle environment must be set up, including setting of logical ORA_SID
and ORACLE_SID
. To do this, run the following:
$ @device:[top_install_location]orauser SID
This section contains the following topics:
An SGA is an area of shared memory that is allocated to each Oracle Database instance. All database operations use data stored in the SGA.
The size of the SGA is determined by parameters in the INIT.ORA
file. These parameters determine:
Allocation of Oracle Database resources used by the processes that share the SGA
Amount of data that may be maintained in the SGA
After you create an instance, you can change the size of its SGA by shutting down the instance with the SQL*Plus utility and modifying the values set in the INIT.ORA
file as needed.
Consequently, parameter settings also determine the memory space needed to support these requirements. Increasing the value of these parameters can improve performance, but performance may also decrease if the SGA consumes the system memory to the extent that the system is forced to page portions of processes in and out of memory.
Oracle Database 10g release 2 (10.2) includes support for the Very Large Memory (VLM) 64-bit feature. This allows a large SGA that is limited only by the amount of physical memory available.
Data retrieved or inserted by user transactions is temporarily buffered in the SGA. Because this data resides in an area of memory accessible by all Oracle processes, disk I/O is reduced and transaction time is significantly improved.
The following are the most significant structures in the SGA.
See Also:
Oracle Database Administrator's GuideThe shared pool contains shared cursors, stored procedures, SQL, PL/SQL blocks, and trigger code. The size of the shared pool is specified by the initialization parameter SHARED_POOL_SIZE
. Larger values of this parameter improve performance in multiuser systems. Smaller values use less memory. The limit for this parameter is determined by the size of the SGA. The shared pool must be at least 150 MB.
Blocks of data retrieved by user transactions are read from the database file and then cached in the database buffer pool in the SGA. This data remains in the buffer pool (even after changes are committed) until more buffers are required. If the data has been modified, then it is written to the database files.
The number of blocks that can be maintained in the buffer pool is determined by the initialization parameters DB_CACHE_SIZE
or DB_nnK_CACHE_SIZE
.
When data is modified, a record of the change (known as a redo entry) is generated in the redo log buffer. When changes to the data are committed, the redo entries in the buffer are written to the current redo log file. Redo log files provide for data recovery if media or system failure occurs before modified data is written from the database buffer to the database file.
The number of bytes that can be maintained in the redo log buffer is determined by the initialization parameter LOG_BUFFER
.
Data is stored in database files. Each database must have at least one database file. Whenever you create a database, an initial database file is also created for the database.
During the installation procedure, if you choose to create a database, then create one database file, typically in the ORA_ROOT:[ORADATA
.
dbname
] directory, where dbname is the name you assign to the database. You can specify any directory for the system data file, and this directory does not necessarily need to be under ORACLE_HOME
.
This initial file contains the data dictionary tables and all data entered by Oracle users (until you expand the database by creating tablespaces and adding data files).
Oracle recommends that the cluster size on the disk drive that will contain the database files be an integer multiple of the Oracle Database 10g Enterprise Edition block size. For example, if the blocks are 2 KB, then the cluster size should be 2 KB, 8 KB, 12 KB, and so on. Keep in mind, though, that cluster sizes are specified in terms of disk blocks (where one block = 512 bytes). So, a 2 KB cluster is a 4-block cluster.
A disk cluster size is the minimum unit of disk allocation. You determine the size when you initialize a disk.
Changes made to the database are logged in the database buffer pool and in a file called a redo log. The changes recorded in the redo log provide for data recovery if media, software, or system failure occurs before the database buffers are written to the database files. Every database must have at least two redo log files so that another redo log is available when the current log is filled.
Modified data is written from the database buffer pool to the database files when the current redo log is full or when the number of blocks in the redo log equals the value set by the INIT.ORA
parameter LOG_CHECKPOINT_INTERVAL
. Any event that causes the database buffers to be written is known as a checkpoint.
The default value of the LOG_CHECKPOINT_INTERVAL
parameter is 10,000 disk blocks (5MB). If required, you can specify a different value. The values assigned to this parameter must be in multiples of physical block size. By setting the LOG_CHECKPOINT_INTERVAL
initialization parameter to zero, you can eliminate interval checkpoints. This reduces the checkpoint frequency and optimizes run-time performance.
You can specify one of two modes for writing redo log files: ARCHIVELOG
and NOARCHIVELOG
. Using the redo logs in ARCHIVELOG
mode allows data recovery in the event of media, software, or system failure.
Caution:
If you are using theNOARCHIVELOG
mode when a media failure occurs, then you will not be able to perform media recovery. You must use the ARCHIVELOG
mode to recover from media failure.When a redo log file is full, the DBA must archive the log file to an offline file before the redo log file can be reused. If it is not archived by the time all other redo log files are filled, then database operations are suspended until archiving is completed. The DBA can archive the redo logs either manually or automatically.
In the NOARCHIVELOG
mode, data in the log file is overwritten when a redo log file must be reused. However, data is never overwritten until data in the database buffer has been written to the database file. Using the redo log files in the NOARCHIVELOG
mode ensures data recovery only for software and system failure.
During the Oracle Database installation procedure, redo log files are created with the default names REDO01.LOG
, REDO02.LOG
and REDO03.LOG
. By default, these files are saved in the ORA_DB
directory, but you can choose an alternative directory. These log files are used in NOARCHIVELOG
mode by default. You can change the mode to ARCHIVELOG
. These files are also 100 MB each, by default. You can alter this size and specify different file names during the installation procedure.
You can use logical names to specify the names of the database, redo log, and control files. Oracle recommends that you use system-level logical names to name the devices where the database and redo log files reside, and that you specify full directory and file name paths for these files.
Control files store logical file names as their translated equivalents, but do not translate concealed logical names.
Caution:
Never use process-level concealed logical names to name any Oracle Database, redo log, or control file. Read the information given in Oracle Database Administrator's Guide before renaming these files.You can rename these files by using the ALTER DATABASE
and ALTER TABLESPACE
commands.
Oracle data files may be placed in any location on any disk, subject to the following restrictions:
The data files or the directory that contains the data files cannot be owned by anyone with a group equal to or less than MAXSYSGROUP
.
The Oracle Database 10g account must have write access to the location of the data files.
Data files cannot be saved in the root-level directory of a disk.
If the directory is not specified, then the default location for created data files is the ORA_DB
directory.
You can identify data files by logical names rather than fully qualified file names in the CREATE DATABASE
or ALTER TABLESPACE
statements. However, these logical names must be defined at the GROUP
level or higher, preferably at the SYSTEM
level. Logical names at the PROCESS
or JOB
level cannot be used to identify data files. If you identify the data files by logical names, then ensure that these logical names are defined during system startup before calling any procedure to restart the database.
The following sections provide information about Oracle Database initialization parameters:
Section 1.2.1, "Maximum Value of DB_BLOCK_SIZE Initialization Parameter"
Section 1.2.2, "CLUSTER_INTERCONNECTS Initialization Parameter"
The DB_BLOCK_SIZE
initialization parameter specifies the standard block size for the database. This block size is used for the SYSTEM
tablespace and by default in other tablespaces.
You can set the DB_BLOCK_SIZE
parameter value to a maximum of 32 KB.
Note:
You cannot change the value of theDB_BLOCK_SIZE
parameter after you create a database.In an Oracle RAC environment, you can use the CLUSTER_INTERCONNECTS
initialization parameter to specify an alternative interconnect for the private network.
The CLUSTER_INTERCONNECTS
parameter requires the IP address of the interconnect instead of the device name. Oracle RAC network traffic is distributed among all the specified IP addresses.
The CLUSTER_INTERCONNECTS
parameter is useful only in an Oracle RAC environment where UDP IPC is enabled. It enables users to specify an interconnect for all IPC traffic that includes Oracle Global Cache Service (GCS), Global Enqueue Service (GES), and Interprocessor Parallel Query (IPQ).
Overall cluster stability and performance may improve when you force Oracle GCS, GES, and IPQ over a different interconnect by setting the CLUSTER_INTERCONNECTS
parameter. For example, to use the network interface whose IP address is 129.34.137.212 for all GCS, GES, and IPQ IPC traffic, set the CLUSTER_INTERCONNECTS
parameter as follows:
CLUSTER_INTERCONNECTS=129.34.137.212
With Hewlett-Packard TCP/IP for HP OpenVMS, run the following:
@SYS$MANAGER:TCPIP$DEFINE_COMMANDS
Use the ifconfig
command to display the IP address of a device. This command displays device names and their IP addresses. For example, to determine the IP address of a device on HP OpenVMS, enter the following command:
CPQ024> ifconfig -a IE0: flags=c43<UP,BROADCAST,RUNNING,MULTICAST,SIMPLEX> *inet 144.25.70.16 netmask fffffc00 broadcast 144.25.71.255 ipmtu 1500 LO0: flags=100c89<UP,LOOPBACK,NOARP,MULTICAST,SIMPLEX,NOCHECKSUM> inet 127.0.0.1 netmask ff000000 ipmtu 4096 TN0: flags=80<NOARP> TN1: flags=80<NOARP>
In the preceding example, the interface IE0:
has an IP address of 144.25.70.16.
Note the following points when using the CLUSTER_INTERCONNECTS
initialization parameter:
The IP addresses specified for the different instances of the same database on different nodes should belong to network adapters that connect to the same network. If you do not follow this rule, then internode traffic may pass through bridges and routers or there may not be a path between the two nodes at all.
Specify the CLUSTER_INTERCONNECTS
parameter in the parameter file, setting a different value for each database instance.
Set a value for CLUSTER_INTERCONNECTS
only when you have an alternative network address to use for the specific Oracle RAC instance within an Oracle RAC environment.
The minimum requirement for a Cluster Interconnect is 1 GB. You would typically use an alternative network address when the default HP OpenVMS Cluster network address is insufficient to meet the bandwidth requirements of large Oracle RAC databases.
On HP OpenVMS Galaxy systems, this can be a shared memory network address.
UDP is required for Cluster_Interconnect
to allow the use of Oracle RAC. You must adjust UDP and socket settings from the default. This is done using OpenVMS management tools.
Current settings can be viewed using the following:
$ @sys$manager:tcpip$define_commands $ sysconfig -q inet
Change and verify the size of the sb_max
parameter as this must be larger than the send or receive socket bufferspaces udp_recvspace
and udp_sendspace
.
$ sysconfig -q socket socket: sbcompress_threshold = 0 sb_max = 1048576
You must increase sb_max
to a larger value before increasing udp_recvspace
and udp_sendspace
.
The following command will make the required change:
$ sysconfig -r inet sb_max=1048576
The following command enables you to view the current settings for only udp_recvspace
and udp_sendspace
:
$ pipe sysconfig -q inet | search sys$input udp udpcksum = 1 udp_recvspace = 42080 udp_sendspace = 9216 udp_ttl = 128
To modify udp_recvspace
and udp_sendspace
, use the following command:
$ sysconfig -r inet udp_recvspace=65536 $ sysconfig -r inet udp_sendspace=65536
Note:
If you specify an HP OpenVMS shared memory network address, then all Oracle RAC instances which are part of an Oracle RAC environment must be within the same HP OpenVMS Galaxy and all of them must be using shared memory network addresses. Contact the HP OpenVMS ambassador to set up shared memory.Table A-1 provides information about the default and maximum values for parameters in a CREATE DATABASE
or CREATE CONTROLFILE
statement.
Note:
Interdependencies among these parameters may affect allowable values.Table 1-1 lists the Oracle Database 10g file size limits specific to HP OpenVMS.
File Type | Maximum Size |
---|---|
Data File |
4,194,303 multiplied by the value of the DB_BLOCK_SIZE parameter |
Import file |
Unlimited (limited by the operating system file size limit) |
Export file |
Unlimited (limited by the operating system file size limit) |
SQL*Loader file |
Unlimited (limited by the operating system file size limit) |
Special operating system accounts and groups are required by Oracle Database 10g.
Table 1-2 describes the oracle
and system
operating system accounts.
Note:
It is possible for different accounts to be used to perform Oracle Cluster Ready Services operations in the CRS home. However, all such accounts must be in the same UIC group as the account that was used to install and startup Oracle Cluster Ready Services in the CRS home.Oracle Database 10g uses several features of the HP OpenVMS 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 when it is run.
The two-task architecture of Oracle Database 10g improves security by dividing work (and address space) between the user program and the oracle
server process. All database access is achieved using the shadow process and special authorizations in the oracle
server process.
This section covers the following topics:
See Also:
Oracle Database Administrator's Guide for more information about security issuesOnly the Oracle software owner and database administrator should have system privileges and the requirements for STARTUP
, SHUTDOWN
, and CONNECT AS SYSDBA
.
The oracle
user should own the database files. Set the permissions on these files to read/write for the owner and no permission for System, Group, and World.
The oracle
user should also own the directories containing the database files. For additional security, you can curtail visibility of the datafiles. To do this, remove any directory permission for System, Group, and World.
The default initialization file (init.ora
) is provided with the Oracle Database 10g software. All Oracle Database 10g instances assume these values if you do not specify different values for them in the init
sid
.ora
file. Oracle recommends that you include only those parameters in the init
sid
.ora
file that differ from the default initialization parameter values.
Use the SQL*Plus command SHOW PARAMETERS
to display the current values of these parameters on the system.
Caution:
Ensure that your database uses the following values for the listed parameters.disk_asynch_io=false
tape_asynch_io=false
backup_tape_io_slaves=false
db_Writer_processes=1
By default, Database Configuration Assistant creates the two asynch parameters with true values.
Table 1-3 lists default initialization parameter values on HP OpenVMS and their default values and range of values.
Table 1-3 Initialization Parameters
Parameters | Default Value | Range of Values |
---|---|---|
1048576 |
From 65536 onward (no upper limit) |
|
1 |
0 to 255 |
|
none |
none |
|
8388608 |
From 65536 onward (no upper limit) |
|
0 |
2 KB to 32 KB |
|
8192 |
2 KB to 32 KB |
|
DB_CACHE_SIZE |
0 |
From 8 MB onward (no upper limit) |
DB_FILES |
200 |
1 to 2000000 |
16 |
1 to the lower of the following: |
|
1/4 TRANSACTIONS |
From 0 onward (no upper limit) |
|
2*SORT_AREA_SIZE |
From 0 onward (no upper limit) |
|
210 MB |
Between 1000000 and 1000000000 |
|
512 KB or 128 KB multiplied by the value of the |
66560 to unlimited |
|
0 |
0 to unlimited |
|
5 |
Between |
|
2 multiplied by the value of the |
Between |
|
1, if |
Between 1 and |
|
AMERICAN |
Valid language names |
|
AMERICA |
Valid territory names |
|
10 |
0 to unlimited |
|
100 KB |
10 KB to unlimited |
|
300 |
1 to unlimited |
|
OPS$ |
Arbitrary string |
See Also:
Oracle Database Release Notes for HP OpenVMS for a complete list of desupported initialization parametersYou can use a password file to identify users who can use the SYSDBA
and SYSOPER
privileges when connecting to the database. To create the password file:
Log in as the oracle user.
Use the ORACLE_HOME:[BIN]orawpd
utility, which has the following syntax:
$ ORAPWD FILE=filename PASSWORD=password ENTRIES=max_users
Table 1-4 describes the filename
, password
, and max_users
syntax for running the orapwd
utility.
Table 1-4 Syntax for Running the orapwd Utility
Variable | Description |
---|---|
|
Name of the file where password information is written. The name of the file must be |
|
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, then both the password stored in the data dictionary and the password stored in the password file are updated. This parameter is mandatory. |
|
Maximum number of entries that you require the password file to accept. |
For security reasons, 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 the accounts that are locked and change their passwords before logging into them. Use SQL*Plus to connect to the database as SYSDBA and enter the following command:
SQL> ALTER USER username IDENTIFIED BY passwd ACCOUNT UNLOCK;
To securely execute external jobs, for example using DBMS_SCHEDULER.CREATE_JOB
, it is necessary to create a minimally privileged HP OpenVMS account called ORA_NOBODY
. The external jobs will run under this account. The account should be created by the HP OpenVMS System Administrator. For example:
$ SET DEFAULT SYS$SYSTEM $ RUN SYSGEN SYSGEN> SHOW MAXSYSGROUP
Make a note of the current value, for example, 8.
SYSGEN> EXIT $ RUN AUTHORIZE UAF> LIST * /BRIEF %UAF-I-LSTMSG1, writing listing file %UAF-I-LSTMSG2, listing file SYSUAF.LIS complete UAF> EXIT $ SORT/KEY=(POSITION=35,SIZE=12) SYSUAF.LIS SYSUAF.TMP $ EDIT/READ SYSUAF.TMP
Determine a suitable new UIC
group which can be used for the new ORA_NOBODY
account. There should currently be no other accounts in this group and the group must be greater than MAXSYSGROUP
. For example:
Greg Mayhew GMAYHEW [240,22] 24959 Normal Gordon Brown GBROWN [240,23] 24959 Normal Jeffrey Archer JARCHER [240,24] 24959 Normal Charles Windsor CWINDSOR [240,25] 24959 Normal Oracle DBA1 DBA1 [244,1] 27335 All Oracle DBA2 DBA2 [244,2] 27335 All Oracle Admin ORA_ADMIN [244,3] 27335 Devour I
In the example, it could be determined that the UIC
group 241
could be used
Exit from the editor.
$ DELETE SYSUAF.LIS;0 $ DELETE SYSUAF.TMP;0 $ RUN AUTHORIZE UAF> ADD ORA_NOBODY /ACCOUNT="Nobody"/UIC=[241,1]/PASSWORD=PRIVATE1 - /NOPWDEXP/PGFLQUOTA=200000/BYTLM=4000000/BIOLM=100/DIOLM=100 - /ASTLM=100/ENQLM=200/TQELM=100/FILLM=100/PRCLM=10/JTQUOTA=512000 - /WSDEF=512000/WSQUO=512000/WSEXTENT=1024000/PRIV=(NETMBX,TMPMBX) - /DEFPRIV=(NETMBX,TMPMBX)/DEVICE=DKA100:/DIR=[ORA_NOBODY] - /FLAGS=(DISCTLY,DISMAIL)
Note:
The values are just examples.UAF> EXIT $ CREATE/DIR/LOG/OWNER=ORA_NOBODY DKA100:[ORA_NOBODY] ! Example
This section describes the trace (or dump) and alert files that Oracle Database 10g creates to diagnose and resolve operating problems, and includes:
Each server and background process can write to an associated trace file. When a process detects an internal error, it writes information about the error to its trace file. The file name format of a trace file is:
nodename_sid_processtype_processname_number.trc,
In this file name format:
nodename
is the name of the system where an instance is running
sid
is the instance system identifier
processtype
is FG (foreground) or (background)
processname
is the process that generates it
number
is a unique 3-digit numeric identifier
A sample trace file name is:
ORA_ROOT:[ADMIN.dbname.BDUMP]NODEA_PROD_BG_PMON_002.TRC
All trace files for background processes are written to the destination directory specified by the BACKGROUND_DUMP_DEST
initialization parameter. All trace files for server processes are written to the destination directory specified by the USER_DUMP_DEST
initialization parameter.
Set the MAX_DUMP_FILE_SIZE
initialization parameter to unlimited or 100m to ensure that the trace file is large enough to store error information.
The nodename
_
sid
_alert.log
file stores significant database events and messages. Anything that affects the database instance or global database is recorded in this file. This file is associated with a database and is located in the directory specified by the BACKGROUND_DUMP_DEST
initialization parameter.
The mod_plsql module is a PL/SQL gateway running within an Apache module in the middle tier server. It runs PL/SQL procedures in a back-end Oracle Database using OCI. The mod_plsql module currently supports only stateless PL/SQL Web applications.
See Also:
Oracle HTTP Server mod_plsql User's Guide for information about developing Web applications using PL/SQLThe PL/SQL Gateway is installed as part of the Apache Install on HP OpenVMS.
See Also:
TheREADME_MODPLSQL.TXT
file in the Apache directoryRefer to Oracle Database Installation Guide (B13681-01) for information about installing and configuring Oracle HTTP Server powered by Apache.
To grant users access to Oracle Database 10g:
Note:
TheORAUSER.COM
script must be located in the top-level Oracle home directory. Do not move this script. The definitions of Oracle logicals are created from the top-level Oracle home directory. If you run the ORAUSER.COM
script from any other location, then Oracle Database 10g will not work correctly.You can define a symbol in the systemwide login procedure (typically, SYLOGIN.COM
) that runs a particular ORAUSER.COM
file. This method may be more useful if users access multiple instances and, therefore, need to run a database-specific ORAUSER
file with the proper parameters. For example:
$ go_prod:== @DISK$DISK1:[ORACLE10G]ORAUSER PROD
Ensure that each user's HP OpenVMS account meets at least the minimum requirements for ASTLM, BYTLM, ENQLM, WSDEFAULT, WSEXTENT, WSQUOTA, and PGFLQUO.
See Also:
Oracle Database Installation Guide for HP OpenVMS for more information about account quotasCreate the Oracle Database 10g user accounts with the CREATE USER
and ALTER USER
commands. Use the GRANT
command to grant the required database privileges or roles as documented in Oracle Database Administrator's Guide.
To enable users to use the SQL*PLUS utility to start or shut down an Oracle Database 10g instance, use the HP OpenVMS AUTHORIZE
utility to add an ORA_
sid
_DBA
or ORA_DBA
process rights identifier to the user's HP OpenVMS account from the HP OpenVMS rights database.