 |
|
Oracle Replication Tips by Burleson |
Create the Oracle
master replication group REP_GP1
With the master site now tracking changes to the PUBS base tables,
we can create the updatable materialized views on the mydb
(remote) database.
Create the Oracle
Updatable Materialized View at the Remote Database
IMPORTANT: Log
onto the UMV site as REPADMIN, not as PUBS.
Step 8.1 - Create a Refresh Group
We first need to create a refresh group on the remote database so
that all three UMVs will refresh together. Notice that we set
push_deferred and refresh_after_errors
to TRUE. Setting push_deferred_rpc tells the refresh group that
updates will be pushed back to the master table. Setting
refresh_after_errors to true will allow the UMVs to continue to
refresh, even if there are errors in the DEFERROR queue.
BEGIN
DBMS_REFRESH.MAKE(
name => '"REPADMIN"."GROUPA"',
list => '',
next_date => SYSDATE,
interval => '/*1:Mins*/ sysdate +
1/(60*24)',
implicit_destroy => FALSE,
lax => FALSE,
job => 0,
SEE CODE DEPOT FOR FULL SCRIPT
purge_option => NULL,
parallelism => NULL,
heap_size => NULL);
END;
/
Step 8.2 – Create the Materialized View Group
Now, create the materialized view group in the MYDB database. In
order for UMVs to push changes back to
Create the Oracle
Updatable Materialized View at the Remote Database
the master tables, the UMVs must belong to a materialized view
group.
BEGIN
DBMS_REPCAT.CREATE_MATERIALIZED VIEW_REPGROUP(
gname => '"REP_GP1"',
master => 'NAVDB.WORLD',
propagation_mode => 'ASYNCHRONOUS');
END;
/
This creates a replication group called REP_GP1 on the UMV database
that uses the mydb.world
database link. It will propagate data asynchronously.
Step 8.3 - Create the BOOK Updatable Materialized View
The next step is to create the actual materialized view on the
remote site. Here, we create the BOOK materialized view:
CREATE MATERIALIZED VIEW "PUBS"."BOOK"
REFRESH FAST FOR UPDATE
AS SELECT * FROM "PUBS"."BOOK"@MYDB.WORLD c
In this CREATE MATERIALIZED VIEW
statement, we want to fully qualify the table names, since we are
creating a UMV in the PUBS schema as the REPADMIN user. We could
have created and used private or public synonyms, instead of fully
qualifying, if that is what we preferred to do.
Create the Oracle
Updatable Materialized View at the Remote Database
We could also have placed restrictions on the columns in the UMV by
specifying which columns to include, or restricting the rows with a
WHERE clause. Here is an example of that type of materialized view:
CREATE MATERIALIZED VIEW "PUBS"."BOOK"
REFRESH FAST FOR UPDATE
AS SELECT isbm_number, name, publisher
FROM "PUBS"."BOOK"@MYDB.WORLD c
Step 8.4 – Add the Book UMV To the Refresh Group
Since we want the UMVs to replicate together, we add the BOOK
updatable materialized view to the refresh group GROUPA.
BEGIN
DBMS_REFRESH.ADD(
name => '"REPADMIN"."GROUPA"',
list => '"PUBS"."BOOK"',
lax => TRUE);
END;
/
Step 8.5 - Update The UMV Replication Group
Now, we need to add the BOOK updatable
materialized view to the materialized view replication group REP_GP1
that we created earlier. This creates the replication support
needed for two-way replication with the master site. The BOOK
updatable materialized view is now replicating with the book
base table on the master database.
BEGIN
DBMS_REPCAT.CREATE_MATERIALIZED VIEW_REPOBJECT(
gname => '"REP_GP1"',
Create the Oracle
Updatable Materialized View at the Remote Database
sname => '"PUBS"',
oname => '"BOOK"',
SEE CODE DEPOT FOR FULL SCRIPT
min_communication => TRUE);
END;
/
Step 8.6 - Add Other UMVs
Now follow steps 8.3 through 8.5 to add the other two updatable
materialized views (Sales and Store).
CREATE MATERIALIZED VIEW "PUBS"."SALES"
REFRESH FAST FOR UPDATE
AS SELECT * FROM
"PUBS"."SALES"@NAVDB.WORLD;
CREATE MATERIALIZED VIEW "PUBS"."STORE"
REFRESH FAST FOR UPDATE
AS SELECT * FROM
"PUBS"."STORE"@NAVDB.WORLD;
BEGIN
DBMS_REFRESH.ADD(
name => '"REPADMIN"."GROUPA"',
list => '"PUBS"."SALES"',
lax => TRUE);
END;
/
BEGIN
DBMS_REFRESH.ADD(
name => '"REPADMIN"."GROUPA"',
list => '"PUBS"."STORE"',
lax => TRUE);
END;
This is an
excerpt from Oracle Replication By Rampant TechPress (only $19.95).
You can click here to order a copy and get instant access to the code
depot:
http://www.rampant-books.com/book_2003_2_replication.htm
|
Download your Oracle scripts now:
www.oracle-script.com
The
definitive Oracle Script collection for every Oracle professional DBA
|
| 
