The Advantages of Oracle8 and the Replication Manager

It might seem that Oracle’s replication strategy would be tedious for the DBA who needed to run an endless array of PL/SQL packages with many different parameters. Oracle has mercifully hidden this from the casual user with the Replication Manager. This product is a series of GUI screens that allow for intuitive drag-and-drop manipulation of a replication scheme.

The Replication Manager issues the preceding PL/SQL and new C routines to perform the same tasks, but instead hides this mess from the casual user that instead uses intuitive GUI tools to configure a replicated environment, as in Figure 16.13.

Figure 16.13.  The Replication Manager in version 8 of Oracle shelters the user from SQL, C, and PL/SQL tasks.

The Replication Manager has a Setup Wizard that enables you to create the replication links and needed privileges to configure a replicated environment. Along with this is the Snapshot Wizard, which enables users to point-and-click while creating snapshot groups, snapshot logs, and snapshots.

One important feature this product offers is a validate procedure that validates an existing configuration. This is valuable because with manual validation and use of PL/SQL, a user at another replicated node can invalidate a portion of the replication strategy without us being aware of it. This forces someone concerned with the overall replication state to create a set of validation routines.

The Snapshot Wizard can also handle primary key snapshots that enable you to reorganize a master table without blocking other sites in performing a fast refresh. It also saves time because we might need to propagate only new values in the primary key.

The new deferred constraints feature speeds up replication refresh by not checking referential integrity on the remote site until the end of a refresh. This speeds up the process but might result in a large listing of errors at the end of a forced or automatic refresh.

Another advantage that comes with using Oracle8 replication is the use of fine grain quiesce, which in Oracle-speak means that you can manage different replication groups down to the column level. For instance, if you have an engineering and an accounting group both looking at your Product table, with this level of replication control, you can define further columns or tables for replication to your engineering group and not your accounting group. This can be useful if your engineering group needs technical documents and specifications that go with each product; whereas your sales group only needs the product code and the description. In this case, whenever you have a sale, we wouldn’t need to move down all the information we had on the product sold.

Oracle was forced to move to internal triggers in Oracle8 simply because traditional PL/SQL code is not as fast. Oracle needed to improve performance so now they offer C modules that are compiled directly into the Oracle kernel.

I was puzzled when Oracle introduced replication without these changes to the Oracle kernel. By simply using PL/SQL, you are creating a flexible, yet flimsy, layer for your replication logic. The internal C libraries are always faster than PL/SQL, which is, at best, interpreted code as shown in Figure 16.14.

Figure 16.14.  PL/SQL needs to be interpreted before CPU execution; C executables do not.

Oracle is now using a new distributed transaction protocol that ensures that no transaction is ever applied twice, which is what a two-phase commit is defined as. This protocol reduces network traffic and has the same user transparency as the two-phase commit. Oracle actually claims that this protocol eliminates the two-phase commit. I think I will stay out of that war!

Summary

On our long voyage through the universe of replication, we have seen the strategy of Oracle’s replication offering. It appears Oracle has made the correct move by rewriting much of the replication logic in the form of C modules that link to the kernel. This strategy adds speed but doesn’t throw away the detail to which a replication DBA can configure a replicated site. Flexibility was the power of the PL/SQL packages that we learned.

It is important to remember that replication takes place between physically separate machines. Two instances of Oracle can perform perfect replication if they are on the same cluster by using the Oracle Parallel Server and not replication. There are also tools that give highly available database server functionality to a system that can be purchased from a third-party along with a hardware server. Don’t consider replication until these other alternatives are ruled out. Replication is a whole layer of complexity that affects every database that is being replicated; it is not a trivial task that is performed by a black-box. In most cases, replication demands a great deal of design and planning before it is implemented and a vigilance to maintain the replicated databases after implementation. Beware before you enter its many hallowed halls!