[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