Oracle Apps DBA stuff: October 2013

Wednesday, October 30, 2013

Users Intermittently Running Into 'Your session has expired' when using RAC database

APPLIES TO:

Oracle Applications Technology Stack - Version 11.5.10.0 to 12.1.3 [Release 11.5 to 12.1]
Information in this document applies to any platform.

SYMPTOMS

Users working with Oracle Applications are intermittently redirected to Login page which shows the following error:

Error
Your session has expired.

This error is raised while actively performing actions and happens before period set for profile ICX: Session Timeout.

CHANGES

Problem may start after implementation or addition of RAC node(s) for the Applications database.

CAUSE

Summary:

The error is raised when there is a difference in OS (date) time between hosts running the RAC nodes and the difference is larger than profile 'ICX: Session timeout'.

Explanation:

All user actions update the session record in table ICX_SESSIONS with the new 'status'. Th column LAST_CONNECT indicates the moment of last activity. For a new activity the LAST_CONNECT is used to check if the session was idle longer than 'ICX: Session timeout'. If so the user must log in again to re-validate the session.
If there is a significant difference in OS time between RAC nodes used this will introduce a problem best explained in a simple example. The following scenario illustrates why the 'Your session has been expired.' is raised right after user provided the credentials to log in on a Release 12 environment.
Assumptions:

Profile ICX: Session Timeout = 30 minutes
Time RAC Node 1 = 02:02:48
Time RAC Node 2 = 02:46:43

Remark: Note that the time difference between the RAC hosts is 44 minutes. This is longer than the Session timeout.

Sequence of events

1. The user requests the Login page. This starts the session and uses the MainLoginPG application module.

The MainLoginPG uses a JDBC connection from the pool which connects to the database via RAC Node 1
A new record is created in ICX_SESSIONS table
The LAST_CONNECT column is based on the SYSDATE giving 02:02:48 (derived from OS time of RAC Node 1)

2. After 30 seconds the user submits the username/password.

If validation succeeds the ICX_SESSION record is updated
- USER_ID changes from 6 (GUEST) to ID from the user logging in
- LAST_CONNECT is updated with the time stamp of the login action

3. After the validation the session continues to render the HOMEPAGE. This uses the HomePG application module

The JDBC connection is borrowed by instance of an application module
With the change of application module from MainLoginPG to HomePG also the JDBC connection changes
This JDBC connection connects to the database via RAC Node 2 being 44 minutes ahead of RAC Node 1

4. As part of the page rendering processing a validation is done to check is the last activity in the session was longer ago than the session time-out. If so the user has to loging again to re-establish the session.

For the validation it's expected that LAST_CONNECT 02:02:48 is validated against a SYSDATE around 02:03:18 (OS time on RAC Node 1)
However the JDBC connection now connects via RAC Node 2 it's validated against the SYSDATE 02:47:13 (OS time on RAC Node 2)
The difference between 02:02:48 and 02:47:13 = 44 minutes
Since 44 minutes > 30 minutes timeout the validation fails and returns the error 'Your session has expired' !!!

The above is a very simply cases. In reality it happens that a number of actions keep using JDBC connection via RAC Node1 or start via RAC Node 2 and work as expected. However as soon as JDBC connection changes from RAC Node 1 to RAC Node 2 this triggers the error. This explains the intermittent nature where in some cases the error is raised immediately, while in other cases it takes some navigation steps.
The error is prevented when all the RAC nodes used have the OS time synchronized. In this case the LAST_CONNECT is validated against the 'same' SYSDATE independent of RAC Node connected to.

SOLUTION

Synchronize the time on all the RAC Nodes. Follow the following sequence of actions:

Stop all Applications Middle Tier Services
Stop all RAC Clusterware Services
Synchronize the time between all RAC nodes (for example using NTP (*))
Restart the RAC Clusterware Services
Restart the Applications Middle Tier Services
Test to confirm the error is not raised anymore

(*) Synchronizing the time between nodes is an OS administrative task. Please check with the OS Administrator for assistance.

Thursday, October 24, 2013

Hierarchy Functionality In Supplier Data Librarian (Super User) Has References To Customer Screens R12.1.3

Hierarchy Functionality In Supplier Data Librarian (Super User) Has References To Customer Screens

In this Document
Symptoms
Cause
Solution
References
APPLIES TO:

Oracle Supplier Hub - Version 12.1.3 and later
Information in this document applies to any platform.
SYMPTOMS

On : 12.1.3 version, Set Up

ACTUAL BEHAVIOR
---------------
The Hierarchy functionality in the Supplier Data Librarian (Super User) has references to Customer screens.

EXPECTED BEHAVIOR
-----------------------
Expect to see Supplier context instead of customer context or reference

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1.Supplier Data Librarian Super User > Hierarchies
2.Create button to create a hierarchy
3.Screen is functioning correctly but has switched to Customer context.
4.Incorrect context remains after creation
5.Clicking on the Party Name link, opens a Customer screen

CAUSE

Bug 13928898 - HIERARCHY FUNCTIONALITY IN SUPPLIER DATA LIBRARIAN (SUPER USER) HAS REFERENCES T

The Hierarchy functionality in the Supplier Data Librarian (Super User) has references to Customer screens. Tabs will switch to Customer context when creating hierarchies.

Context Issue

SOLUTION

To implement the solution, please execute the following steps:

1. Download and review the readme and pre-requisites for Patch 13928898:R12.POS.B

2. Ensure that you have taken a backup of your system before applying
the recommended patch.

3. Apply the patch in a test environment.

4. Confirm the following file versions:

$JAVA_TOP/oracle/apps/pos/sdh/hierarchy/server PosHierarchyAM.xml 120.0.12010000.1
$JAVA_TOP/oracle/apps/pos/sdh/hierarchy/server PosHierarchyAMImpl.java 120.0.12010000.1
$JAVA_TOP/oracle/apps/pos/sdh/hierarchy/webui PosEditRelationshipCO.java 120.0.12010000.1
$JAVA_TOP/oracle/apps/pos/sdh/hierarchy/webui PosHierarchyAddChildCO.java 120.0.12010000.1
$JAVA_TOP/oracle/apps/pos/sdh/hierarchy/webui PosHierarchyCopyCO.java 120.0.12010000.1
$JAVA_TOP/oracle/apps/pos/sdh/hierarchy/webui PosHierarchyCreateCO.java 120.0.12010000.1
$JAVA_TOP/oracle/apps/pos/sdh/hierarchy/webui PosHierarchyMoveCO.java 120.0.12010000.1
$JAVA_TOP/oracle/apps/pos/sdh/hierarchy/webui PosHierarchySearchCO.java 120.0.12010000.2
$JAVA_TOP/oracle/apps/pos/sdh/hierarchy/webui PosHierarchyViewCO.java 120.0.12010000.2
$POS_TOP/mds/sdh/hierarchy/webui PosEditRelationshipPG.xml 120.0.12010000.1
$POS_TOP/mds/sdh/hierarchy/webui PosHierarchyAddChildPG.xml 120.0.12010000.1
$POS_TOP/mds/sdh/hierarchy/webui PosHierarchyCopyPG.xml 120.0.12010000.1
$POS_TOP/mds/sdh/hierarchy/webui PosHierarchyCreatePG.xml 120.0.12010000.1
$POS_TOP/mds/sdh/hierarchy/webui PosHierarchyMovePG.xml 120.0.12010000.1
$POS_TOP/mds/sdh/hierarchy/webui PosHierarchySearchPG.xml 120.0.12010000.1
$POS_TOP/mds/sdh/hierarchy/webui PosHierarchyViewPG.xml 120.0.12010000.1
$POS_TOP/patch/115/import/US posmenu.ldt 120.1.12010000.46
$POS_TOP/patch/115/sql possmmenu.sql 120.0.12010000.6

Tuesday, October 22, 2013

R12: Unable To Relink MSC Executables on On OEL5/RHEL5

R12: Unable To Relink MSC Executables on On OEL5/RHEL5 (Doc ID 781553.1)

