Peoplesoft report re-send content

Deploying the latest Peoplesoft image (HCM92002, Peopletools 8.53.06), and you’ll see a lot of reports not posted:

Well, fine, they’re probably not that important, but it can be fixed quite easily especially since they are not posted for one single reason, the process scheduler was started before the PIA during the deployment.

More generally, if you have a bunch of reports which have a non-posted content for a problem you fixed, you may want to avoid to go in every single process just to click “re-send Content” as below:
 

Going to all and every process is tedious, so let’s try to do it with back-end statements.

First, what this “Re-send Content” is doing ? Set the AppServer in trace mode (Trace SQL=7), and we’ll see all what we need to know:
<…>
PSAPPSRV.12845 (17)      1-1286   12.04.39    0.000045 Cur#2.12845.HR92DM02 RC=0 Dur=0.000018 COM Stmt=UPDATE PSPRCSRQST SET DISTSTATUS = :1 WHERE PRCSINSTANCE = :2
PSAPPSRV.12845 (17)      1-1287   12.04.39    0.000005 Cur#2.12845.HR92DM02 RC=0 Dur=0.000000 Bind-1 type=2 length=1 value=7
PSAPPSRV.12845 (17)      1-1288   12.04.39    0.000003 Cur#2.12845.HR92DM02 RC=0 Dur=0.000000 Bind-2 type=8 length=4 value=2563
PSAPPSRV.12845 (17)      1-1289   12.04.39    0.000879 Cur#2.12845.HR92DM02 RC=0 Dur=0.000019 COM Stmt=UPDATE PSPRCSQUE SET DISTSTATUS = :1 WHERE PRCSINSTANCE = :2
PSAPPSRV.12845 (17)      1-1290   12.04.39    0.000005 Cur#2.12845.HR92DM02 RC=0 Dur=0.000001 Bind-1 type=2 length=1 value=7
PSAPPSRV.12845 (17)      1-1291   12.04.39    0.000003 Cur#2.12845.HR92DM02 RC=0 Dur=0.000000 Bind-2 type=8 length=4 value=2563
PSAPPSRV.12845 (17)      1-1292   12.04.39    0.000321 Cur#2.12845.HR92DM02 RC=0 Dur=0.000022 COM Stmt=UPDATE PS_CDM_LIST SET DISTSTATUS = '8',TRANSFERINSTANCE = 0 WHERE PRCSINSTANCE = :1 AND DISTSTATUS NOT in ('5', '9')
PSAPPSRV.12845 (17)      1-1293   12.04.39    0.000004 Cur#2.12845.HR92DM02 RC=0 Dur=0.000001 Bind-1 type=8 length=4 value=2563
PSAPPSRV.12845 (17)      1-1294   12.04.39    0.010821 Cur#2.12845.HR92DM02 RC=0 Dur=0.009838 Commit
PSAPPSRV.12845 (17)      1-1295   12.04.39    0.000032 Cur#2.12845.HR92DM02 RC=0 Dur=0.000020 Disconnect
PSAPPSRV.12845 (17)      1-1296   12.04.39    0.000819 Cur#1.12845.HR92DM02 RC=0 Dur=0.000000 Commit
PSAPPSRV.12845 (17)      1-1297   12.04.39    0.000007 Cur#1.12845.HR92DM02 RC=0 Dur=0.000004 Disconnect
PSAPPSRV.12845 (17)      1-1298   12.04.39    0.004512 Cur#1.12845.notSamTran RC=0 Dur=0.000033 Open Cursor Handle=0000000002429BE0
<…>
Actually 3 tables are involved: PSPRCSRQST, PSPRCSQUE and PS_CDM_LIST. We just have to update them properly as indicated in the trace file above to make the process scheduler a try to repost.

From the given below screenshot, 9 reports are not posted:
 

And from the back-end, the same:
SQL> select PRCSINSTANCE from PSPRCSQUE where DISTSTATUS=4 order by 1 desc;

PRCSINSTANCE
------------
        2561
        2560
        2559
        2558
        2557
        2556
        2550
        2549
        2548

9 rows selected.

SQL> select PRCSINSTANCE from PS_CDM_LIST where DISTSTATUS NOT in ('5', '9');

PRCSINSTANCE
------------
        2548
        2550
        2549
        2556
        2557
        2558
        2559
        2560
        2561

9 rows selected.

SQL> select PRCSINSTANCE from PS_CDM_LIST where DISTSTATUS NOT in ('5', '9') order by 1 desc;

PRCSINSTANCE
------------
        2561
        2560
        2559
        2558
        2557
        2556
        2550
        2549
        2548

9 rows selected.

SQL>

Then update these tables:
SQL> UPDATE PSPRCSRQST SET DISTSTATUS = 7 where DISTSTATUS=4 ;

9 rows updated.

SQL> UPDATE PSPRCSQUE SET DISTSTATUS = 7 WHERE DISTSTATUS=4 ;

9 rows updated.

SQL> UPDATE PS_CDM_LIST SET DISTSTATUS = '8',TRANSFERINSTANCE = 0 WHERE DISTSTATUS NOT in ('5', '9');

9 rows updated.

SQL> commit;

Commit complete.

SQL>

And finally, from the front-end, all are now “posted”:

And the reports are all reachable:




Of course, it won’t solve a posting problem if there is, it just tries to repost. But in such a case, useful to keep it in mind.

Nicolas.

Oracle Listener details on Peoplesoft Demo Image

If you ever wonder how to reach the databases hosted on a Peoplesoft Demo Images from a remote client…
Here from within the Image FSCM92000 (FSCMDB-SES-85302d).

