Reset Global Unique Identifier when cloning PeopleSoft databases

When cloning PeopleSoft databases there are a number of data values that need to be changed in the database to reflect the new database name. The most obvious is DBNAME on PS.PSDBOWNER. This maps the name of the databases, which on Oracle must also match the TNS Service Name, to the schema that contains the database.

However, a piece of data that is often forgotten is GUID on PSOPTIONS. It is documented in PeopleBooks, and is used by both the Performance Monitor and the Environmental Hub. GUID uniquely identifies a particular PeopleSoft system. PeopleSoft assigns a unique value, referred to as a GUID, to each PeopleSoft application installation. This value can't be customized.

When a Performance Monitoring agent registers with the PeopleSoft Performance Monitor, it provides this GUID. The first time the monitoring system receives information from a monitored system, it detects the GUID. For each new GUID detected, the monitoring system creates a new monitored system definition. Unless the specified monitor is changed, the new database will be monitored by the same instance of the PeopleSoft Performance Monitor that is monitoring the source database. The monitor assumes that the agents for both systems belong to the same system. Data for both systems will be mixed up, making it unreliable.

When an Environment Management agent notifies the hub that it has found a manageable component belonging to an environment, if the GUID of the environment is not recognized, the hub creates a new environment representation. Otherwise the hub will assume that the two environments are the same, leading to confusion.

To resolve these problems, set the value of the GUID field in the PSOPTIONS table to a single space in the new copy database. The next time an application server connects to the database, the system generates a new, unique GUID. You can insert the blank value in the PSOPTIONS table using the SQL tool at your site.

Use of Windows Service Dependency in PeopleSoft

If you run the PeoleSoft Application Server on Windows, there
are advantages to configuring the domains to run as services.
Processes do not run in DOS windows, where they can accidentally
be shut down. They can run with the privileges for a different NT
user. Even if you don't want services to start automatically with
server, it can simply startup scripts to simply use the NET START/STOP
command. Then you might want to define interdependencies between
the services to make sure that dependant services are started in
the right order. Service dependencies are specified by adding the
string value DependOnService to the registry key for the
service in HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\
(see Microsoft support note 193888).


The services that start either the application server or process schuler should be made dependent upon the BEA ProcMGR V8.1 (Tuxedo IPC Helper) service, otherwise the domain startup will fail. I have made the PeopleSoft service dependent on both the Oracle database service and the IPC process by making DependOnService a multi-string value.


The Dependencies tab of the service properties window shows which services this service depends upon, and which services depend on this service.


When the PeopleSoft service was made dependant upon the BEA ProcMgr service, the BEA ProcMgr service also reports that the PeopleSoft service depends upon it.


Now, when the PeopleSoft process is started, it will also start the BEA ProcMgr process if it is not already started. Shutting down the BEA ProcMgr service will also cause the PeopleSoft process to shutdown first.

Retrieving Oracle trace files via an external table

December 21st 2006: Correction to script

When talking about performance tuning, I will often tell developers that they need to use Oracle SQL*Trace and TKPROF because that is an absolutely reliable way to find out how their SQL executed, how long it took and hence where they should focus their attention to improve performance. However, that requires access to the trace files in the USER_DUMP_DEST directory on the database server, which is something that database administrators are justifiably reticent to provide. When I am on a customer site, I often experience the same difficulties.

Even if developers can get a log on to a development database server, trace files will only be readable on Unix servers within the DBA group unless _trace_file_public is set to true.

Another option is to retrieve trace files from the user_dump_dest directory via an external table. The idea is not mine. I've seen this technique demonstrated by Tanel Poder, there is an article by Jared Still that demonstrates how to retrieve the alert log, and there is also an posting on Ask Tom using utl_dir. However, I couldn't find a script to do trace files, so I wrote one.

The script assumes the trace file is created by the current session. The first thing it does is to create a database directory that maps to the USER_DUMP_DEST directory, and an external table that corresponds to the trace file. Then you can just query the trace file in SQL*PLus and spool the output to a local file.

