SQL script to copy user profile in PeopleSoft application

User profile creation is mostly automated in stable PeopleSoft applications.

Sometimes we may get lots of request to create user profiles in PeopleSoft Applications manually, it may not be feasible to create all profiles from PIA.
Sometimes PIA is down due to issues or slowness in PIA, we may not be able to login in PIA using our user profile.

Simplest way to copy user profile : use navigation PeopleTools -> security -> user profile -> Copy User profile.

Here is a way to skip PIA login to copy user profile and do it from SQL plus manually.

Below SQL script runs fine in Peopletools 8.53 to copy user profile. For lower PeopleTools version ( PT version < 8.53), we need to edit script ( remove "OPERPSWDSALT," text from script  ) and script can be executed successfully in lower versions.

The script create a new user profile KETAN (ID: KETAN, Description : Ketan Pitroda, Email ID : ketan.pitroda@abc.com) from profile (ID : DHARA)

Once profile is created, we can login with new profile KETAN using password of user profile "DHARA" in PIA. We will discuss about a way to change password of user profile using SQL plus in new Post.


set echo on
whenever sqlerror exit rollback

SET DEFINE OFF;
Insert into PSOPRDEFN
   (OPRID, VERSION, OPRDEFNDESC, OPERPSWDSALT, EMPLID, EMAILID, OPRCLASS, ROWSECCLASS, OPERPSWD, ENCRYPTED, SYMBOLICID, LANGUAGE_CD, MULTILANG, CURRENCY_CD, LASTPSWDCHANGE, ACCTLOCK, PRCSPRFLCLS, DEFAULTNAVHP, FAILEDLOGINS, EXPENT, OPRTYPE, USERIDALIAS, LASTSIGNONDTTM, LASTUPDDTTM, LASTUPDOPRID, PTALLOWSWITCHUSER)
 (select 'KETAN', VERSION, 'Ketan Pitroda', OPERPSWDSALT, EMPLID, EMAILID, OPRCLASS, ROWSECCLASS, OPERPSWD,  ENCRYPTED, SYMBOLICID, LANGUAGE_CD, MULTILANG, CURRENCY_CD, LASTPSWDCHANGE, ACCTLOCK, PRCSPRFLCLS, DEFAULTNAVHP, FAILEDLOGINS, EXPENT, OPRTYPE, USERIDALIAS, LASTSIGNONDTTM, LASTUPDDTTM, LASTUPDOPRID, PTALLOWSWITCHUSER
from psoprdefn where oprid = 'DHARA');
 
Insert into PSOPRALIAS
   (OPRID, OPRALIASTYPE, OPRALIASVALUE, SETID, EMPLID, CUST_ID, VENDOR_ID, APPLID, CONTACT_ID, PERSON_ID, EXT_ORG_ID, BIDDER_ID, EOTP_PARTNERID)
( select
   'KETAN',OPRALIASTYPE, OPRALIASVALUE, SETID, EMPLID, CUST_ID, VENDOR_ID, APPLID, CONTACT_ID, PERSON_ID, EXT_ORG_ID, BIDDER_ID, EOTP_PARTNERID
from psopralias where oprid = 'DHARA');

Insert into PSROLEUSER (select 'KETAN', rolename, dynamic_sw from psroleuser where roleuser = 'DHARA');

Insert into PSUSERATTR
   (OPRID, HINT_QUESTION, HINT_RESPONSE, NO_SYMBID_WARN, LASTUPDOPRID, MPDEFAULMP)
 (select
   'KETAN', HINT_QUESTION, HINT_RESPONSE, NO_SYMBID_WARN, LASTUPDOPRID, MPDEFAULMP from psuserattr where oprid = 'DHARA');



Insert into PSUSEREMAIL
   (OPRID, EMAILTYPE, EMAILID, PRIMARY_EMAIL)
( select
   'KETAN', EMAILTYPE, 'ketan.pitroda@abc.com', PRIMARY_EMAIL from psuseremail where oprid = 'DHARA');

Insert into PSUSERPRSNLOPTN
   (OPRID, OPTN_CATEGORY_LVL, USEROPTN, USER_OPTION_CNTL, USER_OPTION_VALUE)
( select 'KETAN', OPTN_CATEGORY_LVL, USEROPTN, USER_OPTION_CNTL, USER_OPTION_VALUE from PSUSERPRSNLOPTN where oprid = 'DHARA');


Insert into PS_ROLEXLATOPR
   (ROLEUSER, DESCR, OPRID, EMAILID, FORMID, WORKLIST_USER_SW, EMAIL_USER_SW, FORMS_USER_SW, EMPLID, ROLEUSER_ALT, ROLEUSER_SUPR)
 (select
   'KETAN', 'Ketan Pitroda', 'KETAN', 'ketan.pitroda@abc.com', FORMID, WORKLIST_USER_SW, EMAIL_USER_SW, FORMS_USER_SW, EMPLID, ROLEUSER_ALT, ROLEUSER_SUPR from PS_ROLEXLATOPR where roleuser = 'DHARA');
COMMIT;


Have a Good Day.
Thanks,
Ketan.

Multi Server Web domain setup in Peoplesoft

The purpose of this post is to assist you in creation of multiServer domain from scratch in Peoplesoft. Prior to creation of multiServer web domain, you must install required PeopleTools software as per PeopleSoft software installation post.

Also, You need to plan admin port, PIA ports, JVM size, https ports, domain name and site name of your multi server domain to proceed with new domain creation.

You can quickly follow below steps and create a new multiserver domain as per your specification.

1. Create New MultiServer Domain

	Description	Input
1	Run setup.sh under $PS_HOME/setup/PsMpPIAInstall	$./setup.sh
2	Choose the directory where you wish to deploy PeopleSoft Pure Internet Architecture	Enter $PS_CFG_HOME path and next.
3	Choose the installation type that best suits your needs.	->1- Oracle WebLogic Server     2- IBM WebSphere Server Select Oracle WebLogic Server and next.
4	Select the web server root directory	Enter WebLogic Home : <Path>/weblogic1036 and next.
5	Please enter the administrator login and password for WebLogic domain.	Keep Login ID: system Password: <Password> Re-type Password: <Password> Enter next.
6	Please select 1 to create new domain.	->1- Create New WebLogic Domain Enter next.
7	Please select the configuration to install.	1- Single Server Domain   ->2- Multi Server Domain     3- Distributed Managed Server Enter 2 and Next.
8	Please enter the Integration Gateway User and Password.	Integration Gateway User [administrator]:  Password []:          Re-type Password []: Enter Password and Next.
9	Please enter the AppServer Domain Connection Password.	Password []:  Re-type Password []: Don’t Provide any password, enter two times and next.
10	Please specify a name for the PeopleSoft web site:	Website name [ps]: <website Name> Enter Website Name and next.
11	Enter port numbers and summaries.	AppServer name [host]: JSL Port [9000]: HTTP Port [80]: HTTPS Port [443]: Authentication Token Domain:(optional) []: Enter JSL, HTTP, HTTPs and Authentication Token Domain. Enter Next.
12	Please enter the Name of the Web Profile used to configure the webserver. The user id and password will be used to retrieve the web profile from the database.	Web Profile Name [PROD]: TEST User ID : PTWEBSERVER Password []: Enter password as PTWEBSERVER and go to next.
13	Select the Report Repository location: Please specify a directory name or press Enter	[PeopleSoft Internet Architecture/psreports]: <Path>/psreports Enter /.../psreports path and next.
14	Check Summary and complete Multi Server Domain Creation.	Congratulations! PeopleSoft Internet Architecture has been successfully installed to: /.../webserv

2. Start Admin Server in Multi Server Domain

	Step	Details
1	CD to $PS_CFG_HOME/webserv/<Domain>/bin	cd $PS_CFG_HOME/webserv/<Domain>/bin
2	Start Admin Server	$ ./startWebLogicAdmin.sh

3. Add New Machine in Admin Console

• Login in Admin Console with System User
• Click on Machine in Home page to add new Machine
• Click on Lock and Edit to enable edit configuration
• Click New in Machines frame to open new machine form
• Enter New Machine name (i.e. host1) and type (windows / unix), click next
• Enter listen address and port for node manager, click next
• Finish the configuration and new Machine will be added in Machines frame.

4. Update Default PIAs in Admin console with new machine created in step (3)

By default Weblogic Admin, PIA, PIA1, PIA2, PSOL, PSEMHUB and RPS servers are created in multi server domains. Also peoplesoftCluster is created in multi server domain as default but unassigned to any server.

Edit PIA, PIA2 server configuration, update Machine, Cluster Name, HTTP listner port, SSL listner Port in server settings.

5. Clone the default PIA to add more PIAs in multi server domain.

If it is required to create more than 2 PIAs in multi server domain, you need to clone the PIA server in admin console.

When cloning the PIA, enter new server IP, http port and ssl port. By this, you will be able to create
PIA3, PIA4, PIA5 and so on.

 6) Start Managed PIAs : execute $PS_CFG_HOME/webserv/<Domain>/bin/startManagedPIA.sh <PIA1 PIA2> or start PIAs from weblogic Admin console.

We have created Multi Server Domain with multiple PIAs and we can administer PIAs from Admin console (which is benifit of using multi server domain compared to single server Domain).

Thanks,
Ketan Pitroda.

SQLs to Monitor PeopleSoft Database

Important SQLs to monitor the Peoplesoft Database.

1) Monitor the database connections to track PeopleSoft User ID and its session information 

set linesize 200 

select p.spid, substr(s.osuser,1,10) osuser, substr(s.username,1,8) username, substr(s.program,1,24) program, substr(s.client_info,1,60) ClientInfo 

from v$session s, v$process p 

where s.paddr=p.addr and s.osuser is not null 

order by s.osuser

a.) To filter SQLPLUS sessions, append condition "upper(s.program) like '%SQLPLUS%'"

b.) To filter App server sessions,  append condition "upper(s.program) like '%PSAPPSRV%'"

c.) To filter Application designer sessions, append "upper(s.program) like '%PSIDE.EXE%'"

d.) To filter SQR programs condition "upper(s.program) like '%SQRW%'"

e.) To filter COBOL programs, monitor all sessions and identify programs with cobol report name like ptptedit report.

2) Identify the process ID including module and action processed by application server processes.

set linesize 200 

select p.spid, substr(s.osuser,1,10) osuser, substr(s.username,1,8) username, substr(s.program,1,24) program, substr(s.client_info,1,60) ClientInfo, substr(s.module,1,48) module, substr(s.action,1,32) action 

from v$session s, v$process p 

where s.paddr=p.addr and s.osuser is not null 

order by s.osuser; 

3) Monitor Locked objects in session

SET LINESIZE 500

SET PAGESIZE 1000

SET VERIFY OFF

COLUMN owner FORMAT A20

COLUMN username FORMAT A20

COLUMN object_owner FORMAT A20

COLUMN object_name FORMAT A30

COLUMN locked_mode FORMAT A15

SELECT lo.session_id AS sid,

       s.serial#,

       NVL(lo.oracle_username, '(oracle)') AS username,

       o.owner AS object_owner,

       o.object_name,

       Decode(lo.locked_mode, 0, 'None',

                             1, 'Null (NULL)',

                             2, 'Row-S (SS)',

                             3, 'Row-X (SX)',

                             4, 'Share (S)',

                             5, 'S/Row-X (SSX)',

                             6, 'Exclusive (X)',

                             lo.locked_mode) locked_mode,

       lo.os_user_name

FROM   v$locked_object lo

       JOIN dba_objects o ON o.object_id = lo.object_id

       JOIN v$session s ON lo.session_id = s.sid

ORDER BY 1, 2, 3, 4;

SET PAGESIZE 14

SET VERIFY ON

4) Monitor SQLs consuming most resources in oracle database.

SET LINESIZE 500

SET PAGESIZE 1000

SET VERIFY OFF

 col SQL_TEXT for a60

 col BUFFER_GETS for 99999999

 col DISK_READS for 99999999

 col EXECUTIONS for 99999999

 col sorts for 99999999

 col address for a30

SELECT *

FROM   (SELECT Substr(a.sql_text,1,50) sql_text,

               Trunc(a.disk_reads/Decode(a.executions,0,1,a.executions)) reads_per_execution, 

               a.buffer_gets, 

               a.disk_reads, 

               a.executions, 

               a.sorts,

               a.address,

a.sql_id

        FROM   v$sqlarea a

where a.sql_id in (select sql_id from v$session) 

        ORDER BY 2 DESC)

WHERE  rownum <= 10;

SET PAGESIZE 14

5) Generate grant script before you build any record in application designer, It is required since grant will be dropped after build / alter any record in Application designer.

select 'grant ' || privilege || ' on ' || table_name || ' to ' || grantee || ';' from user_tab_privs where table_name =  '<Table Name>'

6) Generate index script before you build record. It is required since existing indexes will be dropped after build / alter any record in Application designer.

select dbms_metadata.get_ddl('INDEX', 'index_name') from dual;

Shell script to identify invalid variable paths in environment

All environment variables configured in Linux or Unix OS, should be correct means - path present in environment variable should be correct. Sometime over the time, due to file system changes or directory structure changes or software upgrade or installation in system, paths become invalid. It is very time consuming to check each variable paths (env command) and validate it, especially PATH variable may contain many different paths. 

Here is the script to identify each environment variable and its wrong path configured in system. 

No impact, no delete or no changes, script does. It only reads environment variables and identify wrong paths in environment variables. Store below text in some shell script, provide execute permission (chmod 775 abc.sh) and execute it.

*******************************************************************

for Variable in `env | grep "/"`

#for Variable in `env | grep PATH`

do

V_name=`echo $Variable | awk -F"=" '{ print $1 }'`

V_Value=`echo $Variable | awk -F"=" '{ print $2 }'`

            for Path_value in `echo $V_Value | awk 'BEGIN { RS = ":" }; {print $0}'`

             do

                                                if [[ ! -f $Path_value && ! -d $Path_value ]];

                                                then

                                                                                echo $V_name " :- " $Path_value

                                                 fi

               done

  done​

*********************************************************************

Regards,

Ketan Pitroda.

Verity Search Installation

Verity search installation is optional, it is needed to install only if verity search is required in application. Peopletools must be installed as per Peopletools installation post. You must install Verity Search on Application server and you may copy search registry indexes from Application server to process scheduler server before you start build search registry index process.


Install Verity search as per below steps

	Step Description	Command / Input
1	CD to verity search installable and make sure execute permission present on setup file and temp directory path is valid
2	Execute setup to install verity search, temp directory should be /temp or choose other temp directory for installation.	$./setup.sh -tempdir $PS_HOME/PT853/temp
3	Choose the directory where you installed the PeopleSoft software, commonly known as PS_HOME	Enter PS_HOME path
4	Select the verity features for PeopleTools 8.53	Verity selected by default, enter next ->1- Verity Development Kit
5	Verify summary and complete installation.
6	Run PORTAL_BUILD app. Engine to build search registry index	Login in PIA and navigate to Peopletools à Portal à Build Search registry Index to run the app. Engine
7	Check veritybuild.log in $PS_CFG_HOME/data/search path. If below error in veritybuild log Cannot load program mkvdk.bin Dependent module libvdk30.a could not be loaded	Make sure Search path present in PATH and LIBPATH $env | grep _rs6k43 LIBPATH and PATH variable should include %PS_HOME/verity/aix/_rs6k43/bin path.

Please check Peopletools 8.53 software installation post for all installation posts.
 

PeopleTools Patch (8.53.09) Installation

Prior to Peopletools 8.53.09 patch installation, you must install Peopletools 8.53 as per Peopletools 8.53 installation post.

Quickly install Peopletools 8.53.09 patch, guidelines provided below.

	Step Description	Command
1	CD to installable file and make sure permission present	$cd cd85309 $chmod –R 755 Disk1
2	Execute setup – Make sure temp directory is valid. Follow below stesp	./Disk1/setup.sh -tempdir <$PS_HOME>/temp
3	Enter Peoplesoft Licence code	license code for PT85309 patch
4	Oracle database character set:	Enter 2 for Unicode Database ->1- Non-Unicode Database     2- Unicode Database
5	select the products to install	All are selected by default, enter next   ->1- PeopleSoft Application Server   ->2- PeopleSoft Batch Server   ->3- PeopleSoft Database Server   ->4- PeopleSoft Web Server
6	Please enter an installation location	Provide path of PT $PS_HOME
7	wish to remain uninformed of critical security issues? (Y/N)	Y
8	select the features to install:	Both 1 &2  selected by default, enter next ->1- PeopleTools  ->2- PeopleTools System Database
9	Finish the setup and verify the patch installed.

Please check peopletools software installation post for next installations.

PeopleTools Patch (8.53.09) Installation

Prior to Peopletools 8.53.09 patch installation, you must install Peopletools 8.53 as per Peopletools 8.53 installation post.

Quickly install Peopletools 8.53.09 patch, guidelines provided below.

	Step Description	Command
1	CD to installable file and make sure permission present	$cd cd85309 $chmod –R 755 Disk1
2	Execute setup – Make sure temp directory is valid. Follow below stesp	./Disk1/setup.sh -tempdir <$PS_HOME>/temp
3	Enter Peoplesoft Licence code	license code for PT85309 patch
4	Oracle database character set:	Enter 2 for Unicode Database ->1- Non-Unicode Database     2- Unicode Database
5	select the products to install	All are selected by default, enter next   ->1- PeopleSoft Application Server   ->2- PeopleSoft Batch Server   ->3- PeopleSoft Database Server   ->4- PeopleSoft Web Server
6	Please enter an installation location	Provide path of PT $PS_HOME
7	wish to remain uninformed of critical security issues? (Y/N)	Y
8	select the features to install:	Both 1 &2  selected by default, enter next ->1- PeopleTools  ->2- PeopleTools System Database
9	Finish the setup and verify the patch installed.

Please check peopletools software installation post for next installations.