Oracle Database Setup

📄 Overview

GermainUX supports Oracle Database as an on-premise relational datastore.

Oracle may be appropriate when:

Elasticsearch remains the preferred datastore for most new, high-volume deployments. Review the Datastore Recommendation and Benchmark before selecting Oracle.

⚙️ Supported Capabilities

Oracle Database can provide:

The selected Oracle edition and configuration must support the features required by the GermainUX deployment.

📋 Before You Begin

Confirm with Germain Software:

Supported versions may include Oracle 11g, 12c, 18c, and 19c Enterprise Edition, depending on the GermainUX release. Confirm compatibility before installation or upgrade.

📦 Requirements

Prepare:

See:

🗄️ GermainUX Schemas

GermainUX uses two Oracle schemas:

Use separate, strong credentials for each schema.

🔧 Prepare Oracle Database

Before running the GermainUX scripts:

1. Install or identify the supported Oracle Database instance.

2. Create or select the Oracle service or pluggable database.

3. Configure listeners and network access.

4. Prepare the required tablespaces.

5. Confirm character-set compatibility.

6. Configure storage growth and alerting.

7. Configure backups.

8. Confirm connectivity from the GermainUX Enterprise host.

9. Obtain the supported Oracle JDBC driver.

10. Confirm that SYSDBA access is available for initial schema creation.

The default Oracle listener port is commonly:

1521

Use the actual port configured in your environment.

🚀 Initialize the Database

1️⃣ Extract the GermainUX Service Package

Extract the GermainUX Service release package to a directory referenced below as:

$SERVICE_HOME

2️⃣ Review the Release Instructions

Open:

$SERVICE_HOME/install/databases/readme.txt

This file defines the exact scripts and execution order for the installed GermainUX release.

Always follow the instructions supplied with that release.

🔍 3. Locate the Oracle Scripts

The Oracle scripts are located in:

$SERVICE_HOME/install/databases/oracle

✏️ 4. Update the Setup Script

Open:

$SERVICE_HOME/install/databases/oracle/basic-setup.sql

Replace every password placeholder—such as xxxx—with strong, unique credentials for:

APM_CONFIG
APM_DATAMART

Also review:

Do not store the completed script containing production passwords in source control or an unsecured shared location.

💻 5. Choose a SQL Client

Run the scripts using Oracle SQL Developer or another compatible SQL client.

SQL Developer is recommended because some scripts contain blank lines within INSERT statements that may not execute correctly under the default SQL*Plus behavior.

If SQL*Plus must be used, validate its blank-line and script-processing settings in a non-production environment first.

🔁 6. Run the Scripts in Sequence

Run the scripts in the exact order specified by:

$SERVICE_HOME/install/databases/readme.txt

Use the following accounts:

Do not skip scripts or change their order unless instructed by Germain Software.

🔑 Initial Setup

Run the basic-*.sql scripts as SYSDBA to create:

🆔 Configuration Schema

Connect as:

APM_CONFIG

Run all config-*.sql scripts in the documented sequence.

📊 Datamart Schema

Connect as:

APM_DATAMART

Run all datamart-*.sql scripts in the documented sequence.

⚙️ Configure GermainUX Enterprise

Configure GermainUX Enterprise with:

Use a service name rather than a SID when required by your Oracle architecture.

Place the JDBC driver in the location specified by the GermainUX Enterprise installation guide.

✅ Validate the Setup

Before starting production monitoring:

1. Connect to Oracle using APM_CONFIG.

2. Connect using APM_DATAMART.

3. Confirm that the required tables, indexes, sequences, views, and materialized views exist.

4. Verify that GermainUX Enterprise can connect to both schemas.

5. Start GermainUX Enterprise.

6. Review logs for schema, permission, JDBC, or connection errors.

7. Save and retrieve a GermainUX configuration.

8. Ingest a limited monitoring workload.

9. Confirm that facts are stored in APM_DATAMART.

10. Open representative dashboards and analyses.

11. Validate retention and partition maintenance.

12. Measure ingestion and query performance.

🛠️ Materialized-View Maintenance

The APM_DATAMART schema includes materialized views that refresh periodically.

These jobs normally run without administrator intervention. If aggregated data becomes delayed or incomplete, inspect the Oracle refresh jobs:

SELECT
    JOB,
    LAST_DATE,
    FAILURES,
    BROKEN,
    WHAT
FROM ALL_JOBS
WHERE WHAT LIKE '%dbms_refresh.refresh%';

Review:

Investigate repeated failures before manually changing or restarting a job.

🗄 Partition and Storage Maintenance

Monitor:

Old partitions must be removed according to the configured GermainUX retention policy. Failure to remove expired partitions can cause the Oracle database to run out of space.

Partition deletion is destructive. Verify the retention period, target table, partition boundaries, and available backup before removing any partition.

See Oracle Database Troubleshooting.

🔒 Backup and Recovery

Back up both schemas:

APM_CONFIG
APM_DATAMART

The backup strategy should cover:

The configuration and datamart schemas may require different recovery and retention objectives.

🛡️ Security

Before production use:

Do not use SYS or SYSTEM as the GermainUX runtime account.

✅ Production Checklist

For supported Oracle versions, schema scripts, migration assistance, or production sizing, contact Germain team.