Advanced Replication (1)

最新推荐文章于 2025-06-07 19:24:53 发布

转载最新推荐文章于 2025-06-07 19:24:53 发布 · 771 阅读

文章标签：

#database #schema #table #dictionary #asynchronous #conflict

1 Create Replication Site

Overview of Setting Up Replication Sites

The examples use the following databases:

■ orc1.world

■ orc2.world

orc1.world(Master Site)<====> orc2.world(Master Site)

Setting Up Master Sites

/************************* BEGINNING OF SCRIPT ******************************

Setting Up orc1.world

Complete the following steps to set up the orc1.world master site.

Step 1 Connect as SYSTEM at a master site at orc1.world.

Connect as SYSTEM to the database that you want to set up for replication. After you

set up orc1.world, begin again with Step 1 for site orc2.world.

SET ECHO ON

SPOOL setup_masters.out

CONNECT SYSTEM/MANAGER@orc1.world

Step 2 Create the replication administrator at orc1.world.

The replication administrator must be granted the necessary privileges to create and

manage a replication environment. The replication administrator must be created at

each database that participates in the replication environment.

CREATE USER repadmin IDENTIFIED BY repadmin;

Step 3 Grant privileges to the replication administrator at orc1.world.

Execute the GRANT_ADMIN_ANY_SCHEMA procedure to grant the replication

administrator powerful privileges to create and manage a replicated environment.

BEGIN

DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA (

username => 'repadmin');

END;

If you want your repadmin to be able to create materialized view logs for any

replicated table, then grant COMMENT ANY TABLE and LOCK ANY TABLE to repadmin:

GRANT COMMENT ANY TABLE TO repadmin;

GRANT LOCK ANY TABLE TO repadmin;

If you want your repadmin to be able to connect to the Replication Management tool,

then grant SELECT ANY DICTIONARY to repadmin:

GRANT SELECT ANY DICTIONARY TO repadmin;

Step 4 Register the propagator at orc1.world.

The propagator is responsible for propagating the deferred transaction queue to other

master sites.

BEGIN

DBMS_DEFER_SYS.REGISTER_PROPAGATOR (

username => 'repadmin');

END;

Step 5 Register the receiver at orc1.world.

The receiver receives the propagated deferred transactions sent by the propagator

from other master sites.

BEGIN

DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (

username => 'repadmin',

privilege_type => 'receiver',

list_of_gnames => NULL);

END;

Step 6 Schedule purge at master site orc1.world.

In order to keep the size of the deferred transaction queue in check, you should purge

successfully completed deferred transactions. The SCHEDULE_PURGE procedure

automates the purge process for you. You must execute this procedure as the

replication administrator.

Note: Date expressions are used for the NEXT_DATE and

INTERVAL parameters. For example:

■ Now is specified as: SYSDATE

■ An interval of one hour is specified as: SYSDATE + 1/24

■ An interval of seven days could be specified as: SYSDATE + 7

CONNECT repadmin/repadmin@orc1.world

BEGIN

DBMS_DEFER_SYS.SCHEDULE_PURGE (

next_date => SYSDATE,

interval => 'SYSDATE + 1/24',

delay_seconds => 0);

END;

Setting Up orc2.world

Complete the following steps to set up the orc2.world master site.

Step 1 Connect as SYSTEM at orc2.world.

You must connect as SYSTEM to the database that you want to set up for replication.

CONNECT SYSTEM/MANAGER@orc2.world

Step 2 Create the replication administrator at orc2.world.

The replication administrator must be granted the necessary privileges to create and

manage a replication environment. The replication administrator must be created at

each database that participates in the replication environment.

CREATE USER repadmin IDENTIFIED BY repadmin;

Step 3 Grant privileges to replication administrator at orc2.world.

Execute the GRANT_ADMIN_ANY_SCHEMA procedure to grant the replication

administrator powerful privileges to create and manage a replicated environment.

BEGIN

DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA (

username => 'repadmin');

END;

If you want your repadmin to be able to create materialized view logs for any

replicated table, then grant COMMENT ANY TABLE and LOCK ANY TABLE privileges to

repadmin:

GRANT COMMENT ANY TABLE TO repadmin;

GRANT LOCK ANY TABLE TO repadmin;

If you want your repadmin to be able to connect to the Replication Management tool,

then grant SELECT ANY DICTIONARY to repadmin:

GRANT SELECT ANY DICTIONARY TO repadmin;

Step 4 Register the propagator at orc2.world.

The propagator is responsible for propagating the deferred transaction queue to other

master sites.

BEGIN

DBMS_DEFER_SYS.REGISTER_PROPAGATOR (

username => 'repadmin');

END;

Step 5 Register the receiver at orc2.world.

The receiver receives the propagated deferred transactions sent by the propagator

from the other master sites.

BEGIN

DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (

username => 'repadmin',

privilege_type => 'receiver',

list_of_gnames => NULL);

END;

Step 6 Schedule purge at master site at orc2.world.

In order to keep the size of the deferred transaction queue in check, you should purge

successfully completed deferred transactions. The SCHEDULE_PURGE procedure

automates the purge process for you. You must execute this procedure as the

replication administrator.

CONNECT repadmin/repadmin@orc2.world

BEGIN

DBMS_DEFER_SYS.SCHEDULE_PURGE (

next_date => SYSDATE,

interval => 'SYSDATE + 1/24',

delay_seconds => 0);

END;

Creating Scheduled Links Between the Master Sites

Complete the following steps to create scheduled links between the master sites.

Step 1 Create database links between master sites.

The database links provide the necessary distributed mechanisms to allow the

different replication sites to replicate data among themselves. Before you create any

private database links, you must create the public database links that each private

database link will use. You then must create a database link between all replication

administrators at each of the master sites that you have set up.

CONNECT SYSTEM/MANAGER@orc1.world

CREATE PUBLIC DATABASE LINK orc2.world USING 'orc2.world';

CONNECT repadmin/repadmin@orc1.world

CREATE DATABASE LINK orc2.world CONNECT TO repadmin IDENTIFIED BY repadmin;

CONNECT SYSTEM/MANAGER@orc2.world

CREATE PUBLIC DATABASE LINK orc1.world USING 'orc1.world';

CONNECT repadmin/repadmin@orc2.world

CREATE DATABASE LINK orc1.world CONNECT TO repadmin IDENTIFIED BY repadmin;

Step 2 Define a schedule for each database link to create scheduled links.

Create a scheduled link by defining a database link when you execute the SCHEDULE_

PUSH procedure. The scheduled link determines how often your deferred transaction

queue is propagated to each of the other master sites. You need to execute the

SCHEDULE_PUSH procedure for each database link that you created in Step 1. The

database link is specified in the destination parameter of the SCHEDULE_PUSH

procedure.

Even when using Oracle's asynchronous replication mechanisms, you can configure a

scheduled link to simulate continuous, real-time replication. The scheduled links in

this example simulate continuous replication.

CONNECT repadmin/repadmin@orc1.world

BEGIN

DBMS_DEFER_SYS.SCHEDULE_PUSH (

destination => 'orc2.world',

interval => 'SYSDATE + (1/144)',

next_date => SYSDATE,

parallelism => 1,

execution_seconds => 1500,

delay_seconds => 1200);

END;

CONNECT repadmin/repadmin@orc2.world

BEGIN

DBMS_DEFER_SYS.SCHEDULE_PUSH (

destination => 'orc1.world',

interval => 'SYSDATE + (1/144)',

next_date => SYSDATE,

parallelism => 1,

execution_seconds => 1500,

delay_seconds => 1200);

END;

SET ECHO OFF

SPOOL OFF

/**************************END OF SCRIPT***********************************/

