Skip to content
Contact us Go to Customer Portal
  • There are no suggestions because the search field is empty.

Oracle - Target environment

Applies to
Platform: Oracle

An Oracle target environment needs PowerShell 7 and SQL*Plus on the machine that deploys, and one database user per layer plus one load-control user. Prepare it before you replace the placeholders and deploy the artifacts of an Oracle generator. Afterwards each user can connect and holds the privileges that deployment and loading need.

The steps apply to all Oracle generator configurations: Stage, Data Store, Data Vault, Data Mart and their combination. biGENIUS support does not cover the installation and configuration of Oracle beyond the example in this article.

Prerequisites

  • An Oracle database that the deploying machine reaches by host name, port and service name.
  • An administrator account that can create users and grant privileges, or a database administrator who creates the users for you.
  • For the helper script: the artifacts are generated and downloaded, and their placeholders are replaced.

Install the tools

  1. Install PowerShell 7. The scripts deploy.ps1 and execute.ps1 do not run in Windows PowerShell 5.1.
  2. Install SQL*Plus, for example the Basic and SQL*Plus packages of Oracle Instant Client.
  3. Add the SQL*Plus folder to the PATH environment variable and open a new PowerShell 7 window.
  4. Run sqlplus -v. SQL*Plus prints its release, for example SQL*Plus: Release 23.26.1.0.0 - Production.

Create the database users

The artifacts connect as one user per layer, for example Stage, Raw Vault and Business Vault, and with Native Load Control as one load-control user. Each user owns the objects of its layer, so its user name is also the schema name in the placeholder values. Deployment and native loading need at least these privileges:

  • Every user: CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE PROCEDURE and CREATE SEQUENCE, and quota on each tablespace its tables and indexes use.
  • Stage user with Database Links for Source Systems set to Create Database Links: CREATE DATABASE LINK.
  • Stage user with Do Not Use Database Links: SELECT on each source table or view.
  • Load-control user: EXECUTE on SYS.DBMS_LOCK.

CREATE SEQUENCE is needed because the load-control tables and some vault tables have identity columns. In Oracle AI Database 26ai (release 23.26), the roles CONNECT and RESOURCE together contain the five privileges of the first item, but no tablespace quota, no CREATE DATABASE LINK and no EXECUTE on SYS.DBMS_LOCK.

Create the users with the helper script

The artifacts contain the example script Helpers\create_development_user.sql, available since Generator 2.2. It creates each layer user and the load-control user with the default tablespace USERS and the temporary tablespace TEMP. It grants the roles RESOURCE and CONNECT, CREATE VIEW and unlimited quota on USERS. The Stage user also gets CREATE DATABASE LINK and CREATE ANY DIRECTORY. The load-control user also gets EXECUTE on DBMS_LOCK, CREATE ANY CONTEXT and CREATE PUBLIC SYNONYM.

  1. Open Helpers\create_development_user.sql of the replaced artifacts in a text editor.
  2. Replace each password after IDENTIFIED BY with the password of that user in replacement_config.json and save the file.

    The script sets each password to the user name. Replace the passwords before you run it.

  3. Open PowerShell 7 in the artifacts folder and connect as an administrator, for example sqlplus sys@//<host name>:<port>/<service name> as sysdba. Here <host name>, <port> and <service name> identify your database.
  4. Run @Helpers/create_development_user.sql. SQL*Plus prints User created., User altered. and Grant succeeded. for each user.

The script grants quota only on USERS. A model object whose Tablespace Table or Tablespace Index names another tablespace needs quota there as well. Without it, the deployment of that object fails with ORA-01950.

Provide the source data

The Stage layer reads the source systems through database links or directly, as set in Database Links for Source Systems (Project). To create source model objects from an Oracle schema, see Discover an Oracle database. The source system types each generator configuration accepts are listed in Source Systems per Generator Configurations.

Example

A Data Vault project with the layers Stage, Raw Vault and Business Vault uses the example users DWH_STG, DWH_RV, DWH_BV and LC. After the replacement, the helper script creates the load-control user like this:

CREATE USER LC IDENTIFIED BY LC;
ALTER USER LC
    DEFAULT TABLESPACE users
    TEMPORARY TABLESPACE temp
    ACCOUNT UNLOCK ;
GRANT RESOURCE, CONNECT TO LC;
ALTER USER LC DEFAULT ROLE "RESOURCE","CONNECT";
ALTER USER LC QUOTA UNLIMITED ON USERS;
GRANT CREATE VIEW TO LC;
GRANT EXECUTE ON DBMS_LOCK TO LC;
GRANT CREATE ANY CONTEXT TO LC;
GRANT CREATE PUBLIC SYNONYM TO LC;

Before you run it, replace IDENTIFIED BY LC with IDENTIFIED BY <password>, where <password> is the password of the load-control user in replacement_config.json.

Check the result

Connect as each user, for example sqlplus DWH_STG@//<host name>:<port>/<service name>. SQL*Plus prints Connected to: and the database release. Then run SELECT * FROM SESSION_PRIVS; and check that the CREATE privileges listed above appear.

Failure symptoms and recovery

The sqlplus command is not found

Message: SqlPlus is not installed, not recognized or the PATH environment variable is not properly configured
Cause: The SQL*Plus folder is not in PATH of the PowerShell window.
Fix: Add the folder to PATH, open a new PowerShell 7 window and check sqlplus -v.

The helper script cannot create the load-control user

Message: ORA-01920
Cause: The load-control user exists already, for example because the helper script of another project created it.
Fix: None needed. The script continues and creates the other users.

Related articles