Testing Multicasting for Oracle 11.2.0.2 Grid Infrastructure

Oracle 11.2.0.2 Grid Infrastructure uses multicasting during installation of CSSD. Multicasting is usually enabled by default, but can be disabled by some network administrators at switch level.

If multicasting is not enabled then installation of the Grid Infrastructure will fail when root.sh is run on the second node. By default Oracle uses multicast address 230.0.1.0. If this is not available then configuration of CSSD will fail. A patch is available for Bug 9974223 – Grid Infrastructure needs multicast communication on 230.0.1.0 address working. The patch allows Oracle to use an alternative multicast address 224.0.0.251 if the 230.0.1.0 is not available. The patch must be installed during the Grid Infrastructure installation, prior to running the root.sh or rootupgrade.sh scripts on each node. A new version of OPatch is also required to install this patch. Obviously installing this patch increases the complexity of the Grid Infrastructure installation / upgrade.

Oracle has issued a utility that checks whether the patch is required. The utility can be downloaded from MOS Note 1212703.1 Grid Infrastructure install or upgrade may fail due to Multicasting. The utility can be downloaded and installed by the grid user (as opposed to root).

The downloaded file is called mcasttest.tgz; it should be unzipped and unarchived into an empty directory using:

[grid@server19]$ gunzip mcasttest.tgz
[grid@server19]$ tar xfv mcastest.tar

This files are unarchived into a subdirectory called mcasttest which contains the following files:

[grid@server19]$ ls -l
-rwxr-xr-x 1 grid oinstall 15048 mcast2.aix.ppc64
-rwxr-xr-x 1 grid oinstall 73380 mcast2.hpux.ia64
-rwxr-xr-x 1 grid oinstall 32768 mcast2.hpux.parisc64
-rwxr-xr-x 1 grid oinstall 10232 mcast2.linux.x32
-rwxr-xr-x 1 grid oinstall 14799 mcast2.linux.x64
-rwxr-xr-x 1 grid oinstall 11852 mcast2.solaris.sparc64
-rwxr-xr-x 1 grid oinstall 11328 mcast2.solaris.x64
-rwxr-xr-x 1 grid oinstall 12093 mcasttest.pl
-rw-r--r-- 1 grid oinstall  3455 README.txt

As you can see there is a Perl script called mcasttest.pl and a series of binaries for each of the major Unix-like platforms supported by Oracle. The dates have been omitted to fit the listing on the page.

The mcasttest.pl script takes the following options:

# Options:
# -n node,node,...             List of cluster nodes
# -i interface,interface,...   List of interfaces to test
# -m IP,IP,...                 Multicast Address(es) to test
# -d directory                 Directory to place executable
# -g debug level               Debug Level 'low' or 'high'

The -n option specifies a list of nodes e.g -n server19,server20

The -i option specifes a list of interfaces e.g. -i bond2

By default the utility will test the following multicast addresses and ports:
• 230.0.1.0:42000
• 224.0.0.251:42001.

The Perl script processes the options, determines the operating system type and then executes the appropriate binary.

So far we have seen three possible outcomes. All examples were run by the grid user:

Example 1: Both addresses succeed (tested on Solaris SPARC)

$ ./mcasttest.pl -n server21,server22 -i e1000g2
###########  Setup for node server21  ##########
Checking node access 'server21'
Checking node login 'server21'
Checking/Creating Directory /tmp/mcasttest for binary 
  on node 'server21'
Distributing mcast2 binary to node 'server21'
###########  Setup for node server22  ##########
Checking node access 'server22'
Checking node login 'server22'
Checking/Creating Directory /tmp/mcasttest for binary 
  on node 'server22'
Distributing mcast2 binary to node 'server22'
###########  testing Multicast on all nodes  ##########

Test for Multicast address 230.0.1.0

Nov 22 13:24:43 | Multicast Succeeded for e1000g2 
  using address 230.0.1.0:42000

Test for Multicast address 224.0.0.251

Nov 22 13:24:44 | Multicast Succeeded for e1000g2 
  using address 224.0.0.251:42001

In the above example the multicast test was successful for both nodes. No further action is require to install Grid Infrastructure.

Example 2: First address fails, second address succeeds (tested on Linux x86-64)

$ ./mcasttest.pl -n server23,server24 -i bond1
###########  Setup for node server23  ##########
Checking node access 'server23'
Checking node login 'server23'
Checking/Creating Directory /tmp/mcasttest for binary 
  on node 'server23'
Distributing mcast2 binary to node 'server23'
###########  Setup for node server24  ##########
Checking node access 'server24'
Checking node login 'server24'
Checking/Creating Directory /tmp/mcasttest for binary 
  on node 'server24'
Distributing mcast2 binary to node 'server24'
###########  testing Multicast on all nodes  ##########

Test for Multicast address 230.0.1.0

Nov 19 11:29:11 | Multicast Failed for bond1 
  using address 230.0.1.0:42000

Test for Multicast address 224.0.0.251

Nov 19 11:29:12 | Multicast Succeeded for bond1 
  using address 224.0.0.251:42001

In this example the test for the 230.0.1.0 address has failed, but the test for the 224.0.0.251 address has been successful. In this case the patch for bug 9974223 should be installed.

Example 3: Both addresses fail (tested on Solaris SPARC)

$ ./mcasttest.pl -n server21,server22 -i e1000g2
###########  Setup for node server21  ##########
Checking node access 'server21'
Checking node login 'server21'
Checking/Creating Directory /tmp/mcasttest for binary 
  on node 'server21'
Distributing mcast2 binary to node 'server21'
###########  Setup for node server22  ##########
Checking node access 'server22'
Checking node login 'server22'
Checking/Creating Directory /tmp/mcasttest for binary 
  on node 'server22'
Distributing mcast2 binary to node 'server22'
###########  testing Multicast on all nodes  ##########

Test for Multicast address 230.0.1.0

Nov 22 10:40:40 | Multicast Failed for e1000g2 
  using address 230.0.1.0:42000

Test for Multicast address 224.0.0.251

Nov 22 10:41:11 | Multicast Failed for e1000g2 
  using address 224.0.0.251:42001

In this example both addresses have failed. Installation of Grid Infrastructure will definitely fail. It will be necessary either to enable multicasting for the existing switches or to use new switches for the private network which permit multicasting.

