Get actual query associated to an Oracle Enterprise Manager metric – the unofficial way

Photo by Annie Spratt on Unsplash

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”.

Where does this result comes from ?

Continue reading

New in 19 : Oracle Network Log File Segmentation with LOG_FILE_NUM_listener_name and LOG_FILE_SIZE_listener_name

Photo by Chris Greenhow on Unsplash

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.

The Net Services Administrator’s Guide briefly describes this feature and the Database Net Services Reference gives a definition of 2 new parameters in listener.ora :

  • 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.
Continue reading

Drop the underlying sequence of an identity column ?

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.

First, let’s create a simple table :

Continue reading

OGB Appreciation Day : SQLcl

Picture from Oracle.com

I am really glad to participate in #ThanksOGB this year. The 2017 edition (called #ThanksODC at that time) was actually the starting point of my blogging journey.

This year, I will not surprisingly talk about a Command Line Interface again : Oracle SQL Developer Command Line, as known as SQLcl.

What is it ?

Simply put :

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 !
  • One feature that I haven’t tested yet but looks promising is the integration of Liquibase starting from version 19.2. Here is a blog post by Jeff Smith and the official documentation regarding this feature.
  • And last but not least, emojis can be used as a delimiter \o/

I hope you are now convinced that SQLcl is your new best friend ! And if you want to learn new tricks regularly, follow @oraclesqlcl on Twitter !

“The software home is already registered in the central inventory ?” A failed attempt to apply RU on 19c Grid Infrastructure home before it is configured

Photo by Kelly Sikkema on Unsplash

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 :

# /u01/app/19_4_0_0/grid/gridSetup.sh -silent -applyRU /staging/path/29708769

(Please refer to Doc ID 1410202.1 for the full explanation.)

Unfortunately, I had my head in the clouds (pun intended), and the node on which the patch was running encountered a problem and the session got killed.

Continue reading

Exadata Cloud at Customer : Drill down into Cloud Tooling RPM content

Photo by Cassie Matias on Unsplash

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 :

Continue reading

Oracle Data Guard Broker 18 : new VALIDATE NETWORK CONFIGURATION command

(Photo by Chrissa Giannakoudi, on Unsplash)

There is a very handy new command available in Oracle Data Guard Broker 18c. Too bad I discovered it AFTER I solved my problem 😉
This quick blog post demonstrates what can be easily spotted with this new VALIDATE NETWORK CONFIGURATION command.

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.

Continue reading

Exadata Cloud at Customer : Automate CDB lifecycle with REST APIs in PL/SQL – Part 2

(Photo by Francesco Gallarotti, on Unsplash)

Following the first part of this blog serie, here are some new useful functions to automate CDB lifecycle on Exadata Cloud at Customer. Please make sure to read the previous post first, as it contains all the rudiments to build the PL/SQL package.

Continue reading

Exadata Cloud at Customer : Automate CDB lifecycle with REST APIs in PL/SQL – Part 1

(Photo by Michael Fertig, on Unsplash)

Exadata Cloud at Customer offers a very convenient method to manage your Oracle Database Exadata Cloud Services : REST APIs \o/

I am currently working on a PL/SQL package to interact with Exadata instances, from a central administrative database. Let’s see, step by step, what are the prerequisite to achieve this goal. (Note : I am currently learning PL/SQL, and I use Trivadis PL/SQL Cop, especially the very useful plugin for SQL Developer.)

Continue reading