1 Administering Oracle Database 10g

1.1.1 Oracle Database 10g Code

Oracle Database 10g code consists of several object libraries that are used to form the Oracle Database 10g image during installation. This code also consists of a shared Oracle Database Client image linked during installation.

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

1.1.2 Oracle Database 10g Instances

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 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 ORACLE_SID. To do this, run the following:

$ @device:[top_install_location]orauser SID 

This section contains the following topics:

• Section 1.1.2.1, "System Global Area"

• Section 1.1.2.2, "Storing Data"

• Section 1.1.2.3, "Storing Changes to the Database"

• Section 1.1.2.4, "Using Redo Log Files"

• Section 1.1.2.5, "Using Logical Names"

1.1.3 Logical Names Data Files: Locations and Identifying

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.

1.2.1 Maximum Value of DB_BLOCK_SIZE 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.

1.2.2 CLUSTER_INTERCONNECTS Initialization Parameter

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 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 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 OpenVMS Cluster network address is insufficient to meet the bandwidth requirements of large Oracle RAC databases.

  On OpenVMS Galaxy systems, this can be a shared memory network address.

1.5.1 Security for SQL*Plus Command

Only the Oracle software owner and database administrator should have system privileges and the requirements for STARTUP, SHUTDOWN, and CONNECT AS SYSDBA.

1.5.2 Security for Database Files

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.

1.5.3 Customizing the Initialization File

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 initsid.ora file. Oracle recommends that you include only those parameters in the initsid.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.

Table 1-3 lists default initialization parameter values on OpenVMS and their default values and range of values.

The following two parameters have been desupported:

1.5.4 Running the orapwd Utility

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

1. Log in as the oracle user.

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

1.5.5 Password Management

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;

1.5.6 Security for External Jobs

To securely execute external jobs, for example using DBMS_SCHEDULER.CREATE_JOB, it is necessary to create a minimally privileged OpenVMS account called ORA_NOBODY. The external jobs will run under this account. The account should be created by the 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)

UAF> EXIT
$ CREATE/DIR/LOG/OWNER=ORA_NOBODY DKA100:[ORA_NOBODY]      !  Example

1.6.1 Trace Files

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.

1.6.2 Alert Files

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.