Oracle GoldenGate 11g Implementer's guide

Oracle GoldenGate's capture process, known as Extract, obtains the necessary data from the databases' transaction logs. For Oracle, these are the online redo logs that contain all the data changes made in the database. GoldenGate does not require access to the source database and only extracts the committed transactions from the online redo logs. It can however, read archived redo logs to extract the data from long running transactions, but more about that later in the book.

The Extract process will regularly checkpoint its read and write position, typically to a file. The checkpoint data insures GoldenGate can recover its processes without data loss in the case of failure.

The Extract process can have one the following statuses:

The ABENDED status stems back to the Tandem computer, where processes either stop (end normally) or abend (end abnormally). Abend is short for abnormal end.

To replicate transactional data efficiently from one database to another, Oracle GoldenGate converts the captured data into a Canonical Format which is written to trail files, both on the source and the target system. The provision of source and target trail files in the GoldenGates architecture eliminates any single point of failure and ensures data integrity is maintained. A dedicated checkpoint process keeps track of the data being written to the trails on both the source and target for fault tolerance.

It is possible to configure GoldenGate not to use trail files on the source system and write data directly from the database's redo logs to the target server data collector. In this case, the Extract process sends data in large blocks across a TCP/IP network to the target system. However, this configuration is not recommended due to the possibility of data loss occurring during unplanned system or network outages. Best practice states, the use of local trail files would provide a history of transactions and support the recovery of data for retransmission via a Data Pump.

When using trail files on the source system, known as a local trail, GoldenGate requires an additional Extract process called Data Pump that sends data in large blocks across a TCP/IP network to the target system. As previously sated, this is best practice and should be adopted for all Extract configurations.

The Server Collector process runs on the target system and accepts data from the source (Extract/Data Pump). Its job is to reassemble the data and write it to a GoldenGate trail file, known as a remote trail.

The Apply process, known in GoldenGate as Replicat, is the final step in the data delivery. It reads the trail file and applies it to the target database in the form of DML (deletes, updates and inserts) or DDL*. (database structural changes). This can be concurrent with the data capture or performed later.

The Replicat process will regularly checkpoint its read and write position, typically to a file. The checkpoint data ensures that GoldenGate can recover its processes without data loss in the case of failure.

The Replicat process can have one of the following statuses:

* DDL is only supported in unidirectional configurations and non-heterogeneous (Oracle to Oracle) environments.

The Manager process runs on both source and target systems. Its job is to control activities such as starting, monitoring, and restarting processes; allocating data storage; and reporting errors and events. The Manager process must exist in any GoldenGate implementation. However, there can be only one Manager process per Changed Data Capture configuration on the source and target.

The Manager process can have either of the following statuses:

In addition to the processes previously described, Oracle GoldenGate 10.4 ships with its own command line interface known as GoldenGate Software Command Interface (GGSCI). This tool provides the administrator with a comprehensive set of commands to create, configure, and monitor all GoldenGate processes. You will become very familiar with GGSCI as you continue through this book.

Oracle GoldenGate 10.4 is command-line driven. However, there is a product called Oracle GoldenGate Director that provides a GUI for configuration and management of your GoldenGate environment.

The following diagram illustrates the GoldenGate processes and their dependencies. The arrows largely depict replicated data flow (committed transactions), apart from checkpoint data and configuration data. The Extract and Replicat processes periodically checkpoint to a file for persistence. The parameter file provides the configuration data. As described in the previous paragraphs, two options exist for sending data from source to target; these are shown as broken arrows:

Having discovered all the processes required for GoldenGate to replicate data, let's now dive a little deeper into the architecture and configurations.

By far the simplest and most common configuration is the "source to target". Here we are performing real-time or batch change data replication between two sites in a unidirectional fashion. This could be, for example, between a primary and standby site for Disaster Recovery (DR) or an OLTP to data warehouse for Business Intelligence (BI) and OLAP.

One-to-One architecture provides a data replication solution that offers the following key benefits:

Due to its simplicity, the One-to-One architecture is referred to many times in this book to effectively demonstrate:

Another popular GoldenGate configuration is the One-to-Many architecture. This architecture lends itself perfectly to provide two solutions. One data replication feed for reporting and one for backup and DR. The following example helps to illustrate the method.

One-to-Many architecture provides a data replication solution that offers the following key benefits:

The One-to-Many architecture is very flexible, given that it provides two solutions in one—a reporting and a standby database, both of which can have different table structures.

The Many-to-One configuration comes into play for peripheral sites updating a central computer system representing a hub and spokes on a wheel. This scenario is common in all industries, from retail outlets taking customer orders to high street bank branches processing customer transactions. Ultimately, the data needs to make it to the central database ASAP and cannot become lost or corrupted. GoldenGate's architecture lends itself perfectly to this scenario, as seen in the next example. Here we have three spoke sites sending data to the central hub site.

One important point to mention here is Conflict Handling. In a "hub and spoke" configuration, with concurrent updates taking place, data conflicts are highly likely to occur. Should the same database table row or field be updated by more than one source, on the target the conflict must be handled by GoldenGate to allow either one of the transactions to succeed or to fail all.

Another "hub and spoke" solution includes the One-to-Many configuration. A typical example being the company head office sending data to its branches. Here, conflict handling is less of an issue.

The cascading architecture offers data replication at n sites, originating from a single source. As the data flows from the originating source database, parts or all of it are "dropped off" at each site in a cascading fashion until the final target is populated. In the following example, we have one source (Site A) and three targets (Sites B, C and D). Intermediate Sites B and C have both source and target trails, whereas Site A has only a source and Site D only a target trail.

What data to replicate is configured by using Filters in the GoldenGate parameter files at each target site, making the Cascade architecture one of the most powerful, yet complex configurations. Users at each site input data that can also be replicated to the next site.

The following is an example of an active-active configuration, where Site A sends changed data to Site B and vice versa. Again, Conflict Handling is an important consideration. A conflict is likely to occur in a bi-directional environment, where the same row or field is being updated at both sites. When the change is replicated, a conflict occurs. This needs to be resolved by GoldenGate based on the business rules, that is, should data from Site B overwrite Site A, or should both transactions fail?

Bi-directional (active-active) architecture provides a data replication solution that offers the following key benefits:

Another key element to include in your configuration is Loop Detection. We do not want data changes going round in a loop, where Site A updates Site B, then Site B updates Site A, and so on.

Do not be put off by the Bi-directional architecture. When configured correctly, this architecture offers the most appropriate solution for global companies and organizations, allowing users in two centers, both sides of the globe to share the same system and data.

The active-active configuration is very different from the active-passive, which we discuss in the following section.

The following is an example of an active-passive configuration, sometimes called "Live Standby", where Site A sends changed data to Site B only. You'll notice that the path from Site B to Site A is "grayed-out", suggesting that the data replication path can be re-enabled at short notice. This means that the GoldenGate processes exist and are configured, but have not been started.

Bi-directional (active-passive) architecture provides a data replication solution that offers the following key benefits:

The active-passive configuration lends itself to being a DR solution, supporting a backup site should processes fail on the production site.

The following sections describe the process topology. Firstly discussing the rules that you must adhere to when implementing GoldenGate, followed by the order in which the processes must execute for end to end data replication.

Statistics

In terms of performance monitoring, the GGSCI tool provides real-time statistics as well as comprehensive reports for each process configured in the GoldenGate topology. In addition to reporting on demand, it is also possible to schedule reports to be run. This can be particularly useful when performance tuning a process for a given load and period.

The INFO ALL command provides a comprehensive overview of process status and lag, whereas the STATS option gives more detail on the number of operations. Both commands offer real-time reporting. This is demonstrated in the following screen shots:

