The purpose of this section is to help you configure your database so that you can install the SPARCS N4 application servers. You install the database on a dedicated host computer.
SPARCS N4 is compatible with Oracle, Oracle RAC, Microsoft SQL Server, and MySQL. However, each of these is only appropriate for certain production environments. The customer is responsible for working with a database administrator and a database vendor to choose a database and install and configure it on its own host. The table below recommends basic environment attributes, based on your database choice.
Navis Recommendations for Database Configurations
SQL Server MySQL Oracle Oracle RAC
Operating System
64-bit Microsoft Windows
64-bit Linux
Database Name for SPARCS N4
sparcsn4
User and Password
The user you create should be able to add/modify/delete in the database.
Create an administrator-level user and password, and write down the Oracle System ID (SID) of the database instance in a secure location.
Navis provides the following installation checklists for installing the supported databases:
Oracle/Oracle RAC (on page 26)
SQL Server (on page 30)
MySQL
For information about the hardware capacity recommended for the database server, see Database server size based on annual TEU.
Important Considerations
Your SPARCS N4 database may run on any operating system regardless of the operating system on which you are running the application.
Regardless of OS or database type, Navis highly recommends a 64-bit system for the database server for all customers. If you do not use a 64-bit system, you may experience slow or unacceptable performance.
Current performance and scalability tests, as well as the associated hardware/TEU volume
SPARCS N4: Installation Guide 2.4 25
recommendations, assume that the database for the N4 Billing application runs on a separate database host. It is up to the customer to ensure that performance is adequate for their specific operational profile if they wish to maintain the SPARCS N4 and N4 Billing databases on the same host.
A MySQL database is only suited to terminals with volumes of less than 100 thousand TEUs per annum. At higher volumes, there are serious concerns about indexing performance and the robustness of local support for what is a mission-critical database. If you have more than 100,000 TEU/annum, and are considering MySQL, we require prior consultation and certification from the engineering
department.
Oracle Configuration Checklist
This checklist is intended for your database administrator to use while installing and configuring an Oracle database for SPARCS N4. It is only a checklist, and not intended to replace comprehensive instructions. If your database administrator is unclear about any of the items, contact your Navis Professional Services representative for more information.
Supported Oracle Database Versions: 11g or 11g RAC.
Supported Platforms: Linux, Unix, or Microsoft Windows Server 2008 R2 Licensing Recommendations for your Oracle database:
Recommended for all customers - Diagnostic Package. The advantages are: Automatic Workload Repository Reports, Automatic Database Diagnostic Monitoring.
Recommended for terminals with greater than 750 Million Annual TEU (Large and Extra-Large sites as defined in Database server size based on annual TEU): Enterprise Edition The advantages are: No CPU limit (the standard edition is limited by 4 sockets), and access to the following features: Oracle Parallel Query, Data Guard or Advanced Replication, Online Index Rebuild.
Hardware Recommendations: We highly recommend using a dedicated 64-bit Linux system and at least 4 CPUs for the database server for all customers. If you do not use such system, you may experience slow or unacceptable performance. If you are using a database reporting application, it should use a separate database server.
Running any other applications on the Oracle database server may create a significant load on the database, and the SPARCS N4 application performance will suffer.
The N4 and N4 Billing install wizards do not support SCAN (Single Client Access Name) addressing that provides a single name for clients to access Oracle databases in a cluster. The wizards only generate the older general format when configuring an Oracle RAC database connection. The older general format continues to work with 11gR2, so you can continue to use Oracle RAC with the older general format.
Oracle Configuration Checklist
Apply all recommended patches and updates before proceeding.
Review the SPARCS N4 2.4 Technical Requirements document.
Use only the driver shipped with your SPARCS N4 installation package.26 SPARCS N4: Installation Guide 2.4
The shipped version may not be the latest version commercially available.
Verify that the database character set is AL32UTF8. (AL32UTF8 is the database character set that supports the latest version (5.0 in Oracle 11.1) of the Unicode standard.) Contact your Navis representative for more information.
Verify that you have the connection pooling configured correctly by reviewing the application descriptor file, apex.xml. It should look similar to the sample provided in this document for configuring the database connection: Sample apex.xml file from Oracle 11g (on page 201)
Sample apex.xml file from Oracle 11g RAC (on page 203)
If you have more than one connection pool, make sure all 'cache names' are unique.
Configure a second data source. Refer to the sample files provided in Configure the database connection (on page 199), which all contain segments for a system data source, and Sample scripts for the second database user (on page 42).Activating the Database Performance Pack and configuring a second data source provide you with additional diagnostic information for identifying performance problems and verifying the database configuration. See Deploy the Database Performance Pack (on page 41) for more information.
(Optional) Verify that Fast Connection Failover (FCF) is configured. FCF is an advanced feature of Oracle RAC, which provides high availability of the system. Contact your Navis representative for more information.
Create tablespace(s). To do this, you verify that the segment space management (ASSM) is set to automatic. Contact your Navis representative for more information. In Oracle 10g RAC, ASSM defaults to "manual". In Oracle 10g, ASSM defaults to "auto" for all permanent tablespaces.
Create application user and schema. Verify in the dba_role_privs and dba_sys_privs tables that the SPARCS N4 application user has only two privileges: CONNECT and RESOURCE. Contact your Navis representative for more information. Verify in the dba_ts_quotas that the SPARCS N4 application user has sufficient quota.Do not grant more than the two privileges listed above. If you grant more than CONNECT and RESOURCE, you risk data loss or corruption. For example, granting the 'dba' privilege, could result in upgrade failures that require you to recover your last production database snapshot from the previous release, then restart the upgrade.
If one of the necessary privileges is missing, you must issue an appropriate "grant" command for the application to operate.
You can issue an appropriate "revoke" command to remove non-required privileges.
Oracle 11 expires passwords by default. Make sure the PASSWORD_LIFE_TIME value for SPARCS N4 application users is set to UNLIMITED.
Use the command below to check to see if the database is configured to use the "DEFAULT profile
SPARCS N4: Installation Guide 2.4 27
when deciding to expire passwords:
select resource_name, limit from dba_profiles where profile='DEFAULT' and RESOURCE_TYPE='PASSWORD';
If so, to prevent the password from expiring in Oracle 11, use the following statement:
ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED PASSWORD_LIFE_TIME UNLIMITED;
Verify that the database Server Parameters file is configured by locating the spfile parameter, and making sure it is pointing to an appropriate spfile. Contact your Navis representative for more information.
Schedule an interval for gathering schema statistics. As a default, we recommend you initially set this for twice a week and adjust to be more or less frequent as needed for your terminal. Schedule it for off-peak hours. Contact your Navis representative for more information.Navis also highly recommends gathering system statistics in addition to schema statistics.
Navis highly recommends gathering system statistics during normal (peak) activity and during most idle times. Contact your Navis representative for more information.Analyzing the SYS schema might downgrade performance. Avoid gathering statistics for the entire database.
If you do not gather both types of statistics, the Oracle Optimizer might use non-optimal execution plans, and it will adversely affect application performance.
You must have Oracle interMedia installed for SPARCS N4 2.2 and later versions. Typically this is part of the standard Oracle installation. The interMedia installation includes Oracle Locator, which is required for SPARCS N4 to support geometry objects in an Oracle database. If DDL statements involving geometry columns are failing, please check to see if Oracle interMedia is installed. The link below to the Oracle interMedia User's Guide gives instructions for verifying whether interMedia is installed and how to install it if it is missing:http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14302/ap_instl_up grd.htm
Verify the database memory parameters are sufficient. The following table describes *required*values for the key memory allocation parameter, depending on the size of the database:
Parameter Name Small Database (50-399K Annual
Set SGA_TARGET and PGA_AGGREGATED_TARGET parameters to = 0. Oracle then distributes memory based on actual load. If a memory parameter is a positive number, the values act as minimum value. To review memory settings, use the "show parameter target" command or
28 SPARCS N4: Installation Guide 2.4
"select from v$parameters where ...." statement.
We recommend reviewing Automated Workload Repository (AWR) Reports periodically for advice about how to alter these parameters as necessary.
Verify that PROCESSES, SESSIONS, and OPEN_CURSORS parameters are sufficient. The following table describes the values for these parameters, depending on the size of the database:Parameter Name Small Database (50-399K Annual
(Conditional) For multi-byte encoding systems, we recommend setting the NLS_LENGTH_SEMANTICS parameter to CHAR (the default is BYTE).
(Optional) Configure data storage. Navis provides a simple and robust schema deployment script which is created to have minimal complexity for customers. Only one tablespace is needed to deploy the entire schema.However, Navis recommends you follow Oracle's guidelines for best practices when it comes to database instance and schema configuration, and maintenance.
Navis can provide advice or prepare a configuration/data migration plan on a time and material basis through Navis Professional Services.
The following suggestions are only recommendations, and should be applied only if they suit the needs and fit your environment.
Use ASM (Automated Storage Management). In most cases this will yield better performance and configuration flexibility.
Distribute objects into different tablespaces. This may not bring any performance gains (unless multiple storage devices are utilized), but in general is considered a good practice. Here are some things to keep in mind:
An example of groups of objects can be: LARGE TABLES, INDEXES OF THE LARGE TABLES, OTHER TABLES, INDEXES OF THE OTHER TABLES, LOBs.
In cases when multiple storage devices are utilized by a database, it could be beneficial to distribute tablespace storage over those devices in a way which would balance the IO.
It may be beneficial to:
Create separate tablespaces for read-only tables.
Keep small, referenced tables in memory.
Keep redo log files on separate disks.
Deliberately map different tables and index structures to tablespaces of different sizes.
This allocation is based on the average row length of the data within the table, as well as access patterns within the whole database.
SPARCS N4: Installation Guide 2.4 29
Oracle lets you choose between 2K, 4K, 16K, and 32K sizes of data block tablespaces.
Use appropriate STORAGE parameters on an object level and EXTENT parameters on tablespace level.
For example, use AUTO segmentspace management, and LOCAL extent management.
Additionally, if a tablespace contains only large objects, it may be advantageous to use large uniformly-sized extents.
Use an appropriate number of INI_TRANS object level parameters depending on a number of concurrent updates.
All options are dependent upon database size and hardware configuration.
(Conditional) If you are using Oracle RAC, the Interconnect protocol setup is important because User Data Protocol (UDP) settings define the amount of send and receive buffer space for sending and receiving datagrams over an IP network. These settings affect cluster interconnect transmissions. If the buffers set by these parameters are too small, the incoming UDP datagrams can drop due to insufficient space. Then they require send-side transmission, which can result in poor cluster performance. Contact your Navis representative for more information.
Alter the connection parameters to avoid connections from being severed due to a firewall timeout.SPARCS N4 can hang if you have set up a firewall timeout for idle connections. To do this, set the three parameters below.
Set the InactivityTimeout value (seconds) on the connection cache to be shorter than the firewall idle timeout value. For example:
connectionCacheProperties="{MinLimit=6, InitialLimit=6, MaxLimit=50, MaxStatementsLimit=50, InactivityTimeout=120,
ConnectionWaitTimeout=120}"
Set maxInactiveInterval parameter for Tomcat to the same value. For example:
maxInactiveInterval="120"
Enable Oracle Net DCD (Dead Client Detection) by setting SQLNET.EXPIRE_TIME=1 in the sqlnet.ora file on the server-side.
You can also review other suggestion from Oracle at:
http://download.oracle.com/docs/cd/B28359_01/java.111/b31224/apxtblsh.htm#C HDBBDDA
For more information, see the SPARCS N4 Administrator's Guide. It contains instructions for collecting performance statistics and a procedure for gathering information in case there is a performance problem.SQL Server Installation and Configuration Checklist
This checklist is intended for your database administrator to use while installing and configuring a Microsoft SQL ServerTM database for Navis SPARCS N4. It is only a checklist for configuring SQL Server for SPARCS N4; it is not possible to replace comprehensive Oracle installation instructions with this list. If your database administrator is unclear about any of the items, contact your Navis Professional Services representative for more information.
30 SPARCS N4: Installation Guide 2.4
Supported SQLServer Database Versions: Microsoft SQL Server Enterprise 2008 R2 Edition (US English) or, if fewer than 100K annual TEU, Microsoft SQL Server Standard 2008 R2 Edition (US English).
Supported Platforms: Microsoft Windows Server 2008 R2 or Windows Server 2003 (64-bit - see below) Licensing Recommendations: Enterprise Edition
Microsoft SQL Server Enterprise editions have the following advantages over SQL Server Standard:
No limitation on the number of CPUs
Full scalability vs. limited scalability
High availability
Ability to create and rebuild indexes online
Superior performance and diagnostic tools
Hardware Recommendations: SPARCS N4 requires a 64-bit system and at least 4 CPUs for the database server for all customers. If you do not use such system, you may experience slow or unacceptable
performance.
SQL Server Installation and Configuration Checklist
Apply all recommended patches and updates before proceeding.
Microsoft SQL Server 2008 requires an installation of Internet Information Services (IIS). Therefore, before installing Microsoft SQL Server 2008, make sure your machine includes an IIS installation. It is a Windows component you can add from the Control Panel.
Install the following components in the SQL Server setup wizard: SQL Server Database Services
(optional, only required if you use Analytics) Analysis Services
(optional, only required if you use Analytics) Integration Services
Workstation components
Books Online
development tools
(optional) Choose the options to start SQL Server Agent and SQL Browser services after setup (as well as any other services you may have installed).
(optional) Choose Mixed Mode for the Authentication Mode.
You can check to see if they are installed using Start All Programs MS SQL Server 2008 Configuration Tools SQL Server Configuration Manager SQL Server Services. If they are not present, reinstall the database software, and select the components above.
After you walk through the installation wizard, the install processing could take 30 minutes or longer.
Review the following additional recommendations before you complete installation: Navis recommends against installing Reporting Services and Notification Services unless you have separate plans to run reports and use notification services. SPARCS N4 does not use either of
SPARCS N4: Installation Guide 2.4 31
them.
During installation, set up the system to use the built-in system account as the service account.
It is acceptable to use a domain user account as the service account if your database administrator is experienced with administering these types of accounts.
Configure SQL Server using the Configuration Manager (Start All Programs Microsoft SQL Server 2008 Configuration Tools SQL Server Configuration Manager): Add TCP/IP protocol.
You do this by expanding the SQL Server Network Configuration and selecting Protocols for MSSQLServer. Then, in the TCP/IP Properties window, on the Protocol tab, in the Enabled row, select Yes from the list.
Configure the SQL Native Client with TCP/IP protocol.
You do this by expanding the SQL Native Client Configuration node and selecting Client Protocols. Then, in the TCP/IP Properties window, on the Protocol tab, in the Enabled row, select Yes from the list.
Restart the service.
You do this by selecting SQL Server Services in the left panel. Then, in the right panel, right-click SQL Server (MSSQLSERVER), and select Restart.
Create the sparcsn4database using the Microsoft SQL Server Management Studio (Start All Programs Microsoft SQL Server SQL Server Management Studio). Connect to your database server, and use the Object Explorer to create a new database named
"sparcsn4" (If you prefer, you may use a different name.)
Choose the following general setup attributes:
For Initial Size, and Autogrowth, set as in the table below:
Filegroup File Type (Extension)
Initial Size Autogrowth
sparcsn4 Primary Data (.mdf) 250 Mb 500 Mb, unrestricted growth sparcsn4_log Not
Applicable
Log (.ldf) 1,000 Mb 500 Mb or by 10 percent, with unrestricted growth
Full-text indexing is always enabled in SQL Server 2008.
Set the option for Recovery Model to Full.
Set option for Auto Update Statistics Asynchronously to True.
Add a file group with the name sparcsn4_data and make it the default group.
Once you finalize the general attributes, options, and file groups by clicking OK in the New Database dialog, Microsoft SQL Server creates the sparcsn4 database and adds it to the tree in the Object Explorer.
Add four or more data files to the sparcsn4_data filegroup. Use the attribute values in the table below:
32 SPARCS N4: Installation Guide 2.4
Logical Name Filegroup Initial Size Autogrowth sparcsn4_data_F001 sparcsn4_data 500 Mb 500 Mb, unrestricted growth sparcsn4_data_F002 sparcsn4_data 500 Mb 500 Mb, unrestricted growth sparcsn4_data_F003 sparcsn4_data 500 Mb 500 Mb, unrestricted growth sparcsn4_data_F004 sparcsn4_data 500 Mb 500 Mb, unrestricted growth
Your sparcsn4_log file should look like this:
Logical Name Filegroup Initial Size Autogrowth
sparcsn4_log Not Applicable 1,000 Mb 500 Mb or by 10 percent, with unrestricted growth
Add the data files from the Database Properties window, on the Files page.
Navis recommends that you add a minimum of four data files for all sites. Large sites should add more as needed.
Verify that the database collation is case insensitive by reviewing the properties: For the collation option, choose the suffix, "CI", for example: SQL_Latin1_General_CP1_CI_AS
SPARCS N4 must have case-insensitive collation to operate properly.
Create the sparcsn4user using the Microsoft SQL Server Management Studio (Start All Programs Microsoft SQL Server SQL Server Management Studio. To get a new login dialog box, expand the Security node on the highest level (not the one under the sparcsn4 database), right-click Login and select New Login.
Create a new Login Name called "sparcsn4". (If you prefer, you can use a different name.) Require SQL Server Authentication.
The password you create here is the same password you specify in the apex.xml file when you Configure the database connection (on page 199).
Disable Enforce password policy.
Select sparcsn4as the default database.
Grant the "public" server role to the sparcsn4 user.
If the list in the interface does not include public, leave all check boxes cleared and skip this step.
If the list in the interface does not include public, leave all check boxes cleared and skip this step.