×
 
 
  •  
$helper.renderConfluenceMacro('{bmc-global-announcement:$space.key}')

Unsupported content

 

This version of the documentation is no longer supported. However, the documentation is available for your convenience. You will not be able to leave comments.
BMC Decision Support for Server Automation 8.8 ... Installing Preparing for installation Setting up the installation environment Setting up the Oracle database

Creating tablespaces and users on Oracle

This topic describes how to create portal content and reports data warehouse tablespaces and users.

You can create tablespaces and users by using either of the following methods: 

Required tablespaces

The following table lists the Oracle tablespace names and purposes that are created by the downloaded scripts. You need to be aware of these tablespace names because the schema creation scripts use them.

Notes

  • You must create the tablespaces by using the same names that are specified in the following table. Creating the tablespaces with different names can impact the product execution.
  • The BSARA_DATA, BSARA_INDEX, BSARA_DATA2, BSARA_INDEX2, BSARA_DATA3, and BSARA_INDEX3 tablespaces should not be on the same physical disk as the BSARA_ETL_STAGE tablespace to prevent impact on the ETL performance of the reports data warehouse.

Oracle tablespaces

Database instance

Tablespace name

Purpose

2

BSARA_DATA

Storage of the reports data warehouse tables except for the Audit and Inventory tables

2

BSARA_INDEX

Storage of the reports data warehouse indexes except for the Audit and Inventory tables

2

BSARA_DATA2

Storage of the Inventory Snapshot tables

2

BSARA_INDEX2

Storage of the Inventory Snapshot indexes

2

BSARA_DATA3

Storage of the Audit tables

2

BSARA_INDEX3

Storage of the Audit indexes

2

BSARA_ETL_REPO

Storage of the ETL tool repository

2

BSARA_ETL_STAGE

Storage of the ETL staging tables that are created, used, and dropped by the ETL processes

1

BSARA_PORTAL

Storage of the portal content store

Required users

The following table lists the users that are created by the downloaded scripts. You can use different user names than those used in the table examples by modifying the script. Make a note of the user names for reference during the BMC Decision Support for Server Automation configuration. These users are created with a default password, sa. You can modify the password as required.

Oracle users

User

Description

Required privileges

BDSSA_DW

This user has the following responsibilities:

  • Is owner of the reports database objects.

  • Executes ETL jobs (using JDBC connectivity) by reading data from the BMC Server Automation database across the database link.

  • Connects to the IBM Cognos reports layer and executes report queries.

This user requires the following privileges:

  • connect
  • create database link
  • create procedure
  • create sequence
  • create synonym
  • create table
  • create trigger
  • create view
  • query rewrite
  • execute on DBMS_LOB

The user also needs an unlimited quota on the BSARA_DATA, BSARA_INDEX, BSARA_DATA2, BSARA_INDEX2, BSARA_DATA3, BSARA_INDEX3, and BSARA_ETL_STAGE tablespaces that are listed in Required tablespaces.

BDSSA_ETL_MASTER

This user has the following responsibilities:

  • Is owner of the ETL master repository objects.
  • Uses JDBC connectivity for ETL job execution.

This user requires the following privileges:

  • connect
  • create database link
  • create procedure
  • create sequence
  • create synonym
  • create table
  • create trigger
  • create view

The user also needs an unlimited quota on the BSARA_ETL_REPO tablespace that is listed in Required tablespaces.

BDSSA_ETL_WORK

This user has the following responsibilities:

  • Is owner of the ETL work repository objects.
  • Uses JDBC connectivity for ETL job execution.

This user requires the following privileges:

  • connect
  • create database link
  • create procedure
  • create sequence
  • create synonym
  • create table
  • create trigger
  • create view

The user also needs an unlimited quota on the BSARA_ETL_REPO tablespace that is listed in Required tablespaces.

BDSSA_PORTAL

This user is owner of the portal content store objects.

This user requires the following privileges:

  • grant connect
  • create database link
  • create procedure
  • create sequence
  • create synonym
  • create table
  • create trigger
  • create view

 

To create tablespaces and users from the scripts that are copied from the external files

  1. If you have not already done so, extract the contents of the bdssa88<servicepackVersion>external-files.zip into a temporary directory.
    The scripts for creating tablespaces and users are copied into the /Oracle directory.  
  2. Navigate to the Oracle directory.

  3. Open the create_portal_content_oracle_tablespaces_and_users.sql script and edit it to modify the Oracle data file paths and user passwords as necessary.

    Warning

    Do not modify the tablespace names.

  4. On the Unicode instance, log on to SQL Plus as system.

  5. Run the following script:

    start create_portal_content_oracle_tablespaces_and_users.sql


    This script creates the reports portal content tablespaces and users.

  6. Log out of SQL Plus.

  7. Open the create_oracle_tablespaces_and_users.sql script and edit it to modify the Oracle data file paths and user passwords as necessary.

    Warning

    Do not modify tablespace names.

  8. In the script, modify the value of the MAXSIZE parameter for the following tablespaces according to your sizing requirements.
    To calculate the sizing requirements, use the sizing worksheet as described in Sizing for new product installations and new BMC Server Automation installations.

    Note

    The sizing worksheet returns the tablespace size in GB. Convert it into MB before you modify the MAXSIZE parameter in the script.

    • BSARA_DATA
    • BSARA_INDEX
    • BSARA_DATA2 
    • BSARA_INDEX2
    • BSARA_DATA3 
    • BSARA_INDEX3
    • BSARA_ETL_STAGE
    • BSARA_ETL_REPO
  9. Save the script.

  10. Log on to the instance on which you want to create the reports data warehouse and log on to SQL Plus using a user name that has sysdba privileges. 
    For example, you can log on using the following commands:

    sqlplus /nolog
connect sys/<password>@<TNS_ENTRY> as sysdba

  11. Run the following script:

    @ create_oracle_tablespaces_and_users.sql;


    This script creates the reports data warehouse tablespaces and users.

  12. Log out of SQL Plus.

To create tablespaces and users from the script that is downloaded during configuration

  1. Navigate to the directory in which you have downloaded the create_bdssa_oracle_users.sql script.

  2. Open the script and edit it to modify the Oracle data file paths and user passwords for both Oracle and portal content tablespace and users, as necessary.

    Warning

    Do not modify the tablespace names.

  3. Modify the value of the MAXSIZE parameter for the following tablespaces according to your sizing requirements.
    To calculate the sizing requirements, use the sizing worksheet as described in Sizing for new product installations and new BMC Server Automation installations.

    Note

    The sizing worksheet returns the tablespace size in GB. Convert it into MB before you modify the MAXSIZE parameter in the script.

    • BSARA_DATA
    • BSARA_INDEX
    • BSARA_DATA2 
    • BSARA_INDEX2
    • BSARA_DATA3 
    • BSARA_INDEX3
    • BSARA_ETL_STAGE
    • BSARA_ETL_REPO

  4. On the Unicode instance, log on to SQL Plus as system .

  5. From the create_bdssa_oracle_users.sql script, copy and paste the section starting with CREATE TABLESPACE BSARA_PORTAL on the SQL prompt for creating portal content tablespaces.

  6. Press Enter.
    The portal content tablesapces are created. 

  7. From the create_bdssa_oracle_users.sql script, copy and paste the sections starting with CREATE USER BDSSA_PORTAL on the SQL prompt for creating portal content users.

  8. Press Enter.
    The portal content users are created. 
  9. Log out of SQL Plus.

  10. Log on to the instance on which you want to create the reports data warehouse and log on to SQL Plus using a user name that has sysdba privileges. 
    For example, you can log on using the following commands:

    sqlplus /nolog
connect sys/<password>@<TNS_ENTRY> as sysdba

  11. At the SQL prompt, copy and paste the sections starting with the following names for creating reports data warehouse tablespaces:

    • CREATE TABLESPACE BSARA_DATA 
    • CREATE TABLESPACE BSARA_INDEX
    • CREATE TABLESPACE BSARA_DATA2
    • CREATE TABLESPACE BSARA_INDEX2
    • CREATE TABLESPACE BSARA_DATA3
    • CREATE TABLESPACE BSARA_INDEX3
    • CREATE TABLESPACE BSARA_ETL_STAGE
    • CREATE TABLESPACE BSARA_ETL_REPO
  12. Press Enter.
    The reports data warehouse tablespaces are created. 
  13. At the SQL prompt, copy and paste the sections starting with the following names for creating reports data warehouse, and ETL work and master repository users:
    • CREATE USER BDSSA_DW
    • CREATE USER BDSSA_ETL_WORK
    • CREATE USER BDSSA_ETL_MASTER 
  14. Press Enter.
    The reports data warehouse, and ETL work and master repository users are created.
  15. Log out of SQL Plus.

This version of the documentation is no longer supported. However, the documentation is available for your convenience. You will not be able to leave comments.

Last modified by Yechezkel Schatz on Aug 23, 2016
scripts reports_schema users epd tablespaces task oracle creation prerequisites installation

Comments

Log in or register to comment.

Prerequisites for the Oracle database setup
Setting up the SQL Server database
© Copyright 2013 - 2017 BMC Software, Inc.
Legal notices

Powered by Atlassian Confluence and Scroll Viewport

© Copyright 2005-2024 BMC Software, Inc. Use of this site signifies your acceptance of BMC’s Terms of Use . BMC, the BMC logo, and other BMC marks are assets of BMC Software, Inc. These trademarks are registered and may be registered in the U.S. and in other countries.