Thanks are due to several of my customers for assisting with the research for this post including Mike Shield, Abie Solano, Oliver Tweedie and Andy Thorpe.

UKOUG Technical and E-Business Suite Conference 2010 – The Controversial Stuff

So the UKOUG Technical and E-Business Suite Conference is over for another year. Considering the adverse external factors, the event was incredibly successful (in my opinion). We had expected that the recession and imminent public sector cutbacks would affect attendance this year. However, despite the inclement weather, attendances were surprisingly resilient this year. This is undoubtedly due to the quality of speakers that we attract from around the world and the quality of our audience, both domestic and international.

In this post I will try to explain one or two unexpected issues that we faced this year.

First of all we experienced an unprecedented number of issues with the agenda. Many of these were caused by late speaker cancellations due to the unexpectedly adverse weather often preventing speakers from travelling. There is not much we can do about the weather. However, it was suggested to me by several attendees that we could copy some of the larger US conferences by asking regular speakers to bring along a reserve presentation in order to avoid gaps in the agenda.

The remaining agenda issues were almost entirely caused by the retrospectively bad decision to hold judging and agenda planning later in the year. The main motivation for this decision was to ensure papers selected for conference were as current as possible. In particular we would like to have been able to consider material and announcements from Oracle OpenWorld. As it happened the latest date it was possible to hold the agenda planning meeting was early September, too early for OpenWorld anyway. However shifting this meeting back put a huge amount of pressure on UKOUG staff for whom the conference is the largest single operation. Speakers were notified during OpenWorld and it takes at least six weeks to get confirmations and to source suitable replacements in the event the speaker can no longer take up the slot. Apparently the office dealt with over 600 e-mails regarding agenda changes following the original planning meeting.

As a director I can only apologise for the inconvenience the resulting agenda changes have caused to speakers, session chairs and delegates and the unnecessary pressure inflicted on the staff.

As it happens I was not present at the meeting where the decision to delay paper selection was made; I would have strongly opposed it, but not because I have some crystal ball; As a regular speaker I know it takes at least one month for me to prepare a new presentation and I would have been selfishly concerned that I would not have time to develop new material for two presentations. I also recognize that the late acceptance of sessions puts unnecessary pressure on other speakers making it difficult to schedule time and potentially leading to increased travel and accommodation costs.

I also have to take some of the blame for the agenda as I am a member of the agenda planning team. I do not to participate in abstract judging, but I am involved in scheduling the server technology stream. We try to select papers based on judges scores but with some sensitivity to the material so, for example, we don’t get eight presentations on the same topic. Server technology is the largest stream usually with at least three concurrent sessions often allocated to the three largest halls available. During the planning meeting we produced a reasonably attractive and consistent agenda. However, subsequent changes are made in response to speaker cancellations, replacements, dependencies on travel arrangements etc. In past years this process has worked reasonably well; this year due to the compressed timetable we needed to respond much more rapidly which meant it was difficult to retain an overview of the entire agenda. We recognize that a better process is needed for next year.

One of the main victims of this process was myself. I originally submitted three abstracts, of which two were selected in the planning meeting. The third presentation was discarded under our rule that most presenters have a maximum of two sessions. In retrospect I should have torn up the card for the third session at the time but it somehow sneaked back onto the agenda as a late replacement. Unfortunately I only discovered this on the Thursday before the event at which point it was far too late to develop a new presentation and there was no alternative but to cancel it. I must apologize again to anyone who turned up to “The Statistics Never Lie?”; they might not but on this occasion the printed agenda definitely did.

I would like to thank Dan Morgan for stepping in at the last moment with a really excellent replacement presentation on Very Large Databases – it was much better than mine would have been and it was a pity that the confusion over scheduling resulted in a smaller audience than the presentation deserved.

In order to ensure the continued financial viability of the event, we made some changes to the structure of the conference. A couple of these worked incredibly well in my opinion:

• We moved the early evening social events from a dedicated hall into the exhibition hall. I thought this was an inspired decision; the exhibitors were pleased with the additional footfall and for delegates like myself, it was great to be able to have conversations without intrusive music or other distractions. My kids used to like jugglers and unicyclists; I’m not sure I need them around when I am trying to discuss RAC upgrades or to close a consultancy deal.
• We moved the speaker lounge to the balcony of the exhibition hall. Again this worked well as the speakers were more centrally located and had to traverse the reception area to access the lounge making them more accessible to other delegates. Previously they were located in the Media Suite which segregated them completely from other attendees.

So all in all I think this was a successful event despite the weather which affected the travel plans of many delegates. When I left on Wednesday evening there were quite a large number of delegates stranded in Birmingham because of flight delays, rail cancellations or road closures. There’s not much we can do about the weather, but there are definitely worse places in the world to get stuck.

I will discuss the individual sessions that I attended in separate posts. In the meantime, thanks to everyone who contributed to such an enjoyable and productive event.

Recoverable Scripts

The DBMS_RECOVERABLE_SCRIPT package is called by the MAINTAIN_TABLES procedure in the DBMS_STREAMS_ADM package. It is probably called from a few other places as well. As I hit an error attempting to set up Streams replication of a table from one database to another, I thought I would document the troubleshooting process.

 My script failed with the following error:

ERROR at line 1:
ORA-23616: Failure in executing block 8 for script
954444D7D90C47DBE040A8C07702188D with
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "SYS.DBMS_RECO_SCRIPT_INVOK", line 139
ORA-06512: at "SYS.DBMS_STREAMS_RPC", line 465
ORA-06512: at "SYS.DBMS_RECOVERABLE_SCRIPT", line 659
ORA-06512: at "SYS.DBMS_RECOVERABLE_SCRIPT", line 682
ORA-06512: at "SYS.DBMS_STREAMS_MT", line 7972
ORA-06512: at "SYS.DBMS_STREAMS_ADM", line 2526
ORA-06512: at "SYS.DBMS_STREAMS_ADM", line 2593
ORA-06512: at line 2

In 11.2 there is a package called DBMS_RECOVERABLE_SCRIPT that can be used to manage recoverable scripts. Following my original post I now know this package should not be called directly. See below for further details.

There is also a set of data dictionary views including:

• DBA_RECOVERABLE_SCRIPT
• DBA_RECOVERABLE_SCRIPT_BLOCKS
• DBA_RECOVERABLE_SCRIPT_ERRORS
• DBA_RECOVERABLE_SCRIPT_PARAMS
• DBA_RECOVERABLE_SCRIPT_HIST

DBA_RECOVERABLE_SCRIPT contains one row for each recoverable script. The primary key is the SCRIPT_ID – in my case “954444D7D90C47DBE040A8C07702188D” which is obviously system-generated

The following query reports the current status of the script:

SQL> SELECT status, total_blocks, done_block_num
  2  FROM dba_recoverable_script
  3  WHERE script_id = '954444D7D90C47DBE040A8C07702188D';

STATUS       TOTAL_BLOCKS DONE_BLOCK_NUM
------------ ------------ --------------
ERROR                  11              7

So this query confirms that my script has failed with an error having completed seven of its eleven blocks.

The recoverable script is divided into blocks. DBA_RECOVERABLE_SCRIPT_BLOCKS contains one row for each block in the recoverable script. The primary key is formed by the SCRIPT_ID and BLOCK_NUM columns. Each block consists of a FORWARD_BLOCK CLOB which contains the actions required to complete the block and an UNDO_BLOCK CLOB which contains the actions required to reverse out the block. There is also a STATUS column and a BLOCK_COMMENT column.

The following script provides a more detailed summary of the execution of the script:

SQL> SELECT block_num,status
  2  FROM dba_recoverable_script_blocks
  3* WHERE script_id = '954444D7D90C47DBE040A8C07702188D' ORDER BY 1

BLOCK_NUM STATUS
---------- ------------
         1 EXECUTED
         2 EXECUTED
         3 EXECUTED
         4 EXECUTED
         5 EXECUTED
         6 EXECUTED
         7 EXECUTED
         8 ERROR
         9 NOT EXECUTED
        10 NOT EXECUTED
        11 NOT EXECUTED

The error was in block 8.

Let’s find out what the block 8 was trying to do.

SELECT forward_block
FROM dba_recoverable_script_blocks
WHERE script_id = '954444D7D90C47DBE040A8C07702188D'
AND block_num = 8;

--
-- Get tag value to be used for Apply
--
DECLARE
  found            BINARY_INTEGER := 0;
  tag_num          NUMBER;
  apply_nm         VARCHAR2(30);
  apply_nm_dqt     VARCHAR2(32);
BEGIN
  SELECT apply_name INTO apply_nm
  FROM dba_apply_progress
  WHERE source_database = 'EAST.JULIANDYKE.COM';

  apply_nm_dqt := '"' || apply_nm || '"';
  -- Use the apply object id as the tag
  SELECT o.object_id INTO tag_num
  FROM dba_objects o
  WHERE o.object_name= apply_nm AND
        o.object_type='APPLY';
  LOOP
    BEGIN
      found := 0;
      SELECT 1 INTO found FROM dba_apply
      WHERE apply_name != apply_nm AND
            apply_tag = hextoraw(tag_num);
    EXCEPTION WHEN no_data_found THEN
      EXIT;
    END;
    EXIT WHEN (found = 0);
    tag_num := tag_num + 1;
  END LOOP;
  -- alter apply
  dbms_apply_adm.alter_apply(
    apply_name => apply_nm_dqt,
    apply_tag => hextoraw(tag_num));
END;

Now let’s check the error message in DBA_RECOVERABLE_SCRIPT_ERRORS for this block.

SQL> SELECT error_message
  2  FROM dba_recoverable_script_errors
  3  WHERE script_id = '954444D7D90C47DBE040A8C07702188D'
  4  AND block_num = 8;

ERROR_MESSAGE
----------------------------------------------------------------------------
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "SYS.DBMS_RECO_SCRIPT_INVOK", line 139
ORA-06512: at "SYS.DBMS_STREAMS_RPC", line 465

So this confirms what we already knew – an exact fetch has probably returned more than one row.

Not particularly useful if you captured the original failure in SQL*Plus; very useful if the error was embedded in application code or the exception was not captured correctly.

DBA_RECOVERABLE_SCRIPT_PARAMS contains a list of parameters for the script together with their values; in this case there were 22. In this example the list of parameters is very similar to the list of parameters for the MAINTAIN_TABLES procedure in the DBMS_STREAMS_ADM package.

The other view is DBA_RECOVERABLE_SCRIPT_HIST which contains a history of script executions. When I was testing this feature in a newish database, this view was empty suggesting that rows are only added to the history view when the entire script has executed successfully or when it is deleted.

Back to the error – well I’m not impressed. Prior to testing Oracle Streams I had set up Change Data Capture of which more another time. On the apply database the statement:

SELECT apply_name,source_database FROM dba_apply_progress;

returned the following output:

APPLY_NAME                     SOURCE_DATABASE
------------------------------ ------------------------------
CDC$A_CHANGE_SET3              EAST.JULIANDYKE.COM
APPLY$_EAST_5                  EAST.JULIANDYKE.COM

I guess there are a couple of ways to fix this – delete the CDC configuration or modify the FORWARD_BLOCK for the script. Neither solution is particularly user-friendly. However, at least I know what the problem is…

I decided to drop the CDC apply process using:

BEGIN
  dbms_apply_adm.stop_apply ('CDC$A_CHANGE_SET3',force=>TRUE);
  dbms_apply_adm.drop_apply ('CDC$A_CHANGE_SET3');
END;
/

Now the query of DBA_APPLY_PROGRESS only returns one row (the correct one).

Alternatively I suspect I could have corrected the recoverable script using the MODIFY_FORWARD_BLOCK in the DBMS_RECOVERABLE_SCRIPT package.

We now need to restart the recoverable script. The obvious thing to do here is to look up the package in the PL/SQL Packages and Types Reference, but unfortunately this package is not documented. So back to trial and error…

The obvious candidate is the RUN procedure in the DBMS_RECOVERABLE_SCRIPT package. This turns out to have been the wrong choice as noted in the comment from Pat McElroy; I should have used the RECOVER_OPERATION procedure in the DBMS_STREAMS_ADM package which can be used to roll forward, roll back or purge a recoverable script. This makes a lot more sense – I was uncomfortable using the RUN procedure as I was not certain whether it would re-execute the successful steps for a second time.

