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”) :
DBWizard851_W2k8_035
So, I installed Oracle 11.2.0.2 64-bit database software in one Oracle home, then 11.2.0.2 32-bit client software in an other one.

Created the init.ora file with the minimum required :
DBWizard851_W2k8_002 
And password file as well :
DBWizard851_W2k8_003
Start the listener :
DBWizard851_W2k8_006
Update the tnsnames.ora in both Oracle home (server/client) :
DBWizard851_W2k8_007
Then start the DBWizard utility (%PS_HOME%\setup\PsMpDbInstall\setup.bat), and it looks very handy and straightforward :
DBWizard851_W2k8_008
DBWizard851_W2k8_009DBWizard851_W2k8_010DBWizard851_W2k8_011
DBWizard851_W2k8_012DBWizard851_W2k8_013
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_014
DBWizard851_W2k8_015DBWizard851_W2k8_016DBWizard851_W2k8_017
DBWizard851_W2k8_018 DBWizard851_W2k8_019DBWizard851_W2k8_020
DBWizard851_W2k8_021
And now start the process :
DBWizard851_W2k8_022
Before going further, you can check if the service is created as well as the database’s datafiles (such as SYSTEM, SYSAUX, UNDO, TEMP…).     
DBWizard851_W2k8_023DBWizard851_W2k8_030
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.
DBWizard851_W2k8_025
But it failed down on the last step, the call of psdmtx to load the database !
DBWizard851_W2k8_036DBWizard851_W2k8_037
The logfile is not very useful, is it ?
DBWizard851_W2k8_038
However, when I run it from DOS command prompt, dbsetup.dms call is working fine :
DBWizard851_W2k8_042     
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 :
DBWizard851_W2k8_043
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,

Nicolas.

2 comments:

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.