Knowing how LCR moves from source to target is only part of the story. What an LCR contains is also important. Let's start by going over what we know about database transactions. Every transaction in a database is assigned a unique transaction ID. The transaction itself can be composed of one or more DML or DDL instructions.
Note
Most implicit DDL LCRs will have a single DDL instruction, due to the implicit commit nature of Oracle's handling of DDL.
Each one of these instructions is associated to its parent transaction via this ID. When we attempt to commit a transaction, all the instructions in the transaction must be successfully completed or the whole transaction fails/rolls back. This means that all the DML/DDL instructions within that transaction do not get applied to the database. Remember this. It will be important when you have to troubleshoot situations where a user demands "Where did my data go?"
As mentioned above, an LCR is a logical change record that is created by the Capture process. The content of the LCR is the actual steps the database took to accomplish the change instruction(s) of a transaction. These steps are stored in a special, ordered format that is then parsed by the Apply process to rebuild the SQL to duplicate the original transaction. We know that a transaction can have multiple instructions, thus, an LCR can include multiple steps. Each one of these steps is a message. When you look at the LCR metadata (where available; usually in error queues), you will see that each LCR has a message count, and that each message has a sequential ID.
The message itself is composed of metadata from which the Apply process builds the SQL to accomplish the instruction. This information includes (but is not limited to) the following:
- Message ID/Sequence
- Message type name: LCR or User Enqueued Message
- Source database: where the LCR originated
- Owner: Schema owner for the object/table which the message is changing
- Object: Name of the object/table
- Is Tag Null: Indicates if there are any tag values. (
Ymeans no tag values) command_type:- If a DML message, this will be
INSERT/UPDATE/DELETE/LOB_UPDATE - If a DDL message, this will be
CREATE/ALTER/DROP/and so on
- If a DML message, this will be
- Change Values:
- The values included depend on the command type:
- Command type:
INSERT, you will only see new values - Command type:
DELETE, you will only see old values - Command type:
UPDATE, you will see both old and new values
- Command type:
- For special field data types, you may also see a
typenamevalue as well (such as timestamp). If a DDL message: you will see the actual command text.
There is additional information stored in LCRs. If you wish to familiarize yourself with the content of LCRs you can review the Types of Information Captured with Oracle Streams section in the Oracle Streams Concepts and Administration user's manual, and SYS.LCR$_ROW_RECORD and LCR$_DDL_RECORD type definitions found in the Oracle PL/SQL Packages and Types Reference manual. These types are visible to the user for use in explicit capture and are used by implicit capture as well.
For regular, implicit Streams, you will most likely only need to extract data from an LCR in the event of an apply error. You would extract and review this data to determine what was in the LCR to help determine what caused the error. You can drill down to the LCRs in the Apply Error Queue using Enterprise Manager or you can create your own procedures that use Oracle APIs to extract the LCR data (we will go over this in more detail in Chapter 8, Dealing with the Ever Constant Tides of Change, dealing with how to administer and monitor Oracle 11g Streams).
In an Oracle replicated environment (Streams or Advanced), Conflict detection is always turned on. Conflict detection acts as a guard-dog to the LCR. When the Apply process attempts to apply the changes in an LCR, it first calls Conflict detection to verify that the change can be applied without the unexpected loss of data at the Apply site. Conflict detection identifies the row to be changed by the LCR. It then compares values in the LCR with the actual values in the existing row (if they exist). Depending on the change type, if certain values don't match (also known as data divergence), Conflict detection will attempt to find any conflict resolution rules assigned to the Apply process.
If none are found, or the conflict resolution rules do not resolve the conflict, the Conflict detection will not allow the change to be applied by raising an error to the Apply process. If this happens, the Apply process will place the LCR, along with the error raised by Conflict detection, in the Apply Error queue.
Note
If an LCR is placed in the Apply Error queue, the DML/DDL messages in that LCR have not been applied to the database object. This means all messages (DML/DDL instructions) in the LCR, not just the one(s) that failed. If you have multiple messages in the LCR, there may only be one message that fails, but the entire LCR transaction fails because of that one message failure. Keep this in mind when developing your transactions. The more messages you have in an LCR, the more difficult it is to determine which message(s) caused the failure.
If an LCR fails, all subsequent LCRs dependent on that failed LCR will also fail. This makes it very important to have as much understanding about how data changes will flow through your distributed environment before you implement production. If not carefully planned, all your changes could easily end up in your target error queue. It also makes it very important to faithfully monitor the Apply Error queues and address errors as quickly as possible.
The key to conflict detection and LCR playing nicely together is planning and conflict resolution. These activities are discussed in more detail in the following chapters.
As discussed earlier, conflict detection will compare all the values of all the columns by default. You do have some control on whether or not a non-key column value should be compared or can be ignored and when. This is accomplished with the DBMS_APPLY_ADM.COMPARE_OLD_VALUES procedure.
This procedure allows you specify a list of non-key columns in a table that are either included or excluded from conflict detection value comparison. Use this power with caution! Make sure you have identified all the ramifications to data convergence if you choose to exclude column values from conflict detection to avoid unexpected data loss.
The key term is is "non-key columns". The DBMS_APPLY_ADM.COMPARE_OLD_VALUES procedure will not let you exclude key columns. It will raise an error. If you absolutely, positively, without question, must exclude a key column from conflict detection, you will need to redefine the table's key column list using the DBMS_APPLY_ADM.SET_KEY_COLUMNS. Again, use this with reserve.
The method used to create an LCR determines the LCR type.
- If an LCR is created by an asynchronous Capture process (implicitly) it is a captured LCR
- If the LCR is created by a user application (explicitly), by a synchronous Capture process, or enqueued by an Apply process, it is a persistent LCR
- If an LCR is explicitly created by an application and enqueued with
delivery_modeset toBUFFERED, it is a buffered LCR