Walkthrough: Creating the TrueSight Server Automation Database for Oracle

This topic walks you through the process of setting up an Oracle database to be used while installing TrueSight Server Automation on Linux.

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

Setting-up-an-Oracle-database-for-TrueSight-Server-Automation

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

Walkthrough-Setting-up-a-SQL-Server-database

Introduction

This topic is intended for system and database administrators preparing to install TrueSight Server Automation. You must have access to a database before running the BSA unified product installer or when manually installing TrueSight Server Automation. 

You have two distinct choices. Use the script to create the tablespaces and user, or create them manually, but do not combine them. 

  • You can use the create_oracle_instance.sql to create the database user (typically BLADELOGIC) and the BLADELOGIC and BLADELOGIC_INDEX tablespaces and grant privileges to the database user. For more information, see .
  • You can manually set up a database table spaces and database user with the required permissions. Your Oracle environment might require special permissions. You can modify these steps accordingly. For more information, see .

Note

For detailed information, see Setting-up-an-Oracle-database-for-TrueSight-Server-Automation.

What does this walkthrough show?

In this walk through, we primarily perform the following tasks:

  • Create a table space to hold the the TrueSight Server Automation schema
  • Create a database user
  • Grant the database permissions required to create the TrueSight Server Automation schema

When installing TrueSight Server Automation using the unified product installer, the installation program automatically populates a database schema. The installer accesses that database using the connection information that is defined in this walkthrough.

If you are installing TrueSight Server Automation and its database manually (rather than by using the unified product installer), you can use this procedure to set up a database and then later run the script that populates the database schema for TrueSight Server Automation (as described in To manually populate the Oracle database schema (not required if you using the unified product installer).

What do I need to do before I get started?

For this walkthrough, you must have Oracle database and SQL *Plus installed on the Linux machine you want to use as a database server. Your Oracle environment must be 100% clean, with no existing TrueSight Server Automation installations.

Click here for a list of supported Oracle databases.

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

  • 21c
  • 21c RAC
  • 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)

How to use a script to create the Oracle tablespaces and user

Run the create_oracle_instance.sql script if you do not want to manually create the tablespaces and user. BMC recommends this approach because it is less prone to error. 

 Log on to the Linux server you want to use as a database server and perform the following steps:

 

Step

Commands with examples

1

Create a bsa directory with the correct permissions to store the table spaces.

mkdir -p /u01/app/oracle/oradata/bsa
chown oracle:dba /u01/app/oracle/oradata/bsa

2

Copy the create_oracle_instance.sql script into the directory that you created for the tablespaces.

  1. Extract the TSSA<version>-LIN4.zip file that you downloaded from the EPD website to obtain the TSSA<version>-LIN64 folder.
    For example:
    unzip TSSA89-LIN64.zip -d /tmp/TSSA89-LIN64  
  2. Copy the create_oracle_instance.sql script from the db_scripts/oracle/schema directory (for example, ..<download_directory>/Disk1/files/configurations/db_scripts/oracle/schema) into the directory that you created for the tablespace. 
    For example:
    cp /data1/TSSA89-LIN64/Disk1/files/configurations/db_scripts/oracle/schema/create_oracle_instance.sql /u01/app/oracle/oradata/bsa

3

Change directory to the new bsa directory. where you copied the create_oracle_instance.sql script.

Change directory to the new bsa directory where you copied the create_oracle_instance.sql script.

 cd /u01/app/oracle/oradata/bsa

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.

Modify the BLADELOGIC user password as needed.

Modify both the CREATE TABLESPACE and ALTER DATABASE commands. You must change the path in four places.

As an option, modify the user password, for example, from PROFILE DEFAULT IDENTIFIED BY sa (sa is the default) to PROFILE DEFAULT IDENTIFIED BY <password>.

Click here to see an example of the modified script

--
--
-- Create Tablespace for Data
--
CREATE TABLESPACE BLADELOGIC LOGGING 
DATAFILE '/data1/oracle/product/databases/bsa/BLADELOGIC.ora' 
SIZE 200M DEFAULT 
STORAGE ( INITIAL 500M NEXT 100M MAXEXTENTS UNLIMITED PCTINCREASE 0);

-- Create table space for Index
--
CREATE TABLESPACE BLADELOGIC_INDEX LOGGING 
DATAFILE '/data1/oracle/product/databases/bsa/BLADELOGIC_INDEX.ora' 
SIZE 50M DEFAULT 
STORAGE ( INITIAL 500M NEXT 100M MAXEXTENTS UNLIMITED PCTINCREASE 0);

--
-- Create User
--
CREATE USER BLADELOGIC 
PROFILE DEFAULT IDENTIFIED BY system 
DEFAULT TABLESPACE BLADELOGIC 
TEMPORARY TABLESPACE TEMP ACCOUNT UNLOCK;

GRANT CONNECT TO BLADELOGIC;

--
-- GRANT RESOURCE privileges TO BSA schema owner
--
GRANT CREATE CLUSTER TO BLADELOGIC;
GRANT CREATE INDEXTYPE TO BLADELOGIC;
GRANT CREATE OPERATOR TO BLADELOGIC;
GRANT CREATE PROCEDURE TO BLADELOGIC;
GRANT CREATE SEQUENCE TO BLADELOGIC;
GRANT CREATE TABLE TO BLADELOGIC;
GRANT CREATE TRIGGER TO BLADELOGIC;
GRANT CREATE TYPE TO BLADELOGIC;

GRANT CREATE VIEW TO BLADELOGIC;
GRANT EXECUTE ON DBMS_LOCK TO BLADELOGIC;

GRANT CREATE SESSION TO BLADELOGIC;

--
-- The following privilege is required for BMC DBD, BMC DBM, BMC BDSSA
--
GRANT SELECT ANY DICTIONARY TO BLADELOGIC;

--
-- Although Resource grants unlimited tablespace, re-inforce unlimited tablespace 
-- via explicite grant. Need to execute the following when DBA/Resource role 
-- is revoked 
--
GRANT UNLIMITED TABLESPACE TO BLADELOGIC;

--
-- Alter datafile to autoextend
--
ALTER DATABASE
 DATAFILE '/data1/oracle/product/databases/bsa/BLADELOGIC.ora' 
AUTOEXTEND
 ON NEXT 250M;

ALTER DATABASE
 DATAFILE '/data1/oracle/product/databases/bsa/BLADELOGIC_INDEX.ora' 
AUTOEXTEND
 ON NEXT 50M;

5

(Optional) If required by your company policy, modify the tablespace privileges.

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

Use the command shown on the right to log on to SQL *Plus and connect to the Oracle database.

su - oracle
sqlplus /nolog

connect sys/<password>@<database_SID> as SYSDBA

 Replace the following variables appropriate values, as described below:

  • <password> is the password of the sys account of your database instance
  • <database_SID> is the SID of your database instance running on the Linux machine.

You are now connected to your database instance using SQL *Plus.

Click here to see an example of the command
su - oracle
sqlplus /nolog
connect sys/password@ORA121DB as SYSDBA

7

Run the script.

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

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

How to manually set up an Oracle database server and user

If your company policy requires special privileges or you want more granular control, you can manually create the Oracle tablespaces and user. 

Log on to the Linux server you want to use as a database server and perform the following steps:

 

Step

Commands with examples

1

Create a TrueSight Server Automation directory with the correct permissions to store the table space.

mkdir -p /u01/app/oracle/oradata/bsa
chown oracle:dba /u01/app/oracle/oradata/bsa

2

Use the command shown on the right to log on to SQL *Plus and connect to the Oracle database.

su - oracle
sqlplus /nolog

connect sys/<password>@<database_SID> as SYSDBA

Replace the following variables appropriate values, as described below:

  • <password> is the password of the sys account of your database instance
  • <database_SID> is the SID of your database instance running on the Linux machine.

You are now connected to your database instance using SQL *Plus.

Click here to see an example of the command
su - oracle
sqlplus /nolog
connect sys/password@ORA121DB as SYSDBA

3

Create a table space, which can be used by the unified product installer to set up the TrueSight Server Automation Schema.

CREATE TABLESPACE <Table_Space_Name> LOGGING

DATAFILE '<Path_To_Table_Space>'

SIZE 200M DEFAULT STORAGE (INITIAL 500M NEXT 100M MAXEXTENTS UNLIMITED PCTINCREASE 0);

Replace the following variables with appropriate values, as described below, as described below:

  • <Table_Space_Name> is the name of the table space you want to create.
  • <Path_To_Table_Space> is the path to the directory where you want to save the schema file (<Table_Space_Name>.dbf)

Click here to see an example of the command
CREATE TABLESPACE BLADELOGIC LOGGING
DATAFILE '/u01/app/oracle/oradata/bsa/BLADELOGIC.dbf'
SIZE 200M DEFAULT STORAGE (INITIAL 500M NEXT 100M MAXEXTENTS UNLIMITED PCTINCREASE 0);

4

Create a table space to index your TrueSight Server Automation schema.

Run the command shown on the right in SQL *Plus.

CREATE TABLESPACE <Index_Table_Space_Name> LOGGING

DATAFILE '<Path_To_Index_Table_Space>'

SIZE 50M DEFAULT STORAGE ( INITIAL 500M NEXT 100M MAXEXTENTS UNLIMITED PCTINCREASE 0);

Replace the following variables with appropriate values, as described below:

  • <Index_Table_Space_Name> is the name of the table space for the TrueSight Server Automation schema index you want to create
  • <Path_To_Index_Table_Space> is the path to the directory where you want to save the TrueSight Server Automation Index schema file (<Index_Table_Space_Name>.ora)

Click here to see an example of the command
CREATE TABLESPACE BLADELOGIC_INDEX LOGGING
DATAFILE '/u01/app/oracle/oradata/bsa/BLADELOGIC_INDEX.dbf'
SIZE 50M DEFAULT STORAGE ( INITIAL 500M NEXT 100M MAXEXTENTS UNLIMITED PCTINCREASE 0);

5

Create a database user and grant required permissions to the user.

Run the commands shown on the right in SQL *Plus, to create the database user and grant required permissions.

CREATE USER <DB_User>

PROFILE DEFAULT IDENTIFIED BY <DB_User_Password>

DEFAULT TABLESPACE <Table_Space_Name>

TEMPORARY TABLESPACE TEMP ACCOUNT UNLOCK;

GRANT CONNECT TO <DB_User>;

GRANT RESOURCE TO <DB_User>;

GRANT CREATE VIEW TO <DB_User>;

GRANT SELECT ANY DICTIONARY TO <DB_User>;

GRANT EXECUTE ON dbms_lock TO <DB_User>;

GRANT UNLIMITED TABLESPACE TO <DB_User>;

GRANT EXECUTE ON DBMS_LOB TO <DB_User>;

GRANT EXECUTE ON DBMS_SQL TO <DB_User>;

GRANT CREATE TABLE TO <DB_User>;

GRANT CREATE SEQUENCE TO <DB_User>;

GRANT CREATE TRIGGER TO <DB_User>;

GRANT CREATE PROCEDURE TO <DB_User>;

GRANT UNLIMITED TABLESPACE TO <DB_User>;

GRANT CREATE SESSION TO <DB_User>;

Replace the following variables with appropriate values, as described below:

  • <DB_User> is the name of the user you want to create to access the TrueSight Server Automation database
  • <DB_User_Password> is the password for the database user you want to create
  • <Table_Space_Name> is the name of the table space you want to create for the TrueSight Server Automation schema

Click here to see an example of the command
CREATE USER BLADELOGIC
PROFILE DEFAULT IDENTIFIED BY password
DEFAULT TABLESPACE BLADELOGIC
TEMPORARY TABLESPACE TEMP ACCOUNT UNLOCK;
GRANT CONNECT TO BLADELOGIC;
GRANT RESOURCE TO BLADELOGIC;
GRANT CREATE VIEW TO BLADELOGIC;
GRANT SELECT ANY DICTIONARY TO BLADELOGIC;
GRANT EXECUTE ON dbms_lock TO BLADELOGIC;
GRANT UNLIMITED TABLESPACE TO BLADELOGIC;
GRANT EXECUTE ON DBMS_LOB TO BLADELOGIC;
GRANT EXECUTE ON DBMS_SQL TO BLADELOGIC;
GRANT CREATE TABLE TO BLADELOGIC;
GRANT CREATE SEQUENCE TO BLADELOGIC;
GRANT CREATE TRIGGER TO BLADELOGIC;
GRANT CREATE PROCEDURE TO BLADELOGIC;
GRANT UNLIMITED TABLESPACE TO BLADELOGIC;

For more information about the privileges, see List-of-required-database-permissions. 

6

Alter the data files of the TrueSight Server Automation schema table space and the Index table space to auto extend.

Run the command shown on the right in SQL *Plus.

ALTER DATABASE

DATAFILE '<Path_To_Table_Space>'

AUTOEXTEND ON NEXT 250M;
ALTER DATABASE

DATAFILE '<Path_To_Index_Table_Space>'

AUTOEXTEND ON NEXT 50M;

Replace the following variables with appropriate values, as described below:

  • <Path_To_Table_Space> is the path to the directory where you want to save the TrueSight Server Automation schema, that is, <Table_Space_Name>.dbf
  • <Path_To_Index_Table_Space> is the path to the directory where you want to save the Index for the TrueSight Server Automation schema, that is, <Index_Table_Space_Name>.dbf
Click here to see an example of the command
ALTER DATABASE
DATAFILE '/u01/app/oracle/oradata/bsa/BLADELOGIC.dbf'
AUTOEXTEND ON NEXT 250M;
ALTER DATABASE
DATAFILE '/u01/app/oracle/oradata/bsa/BLADELOGIC_INDEX.dbf'
AUTOEXTEND ON NEXT 50M;

Wrapping it up

Congratulations. You have successfully set up an Oracle database server. You are now ready to run the unified product installer.

Where to go from here

Walkthrough-Installing-on-Linux-using-the-unified-installer 

 

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