Friday, December 31, 2010

DB Wizard for Oracle on Windows 2008R2 64-bit

A year ago, I was testing the DB Wizard 8.50 for Oracle on Linux. It was painful, and really has not been successful as I would expect. Right now, I’d like to see how the DB Wizard is working on Windows 2008R2 64-bit within the latest Peopletools version, 8.51.
As I said in the linked article above, DB Wizard is not my cup of tea, it is configuring all by default which you do not necessarily want for a production system and it requires the Peopletools installation on your DB server which again you do not necessarily want for a production system. But over the years, I noticed a lot of people are trying to use it because they are not DBA and just want to get a working Peoplesoft application to be able to learn Peoplecode, and/or functional features (without taking care of all the admin settings). So, I think a nice proper tool to build a DEMO database would be very nice in such cases.

Unfortunately, it is not ready yet. First of all, Peopletools 8.5x are certified only on 64-bit OS, however on Windows it needs the 32-bit Oracle librairies, and we’ll see that cause some troubles for the DB Wizard. A full 64-bit application would be much much more easier to manage.

If you did not installed the 32-bit client, you’ll most likely receive the following error (the well known “missing or invalid version of SQL library PSORA”) :
So, I installed Oracle 64-bit database software in one Oracle home, then 32-bit client software in an other one.

Created the init.ora file with the minimum required :
And password file as well :
Start the listener :
Update the tnsnames.ora in both Oracle home (server/client) :
Then start the DBWizard utility (%PS_HOME%\setup\PsMpDbInstall\setup.bat), and it looks very handy and straightforward :
On the screen below, I was wondering if I should put the 32-bit client for sqlplus, but no, it won’t work (I tested it), same for the Oracle home, it should be the 64-bit database software installation path directory.
DBWizard851_W2k8_018 DBWizard851_W2k8_019DBWizard851_W2k8_020
And now start the process :
Before going further, you can check if the service is created as well as the database’s datafiles (such as SYSTEM, SYSAUX, UNDO, TEMP…).     
Everything works fine, service is created, database is created, datafiles are there. Even the xxddl.sql script is executed properly and all the Peoplesoft tablespaces are created.
But it failed down on the last step, the call of psdmtx to load the database !
The logfile is not very useful, is it ?
However, when I run it from DOS command prompt, dbsetup.dms call is working fine :
I was out of luck to make it working through DB Wizard.
Well, the dbsetup.dms creates the proper dms to load the database manually, but I’d be happy if it did it by itself. You can run it from DOS command prompt, or through DataMover GUI :
And do not worry if it stated “not responded”, that’s perfectly normal…

Well, once more, disappointing tool. Unless someone has suggestion how to make it working ?

Have fun,



Anonymous said...

To be honest I don't think anyone who's trying to setup a starter database for learning should use an Oracle database. Even Oracle themselves use MS SQL databases on their courses. You should try this DB wizard with MS SQL, I think it will work properly with MS SQL since it's much 'lighter' then Oracle DB.

Anonymous said...

One of the comments I read in the upgrade manual was that files need to be unzipped on the server to which they are to be applied. By doing so, it apparently adds items to your registry, which would not be added should the file be unzipped elsewhere.

I believe the same happens when running a .bat program from a DOS window rather than letting the GUI take over.