Friday, August 29, 2014

About Erman Arslan 's Oracle Forum

I have added an Oracle Forum and embedded in to this blog.
Both anonymous and registered users can create a new topic or reply to an existing topic.
Ofcourse, you can register and login to this forum for following the discussions by emails.
The idea is to establish an environment for information exchange and to build a context for offering suggestions for your problems and questions.
Currently we have 8 Sub categories , classified by Product & Topics.
We may be increase the topics over time.. I will be checking for the new questions frequently..


EBS 12.2 -- Notification Mailer Outbound/Inbound & SSL problem diagnostics + a real life example

Notification mailer is a key workflow component for both sending emails and processing emails sitting on the  INBOX of the associated Imap account..
Notification mailer is a java program running on the concurrent tier.. Note that : Notification Mailer in the old 11i relaeses was based on C.. In 12.2, we are continuing to use java..
It is running from a a bundled jdk32 as a Service component in Oracle Applications GSM type.
You can see its running from the operating system command line;

[applmgr@ermanprod config]$  ps -ef |grep -i fndcp 
->applmgr   8094  7944  0 Aug23 ?        00:03:14 /apps/fs2/EBSapps/comn/util/jdk32/bin/java -Dxdo.xliff.source=EBS -DCLIENT_PROCESSID=8094 -Dhttp.proxyHost= -Dhttp.proxyPort= -Dhttp.nonProxyHosts= -Djava.awt.headless=true -server -Xmx384m -Doracle.apps.fnd.common.Pool.leak.mode=stderr:off -verbose:gc -Ddbcfile=/apps/fs2/inst/apps/dberp_erpprod/appl/fnd/12.0.0/secure/dberp.dbc -Dcpid=8845 -Dconc_queue_id=1139 -Dqueue_appl_id=0 -Dlogfile=/apps/fs_ne/inst/dberp_erpprod/logs/appl/conc/log/FNDCPGSC8845.txt -DLONG_RUNNING_JVM=true -DOVERRIDE_DBC=true -DFND_JDBC_BUFFER_MIN=1 -DFND_JDBC_BUFFER_MAX=2 oracle.apps.fnd.cp.gsm.GSMSvcComponentContainer  --> "This is the Workflow Agent Listener Service"
->applmgr   8109  7944  0 Aug23 ?        00:03:05 /apps/fs2/EBSapps/comn/util/jdk32/bin/java -Dxdo.xliff.source=EBS -DCLIENT_PROCESSID=8109 -Dhttp.proxyHost= -Dhttp.proxyPort= -Dhttp.nonProxyHosts= -Djava.awt.headless=true -server -Xmx384m -Doracle.apps.fnd.common.Pool.leak.mode=stderr:off -verbose:gc -Ddbcfile=/apps/fs2/inst/apps/dberp_erpprod/appl/fnd/12.0.0/secure/dberp.dbc -Dcpid=8846 -Dconc_queue_id=1259 -Dqueue_appl_id=0 -Dlogfile=/apps/fs_ne/inst/dberp_erpprod/logs/appl/conc/log/FNDCPGSC8846.txt -DLONG_RUNNING_JVM=true -DOVERRIDE_DBC=true -DFND_JDBC_BUFFER_MIN=1 -DFND_JDBC_BUFFER_MAX=2 oracle.apps.fnd.cp.gsm.GSMSvcComponentContainer  --> "This is the Workflow Document Web Services Service"
->applmgr   8128  7944  0 Aug23 ?        00:08:12 /apps/fs2/EBSapps/comn/util/jdk32/bin/java -Dxdo.xliff.source=EBS -DCLIENT_PROCESSID=8128 -Dhttp.proxyHost= -Dhttp.proxyPort= -Dhttp.nonProxyHosts= -Djava.awt.headless=true -server -Xmx384m -Doracle.apps.fnd.common.Pool.leak.mode=stderr:off -verbose:gc -Ddbcfile=/apps/fs2/inst/apps/dberp_erpprod/appl/fnd/12.0.0/secure/dberp.dbc -Dcpid=8847 -Dconc_queue_id=1140 -Dqueue_appl_id=0 -Dlogfile=/apps/fs_ne/inst/dberp_erpprod/logs/appl/conc/log/FNDCPGSC8847.txt -DLONG_RUNNING_JVM=true -DOVERRIDE_DBC=true -DFND_JDBC_BUFFER_MIN=1 -DFND_JDBC_BUFFER_MAX=2 oracle.apps.fnd.cp.gsm.GSMSvcComponentContainer  -> "This it the Notification Mailer"

As you see the command line are almost the same for all the 3 processes. So the difference in their behavior must be based on their control queues..

Anyways;

Notification mailer services are running as concurrent managers at the same time.
Oracle Workflow provides three predefined containers in which you can create components, the Workflow Mailer Service, the Workflow Agent Listener Service, and the Workflow Document Web Services Service.



                              

Okay lets analyze the diagram above;

So;

We use OAM to control Notification mailer.
OAM uses control queues to communicate with GSM container.
Notification Mailer(a GSM process) is controlled by the GSM container using the messages that it degueues from the Control Queue..
GSM Process monitor delivers the control events to OAM using OAM..So, It is working in the opposite direction..
Notification Mailer is managed by the concurrent tier.
Concurrent Manager spawns a GSM container and GSM container invokes Notification Mailer Service, which is referred as a GSM process -- an instance of the GSM..




You can see the concurrent manager information using the query below ;


SELECT concurrent_queue_name manager, fcp.last_update_date, fcp.concurrent_process_id pid, meaning, fcp.logfile_name  
FROM fnd_concurrent_queues fcq, fnd_concurrent_processes fcp, fnd_lookups flkup  
WHERE concurrent_queue_name in ('WFMLRSVC', 'WFALSNRSVC')  
AND fcq.concurrent_queue_id = fcp.concurrent_queue_id  
AND fcq.application_id = fcp.queue_application_id  
AND flkup.lookup_code=fcp.process_status_code  
AND lookup_type ='CP_PROCESS_STATUS_CODE' 
AND meaning='Active'




Also you can check Administer Concurrent Manager form to check the situation of the concurrent managers related to Notification mailer.



Okay, after the introduction. Lets talk about the duties of Notification mailers briefly.
As you know, Mailer has Outbound and Inbound capabilities. Outbound is the side for the smtp operations and the Inbound has the responsibility of the Imap operations..
In the Smtp side, we can configure our mailer to use an account based authentication to login to our SMTP server.. Also, we can use a relay based authentication , which will not require mailer to pass user/pass information..
On the other hand, we must have a dedicated account in the IMAP side.. Mailer must connect to the imap server with its account and check the INBOX for the incoming messages that may require an action to be taken..

So how does it work?
Lets explore the smtp and imap processing of the smtp server a little more;

The flow diagram of smtp activities related with the Notification Mailer is as follows;


  • Workflow process initiates a request for a notification (an insert into WF_NOTIFICATIONS)
  • Request for a notification is enqueued onto the WF_DEFERRED queue
  • Notification Mailer Agent Listener picks up the message off the WF_DEFERRED queue for processing. (processed messages will appear on WF_NOTIFICATION_OUT or WF_ERROR)
  • The notification request is processed and the notification XML has been generated and enqueued to the WF_NOTIFICATION_OUT queue.
  • The notification mailer dequeues the XML notification for dispatch.
  • The notification mailer transforms the XML into a MIME message and dispatches the message through the SMTP server..
  • "Content that contains Oracle Applications Framework regions is obtained from the Application Server" --> An error  encountered in this stage, is the main reason makes me writing this post bytheway :)
  • The message is dispatched to the SMTP server for delivery to the recipient(s)
  • The SMTP Server forwards the message on to the wider network (LAN, WAN or Internet)
  • The message is delivered to the users email server
  • The user receives the email.

The flow diagram of imap activities related with the Notification Mailer is as follows;


  • User response to a notification through email
  • Email Server forwards the message to the LAN/WAN
  • The message is received by the IMAP server
  • Notification mailer retrieves the message from the mail store
  • The mailer will check the status of a notification to determine if it needs to actually process the response any further
  • The response is parsed and transformed into an XML document
  • The response, now encoded in XML is enqueued to the WF_NOTIFICATION_IN queue
  • The Workflow Inbound Notification Agent Listener picks up the reponse for processing.
  • The response attributes are applied to the notification activity and the response is processed by the Workflow Engine

As you see above, there is a complex process there.. Notification mailer, agent listeners and workflow engines play roles on these activities. Note that : Both outbound and inbound can work with ssl.
Notification mailer is cabaple of speaking both ssl-enabled smtp and ssl-enabled imap..
Mailer interacts with the Mail Server using the standard smtp and imap commands in the backend.
Mailer makes some http/https calls during its process cycle.. (Look above .. SMTP flow ->  Content that contains Oracle Applications Framework regions is obtained from the Application Server)
So in other words; when the outbound needs to process OAF region, it obtains the content at a URL...
As I mention above, the flow is a complex one, and problems may be encountered in any step.. Obtaining the content using htttp/https is one these steps, which makes me writing this post actually. This post would be only about a certificate error encountered during the https call of the notification mailer, but I just couldnt stop myself from giving a detailed introduction about the underlying technology .. :)

Okay, lets take a closer look to the http/https activities of the Notification Mailer..
When there is a need to embed Oracle Applications Framework reqions in to the notification emails, Notification mailer's outbound connects to the Web Server of EBS from its web url ( hostname:port) and generates the content..
The host and port of the Webserver is derived from the profile option named : Workflow Mailer Framework Web Agent
The profile option may be set in the form of http://erman.domain.com:8000 or if EBS is SSL enabled then - > https://erman.domain.com:8000
Note that we can still set an http url even if our EBS is SSL enabled.. We can set an http url as long as our web server's http port is open..
So, notification mailer reads the value of this profile option and requests the contents using a url like this : https://erman.domain.com:8000/OA_HTML/OA.jsp?page=/oracle/apps/fnd/wf/worklist/webui/NotifMailerPG&WFRegion=NtfDetail&NtfId=4428&dbc=dberp&OARF=email&language_code=TR&oas=nl-v1pXd61XlMlUZJkG3gw..

and sometimes it may encounter errors in this step!!
When notification mailer encounters an error in this step, the details will be written to the logfiles.. (Hint . use statement level logging for diagnosing notification mailer problems)
The error can be seen in this step is as follows;

Problem getting the HTML content -> oracle.apps.fnd.wf.mailer.NotificationFormatter$FormatterSAXException: Problem obtaining the HTML content -> oracle.apps.fnd.wf.common.HTTPClientException: Unable to invoke method HTTPClient.HTTPConnection.Get caused by: javax.net.ssl.SSLHandshakeException: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target

This error may be seen in  a non-ssl SMTP configured Workflow Mailer, because it is actually encountered when notification mailer reaches the WEB Server.. 
On the other hand, we can directly say that  the associated Web Server is SSL-enabled...

--Note that : I find the diagnostic process we did for fixing this error very informative , and that 's why choosed to focus on this error in this post..



So, by looking the error for the first time , we can guess that there is ssl certificate problem in our system.. It may be in the Web Tier, or it may be related with the Notification mailer itself..
In this case, as we have EBS up&running and as we have no problems at all ( no browser side certificate errors or warnings) , we can narrow down the problem in Notification mailer's code or its configuration..

In this manner, we must follow a set of diagnostic actions..

The error that we must concentrate on is the following;

oracle.apps.fnd.wf.mailer.NotificationFormatter$FormatterSAXException: Problem obtaining the HTML content -> oracle.apps.fnd.wf.common.HTTPClientException: Unable to invoke method HTTPClient.HTTPConnection.Get caused by: javax.net.ssl.SSLHandshakeException: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target)
Especially - > unable to find valid certification path to requested target