I originally executed the MAINTAIN_TABLES procedure as the GP user (part of my Formula 1 database). When I attempted to execute the RUN procedure as this user I got an error because it could not directly access the DBMS_RECOVERABLE_SCRIPT package. To speed things up I switched to the SYS user and run:

GRANT EXECUTE ON DBMS_RECOVERABLE_SCRIPT TO GP;

CREATE PUBLIC SYNONYM DBMS_RECOVERABLE_SCRIPT 
FOR SYS.DBMS_RECOVERABLE_SCRIPT;

Then as the GP user I was able to execute the following command:

BEGIN
  dbms_recoverable_script.run 
  (script_id=> '954444D7D90C47DBE040A8C07702188D');
END;
/

After a few seconds this script returned the following:

PL/SQL procedure successfully completed.

Do not try this at home – as noted above I should have used the RECOVER_OPERATION procedure in the DBMS_STREAMS_ADM package.

So Oracle thinks it worked. Do I agree? A review of the dictionary table reveals the following:

DBA_RECOVERABLE_SCRIPT is now empty; the script I was executing has disappeared.

DBA_RECOVERABLE_SCRIPT_ERRORS is also empty. Good – no more errors.

DBA_RECOVERABLE_SCRIPT_BLOCKS still contains the 11 blocks for the script we have been executing.

DBA_RECOVERABLE_SCRIPT_PARAMS still contains the 22 parameters for the script we have been executing.

DBA_RECOVERABLE_SCRIPT_HIST now contains one row for the script we have successfully executed.. The primary key is SCRIPT_ID. We can check the status of this script using:

SQL> SELECT status, total_blocks, done_block_num
  2  FROM dba_recoverable_script_hist
  3  WHERE script_id = '954444D7D90C47DBE040A8C07702188D';

STATUS   TOTAL_BLOCKS DONE_BLOCK_NUM
-------- ------------ --------------
EXECUTED           11             11

All seems to make sense.

However I do think it would help if the DBMS_RECOVERABLE_SCRIPT package was added to the Package Types and Reference manual stating that scripts should not be run directly using this package and that the RECOVER_OPERATION procedure in the DBMS_STREAMS_ADM package should be run instead. The important thing for Oracle to remember here is that users are unlikely to encounter these scripts until something breaks at which point (like me) they will be under pressure to find a quick fix so they can proceed with the task in hand.

Linux RPM Checker

I have been regularly installing Oracle RAC on Linux since 2003. One area that has consistently frustrated me is checking for required RPM packages, particularly in x86-64 environments. In the past the list of packages has often been imcomplete and it has been necessary to consult Oracle documentation, Metalink and the wider internet to arrive at a comprehensive list.

To their credit Oracle have tried to address this in Oracle 11.2 where the Oracle Installer now checks for packages at the end of the interview process. In a RAC environment, the CVU can also perform checks prior to installation.

Though recent versions of the Oracle database are a great improvement, there are still a few areas of concern including:

- Pre-Oracle 11.2 systems. It is still necessary to build older versions of Oracle to test upgrades and to support legacy databases; the new Installer functionality cannot help here

- Other products e.g. Enterprise Manager Grid Control which do not include the new Installer functionality yet

- Packages are often installed by system administrators as part of a standard build before the Oracle environment is created. Failure to install these packages can result in long and costly delays while the additional packages are added to the standard build.

Another problem is that in x86-64 environments both 32-bit and 64-bit versions of some packages are required.

Within my consultancy business I also have a requirement when generating documentation for a list of required packages and a list of actual packages. These lists are tedious to type in and consequently very error prone.

So I decided to do something about it.

My first approach was to develop an RPM and use the in-built RPM dependency checking algorithm to check versions. It took a while to figure out how to create a spec file for the rpmbuild utility, but eventually I managed to create a package. However, when I tried to extend the package to handle both 32-bit and 64-bit architectures I discovered that there is a fundamental flaw in RPM – it only appears to work for the architecture on which it is executing – I could not find a way to specify packages of another architecture (i386 etc) in the requires directive of the spec file. Getting RPM enhanced might be possible, but I have better things to do with my life and there would still be an issue with compatibility on legacy systems.

However, I did manage to find the source files for RPM and discovered that it uses a library function called rpmvercmp to compare first versions and then releases for packages. A quicker option was to convert the C code into Perl. I like Perl because it is usually available on any Unix-based Oracle environment. My Perl skills are not great, but I managed to develop a working comparison algorithm within a few hours.

The next problem was to provide the Perl script with a list of required packages together with their architectures and minimum versions. XML was an obvious choice but I needed to confirm it would work with Perl. In fact there are  several XML options with Perl – I originally implemented the utility using the XML::Simple package. However, this meant it was necessary to install the perl-XML-Simple package in order to run the checker, requiring a change control at some sites. I decided this would be an unnecessary restriction, so I rewrote the XML parser components in Perl.

The rpmcheck utility is supplied with a set of XML definition files. Each file is based on:

• Oracle product e.g. Grid Infrastructure
• Oracle release e.g. 11.2.0.2
• Operating system e.g. RHEL5
• Architecture e.g x86_64

I think this provides sufficient granularity. At present I have only prepared XML files for a handful of releases – I will expand these as I revisit Linux customers.

If you wish to try these scripts they can be downloaded from my website. Instructions to run the Perl script are included.

The basic procedure is:

1) Download the files from http:://www.juliandyke.com – files are currently delivered in a zip file (rpmcheck.zip)

2) Unzip the archive

unzip rpmcheck.zip

3) Run the checker specifying the appropriate XML file as a parameter. For example:

perl rpmcheck.pl GridInfrastructure-11_2_0_2-RHEL5-x86-64.xml

   The script will report if all packages are valid; if not it provide details of expected and actual (if any) versions of the packages.

Further details and sample output can be found on my website http://www.juliandyke.com/rpmcheck.

The script and XML files can be downloaded directly from http://www.juliandyke.com/Tools/rpmcheck/rpmcheck.zip

I would definitely like some feedback on this – hopefully it can be extended to a comprehensive list of environments.

Investigating CVU Failures

