This documentation supports the 18.02 version of BMC Digital Workplace. To view the latest version, select the version from the Product version menu.

Creating Oracle tablespaces

If you are using Oracle as the database engine, a database administrator (DBA) must create two tablespaces before you install BMC Digital Workplace Catalog: the system database to store the shared application data, and the tenant database to store the organization-specific data related to the service catalog items.

A database administrator who can operate as a sysdba can copy the SQL statements from this topic into an Oracle database client, such as sqlplus, to create the databases and database users.

Note

Perform the following tasks to create tablespaces for a fresh installation of BMC Digital Workplace Catalog. If BMC is providing technical support to migrate your data from version 3.2 to a fresh installation of a later version of BMC Digital Workplace Catalog, BMC Support shall provide different steps for you to follow.

Before you begin

Complete the Database server and data storage creation parameters section of the Installation worksheets for BMC Digital Workplace Catalog.

Note the following standard practices when using Oracle database servers:

  • You can connect to an Oracle database by its service ID (SID) or service name.
  • Oracle restricts a SID to a maximum length of eight characters, case-insensitive.
  • Oracle service names can be any length, and are sometimes in the form of a fully qualified domain name.
  • BMC Digital Workplace Catalog can connect to only one reference to a database. To host an Oracle Real Application Cluster (RAC) you must install the Single Client Access Name (SCAN) service to access the database cluster by one service name.
    For more information, see the following resources on the Oracle website:
  • To improve system performance, the following procedures include the command to create a temporary tablespace for managing temporary tables and sort operations generated by the system and tenant database user queries.

To create the system/application tablespace and database user

  1. Copy the following SQL statement boilerplate that will create the temporary tablespace, system/application database, admin role alias, and database user into a text editor.

    SQL statement boilerplate to create the system/application tablespace and user
    # Create application temporary tablespace
    CREATE TEMPORARY TABLESPACE <BMC_TEMP_TABLESPACE> TEMPFILE '<BMC_TEMP_TABLESPACE>.dbf' SIZE 4000M REUSE AUTOEXTEND ON;
    COMMIT;
    
    # Create application data tablespace
    CREATE TABLESPACE <BMC_DATABASE_TABLESPACE> DATAFILE '<BMC_DATABASE_TABLESPACE>.dbf' SIZE 2000M REUSE AUTOEXTEND ON;
    COMMIT;
    
    # Create application database admin user
    CREATE USER <BMC_DATABASE_LOGIN> IDENTIFIED BY <BMC_DATABASE_PASSWORD> DEFAULT TABLESPACE <BMC_DATABASE_TABLESPACE> TEMPORARY TABLESPACE <BMC_TEMP_TABLESPACE> QUOTA UNLIMITED ON <BMC_DATABASE_TABLESPACE>;
    COMMIT;
    
    # Create the admin role that will contain the administrative permissions
    CREATE ROLE <BMC_DATABASE_ADMIN_ROLE> NOT IDENTIFIED;
    GRANT ALTER SESSION, CREATE CLUSTER, CREATE DATABASE LINK, CREATE SEQUENCE, CREATE SESSION, CREATE SYNONYM, CREATE TABLE, CREATE VIEW, CREATE PROCEDURE, CREATE TRIGGER, QUERY REWRITE TO <BMC_DATABASE_ADMIN_ROLE>;
    COMMIT;
    
    # Apply the admin role permissions to the application database admin user
    GRANT <BMC_DATABASE_ADMIN_ROLE> TO <BMC_DATABASE_LOGIN>;
    COMMIT;
    
  2. Replace the <VARIABLE_NAME> placeholders with the values collected in the installation worksheets.

    Example SQL statements to create the system/application tablespace and user
    CREATE TEMPORARY TABLESPACE dwp_db_temp TEMPFILE 'dwp_db_temp.dbf' SIZE 4000M REUSE AUTOEXTEND ON;
    COMMIT;
    
    CREATE TABLESPACE dwp_db_app DATAFILE 'dwp_db_app.dbf' SIZE 2000M REUSE AUTOEXTEND ON;
    COMMIT;
    
    CREATE USER dwp_admin_app IDENTIFIED BY dwp_password_app DEFAULT TABLESPACE dwp_db_app TEMPORARY TABLESPACE dwp_db_temp QUOTA UNLIMITED ON dwp_db_app;
    COMMIT;
    
    CREATE ROLE dwp_dbadmin_role NOT IDENTIFIED;
    GRANT ALTER SESSION, CREATE CLUSTER, CREATE DATABASE LINK, CREATE SEQUENCE, CREATE SESSION, CREATE SYNONYM, CREATE TABLE, CREATE VIEW, CREATE PROCEDURE, CREATE TRIGGER, QUERY REWRITE TO dwp_dbadmin_role;
    COMMIT;
    
    GRANT dwp_dbadmin_role TO dwp_admin_app;
    COMMIT;
    
  3. While logged into the database server in sqlplus, copy the entire block of commands from the text editor and paste it into the sqlplus shell.
    The commands should execute one at a time and provide feedback for each step.

