SharePlex Initial Database Load

SharePlex is a data replication tool that reads changes from the Oracle Redo Logs and then replicates those changes in near-real time from a source database to one or more target databases. Because SharePlex replicates only changes, the target database must be a “consistent copy” of the source before replication is started, or the databases will be “out of sync”; so, you’ll need to select a tool or method to perform this initial copy.   This blog will offer some suggestions for tools and processes that can be used to establish the initial synchronization between a source and target database.

What’s a “Consistent Copy”?

We define a “consistent copy” of the source database as a copy of all tables being considered for replication relationally consistent to a single point in time.   Specifically for Oracle, this means to either a specific SCN (System Change Number) or, to the end of a specific redo/archive log.

Considerations in Choosing a Copy Method

There are a number of ways to obtain this consistent copy.   The method you’ll want to use depends on a number factors, which I’ll detail below

Your Use Case

The first thing to consider when choosing a tool to load your target database is how you’re going to use that database. This will in turn allow you to decide which tables or schemas need to be loaded.  Here are some examples of how the use cases can impact the number of tables:

Use Case

Number of Tables

Migration/Upgrade

Usually all tables, multiple schemas

Report Offloading

Single or small number of schemas (except ERPs), limited tables

Distributed Data

Schemas to different targets

Consolidated Data

Schemas from different targets

Disaster Recovery

All tables, multiple schemas

 

Size of Tables

The next thing to consider is the size of tables you need to place under replication; both the total size, and the size of individual tables.   This will help determine how long creating a copy will take.

Changes to Tables

You’ll want to look at the frequency and size of the changes (updates, inserts, deletes) being applied to your database.   A good way to gauge this is to look at the size of your redo logs, and the frequency of log switches.   You can also use the SharePlex Analyze feature to find out exactly which tables are being changed, and the size of the changes.

Availability Requirements

Another consideration is the availability requirement for your source database.   If changes to the source database can be suspended for the time required for a “cold” backup, that’s often the easiest way to obtain a consistent copy.  On the other hand, if the source database must always be available, that may preclude the use of certain methods to obtain a consistent copy.

Target Database Architecture

The architecture of the target database can have a big influence on the choice of a copy method.   If the source and target are the same versions of Oracle, or close to the same versions, a file-based copy (cold or hot backups) could be used.  However, if the source is an Oracle database, and the target is a SQLServer database, or a Postgres database, some type of conversion process must be used.

Copy Methods

Once you have considered all of the above, you can choose a copy method that suits your specific environment.   Here’s a description of some of the methods you might choose.  This list is by no means exhaustive and all of the methods listed have variations, but this should give you a general idea of what’s available and when to choose a specific method.  We strongly recommend you engage our Professional Services Group to help develop an architecture specific to your environment.

Table Based Copies

If your replication involves only a small number of tables, or a small amount of data, and those tables can be offline for a period of time, then a simple table based copy can be used.    For Oracle to Oracle replication, a database link might be set up to copy a table from the source to the target, or Oracle export and import could be used.  For Oracle to an open target (Postgres, SQLServer, etc), a cross-platform tool such as TOAD DataPoint or TOAD Data Modeler can be used to both convert and copy the tables.   There are also vendor specific tools, such as Microsoft’s SQLServer Migration Assistant.    Finally, an ODBC connection could be established between the source and target.

Schema Based Copies

As with tables, if the data size is small enough, and availability requirements on the source database allow, for Oracle-to-Oracle, export/import is a possibility.   Transportable tablespaces, or an RMAN backup might also be feasible.  Oracle to open target can again use cross-platform tools such as TOAD Data Modeler and TOAD DataPoint; or vendor specific tools.

Database and FIle Level Copies

If you’re planning on replicating all or most of your source schemas; you’ll want to consider copying the entire database.    This can be accomplished at the database level, using RMAN or DataGuard; or, if the database is stored on a filesystem with the capability to take “snapshots” or “block level copies”, such as EMC. Delphix or NetApp, you can use that capability to create a copy of the database.

Zero DownTime Instantiation

All of the copy methods we’ve outlined above can be used to take “cold” copies of your data, with the source database either completely unavailable, or the tables selected for replication kept “inactive”.  However, if Service or Operational Level agreements preclude keeping your source database inactive for the time required to make these copies; you’ll need to take some additional steps.   Fortunately, SharePlex makes it easy to handle these situations.

Since SharePlex reads the redo log for database changes, you can use that capability to capture and hold the changes while you instantiate the target database.   To do this, you install and configure the SharePlex binaries on both the source and target systems; but only run the ora_setup utility on the source, and configure replication from source to target, and execute a “STOP POST” command on the target.   Then you can activate replication on the source.   This will read changes from the source redo log, and ship them to the target, where they will be held in the POST queue.

After we’ve started replication, we select the latest SCN from the source database and start an online (hot) backup, using RMAN or a consistent file level copy.  When the backup is complete, you restore that backup to the target system, bring up the database and run ora_setup, making sure to choose the option to use the existing user that you set up on the source system.

Once the target database has been set up, you can then use the RECONCILE command to tell post that it should resume posting at the SCN you captured from the source.   Finally, you use the START POST command to begin posting.

All of the commands discussed above, as well as step-by-step instructions for each of the methods discussed, can be found in the latest SharePlex Administration Guide, which can be found on the SharePlex web site.

Cross-Platform Considerations

When replicating across platforms, either from one database type to another, or from one hardware platform to another, some of the techniques mentioned above may not be suitable.  For example, file level database backups have limited utility when moving across hardware platforms, such as from IBM P-Series to Intel X86 architecture.  When moving between databases, for example, from Oracle to Hana, additional data type conversions may be required, such as NUMBER to FLOAT.

Here are some tools and techniques that can be used to assist in these situations.

Data Type Conversions

As documented in the Administration and Reference Guides, SharePlex has a default set of “type mappings” for moving data to each supported target platform.   For initial instantiation, some of the tools in the TOAD family can be very helpful.   

TOAD Data Modeler can be used reverse engineer your source schema and create a compatible schema on the target.

TOAD Data Point can be used to move data from your source to the target, converting the data as part of that process.

Database vendors may also provide tools, such as Microsoft SQLServer Migration Assistant, that can be used to convert schemas and data types.

Hardware Conversions/Intermediate Databases

For hardware conversions or moving from one database type to another when zero downtime is required, you can make use of an intermediate database to obtain a consistent copy.    In these special cases, you start by creating an empty database of the same version and architecture as the source.   For example, if we’re migrating from Oracle 11gR2 on Linux to Oracle 12c on IBM P-Series, or from Oracle 11gR2 to SQLServer 2016; you would set up an empty Oracle 11gR2 database running on Linux.    Then, you setup SharePlex replication from the original source to the eventual target (not to the intermediate database), just as outlined above in Zero Downtime Instantiation.  Once you’ve noted the SCN from the source, you can start a backup from the source and then restore that backup to the intermediate database.   Once the intermediate database is up and running, you can use TOAD or a vendor tool to make a copy of the intermediate database into the eventual target and handle whatever conversions are required.    After that copy is complete, you can RECONCILE POST on the target, as outlined above.

I hope you’ve found this blog useful. All of the commands and information I’ve discussed here can be found on the SharePlex web site.  You can also find additional training on our SharePlex community.

Anonymous