Appendix Oracle Server

To allocate disk space

The examples in this section are based on tablespaces in the file systems. The following table lists the standard names of the Oracle tablespaces:

**Oracle tablespaces**
Tablespace name	Function
SYSTEM	System tables
TEMP	Temporary tablespace
SYSAUX	Tablespace for Auxilary programs like Oracle Enterprise Manager
UNDOTBS	Tablespace for undo information
DATSPACE	LN tables
IDXSPACE	LN indexes

The following table lists the estimated disk space for the Oracle installation and the tablespaces. The guidelines are for approximately 100 users:

Estimated tablespace requirements for 100 users
Dbspace	Disk space	Description
SYSTEM	512 MB	Stores information about all tables. The size depends on the number of companies.
SYSAUX	1 GB	Stores statspack info and contains information of Oracle Enterprise Manager
Redolog	3 X 128 MB	Space for roll forward data.
UNDOTBS	300 MB	Space for uncompleted transactions
TEMP	200 MB	Space for tasks such as sorting.
DATSPACE	4 GB	Tablespace for demo tables and Company 000. The size depends on the amount of data and number of companies. 2 GB can contain company 000 and some demo companies.
IDXSPACE	4 GB	Tablespace for demo tables and Company 000. The size depends on the amount of data and number of companies. 2 GB can contain company 000 and some demo companies.

In case of a multibyte or UNICODE installation, a minimum of 2 GB disk space is required for the DATSPACE and a minimum of 2 GB disk space is required for IDXSPACE.

For best performance, distribute the tablespaces over several disks. For example, if you have four hard disks available for Oracle and the databases, the following configuration is a good choice:

Disk 1: Oracle, system tablespace, tempspace and redolog files
Disk 2, Disk 3 and Disk 4: Data and Index files

Oracle environment variables

During the LN installation, you must set several environment variables for Oracle. For more information about these settings, refer to the online Help of the LN Installation Wizard.

To configure the Oracle environment after installation

The following sections describe the Oracle configuration tasks that you must perform after you successfully install Oracle.

Modify the Oracle parameters

After you create a standard instance, you must modify some Oracle paramters before Oracle can run correctly. Note that you must log on as user oracle.

The parameter can be changed with for example SQL*Plus:

SQL> alter system set sga_max_size = 1G scope=spfile;

For all remaining tuning tasks, you must verify if the kernel on your system can handle the number of users that you specify. Refer also to the chapter on preinstallation tasks in the Oracle Installation and Configuration Guide.

If you change any parameters in the spfile, you must restart the database to put the changes into effect.

All values for the parameters that have been discussed are good starting values. Your system can require additional tuning later. Providing values in this manual is difficult due to the customization and differences in LN installations.

The following variables and indicated values must be added to or changed, in the init.ora<number> file:

db_files:
- This variable is port-specific, but usually 32 or 64. If you set this value too small or too large, this can negatively impact the Oracle Buffer Cache performance.
db_file_multiblock_read_count:
- The value must be 8.
- This parameter helps with full and range scans. The setting in this parameter impacts the optimizer. The optimizer can cost full or range scans cheaper if this value is larger.
sga max size:
- For a description of this parameter, refer to “sga_max_size and sga_target parameters,” later in this chapter.
sga target:
- For a description of this parameter, refer to “sga_max_size and sga_target parameters,” later in this chapter.
log_checkpoint_interval:
- For installations, use a value of 99999999. Allow the size of the redo log files to decide the checkpoint frequency. The default value is 10000, as a larger value enables you to speed up migrations or installs. If the value is extremely large, and you have large redo log files, the startup of a database that was not shutdown correctly can take longer, because the larger log files must be scanned.
log_buffer:
- The default is 8K (8192). For the migrations/installs, a larger value is required. Usually, you can determine the size of the log_buffer with the following formula:
- 3/2 * (avg. redo size per TX * concurrent TX)
- Setting a value of 1M or 2M is not unrealistic.
open_cursors:
- Increase this value to 5000. When companies are uploaded, the value can be lowered to 500.
cursor_space_for_time:
- Set this value to TRUE. This setting helps to reduce the latch contention on the library cache latches. The side effect of this parameter is that potentially a larger shared spool (shared_pool_size) can be required. For this reason, setting this parameter to TRUE is only useful on larger system configurations. Note that if the size of your shared_pool_size parameter is less than 100M, you probably have to set the cursor_space_for_time parameter to FALSE. If you receive the Oracle error ‘Ora-4031,’ this can be an indication that you must switch the cursor_space_for_time parameter back to FALSE.
sort_area_size:
- The default is 64K (65536). A larger value can help to reduce the number of sorts to disk or the number of times a sort must go to disk. However, setting a larger value has an impact on all Oracle processes and can impact your memory requirements for the database server. Usually, sorts go through the Oracle buffer cache. With sort_direct_writes=AUTO, the writes can go directly to disk.
timed_statistics:
- The default is FALSE. If you set this to TRUE, this can have a minor negative performance impact (one to three percent, platform-specific), but provides detailed timing information that can guide you later if you experience performance problems. Otherwise, you must restart the database instance to enable the instance
async I/O:
- The name of this parameter is port-specific and, therefore, is the default value. For best results, you must enable the async I/O on each platform. Refer to the port-specific Oracle documentation for the name and the default value for this parameter. Enable the asynchronous writes, which are still synchronized to disk, for the DBWR process.
db_block_lru_latches:
- The default value is port-specific. This parameter determines the number of buffer cache LRUs in your system. A good range of values is 2 to 4. If you set this value too large, this can negatively impact the Oracle Buffer Cache performance.