To create the tenant tablespace and database user

 You must create at least one tenant tablespace before you can load the initial tenant data that enables users to log into BMC Digital Workplace Catalog to manage the service catalog. Repeat these steps for each of the tenants who plan to share the same BMC Digital Workplace Catalog application instance.

  1. Copy the following SQL statement boilerplate that will create the tenant database and database user into a text editor.

    SQL statement boilerplate to create the tenant tablespace and user
    # Create tenant database
    CREATE TABLESPACE <BMC_DATABASE_TENANT_TABLESPACE> DATAFILE '<BMC_DATABASE_TENANT_TABLESPACE>.dbf' SIZE 2000M REUSE AUTOEXTEND ON;
    COMMIT;
    
    # Create tenant database admin user
    CREATE USER <BMC_DATABASE_TENANT_USER> IDENTIFIED BY <BMC_DATABASE_TENANT_PASSWORD> DEFAULT TABLESPACE <BMC_DATABASE_TENANT_TABLESPACE> TEMPORARY TABLESPACE <BMC_TEMP_TABLESPACE> QUOTA UNLIMITED ON <BMC_DATABASE_TENANT_TABLESPACE>;
    COMMIT;
    
    # Apply the admin role permissions to the tenant database admin user
    GRANT <BMC_DATABASE_ADMIN_ROLE> TO <BMC_DATABASE_TENANT_USER>;
    COMMIT;
  2. Replace the <VARIABLE_NAME> placeholders with the values collected in the installation worksheets.

    Example SQL statements to create the tenant tablespace and user
    CREATE TABLESPACE dwp_db_tenant DATAFILE 'dwp_db_tenant.dbf' SIZE 2000M REUSE AUTOEXTEND ON;
    COMMIT;
    
    CREATE USER dwp_admin_tenant IDENTIFIED BY dwp_password_tenant DEFAULT TABLESPACE dwp_db_tenant TEMPORARY TABLESPACE dwp_db_temp QUOTA UNLIMITED ON dwp_db_tenant;
    COMMIT;
    
    GRANT dwp_dbadmin_role TO dwp_admin_tenant;
    COMMIT;
    
  3. While logged into the database server in sqlplus, copy the entire block of commands from the text editor and paste it into the sqlplus shell.
    The commands should execute one at a time and provide feedback for each step.

To verify the database user account access

Check that each of the newly-created users can log into the database by logging in to the database as each user.

  1. From the Linux command prompt, log in to sqlplus as the system/application tablespace user.

    Example of logging in as a newly created database user
    $ sqlplus dwp_admin_app/dwp_password_app
    
    SQL*Plus: Release 12.1.0.2.0 Production on Tue Aug 8 04:02:45 2017
    Copyright (c) 1982, 2014, Oracle.  All rights reserved.
    Connected to:
    Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
    
    SQL>
  2. From the SQL command prompt, review the tablespace that is assigned to the system/application tablespace user.

    Example of finding the tablespace owned by the database user
    SQL> SELECT TABLESPACE_NAME, STATUS, CONTENTS FROM USER_TABLESPACES;
    
    TABLESPACE_NAME                STATUS    CONTENTS
    ------------------------------ --------- ---------
    DWP_DB_APP                     ONLINE    PERMANENT
    
  3. Log out of sqlplus.

    Example
    SQL> exit 
  4. From the Linux command prompt, log in to sqlplus as the tenant tablespace user and review the tablespace that is assigned to the tenant tablespace user.

    Example of the three combined steps in one SQL block
    $ sqlplus dwp_admin_tenant/dwp_password_tenant
    
    SQL*Plus: Release 12.1.0.2.0 Production on Tue Aug 8 04:02:45 2017
    Copyright (c) 1982, 2014, Oracle.  All rights reserved.
    Connected to:
    Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
    
    SQL> SELECT TABLESPACE_NAME, STATUS, CONTENTS FROM USER_TABLESPACES;
    
    TABLESPACE_NAME                STATUS    CONTENTS
    ------------------------------ --------- ---------
    DWP_DB_TENANT                  ONLINE    PERMANENT
    
    SQL> exit
Was this page helpful? Yes No Submitting... Thank you

Comments