Use Oracle's Advanced Replication to synchronize data between the Intranet and the Internet

This article briefly introduces the advanced replication function of Oracle, and discusses how to use advanced replication to achieve data unification between the Intranet and the Internet through an actual project, finally, the configuration script is provided for your reference.

1. Basic Concepts

Oracle
Oracle is a large-scale relational database based on advanced Structured Query Language (SQL). It is a set of regular data manipulation in a language that facilitates logical management. It is one of the databases in the client/server architecture.

Advanced Replication
What is replication? Simply put, replication is the process of copying data in a distributed database environment consisting of two or more database systems.
Advanced Replication is the process of copying and maintaining database objects in multiple databases that constitute a distributed database system. Oracle Advanced Replication allows applications to update any copies of a database and automatically pass these changes to other databases while ensuring consistency and data integrity of global transaction processing.
Synchronous replication: the copied data is consistent at any time on any replication node. If the replication data of any node in the replication environment is updated, this change is immediately reflected to all other replication nodes. This technology applies to commercial applications with high real-time requirements.
During asynchronous replication, data on all replication nodes is not synchronized for a certain period of time. If the replication data of one of the nodes in the replication environment is updated, this change will be propagated and applied to all other replication nodes in different transactions. The interval between these different transactions can be several seconds, several minutes, several hours, or several days later. Data between replication nodes is temporarily not synchronized, but the propagation will eventually ensure data consistency between all replication nodes.

2. Project Information

Requirement Description
This is an internal/external structure approval system.
The Internet has a Web (+ appserver) and a DB (oracle9.2, dual-nic), which is responsible for receiving the application and feedback the approval results.
The Intranet has a Web (+ appserver) and a DB (oracle9.2, dual-nic). It is responsible for receiving the application and feedback of the approval results, and processing the approval application from the Internet through the approval process.
As shown in the preceding figure, Intranet functions include Internet functions. However, CA authentication is required for Internet reporting, and Intranet authentication is not required.
According to national confidentiality regulations, the Intranet and Internet of the government system must be physically isolated. Therefore, the applications received by the Internet cannot be immediately reflected in the Intranet. Likewise, the Intranet processing results cannot be quickly fed back to the Internet.

Technical select'
We chose Oracle asynchronous manual replication to complete the following functions:
1. Copy the new external network application data to the Intranet;
2. Report the reporting result from the Intranet to the Internet.

Operation implementation
During normal working hours, the Intranet and Internet cannot be paused, while the Intranet and Internet cannot be physically connected. Therefore, the maintenance period is set to (or other time periods) every night. During this period, the Internet and Intranet are stopped, and the system administrator disconnects all network cables connected to the DB, connect two databases with a direct connection network cable. Refresh the records manually on the outer network end through the operation interface provided by Oracle.

3. Specific implementation steps

The following are the technical parameters used in the script. The internal and external tables have the same structure and have primary keys.

Intranet and Internet
IP 192.168.0.50 192.168.0.100
Sid oranei orawai
Table lawtable
Register
Userinfo
......
68 table lawtable
Register
Two tables in total
Login Name/password holen/holen

The following is the SQL script we used to install at the client.

Step 1: configure the Intranet, that is, the master
-- Version 2.0
-- Revised by: Chen guang holen@263.net
-- Time:
-- Intranet as master
-- Create an Intranet user holen

Conn system/password @ oranei

Create user "holen" Profile "default" identified by "holen ";
Grant "Connect" to "holen ";
Grant "dba" to "holen ";
Grant "resource" to "holen ";
-- Import the intranet database backup, which is completed in DOS (Table lawtable, register, and other tables)

Step 2: configure the Intranet, that is, the master end (continued)
-- The table to be copied (synchronized) is lawtable and register under the holen user.

-- Create a repadmin user to manage the replication Environment
Create user repadmin identified by repadmin;
Alter user repadmin default tablespace users;
Alter user repadmin temporary tablespace temp;
Grant connect, resource to repadmin;

-- Grant repadmin user permissions to manage any subject group on the current site
Execute dbms_repcat_admin.grant_admin_any_schema ('repadmin ');

-- Grant the repadmin user permission to create a snapshot logs for any table
Grant comment any table to repadmin;
Grant lock any table to repadmin;

-- Specify the repadmin user as propagator and grant the permission to execute any procedure.
Execute dbms_defer_sys.register_propagator ('repadmin ');
Grant execute any procedure to repadmin;

-- Grant proxy snapshot administration permission to repadmin. If list_of_gnames is null, all object groups can be managed.
Begin
Dbms_repcat_admin.register_user_repgroup (
Username => 'repadmin ',
Privilege_type => 'proxy _ snapadmin ',
List_of_gnames => null );
End;
/

-- Assign the 'Explorer' permission to repadmin.
Begin
Dbms_repcat_admin.register_user_repgroup (
Username => 'repadmin ',
Privilege_type => 'Explorer ',
List_of_gnames => null );
End;
/

Grant select any table to repadmin;

-- Create a principal group on oranei. The principal group is named holen_master and a table is added to the principal group.
-- Create a copy subject group
Begin
Dbms_repcat.create_master_repgroup (
Gname => '"holen_master "',
Qualifier => '',
Group_comment => '');
End;
/

Connect repadmin/repadmin;

