Pythia – An Oracle DB Automation Story – Chapter 1

Maintaining an Oracle Database can be a pain sometimes. I admit that. It’s a massive Software, with so many facets in features, functionality and restrictions. Having to manage several hundreds (if not thousands) of Oracle Databases in my former job, I was searching for some automation to make my life easier. And also here, there are many tools that answer some of my needs but nothing seemed really usable for my individual needs. Second, I was eager to get knowledge in Ansible as an Automation Tool by myself. So I decided to write it on my own. This is the story behind Pythia, my Oracle Database Automation Toolset.

First of all, Pythia evolves quite rapidly, so this series of articles may become outdated in some point in time. To get the entire truth “in code”, you’re welcome to check out the Github Repository of mine here. Also, I sincerely invite you to contribute to Pythia wherever you can.

In the end of 2019, I was fat and bored during the Christmas time and this finally kicked my butt to try out Ansible in general. Needing a project where I can work along, I decided to deploy an Oracle Database RDBMS and Database using Ansible. And this was really the start of Pythia. Some weeks later, I got a Roleset finished which could install the Software itself, build a Database on top and also Update it with Oracle Release Updates. So the next step was to introduce it to the company I worked then. Having not all required components and infrastructure in place, this process took some more weeks. But finally, it was done. The first Workflows were running on our systems and got the whole setup of a Database System according to our Standards running.

From thereon, for me began a rapid journey. Soon the demand for Pythia was, to get all requirements also setup by Pythia regarding the System configuration. So we should create the needed Users, Groups and setup the Kernel Parameters for the OS if neither one of them already exists. Of course, Pythia should also be able to install all needed OS Packages and so on.

Adding some custom SQL Scripts to be executed and during the provisioning of a Database was the next step, followed by the installation of some user defined RPMs and the possibility to provision also an Oracle Database Client to a system. On first sight, this seemed to be some minor new features but think of it from the standpoint of how to provision a “complete” Database System with some custom Users created (by SQL), having an Application Server to talk to your Database (by Oracle Client) and finally have some Backup Client installed (by RPM).

The three latest additions for Pythia were the possibility, to make major Version upgrades possible (e.G. 12c to 19c), perform a duplicate from “Active Database” from one System to another and to do an Export / Import over Network Link from one Database to another. All Features of Pythia are designed to make it as flexible as possible. Giving you the possibility, to implement all of the Features, using your installation standards or guidelines.

So at the moment, there are these features build in Pythia (with more to come soon):

• Install Oracle RDBMS (Starting with Version 18) on a target system
• Create a Database on top of a new or an existing RDBMS
• Adjust all needed Kernel Parameters to run an Oracle Database
• Creates OS Users and Groups implicitly
• Install Patches on top of a new and existing RDBMS and or Database
• Runs SQL Scripts against a specified Oracle Database
• Upgrade a Oracle DB to a new Version
• Duplicate a Database with RMAN “duplicate from active database”
• Datapump Export / Import over NETWORK_LINK
• Install Oracle Client

I plan to write a detailed instruction in this Blog for most of these Features. Enable you, to follow up as you read and getting you to your goal sooner than you would be able to do on your own. So please “read by” in the next weeks / months, maybe there is an update on Pythia here, that I want to share with you. If you want to, you can into Pythia on your own, following the documentations I shared on my Github.

Philip

Update 29.12.2021: Have a look into Chapter 2 of this series here.