Setting up an Oracle database

This topic provides instructions for setting up a fresh installation of the BladeLogic Portal database schema for an Oracle database.

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 linux.zip and extract its contents. The linux.zip file is included in the installation file used to install BladeLogic Portal on Linux. See Downloading-the-installation-files.

To set up an Oracle database schema

1. 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.

2. 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;

      <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 CONNECT TO <PORTAL_DB_NAME>;
      GRANT DBA TO <PORTAL_DB_NAME>;
      commit;
   3. Exit SQL *Plus
3. 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. To accomplish this, run the following command:

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

       

      <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/linux/BMCAutomationPortal/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> 
      

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

4. In Oracle, create an operational user who has no access to the data dictionary. Assign any name consistent with your naming standards.
   For example, create a user named BLPORTAL_OPER. If needed, use the following instructions to create the operational user:

   1. Start SQL*Plus and log in as the user created in step 2. For example, you might enter the following commands:

      sqlplus <portal_db_name>/<portal_db_password>@<SID>

   2. Create the operational user by running the following command:

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

      <PORTAL_OPERATIONAL_OWNER> is the name of the operational user you are creating, such as BLPORTAL_OPER.

      <PORTAL_DB_PASSWORD> is the name of the password for the database.
      <PORTAL_DB_NAME> is the name of the schema, such as BLPORTAL.

   3. Run the following commands to grant the operational user INSERT, UPDATE, DELETE, and SELECT on tables and EXECUTE on procedures:

      GRANT INSERT ANY TABLE TO <PORTAL_OPERATIONAL_OWNER>;
      GRANT UPDATE ANY TABLE TO <PORTAL_OPERATIONAL_OWNER>;
      GRANT DELETE ANY TABLE TO <PORTAL_OPERATIONAL_OWNER>;
      GRANT SELECT ANY TABLE TO <PORTAL_OPERATIONAL_OWNER>;
      GRANT EXECUTE ANY PROCEDURE TO <PORTAL_OPERATIONAL_OWNER>;
      GRANT CONNECT TO <PORTAL_OPERATIONAL_OWNER>;
      commit;

       

   4. Exit SQL *Plus.