6
Planning Your Replication Environment

Logging Columns in the Materializ ed View Log

When you create a materialized view log, you can add c olumns to the log when necessary. To fast refresh a materialized view, the following types of columns must be added to the materializ ed view log:

• A column referenced in the WHERE claus e of a subquery that is not part of an equi-join and is not a primary key column. These columns are called filter columns.
• A column in an equi-join that is not a primary key column, if the subquery is either many to many or one to many. If the subquery is many to one, then you do not need to add the join column to the materialized view log.

A collection column cannot be added to a materialized view log. Also, materialized view lo gs are not required for materialized views that are complete refreshed.

For example, consider the following DDL:

1) CREATE MATERIALIZED VIEW oe.customers REFRESH FAST AS

2)  SELECT * FROM oe.customers@orc1.world c
3)  WHERE EXISTS
4)    (SELECT * FROM oe.orde
rs@orc1.world o
5)     WHERE c.customer_id = o.customer_id AND o.order_total > 20000);




Notice in line 5 of the preceding DDL that three columns are referenced in the WHERE clause. Columns orders.customer_id and customers.customer_id are referenced as part of the equi-join
clause. Because customers.customer_id is a primary key column, it is logged by default, but orders.customer_id is not a primary key column and so must be added to the materialized view log. Also, the column orders.order_total i
s an additional filter column and so must be logged.

 
Therefore, add orders.customer_id<
/code> and orders.order_total the materialized view log for the oe.orders table.

 
To create the materialized view log with these columns added, issue the following statement:
CREATE MATERIALIZED VIEW LOG ON oe.orders 
  WITH PRIMARY KEY (customer_id,order_total);




If a materialized view log already exists on the oe.customers tabl
e, you can add these columns by issuing the following statement:
ALTER MATERIALIZED VIEW LOG
ON oe.orders ADD (customer_id,order_total);




If you are using user-def
ined datatypes, then the attributes of column objects can be logged in the materialized view log. For example, the oe.customers
 table has the cust_address.postal_code attribute, which can be logged in the materialized view log by issuing th
e following statement:
ALTER MATERIALIZED VIEW LOG ON oe.customers ADD (cust_address.postal_c
ode);




You are encouraged to analyze the defining queries of your plan
ned materialized views and identify which columns must be added to your materialized view logs. If you try to create or refresh a mat
erialized view that requires an added column without adding the column to the materialized view log, then your materialized view crea
tion or refresh may fail.
 





Note:  
To fast refresh a materialized view, you must add join columns in subqueries to the materialize
d view log if the join column is not a primary key and the subquery is either many to many or one to many. If the subquery is many to
 one, then you do not need to add the join column to the materialized view log.


 



See Also:  

"Data Subsetting with Materialized Views" for information about materialized views with subqueries
"Restrictions for Materialized Views with Subqueries" for additional
information about materialized views with subqueries

"Creating a Materialized View Log" for information about creating a materialized view log
 
Creating a Materialized View Environment

 
Materialized view e
nvironments can be created in several different ways and from several different locations. In most cases, you should use deployment t
emplates at the master site to locally precreate a materialized view environment that will be individually deployed to the target mat
erialized view site.
 
You can also individually create the materialized view environment by es
tablishing a connection to the materialized view site and building the materialized view environment directly.
 
Creating a Materialized View Enviro
nment Using the Replication Management Tool

 
See the Replication M
anagement tool's online help for information on using deployment templates to centrally create a materialized view environment using
the Replication Management tool.
 
See the Replication Management tool's online help for inform
ation on individually creating the materialized view environment with a direct connection to the remote materialized view site using
the Replication Management tool.
 
Figure 6-2 Fl
owchart for Creating Materialized Views
 
Text description of the illustration repln076.gif
 
Creating a Materialized View Environment
 Using the Replication Management API

 
The instructions for creati
ng a deployment template in the O
racle Database Advanced Replication Management API Reference manual for information on using deployment templates to central
ly pre-create a materialized view environment using the replication management API.
 
The instr
uctions for creating a materialized view group the Oracle Database Advanced Replication Management API Reference manual for information on individually cr
eating the materialized view environment with a direct connection to the remote materialized view site using the replication manageme
nt API.
 
Avoid
ing Problems When Adding a New Materialized View Site

 
After you have c
reated a materialized view environment with one or more materialized view sites, you may need to add new materialized view sites. You
 may encounter problems when you try to fast refresh the materialized views you create at a new materialized view site if both of the
 following conditions are true:

Materialized views at the new ma
terialized view site and existing materialized views at other materialized view sites are based on the same master table or master ma
terialized view.
Existing materialized views can be refreshed while you create t
he new materialized views at the new materialized view site.
 
The problem arises when th
e materialized view logs for the masters are purged before a new materialized view can perform its first fast refresh. If this happen
s and you try to fast refresh the materialized views at the new materialized view site, then you may encounter the following errors:<
/p>
ORA-12004 REFRESH FAST cannot be used for materialized view materialized_v
iew_
name
ORA-12034 materialized view log on materialized_view_name younger than las
t 
refresh




If you receive these errors, then the only solution is to
perform a complete refresh of the new materialized view.
 
To avoid this problem, choose one of
 the following options:

Use deployment templates to create the m
aterialized view environment at materialized view sites. You will not encounter this problem if you use deployment templates. 




See Also:  
Chapter 4, "Deployment Templates Concepts and Architecture" for information
 about deployment templates
Create a dummy materiali
zed view at the new materialized view site before you create your production materialized views. The dummy materialized view ensures
that the materialized view log will not be purged while your production materialized views are being created.
 
If you choose to create a dummy materialized view at the materialized view site, complete the following steps:


Create a dummy materialized view called d
ummy_mview based on the master table or master materialized view. For example, to create a dummy materialized view based on a
master table named sales, issue the following statement at the new materialized view site: 
CREATE MATERIALIZED VIEW dummy_mview REFRESH FAST AS 
  SELECT * FROM pr.sales@orc1.world WHERE 1=0; 



Create your production materialized views at t
he new materialized view site.
Perform fast refresh of your production ma
terialized views at the new materialized view site.
Drop the dummy materi
alized view.
 
Interoperability in an Advanced Replication Environment

 
If you p
lan to configure an Advanced Replication environment that involves different releases of Oracle at different replication sites, then
your environment must meet the following requirements:

Oracle Da
tabase 10g release 1 (10.1) master sites can only interact with Oracle release 8.1 or higher master sites.
Oracle Database 10g release 1 (10.1) materialized view sit
es can only interact with Oracle release 8.1 or higher master sites.
Oracle
 Database 10g release 1 (10.1) master sites can only interact with Oracle release 8.1 or higher materialized
view sites. 




See Also:
  
"Replication Support for Unicode"<
/a> for information about interoperability in Advanced Replication environments that use NCHAR or NVARCHAR
datatypes
 
Guidelines for Scheduled Links



A scheduled link determines how a master site propagates its deferred transaction queue to another master site, or how
 a materialized view site propagates its deferred transaction queue to its master site or master materialized view site. When you cre
ate a scheduled link, Oracle creates a job in the local job queue to push the deferred transaction queue to another site in the syste
m. When Oracle propagates deferred transactions to a remote master site, it does so within the security context of the replication pr
opagator.
 
You can configure a scheduled link to push information using serial or parallel pro
pagation. In general, you should use parallel propagation, even if you set the parallelism parameter to 1.
 
Before creating the scheduled links for a replication environment, carefully consider how you want replica
tion to occur globally throughout the system. For example, you may choose to propagate deferred transactions at intervals, with time
in between these intervals when the deferred transactions are not propagated. In this case, you must decide how often and when to sch
edule pushes. Alternatively, if you want to simulate real-time (or synchronous) replication, then you may want to have each scheduled
 link continuously push a master site's deferred transaction queue to its destination.
 
Also,
you may want to schedule pushes at a time of the day when connectivity is guaranteed or when communications costs are lowest, such as
 during evening hours. Furthermore, you may want to stagger the scheduling for links among all master sites to distribute the load th
at replication places on network resources.
 



See Also:  
"Serial and Parallel Propagation" for more information about issues related to serial and parallel propagation
 
Scheduling Per
iodic Pushes

 
You can schedule periodic intervals between pushes of a s
ite's deferred transaction queue to a remote destination. Examples of periodic intervals are once an hour or once a day. To do so, yo
u can use the DBMS_DEFER_SYS.SCHEDULE_PUSH procedure and specify the settings shown in Table
 6-3.
 
 Table 6-3 Settings to Schedule Periodic Pushes



 SCHEDUL
E_PUSH Procedure Parameter
 Value


 
delay_seconds

 
0

 
interval
 
An appropriate date expression; for example
, to specify an interval of one hour, use 'sysdate + 1/24'
 
You can also use the Replication Management tool to schedule periodic pushes. To do so, set Delay Seconds to the default value o
f 0 when configuring a scheduled link in any of the following places:

The Replication Management tool's Setup Wizard
The Edit Push Schedule dial
og box
 
Then configure the interval (the "then push every" control) to push the deferred
 transaction queue periodically.
 
The following is an example that schedules a periodic push o
nce an hour:
BEGIN
   DBMS_DEFER_SYS.SCHEDULE_PUSH (

      destination => 'orc2.world',
      interval => 'SYSDATE + (1/24)',
      next_dat
e => SYSDATE,
      delay_seconds => 0);
