Read-Only Materialized Replication

Read-Only Materialized Views Based Replication Configuration

“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”

Read-only snapshot sites can be used to provide local access to remote master tables. Having a local snapshot 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:

1. Eliminate the possibility of conflicts because they cannot be updated.
2. 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.

1. Master Site: Oracle 11g, Microsoft Windows Server 2003, SID=HR
2. Snapshot Site: Oracle 10g, Microsoft Windows Server 2003, SID=ORCL

Following are the common steps for deploying Read-Only Materialized Views based Replication.

1. Create Net Service (Update TNSNAMES.ORA Files )
2. Create DB Links
3. Create Snapshot Log
4. Create Snapshot / Materialized View
5. Create Synonym
6. Refresh Snapshot / Materialized view
7. Drop Snapshots and Snapshot Logs

1. Create Net

Update TNANAMES.ORA file on all nodes manualy or using oracle supplied utility NETMGR

On Master (Host=DBSERVER) (Create HR Net Service)
On Snapshot (Host=HR) (Create HOHR Net Service)

2. Create DB Links

On Master (Host=DBSERVER)

Sqlplus scott/tiger@HR
CREATE DATABASE LINK HR CONNECT TO scott IDENTIFIED BY tiger USING 'HR';
Database link created.
On Snapshot (Host=HR)

Sqlplus scott/tiger@HOHR
CREATE DATABASE LINK HOHR CONNECT TO scott IDENTIFIED BY tiger using 'HOHR';
Database link created.

3. Create Snapshot Log

For each table, which should be replicated, a SNAPSHOT LOG must be created table’s owner.
A materialized view log (Snapshot Log) is a table at the materialized view's master 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=DBSERVER)

Sqlplus scott/tiger@HOHR

CREATE SNAPSHOT LOG ON emp
TABLESPACE users
STORAGE
(INITIAL 200K
NEXT 200K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0 );
Materialized view log created.
DROP SNAPSHOT LOG ON emp;

4. Create Snapshot / Materialized View

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=HR)

sqlplus scott/tiger@HR

CREATE SNAPSHOT emp
PCTFREE 15
STORAGE
(INITIAL 200K
NEXT 200K
PCTINCREASE 0)
TABLESPACE USERS
USING INDEXTB
CTFREE 0
STORAGE
(INITIAL 200K
NEXT 200K
PCTINCREASE 0)
TABLESPACE USERSINDEXTB
REFRESH FORCE
START WITH SYSDATE NEXT SYSDATE+(1/1440) /* 60 SECONDS */
AS SELECT * FROM emp@HOHR;
Materialized view created.

5. Create Synonyms

On Snapshot (Host=HR)
sqlplus scott/tiger@HR

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.

6. Refresh the Snapshot

The snapshot on the remote site must be refreshed regularly. This can be done bullet either by hand after a substantial change on the master site or in regular intervals.

Manual Refresh

On Snapshot (Host=HR)

sqlplus scott/tiger@HR

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 periodic jobs in the database (as automatic refresh jobs), the ability to run SNP background jobs must be given.

Especially, in the file init.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;

Refresh Groups
f 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 subtracting the last member */
lax => TRUE, /* delete from other group if already existing in a group */
rollback_seg => 'RB06'
);
commit;


7. Drop Snapshots and Snapshot Logs

On Master (Host=DBSERVER)

sqlplus scott/tiger@HOHR

DROP SNAPSHOT LOG ON emp;
Snapshots dropped.