For some reason, the listener has not been configured on the default and standard port 1521. The listener port is 1522 (listener name is listener1).
Moreover, whether you can choose the name of the database during the VM deployment, the service name is always appended with .us.oracle.com.
You should keep in mind those two things for your remote client connection.

[oracle@fscm92000 ~]$ export ORACLE_SID=EP92DM00
[oracle@fscm92000 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Sep 20 11:11:50 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning and Oracle Label Security options

SQL> show parameter local_listener
NAME            TYPE     VALUE
--------------- -------- -----------------------------------------------------------------
local_listener  string   (ADDRESS = (PROTOCOL=TCP)(HOST=fscm92000.phoenix.nga)(PORT=1522))
SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning and Oracle Label Security options

[oracle@fscm92000 ~]$ lsnrctl status listener1

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 20-SEP-2013 11:12:27

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=fscm92000.phoenix.nga)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     listener1
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                20-SEP-2013 10:47:53
Uptime                    0 days 0 hr. 24 min. 34 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0.x/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/11.2.0.x/db_1/log/diag/tnslsnr/fscm92000/listener1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=fscm92000.phoenix.nga)(PORT=1522)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522)))
Services Summary...
Service "EP92DM00.us.oracle.com" has 1 instance(s).
  Instance "EP92DM00", status READY, has 1 handler(s) for this service...
Service "XDB.us.oracle.com" has 1 instance(s).
  Instance "EP92DM00", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@fscm92000 ~]$

In the end, your client tnsnames.ora file should look like this:
EP92DM00 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.21)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = EP92DM00.us.oracle.com)
    )
  )

Just a little annoying but need to be kept it in mind.

Nicolas.

Addendum (23-Sept 2013):
Note there’s no consistency across images versions, for instance, the last one for HCM (HCM92002) has an other listener name, but still on port 1522:
[oracle@hcm92002 ~]$ cd $ORACLE_HOME/network/admin/
[oracle@hcm92002 admin]$ more listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0.x/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

psft_listener =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = hcm92002.phoenix.nga)(PORT = 1522))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
    )
  )

[oracle@hcm92002 admin]$ lsnrctl status psft_listener

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 23-SEP-2013 11:11:49

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hcm92002.phoenix.nga)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     psft_listener
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                23-SEP-2013 08:30:21
Uptime                    0 days 2 hr. 41 min. 28 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0.x/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/11.2.0.x/db_1/log/diag/tnslsnr/hcm92002/psft_listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hcm92002.phoenix.nga)(PORT=1522)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522)))
Services Summary...
Service "HR92DM02.us.oracle.com" has 1 instance(s).
  Instance "HR92DM02", status READY, has 1 handler(s) for this service...
Service "XDB.us.oracle.com" has 1 instance(s).
  Instance "HR92DM02", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@hcm92002 admin]$

Peoplesoft on Windows 2012 : Middleware

After installing Windows 2012 and Oracle database software, time to move on with Peoplesoft software.
Note that Peopletools 8.53 is now certified on Windows 2012 (check the certification matrix on My Oracle Support).

Before hands, we’ll first install the required software JDK, Tuxedo and Weblogic.

1. JDK
I downloaded the one available on edelivery, Oracle JDK 7 Update 9 for Microsoft Windows x64 (64-bit), part number V35015-01. After unzipping the file, run it. Nothing special here, straight forward, click, click and click. Done. Here we go:






Nothing is required for the JDK, no patch to apply afterwards.     

2. Weblogic
I download the software from edelivery, Oracle WebLogic Server 11gR1 (10.3.6) Generic and Coherence, part number V29856-01.
Now the install, straing from the command line (despite we can see “Powershell windows, I ran a “cmd” first):

Except this command line we should take care of, it’s again a “click'” installation:


The directory I specified is not empty because it is also used for Tuxedo and JDK binaries, nothing wrong to continue:

Again this uninformed question:



The installer retrieve the JDK binaries by itself:

3. Tuxedo
Last part of the middleware, Tuxedo.
Again, I download the software available on edelivery, Oracle Tuxedo 11gR1 (11.1.1.2.0) for Microsoft Windows Server 2008 (64-bit) with MS Visual Studio 2010, part number V28471-01. And again, there’s nothing special here, straight foward:




Choose the full install:










Now, we can check the Tuxedo group installed:
        
   

That’s it. Nothing really exciting here, but mandatory steps anyway in order to use Peoplesoft. Note that there’s no big change (none?) from the previous version, and Windows 2012 does not cause any troubles.

Next step, the Peopletools.

Nicolas.

Peoplesoft on Windows 2012 : Oracle software

At the time I wrote this article offline, Oracle 12cR1 was not available for Windows at all.
I decided to have a go with 11gR2 on Windows 2012, even though it is not supported. Awaiting the forth coming 12c and an upgrade to this latest version.
I’m going to install Oracle 11gR2 64bit database software on Windows 2012 64bit that I already installed as shown here.

The goal is to have a working Peoplesoft environment on my Windows 2012 plateform. It will be based on Peopletools 8.53, fully 64bit compatible on Windows plateform, but still needs 32bit Oracle client for DataMover and Application Designer. That’s why I’m also going to install Oracle 11gR2 32bit client software.

1. Oracle 11gR2 64bit database software









Since the software is not “ready” for Windows 2012, there’s a warning we’re gonna to ignore, we’re on a test plateform, it should not hurt anyway:


















Check out the version:
       

2. Oracle 11gR2 32bit client software

Be sure you’re installing a 32bit client if you don’t want to have troubles with Peoplesoft client utilities (Datamover, AppDesigner).







Again the warning because of the non ready Oracle software for Windows 2012, we’ll gonna to ignore it:








 

Nicolas.