Tag Archives: Oracle

Install Oracle Autonomous Health Framework with Ansible : a beginner’s approach

7 Replies
Photo by Alissa De Leva on Unsplash

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.

Continue reading →

Conditional audit with Fine-Grained Auditing

1 Reply
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

Leave a reply
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

Leave a reply
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 ?

1 Reply
Photo by Rohit Tandon on Unsplash

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

1 Reply
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

Leave a reply
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

2 Replies
(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

Leave a reply

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 →