Discussion on Oracle database set replication methods
Discussion on Oracle database set replication methods
The ORACLE tutorial is: A Brief Discussion on the Oracle database set replication method. Preface
The increasing demand for distributed applications requires a better distributed software environment, which constantly promotes the development of distributed technology. Oracle Data replication is a technology that implements a distributed data environment. It creates a distributed data environment by copying data on different physical sites. It is different from distributed databases. In distributed databases, although each data object is available to all sites, a specific data object only exists in a specific site. Data Replication allows all sites to copy the same data object.
In a typical distributed commercial application, you often need to back up data from different regions to the database at the Headquarters. On the one hand, it can be used as a backup method, and on the other hand, it facilitates the comprehensive statistics of the Headquarters application. This is a simple application of Oracle Data Replication. This article uses this example to describe how to implement Oracle Data replication.
The actual situation is that Company A is headquartered in Beijing and has three business departments located in SHANGHAI (ORACLE. SHANGHAI. COM), HANGZHOU (ORACLE. HANGZHOU. COM), and Wuhan (ORACLE.
WUHAN. COM ). The three business departments have the same software system and database structure. Now we need to back up all the data from the three sales departments to the database at the headquarters.
Preparations
There are a lot of things to be prepared before replication, of course, the most basic thing is that the network must be smooth, and then you need to collect some basic information about the replication environment:
1. Number of database sites to be copied
2. Oracle version number of each site
3. Size of each database to be copied
4. character set used by each database
5. The solution name for each data to be copied
After collecting environment information, you can start to build a centralized database at the Headquarters. a centralized database requires a version later than the version of all main battle sites. It is recommended that all databases use the same character set. After the database is created, create a tablespace for the backup data of each master site. The tablespace is larger than the data volume to be copied. The reserved development space depends on the actual situation.
Create a scheme for the corresponding replication data of each primary site. If the scheme names of each primary site are different, create a scheme with the same name on the centralized database site. Otherwise, a corresponding solution name is created for the copied data of each primary site. The actual situation is that the latter, the databases of various business departments are all using the solution name of Oracle. Here we create three corresponding parties
Case: shyml, hzyml, and whyml. All database versions are 9i.
Basic Concepts
Before copying, explain the following concepts:
1. Primary Site: the Site that provides data sources during the replication process. For example, the Shanghai database site.
2. Materialized View Site: the target Site in the Materialized View copy. For example, the Beijing database site.
3. Multi-master Replication: all sites in the Replication environment are master sites and have the same management permissions on the copied database objects.
4. Materialized View Replication: A subject site provides source Replication objects, and a Materialized View site copies data of the master site.
5. materialized View: Creates a table for each duplicate table or View on the Materialized View site to save the corresponding data, this table can only be added, deleted, and modified using the Oracle replication mechanism.
6. fast refresh, full refresh, and forced Refresh: Three refresh methods during the replication process. Quick refresh only copies the changed part of the source data object. Full refresh copies the source data object once each time. Forced refresh is a compromise solution for the database. If the quick refresh fails, use full refresh.
7. Master Group: a collection of source data objects copied on the Master site.
8. Materialized View Site: a set of copying objects in the Materialized View Site.
9. Materialized View Log (Materialized View Log): The table that records the operation logs of the subject Source Data Objects During Quick refresh in the Materialized View copy.
Synchronous replication and asynchronous replication are not explained. In this example, asynchronous replication is performed once a day.
Copy
Configure the local service names: Shanghai site: SH, Hangzhou site: HZ, Wuhan site: WH, Beijing site: BJ. Go to sqlplus without logon. Let's Start copying!
1. Set the primary site.
Here, we use the main site in Shanghai as an example.
1. Connect to the primary site, create a replication administrator, and grant corresponding permissions. The replication administrator is a user who manages the entire replication environment and creates a replication object. Only the data administrator can create a subject group and a materialized view group.
The following two grant statements allow the replication administrator to create a materialized view log for any table. If you want to change the value, you can use the view manager. You also need the following command:
2. register the propagation party. The propagation party pushes the delayed transaction queue of the main site to another main site or the materialized view site.
3. Scheduled cleanup job. The job regularly clears the delayed transaction queue and pushes the delayed transaction to another main site or materialized view site. Change the user first:
Next_date: The next execution date. sysdate indicates immediate execution.
Interval: interval. sysdate + 1 indicates the interval of one day, and sysdate + 1/24 indicates the interval of one hour.
Delay_seconds: the delay time for stopping the cleanup operation when there is no delay event in the delayed queue.
4. Create a replication proxy for the materialized view site. Create a copy proxy user and grant the view receiver permissions. The replication proxy is the user who copies the receiver to connect to the main site.
5. Create a subject group.
