With the introduction of cloud-managed databases such as AWS RDS and Oracle Cloud Infrastructure, more and more enterprises are moving their applications and data to the cloud. However, one of the main challenges is the migration of large, existing datasets with minimal application downtime during the switchover. This article looks at a strategy to automate the setup of a real-time replication using Ansible with GoldenGate.
Oracle GoldenGate is a software package that allows the real-time replication of data between homogeneous databases (Oracle databases) or heterogeneous databases (MySQL, PostgreSQL, SQLServer, Sybase and others) over a network connection. You can use it to consistently replicate live data between a source and target database.
When migrating data from your own data center to a public or private cloud, a key success factor is 100% data integrity between the source and the target database at all times. Some of your applications may only allow for limited downtime when the switch to a cloud database occurs. The strategy we deployed for our migrations was based on using Oracle GoldenGate to replicate data in real time from the on-premises database to the cloud database, automating the entire process.
Setup, test and tear-downs
On the path, you will most likely set up different environments to replicate data (dev, test, etc.), test your application, and perform a few mock migrations and switchovers before the final. This means setting up a new replication each time, testing your application, and resetting the environments repeatedly. Depending on your database size, this process will be time-consuming for your DBAs, potentially error-prone, or highly inefficient at best. A flexible and automated process that allows you to set up a new replication stream effortlessly will allow you to focus on other important tasks that contribute to the final success of the migration.
Meet Ansible
Ansible is an open source software provisioning, configuration management, and application deployment tool. It is the equivalent of CI/CD for infrastructure. Ansible is amazingly simple, and it works by connecting to your servers through SSH and pushing out small tasks or programs to execute. These small programs are called modules, and they are responsible for executing functions with specific outcomes regarding the target systems. Let’s say you need to create a file, update a configuration file, and execute a script. Ansible can coordinate all these tasks to a single remote server or a set of servers all at once. Because Ansible only uses SSH, there are no processes, daemons or configuration files required on the target hosts. All that is required is a set of SSH keys to allow access to the servers to execute the tasks.
Ansible needs an inventory of servers on which to execute the tasks. This inventory is stored on the Ansible server using the simple INI configuration format. You can list all your servers, create groups of servers, and assign aliases. When calling an Ansible script, simply specify which target group or host(s) to use. Here is an example of an inventory file:
dev-db01.internal.example.com
[local-test-db]test-db01.internal.example.com
[cloud-dev-db]dev-cloud87.dbaas.oraclecloud.com
[cloud-test-db]test-cloud54.dbaas.oraclecloud.com
Ansible is based on the YAML markup language. All tasks are defined in a configuration file called a playbook (an Ansible YAML file). The tasks are listed in the sequence in which they will need to execute. When launching a playbook, specify the group of hosts on the command line, including any additional parameters, and Ansible will start executing each task listed.
One powerful aspect of Ansible is the use of templates leveraging the Jinja2 templating language. By using templates, you can generate scripts (bash, SQL, config, etc.) on the fly based on your configuration. Ansible can also be extended and customized with your own modules. Modules can be written in any language that can return JSON. (If you are not familiar with Ansible yet, check out the rich Ansible documentation and third-party tutorials online. Ansible is easy to learn and get started with.)
Setting up a replication stream
Oracle GoldenGate is a powerful tool that allows you to set up replication streams between two or more databases. It supports multiple topologies (unidirectional, bidirectional, peer-to-peer, broadcast, consolidation and cascading). For a migration from local host A to cloud host B, you will need to configure a unidirectional replication (in most cases). Should you want to fail back to your original database, you might consider a bidirectional replication topology. (Note that there may be additional aspects to consider should you require a bidirectional replication not covered in this article.)
For the rest of this article, the following assumptions are made: the Oracle source and target databases are created and running; the GoldenGate software (version 12.3.2.1) is installed on both instances, with the manager process already configured and running; any firewall between the two hosts has the proper ports open (usually 7809), and the GoldenGate user has been created in both databases.
In order to set up a replication stream, we will need to automate the following steps:
1. Stop/delete the existing extract process on the source
If the source extract process already exists from a previous deployment, it will be stopped gracefully, deleted, and unregistered from the database. All existing trail files will be deleted to avoid conflicts with the new trail files. If the extract does not exist (first-time run), the error will be ignored.
2. Stop/delete the existing replicat process on the target
If the target replicat process already exists, it will be gracefully stopped and deleted. Any remaining trail files and log files will be deleted.
3. Drop any existing objects in the target schema
Before importing the new data, the existing objects (tables, indexes, synonyms, packages) must be dropped from the target database. They will be recreated and populated by the import (Step 8).
4. Add/start a new extract stream on the source (extract and pump)
This step will register a new extract with the source database, add the extract and data pump processes, and start both of them. This will start extracting and recording all updates from the source database, and add them to the trail files. Additionally, a copy of each parameter file will be created, and a new parameter file generated.
5. Get the current SCN number from the source database
In order to create a data-consistent export, all data will be exported based on the internal Oracle SCN number. Only the database schema listed in the configuration will be exported. This allows you to fine-tune the datasets to replicate.
6. Export all mapped schema/users using the SCN for consistency
Conduct the data export for all schema defined for the current stream in the streams.yml config file. Use the database’s native exporting tool. For Oracle database, we will use Oracle Data Pump (known as expdp).
7. Transfer the export dump file to the target (or use a NFS shared filesystem)
Copy the export to the target database server, and place it in a directory accessible by the SYSTEM database user. In our case, we use a shared NFS filesystem between the source and target databases.
8. Import all mapped schema/users into the target database
Import the entire export file into the target database. All objects will be recreated and populated.
9. Add/start the new replicat process on the target
Add a new replicat process and start enable (start) the replication process. The replicat will read the trail files generated and apply all changes. Depending on the size of your database, the process may take seconds, minutes, or up to a few hours. From there, your target database will be in sync with your source database.
10. Grant any missing privileges on the target
If your application requires custom grants or any other SQL custom steps against the data, you can add them in this step. Use an Ansible template to dynamically generate the SQL statements.
11. Rebuild any invalid synonyms or objects on the target
Use this step to validate or recreate any invalid objects.
The code
You can find the main playbook and configuration files on my GitHub page at https://github.com/ora-31/gg-ansible. The playbook and configuration files may need to be updated or customized to fit your own environment (specifically the inventory file, and each <source>.yml and <target>.yml file). Contact me if you have any questions at: [email protected]
Conclusion
Automating a relatively complex task like setting up a replication between two databases will not only help you succeed, but also make your daily work more enjoyable. I was able to learn Ansible and create my automation script in about a week while working on other tasks. From here on, I will be leveraging Ansible more to automate my work and ultimately complete a successful migration. I encourage you to do the same.
