Easy replication with SharePlex

It was in 1999 when I first got some information about a replication tool for Oracle database. At that time I worked for a small consultancy company and had some experience with Oracle Advanced Replication because of a telecommunication project. SharePlex looked exciting and I began to dig into the features and functions and within the next years I became more and more familiar with that product. In 2002 we had a project where the customer already used Oracle standby for disaster recovery and was not satisfied because of the following problem. Even though there was a process to create a standby database you couldn’t validate the installation. Instead whenever you open that database the standby functionality had been lost and you had to recreate the database again.

We introduced Quest SharePlex for Oracle as an alternative for disaster recovery and the customer agreed to use it. It was a very successful project and the customer immediately saw the benefit because both databases were now up and running and whenever they had problems with the infrastructure they were able to switch over the application instantaneously.

In 2003 they decided that it’s now time to upgrade the database to Oracle 9i (guess it was 9i release 2). We did some tests and the timeframe for an ordinary upgrade was unacceptable.

But we had SharePlex installed and running. So we engineered a minimum downtime upgrade and I guess this was the first time ever a replication has been used to upgrade an Oracle database with minimum downtime.

Minimum downtime migration:

For the migration we stopped the post process from EMAIL1 to EMAIL2. Now we had a read only copy of the database and were able to setup the two new databases. As the size of the database was only about 40 GB we could afford having the database twice on every server.

Using conventional export and import we created the two new databases EMAILN1 and EMAILN2 with a well know synchronization point – keep in mind that flashback query was just introduced for 9i and not available for the 8i export.

After the setup of SharePlex for the new servers and the interim between 8i and 9i the post process was started again and – no wonder – within some few hours all 4 databases were in sync. We did some tests on the new servers and within the following week we switched over the application to the new databases.

This was the first but not last migration I did with Shareplex.

Last week I downloaded the latest version (8.5) and even though I have installed SharePlex many times and did some PoCs comparing SharePlex with other products like GoldenGate and Dbvisit Replicat I realized that the installation and configuration of Shareplex hasn’t been changed that much. The config.sql and build_config.sql scripts are still available and the configuration file looks the same as in the early 2000th. But does this indicate that SharePlex is an outdated product? Not at all. Within less than 15 Minutes I was able to install SharePlex for 11g and 12c databases and successfully established a replication.

Setup SharePlex

1. Unpack SharePlex into a temporary directory
2. Run the installation routine:

   sh SharePlex-8.5.0…

   This routine will guide you through the installation process. Two directories are required:

• The software installation directory (SP_HOME)
• The directory for the configuration files, logs, etc. I would recommend to use a dedicated file system for the configuration files (SP_SYS_VARDIR).
• The database, version, tcp-port, etc. will be asked for during the installation.
3. After the installation you need to create a user in the database with the appropriate rights. If there are no concerns you can simply use the ora_setup command in the bin directory. This will again guide you through the installation process.
4. The management process sp_cop must be started to run in the background:

$SP_HOME/bin/sp_cop &

5. Now it’s time to setup the replication. A parameter file is required with a very easy layout which might look like follows:

   datasource:o.RICHARD 
   #Source Table       Destination Table        Routing Map 
   sptest.customers    sptest.customers         cage@o.JOHN

   This configuration file includes the following information and must exist in the config directory ($SP_SYS_VARDIR/config):

• Datasource is an Oracle database “o” named “RICHARD”
• Source table is schema “sptest” with table “customers”
• Target table is again schema “sptest” with table “customers”
• And the destination is host “cage” with the Oracle “o” database “JOHN”
6. With this information we can activate the configuration. The Shareplex control utility will help to activate and monitor the configuration:

$SP_HOME/bin/sp_ctrl 
sp_ctrl>  activate config

That’s all. With this few steps a unidirectional replication can be setup between two Oracle databases. If you want to replicate a bunch of tables or multiple schemas you can use the SQL-Scripts “build_config.sql” and “config.sql”

DELL Software provides a demo license which lasts for 30 days. There are rumors that the next version of SharePlex will support Multitenant database. And as you might have seen I’m a fan of pluggable databases. So hopefully at the end of November I will be able to tell you how to migrate an Oracle 11g database with WE8ISO-character set to a pluggable database with AL32UTF8 character set using SharePlex to minimize the downtime.