|
Types of Replication
Oracle's four basic types of replication. Starting from Oracle9 Read-only Snapshots are so
called Read-Only Materialized Views.
|
Replication Type
|
Description
|
|
Read-only materialized views
|
A master table is copied to one or more databases. Changes in
the master table are reflected in the snapshot tables whenever the snapshot
refreshes. The snapshot site determines the frequency of the refreshes; data is
pulled.
|
|
Updateable materialized views
|
Similar to read-only snapshots, except that the snapshot sites
are able to modify the data and send their changes back to the master. The
snapshot site determines the frequency of the refreshes and the frequency with
which updates are sent back to the master.
|
|
Multi-master replication
|
A table is copied to one or more databases, and each database
has the ability to insert, update, or delete records from it. Modifications are
pushed to the other database at an interval that the DBA sets for each
replication group. The highest theoretical frequency is once per second.
|
|
Procedural replication
|
A call to a packaged procedure or function is replicated to
one or more databases.
|
As you can see, these modes of replication are quite different,
and each is suited for specific kinds of uses. A single environment can utilize all
of these methods; they are not mutually exclusive.
Read-only snapshot sites
can be used to provide local access to remote master tables. Having a local snapshots
of the data improves query response time. Updates can only be issued against the
master table.
In a basic configuration, materialized views can provide read-only
access to the table data that originates from a master site or master materialized
view site. Applications can query data from read-only materialized views to avoid
network access to the master site, regardless of network availability. However,
applications throughout the system must access data at the master site to perform
data manipulation language changes (DML). The master tables and master materialized
views of read-only materialized views do not need to belong to a replication
group.
Read-only materialized views provide the following
benefits:
-
Eliminate the possibility of conflicts because they cannot be
updated.
-
Support complex materialized views. Examples of complex
materialized views are materialized views that contain set operations or a
CONNECT BY clause.
Example
In order to create one (or
many) read-only snapshot of the master Oracle database tables, the following steps
are necessary.
-
Master Site: Oracle
10.1.0.3, Solaris 9, SID=QUO3
-
Snapshot Site: Oracle
8.1.7.4, Solaris 8, SID=DIA1
Check TNSNAMES.ORA
-
On Master (Host=quorum)
DIA1.WORLD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = diamond)(PORT =
1521))
(CONNECT_DATA =
(SERVICE_NAME = DIA1)
(INSTANCE_NAME = DIA1)
(SRVR = DEDICATED)
)
)
DIA1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = diamond)(PORT =
1521))
(CONNECT_DATA =
(SERVICE_NAME = DIA1)
(INSTANCE_NAME = DIA1)
(SRVR = DEDICATED)
)
)
On Snapshot (Host=diamond)
QUO3.WORLD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = quorum)(PORT =
1523))
(CONNECT_DATA =
(SERVICE_NAME = QUO3)
(INSTANCE_NAME = QUO3)
(SRVR = DEDICATED)
)
)
QUO3 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = quorum)(PORT =
1523))
(CONNECT_DATA =
(SERVICE_NAME = QUO3)
(INSTANCE_NAME = QUO3)
(SRVR = DEDICATED)
)
)
Create DB Links
On Master (Host=quorum)
sqlplus scott/tiger@QUO3
CREATE DATABASE LINK DIA1 CONNECT TO scott IDENTIFIED BY tiger using
'DIA1';
Database link created.
On Snapshot (Host=diamond)
sqlplus scott/tiger@DIA1
CREATE DATABASE LINK QUO3 CONNECT TO scott IDENTIFIED BY tiger using
'QUO3';
Database link created.
desc emp@QUO3;
Name
Null? Type
----------------------------------------- --------
----------------------
EMPNO
NOT NULL NUMBER(4)
ENAME
VARCHAR2(10)
JOB
VARCHAR2(9)
MGR
NUMBER(4)
HIREDATE
DATE
SAL
NUMBER(7,2)
COMM
NUMBER(7,2)
DEPTNO
NUMBER(2)
Create Snapshot Log
For each table, which
should be replicated, a SNAPSHOT LOG must be created (as user, who owns the
table).
A materialized view log
(Snapshot Log) is a table at the materialized view's master site or master
materialized view site that records all of the DML changes to the master table or
master materialized view. A materialized view log is associated with a single master
table or master materialized view, and each of those has only one materialized view
log, regardless of how many materialized views refresh from the master. A fast
refresh of a materialized view is possible only if the materialized view's master has
a materialized view log. When a materialized view is fast refreshed, entries in
the materialized view's associated materialized view log that have appeared since the
materialized view was last refreshed are applied to the materialized view.
On Master (Host=quorum)
sqlplus scott/tiger@QUO3
DROP SNAPSHOT LOG ON emp;
CREATE SNAPSHOT LOG ON emp
TABLESPACE tab
STORAGE
(INITIAL 200K
NEXT 200K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0);
Materialized view log created.
Create Snapshot
A snapshot contains on the
remote site the data of the master table. All data changes are reflected in the
snapshot after a refresh of the snapshot (either triggered manually or
automatically).
On Snapshot (Host=diamond)
sqlplus scott/tiger@DIA1
CREATE SNAPSHOT emp
PCTFREE 15
STORAGE
(INITIAL 200K
NEXT 200K
PCTINCREASE 0)
TABLESPACE tab
USING INDEX
PCTFREE 0
STORAGE
(INITIAL 200K
NEXT 200K
PCTINCREASE 0)
TABLESPACE idx
REFRESH FORCE
START WITH SYSDATE NEXT SYSDATE+(1/1440) /* 60
SECONDS */
AS SELECT * FROM emp@QUO3;
Materialized view created.
Create Synonym
On Snapshot (Host=diamond)
sqlplus scott/tiger@DIA1
CREATE PUBLIC SYNONYM emp FOR scott.emp;
Synonym created.
Now, you can access the table emp locally which will be
automatically refreshed every 60 sec.
Refresh the Snapshot
The snapshot on the remote site must be refreshed regularily. This
can be done bullet either by hand after a substantial change on the master site or in
regular intervalls.
Manual Refresh
On Snapshot (Host=diamond)
sqlplus scott/tiger@DIA1
execute dbms_snapshot.refresh('scott.emp','F');
PL/SQL procedure successfully completed.
The first parameter is a list of snapshots to be refreshed. The
second describes the method, F stands for FAST refresh (only changes in the master
table are propagated, C stands for complete refresh.
There is also a dbms_snapshot.refresh_all routine. It
requires some more privileges.
execute dbms_snapshot.refresh_all;
Automatic Refresh
Automatic refresh is realized by parameters for a refresh group or
by the definition of the snapshot. In order to run periodoc jobs in the database (as
automatic refresh jobs), the ability to run SNP background jobs must be given.
Especially, in the file init<instance>.ora , located the parameter
job_queue_processes = 1
must be included (the default is 0) and the database must be
restarted! This parameter allows background processes to be executed in one job
queue.
Check Automatic Refresh
You may use the following query to check, if automatically refresh
works.
SELECT SUBSTR(job,1,4) "Job", SUBSTR(log_user,1,5)
"User",
SUBSTR(schema_user,1,5) "Schema",
SUBSTR(TO_CHAR(last_date,'DD.MM.YYYY HH24:MI'),1,16) "Last Date",
SUBSTR(TO_CHAR(next_date,'DD.MM.YYYY HH24:MI'),1,16) "Next Date",
SUBSTR(broken,1,2) "B", SUBSTR(failures,1,6) "Failed",
SUBSTR(what,1,20) "Command"
FROM dba_jobs;
Job User Schem Last
Date Next
Date B Failed Command
---- ----- ----- ---------------- ---------------- - ------
--------------------
2 SCOTT SCOTT 06.09.2005 15:05 06.09.2005 15:06 N
0 dbms_refresh.refresh
Drop Snapshots and Snapshot Logs
On Master (Host=quorum)
sqlplus scott/tiger@QUO3
spool drop_snapshot_logs.sql
select 'PROMPT Dropping Snapshot Log for '||MASTER||chr(10)
||'drop snapshot log on '||MASTER||';'
from USER_SNAPSHOT_LOGS;
spool off
@drop_snapshot_logs.sql
PROMPT Snapshot Logs Dropped
On Snapshot (Host=diamond)
sqlplus scott/tiger@DIA1
spool drop_snapshots.sql
select 'PROMPT Dropping Snapshot for '||NAME||chr(10)
||'drop snapshot '||NAME||';'
from USER_SNAPSHOTS;
@drop_snapshots.sql
PROMPT Snapshots dropped
Refresh Groups
If the snapshot must obey certain integrity rules, like
referential integrity, then the refresh of the snapshot tables must be synchronized.
This is achieved by creating refresh groups.
dbms_refresh.make(
name =>
'MY_GRP',
list =>
'emp,dept,bonus,salgrade',
next_date
=> SYSDATE,
interval
=> 'SYSDATE + (1/1440)', /* 60 seconds
*/
implicit_destroy => TRUE, /* delete the group if substracting
the last member */
lax
=> TRUE, /* delete from other group if
already
existing in a group */
rollback_seg => 'RB06'
);
commit;
Overview
You have already seen how to create and use read-only materialized
views. They offer the powerful ability to replicate data in tables across separate
databases. With multi-master replication, you can replicate more than just database
tables. You can replicate:
-
Tables
-
Indexes
-
Procedures, functions, and triggers
-
Packages
-
User-defined types (Oracle9i)
As always, there are plusses and minuses to using multi-master
replication.
The positive benefits of MMR include the following:
-
Replicates more objects, including user-defined objects.
-
Updates or modifies the objects being replicated. Adding a
column to a table at the master definition site can be replicated to other master
sites.
-
Replicates with any number of other databases. Any master site
can replicate with other master sites, updatable Mview sites, and read-only Mview
sites.
However, there are some downsides such as:
-
Potentially large network bandwidth requirements. Not only does
multi-master push and pull changes between sites, it also sends
acknowledgements and quite a bit of administrative data.
-
Reduced Performance. Complexity and robustness comes at a
price. MMR involves the use of triggers and procedures, and this can result in a
database performance hit. Depending on how much data you are replicating, this
performance hit can be substantial.
-
Significant increases in administration requirements.
When problems appear in the database, the DBA must insure that replication is not
the cause or that the cause is not replicated to other databases. Database
performance tuning and problem resolution becomes more complicated by an order of
magnitude.
-
Database changes require additional planning. Rolling out a
new version of an application can be much more difficult. Each new version will
require revisiting the design of the replication.
The considerations above should reinforce not to implement a
higher level of replication than you need. Multi-master replication is powerful, and
it is complicated to create and monitor the replication environment.
Concepts and
Terminology
A deferred transaction is a transaction that is queued for
delivery to one or more remote databases. If you use multi-master replication
with asynchronous propagation, Oracle creates deferred transactions for all local DML
activity against the replicated tables.
A replication group is a collection of one or more replicated
objects (typically tables) that are administrated together. Very generally
speaking, the objects in a given replication group are logically related; for
example, they are often the set of objects that a given application uses. A given
replication group can contain objects from multiple schema, and a given schema can
have objects in more than one replication group. However, any given object can be in
only one replication group.
The most significant property of replication groups is that all
objects in a given group are quiesced together. That is, DML activity is enabled and
disabled for all group members simultaneously.
Quiescence is the act of suspending DML activity for all tables
in a given replication group. This is required in order to perform certain
administrative tasks on objects in a replication group, such as altering a table. The
Oracle built-in package procedure call that quiesces a replication group is
DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY.
The master definition site of a replication group is the
database instance from which the group is administered. This site is usually, but
not necessarily, the site at which the replication group was originally
created.
A master site is a site that is participating in one or more
replication groups but is not the master definition site.
Replication support refers to the packages and triggers that
Oracle creates in order to propagate changes to replicated objects, to detect and
resolve conflicts, and so on.
When Oracle propagates an update to destination tables, it expects
the current data for the row at the destination to match the data at the originating
site prior to the update. If the data is not the same, an update conflict results.
Similarly, if an insert fails because of a primary key violation (i.e., a unique
constraint violation) the result is a uniqueness conflict or violation. And, if the
target row of a delete does not exist at the destination site, a delete conflict
results.
Unless you are propagating changes among master sites
synchronously, there is a delay between the time a DML change is applied at the
originating database and the time the transaction reaches the destination databases.
This lag is known as propagation latency.
Replication Users
-
The Administrator maintains the master group, adds or
removes objects, etc.
-
The Propagator is responsible for pushing items in the
deferred transaction queue to all other master sites.
-
The Receiver takes items that have arrived in the
deferred transaction queue and applies them to the local objects.
Oracle recommends that you use the Replication Administrator to
perform all three tasks when establishing your replication environment. For
additional security, you can establish a separate user as the receiver and
propagator.
-
A master site can have only one propagator.
-
A propagator has the "execute any procedure" grant.
-
A master site can have multiple receivers.
-
A master group can have only one receiver per master
site.
-
A receiver is not granted "execute any procedure".
Next, we illustrate how to set up both a master site and a
materialized view replication site using the replication management API.
Before you build the replication environment, you need to set up
the sites that will participate in the replication environment. As illustrated there
are separate processes for setting up a master site versus setting up a materialized
view site.
We use the following databases:
-
CEL1.WORLD
-
CEL2.WORLD
-
REP1.WORLD
-
REP2.WORLD
Notice that REP1.WORLD and REP2.WORLD are materialized views based on the CEL1.WORLD. The arrows in the Figure represents database
links.
Important Note !
|
If you get an error when creating Replication Groups
such as:
ERROR at line 1:
ORA-04052: error occurred when looking up remote object
REPADMIN.SYS@CEL1.WORLD
ORA-00604: error occurred at recursive SQL level 2
ORA-12154: TNS:could not resolve the connect identifier specified
then, connect directly to the Database as
follows:
export ORACLE_SID=CEL1
sqlplus repadmin/repadmin
Now create the Replication Group.
|
Create SCOTT Schemas
We use the simple EMP/DEPT schema from SCOTT/TIGER.
sqlplus scott/tiger@CEL1.WORLD
start demobld.sql
ALTER TABLE emp ADD (CONSTRAINT pk_emp PRIMARY KEY (empno) ENABLE);
ALTER TABLE dept ADD (CONSTRAINT pk_dept PRIMARY KEY (deptno) ENABLE);
ALTER TABLE emp ADD (
CONSTRAINT fk_emp_dept FOREIGN KEY (deptno)
REFERENCES dept (deptno) ENABLE);
sqlplus scott/tiger@CEL2.WORLD
start demobld.sql
ALTER TABLE emp ADD (CONSTRAINT pk_emp PRIMARY KEY (empno) ENABLE);
ALTER TABLE dept ADD (CONSTRAINT pk_dept PRIMARY KEY (deptno) ENABLE);
ALTER TABLE emp ADD (
CONSTRAINT fk_emp_dept FOREIGN KEY (deptno)
REFERENCES dept (deptno) ENABLE);
The following sections contain step-by-step instructions for
setting up the master sites in the sample replication environment: CEL1.WORLD and CEL2.WORLD. Before you
set up the master sites, configure your network and Oracle Net so that all three
databases can communicate with each other.
Create Password Files
If the DBA wants to start up an Oracle instance there must be a
way for Oracle to authenticate this DBA. That is if (s)he is allowed to do so.
Obviously, his password can not be stored in the database, because Oracle can not
access the database if the instance has not been started up. Therefore, the
authentication of the DBA must happen outside of the database.
The init parameter remote_login_passwordfile specifies if a password file is
used to authenticate the DBA or not. If it set either to shared or exclusive a
password file will be used.
Default location and file name
The default location for the password file is:
Deleting a password file
If password file authentication is no longer needed, the password
file can be deleted and the init parameter remote_login_passwordfile set to none.
Password file state
If a password file is shared or exclusive is also stored in the
password file. After its creation, the state is shared. The state can be changed by
setting remote_login_passwordfile and starting
the database. That is, the database overwrites the state in the password file when it
is started up. A password file whose state is shared can
only contain SYS.
Creating a password file
Password files are created with the orapwd tool.
orapwd file=orapwCEL1 password=manager entries=5 force=y
Create a Symbolic Link from $ORACLE_HOME/dbs to the Password.
lrwxrwxrwx 1 oracle dba 39 2005-09-13 16:55 initCEL1.ora ->
/home/oracle/config/10.1.0/initCEL1.ora
lrwxrwxrwx 1 oracle dba 39 2005-09-14 11:22 initCEL2.ora ->
/home/oracle/config/10.1.0/initCEL2.ora
lrwxrwxrwx 1 oracle dba 39 2005-09-14 08:20 initREP1.ora ->
/home/oracle/config/10.1.0/initREP1.ora
lrwxrwxrwx 1 oracle dba 39 2005-09-14 11:22 initREP2.ora ->
/home/oracle/config/10.1.0/initREP2.ora
lrwxrwxrwx 1 oracle dba 36 2005-09-22 11:22 orapwCEL1 ->
/home/oracle/config/10.1.0/orapwCEL1
lrwxrwxrwx 1 oracle dba 36 2005-09-22 11:22 orapwCEL2 ->
/home/oracle/config/10.1.0/orapwCEL2
lrwxrwxrwx 1 oracle dba 36 2005-09-22 11:22 orapwREP1 ->
/home/oracle/config/10.1.0/orapwREP1
lrwxrwxrwx 1 oracle dba 36 2005-09-22 11:22 orapwREP2 ->
/home/oracle/config/10.1.0/orapwREP2
Adding Users to the password file
Users are added to the password file when they're granted the
SYSDBA or SYSOPER privilege.
select * from v$pwfile_users;
USERNAME
SYSDB SYSOP
------------------------------ ----- -----
SYS
TRUE TRUE
grant SYSDBA to scott;
Grant succeeded.
select * from v$pwfile_users;
USERNAME
SYSDB SYSOP
------------------------------ ----- -----
SYS
TRUE TRUE
SCOTT
TRUE FALSE
Setup Oracle Network
The Network must be setup for all Sites:
# Akadia AG, Fichtenweg 10, CH-3672
Oberdiessbach
listener.ora
# --------------------------------------------------------------------------
# File: listener.ora
#
# Autor: Martin Zahn Akadia AG
#
# Purpose: Configuration file for Net Listener
#
# Location: $TNS_ADMIN
#
# Certified: Oracle 10.1.0.4 on Gentoo Linux (2004.0)
# --------------------------------------------------------------------------
LSNR101 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = cellar)(PORT =
1521))
)
)
SID_LIST_LSNR101 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = CEL1.WORLD)
(ORACLE_HOME = /opt/oracle/product/10.1.0)
(SID_NAME = CEL1)
)
(SID_DESC =
(GLOBAL_DBNAME = CEL2.WORLD)
(ORACLE_HOME = /opt/oracle/product/10.1.0)
(SID_NAME = CEL2)
)
(SID_DESC =
(GLOBAL_DBNAME = REP1.WORLD)
(ORACLE_HOME = /opt/oracle/product/10.1.0)
(SID_NAME = REP1)
)
(SID_DESC =
(GLOBAL_DBNAME = REP2.WORLD)
(ORACLE_HOME = /opt/oracle/product/10.1.0)
(SID_NAME = REP2)
)
)
USE_PLUG_AND_PLAY_LSNR101 = OFF
STARTUP_WAIT_TIME_LSNR101 = 0
LOG_DIRECTORY_LSNR101 = /home/oracle/config/10.1.0
TRACE_FILE_LSNR101 = listener_LSNR101.trc
CONNECT_TIMEOUT_LSNR101 = 10
TRACE_LEVEL_LSNR101 = OFF
SAVE_CONFIG_ON_STOP_LISTENER = OFF
# Akadia AG, Fichtenweg 10, CH-3672
Oberdiessbach
tnsnames.ora
#
--------------------------------------------------------------------------
# File: tnsnames.ora
#
# Autor: Martin Zahn Akadia AG
#
# Purpose: Configuration File for all Net Clients
#
# Location: $TNS_ADMIN
#
# Certified: Oracle 10.1.0.2 on Gentoo Linux (2004.0)
# --------------------------------------------------------------------------
#
CEL1.WORLD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = cellar)(PORT =
1521))
)
(CONNECT_DATA =
(SERVICE_NAME = CEL1.WORLD)
)
)
CEL1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = cellar)(PORT =
1521))
)
(CONNECT_DATA =
(SERVICE_NAME = CEL1)
)
)
CEL2.WORLD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = cellar)(PORT =
1521))
)
(CONNECT_DATA =
(SERVICE_NAME = CEL2.WORLD)
)
)
CEL2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = cellar)(PORT =
1521))
)
(CONNECT_DATA =
(SERVICE_NAME = CEL2)
)
)
REP1.WORLD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = cellar)(PORT =
1521))
)
(CONNECT_DATA =
(SERVICE_NAME = REP1.WORLD)
)
)
REP1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = cellar)(PORT =
1521))
)
(CONNECT_DATA =
(SERVICE_NAME = REP1)
)
)
REP2.WORLD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = cellar)(PORT =
1521))
)
(CONNECT_DATA =
(SERVICE_NAME = REP2.WORLD)
)
)
REP2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = cellar)(PORT =
1521))
)
(CONNECT_DATA =
(SERVICE_NAME = REP2)
)
)
# Akadia AG, Fichtenweg 10, CH-3672
Oberdiessbach
sqlnet.ora
#
--------------------------------------------------------------------------
# File: sqlnet.ora
#
# Autor: Martin Zahn Akadia AG
#
# Purpose: Configuration File for all Net8 Clients
#
# Location: $TNS_ADMIN
#
# Certified: Oracle 10.1.0.2 on Gentoo Linux (2004.0)
# --------------------------------------------------------------------------
#
TRACE_LEVEL_CLIENT = OFF
NAMES.DIRECTORY_PATH= (TNSNAMES)
SQLNET.CRYPTO_SEED = 4fhfguweotcadsfdsafjkdsfqp5f201p45mxskdlfdasf
AUTOMATIC_IPC = ON
NAMES.DEFAULT_DOMAIN = WORLD
BEQUEATH_DETACH = NO
SQLNET.EXPIRE_TIME = 10
NAME.DEFAULT_ZONE = WORLD
USE_DEDICATED_SERVER = ON
Setup INIT.ORA Parameters
global_names = TRUE
db_name = CEL1
db_domain = WORLD
instance_name = CEL1
service_names = CEL1.WORLD
remote_login_passwordfile = exclusive
job_queue_processes = 10
parallel_min_servers = 2
parallel_max_servers = 10
The following Figure shows the needed Steps to setup the users at
the Master Sites.
The next steps shows how to create the following Users and
Database Links on the two Master Sites. The commands for CEL2.WORLD are not shown, these are the same as for CEL1.WORLD.
(1) Connect at Master Site CEL1.WORLD
CONNECT sys/manager@CEL1.WORLD as sysdba;
(2) Setup the Replication Administrator
The Replication Administrator on the Master has the following
Privileges
CREATE USER repadmin IDENTIFIED BY repadmin
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA 0 ON system
PROFILE default;
GRANT CONNECT,RESOURCE TO repadmin;
Authorise the Replication Administrator to administer replication
groups and schemas.
BEGIN
DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA (
username => 'repadmin');
END;
/
This procedure grants the necessary privileges to the Replication
Administrator to administer any replication groups at the current site.
(3) Grant create, lock and select to administrator
GRANT
LOCK ANY TABLE,
COMMENT ANY TABLE,
CREATE SESSION,
SELECT ANY TABLE,
CREATE TABLE,
CREATE PROCEDURE,
CREATE SEQUENCE,
CREATE TRIGGER,
CREATE VIEW,
CREATE SYNONYM,
ALTER SESSION,
CREATE MATERIALIZED VIEW,
CREATE DATABASE LINK,
SELECT ANY DICTIONARY
TO repadmin;
(4) Register Propagator
Grant propagate privileges to the Replication Administrator, to
propagate changes to remote sites. The propagator is responsible for propagating the
deferred transaction queue to other master sites.
BEGIN
DBMS_DEFER_SYS.REGISTER_PROPAGATOR (
username=>'repadmin');
END;
/
This procedure registers the specified user as the propagator for
the local database. It also grants the following privileges to the specified user (so
that the user can create wrappers):
CREATE SESSION
CREATE PROCEDURE
CREATE DATABASE LINK
EXECUTE ANY PROCEDURE
(5) Register Receiver
Register receiver, 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;
/
This procedure assigns proxy materialized view administrator or
receiver privileges at the master site or master materialized view site for use with
remote sites. This procedure grants only the necessary privileges to the proxy
materialized view administrator or receiver.
|
privilege_type
|
Specifies the privilege type you are assigning. Use the
following values for to define your privilege_type:
-
receiver for
receiver privileges.
-
proxy_snapadmin for
proxy materialized view administration privileges
|
|
list_of_gnames
|
Comma-separated list of replication groups you want a user
registered for receiver privileges. There must be no spaces between entries in
the list. If you set list_of_gnames to NULL, then the user is registered for all
replication groups, even replication groups that are not yet known when this
procedure is called.
|
(6) Schedule a job to delete successfully replicated transactions on
Master
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@CEL1.WORLD
BEGIN
DBMS_DEFER_SYS.SCHEDULE_PURGE (
next_date => sysdate,
interval => 'sysdate+1/24/30',
delay_seconds =>
0);
END;
/
(7a) Setup Proxy Materialized View Administrator
The proxy materialized view administrator performs tasks at the
target master site on behalf of the materialized view administrator at the
materialized view site.
CONNECT sys/manager@CEL1.WORLD as sysdba;
CREATE USER proxyadmin IDENTIFIED BY proxyadmin
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA 0 ON system
PROFILE default;
GRANT CONNECT,RESOURCE TO proxyadmin;
Register Proxy Materialized View Administrator
BEGIN
DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (
username => 'proxyadmin',
privilege_type => 'proxy_snapadmin',
list_of_gnames => NULL);
END;
/
GRANT
CREATE SESSION,
SELECT ANY TABLE,
SELECT_CATALOG_ROLE
TO proxyadmin;
(7b) Setup Proxy Refresher
The proxy refresher performs tasks at the master site on behalf
of the refresher at the materialized view site.
CREATE USER proxyrefresher IDENTIFIED BY proxyrefresher
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA 0 ON system
PROFILE default;
GRANT CREATE SESSION TO proxyrefresher;
GRANT SELECT ANY TABLE TO proxyrefresher;
********** Now, do exactly the same for the
Master Site CEL2.WORLD (Not shown here). ***********
(8) Create the DB-Links between the 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 sys/manager@CEL1.WORLD as
sysdba;
DROP PUBLIC DATABASE LINK CEL2.WORLD;
CREATE PUBLIC DATABASE LINK CEL2.WORLD USING
'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(COMMUNITY=TCP)(PROTOCOL=TCP)(Host=cellar)(Port=1521)))(CONNECT_DATA=(SID=CEL2)))';
CONNECT repadmin/repadmin@CEL1.WORLD;
CREATE DATABASE LINK CEL2.WORLD CONNECT TO repadmin
IDENTIFIED BY repadmin;
CONNECT sys/manager@CEL2.WORLD as sysdba;
DROP PUBLIC DATABASE LINK CEL1.WORLD;
CREATE PUBLIC DATABASE LINK CEL1.WORLD USING
'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(COMMUNITY=TCP)(PROTOCOL=TCP)(Host=cellar)(Port=1521)))(CONNECT_DATA=(SID=CEL1)))';
CONNECT repadmin/repadmin@CEL2.WORLD;
CREATE DATABASE LINK CEL1.WORLD CONNECT TO repadmin
IDENTIFIED BY repadmin;
(9) Define a schedule for each database link
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. 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.
The following example schedules a periodic push every minute. Make sure that the
refresh rate is not smalled than the time needed to refresh.
CONNECT repadmin/repadmin@CEL1.WORLD
BEGIN
DBMS_DEFER_SYS.SCHEDULE_PUSH (
destination => 'CEL2.WORLD',
interval => 'SYSDATE + (1/24/60)',
next_date => SYSDATE,
stop_on_error =>
FALSE,
parallelism => 1,
delay_seconds => 0);
END;
/
CONNECT repadmin/repadmin@CEL2.WORLD
BEGIN
DBMS_DEFER_SYS.SCHEDULE_PUSH (
destination => 'CEL1.WORLD',
interval => 'SYSDATE + (1/24/60)',
next_date => SYSDATE,
stop_on_error =>
FALSE,
parallelism => 1,
delay_seconds => 0);
END;
/
Scheduling Continuous Pushes
Even when using Oracle's asynchronous replication mechanisms, you
can configure a scheduled link to simulate continuous, real-time replication. To do
so, use the DBMS_DEFER_SYS.SCHEDULE_PUSH procedure and specify the following
settings:
|
Parameter
|
Value
|
|
delay_seconds
|
1200
|
|
interval
|
Lower than the delay_seconds
setting
|
|
parallelism
|
1 or higher
|
|
execution_seconds
|
Higher than the delay_seconds
setting
|
With this configuration, Oracle continues to push transactions
that enter the deferred transaction queue for the duration of the entire interval. If
the deferred transaction queue has no transactions to propagate for the amount of
time specified by the delay_seconds parameter, then
Oracle releases the resources used by the job and starts fresh when the next job
queue process becomes available.
Important Note !
|
We observed, that the Database cannot be shutdown with
SHUTDOWN IMMEDIATE with these settings. The Database can only be shutdown with
SHUTDOWN ABORT, therefore we do NOT recommend to use Continuous Pushes.
|
The following is an example that simulates continual
pushes:
BEGIN
DBMS_DEFER_SYS.SCHEDULE_PUSH (
destination => 'CEL2.WORLD',
interval => 'SYSDATE + (1/144)',
next_date => SYSDATE,
stop_on_error =>
FALSE,
parallelism => 1,
execution_seconds =>
1500,
delay_seconds => 1200);
END;
/
After you have set up your master sites, you are ready to build a
master group.
(1) Create Schema at Master Sites
It is assumed that the SCOTT schema exists at CEL1.WORLD and
CEL2.WORLD.
(2) Create the 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.
CONNECT repadmin/repadmin@CEL1.WORLD
BEGIN
DBMS_REPCAT.DROP_MASTER_REPGROUP (
gname => 'REPG');
END;
/
BEGIN
DBMS_REPCAT.CREATE_MASTER_REPGROUP (
gname => 'REPG');
END;
/
(3) Add objects to the Replication
Group on Master
Now, add the replicated Objets (TABLE, INDEX, SYNONYM, TRIGGER,
VIEW, PROCEDURE, FUNCTION, PACKAGE, and PACKAGE BODY) to the Replication
Group.
BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => 'REPG',
type => 'TABLE',
oname => 'EMP',
sname => 'SCOTT',
use_existing_object => TRUE,
copy_rows => FALSE);
END;
/
BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => 'REPG',
type => 'TABLE',
oname => 'DEPT',
sname => 'SCOTT',
use_existing_object => TRUE,
copy_rows => FALSE);
END;
/
If Your Tables Don’t Have a Primary Key.
The DBMS_REPCAT.SET_COLUMNS procedure enables you to use an
alternate column or group of columns, instead of the primary key, to determine which
columns of a table to compare when using row-level replication. You must call this
procedure from the master definition site.
BEGIN
DBMS_REPCAT.SET_COLUMNS (
sname => 'owner_master',
oname => 'DEPT',
column_list => '"COL1","COL2","COL3","COL4"');
END;
(4) Add Additional Master Site CEL2.WORLD
After you have defined your master group at the master
definition site = CEL1.WORLD (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.
In this example, the use_existing_objects parameter in the ADD_MASTER_DATABASE procedure is set to TRUE because it is
assumed that the SCOTT schema already exists at all master sites. In other words, it
is assumed that the objects in the SCOTT 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.
CONNECT repadmin/repadmin@CEL1.WORLD
If you get an error when connection over SQL*Net for the DBMS_REPCAT.ADD_MASTER_DATABASE
command such as:
ERROR at line 1:
ORA-04052: error occurred when looking up remote object
REPADMIN.SYS@CEL1.WORLD
ORA-00604: error occurred at recursive SQL level 2
ORA-12154: TNS:could not resolve the connect identifier specified
then, connect directly to the Database:
export ORACLE_SID=CEL1
sqlplus repadmin/repadmin
BEGIN
DBMS_REPCAT.ADD_MASTER_DATABASE (
gname => 'REPG',
master => 'CEL2.WORLD',
use_existing_objects => TRUE,
copy_rows => FALSE,
propagation_mode => 'ASYNCHRONOUS');
END;
/
Check that Master Site CEL2.WORLD is
now in the Replication Group 'REPG'.
Wait until CEL2.WORLD appears in the
DBA_REPSITES view before continuing. Execute the following
SELECT statement in another SQL*Plus session to make sure that CEL2.WORLD has appeared:
SELECT DBLINK FROM DBA_REPSITES WHERE GNAME = 'REPG';
DBLINK
----------------------------------------------
CEL1.WORLD
CEL2.WORLD
(5) If conflicts are possible, then configure conflict
resolution methods.
The most common data conflict occurs when the same row at two or
more different sites are updated at nearly the same time, or before the deferred
transaction from one site was successfully propagated to the other sites.
Before you define overwrite or discard conflict resolution
methods, quiesce the master group that contains the table to which you want to apply
the conflict resolution method. In a single master replication environment, quiescing
the master group might not be required
BEGIN
DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY (
gname => 'REPG');
END;
/
All Oracle conflict resolution methods are based on logical column
groupings called column groups.
BEGIN
DBMS_REPCAT.MAKE_COLUMN_GROUP (
sname => 'SCOTT',
oname => 'EMP',
column_group => 'EMPCG',
list_of_column_names =>
'ename,job,mgr,hiredate,sal,comm,deptno');
END;
/
This example creates an OVERWRITE conflict resolution
method.
BEGIN
DBMS_REPCAT.ADD_UPDATE_RESOLUTION (
sname => 'SCOTT',
oname => 'EMP',
column_group => 'EMPCG',
sequence_no => 1,
method => 'DISCARD',
parameter_column_name =>
'ename,job,mgr,hiredate,sal,comm,deptno');
END;
/
(6) Activate Replication Support for each added Table on
Master
Replication support generates the triggers and packages needed to
support replication for a specified object.
CONNECT repadmin/repadmin@CEL1.WORLD
BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => 'SCOTT',
oname => 'EMP',
type => 'TABLE',
min_communication => TRUE);
END;
/
BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => 'SCOTT',
oname => 'DEPT',
type => 'TABLE',
min_communication => TRUE);
END;
/
|
min_communication
|
Set to false if any master site is running Oracle7 release
7.3.
Set to true when you want propagation of new and old values to be
minimized. The default is true.
|
(7) Start the Replication on the Master
We are now all set up and ready to replicate, so let’s do
it. From the master database, start the replication process (track activity on the
REPG objects).
CONNECT repadmin/repadmin@CEL1.WORLD
You should wait until the DBA_REPCATLOG view is empty
before resuming master activity.
SELECT COUNT(*) FROM DBA_REPCATLOG WHERE GNAME = 'REPG';
BEGIN
DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
gname => 'REPG');
END;
/
The master site is now waiting for the snapshot site to begin
replication. As changes are made to the master tables, they will be propagated as
required.
The view dba_repgroup can be used to check the status of
the replication groups that you create. After you have created the replication groups
and generated the replication support for various tables within that group, you
should query the dba_repgroup table to insure that REPG status field is set to
"Normal".
select sname, master, status from dba_repgroup;
SNAME
M STATUS
------------------------------ - ---------
REPG
Y NORMAL
The following Figure shows the needed Steps to setup the users at
the Materialized View Sites.
The next steps shows how to create the following Users and
Database Links on the two Materialized View Sites. The commands for REP2.WORLD are not shown, these are the same as for REP1.WORLD.
(1) Connect as system at Materialized View Site REP1.WORLD
CONNECT sys/manager@REP1.WORLD as
sysdba;
(2) Create Materialized View Site Users
Setup the Materialized View Administrator
DROP USER mviewadmin CASCADE;
CREATE USER mviewadmin IDENTIFIED BY mviewadmin
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA 0 ON system
PROFILE default;
GRANT CONNECT,RESOURCE TO mviewadmin;
Authorise the Materialized View Administrator to administer
replication groups and schemas.
BEGIN
DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA (
username => 'mviewadmin');
END;
/
Grant create,lock and select to Materialized View
Administrator
GRANT
LOCK ANY TABLE,
COMMENT ANY TABLE,
CREATE SESSION,
SELECT ANY TABLE,
CREATE TABLE,
CREATE PROCEDURE,
CREATE SEQUENCE,
CREATE TRIGGER,
CREATE VIEW,
CREATE SYNONYM,
ALTER SESSION,
CREATE MATERIALIZED VIEW,
CREATE DATABASE LINK,
SELECT ANY DICTIONARY
TO mviewadmin;
Create Materialized View Propagator
The propagator is responsible for propagating the deferred
transaction queue to the target master site.
DROP USER propagator CASCADE;
CREATE USER propagator IDENTIFIED BY propagator
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA 0 ON system
PROFILE default;
GRANT CONNECT,RESOURCE TO propagator;
Grant privs to the Materialized View Propagator, to propagate
changes to remote sites
BEGIN
DBMS_DEFER_SYS.REGISTER_PROPAGATOR (
username => 'propagator');
END;
/
This procedure registers the specified user as the propagator for
the local database. It also grants the following privileges to the specified user (so
that the user can create wrappers):
-
CREATE SESSION
-
CREATE PROCEDURE
-
CREATE DATABASE
LINK
-
EXECUTE ANY
PROCEDURE
Create the refresher
The refresher is responsible for "pulling" changes made to the
replicated tables at the target master site to the materialized view site. This user
refreshes one or more materialized views. If you want the mviewadmin user to be the
refresher, then this step is not required. The receiver is necessary only if the site
will function as a master materialized view site for other materialized views
sites.
Register Receiver to Materialized View Administrator
BEGIN
DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (
username => 'mviewadmin',
privilege_type => 'receiver',
list_of_gnames => NULL);
END;
/
(3) Setup Database Links to Master(s)
Create Public Database Link
CONNECT sys/manager@REP1.WORLD as
sysdba;
DROP PUBLIC DATABASE LINK CEL1.WORLD;
CREATE PUBLIC DATABASE LINK CEL1.WORLD USING
'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(COMMUNITY=TCP)(PROTOCOL=TCP)(Host=cellar)(Port=1521)))(CONNECT_DATA=(SID=CEL1)))';
Create the materialized view administrator database link.
You need to create a database link from the materialized view
administrator at the materialized view site to the proxy materialized view
administrator at the master site.
CONNECT mviewadmin/mviewadmin@REP1.WORLD;
DROP DATABASE LINK CEL1.WORLD;
CREATE DATABASE LINK CEL1.WORLD
CONNECT TO proxyadmin
IDENTIFIED BY proxyadmin
USING 'CEL1.WORLD';
Create the propagator/receiver database link.
You need to create a database link from the propagator at the
materialized view site to the receiver at the master site. The receiver was defined
when you created the master site.
CONNECT propagator/propagator@REP1.WORLD
DROP DATABASE LINK CEL1.WORLD;
CREATE DATABASE LINK CEL1.WORLD
CONNECT TO repadmin
IDENTIFIED BY repadmin
USING 'CEL1.WORLD';
(4) Schedule Purge at Materialized View Site (Optional)
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. If your
materialized view site only contains "read-only" materialized views, then you do not
need to execute this procedure.
CONNECT mviewadmin/mviewadmin@REP1.WORLD
BEGIN
DBMS_DEFER_SYS.SCHEDULE_PURGE (
next_date => SYSDATE,
interval => 'SYSDATE + 1/24',
delay_seconds => 0,
rollback_segment => '');
END;
/
(5 ) Schedule Push at Materialized View site (Optional)
If the materialized view site has a constant connection to its
master site, then you optionally can schedule push at the materialized view sites.
If the materialized view site is disconnected from its master site for extended
periods of time, then it is typically better not to schedule push and refresh on
demand, which pushes changes to the master site.
CONNECT mviewadmin/mviewadmin@REP1.WORLD
BEGIN
DBMS_DEFER_SYS.SCHEDULE_PUSH (
destination => 'CEL1.WORLD',
interval => 'SYSDATE + 1/144',
next_date => SYSDATE,
stop_on_error => FALSE,
delay_seconds => 0,
parallelism => 0);
END;
/
(6) Create the proxy materialized view administrator at Materialized
View site (Optional)
The proxy materialized view administrator performs tasks at the
target master materialized view site on behalf of the materialized view administrator
at the materialized view sites based on this materialized view site. This user is not
required if the site will not function as a master materialized view site for other
materialized view sites.
In our example we do not need this step.
********** Now, do exactly the same for the
Materialized View Site REP2 (Not shown here). ***********
The following Figure shows the needed Steps to setup the
Materialized View Group at the Materialized View Sites.
(1) Create Materialized View Logs on Master
When DML changes are made to the master table's data, Oracle
stores rows describing those changes in the materialized view log and then uses the
materialized view log to refresh materialized views based on the master table. This
process is called an incremental or fast refresh.
Without a materialized view log, Oracle must reexecute the
materialized view query to refresh the materialized view. This process is called a
complete refresh. Usually, a fast refresh takes less time than a complete
refresh.
A materialized view log is located in the master database in the
same schema as the master table. A master table can have only one materialized view
log defined on it.
CONNECT scott/tiger@CEL1.WORLD
CREATE MATERIALIZED VIEW LOG ON emp
TABLESPACE tab;
CREATE MATERIALIZED VIEW LOG ON dept
TABLESPACE tab;
(2) Create the Replicated Schema and its Database Link
Before building your materialized view group, you must make sure
that the replicated schema exists at the remote materialized view site and that |