In this Document
Symptoms
Cause
Solution
References
APPLIES TO:

Oracle Advanced Supply Chain Planning - Version 12.0 to 12.1.3 [Release 12 to 12.1]
Linux x86-64
Linux x86
*** Checked for relevance on 06-May-2011 ***

SYMPTOMS

When attempting to relinking any MSC executables via adadmin or manually the following error is visible in the log file :

Relinking module 'MSCNSP' in product msc ...

make -f /u01/oracle/EMMA/apps/apps_st/appl/admin/EMMA/out/link_msc_26274.mk
/u01/oracle/EMMA/apps/apps_st/appl/msc/12.0.0/bin/MSCNSP

Starting link of msc executable 'MSCNSP' on Tue Feb 10 10:16:53 CET 2009
g++ -L/u01/oracle/EMMA/apps/tech_st/10.1.2/lib
-L/u01/oracle/EMMA/apps/tech_st/10.1.2/lib/stubs -lclntsh -o
/u01/oracle/EMMA/apps/apps_st/appl/msc/12.0.0/bin/MSCNSP
/u01/oracle/EMMA/apps/apps_st/appl/msc/12.0.0/lib/mscnsp.o \
/u01/oracle/EMMA/apps/apps_st/appl/msc/12.0.0/lib/libmsc.a
/u01/oracle/EMMA/apps/apps_st/appl/mso/12.0.0/lib/libmso.a
/u01/oracle/EMMA/apps/apps_st/appl/msc/12.0.0/lib/libmsc.a
/u01/oracle/EMMA/apps/apps_st/appl/sht/12.0.0/lib/ilog/6.2/libschedule.a
/u01/oracle/EMMA/apps/apps_st/appl/sht/12.0.0/lib/ilog/6.2/libsolveriim.a
/u01/oracle/EMMA/apps/apps_st/appl/sht/12.0.0/lib/ilog/6.2/libconcertext.a
/u01/oracle/EMMA/apps/apps_st/appl/sht/12.0.0/lib/ilog/6.2/libsolver.a
/u01/oracle/EMMA/apps/apps_st/appl/sht/12.0.0/lib/ilog/6.2/libconcert.a
/u01/oracle/EMMA/apps/apps_st/appl/sht/12.0.0/lib/ilog/6.2/libilocplex.a
/u01/oracle/EMMA/apps/apps_st/appl/sht/12.0.0/lib/ilog/6.2/libcplex.a
/u01/oracle/EMMA/apps/apps_st/appl/sht/12.0.0/lib/ilog/6.2/libcplex.a \

