Oracle Database Audit

Oracle Database Audit function audit_trail: parameter settings enable or disable database audit functions: none, osdbdb, extendedxmlxml, extendednote: When the parameter is db, extended, or xml, extended records SQL bind and SQL text clob-type fields in the aud $ table. If the database was started in read-only mode with AUDIT_TRAIL set to db, extended, then Oracle Database internally sets AUDIT_TRAIL to OS. Check the alert log for details. then take a look at the database log:

alter database open read onlyAUDIT_TRAIL initialization parameter is changed to OS, as DB is NOT compatible for database opened with read-only accessSMON: enabling cache recoveryDatabase Characterset is WE8MSWIN1252Opening with Resource Manager plan: DEFAULT_MAINTENANCE_PLAN

 

We can see that when the database audit_tail parameter is set to db, but I still use open read only to open it, it will be automatically transferred to the OS, and when it starts normally, it will automatically go to the db status. You can view the audit_file_dest parameter to view the location of the OS file:

eg：SQL> show parameter auditNAME TYPE VALUE------------------------------------ ----------- ------------------------------audit_file_dest string /opt/app/oracle/admin/RHYS/adumpaudit_sys_operations boolean FALSEaudit_syslog_level stringaudit_trail string OSSQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL> startupORACLE instance started.Total System Global Area 405020672 bytesFixed Size 2213816 bytesVariable Size 251660360 bytesDatabase Buffers 146800640 bytesRedo Buffers 4345856 bytesDatabase mounted.Database opened.SQL> show parameter auditNAME TYPE VALUE------------------------------------ ----------- ------------------------------audit_file_dest string /opt/app/oracle/admin/RHYS/adumpaudit_sys_operations boolean FALSEaudit_syslog_level stringaudit_trail string DBSQL> 

 

Here, I suddenly think of a foreigner (Nagaraj S) problem, that is, if the audit content is very large, then the system tablespace will have insufficient space and how to delete it automatically. Afterwards, many foreign experts gave their own suggestions (such as Patterson, Joel, Steve Gardiner, Iggy Fernandez, Christopher, and so on) and read the following email: Hello Gurus, I have a task to purge aud $ table and it need to done in automatic wayon every month. please help on sharing the purge script to schedule in dbscheduler-Naga is ready. Then let's take a look at some good methods for foreigners. First, let's take a look at the Ulfet method: 1) Archive and purge aud $ table> Create a New tablespace, create a new Archive table, create a process, create a scheduling plan or crontab, and execute the check results. My experiment results are as follows:

