Oracle E-Business Suite 12：如何查询交易操作的终端IP地址

Hey there! Let's tackle your two questions about tracking IP addresses in Oracle E-Business Suite 12—this is a super common need for audit trails and troubleshooting, so I’ve got practical, actionable steps for you.

There are a few reliable ways to pull the source IP tied to a transaction, depending on your system's setup and the type of transaction:

• Check EBS Web Server Access Logs
  Most EBS environments log web access details in the Oracle HTTP Server (OHS) logs. For R12.1, you’ll find them at $INST_TOP/ora/10.1.3/Apache/Apache/logs; for R12.2, look in $INST_TOP/webserv/<instance_name>/logs/access_log. Scan entries that match the transaction’s timestamp, the user’s ID, and the specific form/function used to perform the transaction—each log line will include the client’s IP address.

• Query the FND_LOG_MESSAGES Table
  If your system has FND logging enabled at the right level, you can dig into this table for transaction-related IPs. Use a query like this:

  SELECT log_message, client_ip
  FROM fnd_log_messages
  WHERE module LIKE '%<transaction_module>%'
    AND log_timestamp BETWEEN '<start_timestamp>' AND '<end_timestamp>'
    AND user_name = '<user_id>';
  

  Replace <transaction_module> with the relevant module (e.g., AP_INVOICES for payables transactions), <start_timestamp>/<end_timestamp> with the window when the transaction happened, and <user_id> with the user who ran it. The client_ip column will give you the source IP.

• Use Database Audit Trails
  If database auditing is enabled for the transaction’s underlying table (like AP_INVOICES_ALL or AR_CASH_RECEIPTS_ALL), query the DBA_AUDIT_TRAIL view. Look for records where OBJECT_NAME matches the table, ACTION_NAME is 'INSERT' or 'UPDATE', and TIMESTAMP aligns with the transaction. The USERHOST or CLIENT_ID column may contain the IP (depending on your audit configuration).

For audit-focused scenarios like intentional data changes or erroneous entries, you’ll want to leverage EBS’s built-in audit tools and session tracking:

• Module-Specific Audit Tables
  EBS maintains audit history tables for key modules. Here’s how to use them:

  • For invoice changes: Query AP_AUDIT_HISTORY and join it with FND_SESSIONS to get the IP:

    SELECT ah.audit_action, ah.table_name, ah.column_name,
           s.client_ip, ah.creation_date, fu.user_name
    FROM ap_audit_history ah
    JOIN fnd_user fu ON ah.created_by = fu.user_id
    JOIN fnd_sessions s ON ah.session_id = s.session_id
    WHERE ah.table_name = 'AP_INVOICES_ALL'
      AND ah.creation_date BETWEEN '<start_time>' AND '<end_time>'
      AND fu.user_name = '<user_id>';
    

  • For cash receipt errors: Swap in AR_AUDIT_HISTORY instead of AP_AUDIT_HISTORY in the above query to track changes to receipt records.

• Directly Query FND_SESSIONS
  Every user session in EBS is tracked in FND_SESSIONS. If you know the user and approximate time of the incorrect entry, use this to pull session details:

  SELECT session_id, client_ip, login_time, logout_time
  FROM fnd_sessions
  WHERE user_id = (SELECT user_id FROM fnd_user WHERE user_name = '<user_id>')
    AND login_time <= '<transaction_time>'
    AND (logout_time IS NULL OR logout_time >= '<transaction_time>');
  

  Once you have the session_id, cross-reference it with module audit tables to link the session to the exact modification.

• Check Oracle Access Manager (OAM) Logs (if used)
  If your EBS setup uses OAM for authentication, OAM logs will have detailed IP and session data for every user action. Look in the OAM instance’s log directory, filter by user ID and timestamp, and you’ll find the associated IP.

Quick Pro Tips #

• Ensure FND logging and database auditing are enabled in your environment—if they’re turned off, you won’t have the data to pull these details. Check with your EBS admin if you’re unsure.
• For R12.2, log paths might differ slightly due to the dual-file system, so adjust paths as needed for your instance.
• If your network uses a proxy server, the client_ip in EBS logs/tables might show the proxy’s IP instead of the end-user’s. You’ll need to check proxy logs to get the actual client IP.

内容的提问来源于stack exchange，提问作者Shailesh