ORACLE

Database administration rights are necessary to perform this step.

Refer to the reference section of the AE database in order to optimize performance. See: Configuration & Performance of the Database and the list of supported database versions.

Modifications in database contents result in an inconsistent database.

You can maintain the AE database by using partitioning with ILM (Information Lifecycle Management). Automic recommends reading the document about maintaining the data records before you set up the database.

Disk Space Required

See also: System Requirements and Sizing

The code-page setting of the DB client must comply with the database settings.

You can choose from either of the following three code pages, whichever best fits your needs:
WE8ISO8859P1, WE8ISO8859P15 and WE8MSWIN1252.

Which one you choose depends on the characters you need to store in the database.
WE8ISO8859P15 seems the better choice as WE8ISO8859P1 does not support the Euro sign (€),
WE8MSWIN1252 supports the Euro sign plus several additional characters. Automic recommends using this code page if you are newly setting up your database.
For further details, see also ORACLE's support document no. 264294.1.

It is not necessary to convert your database to a new character set f you have your database already set up using WE8ISO8859P1 and you do not need any of the additional characters.

You can query your database settings with the following command:

SELECT * FROM NLS_DATABASE_PARAMETERS

Specifying your code-page setting:

On Windows:
Set HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\...\NLS_LANG in the registry according to your database setting.

On UNIX:
You can set the environment variable NLS_LANG as shown below:

NLS_LANG =<NLS_LANGUAGE>_<NLS_TERRITORY>.<CHARACTER SET>;export NLS_LANG

For example:

NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P15;export NLS_LANG

You can also define the code-page settings in the INI files of the relevant components ( [ODBC] section).

Hardware/Infrastructure

• Automic recommends using an external disk subsystem with a connection via Fibre Channel for the data files. Under no circumstances should local disks with software RAID be used, as this could negatively affect the performance of the database and thereby that of the entire AE system.
• Latency periods should not be too long when the AutomationEngine (server) accesses the database (ping response with 8 K packet size < 1 ms). The same requirement applies when using RAC or Data Guard for the communication between database nodes.
• The database server must have sufficient RAM and a suitable swap area on the local disk.
  • o < 2 GB => SWAP = RAM;
  • o > 2 GB => SWAP = RAM x 0.75
  • o No swap-in (SI) may take place.
• The database system(s) must have enough CPUs appropriate to the load. The run queue length of the available processors under high load can serve as a guideline here. If the run queue is frequently longer than 1, you may need to employ more CPUs.
  • RUN QUEUE LENGTH <= COUNT of CORES
• The number of active components such as routers, switches or firewalls on the path between the AutomationEngine and the database should be minimized so as not to slow down access and to avoid single points of failure.

On the Database computer

A 64-bit client must be installed.

• Install the appropriate ORACLE client software on the Automation Engine computer in order to enable access to the database.

The ORACLE-client version must correspond to the ORACLE version in use.

• Enter the database name in the file TNSNAMES.ORA.
• Change the block size to 8192 bytes when you install the database, and use your preferred character set as described dabove.
• Install the package DBMS_LOB.

Oracle UTF-8 and the Automation Engine

