A brief discussion on Oracle database set replication Method _oracle

The Oracle tutorial you are looking at is: a brief discussion of Oracle database set replication methods.

Preface

The growing demand for distributed applications requires a better distributed software environment, which drives the development of distributed technology. Oracle data replication is a technology for implementing distributed data environments, which can be used to create distributed data environments by copying data from different physical sites. Unlike a distributed database, in a distributed database, although each data object is also available to all sites, a particular data object exists only in a particular site. Data replication enables all sites to have copies of the same data objects available.

In a typical distributed business application, it is often necessary to back up the data of the region to the database at Headquarters, on the one hand, it can be used as a backup, on the other hand, it facilitates the comprehensive statistics in the headquarter application. This is a simple application in Oracle data replication, and this article will describe how to implement Oracle data replication with an example.

The reality is that company A is headquartered in Beijing, with three sales offices located in Shanghai (ORACLE, respectively). shanghai.com), Hangzhou (ORACLE. hangzhou.com) and Wuhan (ORACLE.
wuhan.com). Three business departments of the same software system, database structure is the same. It is now necessary to back up the data in all three sales offices to the database at Headquarters.

　　 preparatory work

There are a lot of things you need to prepare before replicating, and of course the basics are that the network must be unblocked, and then you need to collect some basic information about the replication environment:

1. 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 scenario name used for each data that needs to be replicated

After gathering the environment information, you can start to build a centralized database at Headquarters, the centralized database requires a version higher than all the main points of the version, preferably all databases are in the same character set. After building the library for each main site backup data to build a table space, table space is larger than the need to replicate the amount of data, as to reserve the future development of space depending on the actual situation.

Create a scenario for each primary site's corresponding replication data, and if each primary site uses a different scenario name, create a corresponding scenario with the same name in the centralized database site. Otherwise, the corresponding schema name is established for each primary site's replicated data. The actual situation is the latter, the database of each sales department is using Oracle's scheme name, here we establish three counterparts
Case: Shoracl, Hzoracl and Whoracl. All databases have a version of 9i.

　　 Basic Concepts

Before you copy, explain some of the concepts in replication:

1. Primary site (Mater site): The site that provides the data source during the replication process. such as the Shanghai database site in the figure above.

2. The materialized view site (materialized view site): The target site in the materialized view replication. such as the Beijing database site in the above figure.

3. Multi-agent Site Replication (multimaster Replication): The sites in the replication environment are the primary sites that have the same administrative permissions on the replicated database objects.

4. Materialized view replication (materialized view Replication): A principal site provides source replication objects, and a materialized view site copies primary site data.

5. Materialized view: Materialized the materialized view site for each replicated table or view to create a corresponding table to save the corresponding data, the table can only be through the Oracle replication mechanism for the operation of data deletion.

6. Quick Refresh, full refresh, and force refresh: three ways to refresh during replication. Quick Refresh copies only the changes to the source data object; Full refresh copy the source data object every time; Force Refresh is a compromise of the database and use full refresh if the quick refresh fails.

7. Principal group: A collection of source data objects that are replicated in the principal site.

8. Materialized View group (materialized view site): A collection of replicated objects in a materialized view site.

9. Materialized view log (materialized view log): A table that records the action log of the principal source data object in a materialized view copy using a quick refresh.

Synchronous replication and asynchronous replication are not explained, and this example uses asynchronous replication once a day.

　　 for Replication

Configure a good local Service name: Shanghai site: SH, Hangzhou site: HZ, Wuhan site: WH, Beijing site: BJ, into the sqlplus without login, let us start copying!

A Set up the primary site.

Here the Shanghai main site is set up as an example.

1. Connect to the primary site, create a replication administrator, and grant the appropriate permissions, and the replication administrator is the user who manages the entire replication environment and creates the replicated objects. Only the data administrator can establish a body 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 enable replication administrators to establish a materialized view log for any table. If you want to change the user to use View Manager, you need the following command:

grant select any dictionary to REPADMIN;
2. Registers the disseminator, the propagator pushes the deferred transaction queue of the principal site into the other principal site or the materialized view site.

begin
dbms_defer_sys.register_purpagator(username=>'repadmin');
end;
3. Schedules a purge job that periodically clears the deferred transaction queue and propagates deferred transactions to other principal sites or to the materialized view site. To 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: Next execution date, sysdate indicates immediately.

Interval: Interval period, sysdate + 1 means interval one day, sysdate+ 1/24 indicates an hour interval

Delay_seconds: The delay time at which the secondary purge operation is stopped when the delay queue does not delay the event.

4. Establish a replication agent for the materialized view site. Create a replication agent user and grant view subject permissions. The replication agent is the user who replicates the recipient's connection to the principal 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 body group.

disconnect;
connect repadmin/repadmin@SH;
begin
dbms_repcat.create_master_repgroup(gname=>'sh_rep');
end;
/
6. To add a duplicate object to the body group

A) Add 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] 下一页

The Oracle tutorial you are looking at is: a brief discussion of Oracle database set replication methods. >
b) Add 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． 如果添加的表没有主键需要设置可以代替主键的列或者列的集合

begin
dbms_repcat.set_columns(
sname => 'SHORACL',
oname => ' CREDIT_CARD ',
column_list => ' CREDIT_CARD_ID');
end;
/
　　 8． 在主体组中的数据对象可以被复制之前，必须为他们生成复制支持。该方法为复制创建必要的触发器、包或者存储过程：

begin
dbms_repcat.generate_replication_support(
sname=>'SHORACL',
oname=>' CREDIT_CARD',
type=>'TABLE',
min_communication=>TRUE);
end;
/
　　 9． 为快速刷新创建实体化视图日志：

create materialized view log on SHORACL. CREDIT_CARD;
　　 如果是没有主键的表示用一下语句：

create materialized view log on SHORACL. CREDIT_CARD with
rowid excluding new values;
　　 10．启动复制：

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

Two Set up a materialized view site.

1. Create a replication administrator and grant the appropriate 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. Registration of the transmitting party:

begin
dbms_defer_sys.register_propagator(
username => 'mvadmin');
end;
/
3. Public database connection. You need to create three database connections per replication. Public database connection Specifies the global name of the database:

create public database link ORACLSH using 'oracle.shanghai.
com';
The Using clause is followed by a global database name or a 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. Establish purge delay transaction queue scheduling job:

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. To establish a replication administrator Mvadmin database connection:

create database link ORACLSH connect to proxy_bjoracle
identified by proxy_bjoralce
Connect to ... Identified by ...子句指明用什么用户连接远程数据库
6. To establish a replication dispatch 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. Grant Shoracl User (corresponding SHORACL scheme)

The appropriate permissions establish a materialized view:

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. To establish a database connection for a replication scenario:

disconnect;
connect SHORACL/SHORACL@BJ;
create database link ORACLSH connect to ORACL identified
by ORACL;
The database connection to the replication scheme and the replication Administrator's database connection use the same name as the corresponding public database connection in the system user, and the database global name or connection string specified in the public database connection is used when the connection is scheduled.

9. To establish a materialized view:

prev [1] [2] [3] Next page

The Oracle tutorial you are looking at is: a brief discussion of Oracle database set replication methods.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;
The database connection name is followed by the @. Use rowID to refresh if the table does not have a primary key

create materialized view SHORACL. CREDIT_CARD refresh
fast with rowid as select * from ORACL. CREDIT_CARD@ORACLSH;
10. To 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. To add a replication object to the Refresh group:

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

1. See if the Sys.dba_jobs view generates enough jobs.

After the steps above, there should be three jobs that are cleanup jobs, scheduling jobs, and refreshing jobs, and see if the What field of the view has 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 you exclude other systems the database replication in Hangzhou and Wuhan will have 7 jobs (if a refresh group is set up separately for each copy), and the cleanup job is always only one. Each copy corresponds to a dispatch job, and each refresh group corresponds to a refresh job.

2. Review the job_queue_processes parameter to ensure that the parameter is not 0 (the default value for the database is 0), and if the parameter is zero, the system does not automatically refresh the replicated data unless you manually perform a refresh each time.

3. After you make sure that replication is performed, observe the failures field of the Sys.dba_jobs view. If replication fails during the refresh process, Oracle automatically tries to refresh again after 1 minutes, after the failure in 2 minutes, 4 minutes, 8 minutes. After attempting to refresh until the number of failures reaches 16 or the interval exceeds the interval between job settings, the job is marked as interrupted and Oracle no longer executes the job. To rerun the run process using the Dbms_job package for the change job:

begin
dbms_job.run(job_no);
end;
/
Job_no is the value of the Sys.dba_jobs job field, the job number.

You need to manually find the error point and correct it before you rerun the job that was interrupted due to an error.

　　 Summary

This article only uses the simplest features of Oracle advanced replication, and Oracle's advanced replication also provides updatable views and complex, read-only materialized view replication, but not all of us are going to use advanced, complex, and powerful features, depending on the system's capabilities and performance requirements when applied, Select the appropriate replication technology

Previous page

prev [1] [2] [3]