||Oracle Replication Tips by Burleson
Oracle OEM Replication
Our first task here is to create a Refresh Group. In the mydb
database, there currently are no refresh groups defined, so the drop
down box is grayed out. If your database contains refresh groups, then
they will be displayed in the drop down box. It is recommended that you
create a new refresh group rather than mix the UMVs with other
It is also recommended that you separate the UMVs from any one-way
on your database. Although you can place over one hundred
in a refresh group, remember that one process refreshes all members
of the group and holds locks on tables during the refresh process.
Experience shows that a number of smaller refresh groups works best
because the task is spread across more job processes. The only
requirement you must consider when grouping objects in refresh
groups is that they may depend on each other and they must refresh
together to ensure
Selecting the Create button will bring up the Create Refresh Group
page (Figure 4.15).
Create Refresh Group
The refresh group is named REF_GROUPA. Make sure that you select the
REPADMIN schema from the Schema combo-box.
If you want the refresh group to
be deleted when the last object is removed, select the checkbox.
Change the refresh interval by pressing the Set button. Here, the
refresh interval is set to
1 min. Because this refresh group will support UMVs, select the
“Push changes…” and “Continue
to refresh…” checkboxes. Select the “Show SQL” button to see the
actual SQL used to create the refresh group.
If you select the Materialized View tab at the top of the window,
you can see all available materialized views. However, you will not
find the views in the master replication group because they do not
yet exist in the mydb
database. So, without adding Materialized Views,
select Create. OEM will display a dialog box showing that the
refresh group was created and you will be returned to the
Materialized View Defaults page (Figure 4.16).
Materialized View Defaults
Select the first three check boxes. If you want to explicitly
define a storage clause, check the fourth check box and then the
Edit button to define the storage parameters.
Selecting Next takes you to Step
5 of the wizard and allows you to customize any of the UMVs before
creation. As you select each name, the checkboxes on the right will
change to show the current setting.
If you are unable to set the Fast Refresh check box, it is because
you forgot to create the materialized view log on that table. You
will need to cancel the wizard, drop the objects in mydb,
and remove the table from the Master Group in navdb.
Then, create the materialized view log and add the table back to the
master group. Restart the wizard on mydb
Materialized View Customization
This page of the wizard also allows you to incorporate a WHERE
clause in the updatable materialized view to restrict the rows
included in the view.
Selecting Next takes you to the last step. Check the box in the
center of the window to record a script of what OEM is about to
execute. The script is important because if the creation fails, you
will use it to locate problems.
Selecting Finish will produce a list of the objects that will
created (Figure 4.18).
Rollup of Objects Being Created
When you select OK, OEM will create all the objects. After
finishing, you will have a chance to save the script to a file.
Again, this is important when tracking down any problems, and it is
strongly suggested that you save the script (Figure 4.19).
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
Download your Oracle scripts now:
definitive Oracle Script collection for every Oracle professional DBA