This week we had a interesting problem with a new two-node cluster. Oracle 11.2 includes an embedded version of the Cluster Verification Utility (CVU) which is executed at the end of the interview process. Since the CVU can fix many problems that it encounters without restarting the installer, I have got out of the habit of running the stand-alone CVU prior to an installation.  This time the OUI failed reporting that shared disks were not sharable. In this post I will describe the investigative process that we followed; the process is much more instructive than the outcome. In the following notes I have assumed that the unsharable multipath device was /dev/mapper/data (partition /dev/mapper/datap1). The servers are server14 and server15.

The first step was to reproduce the problem using the standalone version of the CVU. In Oracle 11.2 the runcluvfy.sh script is in the same directory as the  runInstaller script. We ran this as follows

./runcluvfy.sh comp ssa -n server14,server15  -s /dev/mapper/datap1

This utility produced the following output:

Verifying shared storage accessibility
 Checking shared storage accessibility...
 "/dev/mapper/datap1" is not shared
 Shared storage check failed on nodes "server15,server14"
Verification of shared storage accessibility was unsuccessful on all the specified nodes.

We tried appending the -verbose parameter, but this did not produce any more informative output.

The next step was to enable CVU trace. On Linux this can be enabled using the CV_TRACELOC parameter. For example:

mkdir /tmp/cvutrace
export CV_TRACELOC=/tmp/cvutrace

The environment variable specifies that trace should be written to a file called cvutrace.log.0 in the /tmp/cvutrace directory.

The trace output allowed us to identify exactly where the failure was occuring, but not the actual cause. By searching the output around the failure we discovered that it was occuring following a call to exectask.

Exectask is an executable that is copied to the /tmp/CVU_11.2.0.2.0_grid directory. It appears to contain all of the port specific system calls required by the CVU. It takes around 20 options and each option returns an XML document containing output and status/error messages. From the CVU trace we determined that exectask was being called with the following parameters:

/tmp/CVU_11.2.0.2.0_grid/exectask -getstinfo 
    -getdiskinfo /dev/mapper/data%/dev/mapper/datap1

The CVU is calling exectask with the -getstinfo (get storage information) parameter and the -getdiskinfo (get disk information) subparameter.  Note that the format of the disk name is device%partition.

Executed stand-alone, we observed that exectask returned different XML documents for each node.

On the first node:

<CV_VAL>
  <disk>
    <disk_name>/dev/mapper/data</diskname>
    <disksignature>PAFUCC29SZ4028</disk_signature>
    <NUMPARTS>0</NUMPARTS>
    <disk_state>0</disk_state>
    <disk_size>5362849792</disk_size>
    <disk_owner>grid</disk_owner>
    <disk_group>asmadmin</disk_group>
    <disk_permissions>0660</disk_permissions>
  </disk>
</CVVAL>
<CV_VRES>0</CV_VRES>
<CV_LOG>Exectask:getDiskInfo success</CV_LOG>
<CV_ERES>0</CV_ERES>

On the second node:

<CV_VAL>
  <disk>
    <disk_name>/dev/mapper/data</diskname>
    <disksignature>PAFUCC29SZ4064</disk_signature>
    <NUMPARTS>0</NUMPARTS>
    <disk_state>0</disk_state>
    <disk_size>5362849792</disk_size>
    <disk_owner>grid</disk_owner>
    <disk_group>asmadmin</disk_group>
    <disk_permissions>0660</disk_permissions>
  </disk>
</CVVAL>
<CV_VRES>0</CV_VRES>
<CV_LOG>Exectask:getDiskInfo success</CV_LOG>
<CV_ERES>0</CV_ERES>

The output has been reformatted to improve readability. On closer inspection, the only difference is the disk signature field.

We used the multpath utility to determine the device names of the SCSI disks (there are probably lots of ways to achieve this)

[root@server14]# multipath -ll /dev/mapper/data
data {36001438005dedd1b0000300002260000} dm-4 HP,HSV400
[size=5.0G][features=0][hwhandler=0][rw]
\_ round-robin 0 [prio=0][active]
\_ 1:0:0:5 sda 8:64 [actve][ready]
\_ round-robin 0 [prio=0][enabled]
\_ 1:0:1:5 sdf 8:144 [actve][ready]

In the above example the devices are /dev/sda and /dev/sdf. We will use /dev/sda.

We then used the scsi_id utility, first to verify the WWID. The default output is:

[root@server14]# /sbin/scsi_id -g -u -s /block/sda
36001438005dedd1b0000300002260000

The -p option of scsi_id specifies the SCSI field returned in the output. The names are not very user-friendly. The default is 0×83. So the following command is identical to the above:

[root@server14]#/sbin/scsi_id -p 0x83 -g -u -s /block/sda
36001438005dedd1b0000300002260000

In this case we are not interested in the WWID, but in the disk identifier. For an HP EVA SAN this turns out to be derived from the controller ID and is stored in SCSI field 0×80. We can use the following command to obtain this value:

[root@server14]# /sbin/scsi_id -p 0x80 -g -u -s /block/sda
SHV_HSV400_PAFUCC29SZ4028

If we execute the same command on the second node we get the following output:

[root@server15]# /sbin/scsi_id -p 0x80 -g -u -s /block/sda
SHV_HSV400_PAFUCC29SZ4064

So the disk identifiers reported by /sbin/scsi_id are different. If the storage is shared they should be identical.

At this point we have proved that this problem is at storage/operating system level and we can reproduce it without Oracle.

After drawing a diagram of the storage we concluded that the problem was with one of the fabric switches, so we disabled the offender. The scsi_id command now returned the same output for both nodes:

[root@server14]# /sbin/scsi_id -p 0x80 -g -u -s /block/sda
SHV_HSV400_PAFUCC29SZ4028
[root@server15]# /sbin/scsi_id -p 0x80 -g -u -s /block/sda
SHV_HSV400_PAFUCC29SZ4028

We could now retry the CVU with more confidence:

./runcluvfy.sh comp ssa -n server14,server15 -s /dev/mapper/data
Verifying shared storage accessibility
Checking shared storage accessibility...
"/dev/mapper/EOrac1_CRS1p1" shared
Shared storage check was successful on nodes "server15,server14"
Verification of shared storage accessibility was successful

Following the successful execution of the CVU, the Grid Infrastructure installation proceeded without any further problems.

Oracle 11.1 Enterprise Manager Grid Control

It took us three days, but we finally managed to install the new 11.1 EM Grid Control. To be fair to Oracle once it is installed it works pretty well. However, in my opinion installation is more complex than previous versions.

