HP 3000 Manuals HP 3000 Manuals
Audit Logging In order for transactions to be copied and applied to the slave, there must be a record of the transactions that have committed against the appropriate partitions on the master. Log records would seem to be a logical choice, but regular log records cannot be used because they contain DBEnvironment specific locations. As objects located at a given address in one DBEnvironment will not be located at that same address in another DBEnvironment, some other mechanism must be used to transfer the transaction information. ALLBASE/Replicate solves this problem by creating a special kind of log records, called audit log records. Audit Log Records When audit logging is enabled on a master (by specifying the AUDIT LOG parameter in the START DBE NEW and START DBE NEWLOG statements), ALLBASE/Replicate creates audit log records. These audit log records are generated for tables in every master partition whenever direct updates are made to those tables. Audit log records are the backbone of the ALLBASE/Replicate product. They are the data structure through which the transactions committed on the master DBEnvironment are transferred to and applied to the slave DBEnvironment by the ALLBASE/Replicate master/slave applications. Mixed mode is when some tables act as the master in one DBEnvironment and as a slave in some other DBEnvironment.
NOTE If a DBEnvironment operates in mixed mode, audit log records are generated for those mixed mode tables each time they are directly or indirectly updated, when audit logging is enabled.
Audit Log Record Structure for Non-DDL Statements Each audit log record generated by a DML (Data Manipulation Language) statement (UPDATE, INSERT, or DELETE) contains an Op Code, the table name, the owner name, a column list, and a before and after image of the tuple (or row) being changed. Op Code indicates whether the log record is for an INSERT, UPDATE, or DELETE operation; or is a record containing DDL information or a comment generated by the LOG COMMENT statement. Table Name contains an ASCII representation of the table name of the replicated table, rather than a DBEnvironment specific identification. Owner Name contains an ASCII representation of the owner name of the replicated table, rather than a DBEnvironment specific identification. Column List identifies columns by column number, not column name. Therefore it is imperative that tables defined in the slave DBEnvironment have the columns defined in the same order as they are defined in the master DBEnvironment. The columns must be in the same ordinal position in both tables, and must be of the same data type. Images provides: * for UPDATE, the before-image and the after-image of the tuple (row) being changed. * for INSERT, the after image of the row being inserted. * for DELETE, the before image of the row being deleted. Audit Log Record Structure for DDL Statements and Log Comments Each audit log record connected with a DDL statement or a log comment has an Op Code. Audit log records generated by a DDL statement (such as CREATE TABLE) also contain a binary linear tree representation of the DDL statement. Audit log records generated by a log comment also contain an ASCII string representation of the log comment. Op Code indicates the type of DDL statement, or that it is a log comment record. Binary linear tree a tree representation of the DDL statement from which pointers to DBEnvironment specific locations have been removed. The DDL statements are part of special partitions (DEFINITION, STORAGE, AUTHORIZATION or SECTION) created when AUDIT ELEMENTS with those same names are specified in the START DBE NEW or START DBE NEWLOG statements. ASCII String a character representation of the log comment. LOG COMMENT log records are part of the COMMENT partition. Using Audit Log Records When audit logging is enabled, the audit log records are placed in the log file along with the normal log records. The advantage of this is that the audit log records have all the same protections as normal log records. The same write-ahead log protocol that applies to regular log records applies to audit log records. When using archive mode logging, audit log records are stored along with the regular log records. Audit log records can be handled by the routines that handle regular log records. When the ALLBASE/Replicate master/slave applications are running, as the slave sends its SCR array to the master, the master can locate (using the transaction identifier) the appropriate audit log records in the log file for the transactions that have not yet successfully committed on the slave. The master places the audit log records in proper sequence in a buffer owned by the master application. The master application handles the necessary network protocols to send the audit log records over the network to the slave's buffer space. The slave application then applies the changes to the slave DBEnvironment. The slave applies the changes on a row-by-row basis. It looks for a row with an image identical to the before-image in the log record, and replaces that row with the appropriate after-image. For this technique to operate successfully, the before-image on the slave must be identical in its entirety to the before-image in the audit log record.
WARNING Direct updates cannot be allowed to rows serving strictly in a slave role in slave tables. If you change even one column in a slave row with a direct update, the before-image in the audit log record cannot be matched against the correct slave row and the update cannot be applied. Direct updates may be made to rows acting in a master role, in tables which serve as a slave to one DBEnvironment and a master to another.
This is one implication of the row-by-row manner in which the slave operates. If operations are executed in a bulk manner on the master (such as an UPDATE to a table that changes many rows at a time instead of a single row), the updates may be applied to the master much more quickly than they are to the slave. In a few cases, this can cause the slave to begin to fall behind the master in the replication of changes to the slave. If this kind of update is the major scenario on the master, it can lead to situations where it is necessary to use hard resynchronization to catch up the slave to the master. Additionally, if you lock a table on the master in exclusive mode (getting one exclusive lock on the entire table), and then do a load to the table, you occasionally may run out of run-time control block space on the slave. This is because the slave table is not locked in exclusive mode. This can cause much more locking activity on the slave than on the master (for instance, a large number of page-level or row-level locks), exhausting the run-time control block space. Viewing Audit Log Records. Use the SQLAudit tool to view audit log records. SQLAudit is described in the section, "Using SQLAudit to check if Slave is Keeping Up," in chapter 3. (Refer to the SQLAudit appendix of the ALLBASE/SQL Database Administration Guide for more information.) Setting Up the Master Enable Audit Logging For a master DBEnvironment to produce audit log records, the following conditions must be met. Refer to Figure 2-3 through Figure 2-10 for annotated syntax for the statements discussed below. * Create partitions with the CREATE PARTITION statement, if the partitions (except the DEFAULT partition) will contain tables having audit log records. * Assign tables having audit log records to a partition that will generate audit log records either through the CREATE TABLE statement or the ALTER TABLE statement. If unassigned, they become part of the DEFAULT partition. You may specify the DEFAULT PARTITION for replication when the slave application is started. (That partition must not be the NONE partition or a partition that has been associated with the NONE partition) * Using the START DBE NEW or START DBE NEWLOG statement: * The AUDIT LOG parameter must be specified. * The DEFAULT PARTITION must be assigned a DefaultPartitionNumber or must be associated with the NONE partition. * The COMMENT partition can be assigned a CommentPartitionNumber, or it may be associated with either the DEFAULT or the NONE partition. If none of these actions are taken, the COMMENT partition will by default be associated with the DEFAULT partition. * A unique AUDIT NAME must be specified which uniquely identifies this particular DBEnvironment. This unique audit name must not be used to identify any other DBEnvironment existing in the network in which this DBEnvironment exists. * The CHECKPOINT and DATA AUDIT ELEMENTS can be specified. If they are not specified, they are the default AUDIT ELEMENT specification if the AUDIT LOG parameter is specified. * Any other desired AUDIT ELEMENTS (COMMENT, DEFINITION, STORAGE, AUTHORIZATION, SECTION, or ALL) must be specified. All parameters, except the AUDIT LOG parameter, should be specified at DBEnvironment creation time using the START DBE NEW statement. At that time, a temporary log file should be specified. (The temporary log file will later be replaced by a permanent log file when the START DBE NEWLOG statement is issued.) Next, complete the creation of the DBEnvironment. Create all database objects such as tables, indexes, and security specifications. Then load the DBEnvironment tables. These are activities you do not want to replicate using soft resynchronization. Then, using the START DBE NEWLOG statement, start the production of audit log records by specifying the AUDIT LOG parameter. Specify the name and characteristics of the permanent log file(s). Use the SQLUtil ADDLOG statement to add one or more additional log files.[REV BEG] ALLBASE/Replicate requires at least one additional log file.[REV END] If you do not have additional log space to switch to when the existing log gets full, there are potential problems. Either an "out of log space error" will stop the operation of the DBEnvironment and roll back all currently active transactions, or you risk overwriting log records for committed transactions on the master that are not yet replicated to the slave. Setting Up the Slave to Use Audit Log Records The slave DBEnvironment may be generated by using scripts with ISQL, or may be a copy of the master DBEnvironment. In order for the slave DBEnvironment to be able to use audit log records produced by the master DBEnvironment, it must meet the following conditions: * If any slave tables will be used as masters to some other slave DBEnvironment, partitions must be created (unless only the DEFAULT partition is needed). * If the slave will be used as the master in the event the master fails, partitions should be set up on the slave in the same manner they are set up on the master. This strategy will facilitate later role switching between master and slave. * Tables needing audit log records must be assigned to a partition that will generate audit log records, either through the use of the CREATE TABLE statement or the ALTER table statement, or by default be allowed to become part of the DEFAULT partition. (This partition must not be the NONE partition or a partition that is associated with the NONE partition.) * Using the START DBE NEW or START DBE NEWLOG statement: * The AUDIT LOG parameter must be specified. * The DEFAULT PARTITION (if needed) must be assigned a DefaultPartitionNumber or must be associated with the NONE partition. * The COMMENT partition (if needed) can be assigned a CommentPartitionNumber, or it may be associated with either the DEFAULT or the NONE partition. If none of these actions is taken, the COMMENT partition will by default be associated with the DEFAULT partition. * A unique AUDIT NAME must be specified that uniquely identifies this particular DBEnvironment. This unique audit name must not be used to identify any other DBEnvironment existing in the network in which this DBEnvironment exists. (An AUDIT NAME must be specified even if the DBEnvironment will be used only as a slave DBEnvironment.) * The CHECKPOINT and DATA AUDIT ELEMENTS can be specified. If they are not specified, they are the default AUDIT ELEMENT specification if the AUDIT LOG parameter is specified. If the DBEnvironment is being used in pure slave mode, specifying only the CHECKPOINT AUDIT ELEMENT limits the slave to generating only the synchronization information required by the ALLBASE/Replicate applications and the DATA audit element is not used. * Any other desired AUDIT ELEMENTS (COMMENT, DEFINITION, STORAGE, AUTHORIZATION, SECTION, or ALL) must be specified. All parameters except the AUDIT LOG parameter should be specified at slave DBEnvironment creation time using the START DBE NEW statement. At that time, a temporary log file should be specified. (The temporary log file will later be replaced by a permanent log file when the START DBE NEWLOG statement is executed.) Next, complete the creation of the slave DBEnvironment. Create all database objects such as tables, indexes, and security specifications. Load the DBEnvironment tables. These are activities you do not want to replicate using soft resynchronization. Use the START DBE NEWLOG statement to start the use of audit log records and the retention of the SCR array. Specify the AUDIT LOG parameter, and specify the name and characteristics of the permanent log file(s). Use the ADDLOG statement to add one or more additional log files at this time. If the slave DBEnvironment was created using SQLUtil STORE/RESTORE (or STOREONLINE and its associated restore commands), alter some of the parameters that were set for the master DBEnvironment. For example, when you execute START DBE NEWLOG, you must specify a new, unique AUDIT NAME. You may change partition definitions or AUDIT ELEMENTS at the same time.