Walkthrough: Creating the BladeLogic Database for Oracle

This topic walks you through the process of setting up an Oracle database to be used while installing BMC Server Automation (BSA) 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:

Setting up a SQL Server database and user for BMC Server Automation

Walkthrough: Setting up a SQL Server database

Introduction

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

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 How to use a script to create the Oracle tablespaces and user.
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 How to manually set up an Oracle database server and user.

Note

For detailed information, see Setting up an Oracle database for BMC 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 BSA schema
Create a database user
Grant the database permissions required to create the BSA schema

When installing BSA 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 BSA 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 BSA (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 BSA installations.

Click here for a list of supported Oracle databases.

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

11g R2
11g R2 RAC
12c
12c RAC

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

Note

The following databases do not have support for IPv6 in BMC 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 BMC Server Automation, before you install BMC 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:

On the database server, ensure that the ORACLE_HOME variable is set to the 11.2.0.2 installation directory.
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

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 /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.	Extract the BBSA89-LIN4.zip file that you downloaded from the EPD website to obtain the BBSA89-LIN64 folder. For example: `unzip BBSA89-LIN64.zip -d /tmp/BBSA89-LIN64` 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/BBSA89-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;` `--` `-- 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 BMC Server Automation database and the BMC BladeLogic Decision Support 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 bsa directory with the correct permissions to store the table space.	`mkdir /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 BSA 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 BSA 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 BSA 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 BSA schema index you want to create `<Path_To_Index_Table_Space>` is the path to the directory where you want to save the BSA 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>;` 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 BSA 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 BSA 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 BSA 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 BSA 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 BSA 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