EDIT : This blog post has been modified thanks to Douglas suggestions, in the comment section.
I recently took the plunge to learn and use Ansible. I read many interesting blog posts on how Ansible can simplify DBA life from a lot of people in the community. But I felt I needed a simple use case to finally get started and become familiar with it.
How is Ansible relevant to my DBA job?
To overly simplify the concept of Ansible, let’s say it allows to SSH from any machine (control node) to a group of servers (managed nodes) to run the same commands, as long as Ansible is installed on the control node. The magic part is that you don’t have to deploy any agent on the the managed nodes. I will not cover the functioning of Ansible in this blog post, the official documentation is a must-read.
I recently had to deploy Autonomous Health Framework on a group of new servers : finally I got the simple use-case I was waiting for.
One of my colleague was trying to find out which application was inserting “wrong” values on a column of a specific table. It took me some time to understand what a “wrong” value can be, but in this case, it was pretty simple : for example, any value greater than 6 on column NUMBER_OF_LEGS of table INSECTS was considered a “wrong” value.
And actually Fine-Grained Auditing makes it possible in a simple way. Here is an example on Oracle Cloud “Always Free” Autonomous Database 19.5 :
The other day, I was obsessing over an alert in Oracle Enterprise Manager Cloud Control 12c Release 4, because the error message was cryptic. In the Incident Manager console, I got the following line for an Oracle Database 12.2 target :
I expected to have some value or an explicit error message. I did not understand why I would get an “ORA-00942: table or view does not exist”. When drilling down on this alert, I noticed it belonged to a Metric Group called “Deferred Transactions” (and of course I had no idea what it was) :
But I still did not understand why I would get such an error (instead of a numeric value for example) and why the Event Type was “Metric Evaluation Error”.
I started off the new year by reading the Oracle Database 19c New Features Guide and I came across a very nice new feature : Oracle Network Log File Segmentation. It is now possible to specify a maximum file size for listener logs and a maximum number of files to keep. In previous versions, only log.xml was segmented in chunks of 10Mb, but not the plain text listener.log.
LOG_FILE_NUM_listener_name : To specify the number of log file segments. At any point of time there can be only n log file segments where n is LOG_FILE_NUM_listener_name. If the log grows beyond this number, then the older segments are deleted.
LOG_FILE_SIZE_listener_name : To specify the size of each log file segment. The size is in MB.
I recently heard about a bug related to identity columns and their underlying sequence : if the sequence is dropped, an insert in the corresponding identity column would generate an error. Somehow it makes sense, but I wanted to check the behaviour in a recent Oracle Database version.
Fortunately, I have an “Always Free” 18.4 Autonomous Database in the Oracle Cloud, which is very convenient to run this kind of quick tests. The schema I am using is empty.
SQLcl is a command-line interface for Oracle Database that combines the power of SQL*Plus and SQL Developer
Or : it is like SQL*Plus, but much better ;-). I could not live without it. OK, I work less efficiently without it.
Of course I cannot explain all the powerful features of this great tool in this blog post. Instead, I’ll give you a short list of very interesting ones, along with relevant documentation or blog posts (the Internet is full of excellent resources on SQLcl) :
Why can’t I live without it ?
Here are some reasons :
The format option lets you format the output in many different ways, as described in this blog post by Kris Rice. In particular, I made ansiconsole my default using login.sql file. Judge for yourself : connect to your favourite database with SQLcl, type set sqlformat ansiconsole, run a query and let the magic happen !
Custom aliases are easy to create. An old blog post I wrote contains some examples (and you can also define them with login.sql). There is also a lot of already existing shortcuts to make your life easier : APEX to list APEX apps, CTAS for create table as select, DDL to get an object DDL …
Simple but useful, auto-completion of keywords works with tab !
On one of my Exadata Cloud at Customer system, I recently had an issue which required to check exactly which files were updated when upgrading the cloud-specific tooling included on Exadata Cloud at Customer, also know as dbaastools_exa. I needed to know if a configuration file had been overwritten or not, when upgrading dbaastools_exa. My knowledge in RPM packages being very limited, I did some research and here is what I found :
In this example, I have 2 sites, each with a 2-node Oracle 18.6 Grid Infrastructure cluster. On both clusters, there is an Oracle 18.6 RAC database. The primary database on site 1 is called tool, and the standby database on site 2 is called opeth. Both RAC databases have 2 instances.
I had trouble setting a correct Oracle Data Guard configuration between database tool and database opeth.