Oracle Distributed Data Solution

Oracle-related Distributed Data Solutions
This solution may gradually die out in the cloud computing and SOA architecture, but it is still quite practical. This article briefly introduces common data synchronization solutions because Advanced Replication solutions are being used, so it focuses on oracle's advanced replication.
I. Common Distributed Data Solutions
If the database is an oracle Distributed System with scattered data, you can often see the following distribution solutions:
Non-real-time, batch Synchronization
1. ETL tools or self-developed Interfaces
Generally, the FTP service is used for data files in a specific format.
Data warehouse or data mart can use ETL tools for data integration. Generally, the system is implemented by self-written interface programs. The general idea is to import data to the interface table. After cleaning, enter the formal table.
There are many import methods. If the data volume is large, you can use sqlLoder. If the data volume is small, you can import data row by row and clean it at the same time. In my experience, it is better to put the cleansing logic in the database.
It may also be an XML file obtained through http. In this case, you need to customize the XML parsing program and read the CDATA section. Advanced Programming Languages have powerful and simple functions.
Real-time and small batch Synchronization
1. provided by a third party
For key systems, middleware can be used to ensure data security and stability. For example, BEA's Tuxedo is good.
2. self-developed
If they are all unix machines, you can use socket programming to compress data into packages, ensuring stability. It is often written into XML format, and the data file has a strong self-describing capability.
These two solutions can be seen in telecommunications and other systems and are relatively reliable.
3. Provided by oracle itself
3.1 Real-Time Table Interface
The use of db_link and stored procedures, combined with jobs, is flexible and powerful.
3.2 oracle Advanced Replication
If you need to ensure bidirectional real-time data synchronization, And the replication process is complex, there are multiple replication nodes, you can use Oracle's advanced replication, this solution was used in the real estate bureau project of Jinan website construction last month. Although this scheme is not suitable for this project, we have continued this synchronization scheme for the first-stage system's needs due to historical reasons. This article mainly describes the process of Advanced Replication:

Ii. Basic concepts of Advanced Replication
1. The same data will exist in multiple nodes. This environment is more suitable for advanced replication.
2. You can use advance replication for ORACLE instances of different versions and operating systems.
3. Terms
Replication object: copying objects, including tables, indexes, and stored procedures
Replication group: a replication group that copies a set of objects. A group contains multiple objects. One object can only belong to one group.
Replication site: replication site
4. multimaster replication: features: full table replication, which can be performed after each transaction
Replication Mode
Asynchronous replication: replication to other masters within the push time, also known as store and forward data replication
Synchronous replication: Update to another master immediately, that is, real time data replication
Procedual replication: the package generates a wrapper. Data changes are completed through the stored procedure. procedure in a master node is investigated. wapper ensures that procedure in other sites is also called, reduces network load when there is a large amount of data
Asynchronous call Process
DML operations (or wappers) generate a delayed rpc by the internal trigger and put it into the delayed transaction queue. One site has a delayed transaction queue, and one queue is shared by multiple groups.
When an error occurs, the transaction is put into the error queue. After the transaction is completed successfully, the purge job deletes the transaction from the transaction queue of the source site.
Synchronous replication process
DML operations are captured immediately, and transactions are executed immediately. If any site fails or the transaction is rolled back, the local row is locked first and the remote row is locked using the after row trigger, oracle is unlocked only after all sites are submitted, which is highly dependent on system and network availability.
3. Steps for creating an advanced copy:
There are a lot of materials on the Internet, and they are also very detailed. Here is a classic of eygle. You can find it after searching it. I will simply talk about it and describe the solution to the problem I encountered.
1. Adjust the oracle environment, create a repadmin user, and grant permissions for usage
2. Create db_link
3. Create a replication object in the replication group
You can use a graphical tool or a manual script to create a table. Because there are many tables, and there is another setup in the formal deployment, I use the manual script method here.
-- Create a replication group:
DBMS_REPCAT.CREATE_MASTER_REPGROUP (
Gname => '"***"',
Qualifier => '',
Group_comment => '');

-- Add a copy object to the copy group:
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
Gname => '"**"',
Type => 'table ',
Oname => '"TEST "',
Sname => '"LFGISTEST "',
Copy_rows => false,
Use_existing_object => TRUE );

-- Supports copying objects:
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
Sname => '"LFGISTEST "',
Oname => '"TEST "',
Type => 'table ');

-- Add a master copy node:
DBMS_REPCAT.ADD_MASTER_DATABASE (
Gname => '"***"',
Master => '***',
Use_existing_objects => TRUE,
Copy_rows => false,
Propagation_mode => 'asynchronous ');
 
-- Start replication on the subject-defined site:
DBMS_REPCAT.RESUME_MASTER_ACTIVITY (gname => '"***"', true );
Errors encountered
1. db_link cannot be used after it is created, and the name starting with db_link is incorrect.
Solution: If global_names = true; the db_link name must be consistent with global_name. The modification succeeds.
2. An error is reported when you add a master copy node:
The ORA-23357: the propagator does not exist
ORA-06512: at "SYS. DBMS_REPCAT_CACHE", line 76
ORA-06512: at "SYS. DBMS_REPCAT_MAS", line 2105
ORA-06512: at "SYS. DBMS_REPCAT", line 146
ORA-06512: at line 2
Possible causes:
1. global_name uses oracle reserved words, refer to: http://oracle-tech.blogspot.com/2008/08/ora-23375-when-adding-new-master-site.html
Change Internet (61) global_name to OUT. WWKJ. LZFC2
Alter database rename global_name to OUT. WWKJ. LZFC2;
2. The REPADMIN user has insufficient permissions. Check the granted statements to ensure that the permissions are in place.
3. db_link must use REPADMIN to connect to the peer database. This is why I reported an error.
4. It is said that the domains of the two databases must be the same
5. the user and schema of the two machines should be consistent
Solution: Hehe, very careless, and did not use repadmin to run the script
3. When a replication object is added to the replication group, the error 23312 is returned. The current site is not masterdef.
Oracle Error: ORA-23312: not the masterdef according to string
Cause: The group name is null, the group name is misspelled, the invocation or given database is not the masterdef, or one of the masters does not believe the invocation database is the masterdef.
Action: If the given group name and masterdef were both correct, connect to the masterdef and retry the request, or relocate the masterdef at the (errant) databases using relocate_masterdef.