Of course, this also shows that external tables are a significant security risk. The privilege to create and read from directories carefully controlled.

REM user_dump_dest.sql
REM (c) Go-Faster Consultancy Ltd.
REM 30.11.2006 initial version

REM trace the thing you are interested in.
ALTER SESSION SET tracefile_identifier = 'gfctest';
ALTER SESSION SET sql_trace = true;
SELECT * FROM dual;
ALTER SESSION SET sql_Trace = false;

REM determine path for user_dump_dest and create an database directory
set echo off feedback off verify on timi off
column dir new_value dir format a18
column path new_value path format a60
SELECT name dir, value path
FROM v$parameter
WHERE name = 'user_dump_dest'
/
CREATE OR REPLACE DIRECTORY &dir AS '&path';

REM determine the name of the trace file from show process ID, and database name and parameters
column tracefile_name new_value tracefile_name
SELECT LOWER(d.name)||'_ora_'||p.spid
||DECODE(p.value,'','','_'||value) tracefile_name
FROM v$parameter p, v$database d, sys.v_$session s, sys.v_$process p
,(SELECT sid FROM v$mystat WHERE rownum=1) m
WHERE p.name = 'tracefile_identifier'
AND s.paddr = p.addr
AND s.sid = m.sid
/

REM create an external table that corresponds to the trace file
DROP TABLE &tracefile_name;
CREATE TABLE &tracefile_name
(trace_line VARCHAR2(4000))
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER DEFAULT DIRECTORY user_dump_dest
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE NOBADFILE NODISCARDFILE NOLOGFILE
FIELDS MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(trace_line CHAR(4000)))
LOCATION ('&tracefile_name..trc')
);

REM just query the trace file back to a local spool file in SQL Plus
set head off pages 0 termout off
spool &tracefile_name..trc
SELECT * FROM &tracefile_name;
spool off
set termout on
DROP TABLE &tracefile_name;

Truncating a Table does not affect the Materialized View Log

I am working on a site that is replicating tables between databases using Materialized Views. I have realised that if a replicated table is truncated on the source database, that the rows remain in the materialized view on the target until a complete refresh is performed. Worse, if ROWID based Materialized View is used, then the fast refresh process will error with 'ORA-12034: materialized view log on "SYSADM"."T_R" younger than last refresh'. In PeopleSoft, there are no primary keys, and it is not possible to build them if any of the key columns are nullable, which is the case with non-required date fields in PeopleSoft. Here is an example. I will create two tables, and replicate them with Materialized Views. One by primary key, and the other by ROWID.

CREATE TABLE t_pk
(a NUMBER
,b VARCHAR2(20)
,CONSTRAINT t PRIMARY KEY(a));

CREATE TABLE t_r
(a NUMBER
,b VARCHAR2(20));

CREATE MATERIALIZED VIEW LOG ON t_pk WITH PRIMARY KEY;
CREATE MATERIALIZED VIEW t_pk_mv REFRESH FAST WITH PRIMARY KEY
AS SELECT * FROM t_pk ;

CREATE MATERIALIZED VIEW LOG ON t_r WITH ROWID;
CREATE MATERIALIZED VIEW t_r_mv REFRESH FAST WITH ROWID
AS SELECT * FROM t_r ;

INSERT INTO t_pk VALUES(1,'Old');
INSERT INTO t_pk VALUES(2,'Old');
INSERT INTO t_r VALUES(1,'Old');
INSERT INTO t_r VALUES(2,'Old');

BEGIN dbms_mview.refresh(list => 'T_PK_MV', method => 'f'); END;
BEGIN dbms_mview.refresh(list => 'T_R_MV', method => 'f'); END;

SELECT * FROM t_pk_mv;
A B
- ---
1 Old
2 Old

SELECT * FROM t_r_mv;
A B
- ---
1 Old
2 Old

So the data has replicated and everything seems to working fine. Now lets truncate the source tables, and put some new data in. One row has the same key value, one does not.

TRUNCATE TABLE t_pk;
TRUNCATE TABLE t_r;

INSERT INTO t_pk VALUES(2,'New');
INSERT INTO t_pk VALUES(3,'New');
INSERT INTO t_r VALUES(2,'New');
INSERT INTO t_r VALUES(3,'New');

The fast refresh of the Materialized View with the primary key appears to work. The new rows are inserted into the Materialized View, replacing existing rows with the same key values, but the old rows remain.

BEGIN dbms_mview.refresh(list => 'T_PK_MV', method => 'f'); END;
PL/SQL procedure successfully completed.

SELECT * FROM t_pk;
A B
- ---
2 New
3 New

SELECT * FROM t_pk_mv;
A B
- ---
1 Old
2 New
3 New

The fast refresh of the Materialized View by ROWID fails, and the old data remains in place.

BEGIN dbms_mview.refresh(list => 'T_R_MV', method => 'f'); END;
*
ERROR at line 1:
ORA-12034: materialized view log on "SYSADM"."T_R" younger than last refresh
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 803
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 860
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 841
ORA-06512: at line 1

But rows 1 & 2 are in MV but not source table

SELECT * FROM t_r;
A B
- ---
2 New
3 New

SELECT * FROM t_r_mv;
A B
- ---
1 Old
2 Old

However, a full refresh corrects the discrepancy

BEGIN dbms_mview.refresh(list => 'T_PK_MV', method => 'c'); END;
BEGIN dbms_mview.refresh(list => 'T_R_MV', method => 'c'); END;

SELECT * FROM t_pk_mv;
A B
- ---
2 New
3 New

SELECT * FROM t_r_mv;
A B
- ---
2 New
3 New

It seems to me, that it would be appropriate to prevent a Truncate command executing if there is a Materialized View Log on the table. I have written such a trigger

CREATE OR REPLACE TRIGGER mvtrunc_lock
BEFORE TRUNCATE
ON SYSADM.SCHEMA
DECLARE
 e_generate_message EXCEPTION;
 l_recname  VARCHAR2(15 CHAR);
 l_msg      VARCHAR2(100 CHAR) := 'No Message.';
 l_msg2     VARCHAR2(100 CHAR) := 'Cannot '||ora_sysevent
   ||' '||lower(ora_dict_obj_type)
   ||' '||ora_dict_obj_owner||'.'||ora_dict_obj_name||'.  ';

 sql_text ora_name_list_t;
 l_sql_stmt VARCHAR2(1000 CHAR) := '';
 n          INTEGER;
 i          INTEGER;

BEGIN
 /*extract the originating SQL statement into a string variable*/
 n := ora_sql_txt(sql_text);
 FOR i IN 1..n LOOP
  l_sql_stmt := SUBSTR(l_sql_stmt || sql_text(i),1,1000);
 END LOOP;

 IF ora_dict_obj_type = 'TABLE' AND 
    ora_sysevent = 'TRUNCATE' THEN

  BEGIN /*if a materialized view log exists*/
   SELECT 'There is a materialized view log.'
   INTO   l_msg
   FROM   all_mview_logs l
   WHERE  ROWNUM = 1
   AND    l.master = ora_dict_obj_name
   AND    l.log_owner = ora_dict_obj_owner
   ;
   RAISE e_generate_message;
  EXCEPTION
   WHEN NO_DATA_FOUND THEN NULL;
  END;
 END IF;

 EXCEPTION
  WHEN NO_DATA_FOUND THEN NULL;
  WHEN e_generate_message THEN
   RAISE_APPLICATION_ERROR(-20042,
     'MVTRUNC_LOCK:'||l_msg2||l_msg||CHR(10)||'SQL:'||l_sql_stmt);
END;
/

show errors

The trigger MVTRUNC_LOCK can be downloaded from the Go-Faster website.

