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 11.2.0.4
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.
SELECT host_name, instance_name, instance_number
FROM gv$instance
ORDER BY instance_number
/
HOST_NAME INSTANCE_NAME INSTANCE_NUMBER
--------------- --------------- ---------------
homeOELdb01 orapdb1 1
homeOELdb02 orapdb2 2
homeOELdb03 orapdb3 3
homeOELdb04 orapdb4 4
But what about the standby nodes? I can query v$archive_dest_status
SELECT destination FROM V$ARCHIVE_DEST_STATUS WHERE status = 'VALID' AND type = 'PHYSICAL' / DESTINATION -------------------- orasdb
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,
DGMGRL> show configuration
Configuration - orapdb_dgconf
Protection Mode: MaxPerformance
Databases:
orapdb - Primary database
orasdb - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> show database 'orasdb'
Database - orasdb
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Apply Rate: 21.80 MByte/s
Real Time Query: OFF
Instance(s):
orasdb1 (apply instance)
orasdb2
orasdb3
Database Status:
SUCCESS
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.
Name Null? Type ----------------------------------------- -------- ---------------------------- ADDR RAW(8) INDX NUMBER INST_ID NUMBER OBJECT_ID NUMBER ATTRIBUTE VARCHAR2(30) VALUE VARCHAR2(512) PARENT_ID VARCHAR2(15) STATUS VARCHAR2(30) MESSAGE VARCHAR2(256) ERRNUM NUMBER VALUE_RAW RAW(512)
Because there isn’t a lot of information on this table I had to make some assumptions, based on the data
SELECT inst_id,
object_id,
parent_id,
attribute,
VALUE
FROM X$DRC
ORDER BY inst_id, object_id, parent_id
/
INST_ID OBJECT_ID PARENT_ID ATTRIBUTE VALUE
---------- ---------- --------------- ------------------------------ --------------------
1 4096 DRC orapdb_dgconf
1 4096 intended_state ONLINE
1 4096 protection_mode MaxPerformance
1 4096 enabled YES
1 4096 fast_start_failover DISABLED
1 50331648 4096 SITE orapdb
1 50397184 50331648 DATABASE orapdb
1 50397184 intended_state READ-WRITE-XPTON
1 50397184 role PRIMARY
1 50397184 enabled YES
1 50397184 RAC YES
1 50397184 connect_string orapdb
1 50397185 50331648 INSTANCE orapdb4
1 50397185 host homeOELdb04
1 50397186 50331648 INSTANCE orapdb2
1 50397186 host homeOELdb02
1 50397187 50331648 INSTANCE orapdb1
1 50397187 host homeOELdb01
1 50397188 50331648 INSTANCE orapdb3
1 50397188 host homeOELdb03
1 67108864 4096 SITE orasdb
1 67174400 67108864 DATABASE orasdb
1 67174400 connect_string orasdb
1 67174400 intended_state PHYSICAL-APPLY-ON
1 67174400 RAC YES
1 67174400 role PHYSICAL
1 67174400 enabled YES
1 67174401 67108864 INSTANCE orasdb1
1 67174401 host homeOELdb01s
1 67174402 67108864 INSTANCE orasdb2
1 67174402 host homeOELdb02s
1 67174403 67108864 INSTANCE orasdb3
1 67174403 host homeOELdb03s
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.
SELECT b.VALUE as HOST, a.VALUE as INSTANCE
FROM X$DRC a, X$DRC b
WHERE a.parent_id IN (SELECT object_id
FROM X$DRC
WHERE VALUE NOT IN (SELECT db_unique_name
FROM v$database)
AND attribute = 'SITE')
AND a.attribute = 'INSTANCE'
AND a.object_id = b.object_id
AND b.ATTRIBUTE = 'host'
/
HOST INSTANCE
-------------------------- ----------
homeOELdb01s orasdb1
homeOELdb02s orasdb2
homeOELdb03s orasdb3
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.
select * from X$DRC * ERROR at line 1: ORA-16525: The Oracle Data Guard broker is not yet available.
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