From the screenshot you can see that the STATS command provides daily and hourly cumulative statistics for a given process, including the overall total and the latest real-time figures.

You have already seen the different solutions GoldenGate has to offer at the beginning of this chapter. You need to choose the most appropriate architecture based on the business requirements. To do this it is necessary to first understand the requirements of the system and what the system has to achieve. These requirements' are both functional and non-functional. Examples of non-functional requirements are performance and scalability.

To address the functional requirements you need to know:

For the non-functional requirements, you need to know:

These are all important factors when considering a design. In the earlier section "Oracle GoldenGate Topology" in this chapter, we mentioned the use of parallel Extract and Replicate processes to increase data throughput. The number of parallel trails is largely dependent on the hardware footprint. How many CPU cores do I have? How much memory is available? Etc.

Other areas to consider are the Network and Database Schema design. Starting with the Network, this is fundamental to a data replication solution. If you have a slow network, you will not be able to replicate high volumes of data in real-time. Furthermore, should your network be unreliable, you need to consider the cost of retransmission or transmitting a backlog of trail files. Redundant networks are very important too and can help to alleviate this problem. If you can avoid the network outage altogether by routing data over a backup network, it will save a number of problems.

Database Schema design is another important consideration. Imagine a schema where every table is related to nearly every other table, and the cascading referential constraints are so complex, that it would be impossible to logically separate groups of related tables for data extract. GoldenGate does provide a solution to this problem by using the @RANGE function. However, this is not ideal. Apart from the complex configuration, GoldenGate has to spend more CPU processing the configuration filters and artificially "splitting" the data into a pre-defined number of trails. A good schema design would be to ensure that logical separation exists between table groups, allowing a simple, effective configuration that performs well. The number of table groups being directly proportional to the number of Extract processes configured.

The power of GoldenGate comes into its own when you select what data you wish to replicate, by using its inbuilt tools and functions. You may even wish to transform the data before it hits the target. There are numerous options at your disposal, but choosing the right combination is paramount.

The configuration of GoldenGate includes mapping of source objects to target objects. Given the enormity of parameters and functions available, it is easy to over complicate your GoldenGate Extract or Replicat process configuration through redundant operations. Try to keep your configuration as simple as possible, choosing the right parameter, option, or function for the job. Although it is possible to string these together to achieve a powerful solution, this may cause significant additional processing and performance will suffer as a result.

GoldenGate provides the ability to select or filter out data based on a variety of levels and conditions. Typical data mapping and selection parameters are as follows:

Before GoldenGate can extract data from the databases' online redo logs, the relevant data needs to be written to its log files. A number of pre-requisites exist to ensure the changed data can be replicated:

It is also advisable to have a primary key defined on your target table(s) to ensure fast lookup when the Replicat recreates and applies the DML statements against the target database. This is particularly important for UPDATE and DELETE operations.

Initial Load is the process of instantiating the objects on the source database, synchronizing the target database objects with the source and providing the starting point for data replication. The process enables "change synchronization" which keeps track of ongoing transactional changes while the load is being applied. This allows users to continue to change data on the source during the Initial Load process.

The Initial Load can be successfully conducted using the following:

An Oracle database uses the System Change Number (SCN) to keep track of transactions. For every commit, a new SCN is assigned. The data changes including primary key and SCN are written to the databases' online redo logs. Oracle requires these logs for crash recovery, which allows the committed transactions to be recovered (uncommitted transactions are rolled back). GoldenGate leverages this mechanism by reading the online redo logs, extracting the data and storing the SCN as a series of bytes. The Replicat process replays the data in SCN order when applying data changes on the target database. The GoldenGate manuals refer to the SCN as a CSN (Commit Sequence Number).

GoldenGate's Trail files are in Canonical Format. Backed by checkpoint files for persistence, they store the changed data in a hierarchical form including metadata definitions. The GoldenGate software includes a comprehensive utility named Logdump that has a number of commands to search and view the internal file format.