ORA-00000: Some Days I wonder

I have to say I have been using the PUM now for several upgrades and patch sets and everytime I get a new image I end up beating my head against the wall trying to figure out little issues.

A common problem, I have seen is the initial load of the PUM is fine and if you only use it once you might never see any issues, however, on subsequent loads if you did not install the network connections configuration right, you might be in for a wild ride.

Today, I ran into an image that another person installed and when starting up the database and listener would not start, which means the web and app are going to be down as well. However, this one was even more special because I got some really odd errors including this one:

ORA-00000: normal, successful completion

Now maybe, I am a little dense but this is an error message, so why is it saying normal and successful completion and it is an error message. I found a few suggestions on my handy dandy Google search that suggested to look at the ORACLE_HOME, ORACLE_SID and ORACLE_BASE variables as well as to double check the hosts file. Well, before I start anything I usually have my ORACLE variables well in hand, so I checked the hosts file and found that all the references I had to localhost were not resolving because there was no entry in the hosts file. That seemed to resolve the situation so that the database would actually start, but I was still having major headaches because everything was pointing to localhost and localhost.localdomain and there was just a ton of resolution problems.

I ended up change the tnsnames.ora file which for an image is under the /etc/tnsnames.ora location the one under $ORACLE_HOME/network/admin has no effect. Once I changed the tnsnames.ora file and the listener.ora file to the actual ip address, I also added a local_listener variable to the init{SID}.ora file for the environment. This solved the database issues, however, I had to also hard code the IP address for the JSL (Jolt) for the application server in order to get it to start, and for some final insult to injury, I ended up re-installing the PIA in order to fix all the invalid references.

For working with the PUM, all database functions are under the user: oracle, all PeopleSoft functions are under the user: psadm2.
Jolt Port is: 9000, webserver http is: 8000, default pia site is: ps, and if you need the IP address assigned to the Virtual box image, when you log in as root, type: ifconfig

Happy upgrading and patching.

O-SES Not Deploying Search Indexes with SSL

Just ran into a crazy little problem trying to get Oracle Secure Enterprise Search working at a client site that was using a self-signed internal SSL certificate.

In order to get Firefox to accept the SSL connection I had to add the Trusted CA (The Clients CA Certificate) to the Firefox keystore.  I had tried to load the certificates into the Windows keystore but apparently Firefox has its own.

Next When trying to deploy the search index I was receiving the following error:

Service Exception: ns2:AdminAPIRuntimeFault : EQA-15011: The plug-in manager raised an unexpected error. (262,1018) PT_SEARCH.SESIMPL.MESSAGE.AdminResponse.OnExecute Name:AdminResponse PCPC:1452 Statement:20
Called from:PT_SEARCH.SESIMPL.AdminService.OnExecute Name:doService Statement:848
Called from:PT_SEARCH.SESIMPL.AdminService.OnExecute Name:createAll Statement:794
Called from:PT_SEARCH.SESIMPL.AdminService.OnExecute Name:AddPSFTSources Statement:106
Called from:PTSF_DP_SBO_WRK.PTSF_DEPLOY_BTN.FieldChange Statement:139

I found a case on my oracle support: E-SES 11.2.2.2: Deploy Step Fails when Using SSL-Enabled PeopleSoft Integration Gateway: Error “EQA-15011: The plug-in manager raised an unexpected error” (Doc ID 1600736.1), which basically says that the cacerts keystore for the O-SES does not contain the trusted root identity for the SSL Certificate that is being used on the PeopleSoft System. So export the the Root and Intermediate certificates to a PEM format and import them into the cacerts keystore. There turns out to be many cacerts in the oracle directories, and I updated all of them with the new trusted certificates.

Lastly, I bounced the O-SES installation using the searchctl restart command.

SQL Server Permissions

Here is a handy little script to figure out what permissions have been granted to a user in SQL Server. For example the connect id user for PeopleSoft needs to have select access to the PSACCESSPRFL, PSOPRDEFN and PSSTATUS tables.  The default connect id is ‘people’.

select p.name, p.type_desc, pp.permission_name, pp.state_desc, pp.class_desc, object_name(pp.major_id)
from sys.database_principals p left join sys.database_permissions pp on pp.grantee_principal_id = p.principal_id
where p.name = 'people'

Not Authorized and Incorrect Rendering of Homepage

Recently I have seen several customers have problems after upgrades/splits/maintenance where after they login the homepage is returning not authorized errors or the homepage styling is completely messed up.

I was a little surprised to find that the root of many of these issues was actually based on security. In several cases the upgrades or the HR / CS separation process caused a removal of a permission list from the PSCLASSDEFN table, however, the reference to that permission list on the PSROLECLASS table which ties Roles to Permission lists does not remove the reference. This causes the security to have invalid (null) pointers which causes a serious problems for items that are associated to that security and effects the rendering of the home page among other things.

I found that in my case that after we ran a sysaudit report that the entries that showed up in the Security #31 section were the permission list references that were causing the problem. Once resolved all the renderings starting to perform as expected.