Oracle Consulting Oracle Training Oracle Support Development
Home
Catalog
Oracle Books
SQL Server Books
IT Books
Job Interview Books
eBooks
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
 Phone
 800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

Privacy Policy

 

 

 

   
  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

 

Linux Oracle commands syntax poster

ION Oracle tuning software

Oracle data dictionary reference poster



Oracle Forum

BC Oracle consulting support training

BC remote Oracle DBA   

 

   

 Copyright © 1996 -2016 by Burleson. All rights reserved.


Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks