Tag Archives: Oracle

Conditional audit with Fine-Grained Auditing

Photo by Sue Thomas on Unsplash

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 :

Continue reading

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 !

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 : Reinstall Cloud Tooling

joel-assuied-179972-unsplash

(Photo by Joël Assuied, on Unsplash)

I just started working on Exadata Cloud at Customer. And with this, come my first mistakes 😉
One of my compute node had all cloud tooling scripts located in the adequate directories :

# ls -l /var/opt/oracle/exapatch/
[...]
-r-xr-xr-x  1 oracle oinstall    0 Feb  4 21:23 exadbcpatchsm
-r-xr-xr-x  1 oracle oinstall    0 Feb  4 21:23 exadbcpatchmulti
-r-xr-xr-x  1 oracle oinstall    0 Feb  4 21:23 exadbcpatch
[..]

but for an obscure reason (made of failed update combined with full filesystem), they were all emtpy.

Continue reading

Fun with attribute STOP_TIMEOUT for a custom clusterware resource

alex-guillaume-769172-unsplash

(Photo by Alex Guillaume, on Unsplash)

I recently encountered a problem, for which I do not have any clue yet. But at least, I have a workaround. The goal of this blog post is to remember the exploration towards this workaround. And then to switch back to a normal sitution when possible.

For some reason, 120 development databases were configured to use Shared Server Architecture. The day after this change of configuration, a lot of users started complaining about a fully-automatized-0-problem-encountered-in-the-last-2-years-procedure to duplicate a production database to development database … Indeed, this procedure begins with stopping target database, and this day, failed almost everytime during this step … Why ?

Continue reading