Preparing the AE Database - Oracle

This topic guides you through the steps necessary to set up an Oracle database for the Automation Engine.

Notes:

This page includes the following:

Disk Space Required

The initial database space required for an Automation Engine installation depends on the type and size of the system:

For more information, see Automic Automation System Requirements and Sizing.

Code-Page Setting

Important! The code-page setting of the DB client must comply with the database settings. The three code pages from which you can choose are WE8ISO8859P1, WE8ISO8859P15 and WE8MSWIN1252. Which one you choose depends on the character set you need to store in the database. Therefore, ensure that the character set defined in XML_ENCODING in the UC_SYSTEM_SETTINGS variable corresponds to the character set of the database. For more information, see XML_ENCODING.

For example, WE8ISO8859P15 seems the better choice as WE8ISO8859P1 does not support the Euro sign (€). WE8MSWIN1252 supports the Euro sign plus several additional characters. Use this code page if you are newly setting up your database.

Note: It is not necessary to convert your database to a new character set if it uses WE8ISO8859P1 and you do not need any of the additional characters.

When using 8-bit character sets you can use the same code set for the database and the database client. In this case there is no data conversion. However, the user interface, the server and the utilities also need to be compatible with the 8-bit character set.

You can query your database settings with the following command:

SELECT * FROM NLS_DATABASE_PARAMETERS

You can also specify your code-page setting:

Oracle UTF-8 and the Automation Engine

The Automation Engine uses ANSI/ASCII. The DB client needs to be configured properly using a 8-bit character set; otherwise, data cannot be converted correctly.

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. Only use other character sets in close cooperation with CA Automic Support.

If you need your database to operate in UTF-8 you need to ensure that the data is converted correctly, otherwise the database will not work properly. Therefore, it is imperative that the code pages of the DB client and the user interface correspond to each other. 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:

Other Requirements

Database Computer

A 64-bit client must be installed.

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

Setting Up an Oracle Database

In the database computer:

  1. Define the tablespaces.

    • Facilitate 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 1024M MAXSIZE 2048M

      DEFAULT STORAGE (INITIAL 512K NEXT 2048K MAXEXTENTS UNLIMITED);

       

      CREATE TABLESPACE UC4_INDEX

      DATAFILE 'Path to uc4_index.ora'

      SIZE 512M

      AUTOEXTEND ON NEXT 512M MAXSIZE 1024M

      DEFAULT STORAGE (INITIAL 512K NEXT 512K MAXEXTENTS UNLIMITED);

      Notes:

      • Changing the name of the tablespaces (UC4_DATA or UC4_INDEX) requires the names to be changed also in the installation file UC_DDL.SQL and the database table UC_DBSYN. The same applies for every AE update.

      • It is best to use the tablespaces UC4_INDEX and UC4_DATA only, otherwise you would have to manually adjust all SQL files during the upgrade process.

  2. Create a database user. It is recommended to create an additional database user for AE. This user must have the required privileges.

  3. Change the storage parameters for large tables:

    • 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);

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

Scenarios

Scenario 1: RAC

The Automation Engine is not fully RAC enabled, as it only benefits from the increased availability of an RAC system. Its performance, however, does not improve by using RAC technology. On the contrary, you need to make sure that your system only communicates with one node of the RAC system in order to minimize data traffic through the Cluster Interconnect and administrative workload. In doing so, you also reduce the likelihood of deadlocks that might occur in the database because of database nodes that try to access the database concurrently.

Note: Within an RAC node, Oracle generally uses row-level locking. However, block-level transfer and resource locking is used between the RAC nodes. To make sure that the AE is always only connected to one node, using Cluster Managed Services is recommended. You need to configure them in a way that the service only runs on one node and that the cluster software moves it to the second node if required.

Example for setting up a cluster service:

srvctl add service –d <DB NAME> -s <SERVICE NAME> -r <Node 1> -a <Node 2>

srvctl start service –d <DB NAME> -s <SERVICE NAME>

Similarly, in TNSNAMES.ORA under CONNECT_DATA=(SERVICE-NAME=<SERVICENAME<) the service name created with srvctl must be used.

Make sure that the following database parameters are set when you are using an RAC system with the AE:

INIT.ORA

TNSNAMES.ORA

Scenario 2: Data Guard

The characteristics of Oracle Data Guard configuration that are relevant for use with the AE are described below. The configuration that you will use depends on the available infrastructure and your requirements.

Configuration 1 (Maximum Protection - Guaranteed Protection Mode)

The primary database will only register a transaction as committed when this transaction was also committed to at least one standby database. If the transaction could not be committed to a standby database, the primary database stops.

The advantage of this configuration is that production can continue immediately after a short manual intervention when the primary database has failed. Check whether the standby database was synchronized with the primary database at the time it failed. If so, you can activate the standby database and continue.

Maximum Protection - Stops the primary database when the last standby database is no longer available:

Illustration of Maximum Protection configuration

Configuration 2 (Maximum Availability - Instant/Rapid Protection Mode)

This configuration ensures that the standby database(s) is/are synchronized in a timely manner. However, the primary database starts processing the next transaction even it has not yet been confirmed that the previous transaction was also committed to at least one standby database. Therefore, the primary database does not stop when no standby database is available anymore. Changes made in the primary database are automatically updated in the standby database as soon as it is available again. In this configuration, you must always check manually whether the standby database was synchronized with the primary database at the time of failure.

Maximum Availability - Switching to delayed mode is possible when the standby database is not available:

Illustration of Maximum Availability configuration

Configuration 3 (Maximum Performance)

In this configuration, changes will only be transferred to the standby database when the online redo log is changed. Therefore, you cannot expect that the standby database includes up-to-date data when the primary database fails. You always need to manually check all the AE activities that have taken place since the log has been changed the last time.

Maximum Performance - Changes are propagated when the online log is filled:

Illustration of Maximum Performance configuration

Comparison of Data Guard Configurations

As a general rule, all the configurations require the same quantity of user data to be transferred between primary and standby databases. The required workload when the primary database fails is always different. The lower the workload during a failure, the higher the load in the form of longer response times during day-to-day operation.

For the reasons mentioned above, it must be calculated for each case how the demand on availability can be realized with the infrastructure that is currently available.

Maximum Protection

Maximum Availability

Maximum Performance

The illustration shown below demonstrates the connection between performance and availability of the Data Guard configurations compared to a single instance (without hardware cluster). For a single instance, the availability is always minimal compared to a Data Guard solution. However, performance is almost not affected. On the contrary, for Data Guard in maximum availability mode, the availability is very high but the negative effect on performance is also at the maximum level. When you intend to install AE Data Guard focus on the infrastructure available to ensure that you won't have to deal with performance bottlenecks.

the connection between Illustration of performance and availability of the Data Guard configurations compared to a single instance

See also: