PeopleSoft Fine Grained Auditing – Part I

We all know how critical it is to enable Oracle Database Auditing for our production environment. It is equally important to monitor the audit results and take actions. Though enabling auditing using the AUDIT_TRAIL initialization parameter plus using the AUDIT statements to enable different auditing options is the common approach, with the availability of FGA feature, it is time to take the next step.
FGA allows us to audit more specific business rules. Today, I will walk you through the steps to implement FGA for PeopleSoft.

Ensure that EnableDBMonitoring is set to 1 in psappsrv.cfg. This will enable PeopleSoft to populate CLIENT_INFO column in V$SESSION.

Identify the table and the criteria that we need to set for the policy. In this example, I will use the custom table PS_ABC_COMPANY_TBL. I need to audit any SELECT* statements on PS_ABC_COMPANY_TBL when user selects data related to
abc_company = ‘ABC Confidential’

We need to create a procedure that will populate the CLIENT_INFO so that we can identify the OPRID.

CREATE OR REPLACE PROCEDURE GET_OPRID (OBJECT_SCHEMA VARCHAR2, OBJECT_NAME VARCHAR2, POLICY_NAME VARCHAR2)

AS

V_CLIENT_INFO VARCHAR2(1000);

V_OPRID VARCHAR2(32);

BEGIN

V_CLIENT_INFO := SYS_CONTEXT(‘USERENV’,'CLIENT_INFO’);

IF ( LENGTH(V_CLIENT_INFO) IS NULL ) THEN

V_OPRID := ‘NOOPRID’;

ELSIF ( SUBSTR(V_CLIENT_INFO,1,1) = ‘,’ ) THEN

V_OPRID := ‘NOOPRID’;

ELSE

V_OPRID := SUBSTR(V_CLIENT_INFO, 1, INSTR(V_CLIENT_INFO,’,',1)-1);

END IF;

DBMS_SESSION.SET_IDENTIFIER (V_OPRID);

END;

Create a policy as shown below

begin

dbms_fga.add_policy (

object_schema=>’SYSADM’,

object_name=>’PS_ABC_COMPANY_TBL’,

policy_name=>’ABC_COMPANY_TBL_ACCESS’,

audit_column => ‘ABC_COMPANY’,

audit_condition => ‘ABC_COMPANY = ”ABC Confidential”’,

handler_module => ‘GET_OPRID’

);

end;

That’s it!!

In my next post I will share the results of enabling this feature.

 

Read More About PeopleSoft Fine Grained Auditing

How to trace AE and determine which AE has trace enabled?

In this blog, I will answer two questions.

1. How to trace AE?
2.  

There are a few ways to trace an AE program. You can enable the trace using some of the below methods:

1. In the process scheduler configuration file psprcs.cfg
2. You can enable the trace in the“Process Definition”.
3. Grab the Oracle session information and generate SQL Trace
4.  

It is not advised to enable the trace using psprcs.cfg because it will enable trace for ALL AE programs using that scheduler. However, this might be the best choice in an environment where you want to trace multiple AE programs or you do not have access to modify the process definition. Exercise this option with caution knowing that it might generate trace files for all AE programs.

To enable trace using “Process Definition”, make the below change. You can use the “-TRACE” or “-TOOLSTRACESQL” or “-TOOLSTRACEPC” option depending on what information you require.

1.  
2. Which AE program has trace enabled in its process definition?
3.  

The below SQL will help you determine all the AE programs that have TRACE enabled. It will be useful to use this SQL in your monitoring scripts if you notice developers enabling trace using the process definitions and getting the program with its process definition migrated all the way to production or if your analyst or DBA have the tendency to enable the trace and forget about it.

SELECT PRCSNAME, PARMLIST

FROM PS_PRCSDEFN

WHERE UPPER(PARMLIST) LIKE ‘%TRACE%’

AND PRCSTYPE = ‘Application Engine’;

Read More About How To Trace AE

How to determine what is being processed?

As a DBA you might be receiving a lot of calls from users asking some of the questions listed below.

- What their process is currently executing?

- How long will it take?

- Should I cancel my process?

In this post, I will cover some steps that will help you quickly respond to the above questions.

I have executed the below SQR Report in my environment and want to know what is happening.

What their process is currently executing?

1.Use the below SQL to correctly identify the Oracle session

SELECT CLIENT_INFO, PROGRAM, SID, SERIAL#, SQL_ADDRESS FROM V$SESSION WHERE CLIENT_INFO LIKE ‘NPAI%’ AND STATUS =‘ACTIVE’;

2.The above results identify the session executing the report; in this case it is SID 32.

3.Use this SID to identify the SQL being executed.

SELECT SQL_TEXT FROM V$SQLTEXT WHERE ADDRESS = (SELECT SQL_ADDRESS FROM V$SESSION WHERE SID= 32) ORDER BY PIECE;

SQL_TEXT

—————————————————————-

SELECT A.RECNAME, A.FIELDNAME FROM PSRECFIELD A WHERE A.SUBRECO

RD <> ‘Y’ AND NOT EXISTS (SELECT ‘X’ FROM PSDBFIELD B WHERE B.FI

ELDNAME = A.FIELDNAME)

4.The above results will provide you the SQL that is currently being executed. Equipped with this information you SHOULD be able to dig deeper if the session continues to execute the same SQL for more than expected time. Some of the scenarios could be:

a.Locked rows

b.Bad SQL

c.Statistics not updated

d.Bad DB configuration – SQL waiting on some event

e.Missing index, etc

Note 1
Also, there are scenarios where the session might not be executing any SQL because it is busy executing the code logic within the program. If the program is written using good coding standards then you should be able to look at the log file to decipher the progress.

Note 2
It is possible that there are no Oracle sessions created by the process. Some of the scenarios are:
- Process is Queued
- Process has completed and is Posting, etc
How long will it take?In most scenarios the functional support personnel along with the developer should be able to respond to this question based on the SQL or results in log file that you have identified. If you have knowledge about this process based on its history and/or your ability to interpret the program then you can estimate the completion time.
Should I cancel my process?This decision will require approval from the business + functional support personnel unless you have knowledge about the impact of the process.

Read More About  what is being processed?

How to unleash the best of Oracle Features… Part I

In this series, I will cover some of the Oracle features that can be easily implemented to optimize our PeopleSoft environment.

Resumable Space

In todayworld, we will hardly find a database that is not monitored for space usage. But there is still a possibility for our process failing because it ran out of space in the UNDO tablespace or user defined tablespace or the DBA was too late to allocate the required space. Majority of PeopleSoft processes have restart capability. However, there might be instances where you just cannot afford to let the process fail and restart from the last commit/rollback step, example – data conversion during cutover.

The Resumable Space feature provides the facility to suspend transactions when they hit the space errors. The transaction will resume when the error is corrected.

Below are the steps to implement this feature for a SQR process.
1. Modify the SQR to include the following procedure which will be called at the start of the process.

begin-procedure SetResumable          ! Set RESUMABLE in current session

begin-SQL
ALTER SESSION ENABLE RESUMABLE;
end-SQL

end-procedure

2. Create an AFTER SUSPEND trigger to monitor any transactions which have been suspended

CREATE OR REPLACE TRIGGER resumable_alert_notifier
AFTER SUSPEND
ON DATABASE

BEGIN

– LOG ERROR
INSERT INTO RESUMABLE_ERR_LOG (
SELECT SQL_TEXT, ERROR_MSG, SUSPEND_TIME
FROM USER_RESUMABLE
WHERE SESSION_ID = (SELECT DISTINCT(SID) FROM V$MYSTAT));
– Send Email notification using UTL_SMTP
– Code not shown

END;

You can determine the best strategy (notification or timeout or abort or log error) when transaction is suspended by coding in the AFTER SUSPEND trigger.

Summary
Resumable Space is a cool feature and I recommend it be considered for implementation in your production environment to avoid business critical processes from failing when it encounters space issues. As with any implementation the mantra is “Understand > Plan > Implement in non-production db > Test > Test > Test > implement in production > relax”.
Read More About  Oracle Features

PeopleSoft Flashback…

Flashback query has been a boon from Oracle since its introduction in 9i. In a PeopleSoft environment there are many areas a DBA can utilize this feature. The commands are straightforward and will make your life easier if you remember them.

Consider a scenario where you are doing a data fix in production database. You have been extremely busy today and swamped with 4 high priority problem tickets. You maintain your composure and use your multitasking skills.

The Functional Consultant gave you the below SQL to perform the data fix.

DELETE FROM PS_ABC_VCH_APPR WHERE VOUCHER_ID IN (‘7865419’,’7865438’,’7865428’) AND ENTRY_STATUS IN (‘P’,’X’);

It will delete 12 rows and you confirm the same. You issue commit, close the problem ticket and start working on the next high priority ticket. Phew, one down and 3 more to go…

You get a phone call from the functional consultant 15 mins after you closed the data fix problem ticket. He wants to re-open the problem ticket and request a restore. The fix did not work and they have identified a workaround that can be performed online. He thanks you for taking care of this so quickly and requests you to call him when the data is restored. As you hang up, you begin to feel a little bit of nervousness. Did you or did you not take a backup? Then it strikes you that you forgot to take the backup in your rush to execute the ticket and assuming that the functional team rarely request restore!! Hmm…

In these scenarios, the below SQL will be a life saver!

INSERT INTO PS_ABC_VCH_APPR SELECT * FROM PS_ABC_VCH_APPR AS OF TIMESTAMP (SYSTIMESTAMP – INTERVAL ‘20’ MINUTE) WHERE VOUCHER_ID IN (‘7865419’,’7865438’,’7865428’) AND ENTRY_STATUS IN (‘P’,’X’)

The SQL above will insert into PS_ABC_VCH_APPR all rows that existed for the above criteria 20 minutes ago.

Or

INSERT INTO PS_ABC_VCH_APPR SELECT * FROM PS_ABC_VCH_APPR AS OF TIMESTAMP TO_TIMESTAMP(‘24-aug-07 14:35:00′,’DD-MON-YY HH24: MI: SS’)WHERE VOUCHER_ID IN (‘7865419’,’7865438’,’7865428’) AND ENTRY_STATUS IN (‘P’,’X’)

The SQL above will insert into PS_ABC_VCH_APPR all rows that existed for the above criteria at the date and time mentioned.

þ You get a call at 4am from the Help Desk, an incomplete migration yesterday evening has resulted in messing up the data after the custom SQR process ran and failed, the functional analyst is requesting an emergency restore of the database. He wants the database to be restored to a point in time just prior to running the SQR process, and complete the migration. Why does this always happen when you are on-call??

You know that restoring the database is going to take 3-4 hours and keep your finger crossed (hoping the hot backup and archive logs are valid).

Instead, you call the functional analyst, check out the custom SQR process and determine that the batch job only impacts 12 tables. You tell him that it is not too late and you will use the flashback feature to get back the data in those 12 tables to a state prior to execution of the batch job!! This has saved your client 3+hrs of downtime and we all know that time is $$$.

Note 1 – It is not recommended to use the flashback feature as a backup policy but as an option in emergency scenarios where you forgot to take a backup or the backup got corrupted.

Note 2 – Do not forget to read about the limitations. You can only flashback if your UNDO segments have this information. This is driven by the parameter UNDO_RETENTION and the fact that Oracle can only honor if enough undo space is available for active transactions.

Summary – Though we still do not have the capability to flashback in our real life (oh we wish we could go back in time and correct that one mistake we made in our life) but there are many scenarios in a real world PeopleSoft environment which can utilize the flashback feature. We just have to keep our mind open to the possibilities that this feature provides.