One of the best features of Oracle GoldenGate is the capability of heterogeneous replication(different source and target technologies), in this post I will show how to execute an Initial Load and CDC from PostgreSQL to Oracle Autonomous Database.

Our environment have:

• OCI GoldenGate for PostgreSQL version 21.9.0.0.3
• Source Database: PostgreSQL 12.13
• Target Database: Autonomous Transaction Processing (ATP)
• SQL Developer 23.1

If you want to know how to deploy the service, create connections and so on, you can follow this great article here written by Julien.

Preparing the target

Before doing our initial load, we need to have the objects created in ATP, for it we will use a very nice feature from SQL Developer called “Copy to Oracle”, we need to create two connections in SQL Developer, one for source PostgreSQL and one for our ATP target.

To create a connection to PostgreSQL you need to download PostgreSQL JDBC, it is easily found here, place the file in a directory and in SQL Developer go to Tools > Preference > Database > Third Party JDBC Drivers and add your downloaded lib:

And the option to create a PostgreSQL connection will show, create a connection to source database:

One tip, if your username is different from your database name, use this to construct the connection string in hostname: ip:port/database_name? 

Source here

And create a connection to our target database, here it is important to create a connection using the target schema!

Now that we have the connections in place, we can copy the ddl to Oracle, you can even copy multiple DDLs at once, select the desired table and right click, go to Copy to Oracle option:

Choose your destination and uncheck the option “Copy Data”, we will use GoldenGate for it:

If everything goes well, you can see your table in Oracle Database and compare the structure with the original one.

This procedure create everything that table need, like sequences and indexes.

Doing a consistent Initial Load

Starting with GoldenGate 21.8, we can run an Initial Load consistent using only GoldenGate, here you can find the detailed documentation but the steps are very simple:

• Register a CDC Extract(ex_cdc)

• Execute an Initial Load Extract(ex_ini)

You must add the INITIALLOADOPTIONS USESNAPSHOT parameter to Initial Load Extract, after the initial load runs, go to report file and take note of the LSN number, we will need it to create the CDC extract

<tt><span style="font-size:11pt; font-variant:normal; white-space:pre-wrap"><span style="font-family:Arial"><span style="color:#000000"><span style="font-weight:400"><span style="font-style:normal"><span style="text-decoration:none">2023-06-01 18:15:40  INFO    OGG-05379  Create or position a Change Data Capture Extract to LSN 3/56090FA8. Example: ADD EXTRACT <extract-name> TRANLOG LSN 3/56090FA8 or ALTER EXTRACT <extract-name> LSN 3/56090FA8.</span></span></span></span></span></span></tt>

• Create and run a CDC Extract with the name of the first one(ex_cdc):

With the LSN number in hands, create a CDC Extract with the same name of the first, select Begin None

Edit the Extract going to Action > Details > Start With option and put the captured LSN:

And in report you can see the slot usage:

<tt><span style="font-size:11pt; font-variant:normal; white-space:pre-wrap"><span style="font-family:Arial"><span style="color:#000000"><span style="font-weight:400"><span style="font-style:normal"><span style="text-decoration:none">2023-06-01 18:21:36  INFO    OGG-25376  Oracle GoldenGate capture 'EX_CDC' running with replication slot 'ex_cdc_b0ee42471692bd61', slot type 'logical', plugin name as 'test_decoding' attached with database 'ociggll' with slot restart LSN as '3/560908D8', flush LSN as '3/56090910' and its current TXID as '260787'.</span></span></span></span></span></span></tt>

Distribution path

With distribution paths we can move trails between deployments, it was previously commented here if you need more details. 

Create two distributions, one to send Initial Load trail and another for the CDC trail, the first one will be used only once and the second will be running while your replication needs to occur.

Replicats

Now that we have the trails in the GoldenGate Oracle deployment, we can configure the replicats, first we need to create a Replicat reading the Initial Load trail and after a replicat reading the cdc trail:

<span style="font-size:11pt; font-variant:normal; white-space:pre-wrap"><span style="font-family:Arial"><span style="color:#000000"><span style="font-weight:400"><span style="font-style:normal"><span style="text-decoration:none">My replicat parameter is very simple, I'm only mapping from public user to target_psql schema:

<tt>REPLICAT PSQL_ini</tt></span></span></span></span></span></span><tt>
<span style="font-size:11pt; font-variant:normal; white-space:pre-wrap"><span style="font-family:Arial"><span style="color:#000000"><span style="font-weight:400"><span style="font-style:normal"><span style="text-decoration:none">USERIDALIAS dbworld_atp_destino DOMAIN OracleGoldenGate</span></span></span></span></span></span>
<span style="font-size:11pt; font-variant:normal; white-space:pre-wrap"><span style="font-family:Arial"><span style="color:#000000"><span style="font-weight:400"><span style="font-style:normal"><span style="text-decoration:none">MAP public.*, TARGET target_psql.*;</span></span></span></span></span></span></tt>

Compare the replicat statistics with the initial load extract:

After the run, stop and delete the replicat.

Create a replicat for the CDC trail

And now we have our PostgreSQL to Oracle replication working.