[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