October 31st, 2009 by Stewart Bryson
First of all, I’d like to thank David Allan of the OWB Product Team for helping me solve the problem I discuss below… and also for identifying what he thinks to be the cause.
A few days after Oracle Database 11gR2 was available, I went about building my first VM to test both the database features as well as OWB. I installed 64-bit Oracle Enterprise Linux 5.4 on a VM and then installed the 64-bit version of Oracle Database 11gR2. The database install went just fine, and once complete I executed $ORACLE_HOME/owb/bin/owb to get OWB started, specifying a valid username and password. OWB recognizes that my username is not a registered user of an existing workspace, so it asks me if I want to create a new workspace… or at least register with an existing one.
I select yes, which immediately launches the Repository Assistant. I select the first option, “Manage Warehouse Builder Workspaces”, and then again select the first option: “Create a new Warehouse Builder workspace”. At the next screen, I select “Create a workspace with a new user as workspace owner”.
At Step 6, I provide a username for my new workspace, a password, and a name for the workspace. Then I select Next.
At Step 10, I go ahead and select the username STEWART to be added as a workspace user, and then select next. I then get a summary screen, where I press Finish. Immediately, I get an error involving the installation of the OWBSYS user. The actual exception is “oracle.ide.ExitNotAllowedException” in the process called “processLoadJavaToken”.
I look at the log generated by the Repository Assistant in the $ORACLE_HOME/owb/UnifiedRepos directory… but the only additional piece of information supplied is the following: “Error occurred during Seeding OWBSYS”. This was all quite confusing, as I knew that Mark had successfully built OWB with no issues, and so had Pete Scott, Venkat and others.
At this point, I contacted David Allan and asked if he had seen this error, which he hadn’t. He looked into it a bit further, but in the end, he simply recommended that I clean out the OWBSYS user and then try to “seed” it manually, which simply means creating the required objects in the schema for supporting new workspaces. This is of course what the Repository Assistant is trying to do, but for whatever reason, the seed process in the GUI is not working correctly for me.
To clean out the OWBSYS schema, I use the clean_owbsys.sql script in the $ORACLE_HOME/owb/UnifiedRepos directory. This will drop the OWBSYS user and all roles associated with it.
SQL> @clean_owbsys User dropped. User dropped. Role dropped. Role dropped. Role dropped. SQL>
I follow that up with cat_owb.sql script, which recreates OWBSYS and seeds all the required objects. I truncated some of the output for brevity sake, which I indicated with the “…” characters.
SQL> @cat_owb Enter Tablespace Name for OWBSYS user: USERS Package created. Package body created. Create user OWBSYS with default tablespace USERS ... old 1: CREATE USER OWBSYS IDENTIFIED BY OWBSYS DEFAULT TABLESPACE &owbsys_tbs new 1: CREATE USER OWBSYS IDENTIFIED BY OWBSYS DEFAULT TABLESPACE USERS User created. User OWBSYS has been created. PL/SQL procedure successfully completed. Grant succeeded. ... Grant succeeded. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. Commit complete. ... PL/SQL procedure successfully completed. Commit complete. Role created. Grant succeeded. Role created. Grant succeeded. Role created. Grant succeeded. ... Grant succeeded. old 1: CREATE USER owbsys_audit IDENTIFIED BY owbsys_audit DEFAULT TABLESPACE &owbsys_tbs new 1: CREATE USER owbsys_audit IDENTIFIED BY owbsys_audit DEFAULT TABLESPACE USERS User created. Grant succeeded. Grant succeeded. Synonym created. ... Synonym created. Table created. PL/SQL procedure successfully completed. User altered. User altered. NOTE: To associate the Control Center with the correct OWB home, now run owb/UnifiedRepos/reset_owbcc_home.sql. You must run the script as a user with java admin privileges. For example, connect as sys as sysdba to run the script. SQL>
I follow the advice from the cat_owb.sql script, and I execute the reset_owbcc_home.sql.
SQL> @reset_owbcc_home Enter the full path of the Oracle home for the OWB Control Center install. If you are installing in a Windows environment, please ensure that the case of the path exactly matches the Oracle install path including the drive letter. OWB Control Center Home: /oracle/product/11.2.0/dbhome_1 PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. SQL>
The cat_owb.sql script recreates and locks the OWBSYS account… so I unlock the account and change the password. Now, when I run the repository assistant I get a different result.
David Allan’s assessment of the cause of this issue is the use of the client tools, namely the Design Client and the Repository Assistant, on a 64-bit Linux platform. According to the certification matrix, none of the OWB client tools are certified on 64-bit Linux. That’s very strange, as it’s unclear why any of the client tools would continue to ship on the 64-bit installations if they shouldn’t be used.
Remember, both the Oracle Database and OWB continue to be supported on 64-bit Linux, it’s just my use of the client tools from that same installation that is not supported. Were I to download a 32-bit Linux installation and use the client tools from that installation to connect to a 64-bit installation… I’d be using a certified solution. And when Oracle starts shipping 11gR2 on Windows, the use of the 32-bit Windows client installation to connect to the 64-bit database installation is supported as well.
I can certainly work around the workspace creation issue, as I showed above, by doing the OWBSYS seeding manually. However, as David points out, I’m opening myself up for lots of issues by using a non-certified OWB client.