熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

oracle數據庫審計功能

2013-11-13 22:14:16  來源: Oracle 

  oracle數據庫審計功能

  audit_trail參數設置啟動或是關閉數據庫的審計功能

  有none

  os

  db

  dbextended

  xml

  xmlextended

  note

  當參數為dbextended或是xmlextended的時候會在aud$表中額外記錄sql bind 和sql text clobtype的字段

  If the database was started in readonly mode with AUDIT_TRAIL set to db extended then Oracle Database internally sets AUDIT_TRAIL to os Check the alert log for details

  然後看一下數據庫日志

  alter database open read only

  AUDIT_TRAIL initialization parameter is changed to OS as DB is NOT compatible for database opened with readonly access

  SMON: enabling cache recovery

  Database Characterset is WEMSWIN

  Opening with Resource Manager plan: DEFAULT_MAINTENANCE_PLAN

  可以看到當數據庫audit_tail參數設置為db但是我依然使用open read only打開的話那麼會自動轉到os當正常啟動後那麼會自動轉到db狀態

  可以查看audit_file_dest參數進行查看os文件所在位置

  eg

  SQL> show parameter audit

  NAME TYPE VALUE

  

  audit_file_dest string /opt/app/oracle/admin/RHYS/adu

  mp

  audit_sys_operations boolean FALSE

  audit_syslog_level string

  audit_trail string OS

  SQL> shutdown immediate

  Database closed

  Database dismounted

  ORACLE instance shut down

  SQL> startup

  ORACLE instance started

  Total System Global Area bytes

  Fixed Size bytes

  Variable Size bytes

  Database Buffers bytes

  Redo Buffers bytes

  Database mounted

  Database opened

  SQL> show parameter audit

  NAME TYPE VALUE

  

  audit_file_dest string /opt/app/oracle/admin/RHYS/adu

  mp

  audit_sys_operations boolean FALSE

  audit_syslog_level string

  audit_trail string DB

  SQL>

  這裡我突然想起了一個老外(Nagaraj S)的問題那就是如果審計內容非常大的話那麼system表空間會存在 空間不足情況怎麼自動刪除隨後很多國外的專家分別給出了自己的建議(如PattersonJoelSteve GardinerIggy FernandezChristopher等等)看如下郵件信息

  Hello Gurus

  I have a task to purge aud$ table and it need to done in automated way

  on every month Please help on sharing the purge script to schedule in db

  scheduler

  Naga

  好了然後我們看看老外有什麼好的辦法首先看看Ulfet的方法

  )Archive and purge aud$ table

  >創建新的表空間創建新的歸檔表 創建過程創建調度計劃或是crontab執行檢查結果

  我實驗結果如下

  eg

  [root@oracleone ~]# su oracle

  [oracle@oracleone ~]$ sqlplus / as sysdba

  SQL*Plus: Release Production on Fri Aug ::

  Copyright (c) Oracle All rights reserved

  Connected to an idle instance

  SQL> startup

  ORACLE instance started

  Total System Global Area bytes

  Fixed Size bytes

  Variable Size bytes

  Database Buffers bytes

  Redo Buffers bytes

  Database mounted

  Database opened

  SQL> select sysdate from dual;

  SYSDATE

  

  AUG

  SQL> col name for a

  SQL> select file#name from v$datafile;

  FILE# NAME

  

   /opt/app/oracle/RHYS/systemdbf

   /opt/app/oracle/RHYS/sysauxdbf

   /opt/app/oracle/RHYS/undotbsdbf

   /opt/app/oracle/RHYS/usersdbf

   /opt/app/oracle/RHYS/testdbf

  SQL> create tablespace arch_tbs datafile /opt/app/oracle/RHYS/arch_tbsdbf size M;

  Tablespace created

  SQL> set pagesize

  SQL> select to_char(dbms_metadataget_ddl(TABLEAUD$)) FROM DUAL;

  TO_CHAR(DBMS_METADATAGET_DDL(TABLEAUD$))

  

  CREATE TABLE SYSAUD$

  ( SESSIONID NUMBER NOT NULL ENABLE

  ENTRYID NUMBER NOT NULL ENABLE

  STATEMENT NUMBER NOT NULL ENABLE

  TIMESTAMP# DATE

  USERID VARCHAR()

  USERHOST VARCHAR()

  TERMINAL VARCHAR()

  ACTION# NUMBER NOT NULL ENABLE

  RETURNCODE NUMBER NOT NULL ENABLE

  OBJ$CREATOR VARCHAR()

  OBJ$NAME VARCHAR()

  AUTH$PRIVILEGES VARCHAR()

  AUTH$GRANTEE VARCHAR()

  NEW$OWNER VARCHAR()

  NEW$NAME VARCHAR()

  SES$ACTIONS VARCHAR()

  SES$TID NUMBER

  LOGOFF$LREAD NUMBER

  LOGOFF$PREAD NUMBER

  LOGOFF$LWRITE NUMBER

  LOGOFF$DEAD NUMBER

  LOGOFF$TIME DATE

  COMMENT$TEXT VARCHAR()

  CLIENTID VARCHAR()

  SPARE VARCHAR()

  SPARE NUMBER

  OBJ$LABEL RAW()

  SES$LABEL RAW()

  PRIV$USED NUMBER

  SESSIONCPU NUMBER

  NTIMESTAMP# TIMESTAMP ()

  PROXY$SID NUMBER

  USER$GUID VARCHAR()

  INSTANCE# NUMBER

  PROCESS# VARCHAR()

  XID RAW()

  AUDITID VARCHAR()

  SCN NUMBER

  DBID NUMBER

  SQLBIND CLOB

  SQLTEXT CLOB

  OBJ$EDITION VARCHAR()

  ) PCTFREE PCTUSED INITRANS MAXTRANS NOCOMPRESS LOGGING

  STORAGE(INITIAL NEXT MINEXTENTS MAXEXTENTS

  PCTINCREASE FREELISTS FREELIST GROUPS BUFFER_POOL DEFAULT FLASH_CACHE DE

  FAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE SYSTEM

  LOB (SQLBIND) STORE AS BASICFILE (

  TABLESPACE SYSTEM ENABLE STORAGE IN ROW CHUNK RETENTION

  NOCACHE LOGGING

  STORAGE(INITIAL NEXT MINEXTENTS MAXEXTENTS

  PCTINCREASE FREELISTS FREELIST GROUPS BUFFER_POOL DEFAULT FLASH_CACHE DE

  FAULT CELL_FLASH_CACHE DEFAULT))

  LOB (SQLTEXT) STORE AS BASICFILE (

  TABLESPACE SYSTEM ENABLE STORAGE IN ROW CHUNK RETENTION

  NOCACHE LOGGING

  STORAGE(INITIAL NEXT MINEXTENTS MAXEXTENTS

  PCTINCREASE FREELISTS FREELIST GROUPS BUFFER_POOL DEFAULT FLASH_CACHE DE

  FAULT CELL_FLASH_CACHE DEFAULT))

  SQL>

  SQL> CREATE TABLE sysAUD$_ARCH

   ( SESSIONID NUMBER NOT NULL ENABLE

   ENTRYID NUMBER NOT NULL ENABLE

   STATEMENT NUMBER NOT NULL ENABLE

   TIMESTAMP# DATE

   USERID VARCHAR()

   USERHOST VARCHAR()

   TERMINAL VARCHAR()

   ACTION# NUMBER NOT NULL ENABLE

   RETURNCODE NUMBER NOT NULL ENABLE

   OBJ$CREATOR VARCHAR()

   OBJ$NAME VARCHAR()

   AUTH$PRIVILEGES VARCHAR()

   AUTH$GRANTEE VARCHAR()

   NEW$OWNER VARCHAR()

   NEW$NAME VARCHAR()

  SES$ACTIONS VARCHAR()

   SES$TID NUMBER

   LOGOFF$LREAD NUMBER

   LOGOFF$PREAD NUMBER

   LOGOFF$LWRITE NUMBER

   LOGOFF$DEAD NUMBER

   LOGOFF$TIME DATE

   COMMENT$TEXT VARCHAR()

   CLIENTID VARCHAR()

   SPARE VARCHAR()

   SPARE NUMBER

   OBJ$LABEL RAW()

   SES$LABEL RAW()

   PRIV$USED NUMBER

   SESSIONCPU NUMBER

   NTIMESTAMP# TIMESTAMP ()

   PROXY$SID NUMBER

   USER$GUID VARCHAR()

   INSTANCE# NUMBER

   PROCESS# VARCHAR()

   XID RAW()

   AUDITID VARCHAR()

   SCN NUMBER

   DBID NUMBER

   SQLBIND CLOB

   SQLTEXT CLOB

   OBJ$EDITION VARCHAR()

   )

   tablespace arch_tbs

   nologging;

  Table created


From:http://tw.wingwit.com/Article/program/Oracle/201311/18560.html
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.