Data Guard Implications of NOLOGGING operations from PeopleTools 8.48

Stuff changes. I keep finding more changes in PT8.48.

From PeopleTools 8.48, PeopleSoft builds all indexes with the NOLOGGING option, and thne it alters them to logged objects. This is done to reduce redo generation during the index build process. It can save time by reducing redo, and can be useful in development environments.

However, this has implications when running Oracle Data Guard. If an operation is not logged on the primary, it will not be replicated to the standby databases.

Here is a simple test. We will create a table on the primary database, but the index will be created NOLOGGING, which exactly what PeopleSoft does. I have deliberately set the database to allow NOLOGGING operations

ALTER DATABASE NO FORCE LOGGING;

CREATE TABLE t (a number);

INSERT INTO t 
SELECT rownum FROM dba_objects 
WHERE ROWNUM <= 1000;  

CREATE UNIQUE INDEX t_idx ON t (a) NOLOGGING;    

ALTER INDEX t_idx LOGGING;      

ALTER SYSTEM SWITCH LOGFILE;

Switching the log file forces the changes to be replicated to the secondary database. Now open the secondary database in read only:

SELECT /*+ FULL(t)*/ COUNT(a) FROM t;

 COUNT(A)
----------
     1000

So there are 1000 rows in the table, but if I force Oracle to count the rows in the index using a hint I get a corrupt block error:

SELECT /*+ INDEX_FFS(t t_idx)*/ COUNT(a) FROM t
                                         *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 1, block # 134419)
ORA-01110: data file 1: '/u01/oradata/hcm89/system01.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option

