Does Big Brother keep a close eye on you?
Today will be about organizing the tracking of changes in our platform .
Any normal ERP system must be able to investigate the changes. Without such a possibility, it is impossible to really transfer the company's resource management function to the program. Thus, the change tracking system should allow you to track all changes, require minimal memory consumption (operational and disk), impose a minimum overhead during operations. The change tracking system should provide the ability to search and view changes with a date, and a description of the change made, eg a new value, who made what kind of change. In real conditions, it is necessary to take into account that only real changes (recorded in the DBMS) are required to be tracked.
At once I will make a reservation that we tried several approaches, including the most obvious for Oracle - the Flashback Archive. Why he did not come, I will tell at the end of the article.
As a result, we stopped on the implementation of logging on triggers. To store the entire history, 4 tables and a bit of logic are enough:
The LOG_TABLES and LOG_FIELDS tables contain enumerations of tables and individual fields logged by the system. There are special tools for managing these tables in the admin interface:
In this form, the administrator can include logging for both the entire table and individual fields. For large databases, logging management requires special care, and often the DBMS administrator does this, so in the default mode we show the tables and field names as they are in the database. For the rest, you can switch to viewing the objects of the system.
')
The LOG_CHANGES and LOG_FIELDS_CHANGED tables contain information about changes directly. LOG_CHANGES - information about the change in the table, the user and the session from which the change was made. By the way, in the session, just in case, information is stored about the client machine from which the application server was logged in, which, again, simplifies investigations. Another small digression about the user and such a function in the system as a masquerade. Masquerade is an opportunity for a user (usually an administrator, developer, technical support engineer or testing department) to log in with all the rights and settings of another user without entering his (other user) password. Actually passwords are not known to anyone and they are not visible in the database. So, with a masquerade in the change history, we remember the real user. Thus, the developer can log in and check anything, but all changes will be visible as made from under his real user.
Let's go back to LOG_FIELDS_CHANGED. As the name implies, this table stores detailed information about the changed fields and their new values. The Type field contains the type of change - Insert, Delete, Update. The system itself generates triggers for metadata objects. In the trigger, we explicitly check that the field value is updated. It is often convenient to write a query that updates the field to its own value. Such a change, or rather, its absence should not interest us.
For the sake of interest you can check - the trigger BEFORE INSERT OR UPDATE FOR EACH ROW will be called for each updated row. The check adds minimal overhead and significantly reduces the load on the disk.
Such a system allows you to log arbitrary tables (including those not described as metadata objects - though this is an exotic situation). For real use, except for a trigger on a table, a column is needed that uniquely identifies the row; in our variant, continuous numbering is required for all tables. Total - one sequence, column and trigger for its calculation (the latter is left for backwards compatibility with Oracle 11g, in 12c auto-increment fields appeared, but we don’t use them yet).
To simplify life, all these details are hidden in the depths of the system and for the application developer (or administrator) you can either use the above system interface, or ask to generate a script to enable logging:
Well, finally, I will give an example of a trigger on the table, this will allow a better understanding of the mechanics:
The described scheme meets the requirements described at the beginning of the article, but also has its negative sides.
1. For physically deleted strings, if the string unique key is unknown, the search by “secondary” features will take much longer
2. Since the usual tables are used for them, redo and archive logs are generated, although it is known that only the addition occurs in these tables.
We tried to use other methods with queues, however, the complexity of implementing rollback changes when rolling back transactions forced us to abandon this option, and the overhead projector became too large.
A very handy feature that allows you to implement logging changes, and searching them is built right into the SQL query language. Just look at the elegance of the implementation:
The request returns the employee as he was at at the specified time.
Here is a similar query that returns the change history.
Incredibly intuitive and understandable.
Unfortunately, it turned out that it is impossible to use!
1.No support for 3-tier architecture. For each change, you can find out only the user in the DBMS. For the 3 rd he is always the same. There is no information about the client machine.
2. Incredible gluttony. At one of the clients with the inclusion of FBA, the base began to grow at a speed of almost gigabytes per hour (before that, the growth in the base volume was about 50-100 MB per hour). It turned out that for each logged table, FBA creates a copy of its structure and copies the ALL line with each change. In addition, it does not check if there is a real change.
We hope that in the future colleagues will be able to improve the FBA, this is a really convenient tool to use. It can be used now in the classic client-server architecture. But be careful - having a task that periodically changes even one field in a “wide” table can easily gobble up all the disk space.
Oracle Streams has not yet been explored. We hope that writing a client for Oracle Streams will allow you to abandon triggers and tables in the system and significantly reduce the overhead head on the main base by moving it somewhere else.
But at the moment, the implemented method, with its flaws, is the smallest evil found. I hope the facts in the article will help you choose a convenient implementation option for logging.
Any normal ERP system must be able to investigate the changes. Without such a possibility, it is impossible to really transfer the company's resource management function to the program. Thus, the change tracking system should allow you to track all changes, require minimal memory consumption (operational and disk), impose a minimum overhead during operations. The change tracking system should provide the ability to search and view changes with a date, and a description of the change made, eg a new value, who made what kind of change. In real conditions, it is necessary to take into account that only real changes (recorded in the DBMS) are required to be tracked.
At once I will make a reservation that we tried several approaches, including the most obvious for Oracle - the Flashback Archive. Why he did not come, I will tell at the end of the article.
Implementation
As a result, we stopped on the implementation of logging on triggers. To store the entire history, 4 tables and a bit of logic are enough:
The LOG_TABLES and LOG_FIELDS tables contain enumerations of tables and individual fields logged by the system. There are special tools for managing these tables in the admin interface:
In this form, the administrator can include logging for both the entire table and individual fields. For large databases, logging management requires special care, and often the DBMS administrator does this, so in the default mode we show the tables and field names as they are in the database. For the rest, you can switch to viewing the objects of the system.
')
The LOG_CHANGES and LOG_FIELDS_CHANGED tables contain information about changes directly. LOG_CHANGES - information about the change in the table, the user and the session from which the change was made. By the way, in the session, just in case, information is stored about the client machine from which the application server was logged in, which, again, simplifies investigations. Another small digression about the user and such a function in the system as a masquerade. Masquerade is an opportunity for a user (usually an administrator, developer, technical support engineer or testing department) to log in with all the rights and settings of another user without entering his (other user) password. Actually passwords are not known to anyone and they are not visible in the database. So, with a masquerade in the change history, we remember the real user. Thus, the developer can log in and check anything, but all changes will be visible as made from under his real user.
Let's go back to LOG_FIELDS_CHANGED. As the name implies, this table stores detailed information about the changed fields and their new values. The Type field contains the type of change - Insert, Delete, Update. The system itself generates triggers for metadata objects. In the trigger, we explicitly check that the field value is updated. It is often convenient to write a query that updates the field to its own value. Such a change, or rather, its absence should not interest us.
For the sake of interest you can check - the trigger BEFORE INSERT OR UPDATE FOR EACH ROW will be called for each updated row. The check adds minimal overhead and significantly reduces the load on the disk.
Such a system allows you to log arbitrary tables (including those not described as metadata objects - though this is an exotic situation). For real use, except for a trigger on a table, a column is needed that uniquely identifies the row; in our variant, continuous numbering is required for all tables. Total - one sequence, column and trigger for its calculation (the latter is left for backwards compatibility with Oracle 11g, in 12c auto-increment fields appeared, but we don’t use them yet).
To simplify life, all these details are hidden in the depths of the system and for the application developer (or administrator) you can either use the above system interface, or ask to generate a script to enable logging:
Well, finally, I will give an example of a trigger on the table, this will allow a better understanding of the mechanics:
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; / The described scheme meets the requirements described at the beginning of the article, but also has its negative sides.
1. For physically deleted strings, if the string unique key is unknown, the search by “secondary” features will take much longer
2. Since the usual tables are used for them, redo and archive logs are generated, although it is known that only the addition occurs in these tables.
We tried to use other methods with queues, however, the complexity of implementing rollback changes when rolling back transactions forced us to abandon this option, and the overhead projector became too large.
Oracle Flashback Archive
A very handy feature that allows you to implement logging changes, and searching them is built right into the SQL query language. Just look at the elegance of the implementation:
SELECT * FROM employee AS OF TIMESTAMP TO_TIMESTAMP('2003-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS') WHERE name = 'JOHN'; The request returns the employee as he was at at the specified time.
Here is a similar query that returns the change history.
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'; Incredibly intuitive and understandable.
Unfortunately, it turned out that it is impossible to use!
1.No support for 3-tier architecture. For each change, you can find out only the user in the DBMS. For the 3 rd he is always the same. There is no information about the client machine.
2. Incredible gluttony. At one of the clients with the inclusion of FBA, the base began to grow at a speed of almost gigabytes per hour (before that, the growth in the base volume was about 50-100 MB per hour). It turned out that for each logged table, FBA creates a copy of its structure and copies the ALL line with each change. In addition, it does not check if there is a real change.
We hope that in the future colleagues will be able to improve the FBA, this is a really convenient tool to use. It can be used now in the classic client-server architecture. But be careful - having a task that periodically changes even one field in a “wide” table can easily gobble up all the disk space.
As a conclusion.
Oracle Streams has not yet been explored. We hope that writing a client for Oracle Streams will allow you to abandon triggers and tables in the system and significantly reduce the overhead head on the main base by moving it somewhere else.
But at the moment, the implemented method, with its flaws, is the smallest evil found. I hope the facts in the article will help you choose a convenient implementation option for logging.
Source: https://habr.com/ru/post/245883/
All Articles