Step by Step to create the DWH Datawarehouse database under Oracle

Document ID : KB000045650
Last Modified Date : 14/02/2018
Show Technical Document Details

Summary: 

This document is a step by step of how to create the DWH database and user under the Oracle server with all the option needed to.

Instructions: 

Step A: Create the Database DWH

1. Open the Command Windows  as a Administrator under the Oracle server.

2. From the command line prompt to create the Database please run this line, it is verify important to know that the line run is case sensitive in the creation:

dbca -silent -createDatabase -templateName Data_Warehouse.dbc -gdbname DWH -sid DWH -responseFile NO_VALUE -characterSet AL32UTF8 -memoryPercentage 30 -emConfiguration LOCAL

NOTE: This creation with ask you for the password for the users : SYS, SYSTEM, DBSNMP, SYSMAN 

 

3. From the command line prompt to login using the SQLPLUS and after run the commands:

sqlplus system@DWH as sysdba

alter system set nls_date_format="YYYY-MM-DD HH24:MI:SS" scope=spfile;

alter system set nls_territory="AMERICA" scope=spfile;

alter system set nls_language="AMERICAN" scope=spfile;

alter system set cursor_sharing= "FORCE" scope=spfile;

alter system set nls_sort="BINARY" scope=spfile;

alter system set nls_comp="BINARY" scope=spfile;

alter system set query_rewrite_enabled="true" scope=spfile;

exit;

 

4. In order to create the Tablespaces needed, from the command line prompt to login using the SQLPLUS and after run the commands:

sqlplus sys@DWH as sysdba

 

Note 1: Run one at a time until it is create, only then create the next one, we have identified here.

Note 2: Please verify that the path is the correct one X:\app\Administrator\oradata\DWH\ to the one you have !!!

 

CREATE TABLESPACE "DWH_PPM_DATA_DIM" DATAFILE 'X:\app\Administrator\oradata\DWH\DWH_PPM_DATA_DIM.DBF' SIZE 2G REUSE AUTOEXTEND ON NEXT 1G MAXSIZE 10G LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

 

CREATE TABLESPACE "DWH_PPM_DATA_FACT" DATAFILE 'X:\app\Administrator\oradata\DWH\DWH_PPM_DATA_FACT.DBF' SIZE 2G REUSE AUTOEXTEND ON NEXT 1G MAXSIZE 10G LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

 

CREATE TABLESPACE "DWH_PPM_INDX_DIM" DATAFILE 'X:\app\Administrator\oradata\DWH\DWH_PPM_INDX_DIM.DBF' SIZE 1G REUSE AUTOEXTEND ON NEXT 1G MAXSIZE 4G LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

 

CREATE TABLESPACE "DWH_PPM_INDX_FACT" DATAFILE 'X:\app\Administrator\oradata\DWH\DWH_PPM_INDX_FACT.DBF' SIZE 1G REUSE AUTOEXTEND ON NEXT 1G MAXSIZE 4G LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

 

Step B: Create the user and grant the privileges for "PPM_DWH"

In order to create the user and add privileges needed, from the command line prompt to login using the SQLPLUS and after run the commands:

sqlplus sys@DWH as sysdba

CREATE USER PPM_DWH IDENTIFIED BY PPM_DWH DEFAULT TABLESPACE DWH_PPM_DATA_DIM TEMPORARY TABLESPACE TEMP PROFILE DEFAULT;

 

GRANT CREATE SESSION TO PPM_DWH;

GRANT ALTER SESSION TO PPM_DWH;

GRANT CREATE TABLE TO PPM_DWH;

GRANT CREATE TRIGGER TO PPM_DWH;

GRANT CREATE VIEW TO PPM_DWH;

GRANT CREATE MATERIALIZED VIEW TO PPM_DWH;

GRANT CREATE SYNONYM TO PPM_DWH;

GRANT QUERY REWRITE TO PPM_DWH;

GRANT CREATE PROCEDURE TO PPM_DWH;

GRANT UNLIMITED TABLESPACE TO PPM_DWH;

GRANT CREATE DATABASE LINK TO PPM_DWH;

GRANT CREATE JOB TO PPM_DWH;

GRANT CONNECT TO PPM_DWH;

GRANT RESOURCE TO PPM_DWH;

Exit;

 

Step C: Restore the DWH CA PPM database from the IMAGE

1. From the Image of the CA PPM version "CA PPM Install Media 14.X, DVD Master" you will have to copy the directory: 

DVD:\Database      or        ISO:\Database

to the local disk, the hold directory \Database

EXAMPLE :

          F:\Database    to    C:\SOFTWARE\Database

 

2. To set the the import directory under Oracle to do the restore, from the Windows command line prompt as a Administrator you have to run :

sqlplus system@DWH as sysdba

create directory import_directory as 'C:\Software\Database';

exit;

 

3. From the command line prompt to do the loading of the database you have to run: 

impdp system@DWH directory=import_directory transform=oid:n dumpfile=dwh_oracle_base.db schemas=ppm_dwh logfile=ppm_dwh_import.log

 

Best Practice:

In order to resolve invalid objects in the imported database, please run these steps, from the command line prompt to login using the SQLPLUS and after run the commands:

 

sqlplus system@DWH as sysdba

EXEC DBMS_UTILITY.COMPILE_SCHEMA(schema=>'DWH');