Nov 17

Where Are My Nodes?

I should mention the current setup is a 4 node RAC database with a dataguard managed standby comprised of a 3 node RAC database cluster. The current database version is

While writing a shell script today, I needed to know from connecting to a database in a cluster what all the related nodes were. This isn’t a big deal, I could simply query gv$instance to pull a list of the host names, and the associated instances.

But what about the standby nodes? I can query v$archive_dest_status

Ok, great, that is the name of my standby database cluster, but there are three nodes! I want to see what is going where! I could use dgmgrl,

So that gives us the databases in the standby, but parsing this out would be a pain. I just want to generate a report, and it is much easier to be able to query the database to generate my output. However I couldn’t find a place to do this!

Enter the X$DRC view. I could find very little about this view itself. With any X$ table, the view is rather cryptic, but I was able to get enough information out of it to solve my problem.

Because there isn’t a lot of information on this table I had to make some assumptions, based on the data

You can see the primary instances and their hosts listed above. To pull the standby databases out, I used the following query. I am guessing this could be made a lot cleaner, but I ran out of brain power while trying to optimize it, so here is the current query.

I now have the information I want to include in my report. You could easily modify this to get all the nodes in the primary and the standby, but I would prefer to use gv$instance to get the primary nodes.

1/14/2016 Update

I didn’t notice this until much much later, if data guard is not enabled, The query on X$DRC will fail.

I was using this to get my primary and standby nodes, and union them together, this was returning an empty set to me. I was spooling this information out to a text file to be used with a shell script, so my solution was to just break into two pieces, first spool out any RAC nodes, then check to see if there are any standby destinations enabled, and if so then spool out standby databases. This seems to work fine.

This is why it isn’t safe to use X$ tables, they aren’t documented and you may not get expected behavior out of them. I would have assumed I would just back zero rows, but that would be to assume the X$ table is like a V$ table. Not true, the X$ tables are used for reading portions of the oracle memory, mostly, but can also bull information like control file information into the memory to be read. So if the memory segment the data guard, or possibly the data guard broker filed don’t exist, this table cannot function.

To learn more about X$ tables, I suggest these three sites, they are my go to for X$ information

Tanner Poder: Oracle X$ tables – Part 1 – Where do they get their data from?

Morgan’s Library: Oracle X$ Structures

ORAFAQ: X$ Table naming structures


Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">