Сегодня будет про организацию отслеживания изменений в нашей платформе.
Любая нормальная ERP система обязана иметь возможность проведения расследования о внесенных изменениях. Без такой возможности невозможно действительно передать на программу функцию управления ресурсами компании. Таким образом, система отслеживания изменений должна позволять отслеживать все изменения, требовать минимального расхода памяти (оперативной и дисковой), накладывать минимальный overhead при операциях. Система отслеживания изменений должна предоставлять возможность поиска и просмотра изменений с датой, и описанием сделанного изменения, e.g. новое значение, кто сделал, какого рода изменение. В реальных условиях надо учесть, что отслеживать требуется только реально сделанные (зафиксированные в СУБД) изменения.
Сразу оговорюсь, что мы пробовали несколько подходов, в том числе самый очевидный для Oracle — Flashback Archive. Почему он не подошел, расскажу в конце статьи.
В итоге, мы остановились на реализации логирования на триггерах. Для хранения всей истории достаточно 4-х таблиц и немного логики:
Таблицы LOG_TABLES и LOG_FIELDS содержат перечисления таблиц и отдельных полей, логируемых системой. Для управления этими таблицами в интерфейсе администратора есть специальные инструменты:
В этой форме администратор может включать логирование как для всей таблицы, так и отдельных полей. Для больших баз управление логированием требует особенной аккуратности, и часто этим занимается администратор СУБД, поэтому в режиме по умолчанию мы показываем именно таблицы и названия полей как они есть в базе данных. Для остальных можно переключиться на просмотр объектов системы.
Таблицы LOG_CHANGES и LOG_FIELDS_CHANGED содержат непосредственно информацию о изменениях. LOG_CHANGES — информацию, о изменении в таблице, пользователя и сессии из которой сделано изменение. В сессии, кстати, на всякий случай хранится информация о клиентской машине с которой произведен вход на сервер приложений, что, опять же, упрощает расследования. Еще небольшое отступление про пользователя и такую функцию в системе как маскарад. Маскарад — это возможность для пользователя (как правило администратора, разработчика, инженера техподдержки или отдела тестирования) войти в систему со всеми правами и настройками другого пользователя не вводя его (другого пользователя) пароль. Собственно пароли никому не известны и в базе их не видно. Так вот, при маскараде в истории изменений мы запоминаем реального пользователя. Таким образом, войти и проверить что-либо разработчик может, а вот все изменения будут видны как сделанные из-под его реального пользователя.
Вернемся к LOG_FIELDS_CHANGED. Как понятно из названия эта таблица хранит детальную информацию о измененных полях и их новых значениях. Поле Type содержит тип изменения — Insert, Delete, Update. Система сама генерирует триггеры для объектов метаданных. В триггере мы явно проверяем, что значение поля обновлено. Часто удобно написать запрос, который обновляет поле в его же значение. Такое изменение, а точнее, его отсутствие нас интересовать не должно.
Ради интереса можете проверить — триггер BEFORE INSERT OR UPDATE FOR EACH ROW будет вызван для каждой обновляемой строки. Проверка добавляет минимальный overhead и значительно снижает нагрузку на диск.
Такая система позволяет логировать произвольные таблицы (в том числе и не описанные как объекты метаданных — правда это экзотическая ситуация). Для реального использования кроме триггера на таблице необходима колонка, уникально идентифицирующая строку, в нашем варианте требуется сквозная нумерация для всех таблиц. Итого — один сиквенс, колонка и триггер для ее вычисления (последний оставлен для обратной совместимости с Oracle 11g, в 12с появились автоинкрементные поля, но мы ими не пользумеся пока).
Для упрощения жизни, все эти подробности скрыты в недрах системы и для прикладного разработчика (или администратора) можно либо пользоваться вышеуказанных интерфейсом системы, либо попросить сгенерить скрипт для включения логирования:
Ну, и наконец, приведу пример триггера на таблице, это позволит лучше понять механику:
Описанная схема отвечает описанным в начале статьи требованиям, однако тоже имеет свои отрицательные стороны
1.Для физически удаленных строк, при неизвестном уникальном ключе строки поиск по “вторичным” признакам займет значительно больше времени
2.Поскольку используются обычные таблицы для них генерируются redo и archive логи, хотя известно, что в эти таблицы происходит только добавление.
Мы пытались использовать другие способы с очередями, однако сложность реализации отката изменений при откате транзакций заставила отказаться от такого вариант, да и оверхед становился слишком большим.
Очень удобная функция, позволяющая реализовать логирование изменений, а поиск по ним встроен прямо в язык SQL запросов. Только посмотрите на изящность реализации:
Запрос возвращает сотрудника в том состоянии, в каком он был в на указанный момент времени.
Вот аналогичный запрос возвращающий историю изменений
Невероятно интуитивно и понятно.
К сожалению, оказалось, что этим невозможно воспользоваться!
1.Нет поддержки 3-tier архитектуры. Для каждого изменения можно узнать только пользователя в СУБД. Для 3-х звенок он всегда одинаковый. Нет никакой информации о клиентской машине.
2.Невероятная прожорливость. На одном из клиентов с включением FBA база начала расти со скоростью почти гигабайт в час (перед этим рост объема базы был примерно 50-100мб в час). Оказалось, что для каждой логируемой таблицы FBA создает копию ее структуры и при каждом изменении копирует ВСЮ строку. Кроме того, он не проверяет, есть ли реальное изменение.
Надеемся, что в будущем коллеги смогут улучшить FBA, это действительно удобный в использовании инструмент. Он может быть использован и сейчас в классической клиент-серверной архитектуре. Но будьте осторожны — наличие задачи, периодически меняющей даже одно поле в “широкой” таблице может легко слопать все дисковое пространство.
Пока не исследованным остался Oracle Streams. Надеемся, что написание клиента для Oracle Streams позволит отказаться от триггеров и таблиц в системе и значительно снизить оверхед на главной базе, перенеся его куда-то еще.
Но на текущий момент реализованный способ при своих недостатках является наименьшим найденным злом. Надеюсь, в приведенные в статье факты помогут выбрать для себя удобный вариант реализации логирования.
Любая нормальная ERP система обязана иметь возможность проведения расследования о внесенных изменениях. Без такой возможности невозможно действительно передать на программу функцию управления ресурсами компании. Таким образом, система отслеживания изменений должна позволять отслеживать все изменения, требовать минимального расхода памяти (оперативной и дисковой), накладывать минимальный overhead при операциях. Система отслеживания изменений должна предоставлять возможность поиска и просмотра изменений с датой, и описанием сделанного изменения, e.g. новое значение, кто сделал, какого рода изменение. В реальных условиях надо учесть, что отслеживать требуется только реально сделанные (зафиксированные в СУБД) изменения.
Сразу оговорюсь, что мы пробовали несколько подходов, в том числе самый очевидный для Oracle — Flashback Archive. Почему он не подошел, расскажу в конце статьи.
Реализация
В итоге, мы остановились на реализации логирования на триггерах. Для хранения всей истории достаточно 4-х таблиц и немного логики:
Таблицы LOG_TABLES и LOG_FIELDS содержат перечисления таблиц и отдельных полей, логируемых системой. Для управления этими таблицами в интерфейсе администратора есть специальные инструменты:
В этой форме администратор может включать логирование как для всей таблицы, так и отдельных полей. Для больших баз управление логированием требует особенной аккуратности, и часто этим занимается администратор СУБД, поэтому в режиме по умолчанию мы показываем именно таблицы и названия полей как они есть в базе данных. Для остальных можно переключиться на просмотр объектов системы.
Таблицы LOG_CHANGES и LOG_FIELDS_CHANGED содержат непосредственно информацию о изменениях. LOG_CHANGES — информацию, о изменении в таблице, пользователя и сессии из которой сделано изменение. В сессии, кстати, на всякий случай хранится информация о клиентской машине с которой произведен вход на сервер приложений, что, опять же, упрощает расследования. Еще небольшое отступление про пользователя и такую функцию в системе как маскарад. Маскарад — это возможность для пользователя (как правило администратора, разработчика, инженера техподдержки или отдела тестирования) войти в систему со всеми правами и настройками другого пользователя не вводя его (другого пользователя) пароль. Собственно пароли никому не известны и в базе их не видно. Так вот, при маскараде в истории изменений мы запоминаем реального пользователя. Таким образом, войти и проверить что-либо разработчик может, а вот все изменения будут видны как сделанные из-под его реального пользователя.
Вернемся к LOG_FIELDS_CHANGED. Как понятно из названия эта таблица хранит детальную информацию о измененных полях и их новых значениях. Поле Type содержит тип изменения — Insert, Delete, Update. Система сама генерирует триггеры для объектов метаданных. В триггере мы явно проверяем, что значение поля обновлено. Часто удобно написать запрос, который обновляет поле в его же значение. Такое изменение, а точнее, его отсутствие нас интересовать не должно.
Ради интереса можете проверить — триггер BEFORE INSERT OR UPDATE FOR EACH ROW будет вызван для каждой обновляемой строки. Проверка добавляет минимальный overhead и значительно снижает нагрузку на диск.
Такая система позволяет логировать произвольные таблицы (в том числе и не описанные как объекты метаданных — правда это экзотическая ситуация). Для реального использования кроме триггера на таблице необходима колонка, уникально идентифицирующая строку, в нашем варианте требуется сквозная нумерация для всех таблиц. Итого — один сиквенс, колонка и триггер для ее вычисления (последний оставлен для обратной совместимости с Oracle 11g, в 12с появились автоинкрементные поля, но мы ими не пользумеся пока).
Для упрощения жизни, все эти подробности скрыты в недрах системы и для прикладного разработчика (или администратора) можно либо пользоваться вышеуказанных интерфейсом системы, либо попросить сгенерить скрипт для включения логирования:
Ну, и наконец, приведу пример триггера на таблице, это позволит лучше понять механику:
CREATE OR REPLACE TRIGGER LG_PARAM_MODES
BEFORE
INSERT OR DELETE OR UPDATE
ON PARAM_MODES FOR EACH ROW
DECLARE
LOGCHANGEID NUMBER(18);
LOGID NUMBER(18);
CHANGEFLAG BOOLEAN;
VCHANGE_TYPE CHAR(1);
VCOL_TYPE CHAR(1);
VTABLE_ID NUMBER(18);
VFIELD_ID NUMBER(18);
BEGIN
LOGCHANGEID := LOG_CHANGES_SEQ.NEXTVAL;
CHANGEFLAG := FALSE;
VTABLE_ID := 164;
IF INSERTING THEN
LOGID := LOG_ID_SEQ.NEXTVAL;
:NEW.LOG_ID := LOGID;
VCHANGE_TYPE := 'I';
IF (:NEW.NAME IS NOT NULL) THEN
CHANGEFLAG := TRUE;
VFIELD_ID := 1639;
VCOL_TYPE := 'V';
INSERT INTO LOG_FIELDS_CHANGED (ID, TYPE, VARCHAR2_VALUE, CHANGE_ID, FIELD_ID)
VALUES (LOG_FIELDS_CHANGED_SEQ.NEXTVAL, VCOL_TYPE, :NEW.NAME, LOGCHANGEID, VFIELD_ID);
END IF;
IF (:NEW.ID IS NOT NULL) THEN
CHANGEFLAG := TRUE;
VFIELD_ID := 1638;
VCOL_TYPE := 'N';
INSERT INTO LOG_FIELDS_CHANGED (ID, TYPE, NUMBER_VALUE, CHANGE_ID, FIELD_ID)
VALUES (LOG_FIELDS_CHANGED_SEQ.NEXTVAL, VCOL_TYPE, :NEW.ID, LOGCHANGEID, VFIELD_ID);
END IF;
IF (CHANGEFLAG = TRUE) THEN
INSERT INTO LOG_CHANGES (ID, LOG_ID, TIME, TYPE, TABLE_ID, USER_ID, SESSION_ID)
VALUES (LOGCHANGEID, LOGID, SYSDATE, VCHANGE_TYPE, VTABLE_ID, GET_REAL_UID, GET_SESSION_ID);
END IF;
ELSIF DELETING THEN
LOGID := :OLD.LOG_ID;
IF LOGID IS NULL THEN
LOGID := LOG_ID_SEQ.NEXTVAL;
END IF;
VCHANGE_TYPE := 'D';
IF (:OLD.NAME IS NOT NULL) THEN
CHANGEFLAG := TRUE;
VFIELD_ID := 1639;
VCOL_TYPE := 'V';
INSERT INTO LOG_FIELDS_CHANGED(ID, TYPE, VARCHAR2_VALUE, CHANGE_ID, FIELD_ID)
VALUES (LOG_FIELDS_CHANGED_SEQ.NEXTVAL, VCOL_TYPE, :OLD.NAME, LOGCHANGEID, VFIELD_ID);
END IF;
IF (:OLD.ID IS NOT NULL) THEN
CHANGEFLAG := TRUE;
VFIELD_ID := 1638;
VCOL_TYPE := 'N';
INSERT INTO LOG_FIELDS_CHANGED(ID, TYPE, NUMBER_VALUE, CHANGE_ID, FIELD_ID)
VALUES (LOG_FIELDS_CHANGED_SEQ.NEXTVAL, VCOL_TYPE, :OLD.ID, LOGCHANGEID, VFIELD_ID);
END IF;
IF (CHANGEFLAG = TRUE) THEN
INSERT INTO LOG_CHANGES(ID, LOG_ID, TIME, TYPE, TABLE_ID, USER_ID, SESSION_ID)
VALUES(LOGCHANGEID, LOGID, SYSDATE, VCHANGE_TYPE, VTABLE_ID, GET_REAL_UID, GET_SESSION_ID);
END IF;
ELSIF UPDATING THEN
LOGID := :NEW.LOG_ID;
IF LOGID IS NULL THEN
LOGID := LOG_ID_SEQ.NEXTVAL;
:NEW.LOG_ID := LOGID;
END IF;
VCHANGE_TYPE := 'U';
IF ((:OLD.NAME <> :NEW.NAME) OR
(:OLD.NAME IS NULL AND :NEW.NAME IS NOT NULL) OR
(:OLD.NAME IS NOT NULL AND :NEW.NAME IS NULL)) THEN
CHANGEFLAG := TRUE;
VFIELD_ID := 1639;
VCOL_TYPE := 'V';
INSERT INTO LOG_FIELDS_CHANGED(ID, TYPE, VARCHAR2_VALUE, CHANGE_ID, FIELD_ID)
VALUES (LOG_FIELDS_CHANGED_SEQ.NEXTVAL, VCOL_TYPE, :NEW.NAME, LOGCHANGEID, VFIELD_ID);
END IF;
IF ((:OLD.ID <> :NEW.ID) OR
(:OLD.ID IS NULL AND :NEW.ID IS NOT NULL) OR
(:OLD.ID IS NOT NULL AND :NEW.ID IS NULL)) THEN
CHANGEFLAG := TRUE;
VFIELD_ID := 1638;
VCOL_TYPE := 'N';
INSERT INTO LOG_FIELDS_CHANGED(ID, TYPE, NUMBER_VALUE, CHANGE_ID, FIELD_ID)
VALUES (LOG_FIELDS_CHANGED_SEQ.NEXTVAL, VCOL_TYPE, :NEW.ID, LOGCHANGEID, VFIELD_ID);
END IF;
IF (CHANGEFLAG = TRUE) THEN
INSERT INTO LOG_CHANGES(ID, LOG_ID, TIME, TYPE, TABLE_ID, USER_ID, SESSION_ID)
VALUES(LOGCHANGEID, LOGID, SYSDATE, VCHANGE_TYPE, VTABLE_ID, GET_REAL_UID, GET_SESSION_ID);
END IF;
END IF;
END;
/
Описанная схема отвечает описанным в начале статьи требованиям, однако тоже имеет свои отрицательные стороны
1.Для физически удаленных строк, при неизвестном уникальном ключе строки поиск по “вторичным” признакам займет значительно больше времени
2.Поскольку используются обычные таблицы для них генерируются redo и archive логи, хотя известно, что в эти таблицы происходит только добавление.
Мы пытались использовать другие способы с очередями, однако сложность реализации отката изменений при откате транзакций заставила отказаться от такого вариант, да и оверхед становился слишком большим.
Oracle Flashback Archive
Очень удобная функция, позволяющая реализовать логирование изменений, а поиск по ним встроен прямо в язык SQL запросов. Только посмотрите на изящность реализации:
SELECT * FROM employee AS OF TIMESTAMP TO_TIMESTAMP('2003-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
WHERE name = 'JOHN';
Запрос возвращает сотрудника в том состоянии, в каком он был в на указанный момент времени.
Вот аналогичный запрос возвращающий историю изменений
SELECT versions_startscn, versions_starttime, versions_endscn, versions_endtime,
versions_xid, versions_operation, name, salary
FROM employee
VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP('2003-07-18 14:00:00', 'YYYY-MM-DD HH24:MI:SS')
AND TO_TIMESTAMP('2003-07-18 17:00:00', 'YYYY-MM-DD HH24:MI:SS')
WHERE name = 'JOE';
Невероятно интуитивно и понятно.
К сожалению, оказалось, что этим невозможно воспользоваться!
1.Нет поддержки 3-tier архитектуры. Для каждого изменения можно узнать только пользователя в СУБД. Для 3-х звенок он всегда одинаковый. Нет никакой информации о клиентской машине.
2.Невероятная прожорливость. На одном из клиентов с включением FBA база начала расти со скоростью почти гигабайт в час (перед этим рост объема базы был примерно 50-100мб в час). Оказалось, что для каждой логируемой таблицы FBA создает копию ее структуры и при каждом изменении копирует ВСЮ строку. Кроме того, он не проверяет, есть ли реальное изменение.
Надеемся, что в будущем коллеги смогут улучшить FBA, это действительно удобный в использовании инструмент. Он может быть использован и сейчас в классической клиент-серверной архитектуре. Но будьте осторожны — наличие задачи, периодически меняющей даже одно поле в “широкой” таблице может легко слопать все дисковое пространство.
В качестве заключения.
Пока не исследованным остался Oracle Streams. Надеемся, что написание клиента для Oracle Streams позволит отказаться от триггеров и таблиц в системе и значительно снизить оверхед на главной базе, перенеся его куда-то еще.
Но на текущий момент реализованный способ при своих недостатках является наименьшим найденным злом. Надеюсь, в приведенные в статье факты помогут выбрать для себя удобный вариант реализации логирования.