Oracle E-Business Suite: Application to Database Mapping

Overview

When DBAs are poor communicators or secretive about infrastructure, system administrators need to independently map application server to database relationships. This guide provides the key files, commands, and techniques to discover and document these connections in Oracle E-Business Suite and WebLogic environments.

Use Case: Creating relationship documentation, troubleshooting connectivity, knowledge transfer, and understanding application dependencies.

Key Configuration Files

1. EBS Context File (The Goldmine)

The context file is the authoritative source for all EBS configuration including database connections.

$INST_TOP/appl/admin/<context_name>.xml

$ORACLE_HOME/appl/admin/<context_name>.xml

Key XML tags to search for:

<s_db_host> - Database server hostname
<s_dbSid> - Database SID
<s_dbGlnam> - Global database name
<s_dbport> - Database listener port
<s_dbdomain> - Database domain

grep -i "db_host\|dbSid\|dbGlnam" $INST_TOP/appl/admin/*.xml

2. TNS Names Configuration

$ORACLE_HOME/network/admin/tnsnames.ora

$TNS_ADMIN/tnsnames.ora

Shows all configured database connection strings available to the application server.

cat $ORACLE_HOME/network/admin/tnsnames.ora

3. Environment File

APPS<CONTEXT_NAME>.env

Sources all Oracle environment variables. Check for:

ORACLE_SID
TWO_TASK
TNS_ADMIN
ORACLE_HOME

4. WebLogic Datasource Configuration

$DOMAIN_HOME/config/jdbc/*-jdbc.xml

$DOMAIN_HOME/config/config.xml

Contains JDBC datasource definitions with database connection details.

find $DOMAIN_HOME/config -name "*jdbc.xml" -exec grep -l "jdbc:oracle" {} \;

Discovery Commands

1. Find All tnsnames.ora Files

find / -name tnsnames.ora 2>/dev/null

find /u01 /u02 -name tnsnames.ora 2>/dev/null

2. Check Active Database Connections

netstat -an | grep :1521

netstat -an | grep ESTABLISHED | grep 1521

lsof -i :1521 | grep ESTABLISHED

Tip: Default Oracle listener port is 1521, but some environments use 1526 or custom ports.

3. Identify Running Oracle Processes

ps -ef | grep oracle | grep -v grep

ps -ef | grep pmon

ps -ef | grep tnslsnr

4. Check Listener Status

lsnrctl status

lsnrctl status LISTENER_NAME

5. Extract Connection Info from Context File

grep -E "s_db_host|s_dbSid|s_dbport|s_dbGlnam" $INST_TOP/appl/admin/*.xml

6. Check WebLogic Datasources

grep -r "jdbc:oracle" $DOMAIN_HOME/config/

find $DOMAIN_HOME -name "config.xml" -exec grep -A 5 "jdbc-data-source" {} \;

7. View Environment Variables

env | grep -i oracle

echo $ORACLE_SID $TWO_TASK $TNS_ADMIN

8. Check Application Logs for Database Connection Info

grep -i "database\|jdbc\|oracle" $ORACLE_HOME/appl/admin/log/*.log | tail -50

9. Find All Oracle Homes

cat /etc/oratab

cat /var/opt/oracle/oratab

Database-Side Discovery

Note: These commands require database access and appropriate privileges.

1. Check Listener Log

$ORACLE_HOME/network/log/listener.log

tail -500 $ORACLE_HOME/network/log/listener.log | grep CONNECT

2. Query Active Sessions (as DBA)

sqlplus / as sysdba

SELECT username, machine, program, osuser FROM v$session WHERE username IS NOT NULL ORDER BY username, machine;

3. Check Registered Services

SELECT name, pdb FROM v$services;

Building Your Relationship Matrix

Use this template to document your findings:

App Server	Hostname	DB Server	DB SID	Service Name	Port	Purpose
apps01	ebsapp01.domain.com	dbprod01.domain.com	PROD	PROD_APP	1521	EBS Production
apps02	ebsapp02.domain.com	dbtest01.domain.com	TEST	TEST_APP	1521	EBS Test

Documentation Script

#!/bin/bash # Quick relationship discovery script echo "=== Oracle EBS App-to-DB Relationship Discovery ===" echo "" echo "Hostname: $(hostname)" echo "Date: $(date)" echo "" echo "--- Environment Variables ---" env | grep -i oracle | sort echo "" echo "--- Context File Database Info ---" if [ -n "$INST_TOP" ]; then grep -E "s_db_host|s_dbSid|s_dbport" $INST_TOP/appl/admin/*.xml 2>/dev/null fi echo "" echo "--- Active Database Connections ---" netstat -an | grep :1521 | grep ESTABLISHED echo "" echo "--- Running Oracle Processes ---" ps -ef | grep pmon | grep -v grep echo "" echo "--- TNS Configuration ---" if [ -n "$TNS_ADMIN" ]; then cat $TNS_ADMIN/tnsnames.ora 2>/dev/null elif [ -n "$ORACLE_HOME" ]; then cat $ORACLE_HOME/network/admin/tnsnames.ora 2>/dev/null fi

Best Practices

Document Everything: Save copies of context files, tnsnames.ora, and configuration files with timestamps
Screenshot Configs: Visual evidence is helpful during outages or disputes
Note Verification Dates: Track when you verified each connection
Watch Change Windows: During maintenance, observe what gets bounced to identify dependencies
Cross-Reference: Verify findings from multiple sources (context file + netstat + logs)
Build Trust: Share your documentation with the DBA team - sometimes transparency breaks down walls

Knowledge Transfer Tip: This documentation is critical for your replacements. Include real examples from your environment with actual hostnames and SIDs (sanitized if needed for security).

Common Port Numbers

Service	Default Port	Notes
Oracle Listener	1521	Standard database listener port
Oracle Listener (Alt)	1526	Alternative port in some environments
Oracle EM Express	5500	Enterprise Manager Express
WebLogic Admin	7001	WebLogic Administration Console
WebLogic Managed	7002-799x	Managed server instances

Troubleshooting Tips

Connection Refused

Check if listener is running: lsnrctl status
Verify firewall rules: telnet db_host 1521
Check tnsnames.ora syntax

TNS: Could Not Resolve Service Name

Verify TNS_ADMIN is set correctly
Check tnsnames.ora exists and is readable
Confirm service name spelling

Unable to Find Context File

Source the environment file first
Check common locations: /u01, /u02, $ORACLE_HOME
Search: find / -name "*.xml" 2>/dev/null | grep -i context

Quick Reference: Source Environment

Before running most Oracle commands, source the environment:

# Find the environment file ls -la /u01/*/appl/*.env ls -la $ORACLE_HOME/*.env # Source it . /path/to/APPS<CONTEXT>.env # Or use oraenv . oraenv # (then enter SID when prompted)

Summary

Key steps for mapping Oracle EBS application to database relationships:

Locate and examine the context file ($INST_TOP/appl/admin/*.xml)
Check tnsnames.ora for connection strings
Use netstat/lsof to see active connections
Review WebLogic datasource configurations
Cross-reference findings from multiple sources
Document everything with timestamps
Build a relationship matrix for future reference

Remember: Always verify your findings during low-impact times and maintain detailed documentation for knowledge transfer.