Upload Target Database to Source Image in PUM (PeopleSoft Update Manager)

In this article I have included details of

1) How PeopleSoft Update Manager (PUM) collects information about Target Database and Source Image?

2) What information PUM collects about Target Database through Change Assistance?

Answer :

Information about target database is stored in Source Image when you upload target database information to source image in change assistance (Tools -> Upload Target Database to Source Image).

Change Assistance initiate Java process which connect to Target database to pull information from target database and connect to Source Image to upload information in source database.

Data from below tables pulled from Target Database after login Authentication using PeopleSoft User.
1)PSLANGUAGES2)PSRELEASE3)PS_MAINTENANCE_LOG4) PSRECFIELD and PSDBFLDLABL (Recursive SQL)5) PS_INSTALLATION6) PSSTATUS


Data uploaded in below tables in Source Image1) PS_PTIASPTARGETS2) PS_PTIASPTARGETLNG3) PS_PTIASPUSERTGTS4) PS_PTIASPLOGTGT5) PS_PTIASPINSTPRD
I have uploaded trace files for reference. This is helpful to know what exact information from above tables fetched by PUM.


PUM uses information from above tables to Create Change Package or Upgrade template.

Below select statements in trace shows data selected from Target Database :
------------------------------------------------------------------------------------------------------
SELECT LONGNAME, LANGUAGE_CD FROM PSOPTIONS
SELECT LANGUAGE_CD FROM PSLANGUAGES WHERE INSTALLED = 1
SELECT TO_CHAR(CAST((RELEASEDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'), RELEASELABEL FROM PSRELEASE WHERE RELEASEDTTM = (SELECT MAX(D.RELEASEDTTM) FROM PSRELEASE D)
SELECT RELEASELABEL FROM PS_MAINTENANCE_LOG WHERE RELEASELABEL LIKE '%PeopleTools%' ORDER BY RELEASELABEL DESC
SELECT DISTINCT A.UPDATE_ID, TO_CHAR(CAST((A.DTTM_IMPORTED) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF') FROM PS_MAINTENANCE_LOG A WHERE A.DTTM_IMPORTED = (SELECT MAX(B.DTTM_IMPORTED) FROM PS_MAINTENANCE_LOG B WHERE A.UPDATE_ID = B.UPDATE_ID)
SELECT FIELDNAME, LABEL_ID, USEEDIT FROM PSRECFIELD WHERE RECNAME = :1
SELECT ALTACCT_OPTN FROM PS_INSTALLATION
SELECT SHORTNAME, LONGNAME FROM PSDBFLDLABL WHERE FIELDNAME = 'ALTACCT_OPTN' AND (LABEL_ID = ' ' OR (' ' = ' ' AND DEFAULT_LABEL = 1))
SELECT COMMITMENT_CNTL_AP FROM PS_INSTALLATION
SELECT COMMITMENT_CNTL_AR FROM PS_INSTALLATION
SELECT COMMITMENT_CNTL_BI FROM PS_INSTALLATION
SELECT COMMITMENT_CNTL_EX FROM PS_INSTALLATION
SELECT COMMITMENT_CNTL_GL FROM PS_INSTALLATION
SELECT COMMITMENT_CNTL_GM FROM PS_INSTALLATION
SELECT COMMITMENT_CNTL_IN FROM PS_INSTALLATION
SELECT COMMITMENT_CNTL_PC FROM PS_INSTALLATION
SELECT COMMITMENT_CNTL_PO FROM PS_INSTALLATION
SELECT COMMITMENT_PROCARD FROM PS_INSTALLATION
SELECT DC_INSTALLED FROM PS_INSTALLATION
SELECT JETFORMS FROM PS_INSTALLATION
SELECT MULTIBOOK_FLAG FROM PS_INSTALLATION
SELECT PROC_PART_GL FROM PS_INSTALLATION
SELECT ADBA FROM PS_INSTALLATION
SELECT AM FROM PS_INSTALLATION
SELECT AP FROM PS_INSTALLATION
SELECT AR FROM PS_INSTALLATION
SELECT AUC FROM PS_INSTALLATION
SELECT BI FROM PS_INSTALLATION
SELECT CA FROM PS_INSTALLATION
SELECT CAT FROM PS_INSTALLATION
SELECT CCM FROM PS_INSTALLATION
sELECT CCU FROM PS_INSTALLATION
SELECT CFG FROM PS_INSTALLATION
SELECT CP FROM PS_INSTALLATION
SELECT DEAL FROM PS_INSTALLATION
SELECT DMI FROM PS_INSTALLATION
SELECT DP FROM PS_INSTALLATION
SELECT EB FROM PS_INSTALLATION
SELECT EG FROM PS_INSTALLATION
SELECT ES FROM PS_INSTALLATION
SELECT EX FROM PS_INSTALLATION
SELECT FAA FROM PS_INSTALLATION
SELECT FCA FROM PS_INSTALLATION
SELECT FO FROM PS_INSTALLATION
SELECT FO_PBM FROM PS_INSTALLATION
SELECT FP FROM PS_INSTALLATION
SELECT FPA FROM PS_INSTALLATION
SELECT GL FROM PS_INSTALLATION
SELECT GM FROM PS_INSTALLATION
SELECT GVC FROM PS_INSTALLATION
SELECT HR FROM PS_INSTALLATION
SELECT INV FROM PS_INSTALLATION
SELECT IP FROM PS_INSTALLATION
SELECT ITAM FROM PS_INSTALLATION
SELECT ITK FROM PS_INSTALLATION
SELECT MG FROM PS_INSTALLATION
SELECT MPA FROM PS_INSTALLATION
SELECT OM FROM PS_INSTALLATION
SELECT PC FROM PS_INSTALLATION
SELECT PGM FROM PS_INSTALLATION
SELECT PM FROM PS_INSTALLATION
SELECT PO FROM PS_INSTALLATION
SELECT PPALM FROM PS_INSTALLATION
SELECT PPESA FROM PS_INSTALLATION
SELECT PPFIN FROM PS_INSTALLATION
SELECT PPSCM FROM PS_INSTALLATION
SELECT PRD FROM PS_INSTALLATION
SELECT PV FROM PS_INSTALLATION
SELECT QS FROM PS_INSTALLATION
SELECT RE FROM PS_INSTALLATION
SELECT RISK FROM PS_INSTALLATION
SELECT RS FROM PS_INSTALLATION
SELECT SCMT FROM PS_INSTALLATION
SELECT SP FROM PS_INSTALLATION
SELECT SPL FROM PS_INSTALLATION
SELECT TD FROM PS_INSTALLATION
SELECT TPM FROM PS_INSTALLATION
SELECT TR FROM PS_INSTALLATION
SELECT WO FROM PS_INSTALLATION
SELECT WR FROM PS_INSTALLATION
SELECT TOOLSREL, UNICODE_ENABLED FROM PSSTATUS
SELECT PTPATCHREL FROM PSSTATUS

Below insert statements shows data Uploaded to PUM Source Image :

------------------------------------------------

INSERT INTO PS_PTIASPTARGETS (DBNAME, LONGNAME, RELEASELABEL, PTIASPTOOLSMAJOR, PTIASPTOOLSMINOR, LANGUAGE_CD) VALUES (:1, :2, :3, :4, :5, :6)

INSERT INTO PS_PTIASPTARGETLNG (DBNAME, LANGUAGE_CD) VALUES (:1, :2)

INSERT INTO PS_PTIASPUSERTGTS (USERID, DBNAME) VALUES (:1, :2)

INSERT INTO PS_PTIASPLOGTGT (DBNAME, PTIASPRPTNO, PTIASPPKGAPPLYDT) VALUES (:1, :2, TO_DATE(:3,'YYYY-MM-DD'))

INSERT INTO PS_PTIASPINSTPRD (DBNAME, PTIASPPRDGRPCD, PTIASPPRDCD, PTIASPPRDNAME, PTIASPINSTALLTBL) VALUES (:1, :2, :3, :4, :5)

Thanks,

Ketan.

Windows Command to get system information, Disk details, CPU, Process, Memory usage and Registry information

When you want to prepare Health check report or Environment configuration details reports for windows system. Below commands are helpful to get details.

DXDIAG

DirectX Diagnostic Tool provides information about your Computer Name, Operating System Version,Language, System Model, Processor, Memory information in graphical view.


You can use below command on command prompt to store output in text file.

Run CMD and execute Dxdiag /t <Path>/dxdiag.txt

SYSTEMINFO : 

SYSTEMINFO command can be executed on command prompt and use this command to get information about Host Name, OS Version, OS Manufacturer, Registered Owner / Organization, BIOS Version, Time zone,  SYSTEM model, system boot time, system type, Processor, Windows / system directory, Total Physical Memory, Available Memory, Virtual Memory, Domain Name, Hot Fixs applied in windows, Page file location, Network Card and Network IPs of windows system.

Run CMD and execute systeminfo command to view result.
 or
Run directly systeminfo command.

WMIC

Windows Management Insrumentation command can be used to get process details, CPU details, Disk Details, Memory usage details and so on.

Open command prompt :

To Get Process details using wmic : wmic process where name='outlook.exe'

To Get All disk drives usage details : wmic LOGICALDISK GET SIZE,FREESPACE,CAPTION

To Get CPU usage on windows : wmic cpu get loadpercentage

To Get Memory / RAM Usage on Windows : wmic os get freephysicalmemory or wmic os get freevirtualmemory

To Get Total RAM memory details : wmic computersystem get totalphysicalmemory.

REG  / REGEDIT

Use regedit To Get Registry information on windows. You can store all registry key values in text file using export menu in File menu of Registry Edit Window.

Use reg from Command line to get registry value of any Parameter.

Execute below command to get all registry parameters under path :  HKEY_LOCAL_MACHINE\SYSTEM\CurrentContorlSet\Services\TcpIp\Parameters in windows registry.
reg query HKEY_LOCAL_MACHINE\SYSTEM\CurrentContorlSet\Services\TcpIp\Parameters
 

Thanks,
Ketan.

Automatic Log clearance shell Script / Script to Delete logs

Admins do Log Clearance on Unix/Linux server manually. It is very difficult to identify space consuming logs and delete hundreds of logs manually.
To overcome manual routine work, I have prepared shell script called autoclean.sh to delete logs based on Search Path, Search pattern and Retention days defined in autoclean.cfg.

How autoclean.sh works?
autoclean.sh Script will find autoclean.cfg file where you have defined Search Path, Search Pattern and Retention days.
autoclean.sh script will read autoclean.cfg.
autoclean.cfg file will have Search Path, Search Pattern and Retention days.
autoclean.sh script will delete logs as per autoclean.cfg file.

How to setup the script?
1) Copy autoclean.sh script on server at any location of your choice.
2) Identify all log paths on server where you want to delete logs.
like /mn01/...../log1, /mn02/....../log2
3) Create autoclean.cfg file using vi editor at all locations where you want to delete logs
like /mn01/...../log1/autoclean.cfg, /mn02/....../log2/autoclean.cfg
4) Define search pattern, retention date and search path in each autoclean.cfg.
Below is sample of autoclean.cfg
rm_log=PSRENSRV*.LOG MONITORSRV*.LOG TUXLOG.*  APPSRV_*.LOG PSAPPSRV_*.LOG WATCHSRV_*.LOG TUXACCESSLOG.* *.tracesql
rm_path=/mn01/app01/server/domain1/log1
rm_retention=10
where rm_log contains search pattern, rm_path contains log path and rm_retention contains retention days.

5) execute autoclean.sh manually or schedule script using crontab to delete logs automatically.