The question came to my mind was, Is notification mailer in EBS 12.2 cabaple of raching web agent from ssl port? But, now I can say that it is quite cabaple..

Note that:

SSL in EBS was implemented using : Enabling SSL in Oracle E-Business Suite Release 12.2 ( Doc ID 1367293.1 )
So we suppose everyting in that document was performed properly..

Diagnostic :

1)   
Find JDK TOPs that EBS components are using.
grep -i jdktop $CONTEXT_FILE

Use keytool to analyze the cacerts files located in JDK_TOP
For Ex: 
keytool -list -v -keystore /apps/fs2/EBSapps/comn/util/jdk32/jre/lib/security/cacerts -storepass changeit > cacertslist32.txt
keytool -list -v -keystore /apps/fs2/EBSapps/comn/util/jdk64/jre/lib/security/cacerts -storepass changeit > cacertslist64.txt

If you are missing any certificates in one of the cacerts files, import it and retest the issue.

2)
Find Notification mailer programs.. See that they are running.
ps -ef |grep -i fndcp

If notificaiton mailer is not running, start it.

3) 
Check connection and show the certificates used for connecting to the webserver with open_ssl..
openssl s_client -connect ermanhost.domain.com:6530 -showcerts

4)
Analyze the notification mailer's log file, and find a nid that is failing..
Then run wfmlrdbg script for that notification id.
How to Run Script wfmlrdbg.sql and Collect its Output ? ( Doc ID 1364300.1 )
Check the errors in the output (if there are any).
If there is no error in the wfmlrdbg's output then, analyze the configured values for notification mailer.. Especially, the title "GSC WF_MAILER Component Parameters.."
Look for the value of MAILER_SSL_TRUSTSTORE parameter.. 
Is it set to the cacerts files that stores you web servers certificates ? 
If it is pointing to a different keystore , then  change that parameter and make it pointing to one of your cacerts files, or import your missing certificates in to the keystore that your notification mailer is configured to use.. 
(use $FND_TOP/sql/afsvcpup.sql if you want to change the keystore)
(use keytool if you want to import the missing certificates to the configured keystore . 
For Ex: keytool -import -alias Root -keystore yournewkeystore.jks -trustcacerts -file root.cer)
Once you are done, Restart application services (especially concurrent manager) and restart.

5)
Access your main web page https://<host.domain:port>.
Double-click on the padlock at the bottom of the page to view the Certificates.
If there is no padlock, then on the top toolbar:
select File->Properties->Certificates
Select the Certification Path tab and:
click on the first line and then View Certificate. - This will be the certificate for the root Certifying Authority (CA).
On Details tab click Copy to File, this will start the export wizard.
Click Next to continue.
Select Base-64 encoded X.509 (.CER) and click next.
Enter ca1 as the name and click ok to export the certificate.
epeat steps a thru e for each line on the Certification Path tab incrementing the file name each time by 1, i.e. ca2, ca3..
Analyze the certificates that exported above.. Are there missing certificates?
We should not expect any missing certificates, as  we implement SSL based on the Oracle Support document, but we check anyways.. just in case..

6)
Check the file : <10.1.2 ORACLE_HOME>/sysman/config/b64InternetCertificate.txt
It is a file stores certificates in Base64 format.
Your root and intermediate certificate must be imported to that txt file..
We should not expect any missing certificates in this file too, as  we implement SSL based on the Oracle Support document.. Anyways, we check b64InternetCertificate.txt just to be sure..
If some certificates are missing , import them.. 
For ex:
cat ca.crt >> <10.1.2 ORACLE_HOME>/sysman/config/b64InternetCertificate.txt
cat intca.crt >> <10.1.2 ORACLE_HOME>/sysman/config/b64InternetCertificate.txt

7)
Check the agent profiles and check the node names..

select p.profile_option_name,v.profile_option_value,
decode(v.level_id,
10001,'SITE',
10002, (select 'App:'||a.application_short_name from fnd_application a
where a.application_id = v.level_value),
10003, (select 'Resp:'||f.RESPONSIBILITY_KEY from fnd_responsibility f
where f.responsibility_id = v.level_value),
10004, (select 'User:'||u.user_name from fnd_user u
where u.user_id = v.level_value),
10005, (select 'Server:'||n.node_name from fnd_nodes n
where n.node_id = v.level_value),
10006, (select 'Org:'||org.name from hr_operating_units org
where org.name = v.level_value),
'NOT SET') PROF_LEVEL
from
fnd_profile_options p,
fnd_profile_option_values v
where
p.profile_option_id = v.profile_option_id (+)
and p.application_id = v.application_id (+)
and p.profile_option_name in ('WF_MAIL_WEB_AGENT','APPS_FRAMEWORK_AGENT','ICX_FORMS_LAUNCHER');
select substr(node_name, 1, 20) node_name, server_address, substr(host, 1, 15) host,
substr(domain, 1, 20) domain, substr(support_cp, 1, 3) cp, substr(support_web, 1, 3) web,
substr(SUPPORT_DB, 1, 3) db, substr(VIRTUAL_IP, 1, 30) virtual_ip from fnd_nodes; 

****

Okay we have seen almost all the diagnostics related with this error.
So these diagnostics should will take us to the solution ...
Lastly, I will mention a real life example, that I have faced recently ..

Environment:
It was a 12.2 EBS, running on Redhat Linux 6..
EBS was working with SSL. (https)
Notification mailer's outbound was working without SSL. (SMTP server was non-ssl)
Notification mailer's inbound was working with SSL.  (IMAP server was ssl enabled)

Problem:
There were errors in the notification mailers log file:
oracle.apps.fnd.wf.common.HTTPClientException: Unable to invoke method HTTPClient.HTTPConnection.Get caused by: javax.net.ssl.SSLHandshakeException: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target

First impression:
Thought that the IMAP was the problem.. Because we had a ssl enabled IMAP..
Then, saw that the problem lines were created by the outbound and also realized that this EBS was SSL enabled. So there should be some errors encountered during the way while Notification mailer's outbound process was reaching the web agent using https.

Workaround:
I set WF_MAIL_WEB_AGENT profile to "http://ermanhost.domain.com.tr:8000
(originally it was set to "https://ermanhost.domain.com.tr:6530)
This action made the NTF mailer to reach the web agent through http, and the problem dissapeared.
But this was a workaround not the fix.. (what will happen when the customer decide to close http port on the EBS server?)

Diagnostics:
Checked cacerts files
Checked mailers log file
Checked b64InternetCertificate.txt
Checked SSL actions which were taken while implementing SSL with EBS 12.2.
Checked SSL actions which were taken while implementing SSL on IMAP in Notification Mailer -- EBS 12.2.
Checked wfmlrdbg.sql output ->> found the problem in here.. The mailer was using /home/applmgr/.keystore file as the keystore.. (actually it should use the cacerts file located in the jdk top)

Cause:
SSL implementation was done firstly. Immediately afterwards, IMAP-SSL was enabled.
SSL implementation document states using cacerts file, but while implemeting IMAP-SSL a .keystore file was created and workflow notification mailer' keystore was set to .keystore file..
Because .keystore file does not include the server certificates, notification mailer can not reach the ssl enabled EBS web agent. On the other hand, as .keystore file includes IMAP server's ssl certificate, Notification mailer inbound could run without any problems..

Fix:
Alternative 1)
Import the missing server,ca and intermediate certificates in to .keystore file using keytool -import
Alternative 2)
Change workflow mailer's keystore parameter to " the cacerts file located in the jdk top"
But then, Inbound will not able to work properly, because this time cacerts file does not include IMAP server's ssl certificate .. That's why import IMAP server's ssl certificate in to the cacerts file , as well.

That's all about the notification mailer. I think , I will not write anything about Notification Mailer for a while , as I feel myself oversaturated with it.. :)
I hope you find this document useful.

Tuesday, August 26, 2014

RDBMS -- Undo tablespace in another aspect, sizing undo, undo_retention

Undo space become a throuble sometimes, especially for crowded databases..
We configure and control undo space using Undo tablespace size and Undo_retention parameter generally. That is , we size our undo according to the needs of our databsae sessions and monitor the usage..
When setting those parameters, we also concentrate on the Flashback needs of our database. In order to retain the undo needed for being able to flashback our database objects to a point in time, we need to adjust the undo parameters in a way that almost guarantees our flashback data to be there when we need it.
Note that , there are also ORA-01555 snaphot too old error may be waiting for us , if the adjustments are not done properly..So we need to know our database character very well, we need to analyze statistics  and then set those parameters to the optimal values that we gather from our analysis. (Note : the undo information for committed transactions can be overwritten , it is not related with that..)
Note that : We have seen and learned that ORA-01555 errors mostly caused by long queries which try to get overwritten--older images of changed blocks.. As these images are overwritten due to undo management mechanism, those queries have returned ORA-01555 errors. Also consider the unsual ORa-01555 scenarios : http://ermanarslan.blogspot.com.tr/2014/05/rdbms-unusual-ora-01555.html

There are script to find the optimal undo retention; like the following;
SELECT 

       ROUND((erm.undo_size / (to_number(f.value) *
g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"
  FROM (
       SELECT SUM(a.bytes) undo_size
          FROM v$datafile a,
               v$tablespace b,
               dba_tablespaces c
         WHERE c.contents = 'UNDO'
           AND c.status = 'ONLINE'
           AND b.name = c.tablespace_name
           AND a.ts# = b.ts#
       ) erm,
       v$parameter e,
       v$parameter f,
       (
       SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
              undo_block_per_sec
         FROM v$undostat
       ) g
WHERE e.name = 'undo_retention'
  AND f.name = 'db_block_size'
  
As you see above, it takes db_block_size and multiplies it with undo block per seconds to find the actual undo size that is produced in a second..
Then it divides undo size to the undo size per second to find the optimal undo retention value..
Actually, it finds optimal undo retention according to the current undo tablespace size..
It makes sense right? It finds generated undo in a sec, and then divides undo size to this value to find how many undo per sec(it is in bytes/or kbytes), it can retain with the current undo size..
In other words; "How many undo per sec (note that it s a quantity but in seconds..) can be retained with the current undo table space" gives us the optimal undo retention value..
Also, there is tool named Undo Advisor for adjusting the undo parameters. 
You may look the details if you are interested with it..

So far so good..

We size undo on disk but do we think of memory? Yes, we dont most of the times...
We know undo is cached in the buffer cache.. It is cached like any other data blocks.. 
Oracle Buffer cache mechanism flushes the blocks according to the LRU.. When the cache is almost full / or when the checkpoint occurs, Dbwr does the job and writes the dirty blocks to the disk..  So if we have a lot of dirty blocks , Dbwr must make a lot of operations to flush the buffer cache and  this will affect Dbwr performance..
Dbwr need to make these flush operations even when your query is working with the undo blocks for the first time.. That is if you have  a big tablespace and if your undo parameters let Oracle to use the new undo blocks rather than overwriting, Oracle will load the undo blocks to cache which will trigger Dbwr if there are no available cells in the cache for storing these undo blocks.  Maybe Dbwr will flush the dirty blocks and then fill the overwritable cells with the undo blocks and then flush again and so on..

You can look to the buffers in the buffer cache using the query below;

SELECT /*+ LEADING (BH DO) */
  DF.TABLESPACE_NAME,
  BH.OBJD,
  BH.FILE#,
  BH.BLOCK#,
  BH.STATUS,
  BH.CLASS#
FROM
  V$BH BH,
  DBA_OBJECTS DO,
  DBA_DATA_FILES DF
WHERE
  BH.OBJD=DO.DATA_OBJECT_ID(+)
  AND DO.DATA_OBJECT_ID IS NULL
  AND BH.FILE#=DF.FILE_ID;

Class number:
1       data or index blocks
2       sort blocks
3       save undo blocks
4       segment headers
5       save undo segment header blocks
6       free list blocks
7       system undo segment header blocks
8       system undo segment blocks
7+(n*2) undo segment n header block
8+(n*2) undo segment n block 

Anayways.. The solution to this problem, would be using the same bunch of undo blocks for undo operations.. So when we talk about optimal values in here , we need to consider Dbwr performance as well..  Also, while considering Dbwr performance and buffer cache activities, we need to consider the errors that might be hit because of under-valued undo parameters..(ORA-1555 and inability to flashback)
What I try to mean is, 
we need to set the optimal undo tablespace size and retention parameters in order to have the ability the flashback,  not to hit ORA-1555 and not to affect Dbwr performance..

To set undo tablespace size and undo_retention , we need to consider the following facts;

If the Undo Tablespace autoextend -  undo retention will be your low threshold. Oracle will never overwrite undo which is retained less than 30 minutes.
Also Oracle will tune the undo retention parameter by itself internally to supply the needed undo for the long queries in  the database.(if it have space)
Finding Tuned undo retention : SELECT TO_CHAR(BEGIN_TIME, 'MM/DD/YYYY HH24:MI:SS') BEGIN_TIME,
TUNED_UNDORETENTION FROM V$UNDOSTAT;

IF the Undo tablespace size is fixed - Oracle ignores undo_retention value.. It will automatically tune undo to have a maximum possible retention period.
Note that :  When non-autoextensible undo space is used, tuned_undoretention is calculated based on a percentage of the undo tablespace size. In some cases especially with large undo tablespace, This will make it to be calculated so large and this may result a large undo_retention..
To fix this behaviour, Set _smu_debug_mode=33554432:
With this setting, TUNED_UNDORETENTION is not calculated based on a percentage of the fixed size undo tablespace. Instead it is set to the maximum of (MAXQUERYLEN secs + 300) and UNDO_RETENTION.

So in brief, undo_retention declares the minimum threshold.. The database behaviour depends on the undo tablespace size and the undo datafile's autoextendability.

Okay, how to find the optimal undo tablespace size and undo retention ?
To find the optimal values; we need to analyze.
I mean, by knowing the facts and using the information gathered fro mthe analysis; we can set the optimal undo values for our Mission Critic databases..
We can start by making the undo tablespace  autoextend in the first place.. Then we can monitor and see till where it is extended? While our undo tablespace is being extended, we need to consider our buffer cache size, too .. As we dont want to exploit it.  We also need the consider our required Flashback time..
So for all of these, we can use undo advisor at this time...
On the other hand, If we dont have undo advisor or dont want to use undo advisor, we should follow the following  method, and set the optimal values for undo..


  • Tune your application queries.. Very Long processess may encounter failures while extending undo tablespace (even if your undo tablespace is big in size) , Very long queries may encounter failures while trying to gather an old undo image which may be overwritten.
  • In my opinion, dont use unlimited autoextend undo datafiles.. If you use autoextend undos , you may end up with a big undo tablespace and you may miss out the problems with processes, which use undo tablespace unreasonably.
  • The UNDO_RETENTION value should at least be equal to the length of longest running query (find : select max(maxquerylen) from v$undostat;) on a given database instance.
  • Consider your Flashback requirements while setting undo retention.. When you enable the Guarentee option, the database never overwrites unexpired undo data. That is undo data whose age is less than the undo retention period..
  • Also think about Guarantee option; if  flashback guarantee is a must for you..
  • If you have decided your minimum Undo_retention, then Set your undo tablespace using the following formula; " Undo_retention * Generated Undo Blocks per second * block size " .. The values used in this formula should be derived from the Peak times.. 
    • Supposing , Undo retention is already set and The blocksize is constant..
    • Generated Undo Blocks per second ->SELECT undoblks/((end_time-begin_time)*86400) "Peak Undo Block Generation" FROM v$undostat WHERE undoblks=(SELECT MAX(undoblks) FROM v$undostat); 

    Okay, subject began to fall apart :) Lets summarize the things mentioned in this blog post and come to a conclusion.. 

    In Brief, "decide your undo_retention based on the longest running query in your database and flashback requirements of yours .. Then enable autoextend with a reasonable limit on your undo datafiles to satisfy this undo retention."
    References:
FAQ – Automatic Undo Management (AUM) / System Managed Undo (SMU) (Doc ID 461480.1)
Expert Oracle Database 11g Administration, Sam Alapati
Asktom, Tom Kyte
How To Size UNDO Tablespace For Automatic Undo Management (Doc ID 262066.1)

How to Shrink the datafile of Undo Tablespace
Bug 5387030 - Automatic tuning of undo_retention causes unusual extra space allocation
Bug 5442919 - Expired extents not being reused (ORA-30036)
10g NEW FEATURE on AUTOMATIC UNDO RETENTION
Bug 4070480 - Unexpired extents used when there is free space available in the UNDO tablespace
Managing Rollback/Undo Segments in AUM (Automatic Undo Management)

Friday, August 22, 2014

EBS 12.2 -- Another FRM-40735: ON INSERT trigger ORA-01403 error

As you know, In Apps Dba world, we use sql tracing for dealing with these kind of Form Errors..


Using SQL Trace with binds functionality of Oracle Forms, we enable db trace, reproduce the error and analyze the trace to find the reason behind these kind of error..
Sometimes, we can not see the reason or the failing sql directly in the trace. So there are times that we need to use our senses and try the Sql's one by one to find  the cause of these types errors.
In the problem which is described in the screenshot , the problem was there in the following sql;

select *
from apps.hr_legislation_installations
where application_short_name='PER'
and legislation_code is null and STATUS='I'

When we run this sql from our Favourite tool (TOAD: ), we have seen that no rows returned.. On the other hand, it is related with legislative data install , and it was obvious that this  was the big problem..
As a solution, we applied hrglobal (online) and problem  dissapeared..

At the end of the day, we have seen that FRM errors are generic.. They are not the causes , but the result.. So as long as , we have an ORA- there, sql tracing is a good method for diagnosing Form errors.
Lastly, I dont even recommend searching the Oracle Support based on these FRM errors.. If needed, analyze the sql tace and then search the Oracle Support for the findings in your analysis.

Tuesday, August 19, 2014

EBS -- Workflow Notification Mailer diagnostics

Supposing that the functional setup was already done properly, I will list possible causes that may prevent Notification mailer to function properly.
Besides, you will find  full process throubleshooting methods in this post.
These throubleshooting methods can be used to investigate the causes which can not be seen clearly at the first sight.

1)Smtp and Imap connection/timeout problems..

These type of problem can easily be detected, as relevant exceptions are reflected to the notification mailer's logfiles.
Note that : for debugging purposes, you should always set your notification mailer log level to "Statement"
You can read the logfiles using OAM, but I dont recommended using OAM for reading the logs, because it starts to encounter problems when the logfiles become large in size.
Rather than that, connect to the application server and open the log files using vi.
For 12.2 logfiles are located in fs_ne, they are under $APPLCSF/$APPLLOG directory..
All the files starting with FNDCPGSC belongs to the Notification mailer. Order them by date, and open the recent one and make your analysis.

To read mailers log file more efficiently:




2)If you have problems sending mails (outbound), then your out queue may be logically corrupt or mailer may be stuck because of an invalid email address.. 

Stop the notification mailer
Back up the creation scripts of wf_notification_out table and queue.
create table APPLSYS.AQ$WF_NOTIFICATION_OUT_ERM as select * from APPLSYS.AQ$WF_NOTIFICATION_OUT
Dropp the WF_NOTIFICATION_OUT queue with the API; sqlplus apps/apps
exec dbms_aqadm.stop_queue( queue_name => 'APPLSYS.WF_NOTIFICATION_OUT', wait => FALSE);
exec dbms_aqadm.drop_queue_table( queue_table => 'APPLSYS.WF_NOTIFICATION_OUT', force =>TRUE);

Recreate the wf_notification_out table & queue manually, with sqlplus using the scripts , backed up in the first place.;

BEGIN
SYS.DBMS_AQADM.CREATE_QUEUE_TABLE
(
QUEUE_TABLE => 'APPLSYS.WF_NOTIFICATION_OUT'
,QUEUE_PAYLOAD_TYPE => 'SYS.AQ$_JMS_TEXT_MESSAGE'
,COMPATIBLE => '8.1'
,STORAGE_CLAUSE => 'NOCOMPRESS
TABLESPACE APPS_TS_QUEUES
RESULT_CACHE (MODE DEFAULT)
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 128K
NEXT 128K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)'
,SORT_LIST => 'PRIORITY,ENQ_TIME'
,MULTIPLE_CONSUMERS => TRUE
,MESSAGE_GROUPING => 0
,COMMENT => 'Workflow JMS Topic'
,SECURE => FALSE
);
End;


CREATE OR REPLACE SYNONYM APPS.WF_NOTIFICATION_OUT FOR APPLSYS.WF_NOTIFICATION_OUT;
GRANT SELECT ON APPLSYS.WF_NOTIFICATION_OUT TO APPS WITH GRANT OPTION;
GRANT SELECT ON APPLSYS.WF_NOTIFICATION_OUT TO EM_OAM_MONITOR_ROLE;


BEGIN
SYS.DBMS_AQADM.CREATE_QUEUE
(
QUEUE_NAME => 'APPLSYS.WF_NOTIFICATION_OUT'
,QUEUE_TABLE => 'APPLSYS.WF_NOTIFICATION_OUT'
,QUEUE_TYPE => SYS.DBMS_AQADM.NORMAL_QUEUE
,MAX_RETRIES => 5
,RETRY_DELAY => 3600
,RETENTION_TIME => 86400
,COMMENT => 'Workflow JMS Topics'
);
END;


DECLARE
aSubscriber sys.aq$_agent;
BEGIN
aSubscriber := sys.aq$_agent('WF_NOTIFICATION_OUT',
'',
0);
dbms_aqadm.add_subscriber
( queue_name => 'APPLSYS.WF_NOTIFICATION_OUT'
,subscriber => aSubscriber
,rule => '1=1');
END;
/

BEGIN
SYS.DBMS_AQADM.START_QUEUE
(
QUEUE_NAME => 'APPLSYS.WF_NOTIFICATION_OUT'
,ENQUEUE => TRUE
,DEQUEUE => TRUE
);
END;
/


BEGIN
sys.dbms_aqadm.grant_queue_privilege
('ENQUEUE', 'APPLSYS.WF_NOTIFICATION_OUT', 'APPS', TRUE);
sys.dbms_aqadm.grant_queue_privilege
('DEQUEUE', 'APPLSYS.WF_NOTIFICATION_OUT', 'APPS', TRUE);
END;
/



Execute following from the application node..sqlplus apps @$FND_TOP/patch/115/sql/wfntfqup.sql APPS apps APPLSYS
sqlplus apps/apps @$FND_TOP/patch/115/sql/wfjmsqc2.sql applsys apps
sqlplus apps/apps @$FND_TOP/patch/115/sql/wfmqsubc2.sql APPLSYS APPS
sqlplus apps/apps @$FND_TOP/patch/115/sql/wfntfqup APPS apps APPLSYS --> again

3) Imap related problems:

For Imap related problems, check your mailer log .. Especially, lines containing inboundThreadGroup.. These lines are for the Imap related messages..
Possible problems with IMAP are;
  • PROCESS and INBOX folders missing-> If this is the case, create those folder properly for your IMAP account in your IMAP server.
  • Autoreply or similar unrelated email are sitting in your IMAP inbox folder - > clear them by connecting to your IMAP server..
  • Unable to connect mail store  > If these kind of errors are seen in your log; 
You should do the the following things , check and take actions if necessary;

  • Stop the mailer;
  • Use the Workflow Mailer configuration screens in OAM and change the TAG section to create entries to ignore junk and spam e-mails:
