Materialized View Replication

This post is for my friend Gerry, who - as a newby to Oracle - has to support a replicated environment. Gerry is my successor at Kanton Luzern. Management didn’t look for a new DBA because they said it’s hopeless to find somebody. Instead they decided to give somebody inhouse the opportunity. Gerry is highly motivated to learn Oracle. He took the chance.

Kanton Luzern uses LiveLink for web content management. LiveLink websites are stored in an Oracle database. Kanton Luzern hosts 144 websites.
There is a staging server where content is being added/edited and a live server where websites are accessed from the internet. New content is replicated from stage to live.

Whenever a content manager wishes to publish new or edited content to the live webserver he may trigger replication just by clicking on a button inside the content management tool. This is not a standard feature of LiveLink Content Manager. I wrote an extension for Content Manager, and a stored procedure to control replication.

Materialized View Replication has been introduced with Oracle 8i. Back then it was called Snapshot Replication. For a quick overview of materialized views read Duncan Davies’ post.
To set up materialized view replication is rather simple. But it takes a few steps.

hpfuchs@hplnbk:~> sqlplus /nolog

SQL*Plus: Release 11.1.0.6.0 - Production on Sun Jan 13 10:18:18 2008

Copyright (c) 1982, 2007, Oracle. All rights reserved.
SQL> connect hpfuchs/*****
Connected.

SQL> CREATE TABLE tab1 AS SELECT * FROM dict;

Table created.

Step 1 - Materialized View Log at Master Site
All changes in a base table are written to its materialized view log.

SQL> CREATE MATERIALIZED VIEW LOG ON tab1;
create materialized view log on tab1
*
ERROR at line 1:
ORA-12014: table ‘TAB1′ does not contain a primary key constraint

Ups! Materialized view log can’t be created on tables with no primary key. Let’s create primary key for tab1.

SQL> desc tab1;
Name Null? Type
—————————————– ——– —————————-
TABLE_NAME VARCHAR2(30)
COMMENTS VARCHAR2(4000)

SQL> ALTER TABLE tab1 ADD CONSTRAINT pk_tab1 PRIMARY KEY (table_name);

Table altered.

SQL> CREATE MATERIALIZED VIEW LOG ON tab1;

Materialized view log created.

Step 2 - Set up Materialized View Site
Most likely the materialized view site is another Oracle database. I have only one database on my notebook. Therefore I just create another user who is the owner of the replication site.

SQL> connect system/*****
Connected.
SQL> CREATE USER otherdb IDENTIFIED BY ***** DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;

User created.

SQL> GRANT connect, resource, create materialized view, create database link TO otherdb;

Grant succeeded.

Step 3 - Database Link
To access objects in another database you need a database link between the two databases. Because I have only one database on my notebook, I will create a loopback database link. Omit the @{connect identifier} - in my case: @orcl - to create a real database link.

SQL> connect otherdb/*****
Connected.

SQL> CREATE DATABASE LINK orcl.kt.lunet.ch@orcl CONNECT TO hpfuchs IDENTIFIED BY ***** USING ‘orcl’;

Database link created.

Step 4 - Materialized View

SQL> CREATE MATERIALIZED VIEW mv1 REFRESH FAST AS SELECT * FROM tab1@orcl.kt.lunet.ch@orcl;

Materialized view created.

Materialized views can be indexed.

SQL> CREATE INDEX ix_mv1_objecttype ON mv1 (table_name);

Index created.

Step 5 - Refresh Group
Materialized views can be grouped together into refresh groups. Refresh groups are replicated as a whole. My replication interval is set to one hour. Materialized view replication uses DBMS_JOBS to schedule its intervals. To switch off all scheduled replication activity set JOB_QUEUE_PROCESSES to 0 at the replication site. Make sure all non-replication jobs use DBMS_SCHEDULER. You better check V$JOBS before changing that parameter to 0…

SQL> BEGIN
dbms_refresh.make(
name => ‘mv1_refgroup’,
list => ‘MV1′,
next_date => sysdate,
interval => ’sysdate + 1′,
implicit_destroy => true,
lax => true);
END;
/

PL/SQL procedure successfully completed.

Step 6 - Refresh At Will
The following command refreshes group “mv1_refgroup”.

SQL> exec dbms_refresh.refresh(name => ‘mv1_refgroup’);

PL/SQL procedure successfully completed.