 |
|
Oracle Replication Tips by Burleson |
Creating Oracle
Replication Using OEM
selecting create. The master database SID is NAVDB, which
is also the Net Service Name in the tnsnames.ora file
(Figure 4.5).
Figure
4.5
Creating a public database link
Be sure to select the public checkbox. Selecting the “SHOW SQL”
button will cause OEM to display the SQL that will be used to
execute the command. Select OK to execute the command and create
the database link. OEM displays a dialog box to confirm that the
link was created (Figure 4.6).
Figure
4.6
Database Link Creation Dialog Box
Next, log on as REPADMIN to create the private database link. To
log onto OEM as REPADMIN, you will need to grant REPADMIN the system
privilege “SELECT ANY DICTIONARY”. Navigate back to the Database
Link folder, right-click, and select create. Create the private
database link from REPADMIN to REPPROXY (Figure 4.7) using the same
link name as the private link created above.
Figure
4.7
Create a Private Database Link
Again, OEM displays a dialog box confirming the creation of the
link. Next, test the link by expanding the Database Link folder,
selecting the database link under the REPADMIN user. Select the
“Test” button and OEM will verify that the link is active.
Creating Oracle
Replication Using OEM
Step 6 - Create the Master Replication Group
Now log onto the Master database (navdb)
as REPADMIN. Again, you are required to grant “SELECT ANY
DICTIONARY” to REPADMIN in order to log on with OEM. Expand the
Distributed tab and the Advanced Replication tab.
This is the area of OEM that you will use to perform most of your
replication maintenance. Expand the Multi-master
Replication folder and you will find the Master Groups folder.
Right-click on the folder and select Create.
In the Name field enter the name of this replication group,
REP_GP1. Select the Objects tab and select ADD. Here, we are going
to add the book
table from the PUBS schema. Use the schema dropdown menu to select
the PUBS schema. Check the “Tables” check box and a list of tables
is
provided. Select the book
table and press the “ADD” button. The book
table is now listed in the “Selected Objects” text area (Figure
4.8).
Figure
4.8 Selecting Replication Objects in OEM
Selecting OK takes you back to the Objects tab, which now displays
the book
table. Selecting the “Show SQL” button shows you the series of
commands that this step will execute. This one step will generate
the master replication group, add the book
table to the group, generate the replication support, and finally
resume replication activity (Figure 4.9).
Creating Oracle
Replication Using OEM
Figure
4.9
OEM Create Master Group
Selecting “Create” will cause OEM to execute the commands and create
the master replication group. Once created, you can expand the
master group folder and the REP_GP1 tab to see the PUBS.BOOK table.
Selecting the REP_GP1 tab will display the status page.
As you can see, Oracle has used the graphic capabilities of OEM to
combine multiple steps for simultaneous execution. You can
highlight and copy the commands out of the “Show SQL” text field to
save as a file, which can be very helpful when problems arise.
At this point, we are going to deviate from the steps introduced in
the first part of this chapter and add the sales
and store
tables in the PUBS schema to the master group. Before we add
additional tables to the replication group, we need to stop
replication activity.
From the REP_GP1 status page (Figure
4.10), select the “Submit Stop Request” button in the
Replication Activity section. Notice that the status changed from
Running (Normal), to Stopped (Quiesced). Select the Objects tab and
then select the Add button at the bottom. If OEM asks you to set an
“alternate key column” on the sales
table, then you forgot to set the primary key. By selecting a
column or columns, you are defining a key for use in replication
that cannot
be used to create a UMV.
Select the Apply button to add the new tables. Next, highlight the
new tables and press the “Generate Replication Support” button.
Return to the General page and restart replication activity. The status
should return to Running (Normal) (Figure 4.10).
Figure
4.10
Restarting Replication Support
Now, all the tables to be replicated in the PUBS schema are
generating replication support and we can create the Updatable
Materialized Views in the mydb
database.
Step 7 - Create the Updatable Materialized Views
Now that we have the master replication running normally, we are
ready to create the updateable materialized views.
Log onto the UMV site as REPADMIN. In our case, that is the
mydb
database. Again, expand the Distributed tab, the Advanced
Replication tab, and in this case, the Materialized View Replication
tab (Figure 4.11).
Figure
4.11
OEM Materialized View Replication
As with creating UMVs manually, the first step using OEM is to
create a materialized view group. Right-click on the Materialized
View Groups folder and select “Create using Wizard”. The first
thing the wizard asks for is the database link. Select the “All”
checkbox to see the available database links. Select the one you
created at the start of this section, in this case
navdb.world
(Figure 4.12).
Figure
4.12 Materialized View
Group Wizard Link to Master
Selecting the “Next” button will take us to the Master Groups page.
Notice that the master replication group we created on the master
database, navdb,
is listed. The wizard polled the dba_repcat
data dictionary view across the link to locate the available master
replication groups.
If you received an error and the text area does not display the name
of your master group, you may need to grant REPPROXY on the master
database the “SELECT ANY DICTIONARY” system grant. Stop the wizard
and restart it to display the list of master groups (Figure 4.13).
Figure
4.13
Materialized View
Group Wizard Master Groups
Leave the Asynchronous radio button set and select the master group,
in our case REP_GP1. Once you select the “Next” button, the wizard
will use the database link to get a list of replication objects
contained in the selected group. In this case, it is the three
tables that are replicated from the PUBS schema (Figure 4.14).
Figure
4.14
Materialized View
Wizard Select Master Objects
Since we are replicating all three tables to the mydb
database, use the right-arrow to move the three tables to the
Materialized View Group Objects side.
There is no requirement that the UMV site replicate all the objects
in the master group. It is not uncommon for some sites to replicate
a subset of the objects. As with all parts of replication, more is
not better, if you don’t need it, don’t replicate it.
As
each table is moved, the wizard gathers more information across the
database link. Selecting Next will display the Materialized View
Defaults page.
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
|
| 
