Creating a data warehouse database schema

To support the reporting functions provided by BMC Database Automation, you must create a schema on the data warehouse database where the data to create the reports will be stored. This topic describes how to create a data warehouse database schema.

Before you begin

Ensure the following:

Bash shell is set up.
Oracle Instant Client has been installed on the Manager.
Host has an Oracle database installed and configured with one of the supported versions.
Oracle database has all services running.
There are no port conflicts.
Steps below are performed as the Oracle owner on the host where the database is running and with the ORACLE_HOME and ORACLE_SID environment variables set appropriately.
(Applicable for version 8.9.01 and later) User tablespace for cmdb user is created.
(Applicable for version 8.9.01 and later) Temporary tablespace for cmdb user is created.

To create a new data warehouse database schema

Obtain the data_warehouse.zip file. The data_warehouse.zip file is included in the Additional Utilities component from the BMC Software Electronic Product Distribution (EPD) website. See Downloading-the-installation-files for more information.
Copy the data_warehouse.zip file from your computer to the server that hosts the data warehouse database.
The .zip file contains one top level directory (data_warehouse) and three sub-directories (export_data, export_tools, and patches).
Unpack the top-level directory contained in the data_warehouse.zip file to the current working directory and cd to the data_warehouse directory.
Information
Information (Applicable for version 8.9.01 and later)
The following steps are provided for creating a data warehouse database user for a CMDB database
(step 4), and creating a data warehouse database user and the database schema for CMDB on a pluggable database (step 5). Choose a relevant step you want to perform.
Create a data warehouse database user for CMDB database:
1. export_tools/create_repos_user.sh SYS_USER SYS_PASSWORD DWDB_USER DWDB_PASSWORD DWDB_DEFAULT_TABLESPACE DWDB_TEMP_TABLESPACE DIRECTORY_ALIAS DIRECTORY_PATH
  
  For example:
  export_tools/create_repos_user.sh sys xxx clarity_dwdb xxx USERS TEMP DWDB_EXP '/tmp/'
  Warning
  Note
  In the previous example, use the following values for the parameters:
  SYS_PASSWORD can be any value (the script connects with "as sysdba" and uses OS-level authentication).
  DWDB_USER and DWDB_PASSWORD are values for a new user and password.
  DIRECTORY_ALIAS is arbitrary, but it is used in the script without protection. Therefore, any value not already used as a directory name in Oracle (and without special characters) is acceptable.
  DIRECTORY_PATH should be set to your ../data_warehouse/export_data directory.
2. Enter this command:
  export_tools/import_cmdb.shDWDB_USER DWDB_PASSWORD DIRECTORY_ALIAS DWDB_DEFAULT_TABLESPACE
  
  For example:
  export_tools/import_cmdb.sh clarity_dwdb xxx DWDB_EXP USERS
(Applicable for version 8.9.01 and later) Create a data warehouse database schema and the user for CMDB on a pluggable database:

Change the directory to data_warehouse/export_tools.
setup.sh PDB_NAME LISTENER_PORT CMDB_USER CMDB_PASSWORD CMDB_DEFAULT_TABLESPACE CMDB_TEMP_TABLESPACE DIRECTORY_NAME DIRECTORY_PATH

The listener port is available at the default path, $ORACLE_HOME/network/admin/tnsnames.ora
For example:
cd BDA_8.9.00_Patch_1_Additional_Utilities/data_warehouse/export_tools./setup.sh PDATA1 1521 bda_obdb xxx cmdb_tablespace cmdb_temp_tablespace cmdb_create /home/oracle/data_warehouse/export_data

To update an existing data warehouse database schema

If you are upgrading to version 8.9 of BMC Database Automation and are running Agents over IPv6 protocol, you must update the data warehouse database schema. For more information, see Updating-the-data-warehouse-database-schema.

Where to go next

Configure the connection between the Manager and the data warehouse.