Creating Oracle tablespaces

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:
  • Oracle Single Client Access Name (PDF 610KB)
  • Introduction to Real Application Cluster
• 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.

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

Where to go from here

Performing-the-BMC-Digital-Workplace-Catalog-installation