Автор: Аруп Нанда, член-директор Oracle ACE Director ORACLE ACE
В этой статье рассказано, как идентифицировать и вернуться назад во времени, отменить определенные транзакции и их зависимости.
ИнтерфейсLogMinerвOracle Enterprise Manager
LogMiner - это ранее часто игнорировавшийся очень мощный инструмент в базе данных Oracle. Он предназначен для извлечения из журнальных (redo log) файлов DML-операторов, то есть оригинальных SQL-предложений, которые составляют транзакции, а так же SQL, которые откатывают транзакции. (Для введения в LogMiner и принципы его работы советую обратиться к моей статье в Oracle Magazine "Mining for Clues" http://www.oracle.com/technology/oramag/oracle/05-jul/o45dba.html). По всей вероятности до сих пор этот мощный инструмент был недооценен из-за отсутствия простого интерфейса. Однако обладающий в Oracle Database 11 g графическим интерфейсом OEM использует LogMinerдля извлечения транзакций из журнальных, что делает процесс анализа и отката транзакций удивительно простым. ( Замечание : как и в предыдущих версиях, для выполнения извлечения информации из журналов можно использовать в командной строке пакет DBMS_LOGMNR).
Давайте посмотрим, как это делается. Для задействования "раскопки данных" (log mining) необходимо включить дополнительное журналирование для базы данных или, как минимум, для таблицы. Для выполнения рекурсивного отката транзакций (Flashback Transaction) требуется журналирования первичных ключей. Для включения этого механизма для всей базы данных надо выполнить следующие команды:
SQL> alter database add supplemental log data;
Database altered.
SQL> alter database add supplemental log data (primary key) columns;
Database altered.
Теперь представим, что в базе данных приложением были выполнены следующие предложения:
SQL> insert into res values (100002,sysdate,12,1);
1 row created.
SQL> commit;
Commit complete.SQL> update res set hotel_id = 13 where res_id = 100002;
1 row updated.
SQL> commit;
Commit complete.SQL> delete res where res_id = 100002;
1 row deleted.
SQL> commit;
Commit complete.
Внимательно посмотрим на эти предложения: каждое заканчивается оператором фиксации (commit), что означает, каждое предложение является транзакцией. Теперь посмотрим, как можно проанализировать транзакции с помощью LogMiner в Oracle Database 11g Database Control.
На экране начальной (home) страницы OEM выберем закладку Availability.
Щелкнем по ссылке ViewandManageTransactions под ссылкой Manage. Тем самым открываем интерфейс LogMiner:
Для поиска транзакций можно ввести временной диапазон или диапазон SCN-ов. На экране выше для поиска я ввел диапазон времени в Query Time Range. В фильтре Query Filter я назвал только транзакции пользователя SCOTT, а поиск будет вестись по всем DML-операторам. В разделе Advanced Query можно ввести любые дополнительные фильтры. После того, как все поля заполнены, щелкаем по Continue.
Тем самым запускается процесс LogMiner, который ищет в во всех журналах (оперативных и заархивированных, если необходимо) и находит транзакции, выполненные пользователем SCOTT. После того, как процесс завершен, вы увидите страницу результатов.
Верхняя часть результирующей страницы выглядит примерно так:
Результат показывает, что при поиске нашлись две транзакции пользователя SCOTT, которые затронули две записи.
Нижняя часть страницы показывает детали этих транзакций. На следующем рисунке приведено частичное отображение экрана. Представлены транзакции, обозначенные как 1 ins (что означает "1 оператор INSERT"). Самая левая колонка отображает идентификатор транзакции (XID), число, однозначно идентифицирующее транзакцию.
Если щелкнуть по идентификатору транзакции, откроется детальное окно этой транзакции:
Как видно, можно использовать Database Control для поиска и идентификации транзакций. Щелкая по кнопкам предыдущая (Previous Transaction) и следующая (Next Transaction) транзакция, можно перемещаться по всем найденным транзакциям.
Возможности использования
В каких случаях следует пользоваться этими возможностями? Есть несколько ситуаций. Самое главное - найти "кто" сделал "это". Если не включен аудит, чтобы не терять производительность, или просто не сохранились записи аудита, то все, что можно сделать, найти при помощи LogMiner какие-либо следы путем извлечения из оперативных и/или заархивированных журналов. На странице поиска можно ввести дополнительные условия фильтрации в поле Advanced Query в Query Finder.
Представим, что надо найти транзакцию, которая вставила, изменила или удалила запись с RES_ID = 100002. Можно поискать конкретное значение в журнальном потоке, используя функцию column_present из пакета dbms_logmnr:
Эта функция разыщет все транзакции, которые содержали 100002 в колонке RES_ID таблицы RES в схеме SCOTT.
Эту возможность также можно использовать, чтобы найти DDL-команды, выполненные в базе данных. Чтобы сделать это, надо выбрать радио-кнопку View DDL only в разделе Query Filter.
Отмена определенных транзакций
Что бы хотелось сделать с транзакцией во время ее анализа? Одна из первопричин, почему анализируется транзакция, возможно, транзакция выполнена ошибочно, и ее надо отменить. Это очень просто: если транзакция - вставка записей, то просто нужно ее удалить; если обновление, то откат транзакции должен заменить строку старым значением.
Далее, внимательно посмотрим на транзакции, приведенные в примере. Первая транзакция вставляет строку. Вторая обновляет только что вставленную строку, а третья удаляет ее. Вы хотите отменить первую транзакцию (вставка). Но тут появляется проблема: строка и так удалена последующими транзакциями; какой будет транзакция для отката в этом случае?
Это тот случай, когда применяется новая функциональность Dependent Transaction (Зависимые транзакции) в Oracle Database 11g. Щелкнем на Flashback Transaction. После некоторых поисков появится похожая на эту страница:
Этот скриншот показывает зависимые транзакции вместе с обновлениями и удалениями. Поэтому, когда отменяется транзакцию, можно отменить и зависимые от нее транзакции. Чтобы сделать это, выберем радио-кнопку Cascade из списка, показанного ниже, и нажмем кнопку OK.
Будут показаны различные транзакции, которые предстоит отменить; щелкнем по соответствующим Transaction ID, чтобы посмотреть SQL-предложения, которые выдаст Oracle, чтобы откатить конкретные транзакции.
Например, чтобы отменить вставку, Oracle должен выдать удаление, как показано выше. Если щелкнуть по следующей транзакции (как показано ниже), видны подробности, что необходимо выполнить для ее отмены:
Это - общий прием. Щелкнем по Submit, и все эти транзакции будут откатаны одним махом.
Интерфейс командной строки
Что если нет доступа к Oracle Enterprise Manager или, может быть, хочется сделать это через скрипт? Пакет DBMS_FLASHBACK, который уже был доступен в Oracle Database 10g, содержит новую процедуру TRANSACTION_BACKOUT. Эта процедура перезагружена (Overloaded), поэтому нужно лишь передать значения именованным параметрам, как показано ниже:
declare
trans_arr xid_array;
begin
trans_arr := xid_array('030003000D040000','F30003000D04010');dbms_flashback.transaction_backout (
numtxns => 1,
xids => trans_arr,
options => dbms_flashback.cascade
);
end;
Тип xid_array так же является нововведением Oracle Database 11g. Он предназначен для передачи идентификаторов транзакций в процедуру.
Другие возможности LogMiner
Если вы использовали XMLType, как тип данных, и все еще есть множество причин для использования его в Oracle Database 11g, то вы будете счастливы увидеть, что XML-данные так же могут быть извлечены с помощью LogMiner. Он показывает их в обоих столбцах - SQL_REDO и SQL_UNDO.
Когда запускается LogMiner, можно установить опцию SKIP_CORRUPTION, которая пропустит испорченные блоки в файлах журнала (redo logs). То есть, можно спасти правильные данные из журналов, даже если они частично испорчены. Вот пример использования улучшенного синтаксиса:
begin
dbms_logmnr.start_logmnr(
options => dbms_logmnr.skip_corruption
) ;
end;
Архивретроспективныхданных (Flashback Data Archive)
В Oracle9i Database Release 2 была представлена "машина времени" в виде Flashback Query, которая позволяла получить версию данных до изменения. Например, если вы изменили и зафиксировали некое значение с 100 на 200, все равно можно выбрать это значение за две минуты до изменения, даже если оно было зафиксировано. Эта технология использует данные до их изменения из сегментов отката. В Oracle Database 10g эта возможность была расширена с введением опции Flashback Versions Query, с помощью которой можно отслеживать изменения, примененные к строке, вместе с изменениями, до сих пор присутствующими в сегментах отката.
Тем не менее, есть небольшая проблема: когда база данных очищается (recycled), данные отката сбрасываются и удаляются значения до изменений. Даже если база данных не была очищена (not recycled), данные могут устареть и быть удалены из сегментов отката для освобождения места под новые изменения.
В связи с тем, что до Oracle Database 11g ретроспективные (flashback) операции основывались на данных отката, которые доступны в течение короткого времени, реально нельзя было их использовать в течение большего периода времени или для записей постоянного хранения, например, для аудита. В этом случае мы используем триггеры, чтобы на длительное время сохранить сведения об изменениях.
Не унывайте. В Oracle Database 11g функциональность Flashback Data Archive (архивирование ретроспективных данных) совмещает лучшее из двух миров: простоту и мощь ретроспективных запросов, которые не зависят от скоротечной памяти типа undo (отката). Это потому, что Flashback Data Archive записывает изменения в место более длительного хранения, в область Flashback Recovery Area.
First, you create a Flashback Data Archive, as shown below:
Рассмотрим пример. ( Замечание : для работы Flashback Data Archive необходимо активировать механизм Automatic Undo Management). Во-первых, создадим Flashback Data Archive, как показано ниже:
SQL> create flashback archive near_term
2 tablespace far_near_term
3 retention 1 month
4 /
Flashback archive created.
На время проигнорируем значение термина "retention", вернемся к этому позже. Архив создается в табличном пространстве far_near_term. (Это место, где хранятся изменения).
Представим, что необходимо записывать изменения в таблице TRANS. Все, что нужно сделать, чтобы началась запись изменений в этот архив, - включить для таблицы статус Flashback Data Archive.
SQL> alter table trans flashback archive near_term;
Table altered.
По этой команде таблица переводится в режим Flashback Data Archive. Все изменения строк теперь записываются для постоянного хранения. Продемонстрируем это.
Для начала выберем конкретную строку из таблицы:
SQL> select txn_amt from trans where trans_id = 2;
TXN_AMT
----------
19325.67
SQL> update trans set txn_amt = 2000 where trans_id = 2;
1 row updated.
SQL> commit;
Commit complete.
Теперь, если выбрать эту строку, она всегда покажет 2000 в столбце. Для того, чтобы найти старое значение на определенный момент времени, следует использовать ретроспективный запрос:
select txn_amt
from trans
as of timestamp to_timestamp ('07/18/2007 12:39:00','mm/dd/yyyy hh24:mi:ss')where trans_id = 2;
TXN_AMT
----------
19325.67
Далее по прошествии какого-то времени, когда данные отката будут удалены из undo- сегментов, еще раз выберем данные ретроспективным запросом:
select txn_amt
from trans
as of timestamp to_timestamp ('07/18/2007 12:39:00','mm/dd/yyyy hh24:mi:ss')where trans_id = 2;
Результат вернет 19325.65. Данных из undo-сегментов ушли, так откуда же результат?
Давайте спросим у Oracle. Для этого воспользуемся авто-трассировкой (autotrace) и посмотрим на план выполнения:
SQL> set autotrace traceonly explain
SQL> select txn_amt
2 from trans
3 as of timestamp to_timestamp ('07/18/2007 12:39:00','mm/dd/yyyy hh24:mi:ss')4 where trans_id = 2;
Execution Plan
----------------------------------------------------------
Plan hash value: 535458644
----------------------------------------------------------
/ Id / Operation / Name / Rows / Bytes / Cost (%CPU)/ Time / Pstart/ Pstop
-------------------------------------------------------------------------------------------------
/ 0 / SELECT STATEMENT / / 2 / 52 / 10 (10)/ 00:00:01 / /
/ 1 / VIEW / / 2 / 52 / 10 (10)/ 00:00:01 / /
/ 2 / UNION-ALL / / / / / / /
/* 3 / FILTER / / / / / / /
/ 4 / PARTITION RANGE SINGLE/ / 1 / 52 / 3 (0)/ 00:00:01 / 1 / 1
/* 5 / TABLE ACCESS FULL / SYS_FBA_HIST_68909 / 1 / 52 / 3 (0)/ 00:00:01 / 1 / 1
/* 6 / FILTER / / / / / / /
/* 7 / HASH JOIN OUTER / / 1 / 4053 / 10 (10)/ 00:00:01 / /
/* 8 / TABLE ACCESS FULL / TRANS / 1 / 38 / 6 (0)/ 00:00:01 / /
/ 9 / VIEW / / 2 / 8030 / 3 (0)/ 00:00:01 / /
/* 10 / TABLE ACCESS FULL / SYS_FBA_TCRV_68909 / 2 / 8056 / 3 (0)/ 00:00:01 / /
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(NULL IS NOT NULL)
5 - filter("TRANS_ID"=2 AND "ENDSCN">161508784336056 AND "ENDSCN"<=1073451 AND ("STARTSCN" IS NULL OR "STARTSCN"<=161508784336056))
6 - filter("F"."STARTSCN"<=161508784336056 OR "F"."STARTSCN" IS NULL) 7 - access("T".ROWID=("F"."RID"(+))) 8 - filter("T"."VERSIONS_STARTSCN" IS NULL AND "T"."TRANS_ID"=2) 10 - filter(("ENDSCN" IS NULL OR "ENDSCN">1073451) AND ("STARTSCN" IS NULL OR "STARTSCN"<1073451))
Note
-----
- dynamic sampling used for this statement
Это отвечает на вопрос "Откуда берутся данные?": они идут из таблицы SYS_FBA_HIST_68909, которая находится во Flashback Archive, который ранее был определен для этой таблицы. Можно проверить эту таблицу, однако Oracle не поддерживает чтение таких данных напрямую. В любом случае я не вижу причин, почему вы не можете сделать этого.
Данные в архиве сохраняются, но как долго? Здесь самое место, чтобы вступил в игру срок хранения. Данные хранятся вплоть до окончания этого периода. После этого по мере поступления новых данных старые стираются. Вы можете и сами стереть их, например:
alter flashback archive near_term purge before scn 1234567;
Управление ретроспективными архивами
В архив можно добавить более одного табличного пространства. И наоборот, их можно удалить оттуда. Если планируется использование табличного пространства, содержащего другие пользовательские данные, существует риск его переполнения данными Flashback Data Archive и недостатка места под данные пользователей. Чтобы снизить этот риск, следует установить квоту, сколько места архив может занять в табличном пространстве. Квоту можно установить так:
alter flashback archive near_term modify tablespace far_near_term quota 10M;
Запрашивая представление словаря, можно проверить, для каких таблиц включен Flashback Data Archive:
SQL> select * from user_flashback_archived_tables;TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NAME
---------- ---------- ------------------------
TRANS ARUP NEAR_TERM
Получить информацию об архивах можно путем запроса к представлению словаря:
sql> select * from flashback_archives;FLASHBACK_ARCHI FLASHBACK_ARCHIVE# RETENTION_IN_DAYS PURGE_SCN STATUS
--------------- ------------------ ----------------- ---------- -------
NEAR_TERM 1 30 1042653
MED_TERM 2 365 1042744
LONG_TERM 3 1825 1042838
Использование нескольких архивов дает возможность творчески подойти к разным ситуациям. Например, база данных отеля нуждается в данных о бронировании в течение года, а о платежах - в течение трех лет. Значит, можно определить несколько архивов с разным политиками хранения и назначить их таблицам. Или же, если применяется стандартная политика хранения, можно определить один архив и назначить его архивом по умолчанию.
alter flashback archive near_term set default;
Когда необходимость в архиве отпадет, его можно выключить командой:
alter table trans no flashback archive;
Как видите, только что вы получили мощную систему хранения изменений без написания даже одной строки кода.
Отличия от стандартного аудита
Чем отличается Flashback Data Archive от стандартного аудита? Во-первых, последний нуждается в установке параметра базы данных audit_trail в значение DB или DB_EXTENDED, а трасса (trails) записывается в таблицу AUD$, расположенную в табличном пространстве SYSTEM. Flashback Data Archives может быть определен в любом табличном пространстве (или больше чем в одном, равно как и в табличном пространстве, содержащем пользовательские данные) и поэтому может располагаться на более дешевом устройстве хранения.
Во-вторых, аудит основан на автономных транзакциях, которые имеют некоторые накладные расходы в плане производительности. Flashback Data Archives написан в виде отдельного фонового процесса FBDA, поэтому в этом случае влияние на производительность меньше.
В заключение, Flashback Data Archives может автоматически регулярно очищаться. Журналами же аудита необходимо управлять вручную.
Примеры использования
Flashback Data Archive удобен во многих случаях. Вот некоторые идеи:
- Для записей аудита как изменяются данные
- Для возможности откатывать изменения (исправление ошибок)
- Для изучения как менялись данные
- Для выполнения каких-либо предписаний, требующих, чтобы данные были неизменны в течение некоторого времени. Таблицы Flashback Data Archives не являются обычными таблицами, поэтому не могут быть изменены обычными пользователями.
- Запись журналов изменений на более дешевых устройствах хранения, что позволяет увеличить сроки хранения за меньшую стоимость.
Заключение