• Spaces
  • People
  • Help
    • BMC Support Central BMC Community BMC.com
    ×
    BMC TrueSight Capacity Optimization 10.7

    Page tree

    Skip to end of metadata
    Go to start of metadata

    If you do not have a sysdba password, you will need to manually set up the database users and tablespaces before you install the product.

    Note

    Use a Database Administrator account to execute the following steps only if it is not possible for the Database Administrator to provide you the sysdba password to automatically create users and tablespaces while you install TrueSight Capacity Optimization. If you follow the procedure given below, you must select the Database users and tablespaces have been created externally option while performing the BMC TrueSight Capacity Optimization installation.

    Related topics

    Preparing to install as a non-root user 

    Preparing for installation

    If you are an Oracle database administrator and you need or choose to manually set up or migrate database users and tablespaces in TrueSight Capacity Optimization, complete the following steps:

    1. Create or migrate tablespaces.
    2. Create or migrate users.
    3. Grant or verify privileges.

    For information about TrueSight Capacity Optimization tablespaces and users, see Database requirements.

    To manually set up database users and tablespaces when installing the product

    If you have Oracle-Managed Files enabled, and you are creating database users and tablespaces, you can use the create_users_and_tablespaces.sql example database creation script. For all other situations, you can download and modify the create_users_and_tablespaces_template_filesystem.sql template file to suit your Oracle DB datafile position.

    Note

    To view the contents of the attached script files, see Contents of create_users_and_tablespaces.sql and Contents of create_users_and_tablespaces_template_filesystem.sql. If needed, modify the database creation script according to your policies. In particular, to modify default database users, roles and tablespace names, edit the first section of the SQL script.

    Content of create_users_and_tablespaces_template_filesystem. sql

     Click to view content of create_users_and_tablespaces_template_filesystem. sql...
    SET DEFINE ON
-- Please modify following variables for role, user or tablespace name customizations

DEFINE ROLE_OWNER = 'BCO_OWN_ROLE'
DEFINE ROLE_DASHBOARD = 'BCO_DASH_ROLE'
DEFINE ROLE_REPORT = 'BCO_REP_ROLE'
DEFINE USER_OWNER = 'BCO_OWN'
DEFINE USER_OWNER_PWD = 'BmcCapac1ty_OWN'
DEFINE USER_DASHBOARD = 'BCO_DASH'
DEFINE USER_DASHBOARD_PWD = 'BmcCapac1ty_DASH'
DEFINE USER_REPORT = 'BCO_REP'
DEFINE USER_REPORT_PWD = 'BmcCapac1ty_REP'
DEFINE TBS_CATALOG = 'BCO'
DEFINE TBS_CATALOG_IDX = 'BCO_IDX'
DEFINE TBS_DATA = 'BCO_DATA'
DEFINE TBS_DATA_IDX = 'BCO_DATA_IDX'
DEFINE TBS_DASH= 'BCO_DASH'
DEFINE TBS_TEMP = 'TEMP'

-- Tablespaces
CREATE TABLESPACE &TBS_CATALOG DATAFILE
'/oradata/bco/cpit_01.dbf' SIZE 16M 
AUTOEXTEND ON NEXT 16M 
ONLINE 
PERMANENT 
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
/

CREATE TABLESPACE &TBS_CATALOG_IDX DATAFILE
'/oradata/bco/cpit_idx_01.dbf' SIZE 16M 
AUTOEXTEND ON NEXT 16M 
ONLINE 
PERMANENT 
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
/

CREATE TABLESPACE &TBS_DATA DATAFILE
'/oradata/bco/cpit_data_01.dbf' SIZE 128M AUTOEXTEND ON NEXT 128M,
'/oradata/bco/cpit_data_02.dbf' SIZE 128M AUTOEXTEND ON NEXT 128M,  
'/oradata/bco/cpit_data_03.dbf' SIZE 128M AUTOEXTEND ON NEXT 128M 
ONLINE 
PERMANENT 
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
/

CREATE TABLESPACE &TBS_DATA_IDX DATAFILE
'/oradata/bco/cpit_data_idx_01.dbf' SIZE 128M AUTOEXTEND ON NEXT 128M, 
'/oradata/bco/cpit_data_idx_02.dbf' SIZE 128M AUTOEXTEND ON NEXT 128M,  
'/oradata/bco/cpit_data_idx_03.dbf' SIZE 128M AUTOEXTEND ON NEXT 128M 
ONLINE 
PERMANENT 
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
/

CREATE TABLESPACE &TBS_DASH DATAFILE
'/oradata/bco/cpit_dash_01.dbf' SIZE 16M 
AUTOEXTEND ON NEXT 16M 
ONLINE 
PERMANENT 
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
/

-- Schema owner role
CREATE ROLE &ROLE_OWNER NOT IDENTIFIED
/
GRANT CREATE SESSION TO &ROLE_OWNER
/
GRANT CREATE SYNONYM TO &ROLE_OWNER
/
GRANT CREATE TABLE TO &ROLE_OWNER
/
GRANT CREATE VIEW  TO &ROLE_OWNER
/
GRANT CREATE SEQUENCE TO &ROLE_OWNER
/
GRANT CREATE TYPE TO &ROLE_OWNER
/
GRANT CREATE PROCEDURE TO &ROLE_OWNER
/
GRANT CREATE TRIGGER TO &ROLE_OWNER
/
GRANT CREATE OPERATOR TO &ROLE_OWNER
/
GRANT SELECT ON DBA_DATA_FILES TO &ROLE_OWNER
/

-- Reporting role
CREATE ROLE &ROLE_REPORT NOT IDENTIFIED
/
GRANT CREATE SESSION TO &ROLE_REPORT
/
GRANT CREATE SYNONYM TO &ROLE_REPORT
/

-- Schema owner user
CREATE USER &USER_OWNER IDENTIFIED BY &USER_OWNER_PWD
 DEFAULT TABLESPACE &TBS_CATALOG
 TEMPORARY TABLESPACE &TBS_TEMP
 QUOTA UNLIMITED ON &TBS_CATALOG
/
GRANT &ROLE_OWNER TO &USER_OWNER
/
GRANT &ROLE_REPORT TO &USER_OWNER
/
ALTER USER &USER_OWNER DEFAULT ROLE ALL
/
ALTER USER &USER_OWNER QUOTA UNLIMITED ON &TBS_CATALOG
/
ALTER USER &USER_OWNER QUOTA UNLIMITED ON &TBS_CATALOG_IDX
/
ALTER USER &USER_OWNER QUOTA UNLIMITED ON &TBS_DATA
/
ALTER USER &USER_OWNER QUOTA UNLIMITED ON &TBS_DATA_IDX
/

-- Reporting user (limited set of grant)
CREATE USER &USER_REPORT IDENTIFIED BY &USER_REPORT_PWD
 TEMPORARY TABLESPACE &TBS_TEMP
/
GRANT &ROLE_REPORT TO &USER_REPORT
/
ALTER USER &USER_REPORT DEFAULT ROLE ALL
/

-- Dashboard schema role
CREATE ROLE &ROLE_DASHBOARD NOT IDENTIFIED
/
GRANT CREATE SESSION TO &ROLE_DASHBOARD
/
GRANT CREATE SYNONYM TO &ROLE_DASHBOARD
/
GRANT CREATE TABLE TO &ROLE_DASHBOARD
/
GRANT CREATE VIEW TO &ROLE_DASHBOARD
/
GRANT CREATE SEQUENCE TO &ROLE_DASHBOARD
/
GRANT CREATE TYPE TO &ROLE_DASHBOARD
/
GRANT CREATE PROCEDURE TO &ROLE_DASHBOARD
/
GRANT CREATE TRIGGER TO &ROLE_DASHBOARD
/
GRANT CREATE OPERATOR TO &ROLE_DASHBOARD
/

-- Dashboard user
CREATE USER &USER_DASHBOARD
  IDENTIFIED BY &USER_DASHBOARD_PWD
  DEFAULT TABLESPACE &TBS_DASH
  TEMPORARY TABLESPACE &TBS_TEMP
/
GRANT &ROLE_DASHBOARD TO &USER_DASHBOARD
/
ALTER USER &USER_DASHBOARD DEFAULT ROLE ALL
/
ALTER USER &USER_DASHBOARD QUOTA UNLIMITED ON &TBS_DASH
/

    Content of create_users_and_tablespaces.sql

     Click to view content of create_users_and_tablespaces.sql...
    SET DEFINE ON
 
-- Please modify following variables for role, user or tablespace name customizations
 
DEFINE ROLE_OWNER = 'BCO_OWN_ROLE'
DEFINE ROLE_DASHBOARD = 'BCO_DASH_ROLE'
DEFINE ROLE_REPORT = 'BCO_REP_ROLE'
 
DEFINE USER_OWNER = 'BCO_OWN'
DEFINE USER_OWNER_PWD = 'BmcCapac1ty_OWN'
DEFINE USER_DASHBOARD = 'BCO_DASH'
 
DEFINE USER_DASHBOARD_PWD = 'BmcCapac1ty_DASH'
DEFINE USER_REPORT = 'BCO_REP'
DEFINE USER_REPORT_PWD = 'BmcCapac1ty_REP'
DEFINE TBS_CATALOG = 'BCO'
DEFINE TBS_CATALOG_IDX = 'BCO_IDX'
 
DEFINE TBS_DATA = 'BCO_DATA'
DEFINE TBS_DATA_IDX = 'BCO_DATA_IDX'
DEFINE TBS_DASH= 'BCO_DASH'
 
DEFINE TBS_TEMP = 'TEMP'
 
-- Tablespaces
CREATE TABLESPACE _TBSCATALOG_ DATAFILE 
'_PATH_/_TBSCATALOG__01.dbf' SIZE 16M AUTOEXTEND ON NEXT 16M
ONLINE 
PERMANENT 
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
/

CREATE TABLESPACE _CATALOGIDX_ DATAFILE 
'_PATH_/_CATALOGIDX__01.dbf' SIZE 16M AUTOEXTEND ON NEXT 16M 
ONLINE 
PERMANENT 
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
/

CREATE TABLESPACE _TBSDATA_ DATAFILE 
'_PATH_/_TBSDATA__01.dbf' SIZE 128M AUTOEXTEND ON NEXT 128M,  
'_PATH_/_TBSDATA__02.dbf' SIZE 128M AUTOEXTEND ON NEXT 128M,  
'_PATH_/_TBSDATA__03.dbf' SIZE 128M AUTOEXTEND ON NEXT 128M 
ONLINE 
PERMANENT 
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
/

CREATE TABLESPACE _DATAIDX_ DATAFILE 
'_PATH_/_DATAIDX__01.dbf' SIZE 128M AUTOEXTEND ON NEXT 128M, 
'_PATH_/_DATAIDX__02.dbf' SIZE 128M AUTOEXTEND ON NEXT 128M,  
'_PATH_/_DATAIDX__03.dbf' SIZE 128M AUTOEXTEND ON NEXT 128M 
ONLINE 
PERMANENT 
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
/

CREATE TABLESPACE _TBSDASH_ DATAFILE 
'_PATH_/_TBSDASH__01.dbf' SIZE 16M AUTOEXTEND ON NEXT 16M 
ONLINE 
PERMANENT 
EXTENT MANAGEMENT LOCAL AUTOALLOCATE 
/
 
