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.

Before you begin

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

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

1. Download the DWPCatalog2002OracleGoldenDB.dmp file from BMC Electronic Product Distribution site. For more information, see Downloading the BMC Digital Workplace Catalog installation files.

2. 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';

3. 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;

# 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.

# 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;

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 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.

$ 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.

SQL> SELECT TABLESPACE_NAME, STATUS, CONTENTS FROM USER_TABLESPACES;

TABLESPACE_NAME                STATUS    CONTENTS
------------------------------ --------- ---------
DWPCatalog                     ONLINE    PERMANENT

Log out of sqlplus.

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.

$ 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