Setting up an Oracle database - Documentation for BMC Helix IT Service Management Deployment 21.3.04

To use an Oracle database, you must configure the database before you install BMC Helix IT Service Management. Typically, Oracle database administrators create instances, directories, and groups, and they install the Oracle database and Oracle client before proceeding with the BMC Helix IT Service Management installation.

BMC Helix Innovation Suite installation supports only a case insensitive Oracle database.

Before you begin

Make sure that you have downloaded the ORACLE_DBDUMP_213004-RTM.zipfile from BMC Electronic Product Distribution (EPD).

Important

For Oracle database, you must import the DB dumps before you begin the deployment.

These DB dumps contain schemas related to AR server and Smart Reporting databases.

For information about how to download database dumps from EPD, see Downloading the installation files.

To prepare your Oracle database

As a database administrator, install at least one instance of the Oracle database.
You can install it on a remote server that is accessible to sever where you want to install AR System.
Enable TCP/IP for the database.
Confirm connection to your Oracle database.
Contact your database administrator for more information.
Set the AR System data file size to at least 2 GB.
Important
For each additional product, add at least 2 GB to the data file size.

Ensure that the database parameters listed in the following table are specified.

Important

The following settings and size estimates must be used as a starting point. You should conduct benchmarks in your environment because sizing will vary depending on your workload mix, data volumes, and data composition.

Parameter	Suggested value
`cursor_space_for_time`	False
`db_block_checking`	False
`db_file_multiblock_read_count`	0 (or do not set)
`LOG_BUFFER`	10485760
`OPEN_CURSORS`	500
`optimizer_dynamic_sampling`	2
`session_cached_cursors`	100
`statistics_level`	Typical
`timed_statistics`	True
`UNDO_RETENTION`	14400
`workarea_size_policy`	auto
`REDO log files`	Three (at least 1 GB each)
`NLS_LENGTH_SEMANTICS`	BYTE
`NLS_CHARACTERSET`	AL32UTF8

To prevent AR System from reaching the size limit of the database, set your tablespaces with the AUTOEXTEND parameter to ON, by executing the following SQL statement as the system user:
```
ALTER DATABASE DATAFILE '<OracleHome>/DATABASE/ARSYS' AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;
```
Set the tablespace and temporary tablespace to at least the following minimum settings:
Parameter Suggested value
arsys
2000 MB
artmpf
500 MB
The tablespace names might be different for your environment.
To avoid timeout errors during installation, set the System Global Area (SGA) minimum size to at least 1 GB (small database), 3 GB (medium database), or 6 GB (large database). For Oracle 11g, set the maximum SGA size and enable the database to automatically manage the internal memory structures of the SGA. For example, to change the SGA size to 1 GB, use the alter system set sga_target=1G scope=both command.

Parameter	Suggested value
`arsys`	2000 MB
`artmpf`	500 MB

Verify or set the following environment variables:

Environment variable	Description
NLS_LANG (Windows)	Set NLS_LANG as a system environment variable value as per your database character set . For information about NLS_LANG and its usage, see the following notes from Oracle: 144808.1, 227330.1, 260192.1.
LANG (UNIX)	Specifies globalization settings.
ORACLE_SID	Set the length of this variable to 8c.

Ensure that the Oracle listener is running and is configured correctly for the database.
Set the value of the Oracle CLOB option to inrow.
Learn about at Oracle CLOB at Evaluating and specifying storage for Oracle LOBs .

(Oracle 12c Release 2 only) Add the following parameters and values to sqlnet.ora:

Parameter and value	Purpose
SQLNET.AUTHENTICATION_SERVICES = (NTS)	Required by Oracle 12.2 to enable the AR System server to run on Windows with the database on Windows.
SQLNET.ALLOWED_LOGON_VERSION_SERVER = 10	Required by Oracle 12.2 to connect the database to AR Systems clients, which contain version 10 of the Oracle client library.

To configure your Oracle database

Unzip the ORACLE_DBDUMP_213004-RTM.zipfile that you downloaded from EPD.
After you unzip the file, the following database dump files are displayed:
- ORACLE_ARSystem-213004-RTM.dmp
- ORACLE_SmartReporting-213004-RTM.dmp
Download and update the following files:
Log in to your database server by using the Oracle user credentials and copy the files to the required path such as /data1/DB_DUMP/ or C:\data1\DB_DUMP.
Change your directory to the directory path where you copy the Data_Population_Script_for_Innovation_Suite and Innovation_Suite_schema_tablespace_creation.sql files.
Execute the Innovation_Suite_schema_tablespace_creation.sql file by using the following command:
```
sqlplus system/<Password> @Innovation_Suite_schema_tablespace_creation.sql
```
Execute the Data_Population_Script_for_Innovation_Suitefile.
- On Windows, perform the following steps:
  1. In the database server, rename the Data_Population_Script_for_Innovation_Suitefile to Data_Population_Script_for_Innovation_Suite.bat
  2. Start command prompt, navigate to the directory that contains the Data_Population_Script_for_Innovation_Suite.bat file and execute the file by using the following command:
```
cmd Data_Population_Script_for_Innovation_Suite.bat 
```
- On Linux, in the database server, execute the file as an Oracle user by using the following command:
```
sh Data_Population_Script_for_Innovation_Suite
```
Make sure that the file is executed successfully.

Considerations for an Oracle 12c or 18c database

If you have an Oracle 12c or 18c database, consider the following:

Consult your Oracle DB administrator to understand the architectural changes in Oracle 12c and 18c. For more information, refer to Oracle documentation.
The following options are available when you choose to use the Oracle 12c or 18c database:
- If want to use a non multi-tenant database, you must create a non-Container Database (CDB) database.
- If you want to use a multi-tenant database, you must create a CDB with Pluggable Database (PDB) inside it.

If you are using non-CDB, no specific settings are required.
If you are using the CDB with Pluggable Database (PDB) inside it, perform the following steps before installing BMC Helix IT Service Management:
1. Create the PDB. For example you could create a database called REMPDB. For more information, see Oracle documentation for Multitenant architecture.
2. Register the PDB as service to the listener. For more information, see Oracle documentation for Multitenant architecture.
  Note
  During installation choose Oracle service name option in the AR System Server User and Database Information panel.

Cursor sharing

Set the value of cursor_sharing to EXACT.

Configuring Oracle Data Guard

Beginning with the AR System 9.1.02, BMC supports Oracle Data Guard. Oracle Data Guard is a technology that helps in recovering from DB failures across the geographical sites. Clients can failover to the next available database in the Oracle Real Applications Cluster (RAC) setup. For more information, see Oracle Data Guard Open link .

Configuring AR System to support Oracle Data Guard

After your BMC Helix IT Service Management is installed and you have configured it to support Oracle Data Guard, you can log in as AR Admin to verify the configuration.

To configure AR System to support Oracle Data Guard

On the AR System Server user, configure a service that has the same name for the primary and standby database. For example, ORADG.BMC.COM.

In the AR System Configuration Generic UI Open link form, set the Oracle-JDBC-URL parameter to the specified syntax:

Oracle-JDBC-URL: jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=OFF)(FAILOVER=ON)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=primarydb_host)(PORT=primarydb_port))(ADDRESS=(PROTOCOL=TCP)(HOST=standbydb_host)(PORT=standbydb_port)))(CONNECT_DATA=(SERVICE_NAME=ORADG.BMC.COM)(SERVER=DEDICATED)))