END;
/






See Also:  

"Delay Seconds"<
/a> for more information about setting delay seconds

Oracle Database Advanced Replication Management API Refere
nce for information about the DBMS_DEFER_SYS.SCHEDULE_PUSH procedure
The Replication Management tool online help for information about using this tool
 
Sched
uling Continuous Pushes

 
Even when using Oracle's asynchronous repl
ication mechanisms, you can configure a scheduled link to simulate continuous, real-time replication. To do so, use the DBMS_DE
FER_SYS.SCHEDULE_PUSH procedure and specify the settings shown in Table 6-4.
  
 Table 6-4 Settings to Simulate Continuous Push



 SCHEDULE_PUSH
 Procedure Parameter
 Value



 
delay_seconds
 
1200

 
interval
 
Lower than the delay_seconds sett
ing

 
parallel
ism
 
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 durat
ion of the entire interval. If the deferred transaction queue has no transactions to propagate for the amount of time specified by th
e delay_seconds parameter, then Oracle releases the resources used by the job and starts fresh when the next job queue p
rocess becomes available.
 
If you are using serial propagation by setting the parallelis
m parameter to 0 (zero), then you can simulate continuous push by reducing the settings of the delay_seconds and
interval parameters to an appropriate value for your environment. However, if you are using serial propagation, simulati
ng continuous push is costly when the push job must initiate often.
 
The following is an examp
le that simulates continual pushes:
BEGIN
   DBMS_DEFER_SYS.SCHEDULE_PUSH
 (
      destination => 'orc2.world',
      interval => 'SYSDATE + (1/144)',
      next_date => SYSDATE,
      parallelism => 1,
      execution_seconds =
> 1500,
      delay_seconds => 1200);
END;
/






See Also:  

"Delay Seconds"
 for more information about setting delay seconds
"Se
rial and Parallel Propagation" for more information about issues related to serial and parallel p
ropagation
Oracle Database Advanced Replication Management API Reference for information about the DBMS_DEFER_SYS.SCHEDULE_PUSH procedure
 
Guidelines for Scheduled Purges of a Deferred Tr
ansaction Queue

 
A scheduled purge determines how a master site or
materialized view site purges applied transactions from its deferred transaction queue. When you use the Replication Management tool'
s Setup Wizard to set up a master site or materialized view site, Oracle creates a job in each site's local job queue to purge the lo
cal deferred transaction queue on a regular basis. Carefully consider how you want purging to occur before configuring the sites in a
 replication environment. For example, consider the following options:

You can synchronize the pushing and purging of a site's deferred transaction queue. For example, you can configure continuous
pushing and purging of the transaction queue. This type of configuration can offer performance advantages because it is likely that i
nformation about recently pushed transactions is already in the server's buffer cache for the corresponding purge operation.
When a server is not CPU bound, you can schedule continuous purging of the deferred tran
saction queue to keep the size of the queue as small as possible.
For servers th
at experience a high-volume of transaction throughput during normal business hours, you can schedule purges to occur during off-peak
hours if you can store an entire day's deferred transactions.
 
Scheduling Periodic Purges

 
You can schedule periodic purges of a site's deferred transaction queue. Examples of periodic purges are purges that occu
r once a day or once a week. To do so, you can use the DBMS_DEFER_SYS.SCHEDULE_PURGE procedure and specify the settings
shown in Table 6-5.
 
 Table 6-5 Settings to Schedule Periodic Purges

<
/table>
You can also use the Replication Management tool's Setup Wizard, or the Purge sub tab of
the Schedule tab on the Administration property sheet to schedule periodic purges. To do so, set Delay Seconds to the default value o
f 0 (zero). Then configure the interval (the "then purge every" control) to purge the deferred transaction queue.
The following is an example that schedules a periodic purge once a day:

BEGIN
   DBMS_DEFER_SYS.SCHEDULE_PURGE (
      next_date => SYSDATE,

     interval => 'SYSDATE + 1',
      delay_seconds => 0);
END;
/
<
/pre>






 SCHEDULE_PURGE Procedure Parameter
 Value

 
delay_seconds
 
0

 
interval
 

An appropriate date expression; for example, to specify an interval of one day, use 'sysdate + 1'
 

 



See Also:  

Oracle Database Advanced Replication Management API Reference for information abo
ut the DBMS_DEFER_SYS.SCHEDULE_PURGE procedure
The Replication Manag
ement tool online help for information about using this tool
 
Scheduling Continuous Purges


 
To configure continuous purging of a site's deferred transaction queue, you can use the DB
MS_DEFER_SYS.SCHEDULE_PURGE procedure and specify the settings shown in Table 6-6.
 
 Tab
le 6-6 Settings to Schedule Periodic Purges
<
/table>
You can also use the Replication Management tool to configure continuous purge. To do so,
 on the Purge sub tab of the Schedule tab on the Administration property sheet, set Delay Seconds to 500,000 and set interval (the "t
hen purge every" control) to a value less than the Delay Seconds setting.
The following is an
 example that simulates continuous purges:
BEGIN
   DBMS_DEFER_SYS.SCHEDU
LE_PURGE (
      next_date => SYSDATE,
      interval => 'SYSDATE + (1/144)',
      purge_method => dbms_defer_sys.purge_method_quick,
      delay_seconds => 500000);
END;
/





 SCHEDULE_PURGE Procedure Par
ameter
 Value


 
delay_seconds
 

500000

 
inter
val
 
Lower than the delay_seconds setting


 
purge_method<
/p>
 
dbms_defer_sys.purge_method_quick
 

 






See Also:  

"Delay Seconds" for more information about setting delay seconds<
/li>
Oracle Database Advanced Replication Management API Reference for information about the DBMS_DEF
ER_SYS.SCHEDULE_PURGE procedure
The Replication Management tool online hel
p for information about using this tool
 
<
font face="Arial, Helvetica, sans-serif" color="#330099">Serial and Parallel Propagation

<
/a> 
When you create the scheduled links for a replication environment, each link can asynchronously propagate changes
to a destination using either serial or parallel propagation. Before you configure your replication environment, decide whether you w
ant to use serial propagation or parallel propagation.

With seri
al propagation, Oracle propagates replicated transactions one at a time in the same order that they are committed on the source syste
m. To configure a scheduled link with serial propagation, set the parallelism parameter to 0 (zero) in the DBMS_DE
FER_SYS.SCHEDULE_PUSH procedure. Or, using the Replication Management tool, set the Parallel Propagation Processes control to
0 in the Edit Push Schedule dialog box.
With parallel propagation, Oracle propag
ates replicated transactions using multiple parallel streams for higher throughput. When necessary, Oracle orders the execution of de
pendent transactions to preserve data integrity. To configure a scheduled link with parallel propagation, set the parallelism parameter to 1 or higher in the DBMS_DEFER_SYS.SCHEDULE_PUSH procedure. Or, using the Replication Management tool,
 set the Parallel Propagation Processes control to 1 or higher in the Edit Push Schedule dialog box. Typically, you should use parall
el propagation. 




See Also:  

"Parallel Prop
agation"
Oracle Database Advanced Replication Management API Reference
 for information about the DBMS_DEFER_SYS package
The Replicati
on Management tool online help for information about using this tool


 
Deployment Templat
es

 
If you plan to include materialized view sites in your replicat
ion environment, then consider using deployment templates to create the replicated objects at the materialized view sites.
 



See Also:  
Chapter 4, "Deployment Templates Concepts and Architecture" for information about
 deployment templates
 
Preparing Materialized View Sites for Instantiation of Deployment Templates

Network Connectivity


 
As with all replication environments, network connectivity is a key componen
t in Advanced Replication. Verify that the remote materialized view site has a proper SQL*Net, Net8, or Oracle Net connection to the
target master site.
 



See Also:  
Oracle
 Net Services Administrator's Guide for information about setting up an Oracle network connection
 
Database Versio
n

 
The materialized view site must have an Oracle release 8.1.5 or high
er database to instantiate a deployment template. If your materialized view site does not meet the database version requirements, the
n you need to upgrade your database at the materialized view site before instantiating a deployment template.
 
Materialized View Site Setup<
/h4>

 
Each materialized view site needs several users that have special privileges
to support a materialized view site. In addition to having the administrative privileges, these users also participate in the propaga
tion and refreshing of data.
 
Materialized view site setup also includes scheduling several au
tomated jobs to handle the automatic refreshing of the materialized view (optional) and the purging of the deferred transaction queue
.
 
You can set up your materialized view site with:

Replication Management Tool: You can connect to the remote materialized view
 site with the Replication Management tool and use the Setup Wizard. 




See Also:  
The Replication Management tool's onlin
e help for instructions on setting up your materialized view site with the Replication Management tool
Replication Management API: Using the replication ma
nagement API to setup your materialized view site is an ideal solution when you are not able to connect to the remote materialized vi
ew site with the Replication Management tool. When you build a SQL script containing the API calls to setup your materialized view si
te, you can also add the DDL and API calls to complete the remaining preparation (such as creating any necessary schemas, database li
nks, and rollback segments, as described in the following three sections). The script that you create should be distributed with the
offline instantiation file and executed before the offline instantiation file.