Discussing migrations or replication from Oracle to other database flavors such as MSSQL, MySQL or PostgreSQL, the most frequent question I get from my customers and prospects is how to facilitate the initial load of the existing data. There are quite a few solutions on the market, and the one thing common to all is: they all require a downtime for the application on the source database. What that means is, you’ll end up doing it over the weekend, and if you are real lucky, you may get it done. But what if your application doesn’t allow that, because it needs to run 24x7x52? SharePlex can help you, and in the following, I’ll give you an example of initializing a PostgreSQL target using just SharePlex and Oracle Data Pump. The charm of this solution is that you don’t need a downtime for your application, because SharePlex will capture all changes made to your source database, while you can take your time when performing the initial load during your office hours.
The Plan: Instantiate PostgreSQL target by using a second Oracle database as a stage for PostgreSQL. SharePlex will instantiate PostgreSQL when importing the data into the Oracle stage.
Assumptions:
1. Oracle source and stage, as well as PostgreSQL databases exist.
2. PostgreSQL ODBC driver and a DSN (data source name) are configured on the PostgreSQL server.
3. SharePlex is installed on Oracle source, stage, and PostgreSQL target. Ora_setup resp. pg_setup has been run on all three.
4. Application is running on Oracle source without downtime.
The Steps:
1. Create tables on PostgreSQL. Use e.g. Toad Data Modeler, Erwin Data Modeler, or any other tool of your choice to re-engineer your source schema, convert it to PostgreSQL and create empty entities on the target.
2. Stop post on the PostgreSQL server.
3. Create and activate a SharePlex config on Oracle source to replicate to PostgreSQL. I recommend you use “named queues” for easier identification later.
4. Take a consistent Data Pump Export of the schema on source using FLASHBACK_SCN with an SCN you pulled after the activation (select CURRENT_SCN from V$DATABASE). This will allow us to reconcile the SharePlex post queue after the initial load. Make sure to copy & paste the SCN into a file – you will need it later.
5. Import the schema on the stage database – specify CONTENT=METADATA_ONLY, to have just the empty tables. We will import the data in a separate step, and have SharePlex capture and replicate it to the PostgreSQL target.
6. Disable FK constraints on the Oracle stage. On the stage, you don’t need the constraints, since you’ll be importing consistent data, and the import will succeed without issues.
7. Disable FK constraints on PostgreSQL. Again, you don’t need them during the initial load, and you’ll enable them once the initial load is done.
8. Create and activate a config on the Oracle stage to replicate to PostgreSQL. Again, you may want to use named queues.
9. Start post on PostgreSQL server, but ONLY for the queue coming from the staging database.
10. On Oracle stage, run “alter database force logging”, to make SharePlex capture Data Pump Import. Otherwise, DP will use direct path load, and SharePlex won’t be able to capture it.
11. Import the schema on staging database - now with CONTENT=DATA_ONLY. SharePlex will capture the import and replicate the data to PostgreSQL. Now this can take a while, depending on the amount of data, but after all, it’s the initial load – you’ll only have to do it once. Since there is no downtime for the application on the source, this shouldn’t be an issue. You can use “show post detail queue <queue_name>” on the PostgreSQL target to get an idea of the throughput.
12. Enable FK constraints on PostgreSQL. If your target is used just for reporting, you may ignore this, but if it’s going to be an OLTP database, you will want to have the constraints enabled.
13. Reconcile on PostgreSQL server to the SCN from step 4. This will remove any changes made to the source database between the activation and the export.
14. Start post on PostgreSQL. All changes made to your source database during the initial load will now be applied to the target, and once the backlog on the target is drained, your source and target are in sync.
15. Deactivate config on Oracle stage. You may also want to drop the stage completely.
So it’s fifteen steps to heaven – if PostgreSQL (or any other database, for that matter) was your heaven – but that’s up to you. SharePlex will get you there.