Teach Yourself Oracle 8 In 21 Days

- Day 18 -
Administering Oracle Replication

Today's lesson completes the three-day section on maximizing uptime and securing your data. On Day 16, "Understanding Effective Backup Techniques," you learned how to back up your database; on Day 17, "Recovering the Database," you learned how to restore that data in the event of a system failure. Today you will learn how to use other techniques to maximize uptime and minimize recovery time.

Not all replication is used for maximizing uptime. It is convenient, however, to include all the benefits and uses of replication in this lesson. Replication can be used for many different purposes, such as read-only copies in satellite locations and multiple access points.

Today you will learn about replication and other methods for reducing downtime, such as the backup database and Oracle failover.

What Is Replication?

New Term: Replication is the facility that allows you to copy database data to multiple local and remote systems. This data can be accessed and modified in certain instances. In many cases, systems are designed so that the remote data is read-only and updates are processed on the master system. There are various options available.

A replicated database might consist of the entire database or of certain tables or tablespaces. This is completely up to you. With Oracle replication, you can configure a number of options to provide whatever features you want.

Replication Options

Oracle provides several replication options; the most basic of these is the read-only table snapshot. This basic method of replication allows you to copy a table to a remote site as a read-only table. Any updates to the table must be made on the master database. This method is described as a "snapshot" because it is a picture of the database at a certain point in time. Unlike other replication options, the read-only table snapshot refreshes the remote copy only on a periodic basis, which you control.

Another option for replication is Oracle advanced replication. With advanced replication it is possible for all replicated objects to be updated. Advanced replication is much more complex than the read-only table snapshot because conflict resolution must be programmed. The advanced replication option can be quite powerful, but it is more difficult to maintain.

Read-Only Table Snapshots

Read-only table snapshots are used when a read-only copy is all that is needed. You might think this method is quite limiting, but in reality it is very useful. Indeed, there are quite a few applications where the read-only table snapshot is sufficient, and even desirable:

Retail--Retail stores typically maintain a master price list at a central location and download new prices on a regular basis. It does not make sense for each store to change its own prices. Of course, you would want to maintain the inventory database locally.
Lookup tables--Lookup tables are also very good applications for read-only snapshots because they are not usually updated and you might want their data to be controlled by a central site.
Data analysis--OLTP data can be periodically replicated to another system for analysis. This replicated system might be performing DSS tasks to analyze sales data and so on.
Manufacturing--Component parts lists can be maintained at a central database and each assembly line gets a read-only copy of that data. If a part replacement is necessary, this should be done globally.

Telemarketing--A list of clients should be maintained at a central location and downloaded periodically to each sales office. It is desirable for all offices to have the same client list.

These are just a few examples of places where a read-only replicated table is quite sufficient for the satellite location. With this type of replication, the data on the local or master database is periodically updated to the replicated sites, as shown in Figure 18.1.

Figure 18.1.

Read-only snapshots.

This snapshot can be an entire table or a selected view of that table. In this manner you can keep information in the master table that is not propagated to the replicated sites, such as item costs or sales figures. With replication you have many options for what data you want to replicate and when you want to replicate it.

In some cases you must update the data on each replicated site. This is where the advanced replication option is necessary.

Advanced (Symmetric) Replication

New Term: The Oracle advanced replication option, sometimes known as symmetric replication or updateable snapshots, allows each of the replicated databases to be updated. The updates are typically batched and periodically sent to the other replicated objects. You can, however, configure Oracle to propagate updates immediately. This is known as synchronous data propagation or realtime data replication.

The updateable snapshot method is usually sufficient, but with certain applications it is necessary to use realtime data replication.

With the advanced replication option, the system is sometimes described as having multiple masters. These masters can each have read-only table snapshots if desired. An advanced replication system is shown in Figure 18.2.

Figure 18.2.

An advanced replication system.

With the advanced replication option, you can have multiple sites throughout the country, and each can be used as an independent database. Each of these databases can be updated independently and on a regular basis--and can even be deferred to off hours.

How Does Replication Work?

Replication is quite straightforward. The data to be replicated is defined by a defining query, which determines the data that is to be replicated. After the query is defined, the data is selected from the master and copied to the replicated site.

The defining query is set up so that each row in the replicated table corresponds to one row or part of one row in the master table. The defining query cannot contain an aggregate function or GROUP BY clause.