As mentioned, when you execute autoclean.sh script : script will find all autoclean.cfg files, script will read all autoclean.cfg file and script will delete logs from all locations mention in autoclean.cfg files as per search pattern and retention days.

Here is code of autoclean.sh script.

#!/bin/bash
#
# SCRIPT: autoclean.sh
# AUTHOR: Ketan Pitroda
# DATE:   06/29/2015
# REV:    1.1.A (Valid are A, B, D, T and P)
#               (For Alpha, Beta, Dev, Test and Production)
#
# PLATFORM: (AIX, HP-UX, Linux, Solaris)
#
# PURPOSE: autoclean.sh script clears the logs as per autoclean.cfg file.
#          The script searches autoclean.cfg file in search_base directory.
#          The script deletes log files as per log path (rm_path), search pattern(rm_log) and retention period (rm_retention)
#
#
# REV LIST:
#        DATE: DATE_of_REVISION
#        BY:   AUTHOR_of_MODIFICATION
#        MODIFICATION: Describe what was modified, new features, etc--
#
#
# set -n   # Uncomment to check script syntax, without execution.
#          # NOTE: Do not forget to put the comment back in or
#          #       the shell script will not execute!
# set -x   # Uncomment to debug this shell script
#
##########################################################
#         DEFINE FILES AND VARIABLES HERE
##########################################################
search_base=$HOME
rm_log="Not Defined"
rm_retention=99999
rm_path="Not_Defined"

