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 Reference (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, …)