2 Create a Master Group

orc1.world(hr.countries) <====> orc2.world(hr.countries)

Before You Start

In order for the script in this chapter to work as designed, it is assumed that the hr

schema exists at orc1.world, orc2.world. The hr schema

includes the following database objects:

■ countries table

Creating a Master Group

Complete the following steps to create the hr_repg master group.

Note: If you are viewing this document online, then you can copy

the text from the "BEGINNING OF SCRIPT" line after this note to

the "END OF SCRIPT" line into a text editor and then edit the text

to create a script for your environment.

/************************* BEGINNING OF SCRIPT ******************************/

SET ECHO ON

SPOOL create_mg.out

CONNECT repadmin/repadmin@orc1.world

Step 1 Create the schema at master sites.

If the schema does not already exist at all of the master sites participating in the master

group, then create the schema now and grant it all of the necessary privileges. This

example uses the hr schema, which is one of the sample schemas that are installed by

default when you install Oracle. So, the hr schema should exist at all master sites.

PAUSE Press <RETURN> to continue when the schema exists at all master sites.

Step 2 Create the master group.

Use the CREATE_MASTER_REPGROUP procedure to define a new master group. When

you add an object to your master group or perform other replication administrative

tasks, you reference the master group name defined during this step. This step must be

completed by the replication administrator.