-- Schema owner role
CREATE ROLE &ROLE_OWNER NOT IDENTIFIED
/
GRANT CREATE SESSION TO &ROLE_OWNER
/
GRANT CREATE SYNONYM TO &ROLE_OWNER
/
GRANT CREATE TABLE TO &ROLE_OWNER
/
GRANT CREATE VIEW  TO &ROLE_OWNER
/
GRANT CREATE SEQUENCE TO &ROLE_OWNER
/
GRANT CREATE TYPE TO &ROLE_OWNER
/
GRANT CREATE PROCEDURE TO &ROLE_OWNER
/
GRANT CREATE TRIGGER TO &ROLE_OWNER
/
GRANT CREATE OPERATOR TO &ROLE_OWNER
/
GRANT SELECT ON DBA_DATA_FILES TO &ROLE_OWNER
/
grant select_catalog_role to &ROLE_OWNER
/
 
-- Reporting role
CREATE ROLE &ROLE_REPORT NOT IDENTIFIED
/
GRANT CREATE SESSION TO &ROLE_REPORT
/
GRANT CREATE SYNONYM TO &ROLE_REPORT
/
 
-- Schema owner user
CREATE USER &USER_OWNER IDENTIFIED BY &USER_OWNER_PWD
DEFAULT TABLESPACE &TBS_CATALOG
TEMPORARY TABLESPACE &TBS_TEMP
QUOTA UNLIMITED ON &TBS_CATALOG
/
GRANT &ROLE_OWNER TO &USER_OWNER
/
GRANT &ROLE_REPORT TO &USER_OWNER
/
ALTER USER &USER_OWNER DEFAULT ROLE ALL
/
ALTER USER &USER_OWNER QUOTA UNLIMITED ON &TBS_CATALOG
/
ALTER USER &USER_OWNER QUOTA UNLIMITED ON &TBS_CATALOG_IDX
/
ALTER USER &USER_OWNER QUOTA UNLIMITED ON &TBS_DATA
/
ALTER USER &USER_OWNER QUOTA UNLIMITED ON &TBS_DATA_IDX
/
 
-- Reporting user (limited set of grant)
CREATE USER &USER_REPORT IDENTIFIED BY &USER_REPORT_PWD
 TEMPORARY TABLESPACE &TBS_TEMP
/
GRANT &ROLE_REPORT TO &USER_REPORT
/
ALTER USER &USER_REPORT DEFAULT ROLE ALL
/
 
-- Dashboard schema role
CREATE ROLE &ROLE_DASHBOARD NOT IDENTIFIED
/
GRANT CREATE SESSION TO &ROLE_DASHBOARD
/
GRANT CREATE SYNONYM TO &ROLE_DASHBOARD
/
GRANT CREATE TABLE TO &ROLE_DASHBOARD
/
GRANT CREATE VIEW TO &ROLE_DASHBOARD
/
GRANT CREATE SEQUENCE TO &ROLE_DASHBOARD
/
GRANT CREATE TYPE TO &ROLE_DASHBOARD
/
GRANT CREATE PROCEDURE TO &ROLE_DASHBOARD
/
GRANT CREATE TRIGGER TO &ROLE_DASHBOARD
/
GRANT CREATE OPERATOR TO &ROLE_DASHBOARD
/
 
-- Dashboard user
CREATE USER &USER_DASHBOARD
  IDENTIFIED BY &USER_DASHBOARD_PWD
  DEFAULT TABLESPACE &TBS_DASH
  TEMPORARY TABLESPACE &TBS_TEMP
/
GRANT &ROLE_DASHBOARD TO &USER_DASHBOARD
/
ALTER USER &USER_DASHBOARD DEFAULT ROLE ALL
/
ALTER USER &USER_DASHBOARD QUOTA UNLIMITED ON &TBS_DASH
/

    3 Comments

    1. Kevin Joyce

       

    2. Nacho Capdepon

       

    3. Melody Locke

       

    © Copyright 2005-2021 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.