Domain creation steps using Peoplesoft dpk yaml

 

Create the below response file: (Modify the file as per your requirement)

[root@zzzzz psoft]# cat responsefile_fs92dev_pt858.txt

env_type=midtier

install_type=FRESH

db_type=ORACLE

db_platform=ORACLE

db_name=FS92zzz

db_service_name=FS92zz

db_host=fin.db.dev.zzzz

db_port=1521

db_protocol=TCP

connect_id=people

connect_pwd=yinth3w0rld#4n3wu

opr_id=VP1

opr_pwd=VP1

admin_pwd=

access_id=SYSADM

access_pwd=

weblogic_admin_pwd=SysTem12

webprofile_user_id=PTWEBSERVER

webprofile_user_pwd=PTWEBSERVER1

gw_user_id=administrator

gw_user_pwd=Admin123

gw_keystore_pwd=Passw0rd

db_is_ml=no

psft_base_dir=/psoft/pt858

user_home_dir=/home

user_config_home_dir=/psoft/pt858/ps_cfg_home

[root@zzzzzz psoft]#

create the custom yaml file copy of psft_deployment.yaml

================================================================

[root@fs92-d-zzzz01 data]# pwd

/psoft/pt860/dpk/puppet/production/data

[root@fs92-d-zzz data]# vi psft_deployment.yaml

[root@fs92-d-zzzz01 data]# cp -prf psft_deployment.yaml psft_customizations.yaml

Modify the yaml file as per your requirement.

[root@fs92-d-zzzz01 data]# cat psft_customizations.yaml

---

domain_user: "%{hiera('psft_user_name')}"

ps_config_home: "/psoft/pt860/ps_cfg_home"

appserver_template: medium

appserver_domain_name: FS92DEV

prcs_domain_name: FS92DEV

prcs_domain_id: PSUNX1

pia_domain_name: FS92DEV

pia_site_name: FS92DEV

pia_http_port: 8008

pia_https_port: 8408

gateway_node_name: PSFT_FS

pia_webprofile_name: TEST

report_repository_dir: "/psoft/pt860/psreports"

report_node_name: HTTPS

appserver_domain_list:

  "%{hiera('appserver_domain_name')}":

    os_user: "%{hiera('domain_user')}"

    ps_cfg_home_dir: "%{hiera('ps_config_home')}"

    template_type: "%{hiera('appserver_template')}"

    db_settings:

      db_name: "%{hiera('db_name')}"

      db_type: "%{hiera('db_platform')}"

      db_opr_id: "%{hiera('db_user')}"

      db_opr_pwd: "%{hiera('db_user_pwd')}"

      db_connect_id: "%{hiera('db_connect_id')}"

      db_connect_pwd: "%{hiera('db_connect_pwd')}"

    config_settings:

      Domain Settings/Domain ID: "%{hiera('appserver_domain_name')}"

      PSAPPSRV/Min Instances: 3

      PSAPPSRV/Max Instances: 3

      PSAPPSRV/Max Fetch Size: 50000

      PSQRYSRV/Max Fetch Size: 50000

      PSQCKSRV/Max Fetch Size: 50000

      Security/DomainConnectionPwd: "%{hiera('domain_conn_pwd')}"

      JOLT Listener/Port: "%{hiera('jolt_port')}"

      JOLT Listener/Address: 0.0.0.0

      Workstation Listener/Port: "%{hiera('wsl_port')}"

    feature_settings:

      PUBSUB: "Yes"

      QUICKSRV: "No"

      QUERYSRV: "Yes"

      JOLT: "Yes"

      JRAD: "No"

      WSL: "No"

      DBGSRV: "No"

      RENSRV: "No"

      MCF: "No"

      PPM: "No"

      PSPPMSRV: "No"

      ANALYTICSRV: "Yes"

      SERVER_EVENTS: "No"

      DOMAIN_GW: "No"