BEGIN

DBMS_REPCAT.CREATE_MASTER_REPGROUP (

gname => 'hr_repg');

END;

Step 3 Add objects to master group.

Use the CREATE_MASTER_REPOBJECT procedure to add an object to your master

group. In most cases, you probably will be adding tables and indexes to your master

group, but you can also add procedures, views, synonyms, and so on.

BEGIN

DBMS_REPCAT.CREATE_MASTER_REPOBJECT (

gname => 'hr_repg',

type => 'TABLE',

oname => 'countries',

sname => 'hr',

use_existing_object => TRUE,

copy_rows => FALSE);

END;

Step 4 Add additional master sites.

After you have defined your master group at the master definition site (the site where

the master group was created becomes the master definition site by default), you can

define the other sites that will participate in the replication environment. You might

have guessed that you will be adding the orc2.world sites to the

replication environment. This example creates the master group at all master sites, but

you have the option of creating the master group at one master site now and adding

additional master sites later without quiescing the database. In this case, you can skip

this step.

In this example, the use_existing_objects parameter in the ADD_MASTER_

DATABASE procedure is set to TRUE because it is assumed that the hr schema already

exists at all master sites. In other words, it is assumed that the objects in the hr schema

are precreated at all master sites. Also, the copy_rows parameter is set to FALSE

because it is assumed that the identical data is stored in the tables at each master site.

Note: When adding a master site to a master group that contains

tables with circular dependencies or a table that contains a

self-referential constraint, you must precreate the table definitions

and manually load the data at the new master site. The following is

an example of a circular dependency: Table A has a foreign key

constraint on table B, and table B has a foreign key constraint on

table A.

BEGIN

DBMS_REPCAT.ADD_MASTER_DATABASE (

gname => 'hr_repg',

master => 'orc2.world',

use_existing_objects => TRUE,

copy_rows => FALSE,

propagation_mode => 'ASYNCHRONOUS');

END;

Note: You should wait until orc2.world appears in the DBA_

REPSITES view before continuing. Execute the following SELECT

statement in another SQL*Plus session to make sure that

orc2.world has appeared:

SELECT DBLINK FROM DBA_REPSITES WHERE GNAME = 'HR_REPG';

PAUSE Press <RETURN> to continue.

Step 5 If conflicts are possible, then configure conflict resolution methods.

PAUSE Press <RETURN> to continue after configuring conflict resolution methods

or if no conflict resolution methods are required.

Step 6 Generate replication support.

BEGIN

DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (

sname => 'hr',

oname => 'countries',

type => 'TABLE',

min_communication => TRUE);

END;

Note: You should wait until the DBA_REPCATLOG view is empty

before resuming master activity. Execute the following SELECT

statement to monitor your DBA_REPCATLOG view:

SELECT COUNT(*) FROM DBA_REPCATLOG WHERE GNAME = 'HR_REPG';

PAUSE Press <RETURN> to continue.

Step 7 Start replication.

After creating your master group, adding replication objects, generating replication

support, and adding additional master databases, you need to start replication activity.

Use the RESUME_MASTER_ACTIVITY procedure to "turn on" replication for the

specified master group.

BEGIN

DBMS_REPCAT.RESUME_MASTER_ACTIVITY (

gname => 'hr_repg');

END;

SET ECHO OFF

SPOOL OFF

/************************* END OF SCRIPT **********************************/