crsctl stat res -t shows a third ASM resource in a incorrect status in a two nodes cluster setup (12c, 18c, 19c)

Starting in 12c, onc we finish the setup of a two nodes cluster, output of csrctl stat res -t shows a third resource in a non-correct status

ora.asm
      1        ONLINE  ONLINE       rac18c1                  Started,STABLE
      2        ONLINE  ONLINE       rac18c2                  Started,STABLE
      3        OFFLINE OFFLINE                               STABLE

According to note 2132715.1 from Support, this is expected behaviour, since with Flex ASM, the default is 3 instances.

If we check the cardinality of the asm instance, we can verify it:

[oracle@rac18c2 ~]$ srvctl config asm
ASM home: <CRS home>
Password file: +DATA/orapwASM
Backup of Password file:
ASM listener: LISTENER
ASM instance count: 3
Cluster ASM listener: ASMNET1LSNR_ASM
[oracle@rac18c2 ~]$
So, we can leave  it safely as it is, or we can change the cardinality.
According to the mentioned note, the best way to change this cardinality is change it to ALL:

[oracle@rac18c2 ~]$ srvctl modify asm -count ALL

We verify the cardinality again:

[oracle@rac18c2 ~]$ srvctl config asm
ASM home: <CRS home>
Password file: +DATA/orapwASM
Backup of Password file:
ASM listener: LISTENER
ASM instance count: ALL
Cluster ASM listener: ASMNET1LSNR_ASM
[oracle@rac18c2 ~]$

And verify it again with crsctl. We see that now is corrected:

[oracle@rac18c2 ~]$ crsctl stat res ora.asm -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.asm
      1        ONLINE  ONLINE       rac18c1                  Started,STABLE
      2        ONLINE  ONLINE       rac18c2                  Started,STABLE
--------------------------------------------------------------------------------
[oracle@rac18c2 ~]$

Error running DBCA - ORA-27528 Transport RDS required by Engineered System not available

After some time not being able to update my blog, due to my schedule, now will try to add some updates more often.
This is an error I found the other day while running dbca on a Exadata Virtual machine and using a prevoiuosly created template for the database

At first glance, could not find any related note in Oracle Support, but a search in Google got some results, and directed me to the correct note.
The problem happens because parameter cluster_interconnect is set to one or more specific IPs. Of course, those IPs are only available in one node but DBCA tries to use them for all nodes. Obviously, it fails.
Solution: There are a couple of solutions, but being in DBCA, the best thing to do is leave the parameter cluster_interconnect empty and update it after database has been created. This is what I did and worked like a charm.

 Reference: DBCA errors when cluster_interconnects is set (Doc ID 1373591.1)

Not always the more the better. ORA-04031 provoked by parallel servers

I thought is worth to write about this incident that happened in one of my servers a couple of months ago. The reason is to show that to add or increase resources is not always the right approach to solve a problem.

ASL - My thermometer for the database

I suppose that most of you, DBAs,  have been asked sometime questions like "How is the general performance of the database?",  "I think there are a problem somewhere, is the database running well?", "Yesterday, at some moments of the day, the database run slow. Can you check what happened?".

Sure, those questions sound familiar to you. How could we give an answer to them?. Of course, we can take a look at the typical ratios, check AWR or Statspack reports, take a look at the OS, etc, etc.

Installation of Oracle Grid/RAC 11.2.0.4 on RHEL 7 or Oracle Linux 7

Some days ago, I had to install Oracle 11.2.0.4 on Red Hat Linux 7 and Grid Infrastructure for a standalone server. What I expected to be a quick task turned to be trickier than expected. Here outlined are the steps for the installation.

Data Guard: Using a Physical Standby database for testing

Here is a test I made. Usually, a standby database is used to provide contingency. But
we can use it sometimes for some testing. Let's say we want to try a job, to check some performance issues, to test a new application, etc.. but we do not want to do it in the main database. Here is a way to do it:

A free troubleshooting tool. Session Sampler

As I needed a tool that permit me to sample the activity of active sessions in Oracle and analyze the data, I wrote a tool for sampling in Python.

This tool is written for troubleshooting and not for continuous monitoring. Is not a substitute for ASH, but can be very helpful to see what happens in the database at one moment in time.

How to extract the hints we need from a SQL Query.

Let's say you have a SQL query running in one database and you need to run it in another similar one with the same execution plan perhaps only
once just to see its behaviour. Let's say you think "Hey, I could do it if I had the appropiate hints". But you do not know for sure
which hints exactly use. Here is a way to retrieve them!:

Troubleshooting a slow RMAN backup

(As I always do, the names of the databases and files are not the real ones, but the numbers are).

Yesterday we got a complain for one client. He said that the RMAN backup of the database was running on working hours and that should not be. I looked at the times of the backup along the week and saw that the backup took much longer than expected.

Recovering a deleted datafile using lsof

Here is a little trick that can be useful in case of a accidental delete of one datafile.
I do not recommend or encourage anyone to do it, but could be useful under certain specific situations.

How to find out what is consuming all of my bandwidth

Some days ago we received a complain from the networking department stating that some connections from one of our database servers were consuming most of the available bandwidth.
Obviously, the highest chance was that it was an oracle process, more, because it was our server who was sending the data, and the only applicacion in this server is Oracle.

Mapping a process in a remote server to a oracle session

Something we have probably needed many times is to find in a remote server the program that is running a session against our database.
Perhaps and for any reason,  we need to know exactly which concrete process in the remote server is accessing our database (from the
operating system perspective).
Let's say is a sqlplus prompt, but when we log on the remote system, we see that there are many sqlplus processes runnning. Which
is the right one?

Who And What SQL Is Using Temp Segments ?

Recently I came across an incident related to temporary segments. When I checked the view V$SORT_USAGE to take a look at what was using my temporary tablespace, I got shocked to see a query of the type "select 1 from dual". Obviously, this query does not do any kind of sort!

So I decided to take a close look at the issue.

First thing I noticed:

ORA-00030, 00000, "user session ID does not exist"

I think it's worth to write a short entry in my blog regards to it. That happened to me some days ago. First time I met this issue.

We were told that a session was blocking another session and preventing it to run a DELETE on one table. Up to this point, everything is normal. I ran an ALTER SYSTEM KILL SESSION.. on the blocking session, and, to my surprise, appeared this message:

AWR Does not show where's the problem. Just shows what is happening!

It looks like something that does not need to be said, but is a trap where many junior DBA fall. AWR (or Statspack) can be misleading if we do not keep in mind this detail:

What AWR and Statspack report shows is not where or what is the problem, but just what was happenning in the database at the moment of the snapshot!.

That means, that we need to interpret correctly what we are seeing at the report. Here I'd like to share an example that illustrates this point.

Identifying memory pressure - Part 4

Continue from part 3...

MEMORY AVAILABLE

At this point, I want to introduce a new parameter: MemAvailable.

This parameter is available is included in /proc/meminfo since version 3.14. In Red Hat, can be backported to previous versions from kernel-2.6.32-504.el6. Just need to set the value of sysctl parameter vm.meminfo_legacy_layout to 0. Then check at /proc/meminfo the new parameter MemAvailable.

Identifying memory pressure - Part 3

Continue from part 2....

AND WHAT DO WE DO WITH ALL THIS?

From the point of view of forecasting, (that is, if I want to know the amount of memory I have to put on one server, or if the actual memory of a server will be enough for the current workload), I have taken this approach:

Identifying memory pressure - Part 2

Continue from part 1....

OK, ALL THIS STUFF LOOKS GREAT, BUT HOW COULD I ESTIMATE THE SIZE OF A PROCESS IN MEMORY?

Taking into account what have been told, the Resident Set Size is the closest to the size in memory of a process, but when checking it by means of top, ps, or another Unix tool we find a problem. The output of those commands takes into account the shared memory segments and the shared libraries, so the results are oversized for double counting the sizes.

Identifying memory pressure - Part 1

ALL WHAT COMES NEXT HAVE BEEN TESTED ON RED HAT LINUX V 6. MOST OF IT SHOULD BE APPLIABLE TO ANOTHER UNIX OR LINUX SYSTEMS

THINGS CAN CHANGE DEPENDING ON MANY FACTORS (NUMA, Huge Pages, etc), BUT WHAT COMES NEXT SGOULD BE EASILY ADAPTABLE.

In the beginning, my idea was to check the amount of physical memory used by Oracle processesin order to do some forecasting and capacity planning.

Unfortunately, I've seen that is very difficult to calculate the exact amount of memory used by Oracle due to many reasons: