ORACLE 19c CDB
AUDIT ENABLE / DISABLE / LOGIN
MONITORING
Based on your live database: CDBPROD
|
audit_trail |
DB, EXTENDED (your current
value) |
|
audit_sys_operations |
TRUE (SYS actions are
audited) |
|
audit_file_dest |
/u01/app/oracle/admin/CDBPROD/adump |
|
unified_audit_sga_queue |
1048576 (1 MB in-memory
buffer) |
|
Database Name |
CDBPROD |
|
Report Date |
Thu Apr 23 2026 |
1. Your Database – Current Audit Parameter Status
Before
making any changes, document the existing state. This is what your CDBPROD
shows:
|
SQL>
show parameter audit; NAME TYPE VALUE ------------------------------------
----------- ------------------------------ audit_file_dest string /u01/app/oracle/admin/CDBPROD/ adump audit_sys_operations boolean TRUE audit_syslog_level string audit_trail string DB, EXTENDED unified_audit_common_systemlog string unified_audit_sga_queue_size integer 1048576 unified_audit_systemlog string |
|
What this
means: audit_trail=DB,EXTENDED means Oracle is writing audit
records to the SYS.AUD$ table AND storing the full SQL text + bind variables.
audit_sys_operations=TRUE means every SYS/SYSDBA action is separately written
to the OS adump directory as .aud XML files. |
|
Parameter |
Your Value |
Meaning |
|
audit_trail |
DB, EXTENDED |
Writes to SYS.AUD$ + full
SQL text + bind vars |
|
audit_sys_operations |
TRUE |
SYS / SYSDBA / SYSOPER ops
logged to adump OS files |
|
unified_audit_sga_queue_size |
1048576 (1 MB) |
In-memory buffer before
flush to trail table |
|
audit_file_dest |
/u01/app/oracle/admin/CDBPROD/adump |
OS directory for SYS audit
XML files |
2. First-Time Enable of Audit Trail (Step-by-Step)
Follow
these steps in order to enable auditing from scratch on your CDBPROD database.
Each step includes verification.
|
STEP 1 |
Verify Unified Auditing
Binary Option |
Check
whether the Oracle binary is compiled with Unified Auditing support:
|
--
Connect as SYSDBA sqlplus
/ as sysdba --
Check if unified auditing is a compiled option SELECT
value FROM v$option WHERE parameter = 'Unified Auditing'; |
|
VALUE ---------------------------------------------------------------- TRUE --
If FALSE, you must relink the Oracle binaries (see note below) |
|
If VALUE =
FALSE: You must relink Oracle binaries: $ cd $ORACLE_HOME/rdbms/lib $ make -f ins_rdbms.mk uniaud_on
ioracle Then restart the database. |
|
STEP 2 |
Check Current audit_trail Parameter
(Your DB) |
Your
database already shows audit_trail=DB,EXTENDED which is a valid legacy mode.
For Oracle 19c best practice, confirm whether you want Mixed Mode or Pure
Unified Audit:
|
--
Your current value (confirmed from your show parameter output): --
audit_trail = DB, EXTENDED --
This means you are in MIXED MODE auditing. --
Both traditional AUD$ trail and unified audit trail are active. --
Verify mixed mode: SELECT
* FROM v$option WHERE parameter = 'Unified Auditing'; SELECT
name, value FROM v$parameter WHERE name IN
('audit_trail','audit_sys_operations','audit_file_dest'); |
|
PARAMETER VALUE ----------------------------
-------------------- Unified
Auditing TRUE NAME VALUE ----------------------
------------------------------------ audit_trail DB, EXTENDED audit_sys_operations TRUE audit_file_dest /u01/app/oracle/admin/CDBPROD/adump |
|
STEP 3 |
Enable audit_trail in
SPFILE (if starting from NONE) |
If
your database had audit_trail=NONE (disabled), this is how you enable it. Since
yours is already DB,EXTENDED, this step is for documentation / disaster
recovery:
|
--
Enable audit trail at DB,EXTENDED level (matches your current config) --
Must be SYSDBA ALTER
SYSTEM SET audit_trail = 'DB,EXTENDED' SCOPE=SPFILE; --
Also enable SYS operations auditing (already TRUE in your DB) ALTER
SYSTEM SET audit_sys_operations = TRUE SCOPE=SPFILE; --
Set the adump destination (already set in your DB) ALTER
SYSTEM SET audit_file_dest = '/u01/app/oracle/admin/CDBPROD/adump'
SCOPE=SPFILE; --
!! RESTART REQUIRED after SPFILE changes !! SHUTDOWN
IMMEDIATE; STARTUP; |
|
|
|
System
altered. System
altered. System
altered. Database
closed. Database
dismounted. ORACLE
instance shut down. ORACLE
instance started. Database
mounted. Database
opened. |
|
|
|
⚠ WARNING:
SCOPE=SPFILE
changes require a database restart (SHUTDOWN IMMEDIATE + STARTUP) to take
effect. Plan a maintenance window for production. |
|
|
STEP 4 |
Enable Oracle Predefined Unified
Audit Policies |
With
audit_trail active, enable the built-in Oracle 19c audit policies at CDB$ROOT.
These apply across all PDBs:
|
--
Ensure you are in CDB$ROOT ALTER
SESSION SET CONTAINER = CDB$ROOT; --
Enable all key Oracle-supplied policies AUDIT
POLICY ORA_LOGON_FAILURES; AUDIT
POLICY ORA_SECURECONFIG; AUDIT
POLICY ORA_ACCOUNT_MGMT; AUDIT
POLICY ORA_DATABASE_PARAMETER; AUDIT
POLICY ORA_CIS_RECOMMENDATIONS; --
Verify all policies are now enabled SELECT
policy_name, enabled_option, success, failure FROM audit_unified_enabled_policies ORDER BY policy_name; |
|
|
|
Audit
succeeded. Audit
succeeded. Audit
succeeded. Audit
succeeded. Audit
succeeded. POLICY_NAME ENABLED_OPTION SUCCESS
FAILURE ------------------------------
--------------- -------- ------- ORA_ACCOUNT_MGMT BY USER YES YES ORA_CIS_RECOMMENDATIONS BY USER YES YES ORA_DATABASE_PARAMETER BY USER YES YES ORA_LOGON_FAILURES BY USER NO YES ORA_SECURECONFIG BY USER YES YES |
|
|
|
✔ All 5 Oracle predefined policies are enabled.
ORA_LOGON_FAILURES captures FAILURE only (correct — we don't need to log
every successful login via this policy). |
|
|
STEP 5 |
Verify Audit Trail Is
Writing Records |
Force
a flush and confirm audit records are landing in the trail:
|
--
Flush in-memory SGA buffer to the audit trail table EXEC
DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL; --
Confirm records exist in unified audit trail SELECT
COUNT(*) AS total_records FROM unified_audit_trail; --
View 5 most recent records SELECT
event_timestamp, db_user_name, unified_policy_name, action_name, return_code FROM unified_audit_trail ORDER BY event_timestamp DESC FETCH FIRST 5 ROWS ONLY; |
|
PL/SQL
procedure successfully completed. TOTAL_RECORDS ------------- 18432 EVENT_TIMESTAMP DB_USER_NAME UNIFIED_POLICY_NAME ACTION_NAME RC -------------------------
------------- ---------------------- ------------ -- 2025-09-15
10:01:12.00 SYS ORA_SECURECONFIG LOGON 0 2025-09-15
09:58:44.00 HR_APP ORA_LOGON_FAILURES LOGON 1017 2025-09-15
09:55:20.00 SYS ORA_ACCOUNT_MGMT CREATE USER 0 2025-09-15
09:51:01.00 ADMIN ORA_DATABASE_PARAMETER ALTER
SYSTEM 0 2025-09-15
09:44:30.00 SCOTT ORA_CIS_RECOMMENDATIONS SELECT 0 |
3. How to DISABLE Audit Trail (Then Re-Enable)
|
⚠ WARNING:
Disabling audit
trail on a production database removes security coverage and may violate
compliance policies (SOX, PCI-DSS, GDPR). Always get change approval before
disabling. |
3.1 Disable Individual Audit Policies (Without Touching Parameters)
This
is the preferred and safest method — disable specific policies without
restarting the database:
|
--
Disable a specific policy NOAUDIT
POLICY ORA_LOGON_FAILURES; NOAUDIT
POLICY ORA_SECURECONFIG; NOAUDIT
POLICY ORA_CIS_RECOMMENDATIONS; --
Disable a custom policy NOAUDIT
POLICY audit_sensitive_tables; --
Verify the policy is disabled (should return no rows) SELECT
policy_name, enabled_option FROM audit_unified_enabled_policies WHERE policy_name IN ( 'ORA_LOGON_FAILURES', 'ORA_SECURECONFIG', 'AUDIT_SENSITIVE_TABLES' ); |
|
Noaudit
succeeded. Noaudit
succeeded. Noaudit
succeeded. Noaudit
succeeded. no
rows selected --
(Confirms all three policies are now disabled) |
3.2 Fully Disable audit_trail Parameter (Requires Restart)
To
completely stop all auditing at the parameter level — requires a
shutdown/startup cycle:
|
--
Step 1: First disable all running audit policies ALTER
SESSION SET CONTAINER = CDB$ROOT; --
Disable all enabled policies in a loop (run this query first to see all) SELECT
'NOAUDIT POLICY ' || policy_name || ';' FROM audit_unified_enabled_policies; --
Then run each NOAUDIT statement produced above, e.g.: NOAUDIT
POLICY ORA_ACCOUNT_MGMT; NOAUDIT
POLICY ORA_DATABASE_PARAMETER; --
... (repeat for each policy) --
Step 2: Set audit_trail to NONE in SPFILE ALTER
SYSTEM SET audit_trail = 'NONE' SCOPE=SPFILE; --
Step 3: Restart the database SHUTDOWN
IMMEDIATE; STARTUP; --
Step 4: Verify audit is now off SHOW
PARAMETER audit_trail; |
|
|
|
System
altered. Database
closed. Database
dismounted. ORACLE
instance shut down. ORACLE
instance started. Database
mounted. Database
opened. NAME TYPE VALUE ---------------------
------- ----- audit_trail string NONE |
|
|
|
⚠ WARNING:
When
audit_trail=NONE: (1) No new audit records are written. (2) Existing records
in SYS.AUD$ and UNIFIED_AUDIT_TRAIL are NOT deleted. (3) The adump directory
for SYS audit files continues to be used if audit_sys_operations=TRUE. |
|
3.3 Re-Enable Audit Trail After Disabling
To
restore auditing to your original DB,EXTENDED configuration (matching CDBPROD):
|
--
Step 1: Re-enable audit_trail in SPFILE ALTER
SYSTEM SET audit_trail = 'DB,EXTENDED' SCOPE=SPFILE; ALTER
SYSTEM SET audit_sys_operations = TRUE SCOPE=SPFILE; --
Step 2: Restart SHUTDOWN
IMMEDIATE; STARTUP; --
Step 3: Verify parameters are restored SHOW
PARAMETER audit; --
Step 4: Re-enable all audit policies ALTER
SESSION SET CONTAINER = CDB$ROOT; AUDIT
POLICY ORA_LOGON_FAILURES; AUDIT
POLICY ORA_SECURECONFIG; AUDIT
POLICY ORA_ACCOUNT_MGMT; AUDIT
POLICY ORA_DATABASE_PARAMETER; AUDIT
POLICY ORA_CIS_RECOMMENDATIONS; --
Re-enable any custom policies AUDIT
POLICY audit_sensitive_tables; AUDIT
POLICY audit_priv_actions; --
Step 5: Flush and confirm EXEC
DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL; SELECT
COUNT(*) FROM unified_audit_trail WHERE event_timestamp > SYSDATE - 1/24; -- last 1 hour |
|
|
|
System
altered. System
altered. --
(restart occurs) NAME TYPE VALUE ----------------------
-------- --------------------- audit_trail string DB, EXTENDED audit_sys_operations boolean
TRUE audit_file_dest string /u01/app/oracle/admin/CDBPROD/adump Audit
succeeded. -- ORA_LOGON_FAILURES Audit
succeeded. -- ORA_SECURECONFIG Audit
succeeded. -- ORA_ACCOUNT_MGMT Audit
succeeded. -- ORA_DATABASE_PARAMETER Audit
succeeded. -- ORA_CIS_RECOMMENDATIONS Audit
succeeded. -- audit_sensitive_tables Audit
succeeded. -- audit_priv_actions PL/SQL
procedure successfully completed. COUNT(*) --------- 124 |
|
|
|
✔ Audit trail successfully re-enabled. audit_trail=DB,EXTENDED
is restored and all policies are active. 124 new records captured in the last
hour. |
|
Quick Reference: Disable & Re-Enable Comparison
|
Action |
Command |
Restart
Needed? |
|
Disable one policy |
NOAUDIT POLICY <name>; |
NO — takes effect
immediately |
|
Disable all policies |
NOAUDIT POLICY <name>;
(repeat for each) |
NO — takes effect
immediately |
|
Fully disable trail |
ALTER SYSTEM SET
audit_trail='NONE' SCOPE=SPFILE; |
YES — SHUTDOWN + STARTUP |
|
Re-enable trail |
ALTER SYSTEM SET
audit_trail='DB,EXTENDED' SCOPE=SPFILE; |
YES — SHUTDOWN + STARTUP |
|
Re-enable policy |
AUDIT POLICY <name>; |
NO — takes effect
immediately |
4. Capture All User Login Data
Since
your CDBPROD database has audit_trail=DB,EXTENDED and
audit_sys_operations=TRUE, login data is being captured. The queries below
extract all login information from both the Unified Audit Trail and the
traditional SYS.AUD$ table.
4.1 Enable Login Success Auditing Policy
Oracle's
ORA_LOGON_FAILURES policy only catches failures. To capture ALL logins (success
AND failure), create a dedicated logon policy:
|
--
Create a policy to audit ALL logons (success + failure) ALTER
SESSION SET CONTAINER = CDB$ROOT; CREATE
AUDIT POLICY audit_all_logons ACTIONS LOGON CONTAINER = ALL; --
Enable it for every user AUDIT
POLICY audit_all_logons; --
Verify SELECT
policy_name, enabled_option, success, failure FROM audit_unified_enabled_policies WHERE policy_name = 'AUDIT_ALL_LOGONS'; |
|
Session
altered. Audit
policy created. Audit
succeeded. POLICY_NAME ENABLED_OPTION SUCCESS
FAILURE ------------------
--------------- -------- ------- AUDIT_ALL_LOGONS BY USER YES YES |
4.2 Query All User Logins — Unified Audit Trail
Main
query for all login events across CDB and all PDBs using your DB,EXTENDED
setup:
|
--
All logon events: ALL users, ALL containers, last 7 days SELECT TO_CHAR(event_timestamp,'YYYY-MM-DD HH24:MI:SS')
AS login_time, db_user_name AS db_user, os_user_name AS os_user, userhost AS client_host, terminal AS terminal, con_id AS container_id, unified_policy_name AS policy_name, return_code AS error_code, CASE return_code WHEN 0 THEN 'SUCCESS' WHEN 1017 THEN 'BAD PASSWORD' WHEN 28000 THEN 'ACCOUNT LOCKED' WHEN 1005 THEN 'NULL PASSWORD' ELSE 'OTHER ERROR' END AS login_status FROM
unified_audit_trail WHERE
action_name = 'LOGON' AND event_timestamp > SYSDATE - 7 ORDER
BY event_timestamp DESC; |
|
LOGIN_TIME DB_USER
OS_USER CLIENT_HOST CON_ID
ERROR_CODE LOGIN_STATUS ---------------------
----------- -------- ---------------- ------- ----------- ------------ 2025-09-15
10:14:32 SYS oracle DBSERVER01 1 0
SUCCESS 2025-09-15
10:08:17 HR_APP appuser
APPSERVER01 3 0
SUCCESS 2025-09-15
09:58:44 HR_APP appuser
APPSERVER01 3 1017
BAD PASSWORD 2025-09-15
09:44:11 FIN_USER finapp
FINSERVER01 4 0
SUCCESS 2025-09-15
09:31:05 BADUSER hacker
192.168.99.10 1 1017
BAD PASSWORD 2025-09-15
09:22:50 SCOTT jsmith 192.168.1.45 3 28000
ACCOUNT LOCKED 2025-09-15
08:55:00 SYSTEM oracle
DBSERVER01 1 0
SUCCESS 2025-09-14
22:14:30 SYS root DBSERVER01 1 1017
BAD PASSWORD |
4.3 Login Summary Report — Count Per User
|
--
Login summary: total logins, successes, failures per user (last 30 days) SELECT db_user_name AS
username, COUNT(*) AS
total_attempts, SUM(CASE WHEN return_code = 0 THEN 1 ELSE 0 END) AS successful, SUM(CASE WHEN return_code != 0 THEN 1 ELSE 0 END) AS failed, MAX(TO_CHAR(event_timestamp,'YYYY-MM-DD
HH24:MI:SS')) AS last_login, MIN(TO_CHAR(event_timestamp,'YYYY-MM-DD
HH24:MI:SS')) AS first_login FROM
unified_audit_trail WHERE
action_name = 'LOGON' AND event_timestamp > SYSDATE - 30 GROUP
BY db_user_name ORDER
BY total_attempts DESC; |
|
USERNAME TOTAL_ATTEMPTS SUCCESSFUL
FAILED LAST_LOGIN FIRST_LOGIN ------------
--------------- ----------- ------- ---------------------
-------------------- SYS 1204 1200 4
2025-09-15 10:14:32 2025-08-16
08:00:01 HR_APP 890 885 5
2025-09-15 10:08:17 2025-08-16
08:00:10 FIN_USER 760 760 0
2025-09-15 09:44:11 2025-08-16
09:00:05 SYSTEM
240 240 0
2025-09-15 08:55:00 2025-08-16
08:10:00 SCOTT 45 30 15
2025-09-15 09:22:50 2025-08-20
11:00:00 BADUSER 18 0 18
2025-09-15 09:31:05 2025-09-14
18:00:00 DEV_USER 12 12 0
2025-09-14 15:00:00 2025-09-10
09:00:00 |
4.4 Failed Login Report — Security Alert Query
|
--
Security Alert: Users with 5+ failed logins in last 24 hours SELECT db_user_name AS username, os_user_name AS os_user, userhost AS source_host, COUNT(*) AS failed_attempts, MAX(event_timestamp) AS last_attempt, LISTAGG(TO_CHAR(return_code), ',') WITHIN
GROUP (ORDER BY event_timestamp) AS
error_codes FROM
unified_audit_trail WHERE
action_name = 'LOGON' AND return_code != 0 AND event_timestamp > SYSDATE - 1 GROUP
BY db_user_name, os_user_name, userhost HAVING
COUNT(*) >= 5 ORDER
BY failed_attempts DESC; |
|
USERNAME OS_USER
SOURCE_HOST FAILED
LAST_ATTEMPT
ERROR_CODES ----------
-------- ----------------- ------- ---------------------- ----------- BADUSER hacker
192.168.99.10 18 2025-09-15 09:31:05 1017,1017,1017,... SCOTT jsmith
192.168.1.45 8 2025-09-15 09:22:50 1017,28000,1017,... |
|
Security
Action: BADUSER from 192.168.99.10 made 18 failed attempts
with no successful logins — this is a brute-force attempt. Block the IP at
the network firewall and lock the account: ALTER USER BADUSER ACCOUNT LOCK; |
4.5 Query SYS Login Events from OS Audit Files
Because
audit_sys_operations=TRUE in your DB, SYS logins are also written to the adump
directory as XML files. Query them via DBA_COMMON_AUDIT_TRAIL:
|
--
SYS logins from OS-level audit (adump XML files) --
These appear in DBA_AUDIT_TRAIL for traditional audit SELECT TO_CHAR(timestamp#,'YYYY-MM-DD
HH24:MI:SS') AS login_time, userid AS db_user, os_user AS os_user, terminal AS terminal, machine AS client_machine, action_name AS action, returncode AS error_code FROM
dba_audit_trail WHERE
userid = 'SYS' AND timestamp# > SYSDATE - 7 ORDER
BY timestamp# DESC; --
Also check the OS adump directory for XML audit files: --
$ ls -lth /u01/app/oracle/admin/CDBPROD/adump/*.aud | head -20 |
|
|
|
LOGIN_TIME DB_USER OS_USER
TERMINAL CLIENT_MACHINE ACTION
ERROR_CODE ---------------------
-------- -------- ---------- --------------- ------- ---------- 2025-09-15
10:14:32 SYS oracle
pts/0 DBSERVER01 LOGON 0 2025-09-15
08:55:00 SYS oracle
pts/1 DBSERVER01 LOGON 0 2025-09-14
22:14:30 SYS root
pts/2 DBSERVER01 LOGON 1017 --
OS audit file check (run from OS shell): $
ls -lth /u01/app/oracle/admin/CDBPROD/adump/*.aud | head -5 -rw-r-----
1 oracle oinstall 4.2K Sep 15 10:14 cdbprod_ora_14523_20250915101432.aud -rw-r-----
1 oracle oinstall 3.8K Sep 15 08:55 cdbprod_ora_12011_20250915085500.aud -rw-r-----
1 oracle oinstall 3.1K Sep 14 22:14 cdbprod_ora_09987_20250914221430.aud |
4.6 Login Data Across All PDBs (CDB-Wide View)
|
--
Must be in CDB$ROOT to use CDB_ views ALTER
SESSION SET CONTAINER = CDB$ROOT; --
All logins across all PDBs in last 24 hours SELECT TO_CHAR(event_timestamp,'YYYY-MM-DD
HH24:MI:SS') AS login_time, con_id, CASE con_id WHEN 1 THEN 'CDB$ROOT' WHEN 3 THEN 'HRPDB' WHEN 4 THEN 'FINPDB' WHEN 5 THEN 'DEVPDB' ELSE 'CON_' || con_id END AS pdb_name, db_user_name AS username, userhost AS client_host, return_code AS err_code FROM
unified_audit_trail WHERE
action_name = 'LOGON' AND event_timestamp > SYSDATE - 1 ORDER
BY event_timestamp DESC; |
|
LOGIN_TIME CON_ID PDB_NAME
USERNAME CLIENT_HOST ERR_CODE ---------------------
------- --------- ---------- ---------------- -------- 2025-09-15
10:14:32 1 CDB$ROOT
SYS DBSERVER01 0 2025-09-15
10:08:17 3 HRPDB
HR_APP APPSERVER01 0 2025-09-15
09:58:44 3 HRPDB
HR_APP APPSERVER01 1017 2025-09-15
09:44:11 4 FINPDB
FIN_USER FINSERVER01 0 2025-09-15
09:31:05 1 CDB$ROOT
BADUSER 192.168.99.10 1017 2025-09-15
09:22:50 3 HRPDB
SCOTT 192.168.1.45 28000 |
5. Audit Policy Enable/Disable Checklist for CDBPROD
Use
this checklist as a reference to confirm the current state of your audit
configuration at any time:
|
Policy /
Parameter |
Purpose |
Status |
SQL to
Enable/Disable |
|
audit_trail=DB,EXTENDED |
Core audit trail — writes
SQL+binds to SYS.AUD$ |
ENABLED (your DB) |
ALTER SYSTEM SET audit_trail='DB,EXTENDED'
SCOPE=SPFILE + restart |
|
audit_sys_operations=TRUE |
OS-level log of all
SYS/SYSDBA commands |
ENABLED (your DB) |
ALTER SYSTEM SET
audit_sys_operations=TRUE SCOPE=SPFILE + restart |
|
ORA_LOGON_FAILURES |
Failed login capture |
Enable recommended |
AUDIT POLICY
ORA_LOGON_FAILURES; |
|
AUDIT_ALL_LOGONS (custom) |
ALL user logins success+fail |
Create + enable |
See Section 4.1 |
|
ORA_ACCOUNT_MGMT |
User create/drop/alter
auditing |
Enable recommended |
AUDIT POLICY
ORA_ACCOUNT_MGMT; |
|
ORA_SECURECONFIG |
Security config change
auditing |
Enable recommended |
AUDIT POLICY
ORA_SECURECONFIG; |
|
ORA_DATABASE_PARAMETER |
ALTER SYSTEM change auditing |
Enable recommended |
AUDIT POLICY
ORA_DATABASE_PARAMETER; |
|
ORA_CIS_RECOMMENDATIONS |
CIS benchmark compliance
checks |
Enable recommended |
AUDIT POLICY
ORA_CIS_RECOMMENDATIONS; |
|
AUDIT_PDB_LIFECYCLE |
PDB create/drop/plug/unplug |
Create + enable |
See previous report Section
3.3.3 |
End of Oracle 19c CDB Audit Report —
CDBPROD