New Term: After the defining query is set up, it will be executed on a regular basis and its result will be propagated to the replicate systems. This propagation of fresh replicated data is called the snapshot refresh. The snapshot refresh can operate in a couple different modes:

Complete refresh--In this mode the master executes the snapshot's defining query and the result replaces the entire snapshot on the replicated system.

Fast refresh--In this mode the master determines what changes have been made to the master and applies these changes to the snapshot. If only a few changes have occurred since the last snapshot, this method is much more efficient.

New Term: If multiple tables are involved in the snapshot, you can use a snapshot group. When you define a snapshot group, several different tables can be linked together so that there is transaction consistency within the snapshot (that is, the tables are in sync).

The snapshot refresh can occur manually or automatically, depending on how you configure the master and the replicated site. With an automatic refresh, no operator intervention is required. The snapshots are automatically refreshed on a regular time interval. With manual refresh, the operator determines when the refresh should occur and manually initiates the refresh. If changes to the tables are rare, manual refresh might work for you.

Configuring Replication

Replication can be configured through Replication Manager or manually via SQL commands. What method you use is up to you.

Configuring Using Replication Manager

To configure replication using Replication Manager, you should first make sure the database was created with the advanced replication option. This option is available through the Oracle Database Assistant installation process.

NOTE: If your database was not built with the advanced replication option, you must run the administrative SQL script \orant\rdbms80\admin\ catrep.sql to install the replication packages.

Replication Manager can be invoked from the Enterprise Manager toolbar or from the Oracle Replication Manager program group. After you invoke Replication Manager, you will see the main screen (shown in Figure 18.3).

Figure 18.3.

Oracle Replication Manager.

If you have never run Replication Manager, you might find yourself in the Setup wizard. From here you can create the master and snapshot sites. If you click the Cancel button, you will see that no database connections are set up, so no information can be displayed.

If you had specified advanced replication when you were building the database from the Database Assistant, this would already be set up for you. To invoke the Setup wizard, click Create (see Figure 18.4).

Figure 18.4.

Create the database connection.

After you click Create, the Create DB Connection dialog (shown in Figure 18.5) prompts you to supply information to connect to a database.

Figure 18.5.

The Create DB Connection dialog.

You will also be prompted to set up a propagator. To do so, highlight the database connection from the Replication Manager main screen (system@dogs in this case) and pull down the Edit menu to select properties. To select a propagator, click the Propagator tab (see Figure 18.6). You can choose any of the users you have defined; DBSNMP is a good choice.

Figure 18.6.

The Edit DB Connection dialog.

The next step is to invoke the Setup wizard. Do so by selecting File | Setup Wizard. From here you will be led through a series of screens that will help you configure a replicated system. The first is the Master or Snapshot Site Setup screen, shown in Figure 18.7.

Figure 18.7.

The Master or Snapshot Site Setup screen of the Setup wizard.

Here you can specify whether you are setting up a database as a master site or as a snapshot site. Both require initial setup.

Configuring the Master Site

Configuring the master site involves setting up a replication account and privileges as well as setting up scheduling for the snapshots. To do so, perform these steps:

1. First you'll see the Select Master Sites screen, shown in Figure 18.8. Here you set up the database that will serve as the master.

Figure 18.8.

The Select Master Sites screen of the Setup wizard.

2. Clicking the New button invokes the New Master Site screen, shown in Figure 18.9. Enter the database connection name and the SYSTEM password on this screen.

Figure 18.9.

The New Master Site screen of the Setup wizard.

3. After you select the master site, you will be prompted for the default administrator and the propagator/receiver, as shown in Figure 18.10. I prefer to use the default settings in this case.

Figure 18.10.

The Default Admin/Propagator/Receiver screen of the Setup wizard.

4. If a propagator already exists, you will be prompted for a password (see Figure 18.11). By default you will be prompted for the password for the default propagator DBSNMP. If you don't know the password, try DBSNMP.

Figure 18.11.

The Existing Propagator's Password screen of the Setup wizard.

5. In the New Replicated Object Schema screen, shown in Figure 18.12, you can choose to create a new schema to hold replicated objects. It is not necessary to choose any schemas at this point but this is a convenient place to do it.

6. The next screen schedules the time for replication and a replication interval. You can leave the default to replicate once per day or change it, as shown in Figure 18.13.

NOTE: The values entered here represent the username and password of the account that does the replication. This account will be created by the Setup wizard.

