 |
|
Oracle Replication Tips by Burleson |
Create Oracle
replication
Database Links
You should get the letter X in the D column if the link worked:
D
X
Step 5.3 - Create a private database link
Lastly, create a private database link from PUBS to REPPROXY.
connect pubs/pubs@mydb
create database link navdb.world
connect to repproxy identified by repproxy;
Next, check the link.
select * from dual@navdb.world;
At this point, Oracle recommends that Replication Manager (Oracle8i)
or Oracle Enterprise Manager OEM (Oracle9i) be used to set up
replication. Interestingly enough, Oracle Support does not
recommend using OEM to create replication. This is because it is
difficult to insure that all OEM executions are successful, so it is
suggested that you perform a few more steps manually before going to
OEM. It is important to know exactly what OEM is executing if there
is a problem.
Later, we will rebuild the replication using OEM.
Create the Oracle replication Materialized View Log
A materialized view log must be created on each of the UMV replicated
tables. Here is an example of the use of the CREATE MATERIALIZED
VIEW LOG
command to create an
Materialized View
log:
create materialized view log on pubs.store
tablespace users
with primary key
including new values;
Before you create the Materialized View
log, you will need to define a primary key for the sales
table. If you have not, you will see the following error message:
create materialized view log on pubs.store
*
ERROR at line 1:
ORA-12014: table 'STORE' does not contain a primary key constraint
Create the Oracle Master Replication Group
Each UMV must be part of a master replication group and must have an
identifiable primary key. If you try to create a UMV without a primary
key being defined, it will fail.
If the UMV does not have a defined primary key, a materialized view
will be created instead of a UMV. This is a special kind of
materialized view, as it can be updated. The data changes made to
the materialized view will be lost with every refresh of new data
from the master, effectively resulting in a one-way system of
replication.
The primary key may be defined from a single column or multiple
columns. If there is no set of columns that defines a primary key,
then it is common practice to establish a pseudo (or surrogate)
primary key for that table (such as a sequence number). The
sales
table happens to have a column that can be defined as a primary key,
the ORDER_NUMBER column.
Cr_master_rep_gp.sql creates the master replication group REP_GP1
and adds the store
table. It then generates replication support for the STORE table
object. Finally, it starts replication support for the REP_GP1.
This is run on the master site.
Create the Oracle master replication group REP_GP1
Use the dbms_repcat
stored procedure called create_master_repgroup
to create the master replication group. This group must be configured
before UMV replication will be possible.
connect repadmin/repadmin@navdb
begin
dbms_repcat.create_master_repgroup(
gname => '"REP_GP1"',
qualifier => '',
group_comment => '');
end;
/
Step 7.2 - Add the STORE table from the PUBS schema to the master
replication group
Now, we need to use the dbms_repcat procedure called
create_master_repobject
to add the store
table to the master replication group we just created.
begin
dbms_repcat.create_master_repobject(
gname => '"REP_GP1"',
type => 'TABLE',
oname => '"STORE"',
sname => '"PUBS"');
end;
/
Step 7.3 - Generate replication support for the STORE table object
Once we have added the table to the master replication group, we
need to generate replication support for it.
Create the Oracle
master replication group REP_GP1
dbms_repcat package provides the facilities to do that. This command
loads the object into the internal tables and creates the functions and
procedures needed for replication support.
begin
dbms_repcat.generate_replication_support(
sname => '"PUBS"',
oname => '"STORE"',
type => 'TABLE',
min_communication => TRUE);
end;
/
At this point, check the view DBA_REPCATLOG for errors.
Select count(*) from dba_repcatlog;
This view should be empty.
If dba_repcatlog is empty, start replication activity on the master site. If
dba_repcatlog
is not empty, then wait and recheck. When the database has created
the replication support for the table, the dba_repcatlog
view should be empty.
Step 7.4 - Add the remaining two tables to the replication group
If everything has gone according to plan, the store
table is now
being replicated. Let’s repeat this process for the book
and sales
tables as well:
connect repadmin/repadmin@navdb
begin
Create the Oracle
master replication group REP_GP1
dbms_repcat.create_master_repobject(
gname => '"REP_GP1"',
type => 'TABLE',
oname => '"BOOK"',
sname => '"PUBS"',
copy_rows => TRUE);
end;
/
SEE CODE DEPOT FOR FULL SCRIPT
begin
dbms_repcat.generate_replication_support(
sname => '"PUBS"',
oname => '"SALES"',
type => 'TABLE',
min_communication => TRUE);
end;
/
Notify the Oracle
master (NAVDB) database to track activity on the REP_GP1 objects by
resuming replication activity
We are all set up and ready to replicate, so let’s do it. From the
master database, use the resume_master_activity procedure within
dbms_repcat to start the replication process:
begin
dbms_repcat.resume_master_activity(
gname => '"REP_GP1"');
end;
/
The master site (NAVDB) is now waiting for the UMV site to begin
replication. As changes are made to the UMV or to the master table,
they will be propagated as required.
The view dba_repgroup can be used to check the status of the replication groups that you
create. After you have created the replication groups and generated
the replication support for various tables within that group, you
should query the dba_repgroup
table to insure that REP_GP1 status field is set to “Normal” as seen
in this example:
SQL> select sname, master, status from dba_repgroup;
SNAME M STATUS
------------------------------ - ---------
REP_GP1 Y NORMAL
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
|
| 
