Setting up an Oracle database

BMC provides an automated approach to setting up a database as part of the overall installation for BladeLogic Portal (see Installing-a-compact-deployment-of-the-portal-on-Linux). However, if you prefer, you can set up an Oracle database manually using this procedure.

This topic provides instructions for setting up a fresh installation of the BladeLogic Portal database schema for an Oracle database, and includes the following sections:

• • About the procedure
  • Before you begin
  • Step 1: Create the tablespaces
  • Step 2: Create the user for the database owner
  • Step 3: Create the schema
  • Where to go next

About the procedure

The procedure describes how to create the schema for the portal database and then how to create an operational user who, for security reasons, is granted only limited access to data.

This procedure uses SQL scripts that are provided in installation files.

Before you begin

• Use Oracle 11.2 or later.
• Use a database formatted to use UTF-8 as the character set (NLS_CHARACTERSET=AL32UTF8).
  By default, Oracle does not create a database with the UTF-8 character set. When you create a new database, ensure that you select AL32UTF8 in the Oracle database setup.
• Obtain the installation file called BTD22.LIN64.tar.gz and extract its contents. The extraction creates a directory structure where the top level directory is called BladeLogicPortal.
  In versions earlier than 2.2.01, the file is called BLPortal22.LIN64.tar.gz.

Step 1: Create the tablespaces

Create two tablespaces, one for data and one for indexes.
Assign any name consistent with your naming standards, For example, create tablespaces named BLPORTAL and BLPORTAL_INDEX.

BMC recommends two tablespaces for performance reasons. The tablespace for data should be at least 16 GB, although unlimited size is preferable. Also, BMC recommends that a separate I/O channel serve each tablespace.

If needed, use the following instructions to create the tablespaces:

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

   sqlplus /nolog
   SQL> connect sys/<password_of_sys_user> as sysdba

2. Create a tablespace for the main data files by entering the following command:

   CREATE BIGFILE TABLESPACE <PORTAL_DB_NAME> DATAFILE '<DATAFILE_LOCATION>/<PORTAL_DB_NAME>.dbf' SIZE 5125M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

   <PORTAL_DB_NAME> is the name of the schema you are creating, such as BLPORTAL.
   <DATAFILE_LOCATION> is the path to the data file you are creating.
   Make sure the file location is accessible to the Oracle user. If the location is not accessible, grant the necessary permissions to the Oracle user.

3. Create a tablespace for the main index file by entering the following command:

   CREATE BIGFILE TABLESPACE <PORTAL_DB_NAME>_INDEX DATAFILE '<DATAFILE_LOCATION>/<PORTAL_DB_NAME>_index.dbf' SIZE 5125M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

   <PORTAL_DB_NAME> is the name of the schema you are creating.
   <DATAFILE_LOCATION> is the path to the data file you are creating.
   Make sure the file location is accessible to the Oracle user. If the location is not accessible, grant the necessary permissions to the Oracle user.

Step 2: Create the user for the database owner

Create a user who is the owner of the database and can create the schema. This user must have permissions to create tables, indexes, and constraints and have read/write access to the data dictionary. 

An easy way to create a user with the necessary permissions is to assign the DBA role to the user you create. For example, create a user called BLPORTAL and assign the DBA role to that user. 

If needed, use the following instructions to create the user:

1. Using SQL *Plus, run the following command:

   CREATE USER <PORTAL_DB_NAME> PROFILE DEFAULT IDENTIFIED BY <PORTAL_DB_PASSWORD> DEFAULT TABLESPACE <PORTAL_DB_NAME> TEMPORARY TABLESPACE TEMP ACCOUNT UNLOCK;

   where:
   <PORTAL_DB_NAME> is the name of the schema you are creating, such as BLPORTAL.
   <PORTAL_DB_PASSWORD> is the name of the password for the database.

2. Run the following commands:

   GRANT RESOURCE, CONNECT TO <PORTAL_DB_NAME>;
   GRANT UNLIMITED TABLESPACE TO <PORTAL_DB_NAME>;
   GRANT CREATE MATERIALIZED VIEW TO <PORTAL_DB_NAME>;
   GRANT CREATE VIEW TO <PORTAL_DB_NAME>;
   ALTER USER <PORTAL_DB_NAME> DEFAULT ROLE ALL;
   
3. Exit SQL *Plus.

Step 3: Create the schema

Create the schema for the portal by executing the oracle_master.sql script. 

1. As root (or a user with similar permissions), recursively grant the Oracle user access to all files needed for installation by running the following command:

   chown -R <user_name> <installation_file_location>./BladeLogicPortal/linux/Disk1/utility/oracle

    where:

   <user_name> is the Oracle user.
   <installation_file_location> is the location to which you extracted files from linux.zip.

2. Switch users to the Oracle user by running the following command:

   su - <user_name> 

3. In the installation files, cd to this directory: installation_file_location/BladeLogicPortal/linux/Disk1/utility/oracle. 

4. Run the following command:

   sqlplus <portal_db_name>/<schema_owner_password>@<SID> @oracle_master.sql <data_tablespace_name> <index_tablespace_name> 
   

   where:<portal_db_name>is the user created in step 2. 

   <schema_owner_password> is the password for the user created in step 2. 
   <SID> is the identifier for the Oracle site the user is accessing.
   <data_tablespace_name> is the tablespace for data, created in step 1.

   <index_tablespace_name> is the tablespace for indexes, created in step 1. 

Where to go next

Enable web services in BMC Server Automation, which is a prerequisite to installation. Then you are ready to install BladeLogic Portal on Linux.