Setting up the database

Before installing TrueSight Server Automation - Data Warehouse, you must set up the required databases. This section provides information about setting up a database for use with the product. These database setup instructions are intended for use by database administrators.

Recommendation

BMC recommends that the reports database uses a separate database instance than the TrueSight Server Automation database.

If you want to use TrueSight Server Automation - Data Warehouse in non-English locales, see To configure TrueSight Server Automation databases for a non-English locale in the TrueSight Server Automation documentation.

Prerequisites for the database setup

  • Ensure that you have the following schemas for Oracle or databases for SQL Server:
    • Reports data warehouse
    • ETL (ODI) master repository
    • ETL (ODI) work repository

  • Ensure that the database names meet the following requirements, when you are setting up your databases for the TrueSight Server Automation - Data Warehouse installation and adding sites after the installation.

    • Database names contain only Latin alphabet letters, numbers, and underscores (_).
    • Database names do not begin with a number.
    • Database names do not contain hyphens (-) in the names. While database names with hyphens work in TrueSight Server Automation, they do not work in TrueSight Server Automation - Data Warehouse.
  • Ensure that the database passwords meet the following requirements:
    • Database passwords do not begin with the equal sign (=) character.
    • Database names do not begin with an integer.
    • Database names do not contain spaces.

Ensure that the Oracle databases meet the following requirements:

  • The recommended setting for the NLS_LENGTH_SEMANTICS parameter for the reports data warehouse schema is CHAR.
  • Customers using non-English databases (such as Asian customers) should use the nchar character set. Customers using English databases (such as LATIN-1) can use the char character set.

  • The reports data warehouse schema type must align with the TrueSight Server Automation database in one of the following ways:

    • If you have one or more TrueSight Server Automation databases that use the char schema, the reports data warehouse must use the char schema.
    • If you have one or more TrueSight Server Automation databases that use the nchar schema, the reports data warehouse must use the nchar schema.
    • If you have multiple TrueSight Server Automation databases and some use the char schema and others use the nchar schema, the reports data warehouse must use the nchar schema. 

    See the instructions in Determining the schemas for the schema being used by the TrueSight Server Automation and TrueSight Server Automation - Data Warehouse database. 

  • The character set of the schema type of the TrueSight Server Automation database and reports data warehouse must match. For example, if the TrueSight Server Automation database uses LATIN-1, the reports data warehouse must use the same character set or its subset character set. If the TrueSight Server Automation database uses AL32UTF8, the reports data warehouse must use the same character set or its subset character set.
  • If you are using nchar schema, you must use a Unicode value of AL16UTF16 (NLS_NCHAR_CHARACTERSET=AL16UTF16).

  • The global_names parameter for the Oracle instance where TrueSight Server Automation - Data Warehouse is located must be set to false to ensure that the database link name generated by the configuration process is resolved correctly. (If the global_names parameter is not set to false, the primary site installation fails.)

    Note

    After you install the product, you can set this parameter to true if needed, as described in Setting-global_names-to-true-for-Oracle-databases.

  • The TrueSight Server Automation database user must have Execute permission on the DBMS_JOB and DBMS_LOCK Oracle packages. For verification instructions, see To verify the permissions.
  • Ensure that the tnsnames.ora file on the reports data warehouse database server contains the TNS entry of the TrueSight Server Automation database instance and the TNS name matches with the Service name.

Ensure that the SQL Server databases meet the following requirements:

  • The SQL Server reports data warehouse must use the TCP/IP protocol.
  • The SQL Server collation sequence for reports data warehouse must be case-insensitive.

For example, SQL_Latin1_General_CP1_CI_AS is a case-insensitive collation sequence.

TrueSight Server Automation - Data Warehouse does not support collation with case-sensitive schema.

  • Customers using non-English databases (such as Asian customers) should use the nchar character set. Customers using English databases (such as LATIN-1) can use the char character set.
  • The reports data warehouse schema type must align with the TrueSight Server Automation database in one of the following ways:
    • If you have one or more TrueSight Server Automation databases that use char schema, the reports data warehouse must use char schema.
    • If you have one or more TrueSight Server Automation databases that use nchar schema, the reports data warehouse must use nchar schema.
    • If you have multiple TrueSight Server Automation databases and some use char and others use nchar schema, the reports data warehouse must use nchar schema.
    • The character set value for the TrueSight Server Automation database and the character set value for the reports data warehouse must match.
  • The SQL Server collation sequence for the TrueSight Server Automation database and the reports data warehouse must be the same and be case-insensitive, regardless of whether the product is installed in an English or a non-English locale.

