[20230306]學習UNIFIED audit--dg相關問題.txt

[20230306]學習UNIFIED audit--dg相關問題.txt

--//如果存在dg的情況，因為dg下的DML操作無法寫入audsys.AUD$UNIFIED表中，只能將審計資訊寫入目錄：
--//$ORACLE_BASE/audit/$ORACLE_SID.
--//測試參考連結:https://blog.dbi-services.com/handling-unified-auditing-spillover-files-on-the-standby-site/

1.環境：
SYS@192.168.100.237:1521/orcldg> @ pr
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 19.0.0.0.0
BANNER                        : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
BANNER_LEGACY                 : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.
--//注意檢視的資料庫是備庫.

2.探察：
$ cd $ORACLE_BASE/audit/orcldg

$ du -sm /u01/app/oracle/audit/orcldg
322     /u01/app/oracle/audit/orcldg

$ ll  |wc
   3079   24626  283697
--//虛擬機器效能有點慢!!執行如上第一次如同當機一般,也就是已經寫入許多審計檔案。
--//隨便檢視一個:
$ strings ora_audit_045.bin
ANG Spillover Audit File
ORAAUDNG
oracle
LISDG
pts/0
(TYPE=(OS));(CLIENT ADDRESS=((PROTOCOL=beq)(HOST=192.168.122.1)));
sqlplus@LISDG (TNS V1-V3)
218392
...._UNLIMIT
ALTER profile ZG_PASSWD_UNLIMIT limit PASSWORD_LIFE_TIME UNLIMITED
SYSDBA
ORA_SECURECONFIG
--//不知道為什麼有一個使用sqlplus登入備庫的命令總是要執行ALTER profile語句.

$ mv $ORACLE_BASE/audit/orcldg $ORACLE_BASE/audit/orcldg_new
'/u01/app/oracle/audit/orcldg' -> '/u01/app/oracle/audit/orcldg_new'

SYS@192.168.100.237:1521/orcldg> select count(*) from unified_audit_trail;
  COUNT(*)
----------
    393819

SYS@192.168.100.237:1521/orcldg> select count(*) from v$unified_audit_trail;
  COUNT(*)
----------
         0

SYS@192.168.100.237:1521/orcldg> select count(*) from audsys.aud$unified;
  COUNT(*)
----------
    393819

--//可以看出mv後,檢視v$unified_audit_trail檢視結果為0.實際上unified_audit_trail是v$unified_audit_trail 和
--//audsys.aud$unified的union all.

$ mv $ORACLE_BASE/audit/orcldg_new $ORACLE_BASE/audit/orcldg
'/u01/app/oracle/audit/orcldg_new' -> '/u01/app/oracle/audit/orcldg/orcldg_new'

select count(*) from unified_audit_trail;
select count(*) from v$unified_audit_trail;

SYS@192.168.100.237:1521/orcldg> select count(*) from unified_audit_trail;
select count(*) from unified_audit_trail
                     *
ERROR at line 1:
ORA-01013: user requested cancel of current operation
--//按ctrl+c中斷，審計檔案實在太多了,執行太慢!!

SYS@192.168.100.237:1521/orcldg> select count(*) from audsys.aud$unified;
  COUNT(*)
----------
    393822

--//檢視unified_audit_trail的定義實際上包含v$unified_audit_trail和audsys.aud$unified。
--//很明顯對於spillover-files的資料資訊來源於v$unified_audit_trail。

--//https://blog.dbi-services.com/handling-unified-auditing-spillover-files-on-the-standby-site/
I.e. moving the spillover directory to a new name results in showing less data in UNIFIED_AUDIT_TRAIL. The view
UNIFIED_AUDIT_TRAIL is a UNION ALL of the view v$unified_audit_trail and the table audsys.aud$unified (you may check
$ORACLE_HOME/rdbms/admin/catuat.sql on what the metadata of UNIFIED_AUDIT_TRAIL is). The data of the spillover-files
comes from the view v$unified_audit_trail:

--//oracle提供DBMS_AUDIT_MGMT.LOAD_UNIFIED_AUDIT_FILES載入這些資料檔案進入資料庫表中。
--//很明顯dg無法進行這樣的操作，這樣會出現如下情況：

That causes 3 issues:

1.) If you move your audit data on your primary database to history tables then those history-tables may not contain the
    full truth, because audit-records of spillover-files on the standby-DBs are not visible in the history tables.
2.) After a switchover a query on unified_audit_trail may be very slow, because reading spillover-files is slower than
    reading from the database.
3.) Loading the spillover files after a switchover to the new primary database may take a long time and causes the
    SYSAUX-tablespace to grow significantly.

--//連結提供一些解決方法，比如移動到主庫然後匯入。
--//實際上如果這些查詢不重要，可以臨時給目錄改名。這樣查詢unified_audit_trail塊一些。
--//或者乾脆使用find刪除一些歷史*.bin檔案。

$ find . -name "*.bin" -mtime +60 -exec ls -ltr --time-style=+"%Y-%m-%d %H:%M:%S" {} \+ |wc
   2453   19624  231831
$ find . -name "*.bin" -mtime -60 -exec ls -ltr --time-style=+"%Y-%m-%d %H:%M:%S" {} \+ |wc
    618    4944   56736