(In Oracle Applications Manager (OAM) --> Navigate to Workflow Manager --> Notification Mailers --> Edit --> Advanced --> Step 6)
For instance, if one of the e-mails that are bugging the mailer has the abstract "AUTOREPLY MESSAGE: The user JSMITH is on holidays" then the text of the new entry can have 'AUTOREPLY MESSAGE' (no quotes) and the action can be ignored.
This way all the messages coming from users with similar subject will be ignored by the java mailer.
Clean the IMAP account mail folders

  • Using any desktop imap client such as Outlook, logon to the imap account and review the contents of the inbox. Remove any spam messages from the inbox. Ensure that any valid message responses are set to 'unread' when you're done. Each IMAP message carries a read / unread flag in the message header and the mailer will not pick up any messages marked as ‘read’. Use the IMAP client tools to delete and purge excess messages from Process and Discard Folders. Don’t delete these folders and don’t use Unix command line utilities to truncate folders from the file system. Use the IMAP client tool instead. When the account is cleaned up, log off of the account and remove the account from your desktop mail client so that you don’t accidentally automatically log in while the mailer is ever running.  Start the mailer using the OAM Workflow manager console.
  • If you have TEST or DEV instances, you should check them.. They must have dedicated IMAP accounts for their notification mailer.Make sure that no other instance is using the same Workflow Mailer account as set in the instance where the issue appears.
You can run the following query in your environments and determine the configured IMAP server accounts..

select c.component_id, c.component_name, p.parameter_id, p.parameter_name, v.parameter_value value
from fnd_svc_comp_param_vals_v v, fnd_svc_comp_params_b p, fnd_svc_components c
where c.component_type = 'WF_MAILER'
and v.component_id = c.component_id
and v.parameter_id = p.parameter_id
and p.parameter_name in ( 'INBOUND_SERVER','ACCOUNT', 'REPLYTO')
order by c.component_id, c.component_name,p.parameter_name;


  • Make sure that Workflow Mailer account is not monitored using an email client while Workflow Notification Mailer is up and running. Folders of this account should be monitored only having the Workflow Notification Mailer stopped to prevent locking it.
  • Make sure that "Processor Close on Read Timeout" and "Expunge Inbox on Close" parameter is checked.
    To check current value you may execute below SQL statement and the value field should be 'Y':
     select c.component_id, c.component_name, p.parameter_id, p.parameter_name, v.parameter_value value
    from fnd_svc_comp_param_vals_v v, fnd_svc_comp_params_b p, fnd_svc_components c
    where c.component_type = 'WF_MAILER'
    and v.component_id = c.component_id
    and v.parameter_id = p.parameter_id
    and p.parameter_name = 'PROCESSOR_READ_TIMEOUT_CLOSE'
    order by c.component_name,p.parameter_name;
  • Make sure your IMAP server is reachable from the application server node.
  • use telnet - > telnet <imap_server_name> 143
    And use the actual java program ->
AFJVAPRG -classpath $AF_CLASSPATH -Ddebug=Y -Dprotocol=imap \
-Ddbcfile=$FND_SECURE/<filename>.dbc \
-Dserver=<imap_server_name> -Dport=143 \
-Daccount=<workflow_account> -Dpassword=<password for this account> -Dconnect_timeout= 120 \
-Dfolder=INBOX oracle.apps.fnd.wf.mailer.Mailer

Check the email address using the following query.. Make sure that there are not email addresses which contains a white space..


If below SQL statement is returning rows you need to correct the email addresses for associated users:
col name format a25
col email_address format a35
select name, '['||substr(email_address, 1, 25)||']', orig_system from wf_local_roles where email_address like '% %'


  • Increase the Inbound connection time out parameter to 200 or higher (default value is 120) (In OAM --> Navigate to Workflow Manager --> Notification Mailers --> Edit --> Advanced --> Step 3)
  • Check with your IMAP server administrator that there is no time out parameter set to a low value at IMAP server level. This may prevent Workflow Notification Mailer to keep the connection active in order to process the responses.
  • Unsolicited emails in the INBOX can cause the inbound thread used by the mailer to be orphaned. This thread locks the account and prevents any new connection from being made. A new exception was discovered in the latest version of the mailer code.. Check the followind Support not for the fix: Workflow Mailer Is Not Able to Handle Unsolicited Emails Due to Error: java.io.IOException: Error in encoded stream OR java.sql.SQLException: Closed Statement (Doc ID 974088.1)
4. Preference (FND_USER_PREFERENCES) MAILTYPE problems:
The MAILTYPE user preference may be disabled for your users.. This will prevent notification mailer from sending emails to them..
You can check mailtype preference using following query;
select PREFERENCE_VALUE
from FND_USER_PREFERENCES
where PREFERENCE_NAME='MAILTYPE' ;

Also check the workflow tables for the preference,select notification_preference from wf_roles ;
select notification_preferenc from wf_local_roles;


If you see the mailtype preference is disabled for a user, this means that user will not receive any emails..

You may update the preference if necessary.
FND_PREFERENCE.put(p_user_name => 'USER_NAME',
p_module_name => l_module_name,
p_pref_name => l_pref_name,
p_pref_value => l_new_pref_value);


Also update the wf_local_roles table accordingly..

UPDATE wf_local_roles
SET NOTIFICATION_PREFERENCE = l_default_preference
WHERE NAMe =l_notify_user;


Options for the MAILTYPE preference are as follows,
Plain text mail (MAILTEXT) - The notification message appears as plain text, with no attachments.

HTML mail with attachments (MAILHTML) - The notification message appears as HTML-formatted text, with at least one standard attachment that is a link to the notification in the Notification Details web page. If the notification message has 'Content-Attached' message attributes, these attributes appear as additional custom attachments to the message.
HTML mail (MAILHTM2) - The notification message appears as HTML-formatted text, but does not include any standard attachments. If the notification message has 'Content-Attached' message attributes, however, these attributes appear as custom attachments to the message.
Attention: If you wish to view notifications with HTML formatting, but your mail reader is not able to interpret HTML formatting in the mail message body, change your notification preference to 'Plain text mail with HTML attachments' (MAILATTH). The MAILATTH preference delivers an HTML-formatted version of the notification as an attachment to the plain text notification.

Plain text mail with HTML attachments (MAILATTH) - The notification message appears as plain text, with at least two standard attachments. One attachment is an HTML-formatted version of the message, and the other is a link to the notification in the Notification Details web page. If the notification message has 'Content-Attached' message attributes, these attributes appear as additional custom attachments to the message. 

Plain text summary mail (SUMMARY) - The message is a plain text summary of all open notifications. To respond to the individual notifications in the summary, you must access the notifications from the Worklist web page.

HTML summary mail (SUMHTML) - The message is an HTML-formatted summary of all open notifications, with a link to the Worklist web page as well as links to each notification in the Notification Details web page. To respond to the individual notifications in the summary, you must access the notifications from the web pages. This notification preference is currently available only in the version of Oracle Workflow embedded in Oracle Applications.

Do not send me mail (QUERY) - The notification mailers do not send you e-mail notifications. Instead you must query and respond to your notifications from the Worklist web page.
Attention: You can always query and respond to your notifications from the Worklist web page, even if you set your notification preference to send you mail.

5) OTHER -- Diagnosing the full process:


If the problems and fixes explained above are not related with the problem ; you need to debug the Full process. We need to investigate the full process for SMTP or IMAP according to the problem..
What I mean by "full process" is the following;

For SMTP:



For IMAP:


So we need to investigate and debug all the processes described in the above diagram, one by one..

Lets start with SMTP:

  • Check the first process.. Check the workflow encounters any error while initiating the request for the notification:
Query WF_NOTIFICATIONS and check the desired notification.. Was the row for the notification inserted into that table? Any errors? .. Check the database.. (for tablespace usage , or any errors recorded in Alert log..)
Use the wfstat.sql script to diagnose the issue further.

  • Check the WF_DEFERRED queue..  The messages should be enqueued in this queue. IF you see any problem in this phase, check for database again.. Also note that : WF_DEFERRED queue may be bypassed if oracle.apps.wf.notification.send event is set to Synchronous
  • Check if  the Mailer Agent Listener picks up the message from the WF_DEFERRED queue..
  • The correlation ID for dequeuing the message may not match the correlation ID specified for the enqueued message. Check the configuration of the Notification Mailer Agent Listener and ensure that the Correlation ID is blank.
    If this occurs particularly after a database upgrade, then a rebuild of the notification mailer queues may be required. For rebuilding you can use these sqls: 
    wfevqcln.sql wfntfqup.sql
  • Check if there is any exceptions encountered during the processing of the XML.. Use the wfmlrdbg.sql to extract the content of the WF_ERROR queue for the notification .After correcting problem, you may retry the operation
  • Check if the notification is processed and the generated XMLis enqueued in the WF_NOTIFICATIONS_OUT.. If you dont see a record for the desired notification:
  • The Workflow Deferred Notification Agent Listener may not be running.. If that is the case, start it..  Note that: You may not start it directly.. In that case, you need to fix the underlying problem.. The underlying problem may be the agent listener service, concurrent managers ,  GSM or other runtime errors. Following lists the possible causes and needed actions for fixing them:
The Agent Listener Service is shut down
The Agent Listener Service should be restarted.
The Concurrent Manager is not running
Do the throubleshooting, fix the problem if there are any and start the concurrent manager
A GSM issue. Either not configured properly or not running.
Do the throubleshooting and fix the GSM errors.
Other Errors:
Errors will be recorded in the Workflow Deferred Notification Agent Listener log file. This can be found in the $APPLCSF/$APPLLOG directory. Use the following command to locate the log file:

grep -l WFALSNRSVC FNDCPGSC*.txt
The highest numbered file will be the most recent.
Note that errors such as "oracle.apps.fnd.cp.gsc.server.GenericSvcComponentEOException: ORA-06508: PL/SQL: could not find program unit being called" imply an invalid package. Check the state of the database packages.

If you cant find any problems using the checks above . Then check that the event for oracle.apps.wf.notification.send and oracle.apps.wf.notification.send.group are enabled and that the subscriptions for oracle.apps.wf.notification.send.group are defined and enabled and that the Generate function of WF_XML.Generate is defined for the oracle.apps.wf.notification.send event.

Make sure that the local system is enabled
select name, text
from wf_resources
where name like 'WF_SYSTEM_STATUS';

Also check for tablespace issues..

Check wf_notification_out queue.. Check to see the PROCESSED message counts are increasing.. If everyting is normal READY state messages should be put into PROCESSED state ..

