<?xml version="1.0" encoding="utf-8"?>
<ManualTopic ID="/baanerp/install/unix/Pre_Unix_Appendix_A_Oracle" xml:lang="en">
   <Header>
      <Title>Appendix Oracle Server</Title>
      <Keyword type="header" order="d1e1082181">
         <Level1>Oracle Appendix</Level1>
      </Keyword>
   </Header>
   <AimSection/>
   <GeneralSection>
      <SectionTitle>To allocate disk space</SectionTitle>
      <Paragraph>The examples in this section are based on tablespaces in the
		file systems. The following table lists the standard names of the Oracle
		tablespaces:</Paragraph>
      <TABLE border="1" borderstyle="default" space-and-orientation="indented-estimated">
         <CAPTION>
            <GUIRef href="" role="manual">Oracle tablespaces</GUIRef>
         </CAPTION>
         <TBODY noTR="7">
            <TR no="1" isLast="false">
               <TD width="203" rowspan="1" colspan="1">
                  <GUIRef href="" role="manual">Tablespace name</GUIRef>
               </TD>
               <TD width="558" rowspan="1" colspan="1">
                  <GUIRef href="" role="manual">Function</GUIRef>
               </TD>
            </TR>
            <TR no="2" isLast="false">
               <TD width="203" rowspan="1" colspan="1">SYSTEM</TD>
               <TD width="558" rowspan="1" colspan="1">System tables</TD>
            </TR>
            <TR no="3" isLast="false">
               <TD width="203" rowspan="1" colspan="1">TEMP</TD>
               <TD width="558" rowspan="1" colspan="1">Temporary tablespace</TD>
            </TR>
            <TR no="4" isLast="false">
               <TD width="203" rowspan="1" colspan="1">SYSAUX</TD>
               <TD width="558" rowspan="1" colspan="1">Tablespace for Auxilary programs like
				Oracle Enterprise Manager</TD>
            </TR>
            <TR no="5" isLast="false">
               <TD width="203" rowspan="1" colspan="1">UNDOTBS</TD>
               <TD width="558" rowspan="1" colspan="1">Tablespace for undo information</TD>
            </TR>
            <TR no="6" isLast="false">
               <TD width="203" rowspan="1" colspan="1">DATSPACE</TD>
               <TD width="558" rowspan="1" colspan="1">LN tables</TD>
            </TR>
            <TR no="7" isLast="true">
               <TD width="203" rowspan="1" colspan="1">IDXSPACE</TD>
               <TD width="558" rowspan="1" colspan="1">LN indexes</TD>
            </TR>
         </TBODY>
      </TABLE>
      <Paragraph>The following table lists the estimated disk space for the
		Oracle installation and the tablespaces. The guidelines are for approximately
		100 users:</Paragraph>
      <TABLE border="1" borderstyle="default" space-and-orientation="indented-estimated">
         <CAPTION>Estimated tablespace requirements for 100
		  users</CAPTION>
         <TBODY noTR="8">
            <TR no="1" isLast="false">
               <TD width="138" rowspan="1" colspan="1">Dbspace</TD>
               <TD width="165" rowspan="1" colspan="1">Disk
				space</TD>
               <TD width="457" rowspan="1" colspan="1">Description</TD>
            </TR>
            <TR no="2" isLast="false">
               <TD width="138" rowspan="1" colspan="1">SYSTEM</TD>
               <TD width="165" rowspan="1" colspan="1">512 MB</TD>
               <TD width="457" rowspan="1" colspan="1">Stores
				information about all tables. The size depends on the number of
				companies.</TD>
            </TR>
            <TR no="3" isLast="false">
               <TD width="138" rowspan="1" colspan="1">SYSAUX</TD>
               <TD width="165" rowspan="1" colspan="1">1 GB</TD>
               <TD width="138" rowspan="1" colspan="1">Stores statspack info and contains information of Oracle Enterprise
				Manager</TD>
            </TR>
            <TR no="4" isLast="false">
               <TD width="138" rowspan="1" colspan="1">Redolog</TD>
               <TD width="165" rowspan="1" colspan="1">3 X 128
				MB</TD>
               <TD width="457" rowspan="1" colspan="1">Space for roll forward data.</TD>
            </TR>
            <TR no="5" isLast="false">
               <TD width="138" rowspan="1" colspan="1">UNDOTBS</TD>
               <TD width="165" rowspan="1" colspan="1">300 MB</TD>
               <TD width="457" rowspan="1" colspan="1">Space for
				uncompleted transactions</TD>
            </TR>
            <TR no="6" isLast="false">
               <TD width="138" rowspan="1" colspan="1">TEMP</TD>
               <TD width="165" rowspan="1" colspan="1">200 MB</TD>
               <TD width="457" rowspan="1" colspan="1">Space for tasks such as
				sorting.</TD>
            </TR>
            <TR no="7" isLast="false">
               <TD width="138" rowspan="1" colspan="1">DATSPACE</TD>
               <TD width="165" rowspan="1" colspan="1">3 GB</TD>
               <TD width="457" rowspan="1" colspan="1">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.</TD>
            </TR>
            <TR no="8" isLast="true">
               <TD width="138" rowspan="1" colspan="1">IDXSPACE</TD>
               <TD width="165" rowspan="1" colspan="1">3
				GB</TD>
               <TD width="457" rowspan="1" colspan="1">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.</TD>
            </TR>
         </TBODY>
      </TABLE>
      <Note>
         <SectionTitle>Note</SectionTitle>
         <Paragraph>In case of a multibyte or UNICODE installation, a minimum of
		  6 GB disk space is required for the DATSPACE and a minimum of 6 GB disk space
		  is required for IDXSPACE.</Paragraph>
      </Note>
      <Paragraph>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:</Paragraph>
      <List>
         <ListItem>Disk 1: Oracle, system tablespace, tempspace and redolog
		  files</ListItem>
         <ListItem>Disk 2, Disk 3 and Disk 4: Data and Index files</ListItem>
      </List>
   </GeneralSection>
   <GeneralSection>
      <SectionTitle>Oracle environment variables</SectionTitle>
      <Paragraph>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.</Paragraph>
      <GeneralSection>
         <SectionTitle>To configure the Oracle environment after
		  installation</SectionTitle>
         <Paragraph>The following sections describe the Oracle configuration tasks
		  that you must perform after you successfully install Oracle.</Paragraph>
         <GeneralSection>
            <SectionTitle>Modify the Oracle parameters</SectionTitle>
            <Paragraph>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.</Paragraph>
            <Paragraph>The parameter can be changed with for example
			 SQL*Plus:</Paragraph>
            <CodeBlock space-and-orientation="indented-estimated">SQL&gt; alter system set sga_max_size = 1G scope=spfile;</CodeBlock>
            <Paragraph>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.</Paragraph>
            <Paragraph>If you change any parameters in the spfile, you must restart
			 the database to put the changes into effect.</Paragraph>
            <Note>
               <SectionTitle>Note</SectionTitle>
               <Paragraph>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.</Paragraph>
            </Note>
            <Paragraph>The following variables and indicated values must be added
			 to or changed, in the <GUIRef href="" role="manual">init.ora&lt;number&gt;</GUIRef> file:</Paragraph>
            <List>
               <ListItem>
                  <GUIRef href="" role="manual">db_files</GUIRef>: <List>
                     <ListItem>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.</ListItem>
                  </List>
               </ListItem>
               <ListItem>
                  <GUIRef href="" role="manual">db_file_multiblock_read_count</GUIRef>: <List>
                     <ListItem>The value must be <GUIRef href="" role="manual">8</GUIRef>.</ListItem>
                     <ListItem>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.</ListItem>
                  </List>
               </ListItem>
               <ListItem>
                  <GUIRef href="" role="manual">sga max size</GUIRef>: <List>
                     <ListItem>For a description of this parameter, refer to
					 “sga_max_size and sga_target parameters,” later in this chapter.</ListItem>
                  </List>
               </ListItem>
               <ListItem>
                  <GUIRef href="" role="manual">sga target</GUIRef>: <List>
                     <ListItem>For a description of this parameter, refer to
					 “sga_max_size and sga_target parameters,” later in this chapter.</ListItem>
                  </List>
               </ListItem>
               <ListItem> 
                  <GUIRef href="" role="manual">log_checkpoint_interval</GUIRef>: <List>
                     <ListItem>For installations, use a value of <GUIRef href="" role="manual">99999999</GUIRef>. Allow the size of the redo log files to decide the
					 checkpoint frequency. The default value is <GUIRef href="" role="manual">10000</GUIRef>, 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.</ListItem>
                  </List>
               </ListItem>
               <ListItem>
                  <GUIRef href="" role="manual">log_buffer</GUIRef>: <List>
                     <ListItem>The default is <GUIRef href="" role="manual">8K</GUIRef> (8192). For the
					 migrations/installs, a larger value is required. Usually, you can determine the
					 size of the log_buffer with the following formula:</ListItem>
                     <ListItem>
                        <Code>3/2 * (avg. redo size per TX * concurrent TX)</Code>
                     </ListItem>
                     <ListItem>Setting a value of <GUIRef href="" role="manual">1M</GUIRef> or <GUIRef href="" role="manual">2M</GUIRef> is not unrealistic.</ListItem>
                  </List>
               </ListItem>
               <ListItem>
                  <GUIRef href="" role="manual">open_cursors</GUIRef>: <List>
                     <ListItem>Increase this value to <GUIRef href="" role="manual">5000</GUIRef>. When
					 companies are uploaded, the value can be lowered to 500.</ListItem>
                  </List>
               </ListItem>
               <ListItem>
                  <GUIRef href="" role="manual">cursor_space_for_time</GUIRef>: <List>
                     <ListItem>Set this value to <GUIRef href="" role="manual">TRUE</GUIRef>. 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 <GUIRef href="" role="manual">TRUE</GUIRef> is only useful on larger system configurations. Note that
					 if the size of your <GUIRef href="" role="manual">shared_pool_size</GUIRef> parameter is less than <GUIRef href="" role="manual">100M</GUIRef>, you probably have to set the <GUIRef href="" role="manual">cursor_space_for_time</GUIRef> parameter to <GUIRef href="" role="manual">FALSE</GUIRef>. If
					 you receive the Oracle error ‘Ora-4031,’ this can be an indication that you
					 must switch the <GUIRef href="" role="manual">cursor_space_for_time</GUIRef> parameter back to <GUIRef href="" role="manual">FALSE</GUIRef>.</ListItem>
                  </List>
               </ListItem>
               <ListItem> 
                  <GUIRef href="" role="manual">sort_area_size</GUIRef>: <List>
                     <ListItem>The default is <GUIRef href="" role="manual">64K</GUIRef> (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.</ListItem>
                  </List>
               </ListItem>
               <ListItem> 
                  <GUIRef href="" role="manual">timed_statistics</GUIRef>: <List>
                     <ListItem>The default is <GUIRef href="" role="manual">FALSE</GUIRef>. If you set this
					 to <GUIRef href="" role="manual">TRUE</GUIRef>, 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</ListItem>
                  </List>
               </ListItem>
               <ListItem> 
                  <GUIRef href="" role="manual">async I/O</GUIRef>: <List>
                     <ListItem>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.</ListItem>
                  </List>
               </ListItem>
               <ListItem> 
                  <GUIRef href="" role="manual">db_block_lru_latches</GUIRef>: <List>
                     <ListItem>The default value is port-specific. This parameter
					 determines the number of buffer cache LRUs in your system. A good range of
					 values is <GUIRef href="" role="manual">2 to 4</GUIRef>. If you set this value too large, this can
					 negatively impact the Oracle Buffer Cache performance.</ListItem>
                  </List>
               </ListItem>
            </List>
         </GeneralSection>
         <GeneralSection>
            <SectionTitle>sga_max_size and sga_target parameters</SectionTitle>
            <Paragraph>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.</Paragraph>
            <Paragraph>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:</Paragraph>
            <List>
               <ListItem>Ib = Base amount of memory</ListItem>
               <ListItem>Ig = Amount of memory per GUI user</ListItem>
               <ListItem>P= Number of connected ERP users</ListItem>
            </List>
            <Paragraph>According to the sizing guide the following is applicable
			 for LN with
			 Oracle version 10.</Paragraph>
            <TABLE border="1" borderstyle="default" space-and-orientation="indented-estimated">
               <TBODY noTR="3">
                  <TR no="1" isLast="false">
                     <TD width="79" colspan="1" rowspan="2">64 bits OS</TD>
                     <TD width="213" colspan="2" rowspan="1">Hostmode</TD>
                     <TD width="212" colspan="2" rowspan="1">Oracle C/S</TD>
                  </TR>
                  <TR no="2" isLast="false">
                     <TD width="213" colspan="2" rowspan="1">2-Tier Arch</TD>
                     <TD width="212" colspan="2" rowspan="1">3 Tier Arch</TD>
                  </TR>
                  <TR no="3" isLast="true">
                     <TD width="79" colspan="1" rowspan="1">Oracle</TD>
                     <TD colspan="1" width="107" rowspan="1">lb= 512 MB</TD>
                     <TD colspan="1" width="102" rowspan="1">lg= 65 MB</TD>
                     <TD colspan="1" width="105" rowspan="1">lb= 512 MB</TD>
                     <TD colspan="1" width="103" rowspan="1">lg= 25
					 MB</TD>
                  </TR>
               </TBODY>
            </TABLE>
            <Paragraph>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.</Paragraph>
            <ExampleSection>
               <SectionTitle>Example</SectionTitle>
               <Paragraph>To calculate memory size, the following formula can be
				used:</Paragraph>
               <Paragraph>It = Ib + (P * Ig) = 512 + (30 * 65) = 2462 Mb</Paragraph>
               <Paragraph>Supply the system with 3 Gb.</Paragraph>
               <Paragraph>It is expected that Oracle will use;</Paragraph>
               <Paragraph>It = (P * lg 3tier) = 30 * 25 = 750 Mb</Paragraph>
               <Paragraph>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.</Paragraph>
            </ExampleSection>
            <ExampleSection>
               <SectionTitle>Example</SectionTitle>
               <Paragraph>To calculate memory size, the following formula can be
				used:</Paragraph>
               <Paragraph>It = Ib + (P * Ig) = 512 + (75 * 25) = 2387 Mb</Paragraph>
               <Paragraph>Supply the system with 3 Gb.</Paragraph>
               <Paragraph>It is expected that Oracle will use;</Paragraph>
               <Paragraph>It = (P * lg 3tier) = 75 * 25 = 1875 Mb</Paragraph>
               <Paragraph>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.</Paragraph>
            </ExampleSection>
         </GeneralSection>
      </GeneralSection>
      <GeneralSection>
         <SectionTitle>To adjust the oratab file</SectionTitle>
         <Paragraph>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:</Paragraph>
         <CodeBlock space-and-orientation="indented-estimated">erpln:/usr2/app/oracle/product/&lt;version&gt;:N</CodeBlock>
         <Paragraph>You must read this entry as follows:</Paragraph>
         <List>
            <ListItem>The first field is the system identifier (ORACLE_SID) of the
			 database</ListItem>
            <ListItem>The second field is the home directory (ORACLE_HOME) of the
			 database</ListItem>
            <ListItem>The third field indicates whether the database must (Y), or
			 must not (N) be brought up at system-startup</ListItem>
         </List>
         <Paragraph>You must change the value of the third field from N to
		  Y.</Paragraph>
      </GeneralSection>
      <GeneralSection>
         <SectionTitle>To enlarge a tablespace</SectionTitle>
         <Paragraph>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.</Paragraph>
         <Paragraph>The following example provides the syntax of the query to
		  enlarge a tablespace. In this example, the sqlplus utility is used:</Paragraph>
         <CodeBlock space-and-orientation="indented-estimated">SQL&gt; alter tablespace tablespace name
	2&gt; add datafile ‘file name‘ 
	3&gt; size size;
</CodeBlock>
         <Paragraph>For example, to enlarge the tablespace DATSPACE in <GUIRef href="" role="manual">/usr2/oracle/dbs</GUIRef> by 512Mb, enter the following query. In this example,
		  the <GUIRef href="" role="manual">sqlplusl</GUIRef> utility is used.</Paragraph>
         <CodeBlock space-and-orientation="indented-estimated">SQL&gt; alter tablespace datspace
	2&gt; add datafile ‘/usr2/oracle/dbs/dat02dbf’ 
	3&gt; size 512M;
</CodeBlock>
         <Paragraph>After you enlarge the tablespace, you do not have to restart
		  the Oracle database server.</Paragraph>
         <GeneralSection>
            <SectionTitle>TNSNAMES.ORA</SectionTitle>
            <Paragraph>The parameters must appear in the DESCRIPTION clause.</Paragraph>
            <CodeBlock space-and-orientation="indented-estimated">service = (DESCRIPTION= 
	(SDU=8192) 
	(TDU=8192) 
	(ADDRESS= (PROTOCOL=tcp) (PORT=2010) (HOST=abc)) 
	(CONNECT_DATA=  (SID=erpln)) 
)
</CodeBlock>
            <Paragraph>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.</Paragraph>
         </GeneralSection>
      </GeneralSection>
      <GeneralSection>
         <SectionTitle>LISTENER.ORA file</SectionTitle>
         <Paragraph>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.</Paragraph>
         <CodeBlock space-and-orientation="indented-estimated">SID_LIST_LISTENER = 
    (SID_LIST = 
	(SID_DESC = 
	    (SDU = 8192)
	    (TDU = 8192)
	    (SID_NAME = erpln) 
	    (ORACLE_HOME = /oracle/product/&lt;version&gt;) 
	) 
    )
</CodeBlock>
      </GeneralSection>
      <GeneralSection>
         <SectionTitle>Net Services</SectionTitle>
         <Paragraph>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.</Paragraph>
         <GeneralSection>
            <SectionTitle>Tune net services</SectionTitle>
            <Paragraph>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.</Paragraph>
            <Paragraph>These sizes are tunable above 2K (default value). The SDU
			 and TDU size is tunable up to 32K.</Paragraph>
            <Paragraph>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.</Paragraph>
         </GeneralSection>
      </GeneralSection>
   </GeneralSection>
</ManualTopic>