/u01/oracle/EMMA/apps/apps_st/appl/fnd/12.0.0/lib/libfnd.a -lsql10 -lclntsh
`cat /u01/oracle/EMMA/apps/tech_st/10.1.2/lib/ldflags` -lnsslb10
-lncrypt10 -lnsgr10 -lnzjs10 -ln10 -lnnz10 -lnl10 -lnro10 `cat
/u01/oracle/EMMA/apps/tech_st/10.1.2/lib/ldflags` -lnsslb10 -lncrypt10
-lnsgr10 -lnzjs10 -ln10 -lnnz10 -lnl10 -lclient10 -lnnetd10 -lvsn10
-lcommon10 -lgeneric10 -lmm -lcore10 -lxml10 -lunls10 -lsnls10 -lnls10
-lcore10 -lnls10 `cat /u01/oracle/EMMA/apps/tech_st/10.1.2/lib/ldflags`
-lnsslb10 -lncrypt10 -lnsgr10 -lnzjs10 -ln10 -lnnz10 -lnl10 -lnro10 `cat
/u01/oracle/EMMA/apps/tech_st/10.1.2/lib/ldflags` -lnsslb10 -lncrypt10
-lnsgr10 -lnzjs10 -ln10 -lnnz10 -lnl10 -lclient10 -lnnetd10 -lvsn10
-lcommon10 -lgeneric10 -lcore10 -lxml10 -lunls10 -lsnls10 -lnls10 -lcore10
-lnls10 -lclient10 -lnnetd10 -lvsn10 -lcommon10 -lgeneric10 -lcore10
-lxml10 -lunls10 -lsnls10 -lnls10 -lcore10 -lnls10 `cat
/u01/oracle/EMMA/apps/tech_st/10.1.2/lib/sysliblist` -ldl -lm -lpthread
/u01/oracle/EMMA/apps/apps_st/appl/sht/12.0.0/lib/stubs/libstdc++.so
/u01/oracle/EMMA/apps/apps_st/appl/sht/12.0.0/lib/stubs/libgcc_s.so.1
`.gnu.linkonce.t._ZN17SCOProfileOptionsC1Ev' referenced in section `.rodata'
of /u01/oracle/EMMA/apps/apps_st/appl/mso/12.0.0/lib/libmso.a(msopomdl.o):
defined in discarded section `.gnu.linkonce.t._ZN17SCOProfileOptionsC1Ev' of
/u01/oracle/EMMA/apps/apps_st/appl/mso/12.0.0/lib/libmso.a(msopomdl.o)
collect2: ld returned 1 exit status
make: *** [/u01/oracle/EMMA/apps/apps_st/appl/msc/12.0.0/bin/MSCNSP] Error 1
Done with link of msc executable 'MSCNSP' on Tue Feb 10 10:16:55 CET 2009

Relink of module "MSCNSP" failed.
See error messages above (also recorded in log file) for possible
reasons for the failure. Also, please check that the Unix userid
running adrelink has read, write, and execute permissions
on the directory /u01/oracle/EMMA/apps/apps_st/appl/msc/12.0.0/bin,
and that there is sufficient space remaining on the disk partition
containing your Oracle Applications installation.

CAUSE

The issue is caused because an incorrect package binutils-2.17.50.0.6-6.el5 is installed on OEL5/RHEL 5 32-bit. This can also occur on the 64-bit version.

The requirements for Linux (32-bit) are similar and documented in Note 402310.1 Oracle Applications Installation and Upgrade Notes Release 12 (12.0.4) for Linux (32-bit) under the Required Packages for Oracle Enterprise Linux 5* and Red Hat Enterprise Linux 5* (base and Advanced Platform):

The following i386 packages are not part of the OS distribution media and must be downloaded separately (from http://oss.oracle.com/projects/compat-oracle/files/Enterprise_Linux for both OEL 5 and RHEL 5) and installed manually:

openmotif21-2.1.30-11.EL5.i3861
xorg-x11-libs-compat-6.8.2-1.EL.33.0.1.i386
binutils-2.17.50.0.6-6.0.1.i3862

SOLUTION

-- To implement the solution, please execute the following steps:

Download the binutils-2.17.50.0.6-6.0.1.x86_642 package if using OEL 5.2 (64-bit) or binutils-2.17.50.0.6-6.0.1.i386 if using OEL 5.2 (32-bit) from: http://oss.oracle.com/projects/compat-oracle/files/Enterprise_Linux/
Install binutils-2.17.50.0.6-6.0.1.el5.x86_64.rpm or binutils-2.17.50.0.6-6.0.1.el5.i386.rpm using the following command:

rpm -U binutils-2.17.50.0.6-6.0.1.el5.x86_64.rpm
or
rpm -U binutils-2.17.50.0.6-6.0.1.el5.i386.rpm

If a higher version of the binutils has already been installed, downgrade using the following command:

rpm -Uvh --oldpackage binutils-2.17.50.0.6-6.0.1.el5.i386.rpm
or
rpm -Uvh --oldpackage binutils-2.17.50.0.6-6.0.1.el5.x86_64.rpm
You can use a command like the following to check the version:

rpm -qa |grep binutils
Retest the issue.
Migrate the solution as appropriate to other environments.
See Important notes below if you still encounter a failure - especially Note 1273390.1
IMPORTANT NOTES:

Failure in OEL 6, REL6 or SLES11 can also be expected as these platforms are not currently supported for VCP applications - Refer to (Note 761566.1)
Unable to relink MSC/MSO executables on OEL/RHEL5 and also RedHat 4.9 (Note 1273390.1) - this shows patches for adrelinknew.sh OR manual updates to resolve relinking error
ADRELINK of MSC and MSO executables fail (Document 1492763.1) - this is specific to OS setups and should be reviewed if this note and 1273390.1 do not resolve the failure.
12: MSC Modules Error Out When Relinking with Adrelink (Note 1345788.1) - this is same as Note 1273390.1

Why is Workflow Ignoring Skip Approval And Proceeding To Approval? R12.1.1

In this Document
Goal
Solution
References

APPLIES TO:

Oracle Credit Management - Version 12.1.1 and later
Information in this document applies to any platform.
***Checked for relevance on 27-MAY-2013***
GOAL

You have set up an automation rule to SKIP_APPROVAL and close the case folder with "No Change". However, workflow is ignoring the set up of skip approval and hence proceeding to the approval process and failing.

SOLUTION

Development has made a code change. The Skip Approval automated rule was retrieving the limit currency rather than the transaction currency. The transaction currency matches the scoring model currency to which the automation rule is associated.

For 12.1.1 and 12.1.2, the fixed file is patch/115/sql ARCMGWFB.pls 120.31.12010000.26 contained in 9871620:R12.OCM.B. Please contact Oracle Support to get this patch.

REFERENCES

BUG:9871620 - REW: SKIP APPROVAL IS NOT BEING HONOURED : SYSTEM GOING FOR APPROVAL PROCESS

Monday, October 21, 2013

Memory/Resources Errors On System That Has Abundent Resources Running Oracle Linux 6

Memory/Resources Errors On System That Has Abundent Resources Running Oracle Linux 6 (Doc ID 1578925.1)

In this Document
Symptoms
Changes
Cause
Solution
References

APPLIES TO:

Oracle Application Install - Version 12.1.3 to 12.1.3 [Release 12.1]
Linux x86-64
SYMPTOMS

Adpatch recommends the use of a high number of workers, ie 128. The machine is a blade server running 4 processors each with 8 cores and 2 threads per core. The machine has 128 Gb of RAM.

Now trying to apply a patch and when running XDOLoader.class the system is erroring out indicating that it does not have enough resources.

Reducing the number of workers to 8 fixed the issue, but clearly something is wrong since the machine should run 128 workers without problems

Checking a pid error log file from an adworker.log ie /oraapp/r12/oadev1/apps/apps_st/appl/admin/OADEV1/out/hs_err_pid14786.log shows the following

rlimit: STACK 8192k, CORE 0k, NPROC 1024, NOFILE 65535, AS infinity

But the limit.conf contains the values specified by the Installation note Oracle E-Business Suite Installation and Upgrade Notes Release 12 (12.1.1) for Linux x86-64 (Doc ID 761566.1)

* hard nofile 65535
* soft nofile 4096
* hard nproc 16384
* soft nproc 2047

CHANGES

Migration to RHEL6/OEL6

CAUSE

RHEL6 and OEL6 by inheritance have a flaw in which a user can override limits.conf by placing a file in /etc/security/limits.d. The PAM Package put an obsolete value in nproc under limits.d which overrode the oracle settings causing the system to not have enough processes available.

In this case the adworkers were taking the value 1024 from limits.d instead of 2047 from /limits.conf, that's the lack of processes reason.

SOLUTION

1. Change the following:

cd /etc/security/limits.d/
ln -s ../limits.conf 99-zzzzzzzzzzzzzz.conf

2. Re-test.

Profile options in R12

How To Check If a Profile Option Is Set In Oracle Applications? [ID 470102.1]

How to Search all of the Profile Options for a Specific Value [ID 282382.1]

How To List E-Business Suite Profile Option Values For All Levels Using SQLPlus [ID 201945.1]

Script To List The Values Of A Profile Option At All Levels [ID 803587.1]

How to Search all of the Profile Options for a Specific Value [ID 282382.1]

How To Find All Users With A Particular Profile Option Set? [ID 367926.1]

How to Change Profile Option Value Without Forms? [ID 943710.1]

Saturday, October 19, 2013

su: cannot set user id: Resource temporarily unavailable

Today I had a customer complaining about his DB that nobody can login anymore. The number of max sessions (in the DB) was set to 4096 and there were about 2048 current seesion on the DB. So, what was wrong?
I decided to login as root into the DB server which worked wihtout any issue. The system it self was OK, enough memory free, no filled volumes etc. Then I tried to switch to the oracle user:

# su - oracle
su: cannot set user id: Resource temporarily unavailable

OK, it seems that the system cannot create any more sessions for the oracle user. A quick check about the number of operating system processes gave me the final clue:

# ps -U oracle | wc -l
2048

The number of processes was 2048 and this value can be set in the file /etc/security/limits.conf:

# cat /etc/security/limits.conf
...
oracle soft nproc 2048
...

I just changed the value for the number of processes (soft nproc) to 4096 (the same value for the maximum number of sessions inside the DB) and the customer could work normally again.

ORA-27300: OS System Dependent Operation:fork Failed With Status: 11

ORA-27300: OS System Dependent Operation:fork Failed With Status: 11

APPLIES TO:

Oracle Database - Enterprise Edition - Version 11.2.0.3 and later
Linux x86-64
SYMPTOMS

After a System restart getting errors in the Database alert logs:

...
ORA-27300: OS system dependent operation:fork failed with status: 11
ORA-27301: OS failure message: Resource temporarily unavailable
ORA-27302: failure occurred at: skgpspawn3
Process PZ98 died, see its trace file
...

- This is a 2 node RAC environment with 6 Databases, and only some are having these errors.
- Top shows no concerns with memory or CPU.
- You have restarted all components in the Cluster (Listeners, Database, etc.) and the problem continues.

CHANGES

Increase in user processes on the server.

CAUSE

Low setting for the OS kernel parameter pid_max.

Root Cause Due to an increased process list average size, the kernel fails to allocate a new PID NUMBER, because its assignable range for PID numbers is temporarily exhausted; The fork(2) system call eventually returns -EAGAIN (11) when it fails to alloc a pid number, as can be verified at the following kernel/fork.c excerpt:1467 long do_fork(unsigned long clone_flags,[...]1475 struct pid *pid = alloc_pid();1476 long nr;1477 1478 if (!pid)1479 return -EAGAIN;[...]

SOLUTION

1. Increase sysctl tunable kernel.pid_max, in the same proportion as the allowed number of user processes (ulimit -u) was increased, plus some extra value for Operating System stability (approx. 8192), when configuring the system to fit the database sizing recommendations.

2. Verifying the actual limits set in /etc/security/limits.conf:[root@localhost ~]# cat /etc/security/limits.conf[...]oracle soft nproc 131072 oracle hard nproc 131072[...]

3. Setting the value found to 'nproc' limits as kernel.pid_max plus extra 8192 buffer (in order to make this change permanent, write it to the /etc/sysctl.conf file):[root@localhost ~]# sysctl -w kernel.pid_max=139264 kernel.pid_max = 139264

Thursday, October 17, 2013

Generate WSDL Button Not Visible On Integration Repository Pages in 12.1.1 Integrated SOA Gateway

To Bottom

Modified:Jun 2, 2012

Type:PROBLEM

In this Document

Symptoms

Cause

Solution

APPLIES TO:

Oracle Workflow Cartridge - Version 12.0.6 and later
Information in this document applies to any platform.
Please note the version affected is 12.1.1 and product is Integrated SOA Gateway. Due to technical issues the correct version and product name doesn't reflect above. This will be modified very soon.

Checked for relevance on 02-JUN-2012

SYMPTOMS

After installing EBS 12.1.1 and configuring Integrated SOA gateway as per Note 565922.1 when trying to generate WSDL for public API's from the Integration Repository pages, don't see the "Generate WSDL" button available to generate webservices. The responsibility "Integration SOA Gateway" is assigned to the user however this button is missing.

NOTE: SYSADMIN user is able to see the "Generate WSDL" button.

CAUSE

The reason the "Generate WSDL" button cannot be seen is because a missing step as mentioned below is mssing:

Integrated SOA Gateway leverages the RBAC security concept of Oracle User Management to secure access to various interface types. There are 3 seeded roles which can be assigned to end users to provide different access to the ISG system. These are :

Integration Repository Administrator
System Integration Developer
System Integration Analyst

As shown in figure below based on these roles you will get access to different features . With default installation of 12.1.1 these roles are not assigned to the end user. You have to manually assign these to gain additional access.

SYSADMIN has access to all three roles by default.

SOLUTION

1. Log into Oracle Applications using sysadmin/.
2. Select the User Management responsibility in the Navigator.
3. Click the Users link from the navigation menu to open the User Maintenance window.
4. Locate the user trying to use.
5. Assign the role and select the "Irep Administrator" role.
6. Bounce the middle tier because after assigning roles they don't take affect unless the cache is cleared and bounce middle tier.
7. Now log in as the user and access the " Integrated SOA Gateway" responsibility ->Integration Repository and should see the Generate WSDL button.

Tuesday, October 15, 2013

apps_ful._report

#--
#-- Program : Apps Health Checks
#--
#-- Description : oracle_apps_full_report.sh
#--
#-- Purpose : Run a variety of Oracle Apps Health Checks.
#-- Warning section at beginning should usually return no rows.
#--
#-- Created By : oracle-latest-technology.blogspot.com
#--
#-- Creation Date : Dec 19, 2011
#--
#-- Change History :
#-- ............................................................
#--
#-- ...........................................................................

dt=`date +"%d.%m.%y"`

. /_.env

sqlplus -s <<!
apps/

SET linesize 80
set pagesize 500
SET space 1

#COLUMN db_name new_value dbname noprint
#COLUMN today new_value runtime noprint

spool /apps_health_checkup_$dt.txt

SELECT name db_name FROM v\$database;
SELECT TO_CHAR(sysdate,'YYYY/MM/DD HH24:MI') today FROM dual;

SET pagesize 1000
COLUMN owner FORMAT a8
COLUMN object_type FORMAT a12
COLUMN object_name FORMAT a30
COLUMN created FORMAT a9
COLUMN application_id on heading app_id FORMAT 99999
COLUMN profile_option_id on heading opt_id FORMAT 99999
COLUMN level_value on heading 'level|value' FORMAT 99999
COLUMN prof_value on heading 'Prof|value' FORMAT a15
COLUMN prof_name FORMAT a20 trunc
COLUMN user_prof_name FORMAT a40 trunc
COLUMN user_name FORMAT a10 trunc

--
-- Health Check Logic starts Here
--
PROMPT ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
PROMPT ++ Warning Section: Should see no rows in this section
PROMPT ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
PROMPT
COLUMN obj_name FORMAT a18
COLUMN own FORMAT a5

-- General Warnings:
PROMPT Confirm not in Maintenance Mode - should see no rows
SELECT
pot.user_profile_option_name "Profile Name"
,v.PROFILE_OPTION_VALUE "Profile Value"
,v.LEVEL_ID "Level ID"
,v.LAST_UPDATE_DATE "Last Update Date"
FROM
apps.fnd_profile_options o
, apps.fnd_profile_options_tl pot
, apps.fnd_profile_option_values v
WHERE
o.profile_option_name = pot.profile_option_name
AND o.profile_option_id = v.profile_option_id (+)
AND o.application_id = v.application_id (+)
AND nvl(v.level_id, 10001) = 10001
AND pot.user_profile_option_name = 'Applications Maintenance Mode'
AND v.profile_option_value != 'NORMAL';

/*PROMPT Warn login accounts that should not become locked out like SYSADMIN
PROMPT
COLUMN user_name FORMAT a25
COLUMN creation_date FORMAT a30
COLUMN last_logon_date FORMAT a30
COLUMN password_date FORMAT a3
SELECT user_name, creation_date
, TO_CHAR(last_logon_date, 'DD-Mon-YY') last_logon
--, ENCRYPTED_USER_PASSWORD
, TO_CHAR(password_date, 'DD-Mon-YY') passwd_date
FROM apps.fnd_user
WHERE ENCRYPTED_USER_PASSWORD = 'INVALID'
AND END_date IS NULL
AND USER_NAME IN ('SYSADMIN')
ORDER BY user_name;*/

/*
PROMPT User Security AND Access Issues section:
PROMPT Show any logins whose password is NOT SET to expire in 60 days
COLUMN user_name FORMAT a20
COLUMN encrypted_password FORMAT a20
SELECT user_id "User ID"
, user_name "User Name"
, password_lifespan_days "Password Lifespan Days"
FROM apps.fnd_user
WHERE end_date IS NULL
AND nvl(password_lifespan_days, 0) != 60
AND user_id > 1051;
*/

PROMPT Non-DBAs who have System Administrator or other powerful Responsibilities
COLUMN user_name FORMAT a10
COLUMN description FORMAT a20
COLUMN responsibility_name FORMAT a25
COLUMN end_date FORMAT a20
SELECT fu.user_name "User Name"
,fu.description "Description"
,substr(fr.responsibility_name,1,35) "Responsibility"
,fur.end_date "End Date"
FROM
apps.fnd_user fu,
apps.fnd_user_resp_groups_direct fur,
apps.fnd_responsibility_vl fr,
apps.fnd_application_vl fa
WHERE
fur.user_id = fu.user_id
AND fu.user_name NOT IN ('YOUR_DBA_NAME','SYSADMIN')
AND fur.responsibility_application_id = fa.application_id
AND fur.responsibility_application_id = fr.application_id
AND fur.responsibility_id = fr.responsibility_id
AND fu.end_date is null -- only show active users
AND nvl(fur.end_date, sysdate+1) > sysdate -- only show active resp assignment
AND fr.responsibility_name in ('System Administrator'
,'Application Developer'
,'Application Developer Common Modules'
,'CRM HTML Administration'
,'FICO Sys Admin Conversion'
,'FICO System Administration GUI'
,'Functional Administrator' -- can chg Profiles
,'Functional Developer'
,'Knowledge Administrator' --use FICO instead
,'Oracle Sales Administrator' --use FICO instead
,'Sales Administrator' --use FICO instead
,'SFM System Administrator'
,'TeleSales Administration' --use FICO instead
)
ORDER BY 1;

COL emp_num format a7
COL per_serv_id format 999999

-- Check for terminated employees
PROMPT Terminated employees who still have an active login to Oracle Apps
SELECT DISTINCT(fu.user_name)
, papf.employee_number "Emp No."
, to_char(ppos.actual_termination_date, 'DD-Mon-YY') "Termination Date"
, period_of_service_id "Period Serv ID"
FROM apps.fnd_user fu
, apps.per_all_people_f papf
, apps.per_periods_of_service ppos
WHERE fu.employee_id = papf.person_id
AND papf.person_id = ppos.person_id
AND fu.end_date IS NULL
AND ppos.period_of_service_id = (SELECT MAX(period_of_service_id)
FROM per_periods_of_service
WHERE person_id = papf.person_id)
AND ppos.actual_termination_date < TRUNC(SYSDATE);

PROMPT Show users whose Responsibility roles have early end date in future
-- To remove, Use Define User Form toggle fnd_user end_date by
-- adding end_date, commit, remove user end_date, commit. Some time lag
-- Remove this logic when Oracle no longer end dates resp roles early

COLUMN user_name FORMAT a25
COLUMN user_end_date FORMAT a20
select distinct user_name "User Name"
, user_end_date "End Date"
from apps.wf_local_user_roles
where user_end_date >= sysdate -- in future
and user_end_date <= sysdate + 700 -- within next week
and user_orig_system != 'WF_LOCAL_USERS'
and to_char(user_end_date,'DD-MON-YYYY') != '31-DEC-4712'
order by user_end_date, user_name;

-- Duplicate email addresses have security issues with workflow notifications
-- Two people should not have same email address since one user can get
-- another user's email for instance when doing password reset
COL email_address FORMAT a50
PROMPT Show fnd_users who have same email address
SELECT user_name "User Name"
, email_address "Email Address"
FROM fnd_user WHERE email_address IN (
SELECT
email_address
FROM apps.fnd_user
WHERE email_address is not NULL
AND email_address != 'employee@.com'
AND end_date is null
GROUP BY
email_address
HAVING count(*) > 1);

PROMPT Show active fnd_users tied to the same HR employee (shows person_id)
SELECT
fu.employee_id "Employee ID"
, count(*) "Count"
FROM apps.fnd_user fu
WHERE fu.employee_id is not null -- this is person_id in per_all_people_f
AND fu.end_date is null
GROUP BY fu.employee_id
HAVING count(*) > 1;

PROMPT Workflow Section:
PROMPT

--Show users whose mailpref is QUERY since causes Workflow Notification issues
PROMPT Users whose mail preference is 'do not send' since causes WF notif issues
SELECT fu.user_name
FROM apps.fnd_user_preferences fup
, apps.fnd_user fu
WHERE fup.preference_name = 'MAILTYPE'
AND fup.preference_value = 'QUERY'
AND fup.user_name = fu.user_name
AND fu.end_date IS NULL
ORDER BY fu.user_name;

PROMPT Show any wf adhoc roles that have wrong notification_preference
SELECT notification_preference "Notification Preference"
, COUNT(*) "Count"
FROM apps.wf_local_roles
WHERE orig_system /*= 'WF_LOCAL_ROLES'*/in ('FND_USR','PER')
AND notification_preference NOT IN ('MAILHTML', 'MAILHTM2')
GROUP BY notification_preference;

PROMPT Show if workflow mailer setting PROCESSOR_READ_TIMEOUT_CLOSE has changed
PROMPT to something other than Y. Should not see any rows
SELECT p.parameter_name
, v.last_update_date
, u.user_name
FROM apps.fnd_svc_comp_param_vals v
, apps.fnd_svc_comp_params_b p
, apps.fnd_svc_components c
, apps.fnd_user u
WHERE c.component_type = 'WF_MAILER'
AND c.component_id = v.component_id
AND v.parameter_id = p.parameter_id
AND p.parameter_name = 'PROCESSOR_READ_TIMEOUT_CLOSE'
AND v.parameter_value <> 'Y'
AND v.last_updated_by = u.user_id;

PROMPT Profile Check Section:

PROMPT
-- Confirm Examine function is password protected in Production
-- If profile is Y can use Examine without password PROMPT so unprotected

PROMPT Profile 'Utilities:Diagnostics' for Examine set to unprotected
SELECT
v.profile_option_id "Profile Option ID"
, pot.user_profile_option_name "Prof Name"
, substr(v.profile_option_value, 1,5) "Prof Value"
, v.level_id "Level ID"
, v.level_value "Level Value"
FROM
apps.fnd_profile_options o
, apps.fnd_profile_options_tl pot
, apps.fnd_profile_option_values v
WHERE
o.profile_option_name = pot.profile_option_name
AND o.profile_option_id = v.profile_option_id (+)
AND o.application_id = v.application_id (+)
AND pot.user_profile_option_name like 'Utilities%Diag%'
AND v.profile_option_value != 'N' -- N means password protected
ORDER BY pot.user_profile_option_name;

PROMPT User Level 'Utilities:Diagnostics' Profile Settings for Examine

SELECT
v.profile_option_id
, pot.user_profile_option_name prof_name
, substr(v.profile_option_value, 1,5) prof_value
, v.level_id
, v.level_value
, fu.user_name
FROM
apps.fnd_profile_options o
, apps.fnd_profile_options_tl pot
, apps.fnd_profile_option_values v
, apps.fnd_user fu
WHERE
o.profile_option_name = pot.profile_option_name
AND o.profile_option_id = v.profile_option_id (+)
AND o.application_id = v.application_id (+)
AND nvl(v.level_id, 1001) = 10004 -- user level Profile
AND v.level_value = fu.user_id
AND pot.user_profile_option_name like 'Utilities%Diag%'
AND v.profile_option_value != 'N'
order by pot.user_profile_option_name;

-- List Profile options for personalization related settings
--
PROMPT User level profile for Personalization related Profiles
SELECT
user_name
, pot.user_profile_option_name prof_name
, v.profile_option_value prof_value
FROM
apps.fnd_profile_options o
, apps.fnd_profile_options_tl pot
, apps.fnd_profile_option_values v
, apps.fnd_user fu
WHERE
o.profile_option_name = pot.profile_option_name
AND o.profile_option_id = v.profile_option_id (+)
AND o.application_id = v.application_id (+)
AND level_id = 10004 -- user level option
AND v.level_value = fu.user_id
AND pot.user_profile_option_name in
(
'AMS : Oracle Personalization Enabled'
,'Create Seeded Personalizations'
,'FND: Personalization Region Link Enabled'
,'FND: Personalization Seeding Mode'
,'HR: Enable User Personalization'
,'OSO : Enable Admin Personalization Feature'
,'Personalize Self-Service Defn'
)
ORDER BY
pot.user_profile_option_name
, user_name;

PROMPT Find Personalization related profiles at other levels
PROMPT
PROMPT *** Site level ***

select t.user_profile_option_name, profile_option_value
from apps.fnd_profile_options o
,apps.fnd_profile_option_values v
,apps.fnd_profile_options_tl t
where o.profile_option_id = v.profile_option_id
and o.application_id = v.application_id
and profile_option_value = 'Y'
and start_date_active <= SYSDATE
and nvl(end_date_active,SYSDATE) >= SYSDATE
and o.profile_option_name = t.profile_option_name
and level_id=10001
and (upper(t.user_profile_option_name) like '%PERSONALIZ%'
and upper(t.user_profile_option_name) not like '%PERSONALIZ%WYSIWYG%'
)
order by user_profile_option_name;

PROMPT
PROMPT *** Application level ***

select t.user_profile_option_name, profile_option_value, application_name
from apps.fnd_profile_options o
,apps.fnd_profile_option_values v
,apps.fnd_profile_options_tl t
,apps.fnd_application_tl a
where o.profile_option_id = v.profile_option_id
and o.application_id = v.application_id
and profile_option_value = 'Y'
and start_date_active <= SYSDATE
and nvl(end_date_active,SYSDATE) >= SYSDATE
and o.profile_option_name = t.profile_option_name
and a.application_id = level_value
and level_id=10002
and t.language = a.language
and upper(t.user_profile_option_name) like '%PERSONALIZ%'
order by user_profile_option_name, application_name;

PROMPT
PROMPT *** Responsibility level ***

select t.user_profile_option_name, profile_option_value,
responsibility_name
from apps.fnd_profile_options o
,apps.fnd_profile_option_values v
,apps.fnd_profile_options_tl t
,apps.fnd_responsibility_tl r
where o.profile_option_id = v.profile_option_id
and o.application_id = v.application_id
and profile_option_value = 'Y'
and start_date_active <= SYSDATE
and nvl(end_date_active,SYSDATE) >= SYSDATE
and o.profile_option_name = t.profile_option_name
and responsibility_id = level_value
and level_id=10003
and t.language = r.language
and upper(t.user_profile_option_name) like '%PERSONALIZ%'
order by user_profile_option_name, responsibility_name;

COLUMN prof_value on heading 'Prof|value' FORMAT a5
PROMPT Confirm self service diagnostics are not turned on
SELECT
v.profile_option_id
, pot.user_profile_option_name prof_name
, substr(v.profile_option_value, 1,5) prof_value
, v.level_id
, v.level_value
FROM
apps.fnd_profile_options o
, apps.fnd_profile_options_tl pot
, apps.fnd_profile_option_values v
WHERE
o.profile_option_name = pot.profile_option_name
AND o.profile_option_id = v.profile_option_id (+)
AND o.application_id = v.application_id (+)
AND pot.user_profile_option_name like 'FND%Diag%'
AND nvl(v.level_id, 1001) != 10004 -- skip user level options
ANd v.profile_option_value != 'N' -- when Y then can use ss diagnostics
order by pot.user_profile_option_name;

PROMPT Confirm only DBA and sysadmin users have access to SS Diagnostics
SELECT
v.profile_option_id
, pot.user_profile_option_name prof_name
, substr(v.profile_option_value, 1,5) prof_value
, v.level_id
, v.level_value
, fu.user_name
FROM
apps.fnd_profile_options o
, apps.fnd_profile_options_tl pot
, apps.fnd_profile_option_values v
, apps.fnd_user fu
WHERE
o.profile_option_name = pot.profile_option_name
AND o.profile_option_id = v.profile_option_id (+)
AND o.application_id = v.application_id (+)
AND nvl(v.level_id, 1001) = 10004 -- user level options
AND fu.user_name NOT IN ('YOUR_DBA_NAME', 'SYSADMIN')
AND v.level_value = fu.user_id
AND pot.user_profile_option_name like 'FND%Diag%'
order by pot.user_profile_option_name;

PROMPT Look for Password Profiles set at user level
SELECT
v.profile_option_id
, pot.user_profile_option_name prof_name
, fu.user_name
, substr(v.profile_option_value, 1,5) prof_value
, v.level_value
FROM
apps.fnd_profile_options o
, apps.fnd_profile_options_tl pot
, apps.fnd_profile_option_values v
, apps.fnd_user fu
WHERE
o.profile_option_name = pot.profile_option_name
AND o.profile_option_id = v.profile_option_id (+)
AND o.application_id = v.application_id (+)
AND nvl(v.level_id, 1001) = 10004
AND v.level_value = fu.user_id
AND pot.user_profile_option_name like '%Password%'
order by pot.user_profile_option_name;

-- End User Security Warnings

PROMPT Warnings about Concurrent Programs and Concurrent Manager
column user_concurrent_program_name on heading Program format a32 trunc
column concurrent_program_name on heading Program format a10 trunc
column description on heading Description format a35 trunc
--column user_name format a10

PROMPT Show any requests by SYSADMIN that ended in error in last 5 days
PROMPT
select request_id "Request ID"
/*,fu.user_name User_Name*/
,to_char(cr.actual_start_date, 'DD HH24:MI') "When Started"
,to_char(cr.actual_completion_date, 'DD HH24:MI') "When Ended"
,cp.concurrent_program_name "Program Name"
,phase_code "Phase Code"
,status_code "Status Code"
from
apps.fnd_concurrent_programs cp,
apps.fnd_user fu,
apps.fnd_concurrent_requests cr
where
cr.concurrent_program_id = cp.concurrent_program_id
and cr.program_application_id = cp.application_id
and cr.actual_completion_date is not null
and cr.requested_by = fu.user_id
and fu.user_name = 'SYSADMIN'
and cr.status_code = 'E' -- requests that ended in error
and trunc(cr.actual_start_date) >= trunc(sysdate-5) -- started recently
order by cr.actual_start_date DESC;

PROMPT Show conc programs that have trace enabled. Normally should be none
PROMPT
-- To Turn off: Go to Define Con Pgm form and uncheck the Enable Trace field
col prog_name format a35
col enable_trace format a15
SELECT concurrent_program_name "Prog Name"
, enable_trace "Trace Enabled"
, last_update_date "Last Update Date"
FROM apps.fnd_concurrent_programs fcp
WHERE NVL(enable_trace,'N') = 'Y';

-- Confirm Alert Periodic Scheduler is pending so that alerts will run

PROMPT Confirm Alert Periodic Scheduler is okay, should see no rows
select 'PROBLEM: No pending Alert Periodic Scheduler request' "Message" from dual
where not exists (
select
cp.concurrent_program_name Program,
cr.request_id Request,
phase_code S,
status_code C,
cr.argument1||', '||
cr.argument2||', '||
cr.argument3||', '||
cr.argument4||', '||
cr.argument5||', '||
cr.argument6||', '||
cr.argument7||', '||
cr.argument8 Arguments
from
apps.fnd_concurrent_requests cr,
apps.fnd_concurrent_programs cp
where
cr.concurrent_program_id = cp.concurrent_program_id
and cp.application_id >= 0 -- force on index leading edge
and cr.program_application_id = cp.application_id
and cp.concurrent_program_name = 'ALEPPE' -- Alert scheduler con pgm
and cr.phase_code = 'P'
and cr.status_code != 'H' -- not on hold
);

-- End Con Program section

/*-- Recent changes to objects with Custom Direct Changes - Research
-- Add standard Apps objects we Customized and will be lost when patched

PROMPT Recent changes to objects with Custom Direct Changes - usually None
PROMPT
PROMPT Any ADP views that have been changed recently

SELECT owner, object_name, created, last_ddl_time
FROM dba_objects
WHERE object_name like Upper('%ADP%')
AND owner = 'APPS'
AND object_type = 'VIEW'
AND last_ddl_time > (sysdate -7)
ORDER BY object_type, owner, object_name;

PROMPT Any changes to iExpense pkg where we have custom direct chg
SELECT owner, object_name, created, last_ddl_time
FROM dba_objects
WHERE object_name = 'AP_WEB_DB_HR_INT_PKG'
AND owner = 'APPS'
AND object_type in ('PACKAGE SPEC', 'PACKAGE')
AND last_ddl_time > (sysdate -7)
ORDER BY object_type, owner, object_name;

PROMPT Any OTL/iExpense views changed recently
SELECT owner, object_name, created, last_ddl_time
FROM dba_objects
WHERE object_name in ('AP_WEB_PA_PROJECTS_V')
AND owner = 'APPS'
AND object_type = 'VIEW'
AND last_ddl_time > (sysdate -7)
ORDER BY object_type, owner, object_name;

PROMPT PA Gen Draft Rev parm we want disabled
col short_name format a10
col executable_name format a10
col parameter format a10

SELECT SUBSTR(fcp.user_concurrent_program_name,1,60) concurrent_program_name
, fcp.concurrent_program_name short_name
, SUBSTR(fe.executable_name,1,25) executable_name
, SUBSTR(fl_em.meaning,1,18) execution_method
, LPAD(TO_CHAR(fdfcu.column_seq_num,'fm990'),4) " SEQ"
, fdfcu.end_user_column_name parameter
, RPAD(fdfcu.enabled_flag,8) "ENABLED?"
, SUBSTR(ffvs.flex_value_set_name,1,45) validation_value_set
, SUBSTR(ffvs.description,1,75) value_set_description
, SUBSTR(fl_dt.meaning,1,13) default_type
, RPAD(fdfcu.required_flag,5) "REQD?"
-- If the default value is populated and more than 80 chars
-- then trim it to 80 for purposes of this report.
, RPAD(DECODE(SIGN(NVL(LENGTH(fdfcu.default_value),0) - 80)
, +1, SUBSTR(fdfcu.default_value,1,80) || '...'
, fdfcu.default_value),83) default_value
, RPAD(fdfcu.display_flag,10) "DISPLAYED?"
FROM apps.fnd_application fa
, apps.fnd_application_tl fat
, apps.fnd_concurrent_programs_vl fcp
, apps.fnd_executables fe
, apps.fnd_descr_flex_column_usages fdfcu
, apps.fnd_descr_flex_col_usage_tl fdfcut
, apps.fnd_flex_value_sets ffvs
, apps.fnd_lookups fl_dt
, apps.fnd_lookups fl_em
WHERE USERENV('LANG') = fat.language
AND fat.application_id = fa.application_id
AND fa.application_id = fcp.application_id
AND fcp.concurrent_program_name = 'PARGDR' -- PA program
AND fdfcu.end_user_column_name = 'Release Draft Revenue' -- this parameter
AND fdfcu.enabled_flag = 'Y' -- has become enabled when it should not
AND fcp.executable_application_id = fe.application_id
AND fcp.executable_id = fe.executable_id
AND fcp.application_id = fdfcu.application_id
AND '$SRS$.'
|| fcp.concurrent_program_name = fdfcu.descriptive_flexfield_name
AND fdfcu.application_id = fdfcut.application_id
AND fdfcu.descriptive_flexfield_name = fdfcut.descriptive_flexfield_name
AND fdfcu.descriptive_flex_context_code = fdfcut.descriptive_flex_context_code
AND fdfcu.application_column_name = fdfcut.application_column_name
AND USERENV('LANG') = fdfcut.language
AND fdfcu.flex_value_set_id = ffvs.flex_value_set_id
AND fdfcu.default_type = fl_dt.lookup_code (+)
AND 'FLEX_DEFAULT_TYPE' = fl_dt.lookup_type (+)
AND fcp.execution_method_code = fl_em.lookup_code (+)
AND 'CP_EXECUTION_METHOD_CODE' = fl_em.lookup_type (+)
ORDER BY fcp.user_concurrent_program_name
, fdfcu.column_seq_num;

-- End Custom Direct Changes Section*/

/*PROMPT Confirm selected Applimation DB triggers that cause problems are off

select count(*)
from dba_triggers
where trigger_name like 'AM_WF_LOCAL_USER_ROLES%'
and status != 'DISABLED' -- these triggers cause severe perf problems when on
/*/

-- Std RDBMS Checks Section

PROMPT Watch for HighWaterMark -- higher than 1000 since may need to incr processes
PROMPT

SELECT V.SESSIONS_CURRENT "Current Sessions",
V.SESSIONS_HIGHWATER "HighWater Sessions",
V.CPU_COUNT_CURRENT "CPU Current Count",
V.CPU_COUNT_HIGHWATER "CPU Count HighWater"
FROM V\$LICENSE V;
--where SESSIONS_HIGHWATER > 100

-- Check for datafiles that have autoextended to their max so stuck
column bytes format 999999999
column file_name format a35
column TSPACE format a12
prompt
prompt Datafiles with autoextend ON that have reached their max so have got STUCK
select tablespace_name "Tablespace"
, file_name "Filename"
, bytes/1048576 "Size"
, maxbytes / 1048576 "Maxsize(MB)" -- convert to MB
from dba_data_files
where autoextensible = 'YES'
and bytes >= maxbytes -- datafile already extended to max
order by tablespace_name;

-- DB Security Warnings:
-- Check for any locked db user accounts
COLUMN username FORMAT a14
COLUMN default_tablespace heading 'DEFAULT TS' FORMAT a10
COLUMN temporary_tablespace heading 'TEMP' FORMAT a4
COLUMN profile FORMAT a13
COLUMN account_status heading 'ACCT|STAT' FORMAT a6 trunc
PROMPT List Locked Oracle schema accounts
PROMPT
SELECT username
,to_char(created, 'DD-MON-YY') created
,default_tablespace
,temporary_tablespace
,account_status
,to_char(lock_date, 'MM/DD/YY HH24:MI') lock_date
,profile
FROM dba_users
WHERE lock_date IS NOT NULL
AND account_status NOT LIKE 'EXPIRED%';

PROMPT Check whether UNDO datafiles have autoextended
PROMPT May want to resize datafiles down lower after big AutoExtend

COLUMN sname FORMAT a12 heading 'Tablespace' justify c
COLUMN ssize FORMAT 999,999.99 heading 'Mb|Total' justify c
COLUMN extents FORMAT 9,999 heading 'Free|Exts' justify c
COLUMN mbytes FORMAT 9,999.99 heading 'Largest|Free Ext' justify c
COLUMN tbytes FORMAT 999,999.99 heading 'Mb|Avail' justify c
COLUMN mused FORMAT 999,999.99 heading 'Mb|Used' justify c
COLUMN pct FORMAT 990.99 heading 'Percent|Free' justify c
SELECT
total.tablespace_name sname,
COUNT(free.bytes) extents,
max(free.bytes)/1048576 mbytes,
total.ts_size/1048576 ssize,
(total.ts_size/1048576) - (sum(free.bytes/1048576)) mused,
sum(free.bytes)/1048576 tbytes,
sum(free.bytes)/total.ts_size * 100 pct
FROM
dba_free_space free
,(SELECT tablespace_name,
sum(bytes) ts_size
FROM dba_data_files
GROUP BY tablespace_name)
total
WHERE
total.tablespace_name = free.tablespace_name (+)
AND total.tablespace_name = 'APPS_UNDOTS1'
AND total.ts_size/1048576 > 8600 -- greater than 8600 MB then autoextended
GROUP BY
total.tablespace_name,
total.ts_size;

PROMPT ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
PROMPT END Warning Section
PROMPT ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
PROMPT
PROMPT

PROMPT ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
PROMPT Begin General Health Checks Section
PROMPT ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
PROMPT
PROMPT

-- Revise prompt as persistent invalid objects increase or reduce
-- See SharePoint Invalid Object Log for details about these objects
PROMPT Invalid APPS objects, at least 6: FND_OID_DIAG FND_TS_SIZE and 4 MRP_
COLUMN object_name FORMAT a30
SELECT
owner, object_name, object_type
, to_char(created, 'DD-MON-YY') created
, status
FROM dba_objects
WHERE Owner = 'APPS'
AND status = 'INVALID'
ORDER BY object_name;

-- Add any custom schemas here
PROMPT Invalid objects owned by custom schema XXCUSTOM Account
SELECT
owner, object_name, object_type, created, status
FROM dba_objects
WHERE Owner = 'XXCUSTOM'
AND status = 'INVALID'
ORDER BY object_name;

-- RDBMS Checks
PROMPT Check SYS audit table to determine number of audit rows
SELECT
COUNT(*)
FROM sys.aud$;

PROMPT List Expired schema accounts
PROMPT
SELECT username
,to_char(created, 'DD-MON-YY') created
,default_tablespace
,temporary_tablespace
,account_status
,to_char(lock_date, 'MM/DD/YY HH24:MI') lock_date
,profile
FROM dba_users
WHERE /*lock_date is not null
AND */ account_status != 'OPEN';

PROMPT Usage Checks Section:

-- Check highest DB connections COUNT since last DB start
-- Count variety of current Oracle connections
PROMPT Current Oracle Connection Characteristics including highwater mark

COLUMN formsusers HEADING "Forms connects" FORMAT 99999
COLUMN selfservusers HEADING "Self Service" FORMAT 99999
COLUMN rundatetime HEADING "TIME OF DAY" FORMAT A16
COLUMN currsession HEADING "Current Sess" FORMAT 99999
COLUMN sesshighwater HEADING "High Water" FORMAT 99999
COLUMN jdbcthinclient HEADING "JDBC Thin" FORMAT 99999

SELECT TO_CHAR(SYSDATE,'DD-MON-YY HH24:MI') "Current Time"
--show currently logged in Forms connections, multiple connections per user
--approximately half as many Forms users as DB forms connections
,(SELECT COUNT(*)
FROM apps.fnd_logins fl -- only has Forms login rows
, apps.fnd_user fu
, v\$session s
, v\$process p
WHERE fl.end_time IS NULL
AND fl.user_id = fu.user_id(+)
AND fl.process_spid = p.spid
AND fl.pid = p.pid
AND fl.serial# = p.serial#
AND p.addr = s.paddr
AND s.program IS NULL) "Forms Users"
--
--
-- SelfServ Activity last 60 min based on icx_sessions
,(SELECT COUNT(*)
FROM apps.icx_sessions icx
, apps.fnd_user fu
, apps.fnd_responsibility_vl frv
WHERE icx.user_id = fu.user_id(+)
AND icx.disabled_flag = 'N'
AND icx.last_connect > SYSDATE - 1/24
--Preferences
AND NVL(icx.responsibility_id,20873) = frv.responsibility_id(+)
AND icx.responsibility_application_id = frv.application_id(+)
-- only web self service responsibilities
AND frv.version(+) = 'W') "Self Serv Users"
--
--
,(SELECT SESSIONS_CURRENT
FROM v\$license ) "Curr Session"
--
--
,(SELECT SESSIONS_HIGHWATER
FROM v\$license ) "Sess HighWater"
--
--
,( SELECT COUNT(*)
FROM sys.v_\$session
WHERE program = 'JDBC Thin Client') "JDBC ThinClient"
FROM DUAL;

PROMPT User Checks Section:
PROMPT
PROMPT Users whose FND Login Account password has become INVALID (11.5.10.2)
PROMPT and whose account is not end dated
COLUMN encrypted_password FORMAT a20

SELECT user_name
, TO_CHAR(last_logon_date, 'DD-Mon-YY') last_logon
, TO_CHAR(password_date, 'DD-Mon-YY') passwd_date
, ENCRYPTED_USER_PASSWORD encrypted_password
, TO_CHAR(end_date, 'DD-Mon-YY') end_date
FROM apps.fnd_user
WHERE encrypted_user_password = 'INVALID' -- value when user is blocked
AND user_name NOT IN ('ANONYMOUS', 'APPSMGR','XML_USER') --inactive seeded
AND end_date is null -- only look at active logins in this query
ORDER BY user_name;

PROMPT Non-Terminated people with end dated fnd_user plus INVALID Password
PROMPT We assume that locked users should not be both end dated and INVALID
SELECT DISTINCT(fu.user_name)
, ENCRYPTED_USER_PASSWORD encrypted_password
, fu.end_date
FROM apps.fnd_user fu
, apps.per_all_people_f papf
WHERE fu.encrypted_user_password = 'INVALID' -- value when user is blocked
AND fu.end_date IS NOT NULL
AND fu.employee_id = papf.person_id
AND papf.current_employee_flag = 'Y'
AND trunc(sysdate) between papf.effective_start_date
and papf.effective_end_date;

-- Profiles Section:

PROMPT PROFILES Section: Check Selected PROFILE Settings:
PROMPT
PROMPT List Site level Profiles that have changed in last Month

COLUMN application_id on heading app_id FORMAT 99999
COLUMN profile_option_id on heading 'opt|id' FORMAT 9999
COLUMN level_value on heading 'level|value' FORMAT 99999
COLUMN prof_value on heading 'Prof|value' FORMAT a15
COLUMN prof_name FORMAT a20 trunc
COLUMN user_prof_name FORMAT a40 trunc
COLUMN user_name FORMAT a10 trunc

PROMPT Site Level Profile Settings
SELECT
o.user_profile_option_name user_prof_name
, substr(v.profile_option_value, 1,45) prof_value
, fu.user_name
, to_char(v.last_update_date,'DD-Mon-YYYY') update_date
FROM apps.fnd_profile_option_values v
, apps.fnd_profile_options_vl o
, apps.fnd_user fu
WHERE o.profile_option_id = v.profile_option_id
AND o.application_id = v.application_id
AND level_id = 10001
AND v.last_updated_by = fu.user_id
AND (v.last_update_date >= sysdate - 30 -- updated in last 4 weeks
OR v.creation_date > sysdate -30) -- created in the last 4 weeks
ORDER BY fu.user_name, o.user_profile_option_name;

PROMPT User Level Profile Settings relating to Trace or Debug
--
COLUMN application_id on heading appl_id FORMAT 999999
COLUMN profile_option_id on heading prof_id FORMAT 99999
COLUMN level_value on heading 'level|value' FORMAT 99999
COLUMN level_id on heading 'level|id' FORMAT 99999
COLUMN prof_value on heading 'Prof|value' FORMAT a5
COLUMN prof_name FORMAT a25 trunc
COLUMN user_name FORMAT a15

PROMPT List Profile options WHERE debug/trace is SET on
SELECT
v.profile_option_id
, v.level_id
, user_name
, pot.user_profile_option_name prof_name
, substr(v.profile_option_value, 1,45) prof_value
FROM
apps.fnd_profile_options o
, apps.fnd_profile_options_tl pot
, apps.fnd_profile_option_values v
, apps.fnd_user fu
WHERE
o.profile_option_name = pot.profile_option_name
AND o.profile_option_id = v.profile_option_id (+)
AND o.application_id = v.application_id (+)
AND level_id = 10004 -- user level option
AND v.level_value = fu.user_id
AND fu.end_date is null -- only look at active logins
AND nvl(v.profile_option_value, 'N') = 'Y'
AND (pot.user_profile_option_name like '%Trace%'
OR pot.user_profile_option_name like '%Debug%')
ORDER BY pot.user_profile_option_name;

PROMPT Non-User Level Profile Settings relating to Trace or Debug
SELECT
v.profile_option_id
, v.level_id
, v.level_value
, pot.user_profile_option_name prof_name
, substr(v.profile_option_value, 1,45) prof_value
FROM
apps.fnd_profile_options o
, apps.fnd_profile_options_tl pot
, apps.fnd_profile_option_values v
WHERE
o.profile_option_name = pot.profile_option_name
AND o.profile_option_id = v.profile_option_id (+)
AND o.application_id = v.application_id (+)
AND level_id != 10004 -- not user level option
AND nvl(v.profile_option_value, 'N') = 'Y'
AND (pot.user_profile_option_name like '%Trace%'
OR pot.user_profile_option_name like '%Debug%')
order by pot.user_profile_option_name;

PROMPT Module Specific Checks Section:
PROMPT
PROMPT Module: Order Mgmt

PROMPT Check OE Processing Msgs for Number AND types of rows
PROMPT If too many rows check whether 'Message Purge' Con Pgm is scheduled
SELECT
request_id
, substr(opmt.message_text,1,50) msg
, COUNT(*)
FROM ont.oe_processing_msgs opm
, ont.oe_processing_msgs_tl opmt
WHERE opm.transaction_id = opmt.transaction_id
group by request_id,opmt.message_text;

PROMPT Module: Advanced Supply Chain Planning
PROMPT We run Purge ATP Temp Tables but it leaves rows with null creation_date
PROMPT Need to truncate mrp.mrp_atp_schedule_temp periodically when too many
PROMPT rows due to the null problem.
SELECT
COUNT(*)
FROM mrp.mrp_atp_schedule_temp;

PROMPT Module: AOL
PROMPT We run Purge FND_STATS History Records but it leaves some rows.
PROMPT Need to truncate apps.fnd_stats_hist periodically when too many rows
SELECT
COUNT(*)
FROM apps.fnd_stats_hist;

PROMPT End Module specific Checks Section
PROMPT

PROMPT ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
PROMPT ++ End of oracle_apps_full_report.sh Logic
PROMPT ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SPOOL OFF

CLEAR COLUMNS
EXIT
!

Oracle Apps DBA stuff

Wednesday, October 30, 2013

Users Intermittently Running Into 'Your session has expired' when using RAC database

APPLIES TO:

SYMPTOMS

CHANGES

CAUSE

SOLUTION

Thursday, October 24, 2013

Hierarchy Functionality In Supplier Data Librarian (Super User) Has References To Customer Screens R12.1.3

Tuesday, October 22, 2013

R12: Unable To Relink MSC Executables on On OEL5/RHEL5

Why is Workflow Ignoring Skip Approval And Proceeding To Approval? R12.1.1

Monday, October 21, 2013

Memory/Resources Errors On System That Has Abundent Resources Running Oracle Linux 6

Profile options in R12

Saturday, October 19, 2013

su: cannot set user id: Resource temporarily unavailable

ORA-27300: OS System Dependent Operation:fork Failed With Status: 11

Thursday, October 17, 2013

Generate WSDL Button Not Visible On Integration Repository Pages in 12.1.1 Integrated SOA Gateway

APPLIES TO:

SYMPTOMS

CAUSE

SOLUTION

Tuesday, October 15, 2013

apps_ful._report

Search This Blog

Blog Archive