Creating Oracle tablespaces

If you are using Oracle as your database, a database administrator (DBA) must create two tablespaces before you install BMC Digital Workplace Catalog:

The system database to store the shared application data
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.

Before you begin

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

Note

Note the following standard practices when using Oracle database servers:

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 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
Configure your Oracle server as described on the Configuring Oracle databases page and ensure that the NLS_CHARACTERSET parameter is set to AL32UTF8

To install BMC Digital Workplace Catalog, you need to create a database first. Before installing BMC Digital Workplace Catalog, take into account the following considerations:

For the BMC Digital Workplace Catalog fresh deployment, the Application Database Dump is provided for setting up your system database.
The login has a default user name / password (Demo / password).
Schema creation is no longer required as part of the post-installation process because the database dump has a pre-created schema.
By default, restoring the Oracle backup creates the DWPCAdmin user/schema and is assigned to "temp" Temporary tablespace. If required, you can create custom temporary table space and assign it to the DWPCAdmin user.

To set up the system/application database and user

Download the DWPCatalog2002OracleGoldenDB.dmp file from BMC Electronic Product Distribution site. For more information, see Downloading-the-BMC-Digital-Workplace-Catalog-installation-files.
Copy the Oracle dump file to your data_pump_dir directory.
Tip
Use the following command to identify the path of the Oracle data_pump_dir:
SELECT directory_name, directory_path FROM dba_directories WHERE directory_name='DATA_PUMP_DIR';
Execute the following commands for creating the database and restoring the dump from the previous step.
Tip
If you are attempting a reinstall of BMC Digital Workplace Catalog, you must prefix the following commands to remove the tablespace and user:
DROP USER DWPCAdmin cascade; DROP TABLESPACE "DWPCatalog" INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

SQL statement boilerplate to set up the Oracle database

# Create the admin role that will contain the administrative permissions
CREATE ROLE DWPCAdminRole 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 DWPCAdminRole;
COMMIT;

# Create application data tablespace
CREATE TABLESPACE DWPCatalog DATAFILE 'DWPCatalog.dbf' SIZE 500M REUSE AUTOEXTEND ON;

# Restore the database dump
!impdp system/bmcAdm1n directory=DATA_PUMP_DIR dumpfile=DWPCatalog2002OracleGoldenDB.dmp logfile=import.log full=y;

# Update the password for the application database admin user
alter user DWPCAdmin identified by password;

# Apply the admin role permissions to the application database admin user
GRANT DWPCAdminRole TO DWPCAdmin;

While logged in to 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 user

Before you can load the initial tenant data that enables users to log in to BMC Digital Workplace Catalog to manage the service catalog, you must create at least one tenant tablespace.

Use the following SQL statements to create the tenant database and database user.

SQL statement boilerplate to create the tenant tablespace and user

# Create tenant tablespace
CREATE TABLESPACE DWPCTenant DATAFILE ' DWPCTenant.dbf' SIZE 2000M REUSE AUTOEXTEND ON;
COMMIT;

# Create temporary tablespace
CREATE TEMPORARY TABLESPACE DWPCTemp TEMPFILE 'DWPCTemp.dbf' SIZE 4000M REUSE AUTOEXTEND ON;
COMMIT;

# Create tenant database admin user
CREATE USER DWPCTenantAdmin IDENTIFIED BY password DEFAULT TABLESPACE DWPCTenant TEMPORARY TABLESPACE DWPCTemp QUOTA UNLIMITED ON DWPCTenant;
COMMIT;

# Apply the admin role permissions to the tenant database admin user
GRANT DWPCAdminRole TO DWPCTenantAdmin;
COMMIT;

To verify the database user account access

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

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 DWPCAdmin/password

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>

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
------------------------------ --------- ---------
DWPCatalog ONLINE PERMANENT

Log out of sqlplus.

Example

SQL> exit

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 DWPCTenantAdmin/password

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
------------------------------ --------- ---------
DWPCTenant ONLINE PERMANENT

SQL> exit

Where to go from here

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