select corr_id, msg_state, retry_count, count(Msg_id)
from applsys.aq$wf_notification_out
group by corr_id, msg_state, retry_count;


  • If there are a lot of ready messages, and processed ones are not increasing, check if the notification mailer is running.. Do the same checks, as I mentioned above for the Workflow Deferred Notification Agent Listener .. In addition check the database, IMAP and Outbound's SMTP server connectivity. If the notification mailer is up, set its log level to statement, and analyze the log in detail.. In addition make a telnet test from the application server , and ensure the emails are sent..
  • telnet [outbound server] 25
    EHLO [mailer node]
    MAIL FROM: [reply_to address]
    RCPT TO: [my_test_email_address]
    DATA
    Subject: Test message

    Test message body
    .
    quit
    Also you may test the smtp connection using Oracle's java program;
     $AFJVAPRG -classpath $AF_CLASSPATH  -Dprotocol=smtp \
            ( -Ddbcfile=<dbcfileLocation_here> | -Ddbuser -Ddbpassword -Ddburl )\
            -Dserver=<servername_here> [-Dport=<port> default 25 ] \
            [-DoutboundUser=<smtp_user_name_here> \
            [-DoutboundPassword=<smtp_user_password_here> \
            [ -Dssl= <Y|N> default N ]\
            [ -Dtruststore=<truststore_here> ]\
            [ -Dconnect_timeout=<seconds> default 5 ] \
            [ -Ddebug=<Y|N> default N ]\
            [ -Dlogfile=<log filename> default test.log ]\
       oracle.apps.fnd.wf.mailer.Mailer

  • Again check the email address. They must be in the form of  local-part "@" domain..
  • Note that : Rejection by the SMTP server to accept any recipient will cause the mailer to set the notification status to FAILED and raise the WFMLRSND_FAILED_UNDELIVERABLE error with a list of the recipients that failed delivery. This will result in the oracle.apps.wf.notification.send.failure event. The subscription for this event will then mark each of the recipients that, failed delivery, with a notification preference of DISABLED.
  • Other communication issues between the Notification Mailer and the SMTP server can be also verified with the Notification Mailer configuration parameter DEBUG_MAIL_SESSION. Setting this to "Y" will cause the SMTP dialogue to be displayed in the log file. 
To enable DEBUG_MAIL_SESSION;

run sqlplus apps/<apps_pw> @$FND_TOP/sql/afsvcpup.sql
sqlplus apps/apps @$FND_TOP/sql/afsvcpup.sql
SQL*Plus: Release 10.1.0.5.0 - Production on Tue Aug 19 15:51:02 2014

Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options


Component Id Component Name Component Status Type Containe
------------ ------------------------------ ---------------- --------------- --------
10000 ECX Inbound Agent Listener STOPPED WF_AGENT_LISTEN GSM
10001 ECX Transaction Agent Listener STOPPED WF_AGENT_LISTEN GSM
10002 Workflow Deferred Agent Listen RUNNING WF_AGENT_LISTEN GSM
10003 Workflow Deferred Notification RUNNING WF_AGENT_LISTEN GSM
10004 Workflow Error Agent Listener RUNNING WF_AGENT_LISTEN GSM
10005 Workflow Inbound Notifications RUNNING WF_AGENT_LISTEN GSM
10006 Workflow Notification Mailer RUNNING WF_MAILER GSM
10020 Web Services IN Agent STOPPED WF_JAVA_AGENT_L GSM
10021 Web Services OUT Agent STOPPED WF_DOCUMENT_WEB GSM
10022 Workflow Java Deferred Agent L RUNNING WF_JAVA_AGENT_L GSM
10023 Workflow Java Error Agent List RUNNING WF_JAVA_AGENT_L GSM
10040 WF_JMS_IN Listener(M4U) RUNNING WF_JAVA_AGENT_L GSM
10041 Workflow Inbound JMS Agent Lis STOPPED WF_AGENT_LISTEN GSM

Enter 10006 as , it belongs to Notification mailer.

Then Enter 10061 for setting Debug Mail Session (default to N)
Lastly Update Debug Mail Session parameter to 'Y'

INBOUND (IMAP):
  • If users can not send response email, check SMTP server, it may not be up.
  • TEST IMAP connectivity using the following example:
  • telnet IMAP_SERVERS_IP 143
    Trying 192.168.10.10...
    Connected to localhost.
    Escape character is '^]'.
    * OK
    A1 OK LOGIN completed
    --> A2 list "" "*"
    --> list all folders
    * LIST () "/" "INBOX"
    * LIST () "/" "Mickey"
    * LIST () "/" "Minnie"
    a2 OK LIST completed
    --> A3 select "INBOX"
    --> selecting a folder
    * 5 EXISTS
    * 0 RECENT
    * OK [UIDVALIDITY 1] UID validity status
    * FLAGS (\Seen \Deleted \Answered \Flagged \Draft X-ORACLE-COMMENT $MDNSENT X-DS
    NREQUESTED X-MDNREQUESTED X-DELETEAFTERSUBMIT X-ORACLE-FILTERED)
    * OK [PERMANENTFLAGS (\Seen \Deleted \Answered \Flagged \Draft X-ORACLE-COMMENT
    $MDNSENT X-DSNREQUESTED X-MDNREQUESTED X-DELETEAFTERSUBMIT X-ORACLE-FILTERED)] P
    ermanent flags
    * OK [UNSEEN 5] 5 is first unseen
    a3 OK [READ-WRITE] SELECT completed
    --> A4 fetch 1:2 RFC822
    --> fetch mail number 1 and 2 and see the mail including header information
    * 1 FETCH (RFC822 {497}
    Return-Path: <dagobert.duck@ducksville.com>
    Received: from sniblade5.ducksville.com by sniblade5.ducksville.com
    with ESMTP id 10031084275002; Tue, 11 May 2004 13:30:02 +0200
    Received: (from orasup@localhost)
    by sniblade5.ducksville.com (8.11.6/8.11.6) id i4BBTwY06042
    for donald.duck@ducksville.com; Tue, 11 May 2004 13:29:58 +0200
    Date: Tue, 11 May 2004 13:29:58 +0200
    From: dagobert.duck@ducksville.com
    Message-Id: <200405111129.i4BBTwY06042@ducksville.com>

    Hello Donald.
    )
    * 2 FETCH (RFC822 {561}
    Return-Path: <orclguest@ducksville.com>
    Received: from sniblade5.ducksville.com by sniblade5.ducksville.com
    with ESMTP id 10041084275106; Tue, 11 May 2004 13:31:46 +0200
    Message-ID: <812650.1084275106601.JavaMail.orasup@sniblade5.ducksville.com>
    Date: Tue, 11 May 2004 13:31:46 +0200 (CEST)
    From: orclguest@ducksville.com
    To: donald.duck@ducksville.com
    Subject: hello from webmail
    Mime-Version: 1.0
    Content-Type: text/plain; charset=ISO-8859-1
    Content-Transfer-Encoding: 7bit
    X-Priority: 3
    X-Mailer: Oracle Webmail Client(UIX)

    )
    --> A99 logout
    * BYE Oracle IMAP4 server terminating connection
    A99 OK LOGOUT completed
    Connection closed by foreign host.

  • In addition ; Use Application Diagnostics responsibility to test IMAP connections
To execute the test, do the following:

Start Oracle E-Business Suite
Connect to responsibility Application Diagnostics
Select the Diagnose menu option
Click button Select Application and select Application "Oracle eMail Center"
Scroll down to group "Email Account"
Select test name "IMAP Connection"
Input Parameters (* required)N/A
Output containsReports if there are any problems connecting to the IMAP Server.

You may use oracle's java to test the Imap connections, as well...


   $AFJVAPRG -classpath $AF_CLASSPATH -Dprotocol=imap  \
        ( -Ddbcfile=<dbcfileLocation_here> | -Ddbuser -Ddbpassword -Ddburl )\
        -Dserver=<servername_here> [-Dport=<port> default 143] \
        -Daccount=<accountname_here> -Dpassword=<password_here> \
        [ -Dfolder=<foldername_here> ]  \
        [ -Dconnect_timeout=5 ] \
        [ -Dssl= <Y|N> default N ] \
        [ -Dtruststore=<truststore_here> ]\
        [ -Dconnect_timeout=<seconds> default 5 ] \
        [ -Ddebug=<Y|N> default N ]\
        [ -Dlogfile=<log filename> default test.log ]\
        oracle.apps.fnd.wf.mailer.Mailer

  • Check wf_notifications_in queue .. If you cant find the desired notification there check the following; 
Database connectivity and database object status
Database table space issues
Invalid Objects
Business Event System Status

  • Check if the Workflow Inbound Notification Agent Listener is running.
The Agent Listener Service is shut down
The Agent Listener Service should be restarted.
The Concurrent Manager is not running
Do the throubleshooting, fix the problem if there are any and start the concurrent manager
A GSM issue. Either not configured properly or not running.
Do the throubleshooting and fix the GSM errors.
Other Errors:
Errors will be recorded in the Workflow Deferred Notification Agent Listener log file. This can be found in the $APPLCSF/$APPLLOG directory. Use the following command to locate the log file.
grep -l WFALSNRSVC FNDCPGSC*.txt
The highest numbered file will be the most recent.
Note that errors such as "oracle.apps.fnd.cp.gsc.server.GenericSvcComponentEOException: ORA-06508: PL/SQL: could not find program unit being called" imply an invalid package. Check the state of the database packages.

If you cant determine the error in the start process Workflow Inbound Notification Agent Listener, use following script to start the listener in command line and gather the information.

sqlplus -s $1 << SESSION_END
set serveroutput on size 1000000;
spool listen.log;
prompt Executing listener
exec wf_event.listen(p_agent_name => 'WF_NOTIFICATION_IN', p_correlation => 'oracle.apps.wf.notification.receive%');
prompt Obtaining output. This may take a few minutes...
spool off;
quit;
SESSION_END
  • Check  WF_ERROR table.. If you see errors there, use wfmlrdbg.sql for the respective notifications will provide the WF_ERROR message details, as will the output of the wfstat.sql script.

Sunday, August 17, 2014

RDBMS -- Oracle "compatible" parameter

Lets have a quick look to the compatibility parameter in Oracle Databases..

The Compatible parameter is used to control the formats of oracle data blocks and redo streams. It is basically controlling what is written to disk.
Compatible parameter can be set to the current database version or a lower version, according to the needs. The important thing is that; when you set your compatible parameter to a upper version, you can set it back. For example: if you set your compatible parameter to 11.2.0 , you can set it back to 11.1.0..
Compatible parameters are used mainly in upgrades and migrations. ASM instance also use the compatible parameters to behave according to the desired binary versions..
ASM has attributes in the diskgroup levels, too.
The compatible.asm diskgroup attribute controls the format of the ASM metadata.  This attribute controls the needed version of  the ASM instances which can mount the related diskgroup.
The compatible.rdbms diskgroup attribute of ASM determines the format of ASM files.
The diskgroup can be accessed by any database instance with a compatible init.ora parameter set equal to or higher than the compatible.rdbms attribute.

In this manner, you can have an Oracle Database 11g  which run in 10.2 compatibility mode.
If you have such a database, your database will generate redo and generate data block that a 10.2 database could understand. On the other hand; even if this database is an 11g, a new  feature -- OLTP Compressed block 11g-- feature will not work  in this database, as its compatible parameter is set to 10.2, and as the Compressed block feature requires a change on data block format..
On the other hand, your data file are written in a format that an 10g Oracle Database could still read..

Additional info:



It is important to know that, compatible parameters does not affect the medata stored in the database..
It also does not effect the optimizer behaviour..  OPTIMIZER_FEATURES_ENABLE parameter controls all about the optimizer.For example, if you upgrade your database from release 10.1 to release 11.1, but you want to keep the release 10.1 optimizer behavior, you can do so by setting this parameter to 10.1.0.
AWR repository also is not affected from  compatible parameter.
As mentioned compatible controls what is written to disk..


So, in a case of an upgrade, the feautes depends on the db block or redo streams requires this parameter to be set to the upgraded version..  To know these feature beforehand, we need to examine the documentation and look for the new features and their compatibility requirements..

Lastly, lets check an Ex-factory Exadata X4 to see that parameter in use;
--in this example we'll see compatability between ASM and Database instances, as well..

We will query v$asm_diskgroup view, which displays one row for every ASM disk group discovered by the ASM instance on a node.

select name,compatibility,database_compatibility from v$asm_diskgroup;

NAME                           COMPATIBILITY                                   DB_COMPATIBILITY

DATAC1                         11.2.0.4.0                                                   11.2.0.2.0
DBFS_DG                        11.2.0.4.0                                                   11.2.0.2.0
RECOC1                         11.2.0.4.0                                                   11.2.0.2.0


As you see here, we  have 3 diskgroups which has compatability set to 11.2.0.4.. This means minimum software version required for an ASM instance to mount these diskgroups needs to be 11.2.0.4..
Also, our diskgroups have 11.2.0.2 set for the databases_compatability, which means minimum software version required for a database instance to use files in this disk group is 11.2.0.2.

So as you may expected, in order to startup a new database which stores its files in ASM of these Exadata environment, our database compatible parameter must be set to 11.2.0.2 at least ...

Okay, that is all for now.. I hope you will find it useful.

Wednesday, August 13, 2014

EBS R12 -- EBS Security Config Checks

Following is a good set of control scripts derived from the Secure Configuration Guide for Oracle E-Business Suite Release 12..  
Using these scripts , we can verify the proper setting of the fundemantal EBS Security configurations.
According to the output of these script , we can determine the actions required to be taken in order have a secure EBS environment..
On the other hand, increasing the security of EBS is a big task , as it is classified into categories such as Oracle TNS Listener Security, Oracle Database Security, Oracle Application Tier Security, Oracle E-Business Suite Security, Desktop Security and Operating Environment Security..
These scripts are useful on the way to check the Oracle E-Business Suite Security , but using these scripts is not enough to check the EBS security as a whole , as you may expect ..

List unnecessary privs in the APPLSYSPUB account :

select grantor,privilege, table_name
  from  DBA_TAB_PRIVS
 where  grantee = 'APPLSYSPUB'
   and  privilege in ('SELECT','INSERT','EXECUTE','DELETE')
   and  Rtrim(privilege) || ' ON ' || table_name NOT IN
('INSERT ON FND_SESSIONS',
'INSERT ON FND_UNSUCCESSFUL_LOGINS',
'EXECUTE ON FND_DISCONNECTED',
'EXECUTE ON FND_MESSAGE',
'EXECUTE ON FND_PUB_MESSAGE',
'EXECUTE ON FND_SECURITY_PKG',
'EXECUTE ON FND_SIGNON',
'EXECUTE ON FND_WEBFILEPUB',
'SELECT ON FND_APPLICATION',
'SELECT ON FND_APPLICATION_TL',
'SELECT ON FND_APPLICATION_VL',
'SELECT ON FND_LANGUAGES_TL',
'SELECT ON FND_LANGUAGES_VL',
'SELECT ON FND_LOOKUPS',
'SELECT ON FND_PRODUCT_GROUPS',
'SELECT ON FND_PRODUCT_INSTALLATIONS',
'INSERT ON FND_SESSIONS#',
'INSERT ON FND_UNSUCCESSFUL_LOGINS#',
'SELECT ON FND_APPLICATION#',
'SELECT ON FND_APPLICATION_TL#',
'SELECT ON FND_PRODUCT_GROUPS#',
'SELECT ON FND_PRODUCT_INSTALLATIONS#',
'SELECT ON FND_LANGUAGES_TL#');


List database users with default passwords:

set serveroutput on
declare
  l_status varchar(200);
  l_name varchar(200);
begin
-- Check if DBA_USERS_WITH_DEFPWD exists
  SELECT 'view exists'
  into l_status
  FROM all_views
  WHERE VIEW_NAME='DBA_USERS_WITH_DEFPWD';

exception
  when no_data_found then --view doesn't exist
    l_status:='Default DB user password view does not exist (pre-11g DB). See Note 361482.1';
    dbms_output.put_line(l_status);
    dbms_output.put_line('The following select will fail with ORA-04043: object DBA_USERS_WITH_DEFPWD does not exist');
    return;
  when others then
    l_status:='Unexpected Error:'||sqlcode;
    dbms_output.put_line(l_status);
end;
/
select USERNAME, ACCOUNT_STATUS from DBA_USERS
 where USERNAME in ( select USERNAME from DBA_USERS_WITH_DEFPWD )
 order by 1
/


 Show whether credit cards are encrypted in the system:

set serveroutput on

declare
  l_release varchar(50);
  l_status varchar(200);
  l_enc_mode varchar(50);
begin

select release_name
  into l_release
  from fnd_product_groups
  where product_group_name='Default product group';


dbms_output.put_line('EBS level - '||l_release);

dbms_output.put_line('Credit Card Encryption Status');
dbms_output.put_line('-----------------------------');

if substr(l_release,1,7)='11.5.10' then

-- Check if function exists

begin
  SELECT 'function exists' 
  into l_status
  FROM all_procedures
  WHERE OBJECT_NAME='IBY_CC_SECURITY_PUB'
  and PROCEDURE_NAME='ENCRYPTION_ENABLED';
exception
  when no_data_found then --function doesn't exist
    l_status:='WARNING: 11i Encryption patch not installed';
    dbms_output.put_line(l_status);
    return;
  when others then
    raise;
end;

-- Returns a boolean so we have to do this as an anonymous PL/SQL.
execute immediate('begin 
if IBY_CC_SECURITY_PUB.encryption_enabled() then 
 dbms_output.put_line(''Encryption enabled'');
else
  dbms_output.put_line(''WARNING: Encryption not enabled'');
end if;
end;');

elsif substr(l_release,1,2)='12' then --R12
  execute immediate 'select decode(IBY_CREDITCARD_PKG.Get_CC_Encrypt_Mode(),''IMMEDIATE'',''Encryption enabled'',''WARNING: Encryption not enabled'') from dual'
  into l_status;

  dbms_output.put_line(l_status);

  begin --Check for Supplemental credit card data encryption
  dbms_output.put(CHR(10)); 
  dbms_output.put_line('Supplemental Credit Card Data Encryption Status');
  dbms_output.put_line('-----------------------------------------------');

  execute immediate 'select decode(IBY_CREDITCARD_PKG.OTHER_CC_ATTRIBS_ENCRYPTED,''Y'',''Supplemental credit card data encrypted'',''WARNING: Supplemental credit card data is not encrypted'') from dual'
  into l_status;

  dbms_output.put_line(l_status);

  exception
  when others then
  if sqlcode=-904 then --function doesn't exist
    l_status:='Supplemental credit card data supported only for 12.1.2 and above';
    dbms_output.put_line(l_status);
  else
    l_status:='Unexpected Error '||sqlcode;
    dbms_output.put_line(l_status); 
  end if;
  
  end;  --Check for Supplemental credit card data encryption
  
  begin --Check for Enhanced Hashing
  dbms_output.put(CHR(10)); 
  dbms_output.put_line('Enhanced Hashing');
  dbms_output.put_line('----------------');

  execute immediate 'select decode(IBY_SECURITY_PKG.Get_Salt_Version,3,''Enhanced Hashing on'',''WARNING: Enhanced Hashing is not on'') from dual'
  into l_status;

  dbms_output.put_line(l_status);

  exception
  when others then
  if sqlcode=-904 then --function doesn't exist
    l_status:='WARNING: Enhanced hashing function not installed';
    dbms_output.put_line(l_status);
  else
    raise; 
 end if;
  
  end;  --Check for Enhanced Hashing
  
else
  dbms_output.put_line('Unrecognized EBS level!');
end if; 


exception
  when others then
    l_status:='Unexpected Error:'||sqlcode;
    dbms_output.put_line(l_status);

end;
/

Check the status of Hashed Passwords for Application Users :

set serveroutput on
declare
l_status varchar2(50);

begin
dbms_output.put_line('Password Mode');
dbms_output.put_line('---------------------------------------');
execute immediate 'select decode(FND_WEB_SEC.GET_PWD_ENC_MODE,
null,''WARNING:Hashed passwords are not on'',
''Hashed passwords are on'') from dual'
into l_status;
dbms_output.put_line(l_status);
exception
when others then
if sqlcode=-904 then
l_status:='WARNING: Hashed password patch not installed';
else
l_status:='Unexpected Error';
end if;
dbms_output.put_line(l_status);
end;
/

List profiles set incorrectly (error) : 

column "Internal Name" format A23
column "Profile Name" format A30
column "Profile Level" format a15
column "Profile Context" format a25
column "Value" format A10 wrap
column "Last Updated" format a12
REM -----------------------------------------------
select p.profile_option_name "Internal name",
n.user_profile_option_name "Profile name",
to_char(v.last_update_date,'DD-MON-RR') "Last Updated",
decode(v.level_id,
10001, 'Site',
10002, 'Application',
10003, 'Responsibility',
10004, 'User',
10005, 'Server',
10007, 'SERVRESP',
'UnDef') "Profile Level",
decode(to_char(v.level_id),
'10001', '',
'10002', app.application_short_name,
'10003', rsp.responsibility_key,
'10005', svr.node_name,
'10006', org.name,
'10004', usr.user_name,
'10007', 'Serv/resp',
'UnDef') "Profile Context",
v.profile_option_value "Value"
from fnd_profile_options p,
fnd_profile_option_values v,
fnd_profile_options_tl n,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp,
fnd_nodes svr,
hr_operating_units org
where p.profile_option_id = v.profile_option_id (+)
and p.profile_option_name = n.profile_option_name
and n.language = 'US'
and ((p.profile_option_name like 'F%_VALIDATION_LEVEL' and v.profile_option_value != 'ERROR') -- Validation profiles should be set to ERROR
or (p.profile_option_name = 'FND_SECURITY_FILETYPE_RESTRICT_DFLT' and v.profile_option_value not in ('N','Y')) -- Whitelist behavior (N) recommended, blacklist (Y) is default and should at minimum be on
or (p.profile_option_name = 'FND_DISABLE_ANTISAMY_FILTER' and v.profile_option_value != 'N') -- Antisamy checks should be enabled (N)
or (p.profile_option_name = 'FND_RESTRICT_INPUT' and v.profile_option_value != 'Y') -- Tag scanner should be enabled
or (p.profile_option_name = 'BNE_ALLOW_NO_SECURITY_RULE' and v.profile_option_value != 'N') -- Access to global integrators (integrators without a security rule) should be disabled (N)
)
and usr.user_id (+) = v.level_value
and rsp.application_id (+) = v.level_value_application_id
and rsp.responsibility_id (+) = v.level_value
and app.application_id (+) = v.level_value
and svr.node_id (+) = v.level_value
and org.organization_id (+) = v.level_value
order by p.profile_option_name, "Profile Level";


List site level profiles that are not found :

select rp.profile_name "Missing Site Profiles"
from fnd_profile_options p,
fnd_profile_option_values v,
(select 'FND_VALIDATION_LEVEL' profile_name from dual union
select 'FND_FUNCTION_VALIDATION_LEVEL' profile_name from dual union
select 'FRAMEWORK_VALIDATION_LEVEL' profile_name from dual union
select 'FND_SERVER_SEC' profile_name from dual union
select 'FND_SERVER_IP_SEC' profile_name from dual union
select 'BNE_ALLOW_NO_SECURITY_RULE' profile_name from dual union
--select 'FND_SECURITY_FILETYPE_RESTRICT_DFLT' profile_name from dual union
--select 'FND_DISABLE_ANTISAMY_FILTER' profile_name from dual union
select 'FND_RESTRICT_INPUT' profile_name from dual union
select 'UPLOAD_FILE_SIZE_LIMITS' profile_name from dual
) rp
where rp.profile_name = p.profile_option_name (+)
and p.profile_option_id = v.profile_option_id (+)
and v.level_id (+)=10001
and p.profile_option_name is null;

List profiles set incorrectly ( warning): 

select p.profile_option_name "Internal name",
n.user_profile_option_name "Profile name",
to_char(v.last_update_date,'DD-MON-RR') "Last Updated",
decode(v.level_id,
10001, 'Site',
10002, 'Application',
10003, 'Responsibility',
10004, 'User',
10005, 'Server',
10007, 'SERVRESP',
'UnDef') "Profile Level",
decode(to_char(v.level_id),
'10001', '',
'10002', app.application_short_name,
'10003', rsp.responsibility_key,
'10005', svr.node_name,
'10006', org.name,
'10004', usr.user_name,
'10007', 'Serv/resp',
'UnDef') "Profile Context",
v.profile_option_value "Value"
from fnd_profile_options p,
fnd_profile_option_values v,
fnd_profile_options_tl n,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp,
fnd_nodes svr,
hr_operating_units org
where p.profile_option_id = v.profile_option_id (+)
and p.profile_option_name = n.profile_option_name
and n.language = 'US'
and ((p.profile_option_name in ('FND_DIAGNOSTICS','DIAGNOSTICS','FND_CUSTOM_OA_DEFINTION') and v.profile_option_value != 'N') -- diagnstostics should not be generally enabled
     or (p.profile_option_name = 'FND_SECURITY_FILETYPE_RESTRICT_DFLT' and v.profile_option_value != 'N') -- Whitelist behavior (N) recommended
      )
and usr.user_id (+) = v.level_value
and rsp.application_id (+) = v.level_value_application_id
and rsp.responsibility_id (+) = v.level_value
and app.application_id (+) = v.level_value
and svr.node_id (+) = v.level_value
and org.organization_id (+) = v.level_value
order by p.profile_option_name, "Profile Level";

list users who have access to the  forms, pages, profile options, roles, and permissions listed in the tables in My Oracle Support Document :

REM ----------------------------------------------
REM Get Forms and HTML screens and functions
REM (driven by page path or jsp filename)
REM ----------------------------------------------

column user_name for a20 wrap
column resp_name for a32 wrap
column role_name for a40 wrap
column function_name for a20 wrap
column user_function_name for a35 wrap
column form_name for a10 wrap
column user_form_name for a30 wrap
column webcall for a40 wrap

prompt
prompt =========================================
prompt Forms via responsibilities
prompt =========================================


select distinct fu.user_name user_name,fr.responsibility_name resp_name,fff.function_name,fff.user_function_name, ff.form_name, ff.user_form_name
from fnd_user fu,
fnd_user_resp_groups furg,
fnd_responsibility_vl fr,
fnd_compiled_menu_functions fcmf,
fnd_form_functions_vl fff,
fnd_form_vl ff
where fff.form_id=ff.form_id
and furg.responsibility_id = fr.responsibility_id
and furg.responsibility_application_id = fr.application_id
and fr.menu_id = fcmf.menu_id
and fcmf.grant_flag = 'Y'
and fcmf.function_id = fff.function_id
and furg.user_id = fu.user_id
and sysdate between fu.start_date and nvl(fu.end_date, sysdate+1)
and sysdate between fr.start_date and nvl(fr.end_date, sysdate+1)
and fff.function_name in (
select fun.function_name
from fnd_form_functions_vl fun, fnd_form_vl form
where form.form_name in (
'ALRALERT',
'FNDATENT',
'FNDCPMCP',
'FNDCPMPE',
'FNDFFMDC',
'FNDFFMIS',
'FNDFFMVS',
'FNDFFIIF',
'FNDFFIDF',
'FNDFMFUN',
'FNDPOMPO',
'FNDPOMPV',
'FNDPRMPD',
'FNDSCAPP',
'FNDSCDDG',
'FNDSCMOU',
'GMAWFPCL',
'GMAWFCOL',
'PSBSTPTY',
'MSDCSDFN',
'MSDCSDFA',
'MSDAUDIT',
'JTFRSDGR',
'JTFBRWKB',
'JTFGANTT',
'JTFTKOBT',
'JTFGRDMD',
'JTFGDIAG',
'JTFBRWKB',
'FFXWSBQR',
'FFXWSMNG',
'OEXPCFVT',
'OEXDEFWK',
'PAYWSDAS',
'PAYWSDYG',
'WMSRULEF',
'QPXPRFOR',
'QPXPTMAP',
'QACHMDF',
'QAPLMDF',
'PERWSSCP')
and fun.form_id=form.form_id
)
order by 1,2
/

prompt
prompt =========================================
prompt Forms via grants
prompt =========================================


select distinct incrns.name user_name,wur.role_name, fff.function_name,fff.user_function_name, ff.form_name, ff.user_form_name
from fnd_grants fg,
wf_user_roles wur,
fnd_compiled_menu_functions fcmf,
fnd_form_functions_vl fff,
wf_roles wr,
wf_users wu,
wf_users incrns,
fnd_form_vl ff
where fff.form_id=ff.form_id
and fg.menu_id = fcmf.menu_id
and fcmf.function_id = fff.function_id
and fg.grantee_type in ('USER', 'GROUP')
and fg.grantee_key = wur.role_name
and wur.role_name = wr.name
and wur.role_orig_system = wr.orig_system
and wur.role_orig_system_id = wr.orig_system_id
and wur.user_name = wu.name
and wur.user_orig_system = wu.orig_system
and wur.user_orig_system_id = wu.orig_system_id
and wu.parent_orig_system = incrns.parent_orig_system
and wu.parent_orig_system_id = incrns.parent_orig_system_id
and incrns.orig_system in ('FND_USR', 'PER')
and sysdate between fg.start_date and nvl(fg.end_date, sysdate+1)
and sysdate between nvl(wur.start_date, sysdate-1)
and nvl(wur.expiration_date, sysdate+1)
and sysdate between nvl(wr.start_date, sysdate-1)
and nvl(wr.expiration_date, sysdate+1)
and sysdate between nvl(wu.start_date, sysdate-1)
and nvl(wu.expiration_date, sysdate+1)
and sysdate between nvl(incrns.start_date, sysdate-1)
and nvl(incrns.expiration_date, sysdate+1)
and fff.function_name in
(
select fun.function_name
from fnd_form_functions_vl fun, fnd_form_vl form
where fun.form_id=form.form_id and
form.form_name in (
'ALRALERT',
'FNDATENT',
'FNDCPMCP',
'FNDCPMPE',
'FNDFFMDC',
'FNDFFMIS',
'FNDFFMVS',
'FNDFFIIF',
'FNDFFIDF',
'FNDFMFUN',
'FNDPOMPO',
'FNDPOMPV',
'FNDPRMPD',
'FNDSCAPP',
'FNDSCDDG',
'FNDSCMOU',
'GMAWFPCL',
'GMAWFCOL',
'PSBSTPTY',
'MSDCSDFN',
'MSDCSDFA',
'MSDAUDIT',
'JTFRSDGR',
'JTFBRWKB',
'JTFGANTT',
'JTFTKOBT',
'JTFGRDMD',
'JTFGDIAG',
'JTFBRWKB',
'FFXWSBQR',
'FFXWSMNG',
'OEXPCFVT',
'OEXDEFWK',
'PAYWSDAS',
'PAYWSDYG',
'WMSRULEF',
'QPXPRFOR',
'QPXPTMAP',
'QACHMDF',
'QAPLMDF',
'PERWSSCP')
)
order by 1,2
/


prompt
prompt =========================================
prompt HTML pages via responsibilities
prompt =========================================


select distinct fu.user_name user_name,fr.responsibility_name resp_name,fff.function_name,fff.user_function_name, fff.WEB_HTML_CALL webcall
from fnd_user fu,
fnd_user_resp_groups furg,
fnd_responsibility_vl fr,
fnd_compiled_menu_functions fcmf,
fnd_form_functions_vl fff
where furg.responsibility_id = fr.responsibility_id
and furg.responsibility_application_id = fr.application_id
and fr.menu_id = fcmf.menu_id
and fcmf.grant_flag = 'Y'
and fcmf.function_id = fff.function_id
and furg.user_id = fu.user_id
and sysdate between fu.start_date and nvl(fu.end_date, sysdate+1)
and sysdate between fr.start_date and nvl(fr.end_date, sysdate+1)
and fff.function_name in (
select fun.function_name
from fnd_form_functions_vl fun
where
fun.WEB_HTML_CALL like '%/oracle/apps/ame/dashboard/webui/BusinessDashboardPG' or
fun.WEB_HTML_CALL like '%/oracle/apps/ame/admin/webui/AdminDashboardPG' or
fun.WEB_HTML_CALL like '%/oracle/apps/ota/admin/content/webui/LOEditPG' or
fun.WEB_HTML_CALL like '%/oracle/apps/ota/admin/adapter/webui/CmiAdapterEditPG' or
fun.WEB_HTML_CALL like '%/oracle/apps/ams/list/webui/DataSrcLOVDefnPG%' or
fun.WEB_HTML_CALL like '%/oracle/apps/ams/list/webui/ListCustomSQLPG%' or
fun.WEB_HTML_CALL like '%/oracle/apps/ams/list/webui/QueryTemplParamSQLPG%' or
fun.WEB_HTML_CALL like '%/oracle/apps/fnd/profile/webui/FndProfileCreatePG%' or
fun.WEB_HTML_CALL like '%/oracle/apps/fnd/profile/webui/FndProfileDefinePG%' or
fun.WEB_HTML_CALL like '%/oracle/apps/fnd/profile/webui/FndProfileSummaryPG%' or
fun.WEB_HTML_CALL like '%/oracle/apps/fnd/profile/webui/FndProfileUpdatePG%' or
fun.WEB_HTML_CALL like '%/oracle/apps/fnd/security/menufunction/webuiFunctionCreatePG%' or
fun.WEB_HTML_CALL like '%/oracle/apps/fnd/security/menufunction/webui/FunctionCreateDetailsPG%' or
fun.WEB_HTML_CALL like '%/oracle/apps/fnd/security/menufunction/webui/FunctionSearchPG%' or
fun.WEB_HTML_CALL like '%/oracle/apps/fnd/security/menufunction/webui/FunctionUpdatePG%' or
fun.WEB_HTML_CALL like '%ibuaugsp.jsp%' or
fun.WEB_HTML_CALL like '%ibuhacnt.jsp%' or
fun.WEB_HTML_CALL like '%/oracle/apps/fnd/security/objects/webui/ObjFindMainPG%' or
fun.WEB_HTML_CALL like '%/oracle/apps/fnd/security/objects/webui/ObjDetailsPG%' or
fun.WEB_HTML_CALL like '%/oracle/apps/fnd/security/objects/webui/ObjInsetUpdatePG%' or
fun.WEB_HTML_CALL like '%jsp/fnd/fndhelputil.jsp%' or
fun.WEB_HTML_CALL like '%ieuwplist.jsp%' or
fun.WEB_HTML_CALL like '%jtffmlqq.jsp%' or
fun.WEB_HTML_CALL like '%jtffmltm.jsp%' or
fun.WEB_HTML_CALL like '%weboam/oam/diagnostics/createTest%')
order by 1,2
/


prompt
prompt =========================================
prompt HTML pages via grants
prompt =========================================
select distinct incrns.name user_name,wur.role_name, fff.function_name,fff.user_function_name, fff.WEB_HTML_CALL webcall
from fnd_grants fg,
wf_user_roles wur,
fnd_compiled_menu_functions fcmf,
fnd_form_functions_vl fff,
wf_roles wr,
wf_users wu,
wf_users incrns
where fg.menu_id = fcmf.menu_id
and fcmf.function_id = fff.function_id
and fg.grantee_type in ('USER', 'GROUP')
and fg.grantee_key = wur.role_name
and wur.role_name = wr.name
and wur.role_orig_system = wr.orig_system
and wur.role_orig_system_id = wr.orig_system_id
and wur.user_name = wu.name
and wur.user_orig_system = wu.orig_system
and wur.user_orig_system_id = wu.orig_system_id
and wu.parent_orig_system = incrns.parent_orig_system
and wu.parent_orig_system_id = incrns.parent_orig_system_id
and incrns.orig_system in ('FND_USR', 'PER')
and sysdate between fg.start_date and nvl(fg.end_date, sysdate+1)
and sysdate between nvl(wur.start_date, sysdate-1)
and nvl(wur.expiration_date, sysdate+1)
and sysdate between nvl(wr.start_date, sysdate-1)
and nvl(wr.expiration_date, sysdate+1)
and sysdate between nvl(wu.start_date, sysdate-1)
and nvl(wu.expiration_date, sysdate+1)
and sysdate between nvl(incrns.start_date, sysdate-1)
and nvl(incrns.expiration_date, sysdate+1)
and fff.function_name in
(
select fun.function_name
from fnd_form_functions_vl fun
where
fun.WEB_HTML_CALL like '%/oracle/apps/ame/dashboard/webui/BusinessDashboardPG' or
fun.WEB_HTML_CALL like '%/oracle/apps/ame/admin/webui/AdminDashboardPG' or
fun.WEB_HTML_CALL like '%/oracle/apps/ota/admin/content/webui/LOEditPG' or
fun.WEB_HTML_CALL like '%/oracle/apps/ota/admin/adapter/webui/CmiAdapterEditPG' or
fun.WEB_HTML_CALL like '%/oracle/apps/ams/list/webui/DataSrcLOVDefnPG%' or
fun.WEB_HTML_CALL like '%/oracle/apps/ams/list/webui/ListCustomSQLPG%' or
fun.WEB_HTML_CALL like '%/oracle/apps/ams/list/webui/QueryTemplParamSQLPG%' or
fun.WEB_HTML_CALL like '%/oracle/apps/fnd/profile/webui/FndProfileCreatePG%' or
fun.WEB_HTML_CALL like '%/oracle/apps/fnd/profile/webui/FndProfileDefinePG%' or
fun.WEB_HTML_CALL like '%/oracle/apps/fnd/profile/webui/FndProfileSummaryPG%' or
fun.WEB_HTML_CALL like '%/oracle/apps/fnd/profile/webui/FndProfileUpdatePG%' or
fun.WEB_HTML_CALL like '%/oracle/apps/fnd/security/menufunction/webui/FunctionCreatePG%' or
fun.WEB_HTML_CALL like '%/oracle/apps/fnd/security/menufunction/webui/FunctionCreateDetailsPG%' or
fun.WEB_HTML_CALL like '%/oracle/apps/fnd/security/menufunction/webui/FunctionSearchPG%' or
fun.WEB_HTML_CALL like '%/oracle/apps/fnd/security/menufunction/webui/FunctionUpdatePG%' or
fun.WEB_HTML_CALL like '%ibuaugsp.jsp%' or
fun.WEB_HTML_CALL like '%ibuhacnt.jsp%' or
fun.WEB_HTML_CALL like '%/oracle/apps/fnd/security/objects/webui/ObjFindMainPG%' or
fun.WEB_HTML_CALL like '%/oracle/apps/fnd/security/objects/webui/ObjDetailsPG%' or
fun.WEB_HTML_CALL like '%/oracle/apps/fnd/security/objects/webui/ObjInsetUpdatePG%' or
fun.WEB_HTML_CALL like '%jsp/fnd/fndhelputil.jsp%' or
fun.WEB_HTML_CALL like '%ieuwplist.jsp%' or
fun.WEB_HTML_CALL like '%jtffmlqq.jsp%' or
fun.WEB_HTML_CALL like '%jtffmltm.jsp%' or
fun.WEB_HTML_CALL like '%weboam/oam/diagnostics/createTest%'
)
order by 1,2
/




prompt
prompt =========================================
prompt Users with JTF Roles and Permissions
prompt =========================================


REM ----------------------------------------------------------------------
REM Users with JTF_SYSTEM_ADMIN_ROLE Role
REM ----------------------------------------------------------------------


select PRINCIPAL_NAME "JTF_SYSTEM_ADMIN_ROLE Users" from JTF_AUTH_PRINCIPALS_B
where JTF_AUTH_PRINCIPAL_ID in
(select jtfpmap.JTF_AUTH_PRINCIPAL_ID
from JTF_AUTH_PRINCIPAL_MAPS jtfpmap,JTF_AUTH_PRINCIPALS_B jtfpb
where jtfpmap.JTF_AUTH_PARENT_PRINCIPAL_ID=jtfpb.JTF_AUTH_PRINCIPAL_ID
and jtfpb.PRINCIPAL_NAME='JTF_SYSTEM_ADMIN_ROLE')
/


REM ----------------------------------------------------------------------
REM Users with JTF_FM_ADMIN Role
REM ----------------------------------------------------------------------


select PRINCIPAL_NAME "JTF_FM_ADMIN Role Users" from JTF_AUTH_PRINCIPALS_B
where JTF_AUTH_PRINCIPAL_ID in
(select jtfpmap.JTF_AUTH_PRINCIPAL_ID
from JTF_AUTH_PRINCIPAL_MAPS jtfpmap,JTF_AUTH_PRINCIPALS_B jtfpb
where jtfpmap.JTF_AUTH_PARENT_PRINCIPAL_ID=jtfpb.JTF_AUTH_PRINCIPAL_ID
and jtfpb.PRINCIPAL_NAME='JTF_FM_ADMIN')
/


REM ----------------------------------------------------------------------
REM Users with CS_Assoc_Ext_Obj_To_Sol Permission
REM ----------------------------------------------------------------------


SELECT PRINCIPAL_NAME "CS_Assoc_Ext_Obj_To_Sol Users" from JTF_AUTH_PRINCIPALS_B where JTF_AUTH_PRINCIPAL_ID
in
(select jtfpmap.JTF_AUTH_PRINCIPAL_ID from JTF_AUTH_PRINCIPAL_MAPS jtfpmap,JTF_AUTH_ROLE_PERMS jtfarp, JTF_AUTH_PERMISSIONS_B jtfperb
where
jtfpmap.JTF_AUTH_PARENT_PRINCIPAL_ID=jtfarp.JTF_AUTH_PRINCIPAL_ID and
jtfarp.JTF_AUTH_PERMISSION_ID=jtfperb.JTF_AUTH_PERMISSION_ID and
jtfperb.PERMISSION_NAME ='CS_Assoc_Ext_Obj_To_Sol'
)
/


REM ----------------------------------------------------------------------
REM Users with JTF_ADMIN_PERM Permission
REM ----------------------------------------------------------------------


SELECT PRINCIPAL_NAME "JTF_ADMIN_PERM Users" from JTF_AUTH_PRINCIPALS_B where JTF_AUTH_PRINCIPAL_ID
in
(select jtfpmap.JTF_AUTH_PRINCIPAL_ID from JTF_AUTH_PRINCIPAL_MAPS jtfpmap,JTF_AUTH_ROLE_PERMS jtfarp, JTF_AUTH_PERMISSIONS_B jtfperb
where
jtfpmap.JTF_AUTH_PARENT_PRINCIPAL_ID=jtfarp.JTF_AUTH_PRINCIPAL_ID and
jtfarp.JTF_AUTH_PERMISSION_ID=jtfperb.JTF_AUTH_PERMISSION_ID and
jtfperb.PERMISSION_NAME ='JTF_ADMIN_PERM'
)
/

 Check to see if "Server Security" is enabled:

set serveroutput on
declare
l_status varchar2(30);
begin
dbms_output.put_line('Server Security Status');
dbms_output.put_line('-----------------------------');

select 'Server Security is on'
into l_status
from FND_NODES
where server_address = '*'
and server_id='SECURE';

dbms_output.put_line(l_status);

exception
when no_data_found then --server security is off
l_status := 'ERROR:Server Security is off';
dbms_output.put_line(l_status);
when others then
raise;
end;
/

Show if EBS has been configured for HTTPS:

select decode(UPPER(SUBSTR(FND_WEB_CONFIG.PROTOCOL,1,5)),
'HTTPS','SSL/TLS is enabled',
'WARNING: SSL/TLS is not enabled') "SSL Mode"
from dual;



List the default (seeded) applications users that still have their default passwords:
REM You should change all the default passwords, even if the user is "END_DATEd".  
REM Note that this will not list shipped accounts that cannot be logged into 

col "Apps Users - Default Passwords" format a50
select USER_NAME "Apps Users - Default Passwords" from (
select fnd_web_sec.validate_login('AME_INVALID_APPROVER','WELCOME') R, 'AME_INVALID_APPROVER' USER_NAME from dual
union ALL 
select fnd_web_sec.validate_login('ANONYMOUS','welcome') R, 'ANONYMOUS' USER_NAME from dual
union ALL 
select fnd_web_sec.validate_login('APPSMGR','C') R, 'APPSMGR' USER_NAME from dual
union ALL 
select fnd_web_sec.validate_login('ASGADM','ASGADM') R, 'ASGADM' USER_NAME from dual
union ALL 
select fnd_web_sec.validate_login('ASGADM','welcome') R, 'ASGADM' USER_NAME from dual
union ALL 
select fnd_web_sec.validate_login('ASGUEST','welcome') R, 'ASGUEST' USER_NAME from dual
union ALL 
select fnd_web_sec.validate_login('AUTOINSTALL','DATAMERGE') R, 'AUTOINSTALL' USER_NAME from dual
union ALL 
select fnd_web_sec.validate_login('GUEST','ORACLE') R, 'GUEST' USER_NAME from dual
union ALL 
select fnd_web_sec.validate_login('IBEGUEST','IBEGUEST2000') R, 'IBEGUEST' USER_NAME from dual
union ALL 
select fnd_web_sec.validate_login('IBE_ADMIN','MANAGER') R, 'IBE_ADMIN' USER_NAME from dual
union ALL 
select fnd_web_sec.validate_login('IBE_GUEST','WELCOME') R, 'IBE_GUEST' USER_NAME from dual
union ALL 
select fnd_web_sec.validate_login('IEXADMIN','COLLECTIONS') R, 'IEXADMIN' USER_NAME from dual
union ALL 
select fnd_web_sec.validate_login('IRC_EMP_GUEST','WELCOME') R, 'IRC_EMP_GUEST' USER_NAME from dual
union ALL 
select fnd_web_sec.validate_login('IRC_EXT_GUEST','WELCOME') R, 'IRC_EXT_GUEST' USER_NAME from dual
union ALL 
select fnd_web_sec.validate_login('MOBADM','C') R, 'MOBADM' USER_NAME from dual
union ALL 
select fnd_web_sec.validate_login('MOBDEV','C') R, 'MOBDEV' USER_NAME from dual
union ALL 
select fnd_web_sec.validate_login('MOBILEADM','MOBILEADM') R, 'MOBILEADM' USER_NAME from dual
union ALL 
select fnd_web_sec.validate_login('MOBILEADM','welcome') R, 'MOBILEADM' USER_NAME from dual
union ALL 
select fnd_web_sec.validate_login('OP_CUST_CARE_ADMIN','OP_CUST_CARE_ADMIN') R, 'OP_CUST_CARE_ADMIN' USER_NAME from dual
union ALL 
select fnd_web_sec.validate_login('OP_SYSADMIN','OP_SYSADMIN') R, 'OP_SYSADMIN' USER_NAME from dual
union ALL 
select fnd_web_sec.validate_login('PORTAL30','PORTAL30') R, 'PORTAL30' USER_NAME from dual
union ALL 
select fnd_web_sec.validate_login('PORTAL30','portal30_new') R, 'PORTAL30' USER_NAME from dual
union ALL 
select fnd_web_sec.validate_login('PORTAL30_SSO','portal30_sso_new') R, 'PORTAL30_SSO' USER_NAME from dual
union ALL 
select fnd_web_sec.validate_login('SYSADMIN','SYSADMIN') R, 'SYSADMIN' USER_NAME from dual
union ALL 
select fnd_web_sec.validate_login('WIZARD','????UE:?H0UA}?K') R, 'WIZARD' USER_NAME from dual
union ALL 
select fnd_web_sec.validate_login('XML_USER','WELCOME') R, 'XML_USER' USER_NAME from dual
) where R='Y'
order by 1
/