Setting up an Oracle database

Before you begin

Make sure that you have downloaded the ORACLE_CIS_25101_DBDUMP.zip or ORACLE_CS_25101_DBDUMP.zip file from BMC Electronic Product Distribution (EPD).

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

To prepare your Oracle database

1. 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.

   Important

   • Make sure that you install the Oracle 19.16 version or later and that the database is configured to use Unicode.
   • If you are using Oracle Real Application Clusters (RAC), make sure that you open the ONS port 6200 on each server worker node and on the database servers.
2. Enable TCP/IP for the database.
3. Confirm connection to your Oracle database.
   Contact your database administrator for more information.

4. 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.

5. 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

6. 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;

7. 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.

8. 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.

9. 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.

10. Ensure that the Oracle listener is running and is configured correctly for the database.

11. Set the value of the Oracle CLOB option to inrow.
    Learn about at Oracle CLOB at Evaluating and specifying storage for Oracle LOBs.

12. (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

1. Unzip the ORACLE_CIS_25101_DBDUMP.zip or ORACLE_CS_25101_DBDUMP.zip file that you downloaded from EPD.
   After you unzip the file, the ARSystem-25101-RTM.dmp dump files is displayed.
2. Unzip the SMARTREPORTING_DBDUMP.zip file that you downloaded from EPD.
   After you unzip the file, the SmartReporting-210503HF-202305181219.dmp database dump file is displayed.

3. Download and update the following files:

   File	Description
   Innovation_Suite_schema_tablespace_creation.sql	This file creates users, database schemas, and table spaces required for setting up database configurations for AR System and BMC Helix ITSM: Smart Reporting.
   Data_Population_Script_for_Innovation_Suite	Update this file with the appropriate parameter values.

4. 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.
5. 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.

6. Execute the Innovation_Suite_schema_tablespace_creation.sql file by using the following command:

   sqlplus system/<Password> @Innovation_Suite_schema_tablespace_creation.sql
   

7. Execute the Data_Population_Script_for_Innovation_Suite file.

   • On Windows, perform the following steps:
     1. In the database server, rename the Data_Population_Script_for_Innovation_Suite file 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.

Cursor sharing

Set the value of cursor_sharing to FORCE.

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.

To configure AR System to support Oracle Data Guard

1. Configure an Oracle service that has the same name for the primary and standby database. For example, ORADG.BMC.COM.
2. When installing BMC Helix Service Management, specify the DB_JDBC_URL parameter in the HELIX_ONPREM_DEPLOYMENT pipeline, and use the RAC SCAN IP or DNS name for the AR_DB_HOSTNAME parameter.
   When you specify the DB_JDBC_URL parameter, the BMC Helix Innovation Suite server ignores the following parameters:
   • Oracle-Service
   • Db-Host-Name
   • Db-Server-Port
   • Oracle-SID
   • Db-Custom-Conn-Props
   • SQL-Secure-Connection
      

Where to go from here