Oracle mechanics

21.09.2013

Перемещение AUD$

Filed under: bugs,Oracle — Игорь Усольцев @ 00:56
Tags: , ,

На версии 11.2.0.3 попытка сократить неоправданно «распухший» сегмент AUD$:

SQL> select tablespace_name,
  2         segment_space_management,
  3         extent_management,
  4         round(bytes / 1024 / 1024 / 1024) as GB
  5    from dba_tablespaces ts
  6    join dba_segments
  7   using (tablespace_name)
  8   where segment_name = 'AUD$'
  9  /

TABLESPACE_NAME  SEGMENT_SPACE_MANAGEMENT EXTENT_MANAGEMENT   GB
---------------- ------------------------ ----------------- ----
SYSAUX           AUTO                     LOCAL               20

, уже находящийся в ASSM табличном пространстве, согласно рекомендациям How To Shrink Table AUD$ (Doc ID 1080112.1) для версий 11.2.0.2 — 11.2.0.4 командами:

alter table SYS.AUD$ enable row movement;
ALTER table SYS.AUD$ SHRINK SPACE CASCADE;
alter table SYS.AUD$ disable row movement;

результата не принесла — размер сегмента не изменился, но в логе появились ошибки типа:

ORA-00600: internal error code, arguments: [ktspgsb-inc], [2], [1], [], [], [], [], [], [], [], [], []
ORA-02002: error while writing to audit trail

----- Current SQL Statement for this session (sql_id=4vs91dcv7u1p6) -----
insert into sys.aud$(...

— по поводу чего техподдержка (в лице отличного специалиста Frank Bleger) завела отдельный Bug 17084570 : ORA-600 [KTSPGSB-INC], [2], [1], [] INSERT INTO SYS.AUD$ (base Bug 10371288) и настоятельно рекомендовала произвести перемещение AUD$ только с использованием пакета DBMS_AUDIT_MGMT

Тесты для 20 GB сегмента показали след.результат:

SQL> select round(bytes/1024/1024/1024) as GB from dba_segments where segment_name = 'AUD$';

GB
--
20

SQL> exec DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION (DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD, 'OTHER_ASSM_TBS');

PL/SQL procedure successfully completed.
Elapsed: 02:33:34.20                     -- не слишком быстро

SQL> select bytes from dba_segments where segment_name = 'AUD$'
  2  /

     BYTES
----------
     65536

SQL> exec DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION (DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD, 'SYSAUX');

PL/SQL procedure successfully completed.
Elapsed: 00:00:13.96                     -- сокращённый сегмент - быстрее

— в отличие от предыдущих версий (10.2) размер сегмента AUD$ успешно сократился

На бд с реальной нагрузкой проблем избежать не удалось:

21:24:43 SQL> select bytes/1024/1024/1024 from dba_segments where segment_name = 'AUD$';

BYTES/1024/1024/1024
--------------------
          20.1473999

21:24:43 SQL> exec DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION (DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD, 'OTHER_ASSM_TBS');
BEGIN DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION (DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD, 'OTHER_ASSM_TBS'); END;

*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 1659
ORA-06512: at line 1

21:39:21 SQL>

— операция была прервана ^C по причине массовых блокировок:

Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                                           Avg
                                                          wait   % DB
Event                                 Waits     Time(s)   (ms)   time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
enq: ZA - add std audit table           768     310,299 4.E+05   88.3 Other
DB CPU                                           14,706           4.2
...

SQL> select name, description from v$lock_type where type = 'ZA';

NAME             DESCRIPTION
---------------- -------------------------------------------
Audit Partition  Lock held for adding partition to Aud table

, не попавших в AWR /dba_hist_active_session_history, но ожидаемых и описанных в Bug 17041211 : DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION EXECUTION IS LOCKING UP THE SYSTEM WITH:

«The ZA enqueue is used only by dbms_audit_mgmt.
When the ZA enqueue is taken out in EXCLUSIVE mode, the objective is to block inserts into aud$ until the move is complete.
During that time, individual sesssions will block, not hang, on the ZA enqueue in request mode 4.
After the move completes, the ZA-6 is removed and those blocked sessions are released to complete whatever audited thing they are doing.»

После прерывания выполнения DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION блокировки были сняты, но при этом и операция была успешно завершена!

SQL> select segment_name, tablespace_name from dba_segments where segment_name like 'AUD$%'
  2  union all
  3  select segment_name, tablespace_name from dba_lobs where table_name = 'AUD$'
  4  /

SEGMENT_NAME                TABLESPACE_NAME
--------------------------- ---------------
AUD$                        OTHER_ASSM_TBS  -- успешно перемещён и шринкнут
AUD$_IDX2                   AUDIT_DATA      -- пользовательский индекс, перемещён НЕ БЫЛ
SYS_LOB0000000090C00040$$   OTHER_ASSM_TBS  -- успешно перемещён
SYS_LOB0000000090C00041$$   OTHER_ASSM_TBS  -- --//--

Несмотря на то, что после перемещения таблицы AUD$ пользовательский индекс не был перестроен, он остался в статусе VALID:

SQL> select * from dba_segments where segment_name like 'AUD$%';

SEGMENT_NAME  SEGMENT_TYPE  SEGMENT_SUBTYPE TABLESPACE_NAME       BYTES     BLOCKS
------------- ------------- --------------- ---------------- ---------- ----------
AUD$          TABLE         ASSM            OTHER_ASSM_TBS    160432128      39168
AUD$_IDX2     INDEX         ASSM            AUDIT_DATA       1485307904     362624 -- размер индекса не изменился

SQL> select * from dba_indexes where index_name = 'AUD$_IDX2';

INDEX_NAME  INDEX_TYPE  TABLE_NAME  TABLE_TYPE  UNIQUENESS TABLESPACE_NAME  LOGGING STATUS VISIBILITY SEGMENT_CREATED
----------- ----------- ----------- ----------- ---------- ---------------- ------- ------ ---------- ---------------
AUD$_IDX2   NORMAL      AUD$        TABLE       NONUNIQUE  AUDIT_DATA       YES     VALID  VISIBLE    YES

, и продолжает успешно использоваться:

SQL> select count(*) from v$sql_plan where operation = 'INDEX' and object_name = 'AUD$_IDX2';

  COUNT(*)
----------
         1

благодаря периодическому архивированию с удалением всех записей из AUD$ заданием DBMS_SCHEDULER

Важно перед перемещением проверить наличие свободного места без учёта AUTOEXTEND — SCRIPT: Basic example to manage AUD$ table in 11.2 with dbms_audit_mgmt (Doc ID 1362997.1):

Please create a tablespace of sufficient size to hold the AUD$ table immediately, dbms_audit_mgmt will check the current space and not take autoextend into account

— в противном случае можно получить:

SQL> begin
  2  DBMS_AUDIT_MGMT.SET_DEBUG_LEVEL(DBMS_AUDIT_MGMT.TRACE_LEVEL_DEBUG);
  3  DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION
  4  (audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,
  5         audit_trail_location_value =>  'OTHER_ASSM_TBS');
  6  end;
  7  /

ORA-46267: Insufficient space in 'OTHER_ASSM_TBS' tablespace, cannot complete operation
ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 1585
ORA-06512: at line 4

С описанием подробностей в трейсе:

KZAM_TRC:AUD$: Space occupied = 28498108416 per row, space required = 29923016704
KZAM_TRC:Space available = 13162774528
KZAM_TRC:tbs_space_check: return FALSE
KZAM_TRC:ORA-46267...1

2 комментария »

  1. Я вот не понял, как так, таблица перенеслась в другое ТБС, а индекс остался Valid. Такое возможно если таблица была пуста…

    комментарий от Sergey Golikov — 23.09.2013 @ 13:26 | Ответить

    • Если переносить обычную пользовательскую таблицу командой ALTER TABLE MOVE — согласен.

      Но во-первых, AUD$ — не совсем простая таблица, а во-вторых, процедура DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION — не совсем аналогична MOVE

      Т.е., есть предположение, что системный пакет от Oracle волен вносить изменения в свой словарь практически «как хочет»

      Например, после пользовательского перемещения таблицы:

      SQL> create index EMP_IDX_DEPT on EMP (DEPTNO) tablespace USERS;
       
      Index created
       
      SQL> select object_type,
        2         object_name,
        3         object_id,
        4         data_object_id
        5    from user_objects
        6   where object_name = 'EMP_IDX_DEPT'
        7  /
       
      OBJECT_TYPE         OBJECT_NAME    OBJECT_ID DATA_OBJECT_ID
      ------------------- ------------- ---------- --------------
      INDEX               EMP_IDX_DEPT       92173          92173
       
      SQL> alter table EMP move tablespace EXAMPLE;
       
      Table altered
       
      SQL> select object_type,
        2         object_name,
        3         object_id,
        4         data_object_id
        5    from user_objects
        6   where object_name = 'EMP_IDX_DEPT'
        7  /
       
      OBJECT_TYPE         OBJECT_NAME    OBJECT_ID DATA_OBJECT_ID
      ------------------- ------------- ---------- --------------
      INDEX               EMP_IDX_DEPT       92173                -- логично обнулился после ALTER TABLE MOVE
      
      SQL> select * from user_segments where segment_name = 'EMP_IDX_DEPT';
      
      no rows selected
      
      SQL> select status from user_indexes where index_name = 'EMP_IDX_DEPT';
       
      STATUS
      --------
      UNUSABLE
      

      — поле USER_OBJECTS.DATA_OBJECT_ID — обнуляется, сегмент индекса удаляется и статус у индекса становится негодным

      После перемещения AUD$ с использованием пакета DBMS_AUDIT_MGMT заметно, что словарь данных модифицировался по другим правилам:

      SQL> select o.object_type,
        2         o.object_name,
        3         o.object_id,
        4         o.data_object_id
        5    from dba_objects o
        6   where object_name like 'AUD$%'
        7  /
       
      OBJECT_TYPE         OBJECT_NAME    OBJECT_ID DATA_OBJECT_ID
      ------------------- ------------- ---------- --------------
      INDEX               AUD$_IDX2         661884         661884 -- OBJECT_ID = DATA_OBJECT_ID, т.е. не менялся с момента создания !
      TABLE               AUD$                  90        1775123 -- ожидаемо изменился

      + одна из причин состоит в том, что индекс AUD$_IDX2 является допустимым, но кастомизированным решением, т.е. не учтён в DBMS_AUDIT_MGMT

      ++ после штатного (через 10 дней с момента перемещения AUD$) сбора статистики индекс AUD$_IDX2 перешёл-таки в ожидаемое состояние UNUSABLE

      комментарий от Igor Usoltsev — 23.09.2013 @ 17:47 | Ответить


RSS feed for comments on this post. TrackBack URI

Добавить комментарий

Заполните поля или щелкните по значку, чтобы оставить свой комментарий:

Логотип WordPress.com

Для комментария используется ваша учётная запись WordPress.com. Выход / Изменить )

Фотография Twitter

Для комментария используется ваша учётная запись Twitter. Выход / Изменить )

Фотография Facebook

Для комментария используется ваша учётная запись Facebook. Выход / Изменить )

Google+ photo

Для комментария используется ваша учётная запись Google+. Выход / Изменить )

Connecting to %s

Создайте бесплатный сайт или блог на WordPress.com.

%d такие блоггеры, как: