重庆思庄Oracle、Redhat认证学习论坛

标题: Dbms_Audit_Mgmt.Set_Audit_Trail_Location Does Not Move Lob And Index Partitions [打印本页]

作者: 郑全 时间: 2021-5-30 19:29
标题: Dbms_Audit_Mgmt.Set_Audit_Trail_Location Does Not Move Lob And Index Partitions
Dbms_Audit_Mgmt.Set_Audit_Trail_Location Does Not Move Lob And Index Partitions (Doc ID 2428624.1)

In this Document
Symptoms
Changes
Cause
Solution
References

APPLIES TO:
Oracle Database - Enterprise Edition - Version 12.2.0.1 to 18.3.0.0.0 [Release 12.2 to 18]
Information in this document applies to any platform.
SYMPTOMS
NOTE: In the images and/or the document content below, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.

When DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION is used to change the default tablespace used for storing audit data, only the new table partitions will be created in the new tablespace. LOB and INDEX partitions will still be stored in SYSAUX.

This behaviour can be easily reproduced with the following steps:

SQL> select segment_name,ds.tablespace_name from dba_segments ds where owner = 'AUDSYS';

SEGMENT_NAME                         TABLESPACE_NAME

-------------------          ----------------------------

AUD$UNIFIED                               SYSAUX

SYS_IL0000017945C00097$$                SYSAUX

SYS_IL0000017945C00031$$                SYSAUX

SYS_IL0000017945C00030$$                SYSAUX

SYS_LOB0000017945C00030$$                SYSAUX

SYS_LOB0000017945C00031$$                SYSAUX

SYS_LOB0000017945C00097$$                SYSAUX

SQL> select table_owner,table_name,tablespace_name,partition_name,partition_position,segment_created,read_only from dba_tab_partitions where table_name like 'AUD%';

TABLE_OWNER          TABLE_NAME          TABLESPACE_NAME    PARTITION_NAME       PARTITION_POSITION       SEGM READ

---------------- ------------------------- ---------------------- --------------------- ------------------------- ------- ----

   AUDSYS       AUD$UNIFIED                SYSAUX             SYS_P8561             1                      YES NO

SQL> create tablespace AUDITTS datafile '<PATH>/auditts.dbf' size 100M autoextend on;

Tablespace created.

SQL> exec

dbms_audit_mgmt.set_audit_trail_location(audit_trail_type=>DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,audit_trail_location_value=>'AUDITTS');

PL/SQL procedure successfully completed.

SQL> begin

2 dbms_audit_mgmt.alter_partition_interval(

3 interval_number=>1,

4 interval_frequency=>'DAY');

5 end;

6 /

PL/SQL procedure successfully completed.

SQL> select owner,segment_name,tablespace_name,partition_name,segment_type,retention from dba_segments where owner='AUDSYS';

OWNER             SEGMENT_NAME                      TABLESPACE_NAME       PARTITION_NAME             SEGMENT_TYPE    RETENTION

---------- ------------------------------------ ------------------------- -------------------- ------------------------ ------------------

AUDSYS                AUD$UNIFIED                   SYSAUX                   SYS_P8561          TABLE PARTITION

AUDSYS    SYS_IL0000017945C00097$$                   SYSAUX                SYS_IL_P8567          INDEX PARTITION

AUDSYS    SYS_IL0000017945C00031$$                   SYSAUX                SYS_IL_P8565          INDEX PARTITION

AUDSYS    SYS_IL0000017945C00030$$                   SYSAUX                SYS_IL_P8563          INDEX PARTITION

AUDSYS    SYS_LOB0000017945C00030$$                   SYSAUX                SYS_LOB_P8562             LOB PARTITION       DEFAULT

AUDSYS    SYS_LOB0000017945C00031$$                   SYSAUX                SYS_LOB_P8564             LOB PARTITION       DEFAULT

AUDSYS    SYS_LOB0000017945C00097$$                   SYSAUX                SYS_LOB_P8566             LOB PARTITION       DEFAULT

SQL> select table_owner,table_name,tablespace_name,partition_name,partition_position,segment_created from dba_tab_partitions where table_name like 'AUD%';

TABLE_OWNER          TABLE_NAME          TABLESPACE_NAME          PARTITION_NAME    PARTITION_POSITION          SEGM

----------------- ------------------------- --------------------- --------------------- ---------------------------------- ------

AUDSYS                AUD$UNIFIED          SYSAUX             SYS_P8561                            1                YES

SQL> select owner,table_name,interval,partitioning_type,partition_count,def_tablespace_name from dba_part_Tables where owner='AUDSYS';

OWNER       TABLE_NAME                INTERVAL                            PARTITION          PARTITION_COUNT             DEF_TABLESPACE_NAME

------------- ------------------------- ----------------------------- ---------------------  -------------------------------- ---------------------------

AUDSYS          AUD$UNIFIED          NUMTODSINTERVAL(1, 'DAY')             RANGE                      1048575                      AUDITTS

AUDSYS          CLI_SWP$a423794$1$1                                           RANGE                         1                         AUDITTS

SQL> select table_owner,table_name,tablespace_name,lob_partition_name,lob_name,lob_indpart _name,partition_position,segment_created,in_row,lob_indpart_name from dba_lob_partitions where table_owner='AUDSYS';

TABLE_OWNER             TABLE_NAME             TABLESPACE_NAME          LOB_PARTITION_NAME             LOB_NAME                         LOB_INDPART_NAME          PARTITION_POSITION       SEG    IN_ROW    LOB_INDPART_NAME

