Ilmar Kerm

Oracle, databases, Linux and maybe more

If you are doing many HTTPS calls from PL/SQL to many different sites, managing oracle wallet with all the required trusted certificates is quite painful. But Linux distros already come bundled with all the trusted CA certs from Mozilla, would it be nice just to convert it to Oracle wallet format so PL/SQL could use it?

Here is an Ansible role just for this, you could run it regularly to make sure you have the latest Mozilla trusted certs.

https://github.com/ilmarkerm/ansible-role-oracle-addnode

I have quite many different databases and would like to log in to them from my linux box using my AD username and password. Creating tnsnames.ora aliases for all of them is too much work, I’d like to just specify hostname and service from command line.

Bash aliases to the rescue, but simple alias is not enough in this case, so just take it as an example how can more complex aliases be created in Bash.

Just copy these functions to $HOME/.bashrc, modify them according to your needs and done 🙂

This creates two “aliases” – sqls for secure database connection and sql for insecure. I also need to use instantclient driver for database connection (Radius authentication).

Usage:

sqls rac-scan.example.com myapplicationservice.prod
sqls rac-scan.example.com myapplicationservice.prod dbauser
SQLS_OPTS="-verbose" sqls rac-scan.example.com myapplicationservice.prod

So your RAC database hangs/has occasional stalls/you want to do an emergency reboot?
Franck Pachot has written a good article what traces to get for troubleshooting or for Oracle support

I had a problem – all RAC instances seem to “stall” occasionally so I wanted to execute Francks script on all instances at the same time when the problem was happening.

Ansible to the rescue.

First I pushed out the following script to all instances, this script actually does the diagnostics dump.

Then the Ansible playbook to execute the script above on all instances at the same time and afterwards download all traces to your local ansible controller host:

This post is continuing my previous port about modifying SQLDeveloper preferences with ansible. Building on the same motivation and technique my goal in thist post is to centrally push out and keep updated connection details for SQLDeveloper on client side.

First lets declare the connections we want to push out:

NB! I’m pushing out connections referring to TNS names, since I want to add some extra RAC related settings to each connection description.

First need to create connection.yml that will contain tasks to add a single connection to SQL Developer. This file will be called for every connection from the main playbook.

Now the main playbook.

NB! This is just an extract from the playbook. I expect you are familiar with ansible and know how to put all these three files together 🙂

Managing Oracle Database homes and patching them on a large scale is a challenge. Patching is a must today due to all the security threats out there plus all the bugs that you will hit during normal database operations.
You can read about the challenges and solutions in Ludovico Caldara blog series

Here I’d like to share my solution. The main idea of this solution is simple:
Never patch existing Oracle home, even when you just need to apply tiny one-off patch. Always install a new home and eventually remove the old one.

It is not possible to execute this strategy in a large environment without automation and here I’m sharing my automation solution using Ansible.

Features of this solution:

  • Oracle home configurations become code
  • Runs over any number of clusters or single hosts, with same configuration in parallel
  • Maintain list of homes or flavours of homes each cluster/single host is having installed or what need to be removed
  • Oracle Grid infrastructure or Oracle Restart installation is required
  • Fully automated, up to the point that you have a job in Jenkins that is triggered by push to version control system (git)
  • Home description in Ansible variable file also servers as documentation
  • All tasks are idempotent, so you can execute playbook multiple times. If the servers already have the desired state, nothing will be changed

Ideal workflow to install a new home:

  • Describe in Ansible variable file the home name, base installer location and list of patches needed
  • Attach home name to clusters/hosts in Ansible files
  • Commit and push to git
  • Go through your typical git workflows to push the change into release branch, create pull requests, have them reviewed by peers, merge pull request into release branch
  • Job in jenkins triggers on push to release branch in git and then executes ansible playbook in target/all hosts

Read more about it and get the code from github