DDL Triggers to prevent loss of database objects not managed by PeopleTools

Sometimes you have to certain database techniques or create database objects on tables that
are maintained by PeopleTools, but which themselves are not maintained by PeopleTools. This is often as a result of performance tuning activities where you choose to use features of the Oracle database that PeopleSoft do not use because they are not available on other database platforms.

• Function-Based Indexes: In Oracle, it is possible to implement an index on a function. A typical example would be an index on UPPER(NAME) on PS_NAMES to facilitate case
  insensitive searching.
• Record-based auditing can be done with a database DML trigger, instead of the default functionality of the Application Server. This was implemented by PeopleSoft to improve performance of the auditing and is a rare example of PeopleSoft coding specific code for each platform because the trigger DDL is slightly different.

However, these objects are not created by the DDL scripts built by the Application Designer, and can accidentally be lost when the table is altered with an 'alter by recname' script generated by Application Designer. The implications can be quite serious. If the auditing trigger were to be lost, then the application would continue to run, but no audit data would be produced, and no error would be raised.
In Oracle, it is possible to build DDL triggers. Just as DML triggers fire when the data is changed, DDL triggers fire when particular DDL commands are issued. I have created a trigger called PSFT_DDL_LOCK (available from the Go-Faster psscripts github repository) that fires when an object is altered or dropped. In certain cases the trigger will raise an error, this causes the original DDL command to fail, and thus prevents loss of the unmanaged objects. If the table related to the object being dropped or altered is not managed by PeopleSoft (if it can't be found in PSRECDEFN), the trigger does not raise any error. Otherwise,

• If a trigger is being dropped or altered, and the name of that trigger does not start with PSU, then an error is raised. Triggers that are named PSU% are created by Application Designer for use with Mobile Agents If an index is dropped or altered, the trigger checks that it is defined in PeopleTools. Indexes that correspond to Unique, Duplicate and Alternate Keys in Application Designer (where the index name is 'PS', followed by either a digit or an underscore, followed by the record name) are ignored.
• If a table is dropped or altered, the DDL trigger checks that there are no user indexes or triggers not defined in PeopleSoft, nor any primary key constraints, materialized views or materialized view logs on the table. It also checks that the table or index is not partitioned, clustered, global temporary or index organised.

If any of the tests fail, then the trigger raises an exception the DDL statement fails with an error message generated in the trigger. The SQL that generated the error is also included in the error text.
When an error is generated by this trigger during development or migration activities, it usually indicates that there is another database object that you need to consider before issuing the command that errorred. It is not simply a matter of disabling the trigger and trying again.
The trigger does have a couple of side effects.

• There are several SQL statements that are run in the trigger, and this does impact the performance of DDL commands. If you are dropping all the tables in a schema, then it would be advisable to disable the trigger. The trigger definitely needs the following function-based index to be created on PSRECDEFN because it needs to look up the PeopleSoft record from the table name.

CREATE INDEX pszpsrecdefn
ON psrecdefn (DECODE(sqltablename,' ','PS_'recname,sqltablename))
TABLESPACE PSINDEX PCTFREE 0;

• If a user index is removed from the PeopleTools definition before it is dropped, the trigger will raise an error. However, in such cases it would be better to keep the definition of the index in Application Designer and set the platform radio button to 'None' so that PeopleTools does not build it. That way a comment can be preserved to explain why the index is no longer necessary.

Updated 2.6.2011: The checking behaviour trigger can be disabled for just the current session by calling this packaged procedure that sets a package global variable which is read by a function called by the trigger. For the one session where this is done, the checks are not performed by trigger are disabled:
execute psft_ddl_lock.set_ddl_permitted(TRUE);

The behaviour can be reenabled like this:
execute psft_ddl_lock.set_ddl_permitted(FALSE);

The previous version of this trigger (called T_LOCK) did not have this capability, so I suggested disabling the trigger, but this affected all sessions.