Setting up an Oracle database for TrueSight Server Automation

This topic provides instructions for setting up an Oracle database for a fresh installation of the TrueSight Server Automation database. 

Warning

These tasks should be performed by a database administrator (DBA). Perform the steps in the exact order in which they are described.

This topic includes the following sections:

Related topics

Walkthrough-Creating-the-TrueSight-Server-Automation-Database-for-Oracle

Setting-up-a-SQL-Server-database-and-user-for-TrueSight-Server-Automation

Walkthrough: Setting up a SQL Server database

Requirements and recommendations

  • Click here for a list of supported Oracle databases.

    Enterprise Edition (EE) and Standard Edition (SE):

    • 11g R2
    • 11g R2 RAC
    • 12c
    • 12c RAC
    • 18c
    • 18c RAC
    • 19c
    • 19c RAC

    (not recommended for production setup) Express Edition (XE)

    Note

    The following databases do not have support for IPv6 in TrueSight Server Automation:

    • Oracle 11g R2 RAC
    • Oracle 12c
    • Oracle 12c RAC

    If you plan to use Oracle version 11.2.0.2 as the database for TrueSight Server Automation, before you install TrueSight Server Automation, you must install the appropriate patch to Oracle 11.2.0.2:

    • Linux and UNIX platforms: Patch 9620994
    • Windows 32 and 64-bit platforms: Oracle 11.2.0.2 Patch 4 (or later) bundle, which contains Patch 9620994.
    To determine if this patch is installed on your Oracle 11.2.0.2 database:
    1. On the database server, ensure that the ORACLE_HOME variable is set to the 11.2.0.2 installation directory.
    2. Run the command to locate Patch 9620994:
      • Linux and UNIX platforms:
        $ORACLE_HOME/OPatch/opatch lsinventory | grep 9620994
      • Windows:
        %ORACLE_HOME%\OPatch\opatch lsinventory | findstr 9620994

    Oracle Exadata - Supported on Oracle Enterprise Edition 12c or later (Extreme Performance and High Performance)

    (Both on-premises and cloud environments are supported)

     

  • If you decide not to use the provided scripts to create the schema, refer to them to determine the privileges required. BMC does not recommend, nor does it support database schema changes.
  • Use only Latin alphabet letters, numbers, and underscores (_) in the database names. Do not begin database names with a number. Do not use hyphens (-). 
  • For a list of steps you can take to optimize the database, see Setting up an Oracle database for TrueSight Server Automation.
  • If you want to use Oracle Exadata, do the following:

    1. Log in to the system where the Oracle Database is installed.
    2. Set the value for the "PARALLEL_DEGREE_POLICY " system-level parameter to "AUTO" or "ADAPTIVE" using the following command:
      ALTER SYSTEM SET PARALLEL_DEGREE_POLICY = ADAPTIVE

To configure the Oracle database

To configure the Oracle database for TrueSight Server Automation, do the following:

  • Edit the init.ora file (where the instance the blade schema is located, for example, ../oracle/product/11.2.0/dbs/init.ora ) to set the following initialization parameters:
    • db_block_size must be set to 8192
    • Consult your database administrator to know the database load and set the processes according to your environment. BMC recommends that processes should be set to 100 for the database in a small-scale environment, 200 for a medium-scale environment, and more than 200 for a large-scale environment. If you are using TrueSight Smart Reporting for Server Automation, the number of processes should ideally be 20 to 30 higher than the total number of database connections.
      For more information about configuring Application Servers, see Configuring-the-Application-Server.
      All other configuration parameters can be set at the discretion of the database administrator.
  • If the database is configured for the UTF8 or AL32UTF8 character set (NLS_CHARACTERSET parameter) in a LATIN-1 locale, and the CHAR schema is used as an underlying persistent schema, set the NLS_LENGTH_SEMANTICS parameter of the database to CHAR to prevent column field overflow. Set this parameter before you run the master schema script to set up the database schema.
  • Ensure that all synonyms are public.

Back to top

To configure TrueSight Server Automation databases for a non-English locale

To use TrueSight Server Automation in a locale other than English, follow these guidelines:

  • Configure your TrueSight Server Automation databases using the settings recommended by your database vendor for that language.
    (Oracle only) If the database is configured for the UTF8 or AL32UTF8 character set (NLS_CHARACTERSET parameter) in a LATIN-1 locale and the CHAR schema is used as an underlying persistent schema, set the NLS_LENGTH_SEMANTICS parameter of the database to CHAR to prevent column field overflow. Set this parameter before you run the master schema script to set up the database schema.
  • (Oracle or SQL) If the database is configured for a multibyte locale or a non-LATIN-1 locale, when you set up the database schema, run the master script that corresponds to the nchar character set.
  • If you are using SQL Server, specify the appropriate collating sequence as described in Setting up an SQL Server database schema for TrueSight Server Automation.

To create an Oracle database user and database tablespaces

A database administrator must manually create a database user and database tablespaces, before installing TrueSight Server Automation (even if you are installing using the unified product installer). 

The create_oracle_instance.sql creates the database user (typically BLADELOGIC). In addition to creating the database user, this script creates the BLADELOGIC and BLADELOGIC_INDEX tablespaces and g rants privileges to the database user.

Note

For information about how to use SQL *Plus to set up a database table space and database user with required permissions, see Walkthrough-Creating-the-TrueSight-Server-Automation-Database-for-Oracle. 

Perform the following steps to set up the Oracle database:

  1. Create the directory with the correct permissions to be used for the tablespace for the new schema. BMC recommends creating a directory on a disk separate from the Oracle system tables located at ../oradata/<SID> . For example:
    mkdir /u01/app/oracle/oradata/tssa
    chown oracle:dba /u01/app/oracle/oradata/tssa
  2. Copy installation scripts from the db_scripts directory (for example, ..<download_directory>/Disk1/files/configurations/db_scripts/) into the directory that you created for the tablespace. For details on the db_scripts directory, see Downloading-the-installation-files. The relevant scripts appear in the /db_scripts/oracle/ and /db_scripts/oracle/schema directories. 
  3. Change directory ( cd ) to /db_scripts/oracle/schema .

  4. Modify the create_oracle_instance.sql  script and change the path for data files to match the directory you created for the tablespace for the new schema.

    Note: Be sure to change both the CREATE TABLESPACE and ALTER DATABASE commands. You must change the path in four places.

  5. (Optional) If required by your company policy, you can modify the create_oracle_instance.sql script to revoke the RESOURCE and UNLIMITED TABLE SPACE privileges, and replace them with more granular privileges.
    • EXECUTE ON DBMS_LOCK (required for carrying out a handshake between TrueSight Server Automation database and the TrueSight Smart Reporting for Server Automation ETL during database clean up.)
    • CONNECT
    • CREATE VIEW

    • RESOURCE 

      Click here if your company policy does not allow you to grant the RESOURCE privilege to BLADELOGIC.

      If your company policy does not allow you to grant the RESOURCE privilege to BLADELOGIC, revoke the RESOURCE privilege and provide the following granular privileges instead:

      • CREATE TRIGGER
      • CREATE SEQUENCE
      • CREATE TYPE
      • CREATE PROCEDURE
      • CREATE CLUSTER
      • CREATE OPERATOR
      • CREATE INDEXTYPE
      • CREATE TABLE

    • SELECT ANY DICTIONARY

      Click here if your company policy does not allow you to grant the SELECT ANY DICTIONARY privilege to BLADELOGIC.

      If your company policy prohibits granting the SELECT ANY DICTIONARY privilege to BLADELOGIC, revoke this privilege and instead provide the following specific privileges on the tables to enable the cleanup and partitioning feature:
      GRANT SELECT ON DBA_SCHEDULER_RUNNING_JOBS TO BLADELOGIC;
      GRANT SELECT ON DBA_DATA_FILES TO BLADELOGIC;
      GRANT SELECT ON DBA_FREE_SPACE TO BLADELOGIC;
      GRANT SELECT ON DBA_ADVISOR_FINDINGS TO BLADELOGIC;
      GRANT SELECT ON DBA_ADVISOR_RECOMMENDATIONS TO BLADELOGIC;

    • UNLIMITED TABLESPACE

      Click here if your company policy does not allow you to grant the UNLIMITED TABLESPACE privilege to BLADELOGIC.

      If your company policy does not allow you to grant the UNLIMITED TABLESPACE privilege to BLADELOGIC, revoke the UNLIMITED TABLESPACE privilege and provide the following granular privilege on the relevant tabelspaces (BLADELOGIC and BLADELOGIC_INDEX) instead:
      Grant UNLIMITED QUOTA on <tablespace>

  6. Start SQL*Plus using a user name that has sysdba privileges. For example, you might enter the following commands:

    sqlplus /nolog
    SQL> connect sys/manager as sysdba

  7. Run the create_oracle_instance.sql  script by using the following commands:

    SQL> @/u01/app/oracle/oradata/tssa/create_oracle_instance.sql;
    SQL> exit

Back to top

To manually populate the Oracle database schema (not required if you using the unified product installer)

The following steps describe how to manually complete the Oracle database schema by populating it with the required database objects. 

Warning

Do not perform this task if you are installing or upgrading using the unified product installer. The unified product installer performs this task for you.

Ensure that while setting up the Oracle database user for TrueSight Server Automation, you have created a directory for the Oracle tablespace of the new schema and have copied installation scripts provided by BMC.

  1. Change ( cd ) to /db_scripts/oracle .

  2. Log on to the TrueSight Server Automation database with the BladeLogic user and password you just created with the create_oracle_instance.sql script. Start sqlplus with the following values:

    Variable

    Value

    <bl_user>

    Logon name of the TrueSight Server Automation database user.

    <bl_pwd>

    TrueSight Server Automation database user password. Do not specify a password that begins with an equal sign (=).

    <bl_tns_entry>

    tns entry for the BMC BladeLogic database instance

    For example:
     sqlplus BLADELOGIC/bmcAdm1n@ORA11G

  3. Start a log and run the master script that corresponds to the character sets for which your database is configured.

    SQL> spool create_bladelogic_schema.log
    SQL> start <master_script.sql> <BL_DATA> <BL_INDEX>
    SQL> spool off

    Replace the variables with the following values:

    Variable

    Value

    <master_script.sql>

    oracle_master.sql if your database is configured for char/varchar character sets or oracle_nchar_master.sql if your database is configured for nchar/nvarchar character sets

    <BL_DATA>

    TrueSight Server Automation data tablespace name. This name must match the data tablespace name in the create_oracle_instance.sql script (by default, BLADELOGIC).

    <BL_INDEX>

    TrueSight Server Automation index tablespace name. This name must match the index tablespace name in the create_oracle_instance.sql script (by default, BLADELOGIC_INDEX).

  4. After executing the master script, check the create_bladelogic_schema.log file for errors.

BMC does not recommend, nor does it support database schema changes.

Back to top

To optimize an Oracle database for TrueSight Server Automation

BMC provides the following recommendations to optimize the performance of an Oracle database serving TrueSight Server Automation:


Recommendations for gathering statistics

Warning

Although Oracle 10g and later versions provide an automatic job to gather statistics in the entire database, we recommend that you exclude the BMC BladeLogic database schema from that job and instead use the statistics gathering package provided with BMC BladeLogic. Details about the package are described in the following sections.

As with other products which use an Oracle database, some performance problems in a TrueSight Server Automation environment can be caused by stale Oracle statistics on the TrueSight Server Automation schema, due to changing data volumes or changes in column values. 

BMC strongly recommends that you gather statistics for the reports data warehouse and the core TrueSight Server Automation database and then clear the query caches after each data warehouse load. 

To facilitate the running of the statistics gathering command, TrueSight Server Automation has a stored procedure. After the stored procedure is created in the TrueSight Server Automation schema, you can easily execute the procedure to gather statistics on a regular basis. BMC recommends that you gather statistics one time per week. Running the procedure more frequently than one time per week does not yield any additional performance improvement. 

The statistics gathering procedure supplied by TrueSight Server Automation:

  • Collects Oracle optimizer statistics on the tables and indexes for the TrueSight Server Automation database at regular intervals.
  • Generates a PL/SQL script to collect statistics, which can be reviewed and modified (if necessary) before execution.

The following table presents more information about the various scripts available in TrueSight Server Automation for performing these procedures:

Objective

Details on TrueSight Server Automation scripts

Location of the TrueSight Server Automation statistics gathering package

The statistics gathering stored procedures are located in the ..\db_scripts\oracle\utility\gather_schema_stats folder, which is created when you extract TSSA<version>-<platform>.zip. For information about this file, see Downloading-the-installation-files.
The folder contains the following items:

  • bl_gather_schema_stats_sp.sql - Loads the BL_GATHER_SCHEMA_STATS stored procedure into the TrueSight Server Automation database; the procedure can either gather the statistics at the time of invocation, or send out PL/SQL commands for statistics gathering. The stored procedure is loaded automatically during the product installation or upgrade, so you do not have to manually load it.
  • gather_bl_schema_stats.sql - A sample script that illustrates how to invoke the stored procedure to collect statistics immediately.
  • generate_gather_schema_stats_script.sql - A sample script that illustrates how to invoke the stored procedure to generate a list of PL/SQL commands to gather statistics.
  • readme.txt - A detailed read-me file describing logic, usage, and so on.

Note: Before you run the script, ensure that you have met the following prerequisites:

  • Access to a fully operational computer with Oracle SQL*Plus installed that can connect remotely or locally to the TrueSight Server Automation database.
  • Oracle schema owner credentials for the TrueSight Server Automation database.

  • You can also run scripts and invoke the stored procedure as a user with DBA privileges. In this case, you must know the name of the schema owner for the TrueSight Server Automation database, and you must change the current schema before you run scripts or invoke the stored procedure using the ALTER SESSION SET CURRENT_SCHEMA command. For example:

    SQL> ALTER SESSION SET CURRENT_SCHEMA="BLADELOGIC";
    SQL> @gather_bl_schema_stats.sql

Gathering statistics at the time of invocation

To gather statistics immediately, perform any of the following:

  • Invoke the BL_GATHER_SCHEMA_STATS stored procedure directly from a PL/SQL block, or from any piece of code that is capable of calling Oracle stored procedures.

  • Run the sample script gather_bl_schema_stats.sql in SQL*Plus. You can either execute the script as is, or edit the script to modify the stored procedure input parameters. The following is an example of script execution.

    SQL>@gather_bl_schema_stats.sql
    Finished collecting statistics. Time elapsed: +00 00:01:43.7780

The following list describes the stored procedure input parameters.

  • p_threshold_rows - Threshold based on the number of rows, the default is one million rows.
  • p_threshold_bytes - Threshold based on the table size, the default is one hundred mega bytes.
  • p_no_invalidate - Indicates whether to invalidate the child cursors currently cached in the SGA.
  • p_print_only - Instead of gathering statistics, send out PL/SQL commands for each table (this parameter requires serveroutput to be enabled on the client and set to unlimited size).

Generating a script to gather statistics at later time

Instead of gathering statistics when you invoke the stored procedure, you might want to generate a PL/SQL script that can be reviewed, edited and executed at later time. 

The sample script generate_gather_schema_stats_script.sql provides an illustration of how to use a script to gather statistics at a later time. You can use the sample script as is or modify it to better suit your needs. The sample script creates a statistics collection script with an automatically generated name in the following format: 
gather_schema_stats_autogen_YYYY_MM_DD_HH24_MI_SS.sql.

  1. To generate a new statistics collection script that you can execute at a later time, execute the generate_gather_schema_stats_script.sql script. The following example shows how to execute the script and illustrates the information that is returned upon execution.

    SQL> @generate_gather_schema_stats_script.sql
    Successfully generated stats collection script:
    gather_schema_stats_autogen_2010_02_19_17_44_32.sql

  2. Review the script to check the statistics collection settings for the various tables.

  3. Execute the script to gather statistics at a later time, such as during the maintenance window or during light system usage:

    SQL> @gather_schema_stats_autogen_2010_02_19_17_44_32.sql
    Started stats collection at 2010-02-19 17:44:43
    Finished stats collection at 2010-02-19 17:46:05

Running gather statistics from the blcli

To run the gather statistics procedures from the BLCLI (for example, from a scheduled NSHScript Job), you can use the DBManager - blGatherSchemaStats BLCLI command.

Verifying that statistics are current

To see if statistics are current, you can access the Health and Value Dashboards and review the dates of database statistics there.

Alternatively, you can run a database diagnostic to see if the DBMS_STATS on the Oracle schema are stale based on a parameter that you specify. If the previous run of DBMS_STATS is older than the time frame you specified, you are prompted to run the BL_GATHER_SCHEMA_STATS stored procedure to update the statistics. You can also instruct the diagnostic to perform autoremediation by automatically invoking the stored procedure. 

The following example shows the command format you would use to run the DBMS_STATS_CHK diagnostic, while the following list describes the parameters available for the diagnostic.

dbdiagnostics runDiag diagId=1000006 STATSExpiresDays=7 STATSRemediateFlg=Y
  • STATSExpiresDays - Sets the time frame, in days, for the recommended frequency for updating statistics. The default value is 15 days.
  • STATSRemediateFlg - Controls autoremediation (execution of the of BL_GATHER_DBMS_STATS PROC script) to update the Schema statistics. Enter Y to turn on autoremediation; enter N to turn it off. The default value is N. Note: Remediation of the statistics could take a considerable amount of time, based on the size of the database on which the script is executed. Before executing the autoremediation of Schema STATS, consult with your DBA. If you use the Oracle STATS GATHER Package for database statistics, BMC recommends that you replace the automatic Oracle job for gathering statistics with the TrueSight Server Automation BL_GATHER_SCHEMA_STATS package.

To view the results of the diagnostic, run the dbdiagnostics command with the getResLastExec parameter, which displays the results of the last execution for this diagnostic.

dbdiagnostics getResLastExec diagId=1000006

For a description of all available database diagnostics, see Monitoring-and-diagnosing-issues-in-the-TrueSight-Server-Automation-environment.

Back to top

Recommendations for configuring the database

The following recommendations are for a 10 GB database. Scale the configuration recommendations up or down based on the relative size of your database.

Configuration parameter

Recommended value

db_cache_size

1 GB

log_buffer

512 KB

pga_aggregate_target

360 MB

undo tablespace size

2 GB

(Oracle 12c only) pga_aggregate_limit

2 x pga_aggregate_target

In addition, if you have an Oracle 12c database, to improve the performance of database queries from TrueSight Server Automation, use the following two database parameters:

  • optimizer_adaptive_plans (default TRUE)
  • optimizer_adaptive_statistics (default FALSE)

For more information, see optimizer-adaptive-features-in-oracle-database-12c-release-2 in Oracle documentation.

Recommendations for physical implementation

To avoid disk contention and increase input-output throughput when accessing data, store the physical data and index data files in different tablespaces (that is, across multiple disks).


Where to go from here

Configuring-the-file-server

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*