Creating database objects for Oracle

Database prerequisites

The database Instance must already exist.
This procedure must be performed by a DBA user.

Let's start

Copy the DOIM_HOME/pkg-repo/pkg-base/db/ghibli-distro-db-<version>.tar.gz to the database host or to a client that can connect to the database.
Extract the package ghibli-distro-db-<version>.tar.gz.
Run the steps described in the following sections.

Please notice that some of the SQL scripts require parameters whose value must be the same as the value specified in the Domain Master Configuration (DMCFG). Here is a quick cross-reference between the two sets of parameters:

SQL script parameter	DMCFG parameter
ADMIN_USER	ceman_db_schema
APP_USER	CEMAN_DB_USR
APP_PASS	CEMAN_DB_PSW
KEYCLOAK_USER	KEYCLOAK_DB_SCHEMA

1. Create the CEMAN tablespaces

(01_CEMAN_PREPARE_TBLS.sql)

sqlplus <dba_user>/<dba_password>@<host_address>:<host_port>/<db_name>  
@01_CEMAN_PREPARE_TBLS.sql <space-separated script parameters>

Replace:

<dba_user> with the username of a DBA user
<dba_password> with the password of the DBA user
<host_address> with the IP address or the fully qualified domain name of the database host
<host_port> with the port the database is listening to
<db_name> with the database name

This is the list of the required space-separated script parameters:

tbls_d1_ge_data: tablespace name for generic data
tbls_d1_ge_data_path: absolute datafile path for generic data tablespace
tbls_d1_ge_indexes: tablespace name for generic indexes
tbls_d1_ge_indexes_path: absolute datafile path for generic indexes tablespace
tbls_d1_ru_data: tablespace name for runtime data
tbls_d1_ru_data_path: absolute datafile path for runtime data tablespace
tbls_d1_ru_indexes: tablespace name for runtime indexes
tbls_d1_ru_indexes_path: absolute datafile path for runtime indexes tablespace
tbls_d1_hi_data: tablespace name for historic data
tbls_d1_hi_data_path: absolute datafile path for historic data tablespace
tbls_d1_hi_indexes: tablespace name for historic indexes
tbls_d1_hi_indexes_path: absolute datafile path for historic indexes tablespace

2. Create the CEMAN users

(02_CEMAN_PREPARE_USERS.sql)

sqlplus <dba_user>/<dba_password>@<host_address>:<host_port>/<db_name>  
@02_CEMAN_PREPARE_USERS.sql <space-separated script parameters>

Replace:

<dba_user> with the username of a DBA user
<dba_password> with the password of the DBA user
<host_address> with the IP address or the fully qualified domain name of the database host
<host_port> with the port the database is listening to
<db_name> with the database name

This is the list of the required space-separated script parameters:

tbls_d1_ge_data: tablespace name for generic data
tbls_d1_ge_indexes: tablespace name for generic indexes
tbls_d1_ru_data: tablespace name for runtime data
tbls_d1_ru_indexes: tablespace name for runtime indexes
tbls_d1_hi_data: tablespace name for historic data
tbls_d1_hi_indexes: tablespace name for historic indexes
ADMIN_USER: username of the owner of objects (tables, views, …)
ADMIN_PASS: password for the owner of objects
APP_USER: username used by the application to connect to the database - see also the note below
APP_PASS: password for the application user

3. Create the CEMAN schema

(03_CEMAN_CREATE_SCHEMA.sql)

sqlplus <ADMIN_USER>/<ADMIN_PASS>@<host_address>:<host_port>/<db_name>  
@03_CEMAN_CREATE_SCHEMA.sql <space-separated script parameters>

Replace:

<ADMIN_USER> with the username of the owner of the objects created in the previous script
<ADMIN_PASS> with the password of the owner of the objects
<host_address> with the IP address or the fully qualified domain name of the database host
<host_port> with the port the database is listening to
<db_name> with the database name

This is the list of the required space-separated script parameters:

tbls_d1_ge_data: tablespace name for generic data
tbls_d1_ge_indexes: tablespace name for generic indexes
tbls_d1_ru_data: tablespace name for runtime data
tbls_d1_ru_indexes: tablespace name for runtime indexes

4. Create CEMAN workflow objects

(04_CEMAN_CREATE_SCHEMA_WORKFLOW.sql)

sqlplus <ADMIN_USER>/<ADMIN_PASS>@<host_address>:<host_port>/<db_name>  
@04_CEMAN_CREATE_SCHEMA_WORKFLOW.sql <space-separated script parameters>

Replace:

<ADMIN_USER> with the username of the owner of the objects
<ADMIN_PASS> with the password of the owner of the objects
<host_address> with the IP address or the fully qualified domain name of the database host
<host_port> with the port where the database is listening to
<db_name> with the database name

This is the list of the required space-separated script parameters:

tbls_d1_ge_data: tablespace name for generic data
tbls_d1_ge_indexes: tablespace name for generic indexes
tbls_d1_ru_data: tablespace name for runtime data
tbls_d1_ru_indexes: tablespace name for runtime indexes
tbls_d1_hi_data: tablespace name for historic data
tbls_d1_hi_indexes: tablespace name for historic indexes

5. Load CEMAN initial data

(05_CEMAN_INITIAL_DML.sql)

sqlplus <ADMIN_USER>/<ADMIN_PASSWORD>@<host_address>:<host_port>/<db_name>  
@05_CEMAN_INITIAL_DML.sql

Replace:

<ADMIN_USER> with the username of the owner of the objects
<ADMIN_PASSWORD> with the password of the owner of the objects
<host_address> with the IP address or the fully qualified domain name of the database host
<host_port> with the port the database is listening to
<db_name> with the database name

6. Assign CEMAN permissions to the application user

(06_CEMAN_GRANT_USER.sql)

sqlplus <dba_user>/<dba_password>@<host_address>:<host_port>/<db_name>  
@06_CEMAN_GRANT_USER.sql <space-separated script parameters>

Replace:

<dba_user> with the username of a DBA user
<dba_password> with the password of the DBA user
<host_address> with the IP address or the fully qualified domain name of the database host
<host_port> with the port the database is listening to
<db_name> with the database name

Here is the list of the required space-separated script parameters:

ADMIN_USER: username of the owner of objects (tables, views, …). The username must be UPPERCASE.
APP_USER: username used by the application to connect to the database. The username can be lowercase or UPPERCASE.

7. Create the KEYCLOAK tablespaces

(07_IAM_PREPARE_TBLS.sql)

sqlplus <dba_user>/<dba_password>@<host_address>:<host_port>/<db_name>  
@07_IAM_PREPARE_TBLS.sql <space-separated script parameters>

Replace:

<dba_user> with the username of a DBA user
<dba_password> with the password of the DBA user
<host_address> with the IP address or the fully qualified domain name of the database host
<host_port> with the port database is listening to
<db_name> with the database name

This is the list of the required space-separated script parameters:

tbls_kc_ge_data: tablespace name for keycloak data
tbls_kc_ge_data_path: absolute datafile path for keycloak data tablespace

8. Create the KEYCLOAK users

(08_IAM_PREPARE_USERS.sql)

sqlplus <dba_user>/<dba_password>@<host_address>:<host_port>/<db_name>  
@08_IAM_PREPARE_USERS.sql <space-separated script parameters>

Replace:

<dba_user> with the username of a DBA user
<dba_password> with the password of the DBA user
<host_address> with the IP address or the fully qualified domain name of the database host
<host_port> with the port the database is listening to
<db_name> with the database name

This is the list of the required space-separated script parameters:

tbls_kc_ge_data: tablespace name for keycloak data
KEYCLOAK_USER: username of the owner of KEYCLOAK objects (tables, views, …)
KEYCLOAK_PASSWORD: password for the owner of KEYCLOAK objects
ADMIN_USER: username of the owner of objects (tables, views, …)

9. Create the KEYCLOAK schema

(09_IAM_CREATE_SCHEMA.sql)

sqlplus <KEYCLOAK_USER>/<KEYCLOAK_PASSWORD>@<host_address>:<host_port>/<db_name>  
@09_IAM_CREATE_SCHEMA.sql <space-separated script parameters>

Replace:

<KEYCLOAK_USER> with the username of the owner of the KEYCLOAK objects (tables, views, …
<KEYCLOAK_PASSWORD> with the password for the owner of the KEYCLOAK objects
<host_address> with the IP address or the fully qualified domain name of the database host
<host_port> with the port where the database is listening to
<db_name> with the database name

This is the list of the required space-separated script parameters:

KEYCLOAK_USER: username of the owner of KEYCLOAK objects (tables, views, …)

10. Assign KEYCLOAK permissions to the application user

(10_IAM_GRANT_USER.sql)

sqlplus <dba_user>/<dba_password>@<host_address>:<host_port>/<db_name>  
@10_IAM_GRANT_USER.sql <space-separated script parameters>

Replace:

<dba_user> with the username of a DBA user
<dba_password> with the password of the DBA user
<host_address> with the IP address or the fully qualified domain name of the database host
<host_port> with the port the database is listening to
<db_name> with the database name

Here is the list of the required space-separated script parameters:

KEYCLOAK_USER: username of the owner of the KEYCLOAK objects (tables, views, …)
ADMIN_USER: username of the owner of the objects (tables, views, …). The username must be UPPERCASE.
APP_USER: username used by the application to connect to the database. The username can be lowercase or UPPERCASE.

11. Create the KEYCLOAK Triggers

(11_IAM_ADD_TRIGGERS.sql)

sqlplus <KEYCLOAK_USER>/<KEYCLOAK_PASSWORD>@<host_address>:<host_port>/<db_name>  
@11_IAM_ADD_TRIGGERS.sql <space-separated script parameters>

Replace:

<KEYCLOAK_USER> with the username of the owner of the KEYCLOAK objects (tables, views, …)
<KEYCLOAK_PASSWORD> with the password for the owner of the KEYCLOAK objects
<host_address> with the IP address or the fully qualified domain name of the database host
<host_port> with the port the database is listening to
<db_name> with the database name

This is the list of the required space-separated script parameters:

ADMIN_USER: username of the owner of the objects (tables, views, …)
KEYCLOAK_USER: username of the owner of the KEYCLOAK objects (tables, views, …)

12. Create the KEYCLOAK schema authorization

(12_IAM_CREATE_SCHEMA_AUTHORIZATION.sql)

sqlplus <ADMIN_USER>/<ADMIN_PASSWORD>@<host_address>:<host_port>/<db_name>  
@12_IAM_CREATE_SCHEMA_AUTHORIZATION.sql <space-separated script parameters>

Replace:

<ADMIN_USER> with the username of the owner of the objects
<ADMIN_PASSWORD> with the password of the owner of the objects
<host_address> with the IP address or the fully qualified domain name of the database host
<host_port> with the port the database is listening to
<db_name> with the database name

This is the list of the required space-separated script parameters:

KEYCLOAK_USER: username of the owner of the KEYCLOAK objects (tables, views, …)
ADMIN_USER: username of the owner of the objects
APP_USER: username used by the application to connect to the database. The username can be lowercase or UPPERCASE.

13. Load KEYCLOAK initial data

(13_IAM_INITIAL_DML.sql)

sqlplus <KEYCLOAK_USER>/<KEYCLOAK_PASSWORD>@<host_address>:<host_port>/<db_name>  
@13_IAM_INITIAL_DML.sql <space-separated script parameters>

Replace:

<KEYCLOAK_USER> with the username of the owner of the KEYCLOAK objects (tables, views, …)
<KEYCLOAK_PASSWORD> with the password for the owner of the KEYCLOAK objects
<host_address> with the IP address or the fully qualified domain name of the database host
<host_port> with the port the database is listening to
<db_name> with the database name

This is the list of the required space-separated script parameters:

KEYCLOAK_USER: username of the owner of the KEYCLOAK objects (tables, views, …)