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 !
I recently came accross a new (to me) error when trying to upgrade Grid Infrastructure on my lab, a 2-node 18.6 RAC cluster. To upgrade Grid Infrastructure directly to 19c with the latest Release Update 19.4, I downloaded the 19.3 base release and tried to apply RU 19.4 before launching the upgrade :
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.