Hey folks! Do you have an APEX application and Oracle Database running somewhere? Have you ever wondered if you could move them to some service which can automatically scale up your database performance needs, and scale-out data storage disk seamlessly? Also, your APEX (low-code) application will never face any system outage? Did security take place in every possible way to ensure data loss?

Yeah sounds like pretty good demand, and you may already know that the Oracle Cloud Infrastructure (OCI) has the only Autonomous Database in the world. Almost every operation is handled automatically by Oracle. Plus it has APEX service too, which is a feature-scoped and significantly lower-priced workload type of Autonomous Database that enables developers to rapidly build and deploy low-code APEX applications.

But wait, how will you migrate to OCI? We also have OCI GoldenGate, which is a cloud-native service to create a database replication to OCI. You can easily design, execute and monitor their data replication and stream data processing solutions without the need to allocate or manage compute environments, it is a fully managed service.

In the below image we have a source Oracle 12c database and a target 19c Autonomous database, along with the APEX web application. These resources will be created during this step by step guide.

Workshop Architecture Diagram

Since we are hypothetically migrating a source Oracle to Oracle and every resource is in cloud,  you can imagine this scenario from on-prem to OCI, just like the below image. This workshop can be replicated in any cases.

On-Prem to OCI migration

Workshop consists of 4 small labs.

Very detailed, step by step entire workshop is available at Oracle LiveLabs, link here https://bit.ly/migrate-oracle-to-oracle. Here is the short summary of how it will work.

All you need to start this workshop is a cloud account not necessarily paid account. It can be run on free account, however not always free resources.

  1. Then you need to download the lab resource to OCI using git command and create resources using terraform automation script. This includes virtual network, source database creation, target database creation and of course OCI GoldenGate creation.

    git clone https://github.com/hol-workshop/oci_gg_cloud_service.git
    cd oci_gg_cloud_service/
    terraform init
    terraform plan
    terraform apply --auto-approve

    This entire script will finish in about 10 minutes.

  2. Now you need to prepare your target database for GoldenGate replication. The below command will enable GGADMIN user in Autonomous database.

    alter user ggadmin identified by "GG##lab12345" account unlock;

    Also you need to create target schema in Oracle Autonomous dattabase. You can download the schema script from workshop. Make sure you save these with the correct extension .sql not txt!

  1. Migration step, this is where magic happens.

    To complete the lab, you need to configure two extract processes at the source database and two replicat processes at the target database:

    • An extract for changed data capture. This process will start by capturing changes also create some files called trail files. We will use these files after the initial load finish at the target database. Let's call it the primary extract.
    • We will connect to the source database and retrieve the oldest available System Change Number (SCN) using SQLPlus connection. This SCN will be used to the instantiation SCN, in other words, it means we will export database rows until that given SCN.
    • The migration step needs another 'special' type of extract process while changes are being captured by the first extract process from the source database. This 'special' process is called the Initial-Load extract. It captures data from a specified list of tables and later will be loaded into target database tables using the SCN.
    • When the initial-load extract finishes at the source, we will create the first replicat process to apply those changes. We will call it Initial-Load replicat, it is responsible for populating the target database using extracted data by the initial-load extract.
    • The second replicat is for applying changed data. We will call it the primary replicat. Once the first replicat process, the initial-load finish, we will create a replicat process for applying changed data captured during the initial load to the target database. But how do we know from which starting point we start to replicate? How do we ensure there are no duplicates?
      We will start applying changes after the same SCN we used for the initial-load extract. This is the instantiation SCN we used to mark the starting point for the replicat process.
  2. We successfully have migrated our on-premise database to target Autonomous database. What is left is to upload the APEX application file, please download from here.. This step by step workshop is on Oracle LiveLabs, be sure you try this.

I hope you will enjoy this workshop. I'd like to thank to Vahidin Qerimi and Tsengel Ikhbayar, who helped me to build this beautiful workshop material.

If you need any issues or help please reach out to us. Remember, Oracle Cloud Infrastructure does more than just a database, also incredibly cheaper than other cloud service. Try it yourself!