Oracle - Deployment

Applies to
Platform: Oracle

Deploy the generated artifacts of an Oracle project with deploy.ps1. The script runs SQL*Plus, creates the objects of every layer and the load control, and reports failed statements and invalid objects per layer. Afterwards the solution is ready to load.

Prerequisites

The Oracle target environment is prepared: PowerShell 7, SQL*Plus and the database users.
The artifacts are generated and downloaded.
The placeholders are replaced with the Oracle placeholder values.

Deploy the artifacts

Open PowerShell 7 in the artifacts folder, the folder that contains deploy.ps1. For example, type pwsh in the address bar of File Explorer.
With the default Deployment Options and Deploy LoadControl true, deploy.ps1 drops and recreates every generated table and the load-control tables. Their data and load history are lost. Before you deploy to a solution that holds data, clear Generate Drop Table Statements and set Deploy LoadControl to false.
Run .\deploy.ps1. The script prints => Executing with the name of each layer script, then the load-control scripts, and ends with Finished.
Read the result of each layer as described in Check the result.

deploy.ps1 connects as each layer user in turn and, with Native Load Control, as the load-control user. The order of the scripts is explained in Oracle - Artifacts.

Check the result

The deployment succeeded when every layer shows NUMBER_OF_FAILED_STATEMENTS 0 and Invalid objects: followed by no rows selected. The load control shows no rows selected for errors and for invalid objects. Each configuration script ends with PL/SQL procedure successfully completed. A layer without errors reads:

NUMBER_OF_FAILED_STATEMENTS
---------------------------
                          0

=> Check table SPERRORLOG for details.
=> Invalid objects:

no rows selected

To see a failed statement, connect as the layer user and run:

SELECT TIMESTAMP, MESSAGE, STATEMENT
FROM SPERRORLOG
ORDER BY TIMESTAMP DESC;

Example

The Data Vault project test.datavault.oracle deploys in about 20 seconds on a local database. The output for the Stage layer and the load control reads:

=> Executing SQL/L010_Stage.sql
=> Errors during deployment:

NUMBER_OF_FAILED_STATEMENTS
---------------------------
                          0

=> Check table SPERRORLOG for details.
=> Invalid objects:

no rows selected
...
=> Executing LoadControl/L000_Loadcontrol_Deployment.sql
=> Errors during deployment:

no rows selected

=> Invalid objects:

no rows selected

=> Executing LoadControl/L010_Stage_Configuration.sql

PL/SQL procedure successfully completed.

Redeploy and keep data

To deploy a changed project into a solution that already holds data:

In Project Settings, open the Additional Settings. In the Deployment group, clear Generate Drop Table Statements in Deployment Options.
In the Load Control group, set Deploy LoadControl to false. Keep Configure LoadControl true, so that new load objects are registered.
Generate, download and replace the placeholders, then run .\deploy.ps1.

The deployment then creates only the tables and indexes that do not exist yet and reports no error for the existing ones. Existing tables keep their rows and their columns. The load-control tables keep the configuration, the execution plans and the execution history. Views and loaders are replaced.

Column changes are not applied to existing tables. For a new column, the deployment reports failed statements with ORA-00904: invalid identifier and lists the loader under Invalid objects. At the next load this loader fails with ORA-04063, and the load control disables the loaders that depend on it. Change the table with ALTER TABLE, then run .\deploy.ps1 again.

Failure symptoms and recovery

The layers report Not connected

Message: SP2-0640: Not connected after => Errors during deployment: and => Invalid objects:, then Finished
Cause: SQL*Plus cannot connect with the host name, port, service name, user name or password of the placeholder values. deploy.ps1 does not print the connection error, and nothing is deployed.
Fix: Connect with the same values, for example sqlplus <user name>@//<host name>:<port>/<service name>. SQL*Plus 23 names the wrong value: ORA-12262 for the host name, ORA-12541 for the port, ORA-12514 for the service name and ORA-01017 for the user name or password. Correct the value in replacement_config.json, replace the placeholders again and deploy.

A layer reports failed statements with insufficient quota

Message: ORA-01950 in SPERRORLOG
Cause: The layer user has no quota on a tablespace that Tablespace Table or Tablespace Index names.
Fix: Grant quota on that tablespace, for example ALTER USER <user name> QUOTA UNLIMITED ON <tablespace>;, and deploy again.

Tables with identity columns fail with insufficient privileges

Message: ORA-01031: insufficient privileges
Cause: The user lacks CREATE SEQUENCE. The load-control tables and some vault tables have identity columns.
Fix: Grant CREATE SEQUENCE to the user and deploy again.

Stage views fail because a source table does not exist

Message: ORA-00942: table or view does not exist
Cause: With Do Not Use Database Links, the Stage user has no SELECT on the source table.
Fix: Grant SELECT on the source objects to the Stage user and deploy again.

Loaders of a layer are invalid

Message: ORA-00942 for the tables of the previous layer
Cause: user_name and schema_name of a layer or of the load control differ. The grants go to the user, the objects to the schema.
Fix: Set both to the same user, replace the placeholders again and deploy.

No deployment script is found

Message: No deployment script found in <folder>
Cause: PowerShell was started in another folder.
Fix: Start PowerShell 7 in the artifacts folder and run .\deploy.ps1 again.

For failures in other steps, see Troubleshoot generation, deployment and load failures.

Release differences

Generator 2.2: the artifacts contain the helper script Helpers\create_development_user.sql, see Generators Release - 2.2.0.