Category: Server Administration

How to Change the SysAdm Password

Nicolas Gasparato wrote a rather thorough article on the subject of user password, and mostly, I just want to capture a bookmark to it on my blog:

On The Peoplesoft Road: PeopleSoft Passwords

The best way that I have found to change the SYSADM password (the database user that PeopleSoft uses to connect) is to use Data Mover and the change_access_password command.

You can read the official documentation on the command here:

PeopleBooks: change_access_password

The syntax looks something like this:

change_access_password SYSADM1 <new password>;

The first parameter is the Symbolic ID.  While it is probably SYSADM1, you can read it from the PSACCESSPRFL table:

select * from PSACCESSPRFL

The second parameter is the new password that you want to use.

The command will both change the password in the database and change the place where PeopleTools stores the password to use when connecting.  You will still probably still want to clear the cache and restart the servers including app, batch, and web.

Script for Finding PeopleSoft Processes

I accidentally deleted a PeopleSoft Home without shutting down the app server.  So, I had to find all of the processes and manually kill each one of them.  A normal “ps -ef | grep FN91TST” didn’t cut it.  The app server spins off some JSL and WSL processes that don’t have the environment name anywhere in the command.

Here’s the script that ended up doing the job:



for e in `ls /proc/*/environ`; do
  PID=`echo $e | sed -e 's#/proc/##' -e 's#/environ##' `
  if [ -e $e ]; then
    if [ `stat -c %U $e` == $PS_USER ]; then
      CURR_PS_HOME=`cat $e | tr '\0' '\n' | grep "^PS_CFG_HOME" | sed 's/^PS_CFG_HOME=//'`
      if [ "$CURR_PS_HOME" == "$TARGET_PS_HOME" ]; then
        echo "PID: $PID Home: $CURR_PS_HOME"
        ps $PID

Note: This script assumes that you are using PeopleTools 8.52 with the PS_CFG_HOME variable configured. If not, you’ll need to change it to either the PS_HOME or PS_APP_HOME variable.

Great Links: Oracle Database Administration Commands

This article is just to remember some links that I found while working on some database issues.  Maybe they will help someone else:

Viewing the Large Pool Size (from here):

SELECT name, SUM(bytes) FROM V$SGASTAT WHERE pool='large pool'

Here are the command to view the different memory related settings/parameters (from here):

show parameter sga_target

show sga

show parameter pool

Flushing cache, etc. (from here)

alter system flush buffer_cache;

alter system flush shared_pool;

alter system switch log_file;


How to Edit PTIBUpgrade.DMS

While upgrading PeopleTools, one of the steps you have to go through is running this PTIBUpgrade Data Mover script.  It took me a bit to figure out what the values are for the script.

This ITToolbox Thread helped a little bit.

There are 3 values that you have to change in this script.  These are the values that I used:

  • Specified Default User ID: PSEM
  • Specified Default Service Namespace: <your login URL or:>
  • Default Permission List: PTPT1000

The namespace is the option that confused me the most.  According to PeopleBooks, “The namespace field on the Service pages provides qualification for attributes and elements within a WSDL document.  The value defined in the Service Namespace field in the Service Configuration page is used as the default service namespace on the Services page. The default value is”

If you have better suggestions for the values to use, please comment!



Finding TNSNames Path

Thanks to this post on StackOverflow, I learned a new trick with diagnosing TNS Names issues.  First, you need to download this tool called Process Monitor from SysInternals.

Installing the program is pretty easy.  Simply unzip the program and place it in a good location such as the Program Files directory:

Process Monitor Installed

I always like to create a shortcut in the Start Menu.  If you right click on the Programs menu in your Start Menu, you can choose the open option to open it.  Then, drag with your right mouse button, and you can drop a shortcut in your start menu:

Shortcut for Process Monitor

Once you have Process Monitor installed, you can use the filter to narrow it down to show where it is looking for tnsnames.  Here is what I found that works:

Process Monitor Filter

Add the process name for the process that you want to check.  Use psdmt for Data Mover.  pside for App Designer.  PrcsAppSrv for the App Server launched by psadmin.

Then, if you put tnsnames in for the Path it will show where it is looking for the tnsnames file.

This tool works great for all kinds of situations.  I have also used it to diagnose other connection issues.  I found it was loading the wrong Oracle client on one installation.  I found it was looking in the registry in the wrong place for Tuxedo settings because I had the wrong version.

So, this is a great tool that will help with troubleshooting.


SysDBA Connection Woes — Simple Fix

I needed SYSDBA privileges in order to extend a data file, and I could not make that happen for some reason.  I felt rather silly when I figured it out.  I’ve got to write it down in case I ever need to come back to it.

First, I opened SQLPlus without logging in:

sqlplus /nolog

Then, in SQLPlus, I tried to connect using OS authentication with SYSDBA access.  Here’s the command:

connect / as sysdba

The only response I could get was:

ORA-01031: insufficient privileges

I jumped through hoops trying to change the password for sys.  I regenerated the file password file.  Nothing helped.  Finally, I ran across this post that led me to check the OS group. All I had to do was put my Windows User into the Windows group for database administration.

I opened the start menu and right clicked on “My Computer”.  Then, I chose Manage from the menu.  In the Manage utility I went to “Local Users and Groups”, then I clicked on the Groups folder.  In the groups folder I double clicked on the “ORA_DBA” group to open the properties.  On the “Members” tab, I added my Windows username to the list.

From there, the connect worked fine.  To complete the story, here’s the resize SQL that I used:

TEMPFILE '<path to file>\pstemp01.dbf'

For me, the temp space was causing me trouble.  If you want to use a regular tablespace, the syntax is similar:

DATAFILE '<path to file>\<data file>'

Quick Note: Invalid Version of PSORA

These days the 32-bit / 64-bit Windows situation is quite frustrating!  We need everything to be either 64-bit or 32-bit.  This half and half stuff makes things challenging.

This post is just a quick note to remind you (and me) that even though you are running 64-bit Windows and 64-bit Oracle database, the PeopleTools on Windows is still 32-bit.  Data mover and the App Server both require a 32-bit database client to talk to the database.

If you don’t install a 32-bit Oracle client, you may get an error that reads something like this:

PS General SQL Routines: Missing or invalid version of SQL library PSORA (200,0)<


Troubleshooting Report Repository Error

I thought I had my environment built, but I keep finding little problems.  Well, this time it was the report repository.  I got the reports posting, and I thought I was home free.  The solution ended out being simpler than I thought.

Let me just walk you through some of the things I checked to see if it helps any of the problems you may be having.

My specific error was: “Site name is not valid“.

The cause was that my Report Repository path was not configured.  PeopleTools > Web Profile > Web Profile Configuration

Blank Report Repository Path

The catch was that somehow I got mixed up on which Profile I was using.  You can check your profile easily from the Web Profile History:

Determining Current Web Profile from Last Loaded

On the search page of the Web Profile History, you can see which profile was loaded last.  You can sort the list by clicking on the “Profile was Loaded” heading.  If you click a second time, it will sort it descending.  Notice that previously I was using the Dev profile on port 8000.  When I last installed PIA, I must have configured it for the Prod profile on Port 80.  So now, I need to configure things on the Prod profile.

Here’s some other things you can check.  Make sure your distribution node is connected to your server:

Report Node connecting to Server

Then, check the Report Node.  Make sure the URL matches the URL you are using to connect to the Portal.  The domain name and port should match.  If there is no port number like mine, you are using port number 80.  Then, check the portal domain name.  For me, I am using the default “ps”.

According to this post, you also need to check the local node.  In PeopleTools > Portal > Node Definitions, look for the node with Local Node set to “Y”:

Default Local Node Search

Now, make sure the node is set to password authentication.  I am not sure what else to check here.

Node Options

Finally, check your file for the path.  I am not sure what this setting controls because mine was wrong just now, yet it seemed to be working.

Look in <PS Home>\webserv\<domain>\applications\PORTAL.war\WEB-INF\psftdocs\<domain>\

configuration properties location

The Repository path is in that file:

Repository Setting in Configuration.Properties


Deleting Projects from PeopleSoft

Have you ever needed to delete a project with all of its objects from an environment?  I don’t know if this necessarily something you would do in a real production environment, but maybe some of the thoughts will help someone out.

I wanted to get all of my little development projects out of this environment to return it back as close to a clean demo environment as possible.  Here’s what I did:

Step 1: Security

If your project contains any Roles or Permission Lists, you need to remove those from the User Profiles first.
A Role in a Project

I learned this the hard way. My user was connected to one of the roles that I deleted via the project. All of the sudden, I couldn’t get to anything inside PeopleSoft Online. I couldn’t log back into App Designer anymore. I had to remove the role from PSROLEUSER manually in the database before I could do anything.

You can use this query to check any roles:

WHERE ROLENAME = '<role name>';

If it is a permission list, you may want to check this query:

WHERE C.CLASSID = '<permission list name>'

Here’s where I am deleting the Role from my user:
Deleting the Role

Step 2: Check your project

Make sure you know what you are deleting! You can’t get these objects back unless you have a backup.

Click on the Upgrade tab and just go through all the folders to make sure you know what objects are included. You want to watch for any delivered objects that you had modified that you don’t really want deleted.
Clicking on the Upgrade Tab in the Project

Step 3: Change the Upgrade Action

Now, you need to change the upgrade action for each of the objects. If this is a large project, you will want to do this from the database:

WHERE PROJECTNAME = '<project name>';

You can see it from the Upgrade tab in the project:
Upgrade Action set to Delete

Step 3: Export the Project to file

Use the menu Tools > Copy Project > To File… Make sure all the object types are selected, and key in the path to a temporary folder.
The Copy to File Dialog

Step 4: Import the Project back from File

Use the menu Tools > Copy Project > From File … Browse to the same path where you save the project. Select that project and import it:
Selecting the Project

Make sure that you select to use the project definition from the File:
Selecting to use the project from the file.

Again, select all the object types and hit copy:
Copy Project From File dialog

Step 4: Confirm Objects Deleted

Now, try to open a few of the objects from the project. You should get a message that it doesn’t exist:
Object Does Not Exists Message

Step 5: Clean up the Database

Here is some SQL that should generate the Drop statements for all of the tables involved in the project. This is the Oracle version:

WHERE B.PROJECTNAME = '<project name>'

WHERE B.PROJECTNAME = '<project name>'

This is the SQL Server version:

WHERE B.PROJECTNAME = '<project name>'

WHERE B.PROJECTNAME = '<project name>'

Step 6: Delete the Project

Now, you are done with your list of definitions, and you can just remove the whole project using App Designer. Use the File > Delete … option.
Deleting the Project

Step 7: Check your system.

Run the DDDAudit and the SysAudit and make sure that you haven’t left anything broken. You can run those from PeopleTools > Process Scheduler > System Process Requests.

Step By Step: PeopleTools 8.51 Upgrade (Part 7)

This is a continuation of my PeopleTools 8.51 posts.  Now that we have our servers started, we can finish the Change Assistant Job.

Please see my Step by Step page for more articles like this, or you can start at the beginning of this series withPart 1.

Read More