OBIEE 10g: IBOTs Do Not Send Emails To All Recipients - "Invalid subscribers skipped" Message In IBOT Log (Doc ID 1159334.1)

APPLIES TO:

Business Intelligence Server Enterprise Edition - Version 10.1.3.4.1 [1900] and later
Information in this document applies to any platform.
***Checked for relevance on 19-Jan-2012***
***Checked for relevance on 15-Jul-2013***

SYMPTOMS

• iBots are not consistently sending emails to all recipients.
• iBots scheduled are sending the report content to multiple email addresses.
• Some of the recipients are not receiving the emails, while others are.
• Some recipients receive the email notifications sporadically.
• Confirmed that all recipients are set up in the SA system table, and they have all logged into the OBI environment previously.
• Non-clustered. All services are running. SA System Subject area enabled. No Cache Seeding with iBots. Users can run/save iBots.
• When scheduling the iBot, selected two recipients from the list of available recipients on the recipients tab in the iBot. The recipients chose were NBD1JGF and NBKRXVF. However, after running the iBot, below text is shown in the iBot log file:

  iBot Started:
  iBotID: /users/nbkrxvf/_ibots/iBot - Band Change Chart
  Priority: normal
  Personalized: True
  Invalid subscribers skipped: NBD1JGF,NBKRXVF

CAUSE

The error message shown in iBot log implies that the two user ids selected from the recipients list could not be found in the SA_System table. Looking in the SA_System table, it was found that the above two ids do exist, but are in lower case letters. So, there is a case mistmatch. Some userids are lowercase while others are uppercase, and there are mixed cases too. Oracle database is doing a case sensitive comparison when quering that table.

SOLUTION

To implement the solution, please execute the following steps:

1. A possible workaround involves turning off case sensitive searches on the Oracle database where the SA_System table is, by placing the below commands in the connection scripts of the connection pool:

   alter session set NLS_COMP='ANSI'
   alter session set NLS_SORT='BINARY_CI'

   After adding these commands to the connection pool, restarting the services, and rerunning the iBot, the users did receive the emails.
   
   Nevertheless, that could fail for some cases. So, better information on solutions can be found in next points, below.
2. NOTE:747892.1 - "emails from Delivers not working reliably. Can't send an email to some users", indicates to use UpperCaseRecipientNames parameter to make queries over SA Sytem to be all in Uppercase.
3. Internal Document Unpublished BUG:8311394, "CG: IBOT LOG - INVALID SUBSCRIBERS SKIPPED: <NAME>", clarifies this issue has been documented in 11G manuals, and mentions:

   "
   If you are using an LDAP server to perform authentication
   Check to see the case of the user returned (usually lower case)
   If you are also using SA System to get the users email address check the case of the Logon username. If it is different from that in the LDAP server you will get an incompatibly that will cause this error in the iBot. Log
   .
   Setting in the instanceConfig.xml <UpperCaseRecipientNames> to true may not solve this.
   
   
   You need to ensure that the SA_SYSTEM Logon value and the LDAP username value are in the same case. (usually the simplest method is to set the LOGON in the SA_USER table to lower case - You can to this by just putting a LOWER(Logon) function around it)
   "

   
   
   So, UpperCaseRecipientNames option in instanceconfig.xml file, case of logon in sa_user table, and case of the users retrieved by OBIEE, for example from LPAD, need to be set and use in a way that all cases match.
4. Finally, this is already documented for 10.x versions too:
   
   - Document "Oracle® Business Intelligence Server Administration Guide Version 10.1.3.2 December 2006", available at http://download.oracle.com/docs/cd/E10415_01/doc/bi.1013/b31770.pdf
   
   Page 181, says:
   
   "
   Authorization and authentication. This option affects what users are allowed to do in the
   system (authorization), not who users are (authentication). For related information about
   database authentication, refer to "About Oracle BI Delivers and Database Authentication" on
   page 328.
   CAUTION: An existing configuration option (the Alerts element) in instanceconfig.xml controls
   how logon names are treated before accessing the SA System subject area. For example, login
   names might be converted to all uppercase. For more information, refer to Oracle Business
   Intelligence Presentation Services Administration Guide.
   "
   
   - Document "Oracle® Business Intelligence Presentation Services Administration Guide Version 10.1.3.2 December 2006", available at
   http://download.oracle.com/docs/cd/E10415_01/doc/bi.1013/b31766.pdf
   
   Page 74 clarifies how to deal with case and SA System Subject area:
   "
   Setting the Case of Logon Names for the SA System Subject Area
   ==============================================================
   
   When the SA System subject area is used, logon names are compared to the Logon column in the
   SA System subject area. By default, this comparison is case sensitive. This means, for example, that
   a login of "Fred" will not match an SA System subject area entry of "fred" If your authentication
   method is case sensitive, this works fine because the login "fred" accepted at login, matches "fred"
   in the Logon column in the SA System subject area. However, if your authentication method is case
   insensitive, then you need the SA System subject area comparison also to be case insensitive.
   
   You can make the SA System subject area comparison case insensitive by:
   - Ensuring that your SA System subject area stores all logon names in uppercase
   - Setting the UpperCaseRecipientNames element to true so that logon names are converted to
   uppercase before being included in SA System subject area queries
   
   To set the UpperCaseRecipientNames element to true, you edit the Oracle BI Presentation Services
   configuration file (instanceconfig.xml) to add the UpperCaseRecipientNames element between the
   <Alerts> and </Alerts> elements (which appear after the <ServerInstance> element) and set it to
   true.
   
   The following entry is an example:
   
   <ServerInstance>
   .. .. ..
   <Alerts>
   .. .. ..
   <UpperCaseRecipientNames>true</UpperCaseRecipientNames>
   .. .. ..
   </Alerts>
   .. .. ..
   </ServerInstance>
   
   For information about working in the Oracle BI Presentation Services configuration file
   (instanceconfig.xml), read "Making Oracle BI Presentation Services Configuration Changes" on
   page 15
   "