Blog

Error accessing database! Check your config files and verify that your database is correctly scripted.

It's 2 AM, and these are not exactly the calm and soothing words you want to be seeing from PTSpy after a clean, fresh  install of your WCI portal. It is frustrating, it is alarming, and above all else, did I mention it is 2 AM?  The prospect of re-scripting a database by hand is just ever so slightly upsetting.  So we look at little deeper at the Spy log:

Exception in thread "Main Thread" com.plumtree.openkernel.exceptions.OKSQLException: Error in function PTSession.Connect (iUserID == 2, SecurityProviderInfo == null): Error in function PTSessionInfo.GetCurrentCommunityMembership (): Exception: [plumtree][Oracle JDBC Driver][Oracle]ORA-00979: not a GROUP BY expression
SQL: '/* USER_COMMUNITY_MEMBERSHIPS:ANSI */ SELECT MAX(CS.ACCESSLEVEL) AS ACCESSLEVEL, C.NAME, C.ISLOCALIZED, C.OBJECTID, MC2.MEMBERSHIPTYPE FROM PTCOMMUNITIES C, PTCOMMSECURITY CS, PTVGROUPMEMBERSHIP GM, (SELECT MAX(MC.MEMBERSHIPTYPE) AS MEMBERSHIPTYPE, MC.COMMUNITYID FROM PTMYCOMMUNITIES MC, PTVGROUPMEMBERSHIP GM WHERE GM.GROUPID=MC.GROUPID AND GM.USERID=? GROUP BY MC.COMMUNITYID) MC2 WHERE GM.GROUPID=CS.GROUPID AND GM.USERID=? AND C.OBJECTID=CS.OBJECTID AND C.OBJECTID=MC2.COMMUNITYID GROUP BY C.NAME, C.ISLOCALIZED, C.OBJECTID, MC2.MEMBERSHIPTYPE ORDER BY LOWER(C.NAME) ASC'

Red Herring Alert: We wasted an hour and a half monkeying around with the Database SQL scripts and looking for an error there based on the PTSpy message above.  That's an hour and a half we'll never get back.

Luckily, you will not have to go through this pain!

As it turns out, the real problem isn't a mis-scripted database, but a compatibility problem between WCI 10gR3 and some Oracle 11g database default settings.

The fix is as follows:

1. To start let's make a back up of our sensitive files just in case things go wrong (just copy them out to somewhere safe):
   oracleproduct11.1.0db_1dbsinit.ora
   oracleproduct11.1.0db_1databaseiinitPLUM10.ora
2. Open sqlplus and connect as sysdba
3. SQL > alter system set "_optimizer_group_by_placement"=FALSE scope=spfile;
4. Stop the Oracle service corresponding to your database SID, it will be called OracleService<YOUR_SID>
5. Modify the file initPLUM10.ora and change ‘compatible = 10.2.0.0.0’ to ‘compatible = 11.0.0’
6. Start OracleService<YOUR_SID>
7. Reset IIS (use inetmgr to handle this using a pretty interface or open a command prompt and type: iisreset)
8. Clear your browser cache
9. Visit your portal home page.
   

For what it's worth, the fix actually is documented somewhat by Oracle in the Release Notes for 10gR3 in the following statement:

* Note: When running WCI with Oracle 11g with the provided initPLUM10.ora file, make the following modification: Change compatible = 10.2.0.0.0 to compatible = 11.0.0. Also, to prevent problems with "group by" optimizations when using WCI with Oracle 11g you must add the following configuration to the bottom of your init$ORACLE_SID.ora file: _optimizer_group_by_placement=false.

The issue here, of course, is that if you did not read this single line addendum a little ways down in the release notes and instead you danced ahead in your portal install process the resulting error has nothing in the logs or the trace that would indicate that this is your issue.

Hopefully though, you are here now and have what you need to continue on. And this one author who will be paying just a little more attention to reviewing software release notes in the future, if for no other reason than to be able to go home before the birds start chirping again in the morning.

So here you are, you have just finished your install of Oracle WebCenter Interaction, 10g Release 3.

On top of that, you even opted to run the latest version of the shiny new database that Oracle has to offer, Oracle Database 11g.

And like a real man would - you read the instructions all along the way (Oracle WCI 10gR3 installation guide, WCI 10gR3 Release Notes), meticulously ... at least for Oracle products.