----------------- ------------------------- ---------------------- --------------------------- -------------------------------------    -------------------- ----------------------------  --------  ---------- -------------------------

  AUDSYS          AUD$UNIFIED                SYSAUX                SYS_LOB_P8562          SYS_LOB0000017945C00030$$                   SYS_IL_P8563                   1                YES    YES       SYS_IL_P8563

  AUDSYS          AUD$UNIFIED                SYSAUX                SYS_LOB_P8564          SYS_LOB0000017945C00031$$                   SYS_IL_P8565                   1                YES    YES          SYS_IL_P8565

  AUDSYS          AUD$UNIFIED                SYSAUX                SYS_LOB_P8566          SYS_LOB0000017945C00097$$                   SYS_IL_P8567                   1                YES    YES          SYS_IL_P8567

  AUDSYS          CLI_SWP$a423794$1$1       AUDITTS                SYS_LOB_P8601          SYS_LOB0000115677C00014$$                   SYS_IL_P8602                   1                NO    YES          SYS_IL_P8602

SQL> select systimestamp from dual;

SYSTIMESTAMP

---------------------------------------------------------------------------

12-FEB-18 06.37.30.595666 PM +02:00

****change system date to March 2nd, generate audit records

SQL> select owner,segment_name,tablespace_name,partition_name,segment_type,retention from dba_segments where owner='AUDSYS';

OWNER          SEGMENT_NAME                   TABLESPACE_NAME       PARTITION_NAME          SEGMENT_TYPE    RETENTION

---------- ----- ----------------------------- ---------------------- ------------------------  --------------------  ----------

AUDSYS                AUD$UNIFIED                SYSAUX             SYS_P8561             TABLE PARTITION

AUDSYS    SYS_IL0000017945C00097$$                SYSAUX          SYS_IL_P8567             INDEX PARTITION

AUDSYS    SYS_IL0000017945C00031$$                SYSAUX          SYS_IL_P8565             INDEX PARTITION

AUDSYS    SYS_IL0000017945C00030$$                SYSAUX          SYS_IL_P8563             INDEX PARTITION

AUDSYS    SYS_IL0000017945C00097$$                SYSAUX          SYS_IL_P8746             INDEX PARTITION

AUDSYS    SYS_IL0000017945C00031$$             SYSAUX          SYS_IL_P8744             INDEX PARTITION

AUDSYS    SYS_IL0000017945C00030$$             SYSAUX             YS_IL_P8742             INDEX PARTITION

AUDSYS    SYS_IL0000017945C00097$$             SYSAUX             SYS_IL_P8753          INDEX PARTITION

AUDSYS    SYS_IL0000017945C00031$$             SYSAUX             SYS_IL_P8751          INDEX PARTITION

AUDSYS    SYS_IL0000017945C00030$$             SYSAUX             SYS_IL_P8749          INDEX PARTITION

AUDSYS    SYS_LOB0000017945C00030$$             SYSAUX          SYS_LOB_P8562             LOB PARTITION       DEFAULT

OWNER             SEGMENT_NAME                   TABLESPACE_NAME       PARTITION_NAME          SEGMENT_TYPE    RETENTION

---------- ------------------------------------- ----------------------- ----------------------- --------------------- ---------------

AUDSYS             SYS_LOB0000017945C00031$$    SYSAUX             SYS_LOB_P8564                LOB PARTITION    DEFAULT

AUDSYS             SYS_LOB0000017945C00097$$    SYSAUX             SYS_LOB_P8566                LOB PARTITION    DEFAULT

AUDSYS             SYS_LOB0000017945C00030$$    SYSAUX             SYS_LOB_P8741                LOB PARTITION    DEFAULT

AUDSYS             SYS_LOB0000017945C00031$$    SYSAUX             SYS_LOB_P8743                LOB PARTITION    DEFAULT

AUDSYS             SYS_LOB0000017945C00097$$    SYSAUX             SYS_LOB_P8745                LOB PARTITION    DEFAULT

AUDSYS             SYS_LOB0000017945C00030$$    SYSAUX             SYS_LOB_P8748                LOB PARTITION    DEFAULT

AUDSYS             SYS_LOB0000017945C00031$$    SYSAUX             SYS_LOB_P8750                LOB PARTITION    DEFAULT

AUDSYS             SYS_LOB0000017945C00097$$    SYSAUX             SYS_LOB_ P8752             LOB PARTITION    DEFAULT

AUDSYS                         AUD$UNIFIED    AUDITTS                   SYS_P8721             TABLE PARTITION

AUDSYS                         AUD$UNIFIED    AUDITTS                   SYS_P8747             TABLE PARTITION

21 rows selected.

CHANGES

CAUSE
Due to Bug 27576342 when DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION is executed to change  the tablespace of unified audit trail, only the new table partitions of AUDSYS.AUD$UNIFIED table will be created in the specified new tablespace while the lob and index partitions are still getting created in the OLD tablespace (SYSAUX)

SOLUTION
With the fix of  Bug 27576342  all new table partitions, lob partitions and index partitions of AUDSYS.AUD$UNIFIED table will be created in the specified new tablespace.

The existing partitions will remain in the old tablespace (SYSAUX).

Apply Patch 27576342

What is the PARTITION_INTERVAL set to ?

Default is 1 month.  you will need to wait until the end of the month for a new partition to be populated.
Until that time, the audit data would be populated to the same partition.

To change this interval, exec DBMS_AUDIT_MGMT.ALTER_PARTITION_INTERVAL

But please note that the change would take effect only after the current partition time completes/expires.

REFERENCES
BUG:27576342 - DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION DOES NOT MOVE LOB AND INDEX PARTITION

欢迎光临重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)