| Appendix Oracle Server |  |
| Oracle on Windows is integrated with the Event Viewer and Performance Monitor utilities on Windows. The Event Viewer utility determines how system-alert messages appear on Windows. Oracle has integrated with Event Viewer to display Oracle startup and shutdown messages and the OS audit trail if you configure OS auditing in Oracle in the Event Viewer. Performance Monitor is the Windows sar utility suite, which provides detailed resource utilization data for all processes that run on the system. Oracle has integrated with Performance Monitor to enable viewing utilization of operating system and Oracle resources, such as file write bytes per second related to Oracle or library-cache hit-ratios. The Oracle Performance Monitor icon in the Oracle group starts the standard Windows Performance Monitor utility but feeds the utility Oracle-specific data. Note The Windows Performance Monitor and Event Viewer utilities recognize only one Oracle instance on Windows. The only Oracle instance recognized by Windows is the instance defined by the registry entry:
    HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE/ORACLE_SID
Although you can run multiple instances of Oracle on Windows, only the one identified by the ORACLE_SID value in the registry is visible to the Performance Monitor, Event Viewer, and Server Manager 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 | ERP LN tables
IDXSPACE | ERP 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. 4 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. 4 GB can contain company 000 and some demo companies.
Note In case of a multibyte or UNICODE installation, a minimum of 4 GB disk space is required for the DATSPACE and a minimum of 4 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 ERP LN installation, you must set several environment variables for Oracle. For more information about these settings, refer to the online Help of the ERP 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. Note 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 ERP 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 ERP 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 ERP 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. Example 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. Example 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 ERP 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. |