Discussion on Oracle database set replication method
- 2020-05-30 21:14:05
- OfStack
The ORACLE tutorial you are looking at is :Oracle database set replication method. preface
The increasing demand of distributed application requires the realization of better distributed software environment, which continuously promotes the progress of distributed technology. Oracle data replication is a technique for creating a distributed data environment by copying data from different physical sites. It is different from a distributed database, where each data object is available to all sites, but a specific data object exists in only one specific site. Data replication implements that all sites have available copies of the same data object.
In a typical distributed business application, it is often necessary to backup the data of each region to the database of the headquarters. One aspect can be used as a backup method, and the other aspect can also facilitate the comprehensive statistics in the headquarters application. This is a simple application of Oracle data replication, and this article will show you how to implement Oracle data replication with one such example.
As a matter of fact, the headquarters of A company is in Beijing, with three business offices located in Shanghai (ORACLE.SHANGHAI.COM), hangzhou (ORACLE.HANGZHOU.COM) and wuhan (ORACLE).
WUHAN COM). The software systems of the three business departments are the same and the database structure is the same. It is now necessary to back up all the data from the three business departments to the headquarters database.
The preparatory work
There are a lot of things that need to be prepared before replication. Of course, the most basic is that the network must be unblocked. After that, we need to collect some basic information about the replication environment:
1. The number of database sites that need to be replicated
2. Oracle version number for each site
3. The size of each database that needs to be replicated
4. The character set used by each database
5. The schema name used for each data to be replicated
After collecting the environment information, you can start to build the centralized database of headquarters. The centralized database requires the version higher than the version of all the main battle points, and it is better to use the same character set for all the databases. After the library is built, one table space is built for the backup data of each main site. The table space is larger than the amount of data to be copied, and the reserved future development space depends on the actual situation.
Establish a scheme for the corresponding replicated data of each primary site. If the scheme name used by each primary site is different, establish a scheme with the same name at the centralized database site. Otherwise, create the corresponding scheme name for the replicated data of each primary site. The actual situation is the latter. The databases of the business departments all use the scheme name of Oracle. Here we set up three counterparts
Case: SHORACL, HZORACL and WHORACL. All databases have a version of 9i.
The basic concept
Before copying, explain a few concepts in copying:
1. Primary site (Mater Site) : the site that provides the data source during the replication process. The Shanghai database site shown above.
2. Materialized view site (Materialized View Site) : the target site in the materialized view replication. The Beijing database site shown above.
3. Multi-principal site replication (Multimaster Replication) : sites in the replication environment are all primary sites and have the same administrative rights over the replicated database objects.
4. Materialized view replication (Materialized View Replication) : 1 principal site provides source replication objects, and 1 materialized view site copies primary site data.
5. Materialized view (Materialized View) : create a corresponding table for each replicated table or view at the materialized view site to save the corresponding data, which can only be added, deleted or modified through the replication mechanism of Oracle.
6. Fast refresh, full refresh, and forced refresh: three refresh modes in the replication process. Fast refresh only copies the changed part of the source data object; Copy the source data object 1 time for full refresh; A forced refresh is a compromise for the database, and a full refresh is used if a quick refresh fails.
7. Principal group (Master Group) : a collection of replicated source data objects in the principal site.
8. Materialized view group (Materialized View Site) : a collection of replicated objects in the materialized view site.
9. Materialized view logging (Materialized View Log) : a table in the materialized view replication that logs the operation of the principal source data object using a quick refresh.
Synchronous replication and asynchronous replication are not explained. This example USES asynchronous replication once a day.
replicate
Configure the local service names: Shanghai site: SH, hangzhou site: HZ, wuhan site: WH, Beijing site: BJ, enter the unlogged sqlplus, let's start copying!
1. Set up the primary site.
Here take Shanghai main site setup as an example.
1. Connect to the primary site, create and grant permissions to a replication administrator, who is the user who manages the entire replication environment and creates replication objects. Only data administrators can set up body groups and materialized view groups.
The next two grant statements allow the replication administrator to create a materialized view log for any table. If you want to change the user to use the view manager, you need the following command:
2. Register the propagator, which pushes the delayed transaction queue of the principal site into another principal site or materialized view site.
3. Schedule a cleanup job that periodically clears the delayed transaction queue and pushes the delayed transaction into another principal site or materialized view site by the propagator. Change users first:
next_date: the next execution date, sysdate means immediately.
interval: interval period, sysdate + 1 means interval of 1 day, sysdate+ 1/24 means interval of 1 hour
delay_seconds: the delay time to stop the secondary cleanup operation when there is no delay event in the delay queue.
4. Create a replication proxy for the materialized view site. Create a replication agent user and grant permissions to the view recipient. A replication agent is a user that the replication recipient connects to the principal site
5. Create the principal group.
The increasing demand of distributed application requires the realization of better distributed software environment, which continuously promotes the progress of distributed technology. Oracle data replication is a technique for creating a distributed data environment by copying data from different physical sites. It is different from a distributed database, where each data object is available to all sites, but a specific data object exists in only one specific site. Data replication implements that all sites have available copies of the same data object.
In a typical distributed business application, it is often necessary to backup the data of each region to the database of the headquarters. One aspect can be used as a backup method, and the other aspect can also facilitate the comprehensive statistics in the headquarters application. This is a simple application of Oracle data replication, and this article will show you how to implement Oracle data replication with one such example.
As a matter of fact, the headquarters of A company is in Beijing, with three business offices located in Shanghai (ORACLE.SHANGHAI.COM), hangzhou (ORACLE.HANGZHOU.COM) and wuhan (ORACLE).
WUHAN COM). The software systems of the three business departments are the same and the database structure is the same. It is now necessary to back up all the data from the three business departments to the headquarters database.
The preparatory work
There are a lot of things that need to be prepared before replication. Of course, the most basic is that the network must be unblocked. After that, we need to collect some basic information about the replication environment:
1. The number of database sites that need to be replicated
2. Oracle version number for each site
3. The size of each database that needs to be replicated
4. The character set used by each database
5. The schema name used for each data to be replicated
After collecting the environment information, you can start to build the centralized database of headquarters. The centralized database requires the version higher than the version of all the main battle points, and it is better to use the same character set for all the databases. After the library is built, one table space is built for the backup data of each main site. The table space is larger than the amount of data to be copied, and the reserved future development space depends on the actual situation.
Establish a scheme for the corresponding replicated data of each primary site. If the scheme name used by each primary site is different, establish a scheme with the same name at the centralized database site. Otherwise, create the corresponding scheme name for the replicated data of each primary site. The actual situation is the latter. The databases of the business departments all use the scheme name of Oracle. Here we set up three counterparts
Case: SHORACL, HZORACL and WHORACL. All databases have a version of 9i.
The basic concept
Before copying, explain a few concepts in copying:
1. Primary site (Mater Site) : the site that provides the data source during the replication process. The Shanghai database site shown above.
2. Materialized view site (Materialized View Site) : the target site in the materialized view replication. The Beijing database site shown above.
3. Multi-principal site replication (Multimaster Replication) : sites in the replication environment are all primary sites and have the same administrative rights over the replicated database objects.
4. Materialized view replication (Materialized View Replication) : 1 principal site provides source replication objects, and 1 materialized view site copies primary site data.
5. Materialized view (Materialized View) : create a corresponding table for each replicated table or view at the materialized view site to save the corresponding data, which can only be added, deleted or modified through the replication mechanism of Oracle.
6. Fast refresh, full refresh, and forced refresh: three refresh modes in the replication process. Fast refresh only copies the changed part of the source data object; Copy the source data object 1 time for full refresh; A forced refresh is a compromise for the database, and a full refresh is used if a quick refresh fails.
7. Principal group (Master Group) : a collection of replicated source data objects in the principal site.
8. Materialized view group (Materialized View Site) : a collection of replicated objects in the materialized view site.
9. Materialized view logging (Materialized View Log) : a table in the materialized view replication that logs the operation of the principal source data object using a quick refresh.
Synchronous replication and asynchronous replication are not explained. This example USES asynchronous replication once a day.
replicate
Configure the local service names: Shanghai site: SH, hangzhou site: HZ, wuhan site: WH, Beijing site: BJ, enter the unlogged sqlplus, let's start copying!
1. Set up the primary site.
Here take Shanghai main site setup as an example.
1. Connect to the primary site, create and grant permissions to a replication administrator, who is the user who manages the entire replication environment and creates replication objects. Only data administrators can set up body groups and materialized view groups.
The next two grant statements allow the replication administrator to create a materialized view log for any table. If you want to change the user to use the view manager, you need the following command:
2. Register the propagator, which pushes the delayed transaction queue of the principal site into another principal site or materialized view site.
3. Schedule a cleanup job that periodically clears the delayed transaction queue and pushes the delayed transaction into another principal site or materialized view site by the propagator. Change users first:
next_date: the next execution date, sysdate means immediately.
interval: interval period, sysdate + 1 means interval of 1 day, sysdate+ 1/24 means interval of 1 hour
delay_seconds: the delay time to stop the secondary cleanup operation when there is no delay event in the delay queue.
4. Create a replication proxy for the materialized view site. Create a replication agent user and grant permissions to the view recipient. A replication agent is a user that the replication recipient connects to the principal site
5. Create the principal group.