ORACLE

This guide includes the individual steps for setting up an Oracle database for Automation EngineDiese Komponente steuert ein Automation Engine-System. Besteht aus verschiedenen Serverprozessen. usage.

Refer to the relevant documents about optimizing the Automation Engine database performance before you set up the database. See: Configuration & Performance of the Database and the list of supported database versions.

Do not limit resource consumption. Aborting transactions due to limitations specified in the database can impede processing in the Automation Engine system. Additionally, inconsistent database contents can result, which can cause subsequent errors or endless loops.

Automic is always built against the lowest supported database version. For Oracle, this refers to version 11. If you are using Oracle 12, you need to create a symbolic link to the Oracle v12 library such as:
libnnz11.so -> /opt/automic/oracle/iclt/12.1.0.2/libnnz12.so
or:
libclntsh.so.11.1 -> /opt/automic/oracle/iclt/12.1.0.2/libclntsh.so.12.1

Modifications in database contents result in an inconsistent database.

An Automation Engine crash under Oracle is only reliably recognized if the Dead Connection Detection is activated. This is set with the entry SQLNET.EXPIRE_TIME in the SQLNET.ORA file to a maximum of 60 seconds.

Visit the Automic website where we provide white papers about Oracle usage.

One effective way to maintain the AE database is to use partitioning with ILMSteht für Information Lifecycle Management, das sich auf eine breite Palette von Strategien zur Verwaltung von Speichersystemen auf Computing-Geräten bezieht. (Information Lifecycle ManagementManagement ist der Funktionsbereich des Service Orchestrator, in dem die Verwaltung von Service Level Agreements (SLAs) durchgeführt wird. In diesem Bereich werden SLA-Definitionen erstellt, bearbeitet und gelöscht. Außerdem dient dieser Bereich auch der Verwaltung Ihrer Geschäftseinheiten.). Automic recommends reading the document Maintaining the data records before you start setting up the database.

Size Requirements for the Initial Installation of an Automation Engine Database

Code-Page Setting

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

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

The choice depends on the characters you need to store in the database.
As WE8ISO8859P1 doesn't support the euro sign (€), WE8ISO8859P15 seems the better choice.
WE8MSWIN1252 supports not only the euro sign, but additional characters as well. So if you set your database up from scratch, this would be the recommended code page.
Please also refer to ORACLE's own support document no. 264294.1 dealing with the choice of code pages.

If you have your database already set up using WE8ISO8859P1 and you don't need the additional characters, there is no need to convert the database to a new character set.

Database settings can be queried as follows:

SELECT * FROM NLS_DATABASE_PARAMETERS

Specifying code-page setting:

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

UNIX: The environment variable NLS_LANG can be set as shown below:

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

For example:

NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P15;export NLS_LANG

Code-page settings can also be specified in the INI files of components (section [ODBC]).

Procedure

Requirements

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. Note that 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 the character set WE8ISO8859P15. Other character sets should only be used with prior approval of the Automic Support team.
• The package DBMS_LOB has to be installed.

Oracle UTF-8 and the Automation Engine

• The Automation Engine functions using ANSI/ASCII code and supports neither UniCode nor UTF-8. If the database needs to operate in UTF-8, the DB client must be configured accordingly, or data cannot be converted correctly. For jobs and job reports, AE converts data to ANSI/ASCII according to the deployed code tables and writes it to the AE Database without changes. AE never supplies UTF-8 format.
  
  The UserInterfaceVeralteter Begriff. Ersetzt durch: Automic Web Interface functions using 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 UserInterface correspond to each other. Otherwise, data cannot correctly be converted. In the Connect String, set the third digit to "1" in order to ignore the check for string data (otherwise the Automation Engine cannot boot). Logs provide information about the Oracle characters that have been converted to ? and are 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.
  • variable UC_SYSTEM_SETTINGS, key XML_ENCODING: Define the same code page as the one used by the database client.
  • INI-file parameter SQLDRIVERCONNECT= (section [ODBC]: Use the same settings for the Automation Engine and the utilities.

Oracle client with UTf-8: Note that a code conversion is required for each SQL statement.

Oracle RAC

• The parameter _lm_dd_interval must be set to <= 10 seconds. Value "0" can also be specified for this parameter in order to recognize deadlocks earlier.
• A database User ID for Automation Engine with sufficient authorizations is required.
• 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 Enginesupports Oracle RAC in an Automation Engine active/active configuration, but we 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.
• DATAGUARD is not HA, so it does require manual steps. Depending on the mode used, it may decrease performance (SYNC commit,…).

Definition of Tablespaces

On the database computer:

• Automic recommends facilitating unlimited extensions (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 installationnull 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.

Creating a Database User

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

Amending Storage Parameters for Large Tables

On the database computer:

• Initial values should be increased as required for productive environments and large tables. This applies to the following tables:

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

• Note that the installation file UC_DDL.SQL for the tables shown below must also be adjusted when the size is changed:

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 facilitating unlimited extensions (MAXEXTENTS) instead of limiting them.

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 facilitating unlimited extensions (MAXEXTENTS) instead of limiting them.

Defining the Roll-back Segment

On the database computer, defining a large roll-back segment for the Automation Engine is strongly recommended. The size should be 10-25% of the Automation Engine data.