OBIEE Web Catalog Corruption in OBIEE 11G

Catalog Corruption can cause the OBIEE system to act strange. We had several instances where by due to the catalog corruption the system stability was severly effected. The OBIEE system would randomly cause user lockouts where it would say you do not have permissions even though the user just accessed the system a couple of minutes ago. This would entail restarting the presentation services to get the user backin. The other issue we noticed was that BI Publisher stopped working all together which we eventaully tracked back to catalog corruption.

Issue 1:

User Lockout Issues: Users recieved the message, We are sorry, but it appears that you either do not have sufficient rights to access this particular report or the system is down. Al though a custom message but actually this message hid the Oracle error message.


All the services at this time were running including em. console and BI services.

Issue 2:

BI Publisher not accessible. Sometimes you would see the error message "Server not initialized. Please make sure the repository is ready" or sometimes you may just get a blank screen. If you lookup the error on internet or metalink (Note: 1265839.1 or 1359064.1) then it talks about priviledges issue and asks to assign the following roles.

• Assign the "BIConsumer" and "BISystem" roles to the "Access to SOAP" privilege.
• Assign the "BIAdministrator" and "BISystem" roles to the "Impersonate as system user" privilege.

But In our case we already had these priviledges granted. After a lot of looking around and a SR with Oracle, we managed to pin the issue to BISystem User corruption in CATALOG.

SOLUTION:

The solution to above issues is to validate the catalog. The Process to do so is given below and well documeted in OBIEE Admin Guide.

Validating the Catalog

Over time, inconsistencies can develop in the catalog as links are broken, users are deleted, or NFS file system issues are encountered. These inconsistencies can eventually lead to incorrect behavior, such as the inability to edit an agent's recipient list. You can periodically take the production system offline and validate the catalog, to be informed of and to take corrective action on inconsistencies.

Performing a Basic Validation of the Catalog

Use the following procedure to perform a basic validation of the catalog.

To validate the catalog:

1.     Stop Presentation Services.

2.     In 11g only, regenerate the user GUIDs for the catalog

Ensure that you regenerate the user GUIDs before validating the catalog. Failure to do so can result in the removal of all accounts, permissions, and privileges from the catalog.

1.     Update the FMW_UPDATE_ROLE_AND_USER_REF_GUIDS parameter in NQSConfig.INI:

a.     Open NQSConfig.INI for editing at:

b.  ORACLE_INSTANCE/config/OracleBIServerComponent/coreapplication_obisn

c.     Locate the FMW_UPDATE_ROLE_AND_USER_REF_GUIDS parameter and set it to YES, as follows:

d.  FMW_UPDATE_ROLE_AND_USER_REF_GUIDS = YES;

e.     Save and close the file.

2.     Update the Catalog element in instanceconfig.xml:

a.     Open instanceconfig.xml for editing at:

b.  ORACLE_INSTANCE/config/OracleBIPresentationServicesComponent/coreapplication_obipsn

c.     Locate the Catalog element and update it as follows:
a.  -<Catalog
b.  -<UpgradeAndExit>
false

c.  UpdateAccountGUIDs>UpdateAndExit
d.  /Catalog>

h.     Save and close the file.

3.     Restart the Oracle Business Intelligence system components using opmnctl:

4.  cd ORACLE_HOME/admin/instancen/bin

5.  ./opmnctl stopall

6.  ./opmnctl startall

7.     Set the FMW_UPDATE_ROLE_AND_USER_REF_GUIDS parameter in NQSConfig.INI back to NO.

Important: You must perform this step to ensure that your system is secure.

8.     Update the Catalog element in instanceconfig.xml to remove the UpdateAccount GUIDs entry.

9.     Restart the Oracle Business Intelligence system components again using opmnctl:

a.  \Oracle_BI1\opmn\bin>opmnctl stoptall

b.   \Oracle_BI1\opmn\bin>opmnctl startall

3.     Back up the catalog by using the 7-Zip utility to create a compressed file for it.

4.     Create a backup copy of the instanceconfig.xml file.

5.     Edit the instanceconfig.xml file so that it contains the appropriate elements for performing the validation.

6.     Start Presentation Services to run the validation according to the values that you specified in the instanceconfig.xml file.

7.     Stop Presentation Services.

8.     Create a backup copy of the instanceconfig.xml file in which you added the validation elements, renaming the file similar to instanceconfig_validate.xml. In this way, you have a version of the file to use as a starting point for subsequent validations.

9.     Restore the backup version of the instanceconfig.xml that you created earlier to use as the current version.

10.  Start Presentation Services.

Specifying the Elements for Validating the Catalog

As part of the process of validating the catalog, you include elements in the instanceconfig.xml file that run the validation when you restart Presentation Services. The following procedure describes how to edit the instanceconfig.xml file to include these elements.

To specify the element for validating the catalog:

1.    Open the instanceconfig.xml file for editing.

2.     File Location in \instances\instance1\config\OracleBIPresentationServicesComponent\coreapplication_obips1

3.    Locate the Catalog section in which you must add the elements that are described in Table 17-1.

4.  Include the elements and their ancestor element as appropriate, as shown in the following example. In this example, the validation runs when Presentation Services starts. Inconsistent accounts (such as those for deleted users), links, and objects are removed. Inconsistent users' home directory names are logged but directories are not removed.

- <ps:Catalog xmlns:ps="oracle.bi.presentation.services/config/v1.1">

      <UpgradeAndExit>false</UpgradeAndExit>

         

  <ps:Validate>OnStartupAndExit</ps:Validate>

  <ps:ValidateAccounts>Clean</ps:ValidateAccounts>

  <ps:ValidateHomes>Report</ps:ValidateHomes>

  <ps:ValidateItems>Clean</ps:ValidateItems>

  <ps:ValidateLinks>Clean</ps:ValidateLinks>

  </ps:Catalog>

Caution:

Include only one Catalog element in the instanceconfig.xml file or unexpected results might occur. Unless expressly noted, include most nodes in an XML document only once.

5.    Save your changes and close the file.

Table 17-1 Elements for Validating the Catalog

Element	Description	Default Value
Validate	Performs the validation of the catalog according to the values of the other Validate-related elements in this section. Values are described in the following list: None — Performs no validation. OnStartup — Performs the validation when Presentation Services starts. OnStartupAndExit — Performs the validation when Presentation Services both starts and stops. This value allows re-running of the validation, performing as many cycles of Report and Clean until the catalog is as clean as appropriate. If this value is not None, then all privileges and each object's ACLs in the entire catalog are cleaned of terminated accounts, regardless of the settings of the other Validate-related elements.	None
ValidateAccounts	Verifies that all information about users, roles, and groups in the catalog is consistent. Values are described in the list after this table.	None
ValidateHomes	Verifies that all information about home directories in the catalog is consistent. Values are described in the list after this table.	None
ValidateItems	Verifies that all information about objects in the catalog is consistent. Values are described in the list after this table.	None
ValidateLinks	Verifies that all information about links in the catalog is consistent. Values are described in the list after this table.	None

The elements have the values that are described in the following list:

·        None — Specifies that no validation is performed.

·        Report — Specifies that details about each inconsistent object are written to the sawlog.log file.

·        Clean — Specifies that details about each inconsistent object are written to the sawlog.log file and that each object is removed from the catalog.