With this query, the business analysts can track specific traits relating to the sale of these Star Trek trinkets. If, for instance, it’s found that people in their 60s purchased the largest amount, commercial time can be bought for reruns of the Golden Girls; whereas, if only young men liked the Star Trek trinkets, advertising money might better be spent during a football game.

Snapshot Logs

As mentioned before, Oracle uses snapshot logs in the fast option when refreshing a snapshot. Snapshot logs help in a situation where only a small percent of the rows of a table are changed per refresh; we really don’t want to recopy all the rows to our snapshot from the master table. The way Oracle does its housekeeping to keep track of only changed rows is accomplished by the use of snapshot logs.

Whenever you have an update, insert, or delete of the master table, the snapshot log records the activity. This obviously takes time and might slow down an OLTP application. In cases where the penalty is too great, consider nightly or weekend complete-style snapshots.

Here is the syntax to create a snapshot log:

Create Snapshot Log On sales

tablespace sales_tablespace

storage (initial 1m next 1m pctincrease 0);

Our snapshot log size here is an initial amount of 1 megabyte, each row that is changed in our sales table (or any table for that matter) takes up approximately 25 bytes in the log. This means that our log will not fill up its initial extent until 1,000,000/25 (extent size/25) rows have been changed in the sales table. This figures out to 40,000 rows.

Obviously, the log size should reflect the time period in which you do your snapshot refresh. If you refresh only once a month, you need a bigger log than if you perform a nightly log refresh.

Aside from the Create Snapshot command, Oracle provides a package called DBMS_REFRESH that is a library of PL/SQL calls that a DBA can use to manually refresh a snapshot.

One important note: Oracle locks the master table in Exclusive mode when a refresh occurs. This means that users can only query the data during this time period. If someone performs a snapshot refresh during business hours, no new sales can be entered throughout our corporate empire! Sales clerks across the seven seas would receive strange Oracle locking errors or timeouts as they tried to process customers.

Remote Databases

Many times the database that you are accessing is totally outside of a local cluster of hardware. In these cases, you will use a network protocol such as TCP/IP to communicate with these remote sites. Atop the network protocol is an Oracle protocol called SQL*Net. Atop SQL*Net, the database protocol between two remote databases is facilitated through a database link. A database link is a short Oracle type of URL, which can point to remote parts of an SQL*Net network.

In the previous example, the actual physical order/entry was in New Jersey; then our sales data warehouse in New York City needs to be refreshed with SQL that included the database link to the New Jersey node in the network. Here would be the snapshot create statement’s AS portion that brings us data from New Jersey.

AS Select * From sales@sales.nj.com;

This networking interface to replication is vital to make replication possible between any two Oracle databases, regardless of physical location. The database link is the object we refer back to again and again throughout remote replication. It is important to understand database links and to properly create and manage them.

Complex Replication—Distributed Databases

When we talk about complex replication, we enter the world of distributed databases. This is a favorite topic among academia because it extends relational algebra to include multiple databases. In simpler terms, a distributed database can be defined as many databases appearing as a single database to the user.

Replication and the World of Virtual Databases

In our original example of the business database, each location had a separate OLTP sales database. Refer back to Figure 16.2 to view this. Read-only replication that we just discussed was offered only for a data warehouse viewing one central OLTP database.

In the case where we instead have multiple active databases that need to see each other’s data as data is being updated, deleted, or inserted, we need to use multi-master replication. In the sales database example we had three equally important databases in London, New York, and Hong Kong—hence the term multi-master. All these databases perform OLTP server functions for the regions of the world they are assigned to.

In a more complex scenario, assume that order clerks at all three locations need to coordinate billing, inventory, and shipping. What is required are three separate Oracle servers, each sharing current information from each other on sales data. Although each database is separate, a virtual database must exist that shows table data from all three as if it were one database, as in Figure 16.7.

Figure 16.7.  User sees all data as if it were one database.

This is a scenario called replicated environment, where we merge information from one physical database to another. In a replicated environment, three types of users need to be a part of the management of our system:

1.  Replication Administrators—Users under this umbrella are responsible for setting up replication among databases and implementing it.

2.  Symmetric Replication Agents (propagators and receivers)—There are certain replication activities that need to be run as SYS, Oracle’s super-user account within a given database.

3.  End Users—Users that will be using the replicated information to either query and/or change it.

Let’s set up replication at our three Star Trek trinket sale sites and assign the DBAs the privileges needed to replicate the sales database. To do this, we use the package DBMS_REPCAT_ADMIN and the procedure within that package, GRANT_ADMIN_REPSCHEMA. Here is the following call; notice that this procedure takes in the Oracle username as a parameter:

dbms_repcat_admin.grant_admin_repschema('SALES’);

Here we assume that the SALES user owns the sales data.

Creating a Replicated Environment “Replication God”

If we wanted to create a new user called rep_god that can replicate any schema at the current site, we first create the user:

Create User rep_god Identified By <password>