Brian Makarewicz
← Back to all posts

EPM Integration Agent on OCI, Part 3: One Agent, Many Databases

7 min readOracle Cloud
oracleepmociintegration-agentatparchitecture

This is a three-part series. Part 1: The Build · Part 2: Pitfalls and Quick Reference · Part 3: One Agent, Many Databases (you are here)

Part 3 of 3, on how the agent resolves a connection and how to add more of them.

Once the agent is running, adding database connections takes very little work, as long as you understand how the agent picks the database, user and wallet for a job. Very little of that lives in EPM Cloud. The agent works it out on the VM from the application name, so this post walks through how that lookup happens and then shows how to add a connection for a different user and for a different database altogether.

This assumes you already have a working agent; if not, see Part 1, Bridging Oracle EPM Cloud to an Autonomous Database.

How the agent finds the database, user and wallet from the application name

When a Data Integration job runs, EPM Cloud only gives the agent two things: the data-source application name and the agent cluster. The agent works out the rest of the connection (host, user, password and wallet) on the VM, by naming convention, starting from that application name.

The chain looks like this:

EPM data-source Application name
        │   (you choose it when you register the application:
        │    EPM builds it as  <Prefix> + <header-file base name>)
        ▼
<ApplicationName>.cred              ← filename must match the app name, exactly
        │   (found in  <EPM_APP_DATA_HOME>/config/ )
        ▼
username + password + jdbcurl       ← the real connection details
        │
        ▼
jdbcurl's  TNS_ADMIN=<wallet folder>  +  service name
        ▼
the actual Autonomous Database

The agent decides where to look and what to read from these settings:

  1. The folder comes from EPM_APP_DATA_HOME in agentparams.ini, and the agent looks in that folder's config/ subdirectory. This is the application folder you created in Part 1, Step 6, for example /opt/epmagent/apps/salesdw.
  2. The filename is the EPM application name with .cred on the end.
  3. Whether to use a file at all depends on the application's Credential Store setting. File means use a .cred file, and Cloud keeps the same details in the EPM UI instead.

Together they give the full path of the credential file the agent reads:

<EPM_APP_DATA_HOME>/config/<EPM application name>.cred

You can see all three in the agent's run log:

EPM_APP_DATA_HOME: /opt/epmagent/apps/salesdw     ← the folder
Data Source Name:  ATPTESTsalesdw_source          ← the filename (minus .cred)
credStore:         LOCAL_FILE                        ← "use a .cred file"

The EPM UI calls this "File", and the agent log calls the same setting LOCAL_FILE.

So you never type the .cred filename anywhere in EPM, because the agent derives it from the application name. Get it wrong by one character (the match is case-sensitive) and the agent just says it can't find credentials, since it has no explicit path to fall back on. That "can't find credentials" error is covered in Part 2, What the EPM Integration Agent Docs Don't Tell You.

Choosing application names that say which database and user they connect to

Since the application name drives the lookup, you might as well choose names that tell you what each connection is. If you want an application literally named EPMAgentforSALESDWusingAPP_READER, register the data source with a header file named APP_READER.csv and the prefix EPMAgentforSALESDWusing. EPM concatenates the two, and your credential file becomes EPMAgentforSALESDWusingAPP_READER.cred. Long application names are fine, because the 14-character limit only applies to the agent name.

What changes for a different user or a different database

Each connection is one EPM application plus one .cred file. The table shows what changes between them:

ScenarioEPM application.cred fileusername=jdbcurl= (wallet + service)
SALESDW, user APP_READERSALESDW_TESTSALESDW_TEST.credAPP_READER@salesdw_low?TNS_ADMIN=/opt/epmagent/wallet
Same DB, user APP_DEVSALESDW_DEVSALESDW_DEV.credAPP_DEV@salesdw_low?TNS_ADMIN=/opt/epmagent/wallet (identical)
Different ATP, user APP_DEVOTHERDW_DBOTHERDW_DB.credAPP_DEV@otherdb_low?TNS_ADMIN=/opt/epmagent/wallets/otherdb (new wallet + service)

In other words:

  • For a different user on the same database, you need a new application and a new .cred file, and only the username and password lines differ. The wallet folder and service name stay the same.
  • For a different database, you need all of that plus a separate wallet folder on the VM, and the jdbcurl gets a different TNS_ADMIN path and service name.

The agent, cluster and service stay the same throughout. You're only adding applications and credential files, plus a wallet folder when it's a new database.

When a new connection needs a second agent

A single agent process serves every data source assigned to its cluster and tells them apart by their .cred files, so a new database doesn't need another agent, another service or another port. The PORT in agentparams.ini only matters in synchronous mode, where EPM calls into the agent. In asynchronous mode, which is what I recommend, the agent polls outbound and nothing ever comes in on that port.

The reasons to add another agent are capacity and network reach, not another database:

ReasonSecond agent?
Another database, reachable from the same VMNo, just a new .cred (and wallet)
More throughput / parallelism / high availabilityYes, run several agents in the cluster
A database reachable only from a different networkYes, an agent that lives in that network
Deliberate workload isolationYes, a second agent in its own cluster

If you do add a second agent for throughput, it needs its own agent name (still 14 characters or fewer), its own application/data folder, its own PORT and its own systemd unit (see Part 1, Step 15 for the unit file). I won't walk through that setup here, but once the second agent is running you assign it to the same cluster and EPM spreads jobs across both.

Steps to add a connection for a new database or a new user

A different database (new wallet). On the VM, put the new wallet in its own folder. Don't merge wallets: each Autonomous Database download has its own auto-login wallet, and that's what lets the agent connect without a wallet password.

sudo mkdir -p /opt/epmagent/wallets/otherdb
sudo unzip Wallet_OTHERDB.zip -d /opt/epmagent/wallets/otherdb
sudo chown -R epmagent:epmagent /opt/epmagent/wallets/otherdb
sudo chmod 700 /opt/epmagent/wallets/otherdb
sudo cat /opt/epmagent/wallets/otherdb/tnsnames.ora   # note its service name

Encrypt that database's password with encryptpassword.sh, register a new On-Premise Database data source in EPM (noting the exact application name it creates), and then write the matching .cred file with TNS_ADMIN pointing at the new wallet:

driver=oracle.jdbc.driver.OracleDriver
jdbcurl=jdbc:oracle:thin:@otherdb_low?TNS_ADMIN=/opt/epmagent/wallets/otherdb
username=<user>
password=<encrypted>

Set the application's filters (query, delimiter, Credential Store = File), confirm the cluster is assigned, build the integration and run it. To check it, run an Import (Part 1, Step 14) and look in the log for credStore: LOCAL_FILE and the right Data Source Name. If both are there, the agent picked up the right credential file.

A different user on the same database. Same steps, minus the wallet work: reuse TNS_ADMIN=/opt/epmagent/wallet and the same service name. All you're adding is the application and its .cred file with the new username and password.

Summary

One agent can serve as many databases and users as the VM can reach. Each job finds its connection through the EPM application name, which points to a .cred file holding the user and the wallet path. Follow that naming convention for every new connection and you won't need a second agent until you run short of capacity or need to reach a different network.