Note

SQL Server databases do not have a Unicode requirements.

To install the SQL Server database

  1. Install the SQL Server database. For more details, see the SQL Server manuals on the  Microsoft site (https://www.microsoft.com/en-in/).

  2. Create the SQL Server database and user. Select the appropriate collation character set according to your server locale.
  3. Configure the SQL Server remote query timeout by performing the following steps:
    1. On SQL Server database instance, right-click the instance name, and select Properties.
    2. In the left pane, click Connections.
    3. In the Remote server connections area, in the Remote query timeout field, enter 0 as the query timeout period.
    4. Click OK.
  4. Create tablespaces and users. For instructions, see Creating filegroups and user on the SQL Server database.

To install the Oracle database

  1. Install the Oracle database. For more details, see the Oracle manuals on the Oracle website (https://www.oracle.com/index.html).

  2. Create the Oracle database instance. For more details, see the Oracle manuals on the  Oracle website (https://www.oracle.com/index.html).

  3. Create tablespaces and users. For instructions, see Creating tablespaces and user on the Oracle database.

Note

An error might occur when the Oracle server is installed on the same computer with Oracle client because of incorrect sequence of the Oracle path in the PATHvariable. To avoid this issue, set the client entry before the Oracle server entry in the path variable. For example:

PATH : D:\oracle\product\11.2.0\client_1\bin;D:\oracle\product\11.2.0\db_1\bin;

To determine the schema being used by the TrueSight Server Automation database

  1. Log on to SQL Plus as any user (for example, system).

  2. Type the following command:

     select data_type from user_tab_columns where table_name = 'SYSTEM_PROPERTY' and column_name like 'NAME' 

    If the schema is char type, VARCHAR2 is returned as the output. If the schema is nchar type, NVARCHAR2 is returned as the output.

To determine the schema being used by the TrueSight Server Automation - Data Warehouse database

  1. Log on to SQL Plus as any user (for example, system).

  2. Type the following command:

    select * from user_tab_columns where table_name = 'BL_SITE' and column_name like 'NAME'

    If the schema is char type, VARCHAR2 is returned as the output. If the schema is nchar type, NVARCHAR2 is returned as the output.

To ensure that the TrueSight Server Automation database user has the Execute permission on these packages

  1. Log on to SQL Plus as SYS on the Oracle database instance where the TrueSight Server Automation user is located.

  2. Type the following commands:

    Grant execute on dbms_job to <TrueSightServerAutomationDatabaseUser>
    Grant execute on dbms_lock to <TrueSightServerAutomationDatabaseUser>

    <TrueSightServerAutomationDatabaseUser> is the TrueSight Server Automation database user name.

To verify that the database is Unicode

  1. Log on to SQL Plus as any user (for example, system).

  2. Type the following command:

    select parameter, value from nls_database_parameters where parameter='NLS_CHARACTERSET'

    Verify that a Unicode value is returned (AL32UTF8 or AL16UTF16). If a non-Unicode value is returned, create a new Unicode database and specify a Unicode value of AL32UTF8 or AL16UTF16. For instructions, see the database software documentation.

Supported scenarios for secondary TrueSight Server Automation sites

If you plan to use a system arrangement in which a secondary site uses a different character set, refer to the following scenarios for guidelines about supported setups.

The following setup is supported:

  • Application Server 1 uses char.
  • Application Server 2 uses nchar.
  • TrueSight Server Automation - Data Warehouse server 1 uses nchar.

TrueSight Server Automation - Data Warehouse server 1 cannot use char because you cannot run ETL from an nchar (Application Server 2 database) database to a char database (TrueSight Server Automation - Data Warehouse server 1 reports data warehouse)

 

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