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.

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'


Oracle Database will typically initialize and running on the default ip address and port: 1521. However, since this is pretty common knowledge you may wish to change up the ports and/or ip address to force it listen somewhere else.

1) Modify the TNSNAMES.ora file to listen to the specific address and port that you want:
{DBNAME}=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST={IPADDRESS})(PORT={PORT})))(CONNECT_DATA=(SERVER=DEDICATED)(SID={ORACLE_SID})))

2) Modify (create) the LISTENER.ora file to listen to the specific address and port that you want:
my_listener=(DESCRIPTION_LIST=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST={IPADDRESS})(PORT={PORT}))(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1522))))

3) Add the following line to the init{ORACLE_SID}.ora file to force it to listen:
LOCAL_LISTENER='(ADDRESS=(PROTOCOL=TCP)(HOST={IPADDRESS})(PORT={PORT}))’

Replace the {DBNAME}, {ORACLE_SID}, {IPADDRESS} and {PORT} variables. NOTE: this is just an example you may need to tweak this a bit for your specific environment. Restart the database and listener and you should be good to go! Not make sure that your firewall knows about the change otherwise you may cause things to break!

I often get asked about performance issues with SQL Server. I often find with a quick check that the data and log files for the database are being stored in terrible inefficient ways. I am not going to argue local disks versus SAN storage in this post, as more often then not my clients are moving to virtualized environments so the storage is on a SAN or on a centralized disk array of some sort.

Lets start with one big drive is not a good way to run an Enterprise Database. I appreciate that SQL Server is easy to setup and get working, but making it work efficiently and effectively takes a little more understanding then accepting the default prompts during the install.

1. Have a small drive setup to run the OS layer of the machine, I have found 40gb is usually more then sufficient for this.
2. Storing Data files: these should be stored on a drive that is within a RAID 5, 6 or 10 configuration.
3. Storing Log Files: these should be stored on a drive that is within a RAID 1 or 10 (however, 5 or 6 will work too).
4. Storing TEMP DB Files: these should be stored on a drive that is RAID 1 (however, 5,6 or 10 will work too).

In a lot of cases, items 2,3,4 are all stored on the same drive. This maybe fine for non-critical stuff, but you will want your Production environments split up. The most common drive configuration I see is RAID 5 or 6, but in bigger SAN’s you will see RAID 10 often. Now that drives are relatively cheap I find disk arrays to be very cost effective for organizations and offer up great flexibility. This is one area that I recommend buying good equipment. Additionally, take speed over capacity and quantity over capacity. The faster the data can be read and/or written makes a difference, and the more drives you have the more heads you have for reading and/or writing.

Lastly, know what is sharing your storage areas. If you have a SAN that is carved up and server multiple databases or environments, you will end up spreading the read/write ability of the equipment across those environments. This is a common problem I see where people will say there must be something wrong with PeopleSoft because it isn’t doing anything and is still slow, but if you dig into the available resources you will find another application is hammering the resources which are shared with PeopleSoft, and therefore PeopleSoft is working fine it just doesn’t have any available resources to run effectively.

Microsoft SQL Server: SQLNCLI11 – Linked Server DTC Issue

When using a Microsoft SQL Server – Linked Server between a remote database server, you may encounter an error when trying to pass transaction sets between the environments.

[Microsoft][SQL Server Native Client 11.0][SQL Server]OLE DB provider “SQLNCLI11” for linked server “REMOTEDB” returned message “The transaction manager has disabled its support for remote/network transactions.”.
[Microsoft][SQL Server Native Client 11.0][SQL Server]The operation could not be performed because OLE DB provider “SQLNCLI11” for linked server “REMOTEDB” was unable to begin a distributed transaction.

In order to rectify this issue you will want to turn on Distribution Transaction Coordinator (DTC) to allow remote connections. On Windows 2008, go into Administrative Tools > Component Services, expand the component services, computers, my computer, and distributed Transaction Coordinator. Its relatively the same process for Windows 2012 & 2012R2.

Right click on the Local DTC, and select properties. Under the Security Tab, select allow remote clients and agents, and allow inbound and outbound transactions. You will need to do this on both the local and remote databases. When you make changes to the DTC it will restart the service. Depending on your configuration you may need to reboot the server for this to take effect.

What is DTC?

The Distributed Transaction Coordinator (MSDTC) service is a component of modern versions of Microsoft Windows that is responsible for coordinating transactions that span multiple resource managers, such as databases, message queues, and file systems. MSDTC is included in Windows 2000 and later operating systems, and is also available for Windows NT 4.0. MSDTC performs the transaction coordination role for components, usually with COM and .NET
architectures. In MSDTC terminology, the director is called the transaction manager. By default, the Microsoft Distributed Transaction Coordinator (MSDTC) service is installed with Windows 2000 and onwards, and is considered to be core functionality that cannot be uninstalled.