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.

connect system/passwd@SH
create user repadmin identified by repadmin;
begin
dbms_repcat_admin.grant_admin_any_schema(
username=>'repadmin');
end;
/
grant comment any table to REPADMIN;
grant lock any table to REPADMIN;
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:

grant select any dictionary to REPADMIN;
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.

begin
dbms_defer_sys.register_purpagator(username=>'repadmin');
end;
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:

disconnect;
connect repadmin/repadmin@SH;
begin
dbms_defer_sys.schedule_purge(
next_date=>sysdate,interval=>'sysdate + 1',delay_seconds=>0);
end;
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.

disconnect;
connect system/passwd@SH;
create user proxy_bjoracle identified by proxy_bjoracle;
begin
dbms_repcat_admin.register_user_repgroup(
user_name=>'proxy_bjoracle,
privilege_type => 'proxy_snapadmin',list_of_gnames => NULL);
end;
/
grant select_catalog_role to proxy_bjoracle;
5. Create a subject group.

disconnect;
connect repadmin/repadmin@SH;
begin
dbms_repcat.create_master_repgroup(gname=>'sh_rep');
end;
/
6. Add a copy object to the subject group.

A) add a table:

begin
dbms_repcat.create_master_repobject(
gname=>'sh_rep',
type=>'TABLE',
oname=>' CREDIT_CARD'
sname=>'SHORACL'
use_existing_object=>TRUE,
copy_rows=>TRUE);
end;

[1] [2] [3] Next page

The ORACLE Tutorial we are reading is a brief introduction to the Oracle database set replication method.>
B) add an index

begin
dbms_repcat.create_master_repobject(
gname=>'sh_rep',
type=>'INDEX',
oname=>' INDEX_CREDIT_CARD'
sname=>'SHORACL'
use_existing_object=>TRUE,
copy_rows=>FALSE);
end;
/

[NextPage]

7. If the added table does not have a primary key, you need to set a column or column set that can replace the primary key.

begin
dbms_repcat.set_columns(
sname => 'SHORACL',
oname => ' CREDIT_CARD ',
column_list => ' CREDIT_CARD_ID');
end;
/
8. Data Objects in the subject group must be copied before they can be copied. This method creates a trigger, package, or stored procedure for replication:

begin
dbms_repcat.generate_replication_support(
sname=>'SHORACL',
oname=>' CREDIT_CARD',
type=>'TABLE',
min_communication=>TRUE);
end;
/
9. Create a materialized view log for quick Refresh:

create materialized view log on SHORACL. CREDIT_CARD;
If there is no primary key, use the following statement:

create materialized view log on SHORACL. CREDIT_CARD with
rowid excluding new values;
10. Start replication:

begin
dbms_repcat.resume_master_activity(
name=>'sh_rep');
end;
/

2. Set up the materialized view site.

1. Create a replication administrator and grant the following permissions:

disconnect;
connect system/passwd@BJ;
create user mvadmin identified by
mvadmin;
begin
dbms_repcat_admin.
grant_admin_any_schema(username=> 'mvadmin');
end;
/
grant comment any table to mvadmin;
grant lock any table to mvadmin;
grant select any dictionary to mvadmin;
2. Registering the publisher:

begin
dbms_defer_sys.register_propagator(
username => 'mvadmin');
end;
/
3. Public database connection. Three database connections are required for each replication. The global name of the specified database for public database connection:

create public database link ORACLSH using 'oracle.shanghai.
com';
The Using clause is followed by the global database name or connection string.

create public database link ORACLSH using '(description=
(address=(protocol=tcp)(host=127.0.0.1)(port=1521))
(connect_data=(service_name=oracl)))'
4. Create a scheduling job to clear the delayed transaction queue:

disconnect;
connect mvadmin/mvadmin@BJ;
begin
dbms_defer_sys.schedule_purge(
next_date => sysdate,
interval => '/*1:hr*/ sysdate + 1',
delay_seconds => 0,
rollback_segment => '');
end;
5. Create a database connection for the replication administrator mvadmin:

Create database link Using lsh connect to proxy_bjoracle
Identified by proxy_bjoralce
Connect to... Identified by... clause specifies which user is used to Connect to the remote database
6. Create a copy and Scheduled Database Connection job:

begin
dbms_defer_sys.schedule_push(
destination => 'ora92zjk',interval => '/*1:hr*/ sysdate + 1',
next_date => sysdate,stop_on_error => false,
delay_seconds => 0,parallelism => 0);
end;
/
7. Authorize shyml users (corresponding to shyml solution)

Create a materialized view for the corresponding permissions:

disconnect;
connect system/passwd@BJ;
grant alter session to crm;
grant create cluster to crm;
grant create database link to crm;
grant create sequence to crm;
grant create session to crm;
grant create synonym to crm;
grant create table to crm;
grant create view to crm;
grant create procedure to crm;
grant create trigger to crm;
grant unlimited tablespace to crm;
grant create type to crm;
grant create any snapshot to crm;
grant alter any snapshot to crm;
8. Create a database connection for the replication solution:

disconnect;
connect SHORACL/SHORACL@BJ;
create database link ORACLSH connect to ORACL identified
by ORACL;
The database connection of the replication solution and the database connection of the replication administrator must use the same name as the public database connection in the system user, during the scheduling connection, the global name or connection string of the database specified in the public database connection will be used.

9. Create a Materialized View:

Previous Page [1] [2] [3] Next page

The ORACLE tutorial is: A Brief Discussion on the Oracle database set replication method.disconnect;
connect mvadmin/mvadmin@BJ;
create materialized view SHORACL.CREDIT_CARD refresh fast
wit h pr imar y key as sele ct * from ORA CL.
CREDIT_CARD@ORACLSH;
@ Is followed by the database connection name. If the table does not have a primary key, use rowid to refresh

create materialized view SHORACL. CREDIT_CARD refresh
fast with rowid as select * from ORACL. CREDIT_CARD@ORACLSH;
10. Create a refresh group for multiple views:

begin
dbms_refresh.make (
name => 'mvadmin.sh_refresh',list => '',
next_date => sysdate,interval => 'sysdate + 1',
implicit_destroy => false,rollback_seg => '',
push_deferred_rpc => true,refresh_after_errors => false);
end;
11. Add a copy object to the refresh group:

begin
dbms_refresh.add (name => 'mvadmin.sh_refresh',list => 'SHORACL.CREDIT_CARD ',lax => true);
end;
/
3. Check the replication process.

1. Check whether enough jobs are generated in the sys. dba_jobs view.

After the preceding steps, three jobs should be cleared, scheduled, and refreshed, respectively, to check whether the view's what field contains the following content:

a) declare rc binary_integer; begin rc := sys.dbms_defer_sys.
purge( delay_seconds=>0); end;
b) declare rc binary_integer; begin rc := sys.dbms_defer_sys.
push(destination=>'ORACLSH', stop_on_error=>FALSE,
delay_seconds=>0, parallelism=>0); end;
c) dbms_refresh.refresh('"MVADMIN"."SH_REFRESH"');
If the Database Replication in Hangzhou and Wuhan is excluded from other system operation examples, there will be 7 jobs (if a refresh group is created for each copy), and there will always be only one job to be cleared. Each copy corresponds to a scheduling job, and each refresh group corresponds to a refresh job.

2. Check the job_queue_processes parameter to ensure that the parameter is not zero (the default value of the database is zero). If the parameter is zero, the system will not automatically refresh the copied data unless it is refreshed manually each time.

3. After the copy is executed, observe the failures field in the sys. dba_jobs view. If a replication error occurs during the refresh process, Oracle will automatically refresh again after 1 minute, and then refresh again after 2 minutes, 4 minutes, or 8 minutes after the failure .. then, refresh the job and wait until the number of failures reaches 16 or the interval exceeds the job setting interval. The job is marked as interrupted and will not be executed by Oracle. Run the change job to use the dbms_job package:

begin
dbms_job.run(job_no);
end;
/
Job_no is the Job field value and Job number of sys. dba_jobs.

Before re-executing a job interrupted due to an error, You need to manually locate the error point and correct it.

　　Summary

This article only uses the simplest features of Oracle Advanced Replication. Oracle Advanced Replication also provides updatable views and complex read-only materialized view replication, of course, not all of us need to use advanced complex and powerful functions. In specific applications, we also need to select appropriate replication technologies based on system functions and performance requirements.

Previous Page

Previous Page [1] [2] [3]