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.

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:

#!/bin/bash

PS_USER=psoft
TARGET_PS_HOME=/psoft/FN91TST

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
      fi
    fi
  fi
done

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.

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'
GROUP BY ROLLUP(name);

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;

Resources

• Burleson Consulting: ORA-04031: unable to allocate string bytes of shared memory(“string”,”string”,”string”,”string”) tips
• Ask Tom Oracle: Question Details
• Flusing Pools in Oracle 10

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:

ERROR:
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:

ALTER DATABASE
TEMPFILE '<path to file>\pstemp01.dbf'
AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED

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

ALTER DATABASE
DATAFILE '<path to file>\<data file>'
 AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED