Adding Oracle Databases

Register database

Firstly, register new monitored Oracle Database(s) with APPM by navigating to Repository -> Databases -> Create Database. Following fields are required:

• Collector Name - name of database and also name of Postgres schema containing its samples. Use lowercase when possible.
• Connection String - TNS Connection string in format hostname:port/service_name.
• APPM Schema - Name of Oracle Database schema that will be created in one of the following steps.
• History Days - Samples older than this number of days are automatically deleted from repository.

Note

This step won’t touch your database yet.

Create / Upgrade Schema

Important

This step creates/updates APPM2 and APPM_COLLECTOR schemas on your Oracle database.

Navigate to Repository -> Databases. Then follow either Automatic or Manual method.

Automatic Method

Select Oracle database(s) on which you wish to create/upgrade APPM2 and APPM_COLLECTOR schemas and click Upgrade Schema button (it creates the schemas if they don’t yet exist). You will need to enter SYSDBA credentials in order to allow APPM to create them.

Note that every SQL sent to Oracle database during the installation of those schemas is logged in /srv/appm/vol/wf-log/server.log.

Manual Method

If you don’t want to provide SYSDBA credentials to APPM, download appm.sql and collector.sql from Repository -> Databases and execute them yourself. These downloads target the APPM2 schema.

Connect to the Oracle database that APPM monitors:

sqlplus / as sysdba

Select the target PDB (multitenant databases only)

If APPM monitors a pluggable database (PDB), both scripts must run in that PDB. Check the current container and, if necessary, switch to it before running either script. Replace PDB_NAME with its name:

ALTER SESSION SET CONTAINER = PDB_NAME;

No switch is needed if you connected directly to the correct PDB service. For a non-CDB database, skip this step; there is no PDB to select.

Existing APPM2 schema

The automatic Upgrade Schema method upgrades an existing schema in place. It checks APPM_SCHEMA_VER and skips table migrations that have already been applied. The downloaded appm.sql contains all migrations and replays them without checking that history. Existing users, tables, columns, and migration records can therefore cause errors. SQL*Plus normally continues after SQL errors, but an inherited WHENEVER SQLERROR EXIT setting can terminate execution before the upgrade finishes.

For a clean manual installation, recreate the existing APPM2 schema before running appm.sql. This allows all migrations to run from the beginning without conflicting with previously applied changes. APPM2 mainly contains monitoring views over Oracle’s V$ and X$ objects, supporting code, and a small set of tables used largely as temporary storage before collection into the central repository.

In the target database/PDB and before running appm.sql, execute:

DROP USER APPM2 CASCADE;

Important

Recreation does not delete historical samples already transferred to the central PostgreSQL repository. It clears local working data and resets local settings, including APPM_SESS_AUD_SETUP and APPM_TRACE_TRIGGERS. Reapply any required custom settings after installation.

Run collector.sql after recreating APPM2 to restore the collector’s grants on the new objects. Do not drop APPM_COLLECTOR as a prerequisite; if that user already exists, its CREATE USER statement in collector.sql will fail, but the following grants still need to execute.

Run the scripts

Run both scripts in the same target database/PDB, in this order:

@appm.sql
@collector.sql

Review the execution output for errors and confirm that both scripts finish.

Additional Information

Installation will create/update the APPM2 schema, which includes views to V$ performance views and APPM configuration and working tables. Historical samples are stored in a separate repository database.

Global objects that are created as a part of APPM installation are named like APPM_<NAME>, where APPM_ is a constant prefix.

Created schema could take up to 250MB of disk space.

Post-Installation

Restart following services in order to force APPM collector to reconnect:

systemctl restart aba-appm
systemctl restart appm_collector # optional and only available in EE edition