eg：[root@oracle-one ~]# su - oracle[oracle@oracle-one ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.1.0 Production on Fri Aug 16 02:43:02 2013Copyright (c) 1982, 2009, Oracle. All rights reserved.Connected to an idle instance.SQL> startupORACLE instance started.Total System Global Area 405020672 bytesFixed Size 2213816 bytesVariable Size 251660360 bytesDatabase Buffers 146800640 bytesRedo Buffers 4345856 bytesDatabase mounted.Database opened.SQL> select sysdate from dual;SYSDATE---------16-AUG-13SQL> col name for a60SQL> select file#,name from v$datafile;FILE# NAME---------- ------------------------------------------------------------1 /opt/app/oracle/RHYS/system01.dbf2 /opt/app/oracle/RHYS/sysaux01.dbf3 /opt/app/oracle/RHYS/undotbs01.dbf4 /opt/app/oracle/RHYS/users01.dbf5 /opt/app/oracle/RHYS/test.dbfSQL> create tablespace arch_tbs datafile '/opt/app/oracle/RHYS/arch_tbs01.dbf' size 500M;Tablespace created.SQL> set pagesize 2000 SQL> select to_char(dbms_metadata.get_ddl('TABLE','AUD$')) FROM DUAL;TO_CHAR(DBMS_METADATA.GET_DDL('TABLE','AUD$'))--------------------------------------------------------------------------------CREATE TABLE "SYS"."AUD$"( "SESSIONID" NUMBER NOT NULL ENABLE,"ENTRYID" NUMBER NOT NULL ENABLE,"STATEMENT" NUMBER NOT NULL ENABLE,"TIMESTAMP#" DATE,"USERID" VARCHAR2(30),"USERHOST" VARCHAR2(128),"TERMINAL" VARCHAR2(255),"ACTION#" NUMBER NOT NULL ENABLE,"RETURNCODE" NUMBER NOT NULL ENABLE,"OBJ$CREATOR" VARCHAR2(30),"OBJ$NAME" VARCHAR2(128),"AUTH$PRIVILEGES" VARCHAR2(16),"AUTH$GRANTEE" VARCHAR2(30),"NEW$OWNER" VARCHAR2(30),"NEW$NAME" VARCHAR2(128),"SES$ACTIONS" VARCHAR2(19),"SES$TID" NUMBER,"LOGOFF$LREAD" NUMBER,"LOGOFF$PREAD" NUMBER,"LOGOFF$LWRITE" NUMBER,"LOGOFF$DEAD" NUMBER,"LOGOFF$TIME" DATE,"COMMENT$TEXT" VARCHAR2(4000),"CLIENTID" VARCHAR2(64),"SPARE1" VARCHAR2(255),"SPARE2" NUMBER,"OBJ$LABEL" RAW(255),"SES$LABEL" RAW(255),"PRIV$USED" NUMBER,"SESSIONCPU" NUMBER,"NTIMESTAMP#" TIMESTAMP (6),"PROXY$SID" NUMBER,"USER$GUID" VARCHAR2(32),"INSTANCE#" NUMBER,"PROCESS#" VARCHAR2(16),"XID" RAW(8),"AUDITID" VARCHAR2(64),"SCN" NUMBER,"DBID" NUMBER,"SQLBIND" CLOB,"SQLTEXT" CLOB,"OBJ$EDITION" VARCHAR2(30)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGINGSTORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)TABLESPACE "SYSTEM"LOB ("SQLBIND") STORE AS BASICFILE (TABLESPACE "SYSTEM" ENABLE STORAGE IN ROW CHUNK 8192 RETENTIONNOCACHE LOGGINGSTORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))LOB ("SQLTEXT") STORE AS BASICFILE (TABLESPACE "SYSTEM" ENABLE STORAGE IN ROW CHUNK 8192 RETENTIONNOCACHE LOGGINGSTORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))SQL> SQL> CREATE TABLE sys.AUD$_ARCH2 ( SESSIONID NUMBER NOT NULL ENABLE,3 ENTRYID NUMBER NOT NULL ENABLE,4 STATEMENT NUMBER NOT NULL ENABLE,5 TIMESTAMP# DATE,6 USERID VARCHAR2(30),7 USERHOST VARCHAR2(128),8 TERMINAL VARCHAR2(255),9 ACTION# NUMBER NOT NULL ENABLE,10 RETURNCODE NUMBER NOT NULL ENABLE,11 OBJ$CREATOR VARCHAR2(30),12 OBJ$NAME VARCHAR2(128),13 AUTH$PRIVILEGES VARCHAR2(16),14 AUTH$GRANTEE VARCHAR2(30),15 NEW$OWNER VARCHAR2(30),16 NEW$NAME VARCHAR2(128),SES$ACTIONS VARCHAR2(19),17 18 SES$TID NUMBER,19 LOGOFF$LREAD NUMBER,20 LOGOFF$PREAD NUMBER,21 LOGOFF$LWRITE NUMBER,22 LOGOFF$DEAD NUMBER,23 LOGOFF$TIME DATE,24 COMMENT$TEXT VARCHAR2(4000),25 CLIENTID VARCHAR2(64),26 SPARE1 VARCHAR2(255),27 SPARE2 NUMBER,28 OBJ$LABEL RAW(255),29 SES$LABEL RAW(255),30 PRIV$USED NUMBER,31 SESSIONCPU NUMBER,32 NTIMESTAMP# TIMESTAMP (6),33 PROXY$SID NUMBER,34 USER$GUID VARCHAR2(32),35 INSTANCE# NUMBER,36 PROCESS# VARCHAR2(16),37 XID RAW(8),38 AUDITID VARCHAR2(64),39 SCN NUMBER,40 DBID NUMBER,41 SQLBIND CLOB,42 SQLTEXT CLOB,43 OBJ$EDITION VARCHAR2(30)44 ) 45 tablespace arch_tbs46 nologging;Table created.