Replication Is a Project

Quickly setting up a working replication will work – but usually not for very long. You should plan at least six months until commissioning for such a project, including evaluation and testing. Here, I describe a project in which a migration took place (together with a hardware migration) from a former Oracle Enterprise Edition 11g with Data Guard environment to an Oracle Standard Edition One 11g with Dbvisit Replicate.

In this migration, several problems immediately needed to be solved by the responsible team of database administrators and developers. The reorganization was initiated to create a new environment with powerful hardware, significantly lower licensing costs, and an improved backup concept from the obsolete hardware with expiring support, less power, and high licensing costs. The following example only concerns setup problems and solutions related to the high-availability environment. The data quantity on the database includes several hundred gigabytes, with access from internal and external employees from different locations throughout Europe.

The solution was implemented with a Windows Server 2008 R2, which has 64GB of RAM with two octa-core processors and SAS hard drives with 6Gbps transfer rate. The server was set up accordingly a second time in another data center for the HA environment. The aim was to create an HA environment with those conditions that ensures the least data loss. As already described, the possibilities of a physical standby database are limited to a certain time factor. Logical replication was therefore used, and the team chose Dbvisit Replicate as a third-party product.

Configuring Replication

Before replication can begin, it is essential to make sure that the same database structure and contents are available on both sides. When working with an already growing database, you can best implement this using an RMAN DUPLICATE. After installing the software on the standby side, the team simply cloned the database one-to-one using standard tools. We therefore avoided conflicts that could arise, for example, by manually setting up the database and its structures and manually loading data.

RMAN makes it pretty simple at this point: After creating and mounting an instance on the standby side, you only need to deposit a DUPLICATE:

RMAN> DUPLICATE TARGET DATABASE \
  TO 'prod' FROM ACTIVE DATABASE \
  NOFILENAMECHECK;

After completely cloning the database, you can start to install and set up Dbvisit Replicate (Figure 2) by installing it on both sides. Afterward, the replication configuration proceeds in four steps. Managing and logging the replication occurs using a scheme that is applied to both sides for Dbvisit. The replication itself can take place either in one direction or bi-directionally. For this example, only replication from Primary toward Standby comes into question to completely avoid conflict.

Figure 2: Dbvisit console.

You can specifically determine which tables and schemes to replicate. Finally, you can configure a MINE process on the primary and an APPLY process on the standby side. The replication starts running as soon as the preconfigured services are started. To this end, Dbvisit generates PLOGs on the primary side from the database redo logs, pushes them to the standby side via the network, and applies them to the standby database.

Adjustments Required

As already described, individual solutions to typical problems of a replication solution are needed for this environment. All subsequent statements refer exclusively to replication with Dbvisit Replicate and may differ from other tools. Note that the replicated database is standalone, which means that SYS and SYSTEM objects are not replicated; it also means that appropriate monitoring must take place here. More importantly, it means that not every DDL is supported by it, and, for example, a table space is therefore not automatically created on both sides.

In the current setup, Dbvisit basically replicates no triggers, constraints, sequences, ALTER DATABASE and ALTER SYSTEM commands, or database structures. To this end, I create an appropriate procedure in the operation that ensures such changes are applied on both sides and checked for conformity. Specific data types that are not supported by Dbvisit Replicate and – if used – must therefore be put on the replicated database cause another problem. This concerns the XMLType in this project. Using Toad and a DB Compare, it is possible to show differences at this point and to apply them to the other side, as required.

Because developers on our team often use triggers, the problem of these now firing on both sides occurred during the first tests. Trigger commands were still replicated at this point in the replication setup. This means that a DML command on the primary side initiated by a trigger is registered once by the replication, and the trigger itself is executed by replicating a second time on the standby side. A conflict occurs immediately because of the duplicate entry or an attempt to perform one, and replication comes to a standstill because it performs a "retry" until the conflict is resolved.

Dbvisit provides a "conflict handler" for this, which generally offers two options: to ignore the conflict and roll back the statement or simply to update an overwrite and the target data in violation of the WHERE clause. Neither method provides certainty regarding achieving data consistency in the end and a guarantee that the same content was written on both sides. What might be tolerable for a test environment must be ruled out for backing up a production database. At this point, it becomes clear why no conflicts can be tolerated. The solution to the trigger problem is then conceivably easy; because there is full access to the replica, all triggers must be shut down before starting replication:

SQL> ALTER TABLE table_name \
  DISABLE ALL TRIGGERS;

The last challenge to take up is shifting in case of failure. Although the main purpose of the HA environment has been realized through replication – the least possible loss of data – that is not enough, because it should be possible to use the replica as a productive environment in case of doubt. The hurdle to overcome is therefore switchover and failover by applying an eponymous dynamic database service to both sides that is control via the DBMS_SERVICE package as a first measure to protect the replication from unauthorized access. Access to the user's PC is realized exclusively via this service, which can be started and stopped at any time.

Both servers are registered on the client side, and a registration can only be done on the production side on which the service is started. If the production fails, then the service on the replication side is release – after assurance that all production-related data are provided here:

SQL> EXEC \
  DBMS_SERVICE.START_SERVICE('PRODUSER');

Yet another problem is caused by replication: Because sequences are not replicated, they need to be adjusted to the current highest value before activation for production. For safety reasons, you should determine the highest value and add 100, for example, to make completely sure that no conflicts arise. This fact often raises discussions regarding accounting and the tax office, which demand consecutive numbers for invoices.

Sequences that do not guarantee consecutive numbering under all circumstances are the cause of problems. A simple test with a rollback shows that a sequence used once is gone and thus immediately causes a gap. The challenge for developers in this case is to find another way, because a sequence is not the appropriate means to ensure consecutive numbering. There are good and less good alternatives for this – depending on the generated overhead – on the Internet.