Figure 18.13.

The Defaults for Scheduled Links screen of the Setup wizard.

7. The next screen, which resembles the one shown in Figure 18.13, allows you to schedule default purging. The purge schedule lets you ordain what time the deferred transaction queues are purged.

8. The Master Site Customizations screen, shown in Figure 18.14, allows you to customize the master site as desired. Select a site and click the Customize button to modify users, the using clause, and so on.

Figure 18.14.

The Master Site Customizations screen of the Setup wizard.

9. The Finish screen, shown in Figure 18.15, consists of an option to record the actions to be taken to a PL/SQL script. Enabling this option is a good idea if you want to be able to re-create these actions in the future.

Figure 18.15.

The Finish screen of the Setup wizard.

10. After you complete the Finish screen, you will be presented with a summary of the actions to be taken in the Setup Wizard Finish screen. If everything looks fine, click OK; the master setup will commence (see Figure 18.16).

Figure 18.16.

The Setup Wizard Finish screen of the Setup wizard.

Configuring the Snapshot Site

Configuring the snapshot site is similar to configuring the master site. The Setup wizard leads you through a series of screens to help you set up the snapshot site correctly:

1. First you'll see the Master Site Selection screen, where you select the site that will be the master for this snapshot site and provide the SYSTEM password.

2. After you have selected the master site, the Setup wizard connects to it to obtain information it needs for the configuration and to validate the connection.

3. Select the snapshot site via the Select Snapshot Sites screen, which resembles the Select Master Sites screen shown in Figure 18.8. From here you select one or more snapshot sites.

4. Clicking the New button invokes the New Snapshot Site screen, where you enter the site name and the SYSTEM password (see Figure 18.17).

Figure 18.17.

The New Snapshot Site screen of the Setup wizard.

5. After you select the master and the snapshot site, you must set up the propagator and the replication administrator. This is done in the Snapshot Site Defaults screen, shown in Figure 18.18. The defaults usually work very well.

Figure 18.18.

The Snapshot Site Defaults screen of the Setup wizard.

6. You will be presented with the Defaults for Scheduled Links screen, which allows you to customize the update schedule just as you did in the previous section. Change this schedule to update at a faster or slower rate.

7. As with the master setup, you must also set the default purge schedule. This is done in the Default Purge Scheduling screen, which enables you to select an interval expression and rollback segment.

8. As with the master setup, you are given the opportunity to customize the snapshot site with the Snapshot Site Customizations screen, shown in Figure 18.19. Select a snapshot site and click the Customize button to modify users, the using clause, link scheduling, and the schema.

9. In the Finish screen, you have the option of running the setup or writing the steps to a script. If you write them to a script, you can get an idea of what steps are performed and you have a record of the operation.

10. After you finish the Setup wizard, you are presented with the Setup Wizard Finish screen (see Figure 18.20), which summarizes all the actions to be taken. When you are satisfied with the steps, click OK and the setup will begin.

Figure 18.19.

The Snapshot Site Customizations screen of the Setup wizard.

Figure 18.20.

The Setup Wizard Finish screen of the Setup wizard.

Both the master and snapshot setups offer a variety of different options. If you don't know which options to use, click the Help button for an explanation of the option. The online help is fairly complete and can be quite useful.

Creating the Snapshots Using Replication Manager

Now that you have set up the replication master (replicated from) and replication slave (replicated to), the final step is to set up the replication objects. To do so, follow these steps:

1. From the main screen of Replication Manager, select the master, then select File | Create New | Snapshot. This invokes the Snapshot wizard, where you can select what type of snapshot to create. I have chosen a simple snapshot, as shown in Figure 18.21.

Figure 18.21.

The Basic Type screen of the Snapshot wizard.

NOTE: Before you can create the snapshot, you must create a database link from the menu where you chose the Create Snapshot option. This link is scheduled between the master and the slave. It is also necessary to create a snapshot log on the database object on the master if you intend to do fast updates. This can be done through Replication Manager.

2. After you have selected the type of snapshot, you will be asked for the database link. Select the database link you scheduled from the Master Link screen shown in Figure 18.22.

Figure 18.22.

The Master Link screen of the Snapshot wizard.

3. Select the master table on which to create the snapshot by selecting the schema and then the table, as shown in Figure 18.23.

Figure 18.23.

The Master Table screen of the Snapshot wizard.

4. You are given the option of restricting the data in the snapshot via a WHERE clause. You can define this WHERE clause in the Where Clause screen, shown in Figure 18.24.

Figure 18.24.

The Where Clause screen of the Snapshot wizard.

5. Define the refresh group in the Refresh Group screen, shown in Figure 18.25. If you do not yet have refresh groups, you can create one by clicking the Create New button.

Figure 18.25.

The Refresh Group screen of the Snapshot wizard.

6. To create a refresh group, type the refresh group name in the Create Refresh Group screen, as shown in Figure 18.26.

Figure 18.26.

The Create Refresh Group screen of the Snapshot wizard.

7. The Tablespace and Extent Characteristics screen, shown in Figure 18.27, allows you to change the tablespace and extent characteristics. Leave these at the default unless you have a reason to change them.

Figure 18.27.

The Tablespace and Extent Characteristics screen of the Snapshot wizard.

8. You have completed all the necessary steps and have reached the Finish screen. As with most Oracle wizards, you must click Finish on the final screen to proceed with the operation.

9. The Finish Snapshot Wizard screen (see Figure 18.28) summarizes all the operations to be performed. At this point you should review the snapshot-creation operation to determine whether all the options have been set up correctly. If you are satisfied, click OK.

Figure 18.28.

The Finish Snapshot Wizard screen of the Snapshot wizard.

After the snapshot is configured, the database link refreshes the snapshot on a regular basis. You can set many small details and options; I have not gone into all of them here. Consult the Oracle documentation or the online help to better use all the options.

Configuring Using the CREATE SNAPSHOT Command

You can define a snapshot via the CREATE SNAPSHOT SQL statement, which is sometimes more useful if the query is complex. Use the CREATE SNAPSHOT command in conjunction with a query to create the snapshot-defining query. An example of a defining query is shown in Listing 18.1.

INPUT:

Listing 18.1. Creating a snapshot.

CREATE SNAPSHOT etw.dogs AS SELECT name, owner_name FROM dogs, dog_owners WHERE dogs.owner_id = dog_owner.id;

ANLYSIS:

This creates a snapshot using a join between the dogs table and the dog_owners table. This snapshot can then be used to set up replicated sites.

Using Advanced Replication

Advanced replication can be used for a variety of different uses: for remote disconnected sites (portable computers), for distributing application loads, and to create failover sites in the event of system failure. Oracle's advanced replication option does not work in quite the same way as the snapshot-replication method. Because multiple sites might be modifying data, it would not work to simply copy the database on a regular basis. Instead, changes are saved and queued to be run on other replication sites.

New Term: Oracle replicates changes on a row basis; this is known as row-level replication. These row changes are saved and queued to run at a later time on the replicated systems. I suggest that you read the Oracle documentation if you want a detailed description of the steps taken to perform advanced replication.

Using Advanced Replication for Disconnected Sites

Advanced replication can be used in a disconnected environment where some of the replicated systems are periodically disconnected and reconnected. For example, these systems can be used with portable computers where they are used on the road (disconnected) and reconnected to the network nightly via modem.

In this manner, the snapshot can be refreshed each night, causing the database to be resynced with other masters. The portable data and the data on the home system can be synced every night, providing the traveling user with regular updated access to corporate data.

Using Advanced Replication for Distributed Workloads

When your database's workload is spread across different areas of the country, it might be beneficial to use advanced replication to allow local access to your corporate data. If the replicated databases are local, access times will be reduced and the data will be available regardless of the state of the wide area network.

If you use advanced replication, each site can perform its own updates and the changes can be propagated nightly to the other systems. There are a few problems, however, if the same records are updated at multiple sites. You must address this when you configure the conflict-resolution setup for advanced replication.

If your database handles a large number of updates that typically involve the same records, replication might not be suitable for your configuration. But if you have only a few updates, and if they do not typically involve the same data, replication might be suitable.

Using Advanced Replication for Quick Recoverability

Advanced replication can serve a variety of different purposes, one of which is quick recovery in the event of a catastrophic system failure. If you use the advanced replication option, an entire database can be replicated. Not only can the database be used during normal operations, but if one of the systems should happen to fail, the users can use one of the available replicated sites.

Using a Standby Database

New Term: Another method sometimes used for quick recovery is the Oracle standby database. A standby database is a complete copy of your primary database, but it is constantly in recovery mode. Each time a log switch occurs on your primary database, the archived log file is applied to the standby database, bringing it up to date.

In the event of a catastrophic system failure, the standby database can be used as the primary database. In the event of a failure on the primary system, you can archive the current log files using the ALTER SYSTEM ARCHIVE LOG CURRENT command. This creates an archive log file of the current redo log file. In this manner, the standby database can be made current with the primary database.

The standby database is designed to be used only as a recovery mechanism. After the standby database has been activated, it becomes the primary database. The original database can later be re-created as the standby database, but you cannot switch back to the original. More details on the standby database can be found in Oracle8 Server Backup and Recovery Guide.

Using Read-Only Tablespaces

By using read-only tablespaces in areas where updates are not necessary, you can reduce downtime spent in recovery. Because the RDBMS knows that a tablespace marked as read-only cannot be updated, the normal recovery process will be skipped. In the event of media failure on the volume containing the read-only tablespace, you must restore the damaged datafiles, but no instance recovery is necessary.

All tablespaces are created read-write and must be populated with data to be considered useful. After the data and indexes have been created to your specifications, the tablespace can be made read-only. This can happen in several ways. For example, Enterprise Manager or Storage Manager can be used to modify a tablespace to be read-only, as described in the previous section. Simply go to the Tablespace Modification screen, shown in Figure 18.29, and click the Read Only box. Invoke this screen by selecting the desired tablespace from the Storage Manager.

Figure 18.29.

Making a tablespace read-only.

The tablespace can also be made read-only via the ALTER TABLESPACE command. The syntax will look something like this:

ALTER TABLESPACE DOGS READONLY;

There are several uses for the read-only tablespace, but they are fairly specific. Whether you can take advantage of it depends on your applications. If you have a large amount of static data that can be put on slower media, the read-only tablespace might be advantageous. Also, if you want to guarantee that archival data is not modified, a read-only tablespace might work.

Summary

Today you learned how Oracle replication works and how to configure it via Replication Manager. You also learned a few ways to reduce downtime by using replication and by using the standby database feature. You learned how using read-only tablespaces can reduce the amount of time it takes to recover a tablespace, because Oracle knows that no changes have been made to it.

Replication is a very specific option and is useful only in very specific cases. If you can take advantage of replication with your system, you will see very good results.

What's Next?

In tomorrow's lesson, "Advanced Oracle Options," you will learn about some of the advanced options available to Oracle, including the Oracle Parallel Server (OPS) and Oracle Parallel Query options.

The Oracle Parallel Server option, an add-on feature, allows more than one instance of Oracle to share the same database. This allows two or more systems to simultaneously access the same data, providing a performance boost and failover capabilities. This linking of systems to form one larger system has traditionally been called a cluster.

The Parallel Query option can provide a good-to-outstanding performance improvement over traditional query operations by splitting some operations into multiple processes or threads. Tomorrow you will learn how the Parallel Query option works and how to use it.

Q&A

Q What is replication?

A Replication is a feature that allows a database or schema object(s) to be copied to another system.

Q What types of replication does Oracle offer?

A Oracle offers several types of replication: read-only table snapshots, updateable snapshots, and realtime data replication.

Q What is a standby database?

A The standby database is a database that is constantly in recovery mode, recovering archived log files from the primary database. In the event of a failure, the standby database can immediately substitute for the primary database.

Q How does a read-only tablespace help speed up recovery?

A Because a tablespace is marked read-only, Oracle knows that no instance recovery is necessary. That tablespace can then be skipped.

Workshop

The workshop provides quiz questions to help you solidify your understanding of the material covered and exercises to provide you with experience in using what you've learned. Find answers to the quiz questions in Appendix A, "Answers."

Quiz

1. What is a read-only table snapshot?

2. What is an updateable snapshot?

3. What is realtime data replication?

4. What is a master site?

5. What is a snapshot site?

6. What are some uses of read-only table snapshots?

7. What are some uses of advanced replication?

8. What is the difference between a complete refresh and a fast refresh?

9. What is a standby database used for?

10. Why would you use a read-only tablespace?

Exercises

1. Use Replication Manager to set up a master site.

2. Use Replication Manager to set up a snapshot site.

3. Use Storage Manager to make a tablespace read-only.

4. Set that tablespace back to read-write.

Teach Yourself Oracle 8 In 21 Days -- Ch 18 -- Administering Oracle Replication