Preparing to install without a sysdba password

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.

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_REPORT = 'BCO_REP_ROLE'
DEFINE USER_OWNER = 'BCO_OWN'
DEFINE USER_OWNER_PWD = 'BmcCapac1ty_OWN'
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_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
/
 
-- 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
/ 

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_REPORT = 'BCO_REP_ROLE'
  
DEFINE USER_OWNER = 'BCO_OWN'
DEFINE USER_OWNER_PWD = 'BmcCapac1ty_OWN'
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_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
/
  
-- 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
/ 
Was this page helpful? Yes No Submitting... Thank you

Comments

  1. Stefan Antelmann

    In the 2 SQL scripts we have some differences.



    1. create_users_and tablespaces_template_filesystem

      We don't list the permission for this role: SELECT_CATALOG_ROLE
    2. create_users_and tablespaces

      We list the permission SELECT_CATALOG_ROLE



    I think this permission belongs to all scripts, and not only to the second.

    Nov 15, 2017 07:55