##########################################################
#              DEFINE FUNCTIONS HERE
##########################################################
get_rm_path()
{
        rm_path=`grep "rm_path" $cfg_file | awk ' BEGIN { FS = "=" };  $1 ~ /rm_path/ { print $2 } '`
        if [ $rm_path"/autoclean.cfg" != $cfg_file ]
        then
                echo "Please define correct rm_path in $cfg_file."
                continue
        fi
}
get_rm_log()
{
        rm_log=`grep "rm_log" $cfg_file | awk ' BEGIN { FS = "=" } $1 ~ /rm_log/ { print $2 } '`
        if [ -z "$rm_log" ]
                then
                        echo "Please define search pattern for logs to be deleted in autoclean.cfg"
                        continue
                fi
        y=0
        for element in $rm_log
        do
                criteria[$y]=$element
                ((y = y + 1))
        done
}
get_rm_retention()
{
        rm_retention=`grep "rm_retention" $cfg_file | awk ' BEGIN { FS = "=" }  $1 ~ /rm_retention/ { print $2 } '`
        if [ -z "$rm_retention" ]
        then
                echo "Please define retention days for files to be deleted in autoclean.cfg"
                continue
        fi
        rm_retention="+"$rm_retention
}
list_clean_config()
{
        echo "-----------------------------------------------------------------------------------------------------------------------------"
        echo "Validated autoclean.cfg configuration"
        echo "autoclean.cfg file : "$cfg_file
        echo "rm_path            : "$rm_path
        echo "rm_log             : "$rm_log
        echo "rm_retention       : "$rm_retention
        echo "Search Pattern     : "${criteria[*]}
}
list_files_for_delete()
{
        echo "List of Files to be Deleted"
        x=0
        while [ $x -lt ${#criteria[@]} ]
        do
        find $rm_path/ -maxdepth 1 -name ${criteria[$x]} -mtime $rm_retention -exec ls -ltr {} \;
        ((x = x + 1))
        done
}
delete_files()
{
        echo "Deleting the files now"
        y=0
        while [ $y -lt ${#criteria[@]} ]
        do
                for delete_file_name in `find $rm_path/ -maxdepth 1 -name ${criteria[$y]} -mtime $rm_retention -exec ls {} \;`
                do
                        echo "rm "$delete_file_name >> autoclean_delete.log
                        rm $delete_file_name    >> autoclean_delete.log
                done
        ((y = y + 1))
        done
}
##########################################################
#               BEGINNING OF MAIN
##########################################################
date >> autoclean_delete.log
echo "------------------------------------------------------------------------------------------------------" >> autoclean_delete.log
for cfg_file in `find $search_base -name autoclean.cfg -print`
do
        get_rm_path
        get_rm_log
        get_rm_retention
        list_clean_config
        list_files_for_delete
        delete_files
done

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.