• The Automation Engine uses ANSI/ASCII code and supports neither Unicode nor UTF-8. If you need your database to operate in UTF-8, you must either configure the DB client accordingly. Otherwise, data cannot be converted correctly. For jobs and job reports, the AE converts data to ANSI/ASCII according to the specified code tables and writes it to the AE Database without changing it. AE never supplies UTF-8 format.
  
  The user interface uses UTF-8 and converts data to ANSI/ASCII according to the specified code page before it is transported to the Server (see key XML_ENCODING in the variable UC_SYSTEM_SETTINGS). Only use other character sets in close cooperation with Automic Support.
  
  The database in UTF-8 only works correctly if the code pages of DB client and user interface correspond to each other. Otherwise, data cannot correctly be converted. In the connect string, set the third digit to "1" so that the string data is not checked (otherwise the Automation Engine cannot boot). In the log files, you can see the characters that Oracle has converted to ?, which are therefore not available.
  
  The following requirements are critical:
  • The database must be set with UTF-8 and NLS_LENGTH_SEMANTICS = CHAR.
  • The database client must use WE8ISO8859P15. Define NLS_LENGTH_SEMANTICS = CHAR.
  • In the variable UC_SYSTEM_SETTINGS, key XML_ENCODING, specify the same code page as is used by the database client.
  • In the INI-file parameter SQLDRIVERCONNECT= (section [ODBC], use the same settings for the Automation Engine and the utilities.

When you use the Oracle client with UTF-8, note that it needs to convert the code of each SQL statement that is made.

Prerequisites for ILM

You can partition your database with ILM to guarantee the optimum performance of your Automation Engine system. Oracle requires the permission on USER_OBJECTS to work with ILM.

Oracle RAC

• In the file INIT.ORA, set the parameter _lm_dd_interval to <= 10 seconds. You can also use the value "0" to identify deadlocks earlier.
• You require a database User ID for the Automation Engine with sufficient rights.
• All Automation Engine processes must only connect to one node. Use Oracle services to make sure only one node is taken and service is only available on one node at a time.
• Automation Engine supports Oracle RAC in an Automation Engine active/active configuration, but will only work with one node. Oracle services must be used and setup so that only one node is used.
• The Automation Engine has a built in software cluster; therefore putting the Automation Engine on multiple boxes for high availability (HA) requires no additional manual steps.
• DATA GUARD is not HA, so it does require manual steps. Depending on the mode that is used, you may experience a performance decrease (SYNC commit,…).. See also: Comparison of Data Guard Configurations

• Automic recommends facilitating unlimited extents (MAXEXTENTS) instead of limiting them.
• Tablespace sizes can be adjusted individually (minimum 1 GB UC4_DATA, 500 MB UC4_INDEX for a test environment).
• Tablespace administration by ASSM (Automatic Segment Space Management): SEGEMENT_SPACE_MANAGEMENT=AUTO.
• Log on to the ORACLE database using a user ID that has DBA rights, and define the tablespaces:

CREATE TABLESPACE UC4_DATA
DATAFILE 'Path to uc4_data.ora'
SIZE 1024M
AUTOEXTEND ON NEXT ???M MAXSIZE ????M
DEFAULT STORAGE (INITIAL 512K NEXT 2048K MAXEXTENTS UNLIMITED);

CREATE TABLESPACE UC4_INDEX
DATAFILE 'Path to uc4_index.ora'
SIZE 512M
AUTOEXTEND ON NEXT ??M MAXSIZE ???M
DEFAULT STORAGE (INITIAL 512K NEXT 512K MAXEXTENTS UNLIMITED);

Changing the name of the tablespaces (UC4_DATA or UC4_INDEX) requires that these names are also changed in the installation file UC_DDL.SQL. This also applies for every AE update.

Automic strongly recommends using the tablespaces UC4_INDEX and UC4_DATA only, as otherwise you would have to manually adjust all SQL files during the upgrade process.

Automic recommends creating an additional database user for AE. This user must have the required privileges.

• Increase the initial values as required for productive environments and large tables. This applies to the following tables:

INI, MELD, ODOC, OT, AH, AJPP, AJPPA, RH and RT.

• To increase the values, you need to change the installation file UC_DDL.SQL for the following tables:

CREATE TABLE MELD (

MELD_Idnr INTEGER NOT NULL,

MELD_USR_Idnr INTEGER NOT NULL,

MELD_Seen SMALLINT NULL,

MELD_TimeStamp DATE NULL,

MELD_MsgNr INTEGER NULL,

MELD_MsgInsert VARCHAR2 (255) NULL,

MELD_DeleteFlag SMALLINT NULL,

MELD_Client SMALLINT NULL,

MELD_Source INTEGER NULL,

MELD_Category INTEGER NULL,

MELD_Type INTEGER NULL,

MELD_Host VARCHAR2 (200) NULL,

MELD_ArchiveFlag SMALLINT NULL,

CONSTRAINT PK_MELD PRIMARY KEY

(

MELD_Idnr

) USING INDEX TABLESPACE UC4_INDEX

) TABLESPACE UC4_DATA

STORAGE (INITIAL 51200K NEXT 7168K MAXEXTENTS UNLIMITED);

Automic recommends defining a large roll-back segment for the Automation Engine. The size should be 10-25% of the Automation Engine data.