sga_max_size and sga_target parameters

The next two parameters, sga_max_size and sga_target, highly impact memory consumption on your machine. Therefore, you must carry out some sizing, depending on the number of companies, installation type, number of processes, and internal memory.

The values calculated must be viewed as the values for larger installations. Smaller installations can, but not always, reduce some of these values. The formula for computing is as follows:

Ib = Base amount of memory
Ig = Amount of memory per GUI user
P= Number of connected ERP users

According to the sizing guide the following is applicable for LN with Oracle version 10.

64 bits OS	Hostmode		Oracle C/S
	2-Tier Arch		3 Tier Arch
Oracle	lb= 512 MB	lg= 65 MB	lb= 512 MB	lg= 25 MB

The table explains the amount of memory per system. In the host mode environment the Ig is the total of the amount of memory for LN and the amount of memory expected for Oracle. For the database server it is the amount of memory spent for Oracle processes and Oracle shared memory.

Voorbeeld

To calculate memory size, the following formula can be used:

It = Ib + (P * Ig) = 512 + (30 * 65) = 2462 Mb

Supply the system with 3 Gb.

It is expected that Oracle will use;

It = (P * lg 3tier) = 30 * 25 = 750 Mb

It is advisable to start with sga_target and sga_max_size on 512Mb. Some memory will use other memory and further tuning will show if that 512Mb is sufficient or not.

Voorbeeld

To calculate memory size, the following formula can be used:

It = Ib + (P * Ig) = 512 + (75 * 25) = 2387 Mb

Supply the system with 3 Gb.

It is expected that Oracle will use;

It = (P * lg 3tier) = 75 * 25 = 1875 Mb

It is adviseble to start with sga_target and sga_max_size on 1500Mb. Some memory will use other memory and further tuning will show if that 1500Mb is sufficient or not.

To adjust the oratab file

While you run the Root.sh script, the Oracle configuration file /etc/oratab is created. On some systems, this file is located in another directory: /var/opt/oracle/oratab. This file contains the real path of the Oracle installation directory. For LN the instance, the /etc/oratab file contains, for example, the following default entry:

erpln:/usr2/app/oracle/product/<version>:N

You must read this entry as follows:

The first field is the system identifier (ORACLE_SID) of the database
The second field is the home directory (ORACLE_HOME) of the database
The third field indicates whether the database must (Y), or must not (N) be brought up at system-startup

You must change the value of the third field from N to Y.

To enlarge a tablespace

One reason to enlarge a tablespace is, for example, if the tablespace is nearly full, or if you want to create extra space in advance. To enlarge the tablespace, add an extra data file to an existing tablespace. To be able to enlarge a tablespace, you must have DBA permissions. Connect as sysdba, as described in the section “To view rollback segments,” previously in this chapter.

The following example provides the syntax of the query to enlarge a tablespace. In this example, the sqlplus utility is used:

SQL> alter tablespace tablespace name
	2> add datafile ‘file name‘ 
	3> size size;

For example, to enlarge the tablespace DATSPACE in /usr2/oracle/dbs by 512Mb, enter the following query. In this example, the sqlplusl utility is used.

SQL> alter tablespace datspace
	2> add datafile ‘/usr2/oracle/dbs/dat02dbf’ 
	3> size 512M;

After you enlarge the tablespace, you do not have to restart the Oracle database server.

TNSNAMES.ORA

The parameters must appear in the DESCRIPTION clause.

service = (DESCRIPTION= 
	(SDU=8192) 
	(TDU=8192) 
	(ADDRESS= (PROTOCOL=tcp) (PORT=2010) (HOST=abc)) 
	(CONNECT_DATA=  (SID=erpln)) 
)

You can set a value in TDU that differs from the value in SDU. However, this value is unnecessary. For example, with TDU=1024 and SDU=1536, 512, 1024 bytes of data are sent to the transport. With TDU=2048 and SDU=1536, 1536 bytes of data are sent to the transport.

LISTENER.ORA file

Oracle Net Services configures a listener with the name and protocol address you select. Oracle Net Services also configures a protocol address and static service information for external procedures. The parameters must appear in the SID_DESC clause.

SID_LIST_LISTENER = 
    (SID_LIST = 
	(SID_DESC = 
	    (SDU = 8192)
	    (TDU = 8192)
	    (SID_NAME = erpln) 
	    (ORACLE_HOME = /oracle/product/<version>) 
	) 
    )

Net Services

In case of client/server installations that use Net Services, you must make some modifications in the settings. For more information, refer to the Oracle9i Net Services Administrator's Guide.

Tune net services

To obtain performance improvements, you must increase the packet sizes that are used in the network layers. You can configure some control over these packet sizes through the SDU and TDU parameters, which control the sizes of the Service and Transport layer buffers, respectively.

These sizes are tunable above 2K (default value). The SDU and TDU size is tunable up to 32K.

You must perform this tuning on the client side (file TNSNAMES.ORA) and on the server side (LISTENER.ORA). The files, depending on your system, are located in one of the directories /etc, /var/opt/oracle or $ORACLE_HOME/network/admin.