Replication is the process of copying and maintaining database objects, such as tables, in multiple databases that make up a distributed database system. Changes applied at one site are captured and stored locally before being forwarded and applied at each of the remote locations. Replication supports a variety of applications that often have different requirements. For example, sales force automation, field service, retail, and other mass deployment applications typically require data to be periodically synchronized between central database systems and a large number of small, remote sites, which are often disconnected from the central database. Members of a sales force must be able to complete transactions, regardless of whether they are connected to the central database. In this case, remote sites must be autonomous.
A replication object is a database object existing on multiple servers in a distributed database system. In a replication environment, any updates made to a replication object at one site are applied to the copies at all other sites. Oracle Replication enables you to replicate the following types of objects: tables, indexes, views and object views, packages and package bodies, procedures and functions, user-defined types and type bodies, triggers, synonyms, index types, and user-defined operators.
Oracle manages replication objects using replication groups. A replication group is a collection of replication objects that are logically related. A replication group can contain objects from multiple schemas, and a single schema can have objects in multiple replication groups. However, each replication object can be a member of only one replication group. A replication group can exist at multiple replication sites. Replication environments support two basic types of sites: master sites and materialized view sites. The differences between master sites and materialized view sites are the following:
| A replication group at a master site is more
specifically referred to as a master group.
A replication group at a materialized view site is based on a master group
and is more specifically referred to as a materialized
view group. Additionally, every master group has exactly one master
definition site. A replication group's master definition site is a
master site serving as the control center for managing the replication group
and the objects in the group.
| A master site maintains a complete copy of all
objects in a replication group, while materialized views at a materialized
view site can contain all or a subset of the table data within a master
group.
| All master sites in a multimaster replication
environment communicate directly with one another to continually propagate
data changes in the replication group. Materialized view sites contain an
image, or materialized view, of the table data from a certain point in time.
Typically, a materialized view is refreshed periodically to synchronize it
with its master site. You can organize materialized views into refresh
groups. Materialized views in a refresh group can belong to one or
more materialized view groups, and they are refreshed at the same time to
ensure that the data in all materialized views in the refresh group
correspond to the same transactionally consistent point in time. | |
Oracle uses materialized views (also known as snapshots in prior releases) to replicate data to non-master sites in a replication environment. A materialized view is a replica of a target master from a single point in time. The master may be a master table at a master site. Materialized views are updated from one or more masters through individual batch updates, known as a refreshes. The following illustrate a materialized view connected to a single master site. (The arrows represent database links.)
When a materialized view is created, several additional mechanisms are created at the materialized view site to support the materialized view. Specifically, a base table, at least one index, and possibly a view are created. If you create an updatable materialized view, then an internal trigger and a local log (the updatable materialized view log) are also created at the materialized view site.
|
The master table or master materialized view is the basis for the materialized view. A master table is located at the target master site while a master materialized view is located at a master materialized view site. Changes made to the master table or master materialized view, as recorded by the materialized view log, are propagated to the materialized view during the refresh process. |
|
When changes are made to the master table or master
materialized view using DML, an internal trigger records information about
the affected rows in the materialized view log. This information includes
the values of the primary key, rowid, and/or object id, as well as the
values of the other columns logged in the materialized view log. This is an
internal |
|
When you create a materialized view log for a master table or
master materialized view, Oracle creates an underlying table as the
materialized view log. The name of a materialized view log's table is |
Three distinct types of users perform operations on materialized views:
| Creator: the user
who creates the materialized view
| Refresher: the user
who refreshes the materialized view
| Owner: the user who
owns the materialized view. The materialized view resides in this user's
schema. | |
One user may perform all of these operations on a particular materialized view. However, in some replication environments, different users perform these operations on a particular materialized view. The privileges required to perform these operations depend on whether the same user performs them or different users perform them. The following sections explain the privileges requirements in detail.
If the creator of a materialized view also owns the materialized view, this user must have the following privileges to create a materialized view, granted either explicitly or through a role:
| CREATE MATERIALIZED VIEW
or CREATE ANY MATERIALIZED VIEW
CREATE TABLE or CREATE
ANY TABLE
CREATE VIEW or CREATE
ANY VIEW if the compatibility level of the
database is lower than 8.1.0
SELECT object privilege on the master
and the master's materialized view log or SELECT ANY
TABLE system privilege. If the master site or master
materialized view site is remote, then the SELECT object
privilege must be granted to the user at the master site or master
materialized view site to which the user at the materialized view site
connects through a database link. |
If the creator of a materialized view is not the owner, certain privileges must be granted to the creator and to the owner to create a materialized view. The creator's privileges can be granted explicitly or through a role, but the owner's privileges must be granted explicitly. That is, the privileges granted to the owner cannot be granted through a role.
If the refresher of a materialized view also owns the materialized view, this
user must have SELECT object privilege on the master and the
master's materialized view log or SELECT ANY TABLE
system privilege. If the master site or master materialized view site is remote,
then the SELECT object privilege must be granted to the user at the
master site or master materialized view site to which the user at the
materialized view site connects through a database link. This
privilege can be granted either explicitly or through a role.
If the refresher of a materialized view is not the owner, certain privileges must be granted to the refresher and to the owner. These privileges can be granted either explicitly or through a role.
| Refresher | Owner | ||||
|---|---|---|---|---|---|
|
|
|
Oracle offers several types of materialized views: Primary Key, Object, ROWID,
and Complex. A materialized view can be either read-only, updatable, or
writeable. Users cannot perform data manipulation language (DML) statements on
read-only materialized views, but they can perform DML on updatable and
writeable materialized views. You can make a materialized view read-only during
creation by omitting the FOR UPDATE clause or
disabling the equivalent option in the Replication Management tool. Read-only
materialized views use many of the same mechanisms as updatable materialized
views, except that they do not need to belong to a materialized view group.
Primary key materialized views are the default type of materialized view. We'll
be examining the Primary Key materialized views for this class.
Whenever you create a materialized view, regardless of its type,
always specify the schema name of the table owner in the query for the
materialized view. For example, consider the following CREATE MATERIALIZED
VIEW statement:
CREATE MATERIALIZED VIEW hr.employees AS SELECT * FROM hr.employees@orc1.world;
The following is an example of a SQL statement for creating an updatable, primary key materialized view:
CREATE MATERIALIZED VIEW oe.customers FOR UPDATE AS SELECT * FROM oe.customers@orc1.world;
The following materialized view is created with a WHERE
clause containing a subquery:
CREATE MATERIALIZED VIEW oe.orders REFRESH FAST AS
SELECT * FROM oe.orders@orc1.world o
WHERE EXISTS
(SELECT * FROM oe.customers@orc1.world c
WHERE o.customer_id = c.customer_id AND c.credit_limit > 10000);
The following statement creates a materialized view group:
BEGIN
DBMS_REPCAT.CREATE_MVIEW_REPGROUP (
gname => 'hr_repg',
master => 'orc1.world',
propagation_mode => 'ASYNCHRONOUS');
END;
The following statement adds the hr.departments
materialized view to the materialized view group, making the materialized view
updatable:
BEGIN
DBMS_REPCAT.CREATE_MVIEW_REPOBJECT (
gname => 'hr_repg',
sname => 'hr',
oname => 'departments',
type => 'SNAPSHOT',
min_communication => TRUE);
END;
/
A materialized view is a transactionally (read) consistent reflection of its master as the data existed at a specific point in time (that is, at creation or when a refresh occurs). To keep a materialized view's data relatively current with the data of its master, the materialized view must be refreshed periodically. A materialized view refresh is an efficient batch operation that makes a materialized view reflect a more current state of its master table or master materialized view. To refresh materialized views, Oracle can refresh a materialized view using either a fast, complete, or force refresh.
|
To perform a complete refresh of a materialized view, the server that manages the materialized view executes the materialized view's defining query, which essentially recreates the materialized view. To refresh the materialized view, the result set of the query replaces the existing materialized view data. If you perform a complete refresh of a master materialized view, then the next refresh performed on any materialized views based on this master materialized view must be a complete refresh. If a fast refresh is attempted for such a materialized view after its master materialized view has performed a complete refresh, then Oracle returns the following error: ORA-12034 mview log is younger than last refresh |
| To perform a fast refresh, the
master that manages the materialized view first identifies the changes that
occurred in the master since the most recent refresh of the materialized
view and then applies these changes to the materialized view. You can
perform fast refreshes of materialized views only when the master table or
master materialized view has a materialized view log. Also, for fast
refreshes to be faster than complete refreshes, each join column in the CREATE
MATERIALIZED VIEW statement must have an index on
it. |
| To perform a force refresh of a materialized view, the server that manages the materialized view attempts to perform a fast refresh. If a fast refresh is not possible, then Oracle performs a complete refresh. |
When creating a refresh group, you can configure the group so that Oracle automatically refreshes the group's materialized views at scheduled intervals. Conversely, you can omit scheduling information so that the refresh group needs to be refreshed manually or "on-demand."
| The following are examples of simple date expressions that you can use to specify an interval: |
SYSDATE + 1/24 /* an interval of one hour */ SYSDATE + 7 /* an interval of seven days */
| The following example illustrates an on-demand refresh of the hr_refg
refresh group: |
execute DBMS_REFRESH.REFRESH('hr_refg');
Back to CS643 schedule