Summary
References
Oracle ® Manuals
- Oracle ® 12.1 Database PL/SQL Packages and Types Reference
- Oracle ® 12.1 Database Reference
- Oracle ® 12.1 Database Security Guide
- 21 Introduction to Auditing
- 23 Administering the Audit Trail
- Managing the Unified Audit Trail
- Writing the Unified Audit Trail Records to the AUDSYS Schema
- About Writing Unified Audit Trail Records to AUDSYS
- Setting the Write Mode for Unified Audit Trail Records
- Manually Flushing Audit Records to the Audit Trail in Queued-Write Mode
- Moving Operating System Audit Records into the Unified Audit Trail
- Archiving the Audit Trail
- Purging Audit Trail Records
- Oracle ® 12.1 Database Upgrade Guide
My Oracle Support
Notes
What is Unified Auditing?
What is Unified Auditing? says:
The unified audit trail, which resides in a read-only table in the
AUDSYSschema in theSYSAUXtablespace, makes this information available in a uniform format in theUNIFIED_AUDIT_TRAILdata dictionary view, and is available in both single-instance and Oracle Database Real Application Clusters environments. In addition to the userSYS, users who have been granted theAUDIT_ADMINandAUDIT_VIEWERroles can query these views. If your users only need to query the views but not create audit policies, then grant them theAUDIT_VIEWERrole.When the database is writeable, audit records are written to the unified audit trail. If the database is not writable, then audit records are written to new format operating system files in the
$ORACLE_BASE/audit/$ORACLE_SIDdirectory.
Missing Procedure
Moving Operating System Audit Records into the Unified Audit Trail recommends:
From the previous release, the following modes, deprecated but retained for backward compatibility, are available. However, Oracle recommends that you use the
DBMS_AUDIT_MGMT.TRANSFER_UNIFIED_AUDIT_RECORDSprocedure instead, as described in Oracle Database Upgrade Guide .
This is no mention of DBMS_AUDIT_MGMT.TRANSFER_UNIFIED_AUDIT_RECORDS in either Oracle ® 12.1 Database Upgrade Guide or Oracle ® 12.1 Database PL/SQL Packages and Types Reference .
This procedure does not exist in Oracle RDBMS 12.1:
SQL>exec DBMS_AUDIT_MGMT.TRANSFER_UNIFIED_AUDIT_RECORDS
BEGIN DBMS_AUDIT_MGMT.TRANSFER_UNIFIED_AUDIT_RECORDS; END;
*
ERROR at line 1:
ORA-06550: line 1, column 23:
PLS-00302: component 'TRANSFER_UNIFIED_AUDIT_RECORDS' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
According to How To Transfer Unified Audit Records To An Internal Relational Table (Doc ID 2212196.1) ,
The procedure is available out of the box in the Oracle RDBMS 12.2 database and in the Oracle RDBMS 12.1.0.2 databases that have been patched with Patch 25985768 .
Procedure
Enable Unified Auditing
Followed the procedure in 4.6.13.2 Migrating to Unified Auditing for Oracle Database :
[oracle@personal ~]$ lsnrctl stop
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 15-APR-2018 21:30:19
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
The command completed successfully
[oracle@personal ~]$ . oraenv
ORACLE_SID = [personal] ? jar
The Oracle base remains unchanged with value /opt/app/oracle
[oracle@personal ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Sun Apr 15 21:31:07 2018
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1543503872 bytes
Fixed Size 2924736 bytes
Variable Size 939528000 bytes
Database Buffers 587202560 bytes
Redo Buffers 13848576 bytes
Database mounted.
Database opened.
SQL> SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing';
VALUE
----------------------------------------------------------------
FALSE
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@personal ~]$ cd ${ORACLE_HOME}/rdbms/lib
[oracle@personal lib]$ make -f ins_rdbms.mk uniaud_on ioracle ORACLE_HOME=$ORACLE_HOME
/usr/bin/ar d /opt/app/oracle/product/12.1.0/dbhome_1/rdbms/lib/libknlopt.a kzanang.o
/usr/bin/ar cr /opt/app/oracle/product/12.1.0/dbhome_1/rdbms/lib/libknlopt.a /opt/app/oracle/product/12.1.0/dbhome_1/rdbms/lib/kzaiang.o
chmod 755 /opt/app/oracle/product/12.1.0/dbhome_1/bin
- Linking Oracle
rm -f /opt/app/oracle/product/12.1.0/dbhome_1/rdbms/lib/oracle
/opt/app/oracle/product/12.1.0/dbhome_1/bin/orald -o /opt/app/oracle/product/12.1.0/dbhome_1/rdbms/lib/oracle -m64 -z noexecstack -Wl,--disable-new-dtags -L/opt/app/oracle/product/12.1.0/dbhome_1/rdbms/lib/ -L/opt/app/oracle/product/12.1.0/dbhome_1/lib/ -L/opt/app/oracle/product/12.1.0/dbhome_1/lib/stubs/ -Wl,-E /opt/app/oracle/product/12.1.0/dbhome_1/rdbms/lib/opimai.o /opt/app/oracle/product/12.1.0/dbhome_1/rdbms/lib/ssoraed.o /opt/app/oracle/product/12.1.0/dbhome_1/rdbms/lib/ttcsoi.o -Wl,--whole-archive -lperfsrv12 -Wl,--no-whole-archive /opt/app/oracle/product/12.1.0/dbhome_1/lib/nautab.o /opt/app/oracle/product/12.1.0/dbhome_1/lib/naeet.o /opt/app/oracle/product/12.1.0/dbhome_1/lib/naect.o /opt/app/oracle/product/12.1.0/dbhome_1/lib/naedhs.o /opt/app/oracle/product/12.1.0/dbhome_1/rdbms/lib/config.o -lserver12 -lodm12 -lcell12 -lnnet12 -lskgxp12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lxml12 -lcore12 -lunls12 -lsnls12 -lnls12 -lcore12 -lnls12 -lclient12 -lvsn12 -lcommon12 -lgeneric12 -lknlopt `if /usr/bin/ar tv /opt/app/oracle/product/12.1.0/dbhome_1/rdbms/lib/libknlopt.a | grep xsyeolap.o > /dev/null 2>&1 ; then echo "-loraolap12" ; fi` -lskjcx12 -lslax12 -lpls12 -lrt -lplp12 -lserver12 -lclient12 -lvsn12 -lcommon12 -lgeneric12 `if [ -f /opt/app/oracle/product/12.1.0/dbhome_1/lib/libavserver12.a ] ; then echo "-lavserver12" ; else echo "-lavstub12"; fi` `if [ -f /opt/app/oracle/product/12.1.0/dbhome_1/lib/libavclient12.a ] ; then echo "-lavclient12" ; fi` -lknlopt -lslax12 -lpls12 -lrt -lplp12 -ljavavm12 -lserver12 -lwwg `cat /opt/app/oracle/product/12.1.0/dbhome_1/lib/ldflags` -lncrypt12 -lnsgr12 -lnzjs12 -ln12 -lnl12 -lnro12 `cat /opt/app/oracle/product/12.1.0/dbhome_1/lib/ldflags` -lncrypt12 -lnsgr12 -lnzjs12 -ln12 -lnl12 -lnnzst12 -lzt12 -lztkg12 -lmm -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lxml12 -lcore12 -lunls12 -lsnls12 -lnls12 -lcore12 -lnls12 -lztkg12 `cat /opt/app/oracle/product/12.1.0/dbhome_1/lib/ldflags` -lncrypt12 -lnsgr12 -lnzjs12 -ln12 -lnl12 -lnro12 `cat /opt/app/oracle/product/12.1.0/dbhome_1/lib/ldflags` -lncrypt12 -lnsgr12 -lnzjs12 -ln12 -lnl12 -lnnzst12 -lzt12 -lztkg12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lxml12 -lcore12 -lunls12 -lsnls12 -lnls12 -lcore12 -lnls12 `if /usr/bin/ar tv /opt/app/oracle/product/12.1.0/dbhome_1/rdbms/lib/libknlopt.a | grep "kxmnsd.o" > /dev/null 2>&1 ; then echo " " ; else echo "-lordsdo12 -lserver12"; fi` -L/opt/app/oracle/product/12.1.0/dbhome_1/ctx/lib/ -lctxc12 -lctx12 -lzx12 -lgx12 -lctx12 -lzx12 -lgx12 -lordimt12 -lclsra12 -ldbcfg12 -lhasgen12 -lskgxn2 -lnnzst12 -lzt12 -lxml12 -locr12 -locrb12 -locrutl12 -lhasgen12 -lskgxn2 -lnnzst12 -lzt12 -lxml12 -lgeneric12 -loraz -llzopro -lorabz2 -lipp_z -lipp_bz2 -lippdcemerged -lippsemerged -lippdcmerged -lippsmerged -lippcore -lippcpemerged -lippcpmerged -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lxml12 -lcore12 -lunls12 -lsnls12 -lnls12 -lcore12 -lnls12 -lsnls12 -lunls12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lxml12 -lcore12 -lunls12 -lsnls12 -lnls12 -lcore12 -lnls12 -lasmclnt12 -lcommon12 -lcore12 -laio -lons `cat /opt/app/oracle/product/12.1.0/dbhome_1/lib/sysliblist` -Wl,-rpath,/opt/app/oracle/product/12.1.0/dbhome_1/lib -lm `cat /opt/app/oracle/product/12.1.0/dbhome_1/lib/sysliblist` -ldl -lm -L/opt/app/oracle/product/12.1.0/dbhome_1/lib
test ! -f /opt/app/oracle/product/12.1.0/dbhome_1/bin/oracle ||\
mv -f /opt/app/oracle/product/12.1.0/dbhome_1/bin/oracle /opt/app/oracle/product/12.1.0/dbhome_1/bin/oracleO
mv /opt/app/oracle/product/12.1.0/dbhome_1/rdbms/lib/oracle /opt/app/oracle/product/12.1.0/dbhome_1/bin/oracle
chmod 6751 /opt/app/oracle/product/12.1.0/dbhome_1/bin/oracle
[oracle@personal lib]$ lsnrctl start
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 15-APR-2018 21:40:51
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Starting /opt/app/oracle/product/12.1.0/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 12.1.0.2.0 - Production
System parameter file is /opt/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Log messages written to /opt/app/oracle/diag/tnslsnr/personal/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=personal)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 15-APR-2018 21:40:52
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /opt/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File /opt/app/oracle/diag/tnslsnr/personal/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=personal)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully
[oracle@personal lib]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Sun Apr 15 21:41:02 2018
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1543503872 bytes
Fixed Size 2924736 bytes
Variable Size 939528000 bytes
Database Buffers 587202560 bytes
Redo Buffers 13848576 bytes
Database mounted.
Database opened.
SQL> SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing';
VALUE
----------------------------------------------------------------
TRUE
SQL>
Summary:
- Stop all listeners and database instances
- Rebuild the kernel using the uniaud_on option
- Start all listeners and database instances
Configure Unified Auditing
Generate OS Audit Records and Load Them into Audit Table
This scenario is based upon Moving Operating System Audit Records into the Unified Audit Trail .
Open Database as Read-Only
First, I open the database as read-only:
[oracle@personal ~]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Thu Apr 19 12:23:38 2018 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics, Real Application Testing and Unified Auditing options SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup open read only ORACLE instance started. Total System Global Area 1543503872 bytes Fixed Size 2924736 bytes Variable Size 939528000 bytes Database Buffers 587202560 bytes Redo Buffers 13848576 bytes Database mounted. Database opened. SQL> select open_mode from v$database; OPEN_MODE -------------------- READ ONLY
Generate OS Audit Records
Next, I generate some audit records:
SQL> connect system Enter password: ERROR: ORA-28032: Your password has expired and the database is set to read-only Warning: You are no longer connected to ORACLE. SQL> connect / as sysdba Connected. SQL> connect demo Enter password: Connected.
Next, I check for the generation of OS Audit records:
SQL> !ls -lth /opt/app/oracle/audit/personal/ total 104K -rw-r-----. 1 oracle oinstall 10K Apr 19 12:24 ora_audit_11.bin -rw-r-----. 1 oracle oinstall 6.0K Apr 19 12:24 ora_audit_00.bin -rw-r-----. 1 oracle oinstall 11K Apr 19 12:24 ora_audit_10.bin -rw-r-----. 1 oracle oinstall 1.5K Apr 19 12:24 ora_audit_092.bin -rw-r-----. 1 oracle oinstall 1.5K Apr 19 12:24 ora_audit_192.bin -rw-r-----. 1 oracle oinstall 8.5K Apr 19 08:44 ora_audit_141.bin -rw-r-----. 1 oracle oinstall 1.5K Apr 18 23:12 ora_audit_082.bin -rw-r-----. 1 oracle oinstall 1.5K Apr 18 23:12 ora_audit_182.bin -rw-r-----. 1 oracle oinstall 2.0K Apr 17 21:28 ora_audit_087.bin -rw-r-----. 1 oracle oinstall 2.5K Apr 17 21:28 ora_audit_187.bin -rw-r-----. 1 oracle oinstall 3.5K Apr 17 18:13 ora_audit_973707194_3058604836.bin -rw-r-----. 1 oracle oinstall 1.5K Apr 17 12:46 ora_audit_070.bin -rw-r-----. 1 oracle oinstall 1.5K Apr 17 12:45 ora_audit_170.bin -rw-r-----. 1 oracle oinstall 3.5K Apr 17 07:27 ora_audit_973668475_2422469920.bin -rw-r-----. 1 oracle oinstall 4.0K Apr 16 19:22 ora_audit_143.bin -rw-r-----. 1 oracle oinstall 8.5K Apr 16 19:21 ora_audit_973624908_1453341488.bin -rw-r-----. 1 oracle oinstall 1.5K Oct 4 2015 ora_audit_159.bin SQL> !file /opt/app/oracle/audit/personal/ora_audit_11.bin /opt/app/oracle/audit/personal/ora_audit_11.bin: data
Open Database as Read-Write
Next, open the database as read-write:
SQL> connect / as sysdba Connected. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup open read write ORACLE instance started. Total System Global Area 1543503872 bytes Fixed Size 2924736 bytes Variable Size 939528000 bytes Database Buffers 587202560 bytes Redo Buffers 13848576 bytes Database mounted. Database opened. SQL> select open_mode from v$database; OPEN_MODE -------------------- READ WRITE
Load OS Audit Records into Audit Table
Now, load the OS audit records into the audit table using the DBMS_AUDIT_MGMT. LOAD_UNIFIED_AUDIT_FILES procedure:
SQL> select count(*) from unified_audit_trail;
COUNT(*)
----------
26393
SQL> exec DBMS_AUDIT_MGMT.LOAD_UNIFIED_AUDIT_FILES
PL/SQL procedure successfully completed.
SQL> select count(*) from unified_audit_trail;
COUNT(*)
----------
26410
SQL> !ls -lth /opt/app/oracle/audit/personal/
total 0
Display Audit Log
I ran the following SQL to get the audit entries for the above scenario:
SELECT event_timestamp, dbusername, audit_type, action_name, sql_text, unified_audit_policies FROM unified_audit_trail WHERE event_timestamp > systimestamp - interval '6' hour ORDER BY event_timestamp
The result was:
| EVENT_TIMESTAMP | DBUSERNAME | AUDIT_TYPE | ACTION_NAME | SQL_TEXT | UNIFIED_AUDIT_POLICIES |
|---|---|---|---|---|---|
| 19-APR-18 12.24.01.754587000 PM | SYS | Standard | ALTER DATABASE | ALTER DATABASE CLOSE NORMAL | |
| 19-APR-18 12.24.02.951241000 PM | SYS | Standard | ALTER DATABASE | ALTER DATABASE DISMOUNT | |
| 19-APR-18 12.24.03.404289000 PM | SYS | Standard | SHUTDOWN | SHUTDOWN IMMEDIATE | |
| 19-APR-18 12.24.17.186108000 PM | SYS | Standard | LOGON | CONNECT | |
| 19-APR-18 12.24.28.918061000 PM | SYS | Standard | STARTUP | STARTUP | |
| 19-APR-18 12.24.29.459443000 PM | SYS | Standard | LOGON | ||
| 19-APR-18 12.24.29.766584000 PM | SYS | Standard | SELECT | SELECT DECODE(null,'','Total System Global Area','') NAME_COL_PLUS_SHOW_SGA, SUM(VALUE), DECODE (null,'', 'bytes','') units_col_plus_show_sga FROM V$SGA UNION ALL SELECT NAME NAME_COL_PLUS_SHOW_SGA , VALUE, DECODE (null,'', 'bytes','') units_col_plus_show_sga FROM V$SGA | |
| 19-APR-18 12.24.36.489172000 PM | SYS | Standard | ALTER DATABASE | ALTER DATABASE MOUNT | |
| 19-APR-18 12.24.36.496004000 PM | SYS | Standard | LOGOFF | ||
| 19-APR-18 12.24.36.525129000 PM | SYS | Standard | LOGON | ||
| 19-APR-18 12.24.43.689182000 PM | DBSNMP | Standard | LOGON | ||
| 19-APR-18 12.24.43.906164000 PM | DBSNMP | Standard | LOGON | ||
| 19-APR-18 12.24.43.921831000 PM | DBSNMP | Standard | LOGON | ||
| 19-APR-18 12.24.58.687069000 PM | DBSNMP | Standard | LOGON | ||
| 19-APR-18 12.25.00.590981000 PM | SYS | Standard | ALTER DATABASE | ALTER DATABASE OPEN READ ONLY | ORA_SECURECONFIG |
| 19-APR-18 12.25.14.532989000 PM | DBSNMP | Standard | SET ROLE | set role all | ORA_SECURECONFIG |
| 19-APR-18 12.25.52.403446000 PM | SYSTEM | Standard | LOGON | ORA_LOGON_FAILURES | |
| 19-APR-18 12.30.54.052754000 PM | SYS | Standard | ALTER DATABASE | ALTER DATABASE CLOSE NORMAL | |
| 19-APR-18 12.30.55.157716000 PM | SYS | Standard | ALTER DATABASE | ALTER DATABASE DISMOUNT | |
| 19-APR-18 12.30.55.649103000 PM | SYS | Standard | SHUTDOWN | SHUTDOWN IMMEDIATE | |
| 19-APR-18 12.31.09.459840000 PM | SYS | Standard | LOGON | CONNECT | |
| 19-APR-18 12.31.23.641632000 PM | SYS | Standard | STARTUP | STARTUP | |
| 19-APR-18 12.31.24.128550000 PM | SYS | Standard | LOGON | ||
| 19-APR-18 12.31.24.426507000 PM | SYS | Standard | SELECT | SELECT DECODE(null,'','Total System Global Area','') NAME_COL_PLUS_SHOW_SGA, SUM(VALUE), DECODE (null,'', 'bytes','') units_col_plus_show_sga FROM V$SGA UNION ALL SELECT NAME NAME_COL_PLUS_SHOW_SGA , VALUE, DECODE (null,'', 'bytes','') units_col_plus_show_sga FROM V$SGA | |
| 19-APR-18 12.31.31.066877000 PM | SYS | Standard | ALTER DATABASE | ALTER DATABASE MOUNT | |
| 19-APR-18 12.31.31.067772000 PM | SYS | Standard | LOGOFF | ||
| 19-APR-18 12.31.31.083152000 PM | SYS | Standard | LOGON | ||
| 19-APR-18 12.31.46.619097000 PM | DBSNMP | Standard | LOGON | ||
| 19-APR-18 12.32.00.265742000 PM | SYS | Standard | ALTER DATABASE | ALTER DATABASE OPEN | ORA_SECURECONFIG |
| 19-APR-18 12.32.04.789271000 PM | DBSNMP | Standard | SET ROLE | set role all | ORA_SECURECONFIG |
| 19-APR-18 12.34.59.775654000 PM | SYS | Standard | EXECUTE | BEGIN DBMS_AUDIT_MGMT.LOAD_UNIFIED_AUDIT_FILES; END; | |
| 19-APR-18 12.51.39.127796000 PM | SYS | Standard | ALTER DATABASE | ALTER DATABASE CLOSE NORMAL | |
| 19-APR-18 12.51.40.201923000 PM | SYS | Standard | ALTER DATABASE | ALTER DATABASE DISMOUNT | |
| 19-APR-18 12.51.40.609504000 PM | SYS | Standard | SHUTDOWN | SHUTDOWN IMMEDIATE | |
| 19-APR-18 12.51.54.230728000 PM | SYS | Standard | LOGON | CONNECT | |
| 19-APR-18 04.18.21.446594000 PM | SYS | Standard | LOGON | CONNECT | |
| 19-APR-18 04.18.30.195187000 PM | SYS | Standard | STARTUP | STARTUP | |
| 19-APR-18 04.18.31.128071000 PM | SYS | Standard | LOGON | ||
| 19-APR-18 04.18.32.275693000 PM | SYS | Standard | SELECT | SELECT DECODE(null,'','Total System Global Area','') NAME_COL_PLUS_SHOW_SGA, SUM(VALUE), DECODE (null,'', 'bytes','') units_col_plus_show_sga FROM V$SGA UNION ALL SELECT NAME NAME_COL_PLUS_SHOW_SGA , VALUE, DECODE (null,'', 'bytes','') units_col_plus_show_sga FROM V$SGA | |
| 19-APR-18 04.18.45.445543000 PM | SYS | Standard | ALTER DATABASE | ALTER DATABASE MOUNT | |
| 19-APR-18 04.18.45.448749000 PM | SYS | Standard | LOGOFF | ||
| 19-APR-18 04.18.45.473787000 PM | SYS | Standard | LOGON | ||
| 19-APR-18 04.18.54.641736000 PM | DBSNMP | Standard | LOGON | ||
| 19-APR-18 04.19.09.232185000 PM | DBSNMP | Standard | LOGON | ||
| 19-APR-18 04.19.24.211608000 PM | DBSNMP | Standard | LOGON | ||
| 19-APR-18 04.19.39.241177000 PM | DBSNMP | Standard | LOGON | ||
| 19-APR-18 04.19.54.210791000 PM | DBSNMP | Standard | LOGON | ||
| 19-APR-18 04.20.09.212651000 PM | DBSNMP | Standard | LOGON | ||
| 19-APR-18 04.20.24.206411000 PM | DBSNMP | Standard | LOGON | ||
| 19-APR-18 04.20.39.216334000 PM | DBSNMP | Standard | LOGON | ||
| 19-APR-18 04.20.54.208924000 PM | DBSNMP | Standard | LOGON | ||
| 19-APR-18 04.21.09.208645000 PM | DBSNMP | Standard | LOGON | ||
| 19-APR-18 04.21.24.206576000 PM | DBSNMP | Standard | LOGON | ||
| 19-APR-18 04.21.39.200686000 PM | DBSNMP | Standard | LOGON | ||
| 19-APR-18 04.21.54.190210000 PM | DBSNMP | Standard | LOGON | ||
| 19-APR-18 04.22.09.234622000 PM | DBSNMP | Standard | LOGON | ||
| 19-APR-18 04.22.24.209157000 PM | DBSNMP | Standard | LOGON | ||
| 19-APR-18 04.22.39.208905000 PM | DBSNMP | Standard | LOGON | ||
| 19-APR-18 04.22.54.232325000 PM | DBSNMP | Standard | LOGON | ||
| 19-APR-18 04.23.09.207302000 PM | DBSNMP | Standard | LOGON | ||
| 19-APR-18 04.23.24.216992000 PM | DBSNMP | Standard | LOGON | ||
| 19-APR-18 04.23.39.202139000 PM | DBSNMP | Standard | LOGON | ||
| 19-APR-18 04.24.25.142017000 PM | SYS | Standard | ALTER DATABASE | ALTER DATABASE OPEN | ORA_SECURECONFIG |
| 19-APR-18 04.25.27.745454000 PM | DBSNMP | Standard | SET ROLE | set role all | ORA_SECURECONFIG |
| 19-APR-18 04.25.49.034433000 PM | DBSNMP | Standard | SET ROLE | set role all | ORA_SECURECONFIG |
Manually Flushing Audit Records to the Audit Trail in Queued-Write Mode
Followed the procedure in Manually Flushing Audit Records to the Audit Trail in Queued-Write Mode .
Flushing audit records is only applicable if the Audit Write Mode is set to Queued .
Check Audit Write Mode
The following snippet of code shows that the Audit Write Mode is set to Queued :
COLUMN parameter_name FORMAT A30 COLUMN parameter_value FORMAT A30 SELECT parameter_name, parameter_value FROM DBA_AUDIT_MGMT_CONFIG_PARAMS WHERE parameter_name = 'AUDIT WRITE MODE' AND audit_trail = 'UNIFIED AUDIT TRAIL' /
The output is:
PARAMETER_NAME PARAMETER_VALUE ------------------------------ ------------------------------ AUDIT WRITE MODE QUEUED WRITE MODE
Manually Flush Audit Records
The DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL procedure is called to manually flush the audit records to disk:
EXEC DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL;
The output is simply:
PL/SQL procedure successfully completed.
Change Audit Write Mode
Check Audit Write Mode
The following snippet of code shows that the Audit Write Mode is set to Queued :
COLUMN parameter_name FORMAT A30 COLUMN parameter_value FORMAT A30 SELECT parameter_name, parameter_value FROM DBA_AUDIT_MGMT_CONFIG_PARAMS WHERE parameter_name = 'AUDIT WRITE MODE' AND audit_trail = 'UNIFIED AUDIT TRAIL' /
The output is:
PARAMETER_NAME PARAMETER_VALUE ------------------------------ ------------------------------ AUDIT WRITE MODE QUEUED WRITE MODE
Switch Audit Write Mode to Immediate
The DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY procedure is called to set the Audit Write Mode to Immediate :
BEGIN DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY( DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, DBMS_AUDIT_MGMT.AUDIT_TRAIL_WRITE_MODE, DBMS_AUDIT_MGMT.AUDIT_TRAIL_IMMEDIATE_WRITE); END; /
The output is simply:
PL/SQL procedure successfully completed.
Check Audit Write Mode
The following snippet of code shows that the Audit Write Mode is now set to Immediate :
COLUMN parameter_name FORMAT A30 COLUMN parameter_value FORMAT A30 SELECT parameter_name, parameter_value FROM DBA_AUDIT_MGMT_CONFIG_PARAMS WHERE parameter_name = 'AUDIT WRITE MODE' AND audit_trail = 'UNIFIED AUDIT TRAIL' /
The output is:
PARAMETER_NAME PARAMETER_VALUE ------------------------------ ------------------------------ AUDIT WRITE MODE IMMEDIATE WRITE MODE
Switch Audit Write Mode to Queued
The DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY procedure is called to set the Audit Write Mode to Queued :
BEGIN DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY( DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, DBMS_AUDIT_MGMT.AUDIT_TRAIL_WRITE_MODE, DBMS_AUDIT_MGMT.AUDIT_TRAIL_QUEUED_WRITE); END; /
The output is simply:
PL/SQL procedure successfully completed.
Check Audit Write Mode
The following snippet of code shows that the Audit Write Mode is now set to Queued :
COLUMN parameter_name FORMAT A30 COLUMN parameter_value FORMAT A30 SELECT parameter_name, parameter_value FROM DBA_AUDIT_MGMT_CONFIG_PARAMS WHERE parameter_name = 'AUDIT WRITE MODE' AND audit_trail = 'UNIFIED AUDIT TRAIL' /
The output is:
PARAMETER_NAME PARAMETER_VALUE ------------------------------ ------------------------------ AUDIT WRITE MODE QUEUED WRITE MODE
Alternative Method to Get Audit Write Mode
The following snippet of code shows that the Audit Write Mode can be gotten by a PL/SQL procedure call to DBMS_AUDIT_MGMT.GET_AUDIT_TRAIL_PROPERTY_VALUE :
VARIABLE write_mode NUMBER
BEGIN
:write_mode
:= dbms_audit_mgmt.get_audit_trail_property_value(
audit_trail_type => dbms_audit_mgmt.audit_trail_unified,
audit_trail_property => dbms_audit_mgmt.audit_trail_write_mode
);
END;
/
PRINT write_mode
The output is:
PL/SQL procedure successfully completed.
WRITE_MODE
----------
1
These values are not in the online documentation. However, by examining the package definition, one finds the following snippet of code:
-- -- NG Audit Trail write mode configuration AUDIT_TRAIL_WRITE_MODE CONSTANT NUMBER := 33; -- Values for Write mode AUDIT_TRAIL_QUEUED_WRITE CONSTANT NUMBER := 1; AUDIT_TRAIL_IMMEDIATE_WRITE CONSTANT NUMBER := 2; --AUDIT_TRAIL_COMMIT_WRITE CONSTANT NUMBER := 3;
Thus, 1 corresponds to Queued Write .