Query to get the undocumented parameters and their values

I am posting this to have a handy reference, as x$ tables are greek to me and I am not aware of another way to do this other than query the x$ tables.

Here is a query to display all hidden or undocumented parameters and their default values.

SELECT
a.ksppinm "Parameter",
b.ksppstvl "Session Value",
c.ksppstvl "Instance Value"
FROM
x$ksppi a,
x$ksppcv b,
x$ksppsv c
WHERE
a.indx = b.indx
AND
a.indx = c.indx
AND
a.ksppinm LIKE '/_%' escape '/'
/

When you run this query, expect to see hundreds of rows where the undocumented parameters start with an “_”. Hence you’d probably want to filter this by providing the parameter of interest in the LIKE clause of the query.

Oracle 10g RAC Cluster interconnects

Yet another post on … say, you’ve just joined a new client, you are in a new RAC environment and you are trying to figure out the different components of the cluster.

This time it is on the Cluster private interconnects.

The private interconnects used in a RAC are typically configured during the Clusterware install and would be registered in OCR. However this default config could thereafter be overrided using cluster_interconnects initialization parameter (not recommended though ) for various reasons.

In order to troubleshoot interconnect related issues, a starting point would be to find out what interconnects were originally configured and/or what interconnects are presently in use by the cluster. Here are a few ways to do this.

1. Using the dynamic view gv$cluster_interconnects:

select * from gv$cluster_interconnects ;

INST_ID    NAME            IP_ADDRESS       IS_ SOURCE
---------- --------------- ---------------- --- -------------------------------
1          eth0            192.168.10.1     NO  Oracle Cluster Repository
1          eth1            192.168.11.1     NO  Oracle Cluster Repository
2          eth0            192.168.10.2     NO  Oracle Cluster Repository
2          eth1            192.168.11.2     NO  Oracle Cluster Repository

In the above output, the column SOURCE indicates where the private interconnect info was derived from; this column could be one of OCR, OS dependent software or cluster_interconnects parameter.

2. Using the clusterware command oifcfg:

$oifcfg getif
eth2 10.104.95.0 global public
eth0 192.168.10.0 global cluster_interconnect
eth1 192.168.11.0 global cluster_interconnect

3. Using oradebug ipc:

sqlplus “/ as sysdba”

SQL>oradebug setmypid
Statement processed.
SQL>oradebug ipc
Information written to trace file.

The above command would dump a trace to user_dump_dest. The last few lines of the trace would indicate the IP of the cluster interconnect. Below is a sample output of those lines.

From the trace file on node1:

SSKGXPT 0×5edf558 flags SSKGXPT_READPENDING socket no 9 IP 192.168.11.1 UDP 18852

From the trace file on node2:

SSKGXPT 0×5edf558 flags SSKGXPT_READPENDING socket no 9 IP 192.168.10.2 UDP 38967