How to achieve separation of reading and writing in Oracle

Methods to achieve read-write separation: 1. Use Oracle's own components, including asynchronous transmission supported by physical mode and synchronous transmission supported by logical mode; 2. Use third-party components, including Shareplex, RealSync and DDS , and DSG RealSync synchronization software.

The operating environment of this tutorial: Windows 10 system, Oracle 11g version, Dell G3 computer.

How oracle implements read-write separation

There are many ways to implement Oracle read-write separation:

1 Use Oracle’s own components

DG Solution

The DG solution is also called the ADG solution, and its full English name is Physical Standby (Active DataGuard). Supports recovery and read-only parallelism, but since it is not a logical application mechanism for logs, it is most limited in the scenario of read-write separation. The logfiles of the production machine are transferred to the disaster recovery machine, and the data mirroring capability is guaranteed through Redo Apply technology. Physically Provides consistent mirroring with the production database at the data block level, also called physical mode. Physical mode supports asynchronous transmission, but the disaster recovery machine is in recovery state and is unavailable;

Logical Standby

Restore the received log files through SQL Apply (i.e. Log Miner) technology into a SQL statement and executed on the logical backup database to achieve data consistency, also called the logical method. The logical mode only supports synchronous transmission, but the disaster recovery machine can be in the read-only state

Streams

to copy data to another database in real time for reading. The most flexible, but the least stable.

2 Choose commercial third-party products

The old Shareplex, the local DSG company’s RealSync and Jiuqiao Company’s DDS, or the upstart Oracle GoldenGate, are all is an optional target.

With the acquisition and promotion of GoldenGate by Oracle, I personally believe that GoldenGate will become popular in disaster recovery, data distribution and synchronization.

DSG RealSync synchronization du software implementation plan:

Extended knowledge:

The focus of reading and writing separation In fact, it is data synchronization. There are many technologies that can achieve real-time data synchronization, based on OS layer (such as VERITAS VVR), based on storage replication (most mid-to-high-end storage supports it), based on application distribution or based on database layer technology. Because data synchronization may not be a single DB entire database synchronization, it may involve issues such as business data selection and multi-source integration. Therefore, OS replication and storage replication are not suitable as the first choice technology for read-write separation in most cases.

Log-based Oracle replication technology can be implemented by Oracle's own components, and there are also mature commercial software. Whether to choose commercial independent products or Oracle's own component functions depends on many factors. For example, the team's corresponding technical operation and maintenance capabilities, project investment costs, and the load level of the business system, etc.

Recommended tutorial: "Oracle Video Tutorial"

The above is the detailed content of How to achieve separation of reading and writing in Oracle. For more information, please follow other related articles on the PHP Chinese website!