OBIEE 11g - MDS or BIPLATFORM Schema Password Change Considerations

As you know the OBI 11g installation requires that the Repository Creation Utility (RCU) be run in advance to create a metadata repository for information used by OBI.  During the installation of the RCU and the creation of the database users/schemas a password (or multiple depending on your install configuration) is created for the MDS and BIPLATFORM schemas.

Perhaps unknown by most, this password(s) ultimately becomes coveted by the OBI 11g system.  What if one needs to change the initial passwords for one or both of these schemas?  How would a password change or expiration of that user/schema password impact the OBI 11g system?

Let’s just say that this whole concept of using a metadata schema to support an application is not a novel one. However, because this concept is new to those who love and have made their bacon working with OBI this may seem cumbersome.  Although we had all better just get used to it.  It’s going to be around for a while according to the Oracle roadmap for BI.

A need to change the password for either schema or if the password just happens to expire (default expiration is set of Oracle RDBMS 11g) can beget some unexpected results.  There real question becomes what will happen to my OBI 11g system? and then of course where do I go to reset the password aside form the RDBMS itself so that OBI 11g picks it up and behaves itself? Fortunately the resolution/process is straightforward.

Can the MDS or BIPLATFORM schema password be reset?

Yes, of course it can. After all it is just a user/schema in your RDBMS.  It may even get locked due to the password expiration default setting of the Oracle RDBMS 11g database if using that version.  But once you change this for any reason that is where the fun begins.  The password can be changed using the basic Oracle PL/SQL syntax for updating a user account identified by…yada yada yada.  But once this is done it actually causes issues with the WLS and OBI system’s ability to connect to those schemas.  I’m guessing some encryption had taking place during the installation and it gets disrupted or something like that.  Either way both the MDS schema required by Fusion Middleware Control (EM) and the BIPLATFORM schema that controls the OBI Agents, etc. gets hosed.

Oracle 11g db seems to default to expiring a user’s password within a fairly short window.  Recently I was testing an old OBI 11g VM I had put together and when the Weblogic services didn’t start up properly the investigation and troubleshooting phase began.  Since the Weblogic and OBI 11g components rely on the two schemas for MDS and BIPlatform if a connection cannot be made to these schemas for any reason unexpected results will occur for your OBI 11g implementation. Ultimately the resolve was to update the user account with an unlimited password lifetime grant similar to the PL/SQL code below.  This code below simply sets the default profile with which a user/schema may be associated.

ALTER PROFILE DEFAULT LIMIT
  FAILED_LOGIN_ATTEMPTS UNLIMITED
  PASSWORD_LIFE_TIME UNLIMITED;

Impact of MDS or BIPLATFORM password reset

If one needs to adjust the password for these schemas or the password expires two things will happen.  First, the WebLogic Server (WLS) will startup but it will not start up properly. Viewing the start up logs one will notice an error stating that there is an invalid username/password as such the WLS cannot properly connect to the database.  This is the main culprit and should be rectified first (more on this below).  The second issue one will notice is a functional one that will show itself clearly in the OBI 11g fusion control enterprise manager (EM).  This has to do with the BI Scheduler service component.  One will notice that this will not start properly.  As a matter of fact it just won’t start.  In fact, it will may have an impact on other components such as the “Restart All” will not complete, etc.

The impact is clear and that nothing will work properly.

How-to Fix / Update the MDS schema password for OBI 11g?

Great question.  Here are the steps:

1. Login to the Fusion Middleware Control (EM)
2. Expand WebLogic Domain > bifoundation_domain
3. Under the bifoundation_domain, expand the WebLogic Domain dropdown and select JDBC Data Sources
   
4. Select mds-owsm from the list and click Edit
   
5. Under the connection properties tab change the password and click the Apply button (upper right of form)
   
6. Restart OBI Services

How-to Fix / Upate the BIPLATFORM schema password for OBI the 11g Scheduler?

Another good one friend.  Here are the steps:

1. Login to the Fusion Middleware Control (EM)
2. Expand Business Intelligence > coreapplication
3. Click on the Deployment tab
4. Click on the Scheduler tab
5. Click the Lock & Edit Configuration button
6. Once the option to edit the scheduler metadata becomes available enter the new password for the schema/user
   
7. Click Apply
8. Click Activate Changes
9. Just restart the scheduler. Or, bounce OBI Services just to be ready and steady if so desired.

Other Possible OBI 11g Symptoms from this or similar password expiration/change

• SampleApp MapViewer obiee_navteq schema will cause maps to not render
• May impact Action Framework to some degree
• Will impact Real-Time Decisions (RTD) if installed

Conclusion

Clearly dealing with security constraints in OBI 11g becomes a new factor of your designated OBI Administrator. It is an extra task that I am sure one could do without.  However, this blog showed a quick approach to getting the core OBI 11g system back up and running in case this is a situation one gets placed into.  There are several other areas that require updating the password for in case the DB user password is changed at any point in time.  I left this post for the core pieces so that your EM capacity tab shows nothing but green up arrows.