Oracle Database: Oracle Performance Secret

When working with a new Oracle Database on a Windows 2012R2 server running Oracle 11.2.0.4.  The PeopleTools Upgrade process was on the last step which is to do a Final Alter Audit (FNLALTAUD).  This Upgrade Step looks at all the tables within the system and determines if the metadata is in sync with what is actually in the database.

14 hours into the process and has to be done, but as per usual it is next to impossible to tell with PeopleSoft.  Since this really should not take anymore than an hour or two, the process is killed and the following Oracle Performance Secret is executed in SQLPlus:

analyze table sys.CDEF$ delete statistics;
analyze table sys.CON$ delete statistics;
analyze table sys.USER$ delete statistics;

Rerunning the Upgrade Task after apply the Oracle Performance Secret and it is now running in:  29 minutes.  That is much more acceptable.

What is the SYS Schema?

The SYS schema stores all of the base tables and views for the Oracle Database’s Data Dictionary.  The Data Dictionary is critical to the operation of Oracle Database.  It is imperative to maintain the integrity of the data dictionary, so manipulation of the Data Dictionary should only be done by Oracle itself.

Where did this fix come from?

During upgrades of environments there has been some reported cases of very poor performance using Oracle PeopleSoft Data Mover to import data into the new environment.  We have experienced this problem while upgrading environments specifically the Final Table Audit (or Initial Audit) Upgrade Tasks.  PeopleSoft has a very high number of constraints (Finance v9.2, I just queried had 2.7 million rows), which are stored on the CDEF$ & CON$ Data Dictionary Tables. If the Cost Based Optimizer (COB) has bad statistics to work from it is often seen that the optimization plan will come up with Full Table Scans which are extremely poor performing.  Patch #4618615 & #5103126 from Oracle apparently address this issue, and so the problem should be limited to Oracle Database 10.2 to 11.0.  However, often times when poor performance is effecting an environment we apply this Oracle Performance Secret and performance typically is greatly improved.

I was putting a Campus Solutions Bundle on a system for a client and as per usual PeopleSoft delivered an emergency ISIR patch to follow up.  However, this time when I went to apply the ISIR patch after the fact Change Assistant was complaining that the pre-requisite bundle was not applied yet.  Now I know my memory is bad, but it was just the other day, I know I put the patch on this environment.

First, I thought cache, so I stopped all the agents and all the environments cleared the cache and reset the PSEM Agents & Hub.  Fired it back up and still the same error.  I even reset the GUID on PSOPTIONS.

I checked the PS_MAINTENANCE_LOG and clearly the update I put on is there, however, it looks a little different on one of the fields for other bundles.  The FIXOPRID for the bundle was PS and not PPLSOFT like the other bundles.  Sure enough I did a

UPDATE PS_MAINTENANCE_LOG SET FIXOPRID = ‘PPLSOFT’ WHERE UPDATE_ID = ‘{CSBundle##}’

Re-ran the check and sure enough all is right in the world again.  All pre-requisites need to have the FIXOPRID be PPLSOFT otherwise things don’t go as planned.

Okay, I have to say, a few things in this world drive me crazy, okay maybe a little more than a few.  Now I know this won’t apply to most people as they probably only have to ever VPN into one place, but for the folks in this world that VPN into multiple places, I often find that my Cisco Anywhere client tool often gets updated.  The problem I often find is that my default URL gets changed and the drop down never works, so I end up memorizing bizarre VPN urls for my various clients.

I just want to be able to have my most common client be first in the list and ideally have my other VPN urls in the drop down.  So I a fit of frustration I did a Google search yesterday and found how to change the default URL.  Look for the file preferences.xml which should be in your c:\users\{youruserid}\appdata\local\cisco\Cisco AnyConnect VPN Client\ folder, I had some issues finding this file as it might be in the roaming folder instead of local, and it might just be hidden.  In the file you can change the entry for:  DefaultHostName.

In order to get the drop down list to work properly, I found in my c:\programdata\cisco\Cisco AnyConnect Secure Mobility Client\Profile that there was an xml file that had my default profile in it.  In my trusty UltraEdit, I found the section for the ServerList and made a new HostEntry block for each of my VPN Servers.

<ServerList>
<HostEntry>
<HostName>{MyVPN-name-vpn1}</HostName>
<HostAddress>{MyVPN-address-vpn1}</HostAddress>
</HostEntry>
<HostEntry>
<HostName>{MyVPN-name-vpn2}</HostName>
<HostAddress>{MyVPN-address-vpn2}</HostAddress>
</HostEntry>
</ServerList>

Now my drop down box works.  Now I am a little bit happier. 🙂

Weblogic Web Server SSL Certificate:

A client called today and said they needed to change their wildcard SSL certificate that they were using on one of the external web servers. This seemed like a simple enough request, so:

Step 1:

Get Client to send the new SSL Certificate in pfx format.

Step 2:

Using Portecle “a user friendly GUI application for creating, managing and examining keystores, keys, certificates, certificate requests, certificate revocation lists and more” – Load the PFX SSL Certificate

Step 3:

Convert the new keystore to JKS format.

Step 4:

Reset the password to the SSL Certificate and set the Alias name to the Alias already in use.

Step 5:

Export the PEM encoded certificate to a file and set the file extension to .CRT – This allows the SSL Certificate to be easily opened in Windows.

Step 6:

Export the intermediate and root certificates in base x.509 formats.

Step 7:

Import the intermediate and root trusted certificates (the ones just exported in Step 6) back into the keystore.

Step 8:

Take the SSL Certificate in PEM format and append the SSL Certificate Intermediate PEM format and then the SSL Certificate Root Certificate Authority (CA) in PEM format.  This basically creates a full certificate chain in PEM format.

Step 9:

In Portecle update the CA Reply of the Certificate with the Full Certificate Chain in PEM format.  This step is important so no certificate errors are reported with Firefox Browser.

Results:

The new JKS Keystore has 3 SSL Certificates in the keystore, the Full SSL Certificate private/public key combination, and the SSL Certificate Intermediate and SSL Certificate Root.

Install New SSL Certificate Keystore:

Log into the Weblogic Server and rename the existing SSL Certificate Keystore and upload the new SSL Certificate Keystore using the original SSL Certificate Keystore filename.

Restart the server, and………..

BOOOOOOOM!

ERROR:  <Emergency> <Security> <BEA-090034> <Not listening for SSL, java.io.IOException: Cannot convert identity certificate.>

Well, that’s new!  A quick investigation on Google, and some checking turns up that the new certificate was created using SHA256withRSA, while the old one used SHA1withRSA.

As it turns out, Weblogic prior to 10.3.4 cannot use certificates with SHA256withRSA encryption as it uses Certicom SSL implementation.  However, since the web server is version 10.3.6, JSSE SSL needs to be enabled.

Which is under the advanced options of the Weblogic console found under the SSL tab for the Server in question.  Once enabled and the web server restarted, the SSL Certificate & New Keystore worked!

Knowledge Reference:

Check out Wikipedia’s page on SHA-2 Encryption information.

Well, last month, I wrote a blog about how much I enjoyed my initial installation with Oracle’s Secure Enterprise Search that is now integrated into the Oracle PeopleSoft 9.2 applications. Surprisingly, I received a lot of email saying that I was not alone in my pain, and more surprisingly, folks were having a lot more problems with it then I did. Heck mine worked with only a day of fooling around with it.

Here is a some of the information I put together:

1. Watch the certification levels and the versions delivered by default. For some reason and I just checked this on edelivery the O-SES that is available for download in the Application Media Packs for HR is version 11.1.2.2.0 which is NOT certified on Windows 2008 R2 or Windows 2012, Solaris 11, RHEL 6.x. Version 11.2.2.2.0 is certified on Windows 2008 R2, Solaris 11 and RHEL 6.x. However, at this time there is NO certified version for Windows 2012. —It should be noted that I have installed and configured 11.1.2.2.0 on Windows 2012 and it worked fine for the Portal Registry Search.

2. Integration Broker is HEAVILY used with O-SES.
– Make sure that your IB configuration is setup and working correctly.
– Make sure that you have the nodes setup with password authentication.
– Make sure the Domain Connection Password (8.53) is 8 characters long and is encrypted in the configuration file.
– Even if you don’t do SSL on the webserver, make sure you run the pskeymanager tool to set a non-default password on the keystore (pskey) on the webserver, and then add the password (encrypted with pscipher) to the integrationGateway.properties file for the PSIGW (PeopleSoft Integration Gateway) configuration.
– If you are using a secure gateway url, make sure the root and intermediate certificates for the keychain are in the digital certificates configuration.
– Make sure all the Service Operations associated with Service: ADMINSERVICE, ORACLESEARCHSERVICE, PT_SES, PTFP_FEED, PTSF_SECURITY, PTSF_SES_FEED and PTSF_META_DATA are active and have a valid routing that is active, and make sure the routing makes sense.
– The Service Operation PTSF_SES_SCHEDULED_FEED has two routings on it that I have seen. One is to the WSDL_NODE and the other is to the ATOM node. What I found was that the I had to inactivate the WSDL_NODE and make sure the ATOM routing was active. If you look in the integration broker monitor I had the FEED messages reporting as done, but if you look at the pub contract it was in error. Once I removed them and resubmitted the builds it generate the messages correctly and the contracts executed correctly and therefore started returning valid entries.

3. Make sure that you can do the 4 step test for the round trip. PeopleTools > Search Framework > Utilities > Diagnostics. If the first 2 steps fail there is most likely a problem with O-SES installation and configuration, if step 3 or 4 fail, there is most likely a problem with the Integration Broker setup for O-SES.

4. Test the search on the Search Test Page. PeopleTools > Search Framework > Utilities > Search Test Page. There is a button on this page to Clear Security Cache, and I found a couple of times that when I did the clear that searches began to work. I haven’t looked into the specifics of the cache it is clearing, but I just noted that this worked in a couple of cases.

5. Make sure the Security Permissions PTPT3100, PTPT3200, PTPT3300 are setup on your administrative user. I also made sure that the PTPT1000 list had permission to the searches that I had deployed under the new tab for permission lists: Search Groups.

6. Turning on the search dialogue box in the headers. First you need to turn this feature on, which is done on the Portal General Settings page: PeopleTools > Portal > General Settings.

6b. Search groups are not appearing in the search box. Make sure that the search index is added to the home page context. PeopleTools > Search Framework > Administration > View Search Contexts. For example if you want the Portal Registry to be in the search, you need to add PTPORTALREGISTRY to the Home Page Context Type.

7. Can Multiple Environments use the same O-SES database? Yes. The best example of this is on a development environment where you might have DEMO, DEV, TEST, QA all running. You can point all of them to use the same O-SES database.