How to enable and use the unified audit trail in an Oracle 19c database
First enable unified auditing by closing the database and using the chopt tool to execute chopt enable Use the uniaud command to enable the unified audit function, and query V$OPTION verification after restarting; then create an audit policy, such as monitoring system permissions or operations on specific tables, and activate the policy through the AUDIT command; then query records such as login failures and sensitive operations through the UNIFIED_AUDIT_TRAIL view, and use DBMS_AUDIT_MGMT to manage audit data retention; finally, follow best practices such as minimizing the audit scope, using conditional auditing, and regular cleanup to ensure a balance between performance and compliance.
To enable and use the unified audit trail in Oracle 19c, you need to activate unified auditing and configure audit policies to capture database activity. Unlike traditional auditing, unified auditing consolidates audit records into a centralized, high-performance data store accessible through views like UNIFIED_AUDIT_TRAIL . Here's how to set it up and start using it effectively.
Enable Unified Auditing
Unified auditing is disabled by default in Oracle 19c and must be explicitly enabled using Oracle Binary Replacement. This requires shutting down the database and relinking the Oracle executable.
- Shut down all database instances and listeners running from the Oracle home.
- Run the following command from the Oracle home's bin directory:
orapki wallet create -wallet $ORACLE_HOME/orapki -auto_login - Use the chopt tool to enable unified auditing:
chopt enable uniaud - Restart the database instance.
- Verify that unified auditing is enabled by querying:
SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing';
This should return TRUE .
Create and Enable Audit Policies
Once unified auditing is active, define audit policies to monitor specific actions or users. Policies can be based on privileges, actions, objects, or conditions.
- Create a privilege audit policy to track use of powerful system privileges:
CREATE AUDIT POLICY audit_dba_privs PRIVILEGES DROP ANY TABLE, ALTER DATABASE; - Create an object audit policy to monitor access to a sensitive table:
CREATE AUDIT POLICY audit_hr_table ACTIONS SELECT, INSERT ON hr.employees; - Enable the policies globally:
AUDIT POLICY audit_dba_privs;
AUDIT POLICY audit_hr_table; - You can also conditionally audit based on context, for example, auditing logins from outside the corporate network using conditional expressions (requires advanced configuration).
Monitor and Query Audit Data
After enabling policies, audit records appear in the UNIFIED_AUDIT_TRAIL view. This view includes detailed information such as timestamp, user, action, object name, client identifier, and more.
- Query recent audit entries:
SELECT EVENT_TIMESTAMP, DB_USER, ACTION_NAME, OBJECT_NAME, CLIENT_IDENTIFIER FROM UNIFIED_AUDIT_TRAIL WHERE EVENT_TIMESTAMP > SYSDATE - 1 ORDER BY EVENT_TIMESTAMP DESC; - Filter for failed login attempts:
SELECT EVENT_TIMESTAMP, DB_USER, RETURN_CODE FROM UNIFIED_AUDIT_TRAIL WHERE ACTION_NAME = 'LOGON' AND RETURN_CODE != 0; - Use DBMS_AUDIT_MGMT to manage audit trail size—set a retention period and purge old records to avoid performance issues.
Best Practices for Unified Auditing
While unified auditing provides comprehensive tracking, indiscriminate auditing can impact performance and generate excessive data.
- Audit only what is necessary—focus on privileged users, sensitive objects, and critical operations.
- Use conditional auditing to reduce volume (eg, audit only during non-business hours or from specific IP ranges).
- Regularly review and rotate audit data using automated cleanup jobs.
- Ensure the audit administrator role ( AUDIT_ADMIN ) is granted only to authorized users.
Basically, enabling unified auditing in Oracle 19c involves relinking the Oracle binary, creating targeted audit policies, and monitoring the UNIFIED_AUDIT_TRAAIL view. Proper configuration ensures security compliance without sacrificing performance.
The above is the detailed content of How to enable and use the unified audit trail in an Oracle 19c database. For more information, please follow other related articles on the PHP Chinese website!
Hot AI Tools
Undress AI Tool
Undress images for free
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undresser.AI Undress
AI-powered app for creating realistic nude photos
ArtGPT
AI image generator for creative art from text prompts.
Stock Market GPT
AI powered investment research for smarter decisions
Hot Article
Popular tool
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
20519
7
13631
4
How to troubleshoot the Oracle Listener startup? (Network Services)
Mar 10, 2026 am 12:58 AM
Oraclelistenerstartupfailuresstemfromsilentlistener.oraparsingerrors,hostnameresolutionissues,orpermissionproblems—notbinariesorports;validatesyntaxwithreload,checkownership,verifyactualconfigpath,testDNS,useexplicitIPs,confirmADR_BASE,enabletracingp
How to patch Oracle Grid Infrastructure? (System Maintenance)
Mar 10, 2026 am 01:00 AM
Three things must be confirmed before applying the GI patch: 1. The opatchlsinventory-detail output of each node is consistent; 2. OCR and VoteDisk are online and crsctlcheckcluster-all and ocrcheck both return SUCCESS; 3. $GRID_HOME/crs/install/rootcrs.sh-prepatch has been successfully executed.
How to use Sequences in Oracle to generate IDs? (Auto-increment)
Mar 06, 2026 am 01:16 AM
ID auto-increment in Oracle requires the cooperation of SEQUENCE and BEFOREINSERT triggers, and the trigger must check: NEW.IDISNULL; 12c supports IDENTITY but is not compatible with older versions and disables explicit insertion.
How to implement Transparent Data Encryption (TDE) in Oracle? (Data Security)
Mar 13, 2026 am 12:14 AM
OracleTDE must first enable and open the encrypted wallet (Wallet), otherwise ORA-28365 will be reported when executing ALTERTABLESPACE...ENCRYPTION; Wallet needs to be created, opened and managed through the ADMINISTERKEYMANAGEMENT command, and the path must be explicitly configured in sqlnet.ora and permissions must be ensured.
How to use Oracle APEX to build a low-code app? (Rapid Development)
Mar 13, 2026 am 12:48 AM
OracleAPEXislow-glue,notno-code:itskipsinfrastructurebutrequiresSQL,PL/SQL,anddeclarativelogic;ApplicationProcesseshandleserver-sidevalidationandsideeffects,DynamicActionsmanageclient-sideinteractivity;InteractiveGridneedskey-preservedsourcesforediti
How to manage Flashback Data Archive_Flashback Data Archive table space allocation
Mar 28, 2026 pm 04:06 PM
The reason why the FlashbackDataArchive table space is full is that the hidden history table (SYS_FBA_HIST_XXXXXX) occupies the table space where the main table is located and does not go through ASSM cleaning; you need to use ALTERFLASHBACKARCHIVE...MODIFYTABLESPACE to migrate to the local management automatic segment space table space, and manually clean up the orphan history table.
How to use JSON data types in Oracle Database? (NoSQL Features)
Mar 08, 2026 am 01:03 AM
In Oracle's JSON scenario, you should select VARCHAR2 (4000CHAR) plus ISJSON constraints (small documents) or BLOB plus ISJSON constraints (large documents), and disable CLOB; ISJSON is a column-level constraint syntax, not a function call; the JSON_VALUE path must be a string literal; JSON_EXISTS needs to be speeded up with the JSON_VALUE function index.
How to grant SYSDBA permissions_sysdba management of password files and OS authentication
Apr 03, 2026 am 08:54 AM
Ordinary users can be authorized through GRANTSYSDBATOusername; provided that the database enables password file authentication (REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE) and has logged in with SYS; there is no need to restart after authorization, but the connection needs to explicitly specify assysdba, and the user credentials must exist in the V$PWFILE_USERS view.
