Asynchronous Replication with Oracle Streams
Currently, the construction of many automated systems raises the problem of data synchronization across multiple sources of information. One of the ways to solve this problem is replication.
In this topic, I will talk about one of these problems and how you can solve this problem using Oracle Streams technology.
The problem of data synchronization across several sources of information is a rather non-trivial task with a very ambiguous solution.
Such problems occur quite often, but at the current moment there is practically no universal solution to such a problem. Almost all ready-made data replication systems operate with significant limitations on the structure and methods of accumulating and modifying data.
There are currently no delays in synchronous replication, but this, in turn, affects the throughput of transactions and the capabilities of the system as a whole.
Thus, it is not surprising that most users choose asynchronous replication.
The challenge is to design an asynchronous replication system that can guarantee a small fixed amount of latency and will support the full throughput of database transactions.
')
Oracle Streams is a versatile, flexible mechanism for exchanging information between servers in a multi-server architecture. Allows you to simultaneously implement replication, messaging, loading data warehouses, work with events, support a backup database. The data follows user-defined routes and is delivered to the destination. The result is a mechanism that provides greater functionality and flexibility than traditional solutions for storing and distributing data, and sharing them with other databases and applications.
Oracle Streams is a separate information infrastructure that consists of the processes of capture , propagation and apply information.
In the context of Oracle Streams, the informational representation of any change made to the database is called LCR (logical change record). LCR is a generalized representation of all possible changes presented in a database.
CR (change record) - change record, used to indicate a specific change in the database.
The user also has the ability to determine the correspondence between the LCR and the rule set. These rules evaluate all changes made to the database and filter non-conforming LCRs.
For example, the following rule defines only DML changes to the SCOTT.EMP table.
Similarly, rules are defined for DDL changes.
In addition, transformations can be tied to the rules. Transformations use custom or system stored procedures and automatically modify any LCR that satisfies the conditions of the rule being used.
The queues store the LCR when they move in the system, i.e. are “between” Oracle Streams processes.
One of the top priorities when setting up Oracle Streams is to create queues and link them to Oracle Streams processes. For each Oracle Streams process, a set of rules and transformations associated with these rules can be defined in order to be able to filter information at the “input” and “output” of the process.
Queues support three types of operations, enqueue - building LCR in a queue, browse - viewing LCR and dequeue - deleting.
Oracle Streams has three main processes:
The main tasks of the capture process:
As well as capture , the propagation process performs 3 main tasks:
Apply process:
In general, all changes made to the database are recorded in the transaction logs, but the changes made by the apply process are not written to the transaction logs, as they occur in the background mode invisible to the user.
The figure shows a general replication scheme based on Oracle Streams:
The example shows a one-way replication option, but other options are possible. For example, we can add another set of capture, propagation, and application processes in the opposite direction to get two-way replication. Similarly, by combining the relevant processes, it is possible to form any replication topology.
As mentioned above, the basis of each LCR is CR, which carries the minimum amount of information. Normally, it is possible to retrieve changed attributes and rowid.
When a data change occurs, i.e. DML change, LCR must contain the primary keys of the rows being changed. But it is possible that the received data is used in parallel processes, and then other, non-key, columns may be needed. Thus, CR may include additional, not key, columns. This is necessary to ensure that these columns remain unchanged, as well as to strengthen the verification of the correspondence between the source and receiver strings. Adding these columns to transaction logs is called supplemental logging.
When solving some problems using Oracle Streams, it will be convenient to change the received LCR “on the fly” using a stored procedure written by the user and called apply handler .
For example, this is necessary when replications occur between schemes with different names and, thus, the source LCR cannot correctly apply on the receiver. It follows that the task of the apply handler is to transform the changes of the source, represented as LCR, so that they can be correctly applied at the receiver.
Asynchronous replication, as well as synchronous, has its drawbacks. The main disadvantage of asynchronous replication is the possibility of data inconsistencies, also called data conflicts. They occur when users make changes on the receiver, and these changes conflict with the source data. In other words, after changes made by the user, the source and destination data do not match. These possible inconsistencies require analysis and resolution. Such conflicts most often occur when updating data at the source.
In the analysis of the LCR taken "old" data source, i.e. data that was prior to the change made by a specific LCR. Then, during the application, they are compared with the current values ​​in the updated string on the receiver.
In addition to user compliance checks, the database also tracks violations of referential integrity, uniqueness, and other restrictions.
In addition, for analysis, Oracle Streams contains a built-in conflicting handler. The two main “modes” of this handler are “maximum value” and “overwrite”. In the first mode, when a conflict arises, the old and new values ​​are compared and the largest is recorded, for strings, the larger value is selected using ASCII codes, in the second, the new value is always recorded. Also, users can handle conflicts themselves, writing a stored procedure that will solve conflicts that arise.
But practice shows that in most cases the “modes” of the built-in handler are suitable for the requirements of most users.
In this article, I tried to describe the main processes and objects of Oracle Streams, which in my opinion may be of interest to someone. I did not go into details, but described everything superficially. More details can always be found in the official documentation. The main thing is to know what to read.
In this topic, I will talk about one of these problems and how you can solve this problem using Oracle Streams technology.
Abstract
The problem of data synchronization across several sources of information is a rather non-trivial task with a very ambiguous solution.
Such problems occur quite often, but at the current moment there is practically no universal solution to such a problem. Almost all ready-made data replication systems operate with significant limitations on the structure and methods of accumulating and modifying data.
Introduction to the problem
There are currently no delays in synchronous replication, but this, in turn, affects the throughput of transactions and the capabilities of the system as a whole.
Thus, it is not surprising that most users choose asynchronous replication.
The challenge is to design an asynchronous replication system that can guarantee a small fixed amount of latency and will support the full throughput of database transactions.
')
Oracle Streams technology
Oracle Streams is a versatile, flexible mechanism for exchanging information between servers in a multi-server architecture. Allows you to simultaneously implement replication, messaging, loading data warehouses, work with events, support a backup database. The data follows user-defined routes and is delivered to the destination. The result is a mechanism that provides greater functionality and flexibility than traditional solutions for storing and distributing data, and sharing them with other databases and applications.
Oracle Streams is a separate information infrastructure that consists of the processes of capture , propagation and apply information.
LCR, CR
In the context of Oracle Streams, the informational representation of any change made to the database is called LCR (logical change record). LCR is a generalized representation of all possible changes presented in a database.
CR (change record) - change record, used to indicate a specific change in the database.
Rules and transformations
The user also has the ability to determine the correspondence between the LCR and the rule set. These rules evaluate all changes made to the database and filter non-conforming LCRs.
For example, the following rule defines only DML changes to the SCOTT.EMP table.
:dml.get_object_owner()='SCOTT' and :dml.get_object_name()='EMP' Similarly, rules are defined for DDL changes.
In addition, transformations can be tied to the rules. Transformations use custom or system stored procedures and automatically modify any LCR that satisfies the conditions of the rule being used.
Queues
The queues store the LCR when they move in the system, i.e. are “between” Oracle Streams processes.
One of the top priorities when setting up Oracle Streams is to create queues and link them to Oracle Streams processes. For each Oracle Streams process, a set of rules and transformations associated with these rules can be defined in order to be able to filter information at the “input” and “output” of the process.
Queues support three types of operations, enqueue - building LCR in a queue, browse - viewing LCR and dequeue - deleting.
Capture, Propagation and Apply
Oracle Streams has three main processes:
- capture ,
- propagation ,
- apply .
The main tasks of the capture process:
- reading changes contained in transaction logs,
- CR to LCR conversion,
- Queuing LCR.
As well as capture , the propagation process performs 3 main tasks:
- view LCR,
- transferring LCR from one queue to another, and the queues can be located on the same database or on different ones,
- LCR removal.
Apply process:
- retrieves the received LCR from the queue,
- makes changes to the database in accordance with LCR,
- removes LCR from the queue.
In general, all changes made to the database are recorded in the transaction logs, but the changes made by the apply process are not written to the transaction logs, as they occur in the background mode invisible to the user.
Overview
The figure shows a general replication scheme based on Oracle Streams:
The example shows a one-way replication option, but other options are possible. For example, we can add another set of capture, propagation, and application processes in the opposite direction to get two-way replication. Similarly, by combining the relevant processes, it is possible to form any replication topology.
Supplemental Logging
As mentioned above, the basis of each LCR is CR, which carries the minimum amount of information. Normally, it is possible to retrieve changed attributes and rowid.
When a data change occurs, i.e. DML change, LCR must contain the primary keys of the rows being changed. But it is possible that the received data is used in parallel processes, and then other, non-key, columns may be needed. Thus, CR may include additional, not key, columns. This is necessary to ensure that these columns remain unchanged, as well as to strengthen the verification of the correspondence between the source and receiver strings. Adding these columns to transaction logs is called supplemental logging.
Apply handler
When solving some problems using Oracle Streams, it will be convenient to change the received LCR “on the fly” using a stored procedure written by the user and called apply handler .
For example, this is necessary when replications occur between schemes with different names and, thus, the source LCR cannot correctly apply on the receiver. It follows that the task of the apply handler is to transform the changes of the source, represented as LCR, so that they can be correctly applied at the receiver.
Conflicts
Asynchronous replication, as well as synchronous, has its drawbacks. The main disadvantage of asynchronous replication is the possibility of data inconsistencies, also called data conflicts. They occur when users make changes on the receiver, and these changes conflict with the source data. In other words, after changes made by the user, the source and destination data do not match. These possible inconsistencies require analysis and resolution. Such conflicts most often occur when updating data at the source.
In the analysis of the LCR taken "old" data source, i.e. data that was prior to the change made by a specific LCR. Then, during the application, they are compared with the current values ​​in the updated string on the receiver.
In addition to user compliance checks, the database also tracks violations of referential integrity, uniqueness, and other restrictions.
In addition, for analysis, Oracle Streams contains a built-in conflicting handler. The two main “modes” of this handler are “maximum value” and “overwrite”. In the first mode, when a conflict arises, the old and new values ​​are compared and the largest is recorded, for strings, the larger value is selected using ASCII codes, in the second, the new value is always recorded. Also, users can handle conflicts themselves, writing a stored procedure that will solve conflicts that arise.
But practice shows that in most cases the “modes” of the built-in handler are suitable for the requirements of most users.
Conclusion
In this article, I tried to describe the main processes and objects of Oracle Streams, which in my opinion may be of interest to someone. I did not go into details, but described everything superficially. More details can always be found in the official documentation. The main thing is to know what to read.
Source: https://habr.com/ru/post/123569/
All Articles