My DB settings:

Connected to:
Oracle Database 19c Enterprise Edition Release - Production
unified_audit_common_systemlog       string
unified_audit_sga_queue_size         integer 1048576
unified_audit_systemlog              string LOCAL2.DEBUG

ATTRIBUTES current_user, db_name

OS Setup:

# /etc/rsyslog.d/xxxx.conf
local2.* @@xxxxx:xxx
# keep a local copy for debugging
local2.debug /var/log/oracle.log

OS /var/log/oracle.log

Jul 28 12:34:03 HOSTNAME journal: Oracle Unified Audit[3586]: LENGTH: '204' TYPE:"4" DBID:"3089420649" SESID:"2072094948" CLIENTID:"" ENTRYID:"4" STMTID:"7" DBUSER:"SYS" CURUSER:"SYS" ACTION:"53" RETCODE:"0" SCHEMA:"" OBJNAME:"BITCOIN" PDB_GUID:"A797E1A81DB27E87E053487009B427E5"


select * from UNIFIED_AUDIT_TRAIL;
...sqlplus@HOSTNAME(TNS V1-V3)              4   7   28.07.21 12:34:03,317870000 28.07.21 10:34:03,317870000 DROP USER   0   3586    07001900BD4E0000    58907432            BITCOIN drop user bitcoin

The DB_NAME is shown in the colum application_context of the UNIFIED_AUDIT_TRAIL trail but not in the OS trail.

Is there a way to get the DB_NAME to the local2 os trail?

  • 1
    When using syslog i always had to set up a separate log file for each database, so local1.debug would be oradb1 and local2.debug would be oradb2, etc. I then had to use a log consolidation tool like Splunk to read the files and add database name as an attribute of the source there, then use the tool to search across all db logs at once. I never found a way to insert the db name directly into the syslog.
    – pmdba
    Jul 29 at 2:57

Per the documentation there are only limited fields available to the syslog interface. Per MOS DocID 2520613.1 this is expected behavior. Apparently most syslog implementations have a limit of 1024 bytes per record, so there is no way to add more information. According to Oracle, the purpose of the syslog interface is not to write complete audit records or to replace the full UNIFIED_AUDIT_TRAIL, but rather to help forensic auditors confirm that no records have been unauthorizedly deleted from the UNIFIED_AUDIT_TRAIL.

If you want to identify the records for a specific database (smart!), you will need to write each database to a separate syslog destination. For example, use local1.debug for oradb1, local2.debug for oradb2, etc.:

local1.debug /var/log/oracle_oradb1.log
local2.debug /var/log/oracle_oradb2.log
local3.debug /var/log/oracle_oradb3.log

Then use some other tool (I've used Splunk for this in the past) to aggregate the individual files and insert appropriate database instance identifying fields.

If you are looking for a centralized auditing tool, you could also use something like Splunk to select or otherwise receive complete records from the UNIFIED_AUDIT_TRAIL using direct queries through JDBC or TCP transmissions using stored procedures. There's always ElasticSearch or Oracle's Audit Vault solution, too. I just depends on what your actual requirements are here, and what (if any) budget you have to work with.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.