Our main goal was to verify that, whilst not explicitly supported,  Oracle 11.2.0.2 Grid Infrastructure, Database and ASM was compatible with the latest version of Grid Control which was released around March 2010 (and therefore pre-dates 11.2.0.2 by about six months).

The Oracle 11.1 Grid Control installation is a bit more labour intensive than its predecessors. Previous versions optionally installed a management repository database and a application server; in this version you must pre-create the database and install the application server yourself.

We tested the installation on Linux x86-64. 

The first step is to install a JDK. The only supported JDK is 1.6 version 18.  It took some time to track this down as the current version is 22 so you need to search the archives. Remember that Oracle now owns Java, so the JDK is downloaded from www.oracle.com rather than a Sun site.

The next step is to install a WebLogic server; only 10.3.2 is supported (as the documentation says, not 10.3.1 or 10.3.3). There is no operating system specific version of this product for Linux x86-64; instead download the generic version.

Next is the worst bit; you need to patch the WebLogic server with patch WDJ7 which is ONLY available through My Oracle Support. So if you don’t have a support contract, you cannot fully install Enterprise Manager. Whilst I can understand why Oracle are not keen on unsupported systems, the downside is that EM is going to be less well tested in the user community and ultimately will probably see a lower take-up amongst the customer base as a result.

Even if you have a support contract, you may still have problems with the WebServer patch. It can only be installed using the Smart Update tool (a fancy GUI product for WebLogic which is similar in many ways to OPatch). By default Smart Update will connect to MOS and download the required patches. If you don’t have a direct internet connection this may cause you some issues because althoough Smart Update can install previously downloaded patches, WDJ7 cannot be downloaded directly from MOS. You have two options; the first is to install Smart Update on another server that DOES have a direct internet connection and to download the patch from MOS using the tool; the second is to raise an SR. We did the latter and to be fair we had the patch within an hour. Of course this is a crazy use of Oracle support time; don’t be surprised if MOS incident resolution rates improve dramatically by the time they have sent the WDJ7 patch to 100,000 customers individually….

Next step is to install RDBMS software for the management repository database. We used 11.2.0.1; we were not quite brave enough to try 11.2.0.2 though it would probably have worked. We used DBCA to create a database; there are no restrictions on database name. You can create a RAC database or use Data Guard to provide HA; in the event we did neither as we are using a VMWare virtual machine and the entire server is replicated to another site.  The main complaint here is that the documentation is a bit muddled; it took a while to figure out what was required. For example hidden deep in the documentation it states that the management repository needs the Partitioning Option and therefore you must install Enterprise Edition. Why not say this up front?

Once the database has been created you are ready to install Grid Control. To be fair, now the database and WebLogic are installed first, there is a much better chance of the Grid Control succeeding first time and in fact it did for us. It took around an hour on our VM. If you have installed Oracle 10.2 Enterprise Manager you will remember there were about 30 agents that ran after the root script had been executed; in this release there are only about half a dozen agents though a couple took 10-15 minutes each. Go to lunch or leave it running overnight…

We are using secure http (https); the default port number is 7799 and you need to login as SYSMAN initially

Although an EM agent was installed on the local machine, EM Grid Control could not access the management repository database until we entered and saved its credentials in EM.

Prior to configuring remote targets it is necessary to install the EM Management agent on remote nodes; I will cover this in a separate post. After installing the Management agent, it is still necessary to enter credentials for each database in EM before you can monitor it. The agent automatically registers nodes, ASM instances and listeners.

We could not enter credentials for the standby database until we switched over (using DGMGRL); further investigation is required to know if this is user-error or a feature.

We had already configured the Data Guard Broker. EM discovered this automatically on both the physical and standby databases. Once credentials for both databases had been entered, we could use EM to switchover and switchback without any additional configuration.

So, apart from the WebLogic patch issue, I was quite impressed with 11g EM Grid Control. Don’t expect to install it first time in a couple of hours though.

Linux: Listing users belonging to a specific group

I have a discovery script that I run on Linux servers to collect configuration files and other information. I use the resulting output to write reports offline.  The script also collects the .bash_profile and ssh configuration files for each Oracle user (which vary from site to site). Up to now I have been customizing the scripts on site, but I have now found a better way to identify Oracle users.

In most environments, all Oracle users (grid, oracle etc) belong to the oinstall operating system group. Therefore all I need is a list of users belonging to this group. I could use awk or perl to extract this information, but I would need to inspect both /etc/passwd and /etc/group which seems a lot of effort.

However, there is a utility called lid (/usr/sbin/lid) in RHEL5 and OEL5 (at least) which can be used for this purpose.

By default lid displays infromation about the groups to which a specific user belongs. It can also display a list of users belonging to a specific group (which is the functionality that I require).

lid takes a couple of options; -g to list users in a specified group and -n to output names only (no UIDs or GIDs).

So I can obtain my list of Oracle users using:

/usr/sbin/lid -g oinstall -n

For example:

[oracle@server14'] $ /usr/sbin/lid -g oinstall -n
grid
oracle
oraclea
oracleb
oraclec

I can use the resulting list to identify files to be collected.

IP Host Names

I have never seen a definition of an IP host name before, but today I found one in the Oracle Enterprise Manager Grid Control Basic Installation Guide which says:

“According to RFC 952, the following are the assumptions: A”name” (Net, Host, Gateway or Domain name) is a text string up to 24 characters drawn from the alphabet (A-Z), digits (0-9), minus sign (-) and period (.). Note that periods are only allowed when they serve to delimit components of the “domain style names”. No blank or space characters are permitted as part of a name. No distinction is made between upper and lower case. The first character must be an alpha character.”

So, maximum of 24 alphanumeric characters plus hyphens. No underscores or spaces – useful to know when devising naming conventions.

Oracle 11gR2 – Data Guard Broker

I have installed Data Guard for about 30 customers over the past five or six years, but so far all have chosen to use SQL*Plus to perform standby role management.

This week I have been working on an Oracle 11.2.0.2 single-instance standby configuration which has been an ideal opportunity to become reaquainted with the Data Guard Broker. I have to say I was impressed…

But I probably cheated; prior to configuring the Broker, I spent the best part of a day building and configuring a physical standby database and then testing redo transport, redo apply, switchover and switchback using the traditional SQL*Plus commands. Before I even started to look at the Broker, I had fixed the usual handful of stupid mistakes, mainly typos, in the configuration.

However, given a solid foundation, implementing the Broker was almost trivial. This post assumes that the db_name is PROD and that the db_unique_names are PROD and STBY.  These names follow my customers existing standards; where possible I prefer to use geographic names for the DB_UNIQUE_NAME parameter as I think it is easier to visualize the configuration during role management.

Configure LISTENER.ORA

First, on each node add a static entry to the LISTENER.ORA file. For example:

SID_LIST_LISTENER =
 (SID_LIST =
   (SID_DESC =
     (GLOBAL_DBNAME = PROD_DGMGRL)
     (ORACLE_HOME= /u01/app/oracle/product/11.2.0/dbhome_1)
     (SID_NAME = PROD)
   )
  )

The Data Guard Broker defaults to static service names with the DGMGRL suffix. It is possible to override the default service names, but in this case I chose the lazy option and stuck with the Oracle defaults.

Note that listener.ora will be different on each node, so don’t copy it around.

Configure TNSNAMES.ORA

We also specified entries for both the primary and standby databases in $ORACLE_HOME/network/admin/tnsnames.ora. For example:

PROD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL=TCP)(HOST=server14.juliandyke.com)(PORT=1521))
   (CONNECT_DATA = 
       (SERVER = DEDICATED)
       (SERVICE_NAME = PROD)
   )
)
STBY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL=TCP)(HOST=server15.juliandyke.com)(PORT=1521))
   (CONNECT_DATA = 
       (SERVER = DEDICATED)
       (SERVICE_NAME = STBY)
   )
)

In this example, the PROD database will run on server14 and the STBY database will run on server15.

Enable Data Guard Broker

Next enable the Data Guard Broker for each database (primary and standby) in the configuration by setting the DG_BROKER_START parameter to TRUE.

ALTER SYSTEM SET dg_broker_start = TRUE;

The Data Guard Broker maintains two copies of its configurations. The locations of these files are specified by the DG_BROKER_CONFIG_FILE1 and DG_BROKER_CONFIG_FILE2 parameters. For a single instance database, the default values are:

• $ORACLE_HOME/dbs/dr1<db_unique_name>.dat
• $ORACLE_HOME/dbs/dr2<db_unique_name>.dat

The defaults are probably adequate for a single instance database; in a RAC environment the defaults should be modified to specify a location in a shared file system or ASM diskgroup(s).

Start DGMGRL

On any node in the configuration, start the Data Guard Broker command utility (DGMGRL) and connect as the SYS user.

[oracle@server14]$ dgmgrl
DGMGRL> CONNECT SYS
Password: <Enter Password>

Note that it is not necessary to specify the SYSDBA privilege as this is assumed by DGMGRL. 

Create Data Guard Broker Configuration 

Create a configuration. For example:

DGMGRL> CREATE CONFIGURATION DG1' AS
> PRIMARY DATABASE IS 'PROD'
> CONNECT IDENTIFIER IS 'PROD';
Configuration "DG1" created with primary database "PROD"

You will need to specify a configuration name and the unique name of the primary database.

Add standby database(s)

Add all standby databases to the configuration. In this example we just have one physical standby database:

 DGMGRL> ADD DATABASE ‘STBY’ AS
> CONNECT IDENTIFIER IS ‘STBY’;
Database ‘STBY’ added

Enable the configuration

By default the Data Guard configuration is disabled. Enable the configuration as follows:

DGMGRL> ENABLE CONFIGURATION
Enabled.

Switchover to standby

As we started with a working Data Guard configuration, it should be possible to switchover to the standby database at this stage. Switchover is achieved using a single command e.g.:

DGMGRL> SWITCHOVER TO 'STBY'
Performing switchover NOW, please wait...
New primary database "STBY" is opening...
Operation requires shutdown of instance "PROD" on database "PROD"
Shutting down instance "PROD"...
ORACLE instance shut down.
Operation requires startup of instance "PROD" on database "PROD"
Starting instance "PROD"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "STBY"

Switchover worked first time for us. However, we have experienced problems with the restart of the new standby (old primary) in subsequent tests. As these problems are intermittent we think they may be due to timing issues; the servers have new Intel 6-core processors.

Switchback

Switchback is very similar to switchover.

DGMGRL> SWITCHOVER TO 'PROD'
Performing switchover NOW, please wait...
New primary database "PROD" is opening...
Operation requires shutdown of instance "STBY" on database "STBY"
Shutting down instance "STBY"...
ORACLE instance shut down.
Operation requires startup of instance "STBY" on database "STBY"
Starting instance "STBY"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "PROD"

As with switchover we have seen occasional failures with switchback which again we think are timing-related.

Disabling Configuration

The configuration can be disabled at any time using:

DGMGRL> DISABLE CONFIGURATION
Disabled.

Removing the configuration

The configuration can be removed using:

DGMGRL> REMOVE CONFIGURATION
Removed configuration

We have removed the Data Guard Broker configuration a couple of times to fix problems with underlying parameters; it is possible to edit properties within DGMGRL that update parameters, but we wanted to ensure that the underlying configuration was also correct in case the Broker was subsequently deconfigured.

Checking the Data Guard Configuration

You can check the Data Guard configuration in DGMGRL using the SHOW CONFIGURATION command which has both default and verbose modes. For example:

DGMGRL> SHOW CONFIGURATION
 Configuration - DG1
  Protection Mode: MaxAvailability
  Databases:
    PROD - Primary database
    STBY - Physical standby database
  Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS

The verbose option includes some additional properties:

DGMGRL> SHOW CONFIGURATION VERBOSE
Configuration - DG1 
  Protection Mode: MaxAvailability
  Databases:
    PROD - Primary database
    STBY - Physical standby database   
  Properties:
    FastStartFailoverThreshold      = '30'
    OperationTimeout                = '30'
    FastStartFailoverLagLimit       = '30'
    CommunicationTimeout            = '180'
    FastStartFailoverAutoReinstate  = 'TRUE'
    FastStartFailoverPmyShutdown    = 'TRUE'
    BystandersFollowRoleChange      = 'ALL'
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS

Checking the Primary Database Configuration

The primary database configuration can be checked using the SHOW DATABASE command. This command also has a default and verbose mode:

DGMGRL> SHOW DATABASE 'PROD' 
Database - PROD

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    PROD
Database Status:
SUCCESS

 The verbose mode includes properties for the database, many of which reflect the underlying initialization parameters:

DGMGRL> SHOW DATABASE VERBOSE 'PROD'
 Database - PROD
   Role:            PRIMARY
   Intended State:  TRANSPORT-ON
  Instance(s):
    PROD
 Properties:
  DGConnectIdentifier            = 'PROD'
  ObserverConnectIdentifier      = ''
  LogXptMode                     = 'SYNC'
  DelayMins                      = '0'
  Binding                        = 'OPTIONAL'
  MaxFailure                     = '0'
  MaxConnections                 = '1'
  ReopenSecs                     = '300'
  NetTimeout                     = '30'
  RedoCompression                = 'DISABLE'
  LogShipping                    = 'ON'
  PreferredApplyInstance         = ''
  ApplyInstanceTimeout           = '0'
  ApplyParallel                  = 'AUTO'
  StandbyFileManagement          = 'AUTO'
  ArchiveLagTarget               = '0'
  LogArchiveMaxProcesses         = '5'
  LogArchiveMinSucceedDest       = '1'
  DbFileNameConvert              = '+DATA2, +DATA1'
  LogFileNameConvert             = '+FRA2, +FRA1'
  FastStartFailoverTarget        = ''
  InconsistentProperties         = '(monitor)'
  InconsistentLogXptProps        = '(monitor)'
  SendQEntries                   = '(monitor)'
  RecvQEntries                   = '(monitor)'
  SidName                        = 'PROD'
  StaticConnectIdentifier        =
  '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.227.27.1)
   (PORT=1521))(CONNECT_DATA=(SERVICE_NAME=PROD_DGMGRL)
  (INSTANCE_NAME=PROD)(SERVER=DEDICATED)))'
  StandbyArchiveLocation         = '+FRA1'
  AlternateLocation              = ''
  LogArchiveTrace                = '0'
  LogArchiveFormat               = '%t_%s_%r.dbf'
  TopWaitEvents                  = '(monitor)'
Database Status:
SUCCESS

Checking the Physical Standby Database Configuration

The physical standby database configuration can also be checked using the SHOW DATABASE command. Again this command has a default and verbose mode:

DGMGRL> SHOW DATABASE 'STBY'
Database - STBY 
  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: OFF
  Instance(s):
    STBY
Database Status:
SUCCESS

I can never remember the names of the Data Guard dynamic performance views so I really like the Transport Lag and Apply Lag fields in the above output.

Again the verbose mode includes properties for the standby database:

DGMGRL> SHOW DATABASE VERBOSE 'STBY'
Database - STBY 
  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: OFF
  Instance(s):
    STBY 
Properties:
  DGConnectIdentifier            = 'STBY'
  ObserverConnectIdentifier      = ''
  LogXptMode                     = 'SYNC'
  DelayMins                      = '0'
  Binding                        = 'OPTIONAL'
  MaxFailure                     = '0'
  MaxConnections                 = '1'
  ReopenSecs                     = '300'
  NetTimeout                     = '30'
  RedoCompression                = 'DISABLE'
  LogShipping                    = 'ON'
  PreferredApplyInstance         = ''
  ApplyInstanceTimeout           = '0'
  ApplyParallel                  = 'AUTO'
  StandbyFileManagement          = 'AUTO'
  ArchiveLagTarget               = '0'
  LogArchiveMaxProcesses         = '5'
  LogArchiveMinSucceedDest       = '1'
  DbFileNameConvert              = '+DATA1, +DATA2'
  LogFileNameConvert             = '+FRA1, +FRA2'
  FastStartFailoverTarget        = ''
  InconsistentProperties         = '(monitor)'
  InconsistentLogXptProps        = '(monitor)'
  SendQEntries                   = '(monitor)
  RecvQEntries                   = '(monitor)'
  SidName                        = 'PROD'
  StaticConnectIdentifier        =
  '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.221.27.2)
  (PORT=1521))(CONNECT_DATA=(SERVICE_NAME=STBY_DGMGRL)
  (INSTANCE_NAME=STBY)(SERVER=DEDICATED)))'
  StandbyArchiveLocation         = '+FRA2'
  AlternateLocation              = ''
  LogArchiveTrace                = '0'
  LogArchiveFormat               = '%t_%s_%r.dbf'
  TopWaitEvents                  = '(monitor)'
 Database Status:
SUCCESS

V$SQL_FEATURE

Continuing my investigations into dynamic performance views that are new in Oracle 11.2.0.1, this week I have been looking at V$SQL_FEATURE. This view contains a list of 497 optimizer features.

V$SQL_FEATURE contains three columns; SQL_FEATURE which is the name of the feature, DESCRIPTION which contains a short description and PROPERTY which is only set to a non-zero value for nine features, all of which appear to be query transformations.

On closer inspection, the majority of features listed in V$SQL_FEATURE are actually bug fixes. In Oracle 11.2.0.1 a total of 407 fixes are listed. This leaves us with 90 actual features.

V$SQL_FEATURE has two related dynamic performance views:

• V$SQL_FEATURE_DEPENDENCY lists dependencies between features. This view contains two columns, SQL_FEATURE (the name of the feature) and DEPEND_ON (the name of the feature on which it depends). Only one feature (QKSFM_OR_EXPAND) is listed as having a dependency (QKSFM_JPPD). I suspect this view has not been fully implemented in Oracle 11.2.0.1
• V$SQL_FEATURE_HIERARCHY lists the hierarchy of features.  This view contains two columns, SQL_FEATURE (the name of the feature) and PARENT_ID (the name of any parent). In Oracle 11.2.0.1 this view contains 501 rows. The PARENT_ID is NULL for 414 rows including all of the bug fixes. Surprisingly four of the features have two parents (QKSFM_UNNEST, QKSFM_JPPD, QKSFM_CVM and QKSFM_CBQT), the remaining 83 features just have one parent.

In summary these are not the most useful set of dynamic performance views for DBAs, but they will definitely be of interest to students and researchers of the optimizer.

A list of the optimizer features reported by V$SQL_FEATURE in Oracle 11.2.0.1 (excluding the bug fixes) is available on my website here