prcs_domain_list:

  "%{hiera('prcs_domain_name')}":

    os_user: "%{hiera('domain_user')}"

    ps_cfg_home_dir: "%{hiera('ps_config_home')}"

    db_settings:

      db_name: "%{hiera('db_name')}"

      db_type: "%{hiera('db_platform')}"

      db_opr_id: "%{hiera('db_user')}"

      db_opr_pwd: "%{hiera('db_user_pwd')}"

      db_connect_id: "%{hiera('db_connect_id')}"

      db_connect_pwd: "%{hiera('db_connect_pwd')}"

    config_settings:

      Process Scheduler/PrcsServerName: "%{hiera('prcs_domain_id')}"

      Security/DomainConnectionPwd: "%{hiera('domain_conn_pwd')}"

    feature_settings:

      MSTRSRV: "Yes"

      APPENG: "Yes"

component_preboot_setup_list:

  web_profile:

    run_control_id: webprofile

    os_user: "%{hiera('domain_user')}"

    db_settings:

      db_name: "%{hiera('db_name')}"

      db_type: "%{hiera('db_platform')}"

      db_opr_id: "%{hiera('db_user')}"

      db_opr_pwd: "%{hiera('db_user_pwd')}"

      db_connect_id: "%{hiera('db_connect_id')}"

      db_connect_pwd: "%{hiera('db_connect_pwd')}"

    acm_plugin_list:

      PTWebProfileConfig:

        plugin.run: true

        env.webprofilename: "%{hiera('pia_webprofile_name')}"

        env.helpurl: "http://www.oracle.com/pls/topic/lookup?id=%CONTEXT_ID%&ctx=%{hiera('help_uri')}"

        env.updateonlycustomproperty: N

        env.propertyname: EnablePNSubscriptions

        env.validationtype: 1

        env.longvalue: true

        env.pttokendomaincomp: N

  integration_broker:

    run_control_id: intbroker

    os_user: "%{hiera('domain_user')}"

    db_settings:

      db_name: "%{hiera('db_name')}"

      db_type: "%{hiera('db_platform')}"

      db_opr_id: "%{hiera('db_user')}"

      db_opr_pwd: "%{hiera('db_user_pwd')}"

      db_connect_id: "%{hiera('db_connect_id')}"

      db_connect_pwd: "%{hiera('db_connect_pwd')}"

    acm_plugin_list:

      PTIBRenameNode:

        env.default_local_node: "%{hiera('gateway_node_name')}"

        env.app_msg_purge_all_dms: true

      PTIBConfigureDBNode:

        env.pia_webserver_host: "%{hiera('pia_host_name')}"

        env.pia_webserver_port: "%{hiera('pia_http_port')}"

        env.pia_webserver_ssl_port: "%{hiera('pia_https_port')}"

        env.pia_site_name: "%{hiera('pia_site_name')}"

        env.gateway_host: "%{hiera('pia_host_name')}"

        env.gateway_port: "%{hiera('pia_http_port')}"

        env.gateway_ssl_port: "%{hiera('pia_https_port')}"

        env.use_ssl_gateway: false

        env.use_ssl_webserver: false

        env.default_user_id: "%{hiera('db_user')}"

        env.default_local_node_pass: "%{hiera('db_user_pwd')}"

        env.anonymous_default_user_id: "%{hiera('db_user')}"

        env.configure_wsdl_node: false

    acm_plugin_order:

      - PTIBRenameNode

      - PTIBConfigureDBNode

  report_distribution:

    run_control_id: reportdistribution

    os_user: "%{hiera('domain_user')}"

    db_settings:

      db_name: "%{hiera('db_name')}"

      db_type: "%{hiera('db_platform')}"

      db_opr_id: "%{hiera('db_user')}"

      db_opr_pwd: "%{hiera('db_user_pwd')}"

      db_connect_id: "%{hiera('db_connect_id')}"

      db_connect_pwd: "%{hiera('db_connect_pwd')}"

    acm_plugin_list:

      PTProcessSchedulerReportNode:

        plugin.run: true

        env.distnodename: "%{hiera('report_node_name')}"

        env.opsys: 4

        env.uri_port: "%{hiera('pia_http_port')}"

        env.uri_host: "%{hiera('pia_host_name')}"

        env.uri_resource: "SchedulerTransfer/%{hiera('pia_site_name')}"

        env.url: "http://%{hiera('pia_host_name')}:%{hiera('pia_http_port')}/psreports/%{hiera('pia_site_name')}"

        env.cdm_proto: 0

      PTProcessSchedulerServerConfig:

        plugin.run: true

        env.servername: "%{hiera('prcs_domain_id')}"

        env.distnodename: "%{hiera('report_node_name')}"

        env.heartbeat: 60

        env.sleeptime: 15

        env.distid: ACM Administrator

        env.distidtype: 3

        env.prcstype: 'Application Engine,Data Mover,Optimization Engine,SQR Process,SQR Report,COBOL SQL,XML Publisher,nVision-Report,nVision-ReportBook,PSJob'

        env.prcscategory: Default,LOADCACHE

        env.processtypepriority: '5,5,5,5,5,5,5,5,5,5'

        env.processtypemaxconcurrent: '15,1,5,5,5,3,3,5,5,5'

        env.maxconcurrent: '25,2'

        env.prcspriority: '5,5'

        env.descr: Server Configured via ACM

        env.transfermaxretry: 3

        env.daemonprcsinst: 0

        env.maxapiaware: 25

        env.transferinterval: 60

        env.transferlogfiles: 1

        env.daemonsleeptime: 0

        env.daemoncyclecnt: 0

        env.srvloadbaloptn: 1

        env.redistwrkoption: 2

        env.start_hours: '0,0,0,0,0,0,0'

        env.start_minutes: '0,0,0,0,0,0,0'

        env.day_ofweek: '0,1,2,3,4,5,6'

        env.end_hours: '23,23,23,23,23,23,23'

        env.end_minutes: '59,59,59,59,59,59,59'

    acm_plugin_order:

      - PTProcessSchedulerReportNode

      - PTProcessSchedulerServerConfig

component_preboot_setup_order:

  - web_profile

  - integration_broker

  - report_distribution

component_postboot_setup_list:

  integration_broker:

    run_control_id: intbroker

    os_user: "%{hiera('domain_user')}"

    db_settings:

      db_name: "%{hiera('db_name')}"

      db_type: "%{hiera('db_platform')}"

      db_opr_id: "%{hiera('db_user')}"

      db_opr_pwd: "%{hiera('db_user_pwd')}"

      db_connect_id: "%{hiera('db_connect_id')}"

      db_connect_pwd: "%{hiera('db_connect_pwd')}"

    acm_plugin_list:

      PTIBActivateDomain:

        plugin.run: true

        domain.activate_retry_count: 10

        domain.activate_wait_time: 10

      PTIBConfigureGatewayNodes:

        plugin.run: true

        env.gateway_host: "%{hiera('pia_host_name')}"

        env.gateway_port: "%{hiera('pia_http_port')}"

        env.gateway_ssl_port: "%{hiera('pia_https_port')}"

        env.use_ssl_gateway: false

        env.default_local_node: "%{hiera('gateway_node_name')}"

        env.gateway_user: "%{hiera('pia_gateway_user')}"

        env.gateway_password: "%{hiera('pia_gateway_user_pwd')}"

        env.ib_appserver_host: "%{hiera('pia_host_name')}"

        env.ib_jolt_port: "%{hiera('jolt_port')}"

        env.ib_node_proxy_userid: "%{hiera('db_user')}"

        env.ib_node_proxy_password: "%{hiera('db_user_pwd')}"

        env.tools_release: "%ToolsRelease"

        env.ib_appserver_domain_password: "%{hiera('domain_conn_pwd')}"

        env.ib_set_as_default_node: true

      PTIBConfigureGatewayProperties:

        env.gateway_keystore_password: "%{hiera('pia_gateway_keystore_pwd')}"

      PTWebServerConfigUpdate:

        env.domainname: "%{hiera('pia_domain_name')}"

        env.sitename: "%{hiera('pia_site_name')}"

        env.piahome: "%{hiera('ps_config_home')}"

        env.psserver: ""

        env.KeyStorePwd: ""

    acm_plugin_order:

      - PTIBActivateDomain

      - PTIBConfigureGatewayNodes

      - PTIBConfigureGatewayProperties

      - PTWebServerConfigUpdate

psft_runtime_group_name: psoft

psft_user_name: psoft

psft_app_install_group_name: psft

psft_runtime_user_name: psoft

groups:

  psft_runtime_group:

    name: "%{hiera('psft_runtime_group_name')}"

    remove: false

  app_install_group:

    name: "%{hiera('psft_app_install_group_name')}"

    remove: false

psft_user_pwd: 0radmin

users:

  psft_user:

    name: "%{hiera('psft_user_name')}"

    gid: "%{hiera('psft_runtime_group_name')}"

    groups: "%{hiera('psft_runtime_group_name')}"

    expiry: absent

    home_dir: "%{hiera('user_home_dir')}/%{hiera('psft_user_name')}"

    password: "%{hiera('psft_user_pwd')}"

    remove: false

Run the command to install DPK inlcuding domains in custom

Silent mode installation command:

============================================================================================================================================

psft-dpk-setup.sh --silent --response_file=/psoft/pt858/responsefile_fs92prd1.txt --customization_file=/psoft/pt858/psft_customizations.yaml

./psft-dpk-setup.sh --silent --response_file=/psoft/pt860/responsefile_phiret.txt --customization_file=/psoft/pt860/psft_customizations.yaml --log_file=/psoft/pt860/logs

Optimizer hits query in oracle database

 optinmizor hits query chapter 7 performance book please check it any issue

===============================================================================

1. Connect the database to the SH schema:

CONNECT sh@TESTDB/sh

2. Set the auto-trace functionality in SQL*Plus to see only the execution plan without executing the queries:

SET AUTOT TRACE EXP

3. Select some records from the CUSTOMERS table (say, all customers born in 1949):

SELECT

C.CUST_FIRST_NAME, C.CUST_LAST_NAME

FROM sh.CUSTOMERS C

WHERE C.CUST_YEAR_OF_BIRTH = 1949;

4. Execute the same query suggesting that it's better to full-scan the CUSTOMERS table in order to answer the same query we just saw:

SELECT /*+ FULL(C) */

C.CUST_FIRST_NAME, C.CUST_LAST_NAME

FROM sh.CUSTOMERS C

WHERE C.CUST_YEAR_OF_BIRTH = 1949;

5. Inform the optimizer that we are only interested in the first 10 rows of the result:

SELECT /*+ FIRST_ROWS(10) */

C.CUST_FIRST_NAME, C.CUST_LAST_NAME

FROM sh.CUSTOMERS C

WHERE C.CUST_YEAR_OF_BIRTH = 1949;

6. Tell the optimizer to use indexes on the CUSTOMERS table:

SELECT /*+ INDEX(C) */

C.CUST_FIRST_NAME, C.CUST_LAST_NAME

FROM sh.CUSTOMERS C

WHERE C.CUST_YEAR_OF_BIRTH = 1949;

7. Disable the auto-trace functionality:

SET AUTOT OFF

List of users connect to PeopleSoft script

 1. To Find the List of Users Log in PIA

SELECT COUNT(nr) users_number FROM (SELECT COUNT(oprid) AS nr FROM psaccesslog WHERE To_date(SYSDATE) - To_date(logindttm) >= 0 AND To_date(logoutdttm) - To_date(SYSDATE) >= 0 GROUP  BY oprid);

################################################################################

2. The following query will list all the PIA users through PSACCESSLOG record:

 SELECT DISTINCT oprid,

                logipaddress,

                To_char(logindttm, 'YYYY-MM-DD:hh:mi:ss')   logintime,

                To_char(logoutdttm, 'YYYY-MM-DD:hh:mi:ss')  logoutime,

                To_char(( SYSDATE ), 'YYYY-MM-DD:hh:mi:ss') currtime

FROM   psaccesslog

WHERE  To_date(SYSDATE) - To_date(logindttm) >= 0

       AND To_date(logoutdttm) - To_date(SYSDATE) >= 0;  

   

#######################################################################################

   

3. The following query will list all the users in the current database(not just those logged in from PIA): SELECT sid,

       serial#,

       username,

       terminal,

       program

FROM   v$session

WHERE  username IS NOT NULL;  

Tablespace Utilization Script

 SELECT F.TABLESPACE_NAME,

       TO_CHAR ((T.TOTAL_SPACE - F.FREE_SPACE),'999,999') "USED (MB)",

       TO_CHAR (F.FREE_SPACE, '999,999') "FREE (MB)",

       TO_CHAR (T.TOTAL_SPACE, '999,999') "TOTAL (MB)",

       TO_CHAR ((ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)),'999')||' %' PER_FREE

FROM   (

       SELECT       TABLESPACE_NAME, 

                    ROUND (SUM (BLOCKS*(SELECT VALUE/1024

                                        FROM V$PARAMETER 

                                        WHERE NAME = 'db_block_size')/1024)

                           ) FREE_SPACE

       FROM DBA_FREE_SPACE

       GROUP BY TABLESPACE_NAME

       ) F,

       (

       SELECT TABLESPACE_NAME,

       ROUND (SUM (BYTES/1048576)) TOTAL_SPACE

       FROM DBA_DATA_FILES

       GROUP BY TABLESPACE_NAME

       ) T

WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME

Monitor the temporary tablespace utilization script

 Monitor temp usage

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

    

Globally

========

select a.tablespace_name, 

to_char(a.used/1024/1024, 'FM999,990.00')  MB_USED, 

to_char(a.total/1024/1024, 'FM999,990.00') MB_TOTAL,

to_char(used*100/total, 'FM990.00')||'%' perc_used from (

  select tablespace_name, block_size

  , (select SUM (v$sort_usage.blocks * block_size) from v$sort_usage 

     where v$sort_usage.TABLESPACE = dba_tablespaces.tablespace_name) USED

  , (select sum(bytes) from dba_temp_files where tablespace_name = dba_tablespaces.tablespace_name) TOTAL

  from dba_tablespaces

  where contents = 'TEMPORARY'

) a

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

For each session

==============

SELECT (SELECT   nvl(SUM (v$sort_usage.blocks * dba_tablespaces.block_size

                     ), 0)

               / 1024

               / 1024

          FROM v$sort_usage, dba_tablespaces

         WHERE dba_tablespaces.tablespace_name = v$sort_usage.TABLESPACE

           AND v$sort_usage.session_addr = s.saddr) sort_space_mb,

       s.*

  FROM v$session s

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

Total Temp Usage

==============

select (total_blocks*8192)/1024/1024/1000 "Avail (Gb)",(used_blocks*8192)/1024/1024/1000 "Used (Gb)",

(free_blocks*8192)/1024/1024/1000 "Free (Gb)" from v$sort_segment

TEMP usage session level

====================

Temp usage - session level 

column username format a15

column tablespace format a10

col sid format 9999

col machine format a12

col last_call_et/3600 format 999.99

col osuser format a15

set pages 1000

set lines 170

select /*+ RULE */ v.sid,v.serial#,v.username,v.osuser,v.machine, v.status,v.last_call_et/3600,tablespace,blocks*8192/1024/1024/1000 "size (GB)" 

from v$session v,v$sort_usage s

where v.saddr=s.session_addr and (blocks*8192/1024/1024/1000) > 0.5 

order by 9 desc

To See Sort being carried out

=====================

select sid, s.username, tablespace, blocks*8192/1024/1024 from v$session s,v$sort_usage ss where s.saddr=ss.session_addr;

Joining "V$SORT_USAGE" to "V$SESSION" will provide the user who is 

performing a sort within the sort segment.

The CONTENTS column shows whether the segment is created in a temporary 

or permanent tablespace.

When there are no active sorts for the instance you will not see any 

rows in v$sort_usage.