-- Add the table object lawtable to the replication group
Begin
Dbms_repcat.create_master_repobject (
Gname => '"holen_master "',
Type => 'table ',
Oname => '"lawtable "',
Sname => '"holen "',
Copy_rows => true,
Use_existing_object => true );
End;
/

-- Create the corresponding snapshot log
Create snapshot log
On "holen". "lawtable"
Tablespace "system"
With primary key
Excluding New values;

-- Generate replication support
Begin
Dbms_repcat.generate_replication_support (
Sname => '"holen "',
Oname => '"lawtable "',
Type => 'table ',
Min_communication => true,
Generate_80_compatible => false );
End;
/

-- Add the table object register to the replication group
Begin
Dbms_repcat.create_master_repobject (
Gname => '"holen_master "',
Type => 'table ',
Oname => '"register "',
Sname => '"holen "',
Copy_rows => true,
Use_existing_object => true );
End;
/

Create snapshot log
On "holen". "register"
Tablespace "system"
With primary key
Excluding New values;

Begin
Dbms_repcat.generate_replication_support (
Sname => '"holen "',
Oname => '"register "',
Type => 'table ',
Min_communication => true,
Generate_80_compatible => false );
End;
/

-- Master end configuration is complete

Step 3: configure the Internet, that is, the snapshot end.
-- Use the Internet as a snapshot
-- Create an Internet user holen

Conn system/password @ orawai

-- Create a common user
Create user "holen" Profile "default" identified by "holen ";
Grant "Connect" to "holen ";
Grant "dba" to "holen ";
Grant "resource" to "holen ";

-- Create a repadmin user to manage the snapshot copy Environment
Create user repadmin identified by repadmin;
Alter user repadmin default tablespace users;
Alter user repadmin temporary tablespace temp;
Grant connect, resource to repadmin;

-- Grant repadmin user permissions to manage any subject group on the current site
Execute dbms_repcat_admin.grant_admin_any_schema ('repadmin ');

-- Grant the repadmin user permission to create a snapshot logs for any table
Grant comment any table to repadmin;
Grant lock any table to repadmin;

-- Specify the repadmin user as propagator and grant the permission to execute any procedure.
Execute dbms_defer_sys.register_propagator ('repadmin ');
Grant execute any procedure to repadmin;

-- Grant the repadmin user the permission to create snapshots.
Grant create any snapshot to repadmin;
Grant alter any snapshot to repadmin;

-- Establish a connection with the intranet on the Internet
-- Add oranei to the tree on the outer network side
Create public database link oranei connect to repadmin identified by repadmin using 'oranei ';

-- Create a refresh group holen_refresh on the Internet
Begin
Dbms_refresh.make (
Name => '"holen". "holen_refresh "',
List => '',
Next_date => sysdate,
Interval => '/* 1: mins */sysdate + 1/(60*24 )',
Implicit_destroy => false,
LAX => false,
Job => 0,
Rollback_seg => null,
Push_deferred_rpc => true,
Refresh_after_errors => true,
Purge_option => null,
Parallelism => null,
Heap_size => null );
End;
/

-- Create a snapshot group on the Internet
Begin
Dbms_repcat.create_snapshot_repgroup (
Gname => '"holen_master "',
Master => 'oranei. Us. Oracle. com ',
Propagation_mode => 'asynchronous ');
End;
/

-- Create a snapshot. the snapshot must be created under the user to which the table belongs. In this example, log in with the holen user.
Connect holen/holen;

-- Create a snapshot lawtable
Create snapshot "holen". "lawtable"
Refresh fast for update
As select * from "holen". "lawtable" @ oranei. Us. Oracle. com C
/

-- Add a snapshot to the refresh Group
Begin
Dbms_refresh.add (
Name => '"holen". "holen_refresh "',
List => '"holen". "lawtable "',
LAX => true );
End;
/

-- Add a snapshot to a snapshot Group
Begin
Dbms_repcat.create_snapshot_repobject (
Gname => '"holen_master "',
Sname => '"holen "',
Oname => '"lawtable "',
Type => 'snapshot ',
Min_communication => false );
End;
/

-- Create a snapshot register
Create snapshot "holen". "register"
Refresh fast for update
As select * from "holen". "register" @ oranei. Us. Oracle. com C
/

-- Add a snapshot to the refresh Group
Begin
Dbms_refresh.add (
Name => '"holen". "holen_refresh "',
List => '"holen". "register "',
LAX => true );
End;
/

-- Add a snapshot to a snapshot Group
Begin
Dbms_repcat.create_snapshot_repobject (
Gname => '"holen_master "',
Sname => '"holen "',
Oname => '"register "',
Type => 'snapshot ',
Min_communication => false );
End;
/

-- Snapshot is configured.

Step 4: configure the Intranet
-- After the Internet is configured, run the following statement on the Intranet:

Begin
Dbms_repcat.resume_master_activity (
Gname => '"holen_master "');
End;
/

4. Postscript

The Advanced Replication function of Oracle is very powerful, and it can implement data synchronization and asynchronous replication between multiple points.
The submission, approval, and record filing of Intranet and Internet structures are becoming increasingly popular. If the internal and external databases are commercial databases of the same type, the database itself provides the replication function, if the Intranet and Intranet are different types of databases, for example, if the Intranet is sqlserver and the Internet is Oracle, the "synchronization Machine" middleware is generally used and can be called in the program.