This could happen every time you build an index using a script generated by the application designer. The status on the corrupt index is still VALID, so you would have no way of knowing whether this problem existed before you open your standby and run on it (because you can't run PeopleSoft on a standby database in read only mode).

So how can you prevent this from happening in the first place?

You could alter the DDL models to remove the NOLOGGING option from the CREATE INDEX DDL model. But the same argument applies to any NOLOGGING operation performed in the database. You are likely to get corrupt objects in your standby database. Although, I know of no other NOLOGGING operations in a vanilla PeopleSoft database, if any are introduced during development then DBAs are unlikely to be able to change them.

It was for exactly this reason that Oracle provided the ability to force NOLOGGING operations to generate redo information. It is set with the following command:

ALTER DATABASE FORCE LOGGING;

Now the database will log all operations regardless and all objects and operations will be fully replicated to the standby database. However, nothing is free. NOLOGGING is often used to improve performance by reducing redo, for example when populating working storage tables which will never need to be recovered. These performance improvements will be lost when logging is forced.

Database Login Information In Your Sqlplus Prompt

Have you ever run the wrong script in SQL*Plus in the wrong database?

It is common to have many databases in PeopleSoft development environments, and developers must often connect to different databases. To help reduce the chance of accidentally working in the wrong environment I have my login script on SQL*Plus that puts information about my session in the command prompt. When you connect to a database SQL*Plus automatically runs %ORACLE_HOME%/sqlplus/admin/glogin.sql. I have put my own login script gfclogin.sql into the same directory. It is called from glogin.sql like this:

@@gfclogin.sql

The double @ means that the called script is in the same directory as the calling script. gfclogin.sql runs a number of SQL queries and stores the output in the SQLPROMPT. Note that from SQL*Plus version 10, the login script is run on every connections, previously it was only run for the initial connection. This has one negative side effect. It will overwrite and SQL statement in the SQL*Plus command buffer when you reconnect (from SQL*Plus 10)

The some of the queries in the script only work if the connecting user has SELECT_CATALOG_ROLE (or individual privilege to query v$session, v$process, v$database. If the user doesn't have these privileges the prompt will just contain the user name

SCOTT>

On a PeopleSoft database it will also report the name of the PeopleSoft database from PS.PSDBOWNER, thus:

SYSADM-HCM89>

If the user has SELECT_CATALOG_ROLE it will also show the session ID, session serial number, database name, name of OS user running the database, and the node name of the database server.

SCOTT.147:4264.GOFASTER.SYSTEM.GO-FASTER-4>

or

SYSADM-HCM89.148:5420.HCM89.SYSTEM.GO-FASTER-4>

Unix Process Limits for PeopleSoft

I have been having some trouble with long running Component Interfaces/Application Engine processes. The amount of memory allocated to the process continues to grow throughout the life of the process, and eventually the process hits the limit imposed by Unix. This leads to two modes of error.

You will get an error message like this in the Application Engine log file.

SQL error. Stmt #: Error Position: Return: 8007 - Insufficient memory available

The process might continue to run, or it might fail (I am not sure what makes the difference here), but if it terminates you get will get a message like this also in the Application Engine log.

<Unix PID>: 1181303384: PSPAL::Abort: Unrecoverable signal received
<Unix PID>: 1181303384: PSPAL::Abort: Location: /vob/peopletools/src/pspal/exception_sigaction.cpp:553: PSPAL::SigactionSignalHandler::UnrecoverableSignalHandler(int, siginfo_t *, void *)
<Unix PID>: 1181303384: PSPAL::Abort: Generating process state report to <PS HOME>appserv/prcs/<Process Scheduler Name>/LOGS/psae.<Unix PID>/process_state.txt
<Unix PID>: 1181303384: PSPAL::Abort: Recursive call to Abort detected. Terminating process now.

A core file is also produced.

Application Engine seems to continue to allocate memory but not relinquish it again. I have found support notes on Customer Connection that recommend use of the CollectGarbage() function in PeopleCode in order to 'remove any unreachable application objects created by Application Classes'. However, my tests suggest that this function does not cause any memory to be released by the process.

Another suggestion from Customer Connection was to raise the Unix system limit for the soft data segment with ulimit -d. This has resolved the memory errors. The command ulimit -d can be user to set soft limits in the Unix shell. However, they cannot be set higher than hard limits. Hence it is necessary to set unlimited parameters in /etc/security/limits (this file can only be read or updated by the superuser: root).

It is common to disable the limits for Oracle, as shown in the example below. I think the same should be done for PeopleSoft accounts. Machine-wide parameter settings the set in the default section. Individual parameters can then be overridden for individual users. The following example is taken from AIX 5.3. The sizes are multiples of 512 byte blocks. A value of -1 means that the parameter is unlimited.

default:
  fsize = 2097151
  core = 2097151
  cpu = -1
  data = 262144
  rss = 65536
  stack = 65536
  nofiles = 4000
root:
  fsize = -1
  data = 4194303
  rss = 4194303
  nofiles = 4000
…
oracle:
  fsize = -1
  data = -1
  stack = -1
…
psoftusr:
  data = -1

Changes to the limits take effect when the user next logs onto Unix. After changing the limits you need to log out and back into your Unix account and restart the process scheduler

PeopleTools 8.48 Process Scheduler Behaviour

Things change, and I recently got caught out by one of those changes.

In PeopleTools 8.4x, the Process Scheduler became a proper Tuxedo domain. By default, it is configured to run Application Engine server processes (PSAESRV). These are persistent Tuxedo server processes that handle Application Engine requests. This change was introduced to help CRM systems that typically run a very large number of Application Engine programs. Because the server process is persistent it saves the overhead of starting a new Application Engine process. An obvious side effect of this change in architecture is that if you shut the Process Scheduler down, it terminates the PSAESRV processes, and cancels any Application Engine program that is running.

You can choose to configure the Process Scheduler without PSAESRV processes, and it will start individual PSAE processes for each Application Engine request.

In previous versions of PeopleTools, it was possible to shut the Process Scheduler down and any running processes (except PSAESRVs) would continue to run. This is not the case in PeopleTools 8.48. On shutdown, the Process Scheduler attempts to cancel the PSAE process. If it is not successful the status of the process goes to Error, but the process continues to run.

PSPRCSRV.7150 (0) [...](3)    Server: PSUNX checking status...
PSPRCSRV.7150 (0) [...](3)    Server action mode: Ok (looking for requests)
PSPRCSRV.7150 (0) [...](3)    Server: PSUNX looking for work
PSPRCSRV.7150 (0) [...](3)    Checking Process cancels...
PSPRCSRV.7150 (0) [...](3)    Process 69 is still running as Session ID 7552
PSPRCSRV.7150 (0) [...](3)                 Application Engine       :           1:3
PSPRCSRV.7150 (0) [...](0)    Server is shutting down

So, the Process Scheduler knows that process 69 is still running but continues to shutdown

PSPRCSRV.1360 (0) [...](1) =================================Error===============================
PSPRCSRV.1360 (0) [...](1)    Process 69 is marked 'Initiated' or 'Processing' but can not detect status of PID
PSPRCSRV.1360 (0) [...](3)    Updating process instance's status to Error.
PSPRCSRV.1360 (0) [...](2)         Process Name: PSQUERY
PSPRCSRV.1360 (0) [...](2)         Process Type: Application Engine
PSPRCSRV.1360 (0) [...](2)         Session Id:   7552
PSPRCSRV.1360 (0) [...](2) =====================================================================
PSPRCSRV.1360 (0) [...](3)   Number of requests removed from Task Active List:  1
PSPRCSRV.1360 (0) [...](3)    Server action mode: Initializing

But when it starts up again it cannot detect the PID of the process, and so it marks the status as 3 (Error).

I'm not sure exactly when this change was introduced, but this is the first time I have encountered it.

Column default values in PeopleTools 8.48 DDL

PeopleSoft’s Mobile Synchronization Framework is designed to support off-line access to a subset of the PeopleSoft application. Application Designer has been able to generate database triggers to maintain Timestamp and System ID fields on tables that support synchronization of mobile clients since PeopleTools 8.44. In 8.48 it is now possible to set column defaults on character and numeric fields via Application Designer. However, this has potential for much wider application in PeopleSoft applications.

There are two new checkboxes on the record properties dialogue box.

If the User -> Server box is checked, Application Designer will add column defaults to the fields in the generated create and alter table DDL. As an example, I created the following record in Application Designer.



And this was the DDL that Application Designer generated.

CREATE TABLE PS_DMK (
   EMPLID VARCHAR2(11)  DEFAULT ' ' NOT NULL,
   EFFDT DATE,
   SEX VARCHAR2(1)  DEFAULT 'U' NOT NULL,
   ACTION_DT DATE  NOT NULL,
   ARRIVAL_TIME DATE  NOT NULL,
   ACTION_DTTM DATE,
   AA_PLAN_YR SMALLINT  DEFAULT 42 NOT NULL,
   ACCRUED_AMOUNT DECIMAL(15, 2)  DEFAULT 0 NOT NULL) 
… 

• All of the numeric and character columns now have default values, but none of the date columns have defaults.
• If a literal value is specified in Application Designer it is carried forward to the DDL, otherwise a character field defaults to a single space and a numeric field to zero
• Whether a field is required or not does not affect the default value,
It is a pity that the system variables to specify current date or time do not get converted to SYSDATE in the DDL, thus

…
   ACTION_DT DATE  DEFAULT TRUNC(SYSDATE) NOT NULL,
   ARRIVAL_TIME DATE DEFAULT SYSDATE NOT NULL,
…

I assume that this is because it is platform specific, but then so is the rest of the field list.

There is another problem to using the Up Sync check box. When checked, you are prevented you from making a field required. The unchecked box is greyed out, but fields that are already required remain so.

I think that the ability to specify column defaults could have advantages during batch processing. All numeric and character columns in PeopleSoft (with minor exceptions) are NOT NULL in the database. Thus, if a process is inserting a row into that table, it must provide a value for every mandatory column. Otherwise, you generate an Oracle error.

ORA-01400: cannot insert NULL into ("SYSADM"."table name"."field name")

Specifying a default value will prevent this error. It would also save developers from having to add this default values to their code, thus simplifying development and maintenance.

Many batch processes in PeopleSoft process working storage or reporting tables. Often they insert rows, filling in key and some other columns, and then update other columns later on. If these columns have defaults they do not need to be referenced in the insert statement.

When a new column is added to a table, it is necessary to find every place where a row is inserted, and add a value. Specifying a column default would save adding a space or zero to those inserts.