According to bug 1111185 (closed as not a bug) the BLOCKS_FREE in 

V$TEMP_SPACE_HEADER can be misleading.

"The extents are free but cached (as shown by v$temp_extent_pool).

You can look at this situation as analagous to the blocks being 

formatted rather than unformatted.

Once extents are used, they remain and don't participate in the 

bytes_free, blocks_free calculations of v$temp_space_header, at least 

for locally managed tablespaces."

Instead, you should look at FREE_BLOCKS from v$sort_segment.

the problem is same with the OEM also, it reports incorrectly.

PeopleSoft Security Script

 

The menu associated with each of the PeopleTools applications:

==============================================================

PeopleTools Application Menu 

Application designer APPLICATION_DESIGNER 

Data mover DATA_MOVER 

Definition Security OBJECT_SECURITY 

Query access QUERY 

Performance Monitor PPMI PERFMONPPMI 

To identify all permission lists (CLASSID) that provide access to the APPLICATION_DESIGNER menu using the following query:

========================================================================================================================

select distinct CLASSID from PSAUTHITEM where MENUNAME = 'APPLICATION_DESIGNER';

select CLASSID from PSAUTHITEM where MENUNAME='DATA_MOVER';

select CLASSID from PSAUTHITEM where MENUNAME='MAINTAIN_SECURITY';

To find the User associated with a particular Permission list

==============================================================

SQL> select OPRID,OPRCLASS from psoprcls where OPRCLASS='PTPT1200';

To find the Role associated with a permission list

====================================================

SQL> select * from PSROLECLASS where CLASSID='PTPT1200';

To find the User associated with a particular ROLE

======================================================

SQL> select * from PSROLEUSER where ROLENAME='PeopleTools';

To find the roll assigned to all the users

===========================================

select a.oprid User_ID,a.oprdefndesc User_Name,b.rolename,c.descr

from psoprdefn a,psroleuser b,PSROLEDEFN c

where a.oprid= b.roleuser

and b.rolename= c.rolename

and a.oprid not LIKE 'SP%'

finding permission lists with access, and identifying which users have those permission lists. 

=============================================================================================

select

    OC.OPRID,

    OC.OPRCLASS

from 

    PSOPRCLS OC

where 

    OPRCLASS = 'PSADMIN' or OPRCLASS in (

        select  CLASSID

        from    PSAUTHITEM        

        where   MENUNAME = 'APPLICATION_DESIGNER'    

        and     AUTHORIZEDACTIONS >= 1

    )

order by OC.OPRID, OPRCLASS

Note that this query takes into account the PeopleSoft Administrator role (permission list PSADMIN) as users with this role will also have access to PeopleTools.

Find out which users (and from what permission lists) have access to a particular page:

==========================================================================

select distinct OPRID, OPRCLASS

from PSOPRCLS

where OPRCLASS in (

    select distinct CLASSID

    from sysadm.PSAUTHITEM

    where PNLITEMNAME = '<PAGENAME>'

);

Synonyms and Grant Script Creation Steps

Synonyms and Grant Script Creation Steps 

 -- Run Dynamic SQL to create synonym script

SET HEA OFF;

SET PAGESIZE 0;

SET LINES 150;

SPOOL SYNONYM.SQL

select 'create public synonym '||object_name||' for '||owner||'.'||object_name||';'

from sys.dba_objects where owner = 'SYSADM' and object_type in ('TABLE', 'VIEW');

SPOOL OFF;

-- Run Dynamic SQL to create grant scripts

SET HEA OFF;

SET PAGESIZE 0;

SET LINES 150

SPOOL GRANT.SQL

select 'grant select, update, insert, delete on '||object_name||' to VOLT_SUID;'

from sys.dba_objects where owner = 'SYSADM' and object_type in ('TABLE', 'VIEW');

SPOOL OFF;

prompt

prompt Manually run synonym script 

prompt Enable spool to generate log

prompt @SYNONYM.SQL

prompt

prompt Manually run grant scripts

prompt Enable spool to generate log

prompt @GRANT.SQL