Worthy for An Apps DBA

Important NOTE IDs:

apscheck.sql from note Note ID: 246150.1
Apache Clearing Cache in R12  Note ID: 742107.1
New utility to change passwords from 12.1.2 -- AFPASSWD   Note ID: 437260.1
How to change from default Servlet mode (in apps R12) to Socket mode ? Note # 384241.1
Utility /Script To Check The Techstack Component Versions (Forms, Http Server, JDK, Framework, Database, etc) (Doc ID 601736.1)
A Detailed Approach To Purging Oracle Workflow Runtime Data (Doc ID 144806.1)
Concurrent Processing - Settings for Profile Option "Concurrent Report Access Level" (Doc ID 736547.1)

Symptoms  567389.1
=========
make: Fatal error: Don’t know how to make target 'install'
SOLUTION
============
This bug will be fixed in the 10.2.0.5 patchset
The bug is also fixed in 10.2.0.4 by Patch 6826281
============
1. Rename the $ORACLE_HOME/odbc/lib/ins_odbc.mk file
2. Run the "relink all" command again

TROUBLESHOOT LOGIN PAGE / APACHE
================================
For related Apache issues One can go through the following logs

$ cd $LOG_HOME
$ find . -name 'application.log'
./ora/10.1.3/j2ee/oacore/oacore_default_group_2/application.log
./ora/10.1.3/j2ee/oacore/oacore_default_group_1/application.log
./ora/10.1.3/j2ee/oafm/oafm_default_group_1/application.log


$FND_TOP/admin/template/custom/httpd_conf_1013.tmp
$INST_TOP/ora/10.1.3/Apache/Apache/conf/httpd.conf

When the issue occur check the log files apart from access and error_log

sec_case_sensitive_logon             boolean     FALSE

==============================================================
Related metalink notes to enable additional debugging

How to enable Apache, OC4J and OPMN logging in Oracle Applications R12   419839.1
R12 How To Enable and Collect Debug for HTTP, OC4J and OPMN 422419.1

============================================================================
Concurrent Manager Status
=======================
set pages 999
col user_concurrent_queue_name for  a48
select  user_concurrent_queue_name,max_processes,running_processes from fnd_concurrent_queues_vl where concurrent_queue_name in  ('FNDCRM','FNDICM','FNDSCH','STANDARD','PODAMGR','RCVOLTM','PASMGR','INVMGR','INVTMRPM','WFALSNRSVC','WFMLRSVC','IEU_SH_CS','IEU_WL_CS','OAMCOLMGR','WFWSSVC','FNDCPOPP') or concurrent_queue_name like 'FNDSM%';
==========================================================
select * from DBA_NETWORK_ACLS;
select * from DBA_NETWORK_ACL_PRIVILEGES;
select * from USER_NETWORK_ACL_PRIVILEGES;
================================
376700.1 ===>SSL Creation Setup
==========================
Query To find any JAVA class Versions
====================================
  SELECT fi.file_id, filename, version
  FROM apps.ad_files fi, apps.ad_file_versions ve
  WHERE UPPER(filename) LIKE UPPER('PoGenerateDocument%')
  AND ve.file_id = fi.file_id
  AND version = (SELECT MAX (version) FROM apps.ad_file_versions ven
 WHERE ven.file_id = fi.file_id)
SQL> /

   FILE_ID FILENAME                       VERSION
---------- ------------------------------ ----------------------------------------
    435163 PoGenerateDocument.class       115.18.11510.2
    435164 PoGenerateDocumentCP.class     115.8.11510.2
    435197 PoGenerateDocument.class       120.18.12010000.9
============================================================================

select decode(nvl(a.APPLICATION_short_name,'Not Found'),
'SQLAP','AP','SQLGL','GL','OFA','FA','Not Found','id '||to_char(fpi.application_id),
a.APPLICATION_short_name) apps, decode(fpi.status,'I','Installed','S','Shared','N','Inactive',fpi.status) status,
fpi.product_version,nvl(fpi.patch_level,'-- Not Available --') Patchset,
to_char(fpi.last_update_date,'dd-Mon-RRRR') "Update Date"
from fnd_oracle_userid o, fnd_application a, fnd_product_installations fpi
where fpi.application_id = a.application_id(+)
and fpi.oracle_id = o.oracle_id(+)
order by 1,2;
=============================================================

### What are the steps to reproduce the problem ###
1. Click on Notification Summary to open worklist
2. Click 1 PO and view for approval, but font is not correct so cant read
3. Do again with Requisition, font is correct, can read it.

Solution:
System Profile Option - 'ICX: Client IANA Encoding' is matching the character set from the NLS_LANG setting
This resolved the issue…….
******************************************************************************************************************************
1. When Inventory Corrupted.
Rebulding inventory:
$ORACLE_HOME/oui/bin/attachHome.sh -silent -local -invPtrLoc $ORACLE_HOME/oraInst.loc
******************************************************************************************************************************
Invalid Objects Issue

When patches applied. New Invalids come
Startup database in Upgrade Mode
RUN $ORACLE_HOME/rdbms/admin/utlirp.sql
This invalidates all the objects
Shutdown DB and Startup in Normal Mode
RUN $ORACLE_HOME/rdbms/admin/utlrp.sql
Some times utl_recomp gone invalid. You need to compile first
=================================================================================

ENABLE TRACE FROM FRONT END  R12
================================
In order to analyze the performance issue we will need a trace file for the validation
process for a SINGLE DAY. To get the R12 Trace:

1. Navigate Responsibility: System Administrator > Profile > System > Query

User: User submitting the Report Profile: Initialization SQL Statement - Custom

2. Click on User column - Edit Field and enter
begin fnd_ctl.fnd_sess_ctl('','','TRUE','TRUE','LOG','ALTER SESSION SET EVENTS='||''''||'10046
TRACE NAME CONTEXT FOREVER,LEVEL 12'||''''); end; 3. Save.

4. Upload the raw and tkprof'd trace file created. Use the following SQL to locate the file:
select value from v$parameter where name = 'user_dump_dest';

<<Parameter:TIMED_STATISTICS>>
Enable/Disable the collection of timed statistics, such as CPU and elapsed times.

TRUE - Enable timing FALSE - Disable timing (Default value). Basic Syntax of TKProf
TKPROF filename_source filename_output EXPLAIN=[username/password] SYS=[YES/No] TABLE=[tablename]
filename_source The trace file that needs to be formatted

SOLARIS OS INFO
Nr.Of CPU   psrinfo
Memory Conf /usr/sbin/prtconf
Swap  /usr/sbin/swap -s
Bit Version /usr/bin/isainfo -kv
Extensible uname -X
AIX
No.Of CPU lscfg -vs|grep proc | wc -l
Bit version ls -l /unix

LINUX
No.Of CPU: /proc/meminfo, /proc/cpuinfo

To Find Oracle Binary Version
==========================
cd $ORACLE_HOME/bin
file oracl*

If The Application Broken BY doing
MO: Operating Unit fix it is remove from site level.

Download the profile option from Any Cloned Instance
FNDLOAD apps/**** O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct fmga_op_un.ldt PROFILE PROFILE_NAME="ORG_ID" APPLICATION_SHORT_NAME="FND"
Download the profile option from FMGT
FNDLOAD apps/**** O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct fmgt_op_un.ldt PROFILE PROFILE_NAME="ORG_ID" APPLICATION_SHORT_NAME="FND"
Run diff on it
diff fmgt_op_un.ldt fmga_op_un.ldt
7c7
< #Source Database FMGT
---
> #Source Database FMGA
126a127,132
> BEGIN FND_PROFILE_OPTION_VALUES "10001" "** Site **" ""
> OWNER = "HABBASI"
> LAST_UPDATE_DATE = "2009/02/23"
> PROFILE_OPTION_VALUE = "106"
> END FND_PROFILE_OPTION_VALUES
>
2579,2581c2585,2587
< OWNER = "ORACLE"
< LAST_UPDATE_DATE = "2009/05/08"
< PROFILE_OPTION_VALUE = "106"
---
> OWNER = "FSARWAR"
> LAST_UPDATE_DATE = "2008/11/21"
> PROFILE_OPTION_VALUE = "107"
usrns214:/home/applfmgt>

upolad the Cloned Instance ldt into Production

FNDLOAD apps/**** O Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct fmga_op_un.ldt

Test the application make sure you log off and log back in to instance.

select request_id, CONCURRENT_QUEUE_NAME from fnd_concurrent_worker_requests where request_id in '12934727';

select
 ptl.user_concurrent_program_name,qtl.user_concurrent_queue_name,t.request_id
 from Fnd_Concurrent_Requests t,
      FND_CONCURRENT_PROCESSES k,
      Fnd_Concurrent_Queues_TL QTL,
      Fnd_Concurrent_Programs_TL PTL
 where k.concurrent_process_id = t.controlling_manager
   and QTL.Concurrent_Queue_Id = k.concurrent_queue_id
   and ptl.concurrent_program_id=t.concurrent_program_id
   and qtl.language='US';

How to Trace a Concurrent Request And Generate TKPROF File
==========================================================
 Enable Tracing For The Concurrent Manager  Program
•  Responsibility: System Administrator
•  Navigate: Concurrent > Program > Define
•  Query Concurrent Program
•  Select the Enable Trace Checkbox

Turn On Tracing
•  Responsibility: System Administrator
•  Navigate: Profiles > System
•  Query Profile Option Concurrent: Allow Debugging
•  Set profile to Yes

 Run Concurrent Program With Tracing Turned On
•  Logon to the Responsibility that runs the Concurrent Program
•   In the Submit Request Screen click on Debug Options (B)
•  Select the Checkbox for SQL Trace

 Run the following SQL to find out the Raw trace name and location for the concurrent program.  The SQL prompts the user for the request id

SELECT ’Request id: ’||request_id ,  ‘Trace id: ’||oracle_Process_id,  ‘Trace Flag: ’||req.enable_trace,  ‘Trace Name:  ‘||dest.value||’/'||lower(dbnm.value)||’_ora_’||oracle_process_id||’.trc’,  ‘Prog. Name: ’||prog.user_concurrent_program_name,  ‘File Name: ’||execname.execution_file_name|| execname.subroutine_name ,  ‘Status : ’||decode(phase_code,’R',’Running’)  ||’-'||decode(status_code,’R',’Normal’),  ‘SID Serial: ’||ses.sid||’,'|| ses.serial#,  ‘Module : ’||ses.module  from fnd_concurrent_requests req, v$session ses, v$process proc,  v$parameter dest, v$parameter dbnm, fnd_concurrent_programs_vl prog,  fnd_executables execname  where req.request_id = &request  and req.oracle_process_id=proc.spid(+)  and proc.addr = ses.paddr(+)  and dest.name=’user_dump_dest’  and dbnm.name=’db_name’  and req.concurrent_program_id = prog.concurrent_program_id  and req.program_application_id = prog.application_id  and prog.application_id = execname.application_id  and prog.executable_id=execname.executable_id;

$tkprof raw_trace_file.trc output_file explain=apps/apps sort=(exeela,fchela) sys=no

The trace file by default is post fixed with oracle Process_id which helps us to identify which trace file belongs to which concurrent request. The below SQL Query returns the process_id of the concurrent request:
Select oracle_process_id from fnd_concurrent_requests where                 request_id='2768335'
(This query displays Process Id)

Ls –ltr *oracle_processid*.trc


********************************************
SID: MPDR1I
Customer is unable to get forms. Issues are intermittent.
Forms seession establishing form not launching at all
********************************************
We saw below message appearing in the logfile.
********************************************
Warning: Shared server clients will not be able to connect because SHARED_SERVERS is 0
Mon May 02 15:42:59 2011
ALTER SYSTEM SET dispatchers='(protocol=tcp)(dispatchers=3)' SCOPE=MEMORY;
Mon May 02 15:43:00 2011
dispatcher 'D000' encountered error setting up listening port
********************************************
Based on this we tried checking all listener parameters & observed that “remote_listener” was set to "MPDR1I_REMOTE".

As this parameter is specific to RAC environment for registering PMON with listener running on remote nodes, we commented
out this parameter on MPDR1I which is non-rac environment.

Yes the problem was mainly with all remote DB connections listening to incorrect listener parameter (as set above).
********************************************
Httpd_server down   id=754838.1

$ grep -i SSLSessionCache ssl.conf
#SSLSessionCache        none
#SSLSessionCache        dbm:/polymi/inst/apps/POLYMI_auohsolym08/logs/ora/10.1.3/Apache/ssl_scache
#SSLSessionCache        shmht:/polymi/inst/apps/POLYMI_auohsolym08/logs/ora/10.1.3/Apache/ssl_scache(512000)
SSLSessionCache         shmcb:/var/opt/TOLYMI_auohsolym02/Apache/logs/ssl_scache(512000)
SSLSessionCacheTimeout  300

Issue is not with NFS, some times it comes due to Wrong NFS mount.
This is on Local File system.Ssl.conf has wrong Entry,

When You get the following ISSUE
=================================
SQL> set time on
09:56:46 SQL> exec FND_STATS.GATHER_SCHEMA_STATISTICS('ALL', 10, 32, 'NOBACKUP', NULL, 'LASTRUN', 'GATHER AUTO', 10, 'N');
BEGIN FND_STATS.GATHER_SCHEMA_STATISTICS('ALL', 10, 32, 'NOBACKUP', NULL, 'LASTRUN', 'GATHER AUTO', 10, 'N'); END;

*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 14254
ORA-06512: at "SYS.DBMS_STATS", line 14277
ORA-06512: at line 1
ORA-06512: at "APPS.FND_STATS", line 711
ORA-06512: at "APPS.FND_STATS", line 1836
ORA-06512: at "APPS.FND_STATS", line 1183
ORA-06512: at "APPS.FND_STATS", line 1275
ORA-06512: at "APPS.FND_STATS", line 815
ORA-06512: at line 1


SQL> select owner, table_name, stattype_locked from dba_tab_statistics where stattype_locked is not null;


OWNER                          TABLE_NAME                     STATT
------------------------------ ------------------------------ -----
SYS                            SCHEDULER$_REMDB_JOBQTAB       ALL
SYS                            AQ_PROP_TABLE                  ALL
SYS                            KUPC$DATAPUMP_QUETAB           ALL
SYS                            SYS$SERVICE_METRICS_TAB        ALL
APPLSYS                        FND_SOA_JMS_IN                 ALL
APPLSYS                        FND_SOA_JMS_OUT                ALL
APPLSYS                        FND_CP_GSM_IPC_AQTBL           ALL
SYS                            WRH$_EVENT_HISTOGRAM           ALL
SYS                            WRH$_FILESTATXS                ALL
SYS                            WRH$_SQLSTAT                   ALL
SYS                            WRH$_SYSTEM_EVENT              ALL
SYS                            WRH$_WAITSTAT                  ALL
SYS                            WRH$_LATCH                     ALL
SYS                            WRH$_LATCH_MISSES_SUMMARY      ALL
SYS                            WRH$_DB_CACHE_ADVICE           ALL

26 rows selected.

SQL> SQL> exec dbms_stats.unlock_table_stats('APPLSYS','FND_SOA_JMS_IN');

PL/SQL procedure successfully completed.
==================================================
Core issue is that the time it takes to create the lob index is huge.
The reason for this is that this is a context index which actually indexes the data in free form text to enable search capability.
The problem is that the FND_LOBS table is used for more than just help files even though the FND_LOB_CTX index is only relevant for helpfiles.

In looking at the lob it turns out this table actually holds 62GB of data. To see this we can run the following sql :

SQL> l
   1* select count(*) , SUM(DBMS_LOB.GETLENGTH(FILE_DATA))/1024/1024
lob_size from fnd_lobs
SQL> /

   COUNT(*)    LOB_SIZE
---------- -----------
     262555    61487.04

When we build the context index it tries to scan through all 62GB in
order to build the search functionality. However if we look at where all
this data is coming from we see this :

   1  select program_name,count(*),SUM(DBMS_LOB.GETLENGTH(FILE_DATA))/1024/1024 lob_size from fnd_lobs
group by program_name order by 3 asc;


PROGRAM_NAME                       COUNT(*)    LOB_SIZE
-------------------------------- ---------- -----------
...
FND_HELP                              90489      573.77
                                      16181     3691.65
FNDATTCH                             155748    57201.69

57GB of the 62GB are attachments and not help files - another 4GB have
no program name attached. All we really want to index are the FND_HELP
files. According to Note 397757.1 this is fixed by doing the following :

update fnd_lobs
set FILE_FORMAT = 'IGNORE'
where NVL(PROGRAM_NAME,'NULL') != 'FND_HELP' ;


WHich will tell the system to only build the index on FND_HELP files.

FND_LOB_CTX is now fixed.

======================================================================
Check Conflicts for any RDBMS Patch#

opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir ./6196748

Level 12 trace for a concurrent prog  Doc ID 295963.1

select REQUEST_ID,ORACLE_ID,ORACLE_PROCESS_ID,ORACLE_SESSION_ID,OS_PROCESS_ID from applsys.FND_CONCURRENT_REQUESTS where REQUEST_ID=13787589;
update APPLSYS.fnd_Concurrent_requests set PHASE_CODE='C', STATUS_CODE='D' where REQUEST_ID=13787589;
commit;



1.Login as system owner and run:

alter profile AD_PATCH_MONITOR_PROFILE limit failed_login_attempts unlimited;
alter profile default limit failed_login_attempts unlimited password_lock_time 1/1440;
alter user apps account unlock;
==================================================================================
select text from dba_source where name='<PACKAGE NAME>' and type='PACKAGE';
select text from dba_source where name='<PACKAGE body NAME>' and type='PACKAGE BODY';
***************************************************************************************************
package version
===============

select text from all_source where name = '&pkg_name' and line < 7;
select to_char(sysdate,'DD-MON-YY HH24:MI:SS') from dual;
SET MARKUP HTML ON
SPOOL RESP_MF_BUY_file.xls;
====================================
Update override address: afsvcpup.sql

select COMPONENT_ID,COMPONENT_NAME from FND_SVC_COMPONENTS where COMPONENT_NAME like '%Workflow Notification%';
select PARAMETER_ID,DISPLAY_NAME from FND_SVC_COMP_PARAMS_TL where DISPLAY_NAME like '%Test%Address%';
update FND_SVC_COMP_PARAM_VALS set PARAMETER_VALUE='glsrk.lir@worth.com' where COMPONENT_ID=10006 and PARAMETER_ID =10057;
select PARAMETER_VALUE from FND_SVC_COMP_PARAM_VALS  where COMPONENT_ID=10006 and PARAMETER_ID =10057;

-- XLA status --
SQL> select fpi.application_id, fpi.status, fpi.patch_level from fnd_product_installations fpi where fpi.application_id = 602;

APPLICATION_ID S PATCH_LEVEL
-------------- - ------------------------------
           602 S R12.XLA.A.6

*************************************************************************************
Select a.APPLICATION_ID, i.STATUS
from fnd_application ,fnd_product_installations i
where a.APPLICATION_ID=i.APPLICATION_ID
and a.APPLICATION_SHORT_NAME ='&applshortname'

**************************************************************************************
1. FND Message Log

1) Enable the FND Debug Log
a)  Navigate to System Administrator responsibility
b) Set the following  profiles at the USER level:
FND: Debug Log Enabled = YES
FND: Debug Log  Filename = NULL
FND: Debug Log Level = STATEMENT
FND: Debug Log Module =  %

==============================================================

Compiling a Form in R12

frmcmp_batch module=CYPPORCT.fmb userid=apps/apps output_file=CYPPORCT.fmx compile_all=special

Creating a DB Link / Database Link

CREATE PUBLIC DATABASE LINK R11toR12 CONNECT TO apps IDENTIFIED BY xxxxx USING '<Target SIE>';

SQL> select HOST_NAME,INSTANCE_NAME from v$instance@R11TOR12;
SQL> select sysdate from dual@r11tor12;

adcfgclone.pl dbTechStack
=========================
When we run adcfgclone.pl dbTechStack, it doesn't recreate the control
file and doesn't try to start the database.  The only things it does
is recreate the database inventory and relink the database oracle
home.
When we want to manually create the control file and bring up the
database on our own, instead of relying on adcfgclone to do this for
us, we use adcfgclone.pl dbTechStack

Issue With AutoConfig Desc
===========================
We are seeing a peculiar issue where the values we modify using vi are reverted during autoconfig.

For e.g.
We modified "s_applcsf" value to null in xml file and ran autoconfig.
Now we see that the xml file has reverted back to old value, we confirmed this from the backup xml file system took during autoconfig that while autoconfig
started it had the correct value (null) when it started.
But after autoconfig we see that the value with the directory came back !

We know the fix is to update the values from OAM, but wanted to get to the core of the issue.

This is on 12.1.3.4
=============
This has been known problem from long time & the below could be a possibility in your case too.

Cause
*****
Each Contextfile version has a serial number. When autoconfig is run, this serial number is written to the table:
FND_OAM_CONTEXT_FILES against the version of the Context file:

However it is possible that the Apps Tier Contextfile serial number can become unsynchronised with the serial number information in the table.
If the serial number in the filesystem reads a lower value compared with that showing in the table for the same contextfile version, then each time Autoconfig is run, AutoConfig will see that the table is showing the higher value and it will replace the Contextfile in the filesystem with the parameter values from the contextfile associated with the serial number in the table.
This results in any changes made to the filesystem Contextfile being ignored.


Solution
*******
1. Manually change the serial number parameter "s_contextserial" in the Apps Tier Contextfile on the Filesystem to a value which is one more than the highest version of the serial number in the FND_OAM_CONTEXT_FILES table, for the relative Contextfile version.
2. Run AutoConfig.

===========================================================================
REVIEW :
========
As per Data Collected, we see that the same error is being reproduced by

$ORACLE_HOME/OPatch/opatch util LoadXML -xmlInput /talsec/oracle/product/oraInventory/ContentsXML/comps.xml

You entered: "/talsec/oracle/product/oraInventory/ContentsXML/comps.xml".
Failed to load XML file due to SAXException, msg=Whitespace required.
UtilitySession failed to check the XML file.
UtilSession::process() failed. Detail: Failed to load XML file due to SAXException, msg=Whitespace required.

===> The above confirms that the file "/relsec/oracle/product/oraInventory/ContentsXML/comps.xml" under Oracle Central Inventory (oraInventory) is CORRUPTED.

ACTION PLAN :
=============
1. Please restore the Central Inventory File "/relsec/oracle/product/oraInventory/ContentsXML/comps.xml" from previously taken good backup or identical setup.

OR

2. If Backup is not available for the file "/relsec/oracle/product/oraInventory/ContentsXML/comps.xml" please try recreating the Central Inventory itself with below steps :

=> Remove the existing Central inventory (oraInventory)

Use the following command to rename the oraInventory in Linux

mv /relsec/oracle/product/oraInventory /relsec/oracle/product/oraInventory-ORIG

=> Change the directory to the oui location.
cd /relsec/oracle/product/102/oui/bin

=> Attaching the Home for Oracle Database 10.2.0.2.0
Execute runInstaller with -attachHome option for Oracle Home.

./runInstaller -silent -ignoreSysPrereqs -attachHome ORACLE_HOME="/relsec/oracle/product/102" ORACLE_HOME_NAME="APPS9I_TALSEC"

3. The above should recreate the oraInventory with appropriate Oracle Home entries.
4. Please use the latest opatch utility version 10.2.0.5.1 and try applying the Patch 5632264 and let us know the results.

=======================================
To Get the Entire EBSO Environment Info:
=======================================

Generate concurrent processing environment information   (Submit Concurrent Programe)

=======================================

However, you can still have problems. If the request remains in a phase of RUNNING and a status of TERMINATING
after allocating more processes to the manager, then shutdown the concurrent managers, kill any processes from the operating system that won't terminate,
and execute the following sqlplus statement as the APPLSYS user to reset the managers in the FND_CONCURRENT_REQUESTS table:

update fnd_concurrent_requests set status_code='X', phase_code='C' where status_code='T';


Changing Dispatching Priority within the Concurrent Manager
If there are requests that have a higher priority to run over other requests, you can navigate to Concurrent --> Program --> Define to change the priority of a request. If a priority is not set for a request, it will have the same priority as all other requests, or it will be set to the value specified in the user's profile option Concurrent:Priority.


Also, you can specify that a request run using an SQL optimizer mode of FIRST_ROWS, ALL_ROWS, RULE, or CHOOSE, and this can radically effect the performance of the SQL inside the Concurrent request. If several long running requests are submitted together, they can cause fast running requests to have to wait unnecessarily. If this is occurring, try to schedule as many long running requests to run after peak business hours. Additionally, a concurrent manager can be created to run only fast running requests.
Using data Dictionary Scripts with the Concurrent Manager

Few Oracle Applications DBAs understand that sophisticated data dictionary queries can be run to reveal details about the workings within each Concurrent Manager. Oracle provides several internal tables that can be queried from SQL*Plus to see the status of the concurrent requests, and the most important are FND_CONCURRENT_PROGRAMS and FND_CONCURRENT_REQUESTS.

Oracle supplies several useful scripts, (located in $FND_TOP/sql directory), for monitoring the concurrent managers:


afcmstat.sql Displays all the defined managers, their maximum capacity, pids, and their status.
afimchk.sql Displays the status of ICM and PMON method in effect, the ICM's log file, and determines if the concurrent manger monitor is running.
afcmcreq.sql  Displays the concurrent manager and the name of its log file that processed a request.
afrqwait.sql Displays the requests that are pending, held, and scheduled.
afrqstat.sql Displays of summary of concurrent request execution time and status since a particular date.
afqpmrid.sql Displays the operating system process id of the FNDLIBR process based on a concurrent request id. The process id can then be used with the ORADEBUG utility.
afimlock.sql Displays the process id, terminal, and process id that may be causing locks that the ICM and CRM are waiting to get. You should run this script if there are long delays when submitting jobs, or if

you suspect the ICM is in a gridlock with another oracle process.
In addition to these canned scripts you can skill write custom Concurrent Manager scripts. For example, the following query can be executed to identify requests based on the number of minutes the request ran:


conc_stat.sql
set echo off
set feedback off
set linesize 97
set verify off
col request_id format 9999999999    heading "Request ID"
     col exec_time format 999999999 heading "Exec Time|(Minutes)"
    col start_date format a10       heading "Start Date"
     col conc_prog format a20       heading "Conc Program Name"
col user_conc_prog format a40 trunc heading "User Program Name"
spool long_running_cr.lst
SELECT
   fcr.request_id request_id,
   TRUNC(((fcr.actual_completion_date-fcr.actual_start_date)/(1/24))*60) exec_time,
   fcr.actual_start_date start_date,
   fcp.concurrent_program_name conc_prog,
   fcpt.user_concurrent_program_name user_conc_prog
FROM
  fnd_concurrent_programs fcp,
  fnd_concurrent_programs_tl fcpt,
  fnd_concurrent_requests fcr
WHERE
   TRUNC(((fcr.actual_completion_date-fcr.actual_start_date)/(1/24))*60) > NVL('&min',45)
and
   fcr.concurrent_program_id = fcp.concurrent_program_id
and
   fcr.program_application_id = fcp.application_id
and
   fcr.concurrent_program_id = fcpt.concurrent_program_id
and
   fcr.program_application_id = fcpt.application_id
and
   fcpt.language = USERENV('Lang')
ORDER BY
   TRUNC(((fcr.actual_completion_date-fcr.actual_start_date)/(1/24))*60) desc;
         
spool off
Note that this script prompts you for the number of minutes. The output from this query with a value of 60 produced the following output on my database. Here we can see important details about currently-running requests, including the request ID, the execution time, the user who submitted the program and the name of the program.

Enter          value for min: 60
            Exec Time
       
 Request ID (Minutes)  Start Date Conc Program Name    User Program Name
----------- ---------- ---------- -------------------- --------------------------------------
    1445627        218 01-SEP-02  MWCRMRGA             Margin Analysis Report(COGS Breakups)
     444965        211 03-JUL-01  CSTRBICR5G           Cost Rollup - No Report GUI
    1418262        208 22-AUG-02  MWCRMRGA             Margin Analysis Report(COGS Breakups)
     439443        205 28-JUN-01  CSTRBICR5G           Cost Rollup - No Report GUI
     516074        178 10-AUG-01  CSTRBICR6G           Cost Rollup - Print Report GUI
    1417551        164 22-AUG-02  MWCRMRGA             Margin Analysis Report(COGS Breakups)
    1449973        160 03-SEP-02  MWCRMRGA             Margin Analysis Report(COGS Breakups)
     520648        159 13-AUG-01  CSTRBICR5G           Cost Rollup - No Report GUI
     446007        122 03-JUL-01  CSTRBICR5G           Cost Rollup - No Report GUI
     392996        120 01-JUN-01  BMCOIN               Bill and Routing Interface
====================================================================================
Check for unused columns that might cause problem to export direct=y (refer to notes 279327.1)

SQL>select * from dba_unused_col_tabs where table_name='FND_FLEX_VALIDATION_TABLES';
If it returns any row like the following:
OWNER TABLE_NAME COUNT
------------------------------
A FND_FLEX_VALIDATION_TABLES 1

Run this sql:
SELECT OBJ#,COL#,SEGCOL#,name,type# FROM COL$ WHERE OBJ# in(select object_id from dba_objects where object_name='FND_FLEX_VALIDATION_TABLES');

OBJ# COL# SEG COL# NAME TYPE#
2732 15 16 E_NAME 1
.....
2732 0 6 SYS_C00006_03071317:21:10$ 2
.....
2732 20 21 ID_CON_TYPE 1

SYS_C00006_03071317:21:10$ is the column that is unused.

Please drop the unused columns by running the following command:
ALTER TABLE FND_FLEX_VALIDATION_TABLES DROP UNUSED COLUMNS ;

If you are on 9.2.0.3, please repeat the same steps above for table FND_TABLES, and drop unused columns for FND_TABLES if need to.


Check SSO installation status
=============================

SELECT 'SSO: '||decode(instr(v.profile_option_value,'SSO'),0,'NOT ','')||'enabled ('||v.profile_option_value||')'
FROM apps.fnd_profile_options_vl p,apps.fnd_profile_option_values v
WHERE v.level_id=10001 AND (p.profile_option_name='APPS_SSO')
AND (p.profile_option_id=v.profile_option_id);

'SSO:'||DECODE(INSTR(V.PROFILE_OPTION_VALUE,'SSO'),0,'NOT','')||'ENABLED('||V.PROFILE_OPTION_VALUE||')'
------------------------------------------------------------------------------------------------------------------------------------
SSO: NOT enabled (SSWA)

CHECK ATG Version
==========

sqlplus apps/#APPS_PWD#
SET head off Lines 120 pages 100
col p_num format A65
col bug_number format A10
col patch_name format A10
select bug_number, decode(bug_number,
'3438354', '11i.ATG_PF.H'
,'4017300' ,'11i.ATG_PF.H.RUP1'
,'4125550' ,'11i.ATG_PF.H.RUP2'
,'4334965' ,'11i.ATG_PF.H RUP3'
,'4676589' ,'11i.ATG_PF.H RUP4'
,'5382500' ,'11i.ATG_PF.H RUP5 HELP'
,'5473858' ,'11i.ATG_PF.H.5'
,'5674941' ,'11i.ATG_PF.H RUP5 SSO Integrat'
,'5903765' ,'11i.ATG_PF.H RUP6'
,'6117031' ,'11i.ATG_PF.H RUP6 SSO 10g Integration'
,'6330890' ,'11i.ATG_PF.H RUP6 HELP'
,'6241631','11i.ATG_PF.H RUP 7'
,'6936696','11i.ATG_PF.H RUP7 SSO 10g Integration'
,'8248307','11i.ATG_PF.H RUP7 HELP'
) p_num, last_update_date
FROM ad_bugs
WHERE bug_number
IN ( '3438354', '4017300', '4125550', '4334965', '4676589', '5382500', '5473858', '5674941', '5903765', '6117031', '6330890','6241631', '6936696', '8248307' );

The following query can be used to assist in detecting types which have multiple versions:  Doc ID 748109.1

select object_id, owner, object_name, object_type, status
from dba_objects
where object_name in
(select distinct object_name
from dba_objects
where object_type='TYPE' and status='INVALID')
order by owner, object_name;


Check Jinitiator version:
=======================
egrep 's_jinit|s_sun' $CONTEXT_FILE
? Check if s_jinit_ver_dot and s_sun_plugin_ver is already on latest version. If so no need to continue further.

egrep 's_jinit_ver_comma|s_jinit_ver_dot|s_jinit_clsid|s_sun_plugin_type|s_sun_plugin_ver|s_sun_clsid' $CONTEXT_FILE

$ egrep 's_jinit|s_sun' $CONTEXT_FILE
         <sun_plugin_ver oa_var="s_sun_plugin_ver">1.4.2_04</sun_plugin_ver>
         <sun_plugin_type oa_var="s_sun_plugin_type">jinit</sun_plugin_type>
         <sun_clsid oa_var="s_sun_clsid">CAFEEFAC-0014-0002-0004-ABCDEFFEDCBA</sun_clsid>
         <jinit_ver_dot oa_var="s_jinit_ver_dot">1.3.1.28</jinit_ver_dot>
         <jinit_ver_comma oa_var="s_jinit_ver_comma">1,3,1,28</jinit_ver_comma>
         <jinit_clsid oa_var="s_jinit_clsid">CAFECAFE-0013-0001-0028-ABCDEFABCDEF</jinit_clsid>

SQL> select USER_CONCURRENT_QUEUE_NAME, concurrent_queue_name, node_name, node_name2 from fnd_concurrent_queues_vl where USER_CONCURRENT_QUEUE_NAME like '%PCP2%' order by 1;

USER_CONCURRENT_QUEUE_NAME              CONCURRENT_QUEUE_NAME          NODE_NAME                      NODE_NAME2
--------------------------------------- ------------------------------ ------------------------------ ------------------------------
FastFormula Transaction Manager PCP2    FFTM2                          VMOHSNOIL011
INV Remote Procedure Manager PCP2       INVTMRPM2                      VMOHSNOIL011
NOV Custom Manager PCP2                 XXNOV_CUSTMGR2                 VMOHSNOIL011                   VMOHSNOIL008
NOV Payroll Child Manager PCP2          XXNOV_PAYCHLD2                 VMOHSNOIL011                   VMOHSNOIL008
NOV Payroll Manager PCP2                XXNOV_PAYMGR2                  VMOHSNOIL011                   VMOHSNOIL008
Output Post Processor PCP2              FNDCPOPP2                      VMOHSNOIL011                   VMOHSNOIL008
PO Document Approval Manager PCP2       PODAMGR2                       VMOHSNOIL011
Receiving Transaction Manager PCP2      RCVOLTM2                       VMOHSNOIL011
Standard Manager PCP2                   STANDARD2                      VMOHSNOIL011                   VMOHSNOIL008

9 rows selected.

To Check whether forms mode:

more $FORMS60_WEB_CONFIG_FILE|grep connectMode
connectMode=socket

more $FORMS60_WEB_CONFIG_FILE|grep serverName=
serverName=devbimboespana
; serverName=%LeastLoadedHost%

$ more $FORMS60_WEB_CONFIG_FILE|grep domainName=
domainName=.oracleoutsourcing.com

$ more $FORMS60_WEB_CONFIG_FILE|grep serverURL=
serverURL=

DMZ Configuration For R12

(1) Make sure a cold backup taken before start the DMZ configration:
(2) Startup database and database listener only on both RAC nodes (srohsnoil33 and srohsnoil34)
(3) run the script to change the profile options hierarchy type values to SERVRESP
(3.1) log into primay MT (srohsnoil008) as apsroi1i user
(3.2) $ cd $FND_TOP/patch/115/sql
(3.3) connect as APPS user

$ sqlplus apps/<apps_passwd>
SQL> @txkChangeProfH.sql SERVRESP

(4) Backup xml file first
-- /sroi1i/inst/apps/sroi1i_srohsnoil013/appl/admin/sroi1i_srohsnoil013.xml
-- /sroi1i/inst/apps/sroi1i_srohsnoil009/appl/admin/sroi1i_srohsnoil009.xml
-- /sroi1i/inst/apps/sroi1i_srohsnoil010/appl/admin/sroi1i_srohsnoil010.xml
-- /sroi1i/inst/apps/sroi1i_srohsnoil012/appl/admin/sroi1i_srohsnoil012.xml
-- /sroi1i/inst/apps/sroi1i_srohsnoil008/appl/admin/sroi1i_srohsnoil008.xml
-- /sroi1i/inst/apps/sroi1i_srohsnoil011/appl/admin/sroi1i_srohsnoil011.xml

(4) Run Autoconfig on DMZ node first:
(4.1) Log into DMZ node (srohsnoil013) as apsroi1i user

Backup original xml files first
$ cd $AD_TOP/bin
$ ./adconfig.sh run=INSTE8_SETUP contextfile=/sroi1i/inst/apps/sroi1i_srohsnoil013/appl/admin/sroi1i_srohsnoil013.xml appspass=<apps_passwd>

Log out and log back in to source env files

(5) Run Autoconfig on additional MT's with the following order:
(5.1) Run autoconfig on srohsnoil009

$ cd $AD_TOP/bin
$ ./adconfig.sh run=INSTE8_SETUP contextfile=/sroi1i/inst/apps/sroi1i_srohsnoil009/appl/admin/sroi1i_srohsnoil009.xml appspass=<apps_passwd>

Log out and log back in to source env files
(5.2) Run autoconfig on srohsnoil010

$ cd $AD_TOP/bin
$ ./adconfig.sh run=INSTE8_SETUP contextfile=/sroi1i/inst/apps/sroi1i_srohsnoil010/appl/admin/sroi1i_srohsnoil010.xml appspass=<apps_passwd>

Log out and log back in to source env files
(5.3) Run autoconfig on srohsnoil012
$ cd $AD_TOP/bin
$ ./adconfig.sh run=INSTE8_SETUP contextfile=/sroi1i/inst/apps/sroi1i_srohsnoil012/appl/admin/sroi1i_srohsnoil012.xml appspass=<apps_passwd>

Log out and log back in to source env files

(6) Run Autoconfig on PCP nodes:
(6.1) Run autoconfig on PCP-2 node, srohsnoil011:

$ cd $AD_TOP/bin
$ ./adconfig.sh contextfile=/sroi1i/inst/apps/sroi1i_srohsnoil011/appl/admin/sroi1i_srohsnoil011.xml appspass=<apps_passwd>

Log out and log back in to source env files
(6.2) Run autoconfig on PCP-1 node, srohsnoil008:
$ cd $AD_TOP/bin
$ ./adconfig.sh contextfile=/sroi1i/inst/apps/sroi1i_srohsnoil008/appl/admin/sroi1i_srohsnoil008.xml appspass=<apps_passwd>

Log out and log back in to source env files

(7) Start up the Application only on Primary MT (srohsnoil008)
(8) Log into sroi1i Application, and Verify 'Hierarchy Type' is set to 'Server-Responsibility'

Login to Application as SYSADMIN with Application Developer responsibility.

Navigate to Application Developer ==> Profile
Query for 'APPS_WEB_AGENT' ==> and verify the field 'Hierarchy Type' from 'Security' to 'Server-Responsibility'
Query for 'APPS_SERVLET_AGENT'  ==> and verify the field 'Hierarchy Type' from 'Security' to 'Server-Responsibility'
Query for 'APPS_JSP_AGENT' ==> and verify the field 'Hierarchy Type' from 'Security' to 'Server-Responsibility'
Query for 'APPS_FRAMEWORK_AGENT' ==> and verify the field 'Hierarchy Type' from 'Security' to 'Server-Responsibility'

(9) Node Trust Level profile option verify and/or Change

Change the responsibility to "System Administrator"
Navigate to Profile ==> System ==> Click on 'Server', enter 'srohsnoil013' ==>  Query for the profile option 'Node Trust Level', and scroll on RIGHT to see the server name.
==> Click on find ==> On the server level for srohsnoil013 change value from blank to 'External'
==>  Save the record.

(10) Responsiblities Trust Level profile option verify and/or Change
(10.1) The list responsibilities of DMZ are :
HR Employee Self Service I
HR Employee Self Service II
HR Employee Self Service III
HR Employee Self Service IV
HR Employee Self Service V
HR Employee Self Service VII
HR Employee Self Service VIII
HR Employee Self Service XX
HR Employee Self Service SG

(10.2) As "System Administrator" responsibility ==> Navigate to Profile ==> System ==> Click on 'Responbsibility', enter 'HR Employee Self Service%'
==> Query for "Responsibility Trust Level", scroll on RIGHT to see the Responsibility name

it should show 'External'

(10.3) Repeat Step (9.2) for all the listed responsibilities
(11) Profile Change: The profile change should only occur at SERVER level

(11.1) APPS_FRAMEWORK_AGENT (make sure there's no '/' at the end for SERVER level profile)
As "System Administrator" responsibility ==> Navigate to Profile ==> System
==> Click on 'Server', enter 'srohsnoil013' ==> Query for the profile option 'Application Framework Agent', scroll on RIGHT to see the server name.
==> Click on find ==> on the server level for srohsnoil013 change value to "https://sriamyhcmw.srv.com:443"
==> Save the record.

(11.2) APPS_JSP_AGENT (make sure there's no '/' at the end for SERVER level profile)
As "System Administrator" responsibility ==> Navigate to Profile ==> System
==> Click on 'Server', enter 'srohsnoil013' ==> Query for the profile option 'Applications JSP Agent', scroll on RIGHT to see the server name.
==> Click on find ==> on the server level for srohsnoil013 change value to "https://sriamyhcmw.srv.com:443"
==> Save the record.

(11.3) APPS_WEB_AGENT  (make sure there's no '/' at the end for SERVER level profile)
As "System Administrator" responsibility ==> Navigate to Profile ==> System
==> Click on 'Server', enter 'srohsnoil013' ==> Query for the profile option 'Applications Web Agent', scroll on RIGHT to see the server name.
==> Click on find ==> on the server level for srohsnoil013 change value to "https://sriamyhcmw.srv.com:443/pls/sroi1i"
==> Save the record.

(11.4) APPS_SERVLET_AGENT (make sure there's no '/' at the end for SERVER level profile)
As "System Administrator" responsibility ==> Navigate to Profile ==> System
==> Click on 'Server', enter 'srohsnoil013' ==> Query for the profile option 'Apps Servlet Agent', scroll on RIGHT to see the server name.
==> Click on find ==> on the server level for srohsnoil013 change value to "https://sriamyhcmw.srv.com:443/OA_HTML"
==> Save the record.

(12) Assign one of the Responsiblities in (10.1) to SYSADMIN
-- For example: HR Employee Self Service I
(13) Startup MT processes on PCP-2 node (srohsnoil011)
(14) Startup MT processes on DMZ node (srohsnoil013)
(15) Log into DMZ url (https://sriamyhcmw.srv.com) and verify the resonsibility is accessible

HANDY APPS SCRIPTS

Find any profile Values
======================
select PROFILE_OPTION_VALUE from FND_PROFILE_OPTION_VALUES where
PROFILE_OPTION_ID=(Select PROFILE_OPTION_ID from fnd_profile_options where PROFILE_OPTION_NAME ='WF_MAIL_WEB_AGENT');

This is the list of few profile options which Apps DBA use frequently. It is not necessary that you as Apps DBA must know all profile options,
it depends on your implemnetation.

Applications Help Web Agent
Applications Servlet Agent
Applications Web Agent
Concurrent: Active Request Limit
Concurrent: Hold Requests
Concurrent: Multiple Time Zones
Concurrent: Report Access Level
Concurrent: Report Copies
Concurrent: Request priority
Database Instance
Enable Security Group
FND: Debug Log Filename
FND: Debug Log Level
Forms Runtime Parameters
Gateway User ID
ICX: Discoverer Launcher
ICX: Forms Launcher
ICX: Report Launcher
ICX: Limit Connect
ICX: Limit time
ICX: Session Timeout
MO Operating Unit
Node Trust Level
RRA: Delete Temporary Files
RRA: Enabled
RRA: Service Prefix
RRA: Maximum Transfer Size
Self Service Personal Home Page Mode
Sign-On: Audit Level
Signon Password Failure Limit
Signon Password Hard to Guess
Signon Password Length
Signon Password No Reuse
Site Name
Socket Listener Port
TCF: Host
TCF: Port
TWO TASK

Spid.sql

select sid, b.serial#, osuser, b.username, status, process, paddr, spid
from v$process a,v$session b
where spid = '&spid'
and a.addr = b.paddr
and b.username != ' '
order by 1
/

Applied_Patches_11i.sql


  REM
  REM Applied_Patches_11i.sql - used to create 11i Applied_Patches_Report.txt
  REM
   set feedback off
   set linesize 80
   set pagesize 9000

   column patch_name head Bug format a10
   column application_short_name head Product format a7
   column patch_type head Patch_Type format a16
   column patch_level head Patch_Level format a12
   column last_update_date head Applied format a11
   column ReportDate head ReportDate format a20

   prompt
   prompt 11i Applied Patches by Most Recently Applied Date

   SELECT sysdate ReportDate FROM dual;

   SELECT distinct ap.patch_name,
          decode(ab.application_short_name, null, mini.app_short_name,
          ab.application_short_name) application_short_name,
          ap.patch_type patch_type,  mini.patch_level patch_level,
          ap.last_update_date Applied
     FROM applsys.ad_bugs ab, applsys.ad_applied_patches ap,
          applsys.ad_patch_drivers pd, applsys.ad_patch_driver_minipks mini
    WHERE ab.bug_number = ap.patch_name
          and ap.applied_patch_id = pd.applied_patch_id
          and pd.patch_driver_id = mini.patch_driver_id(+)
   ORDER BY ap.last_update_date desc;



shrink_rbs.sql

@rseg
@rbs_info2
select 'alter rollback segment '||r.name ||' shrink ;'
from   v$rollname r,
       v$rollstat s
where s.usn = r.usn
and r.name != 'SYSTEM'
  and  r.name not in  (
                select r.name rname
                from   v$lock l,
                       v$process p,
                       v$rollname r,
                       v$rollstat s,
                       v$session  s1
                where  l.sid = s1.sid (+)
                and    trunc(l.id1(+)/65536) = r.usn
                and    l.type(+) = 'TX'
                and    l.lmode (+) = 6
                and    s.usn = r.usn
                and    s1.paddr = p.addr
                )
/

Rseg

select r.name rname,
       s.rssize "size"
from   v$rollname r,
       v$rollstat s
where s.usn = r.usn
/

rbs_info2

set lines 80
set pages 68
col username format a8
col rname format a7
col sid format 9999
col userid format a8
select r.name rname ,
       p.pid "PID",
       p.spid "SPID",
       NVL(p.username, 'NO TRANSACTION') "USERID",
       p.terminal,
       s.rssize "SIZE"
 ,    s1.username "USERNAME"
  ,    s1.sid
from   v$lock l,
       v$process p,
       v$rollname r,
       v$rollstat s,
       v$session  s1
where  l.sid = s1.sid (+)
and    trunc(l.id1(+)/65536) = r.usn
and    l.type(+) = 'TX'
and    l.lmode (+) = 6
and    s.usn = r.usn
and    s1.paddr = p.addr
order by r.name
/

user_sess.sql

Select distinct usr.user_name
from applsys.fnd_logins l, applsys.fnd_user usr, v$session sess
Where l.user_id =usr.user_id and l.spid = sess.process and l.end_time is null order by usr.user_name;

fnd_waiting_jobs.sql

REM $Header: afrqwait.sql 115.3 2000/01/10 12:25:25 pkm ship $
REM +======================================================================+
REM |   Copyright (c) 1992 Oracle Corporation Belmont, California, USA     |
REM |                       All rights reserved.                           |
REM +======================================================================+
REM FILENAME
REM   pendingi.sql
REM DESCRIPTION
REM   Selects all the Pending Requests with status Q
REM
REM           Ram Bhoopalam   Created
REM +======================================================================+

Set Pages 58
Set Linesize 80

Column Reqst     Format 9999999
Column Orcl      Format A8
Column Priority  Format 99999
Column Program   Format A25
Column Start_At  Format A15
Column Req_Date  Format A15

Column Reqst    HEADING 'Request|ID'
Column Orcl     HEADING 'Oracle|Name'
Column Priority HEADING 'Priority'
Column Program  HEADING 'Program'
Column Req_Date HEADING 'Requested at'
Column Start_At HEADING 'To Start at'

Break On Report
Comput Count OF Reqst ON Report

TTITLE 'Pending for Conflict Resolution Manager'

select request_id Reqst,
       User_Concurrent_Program_Name Program,
       To_Char(Request_Date, 'MM-DD-YY HH24:MI') Req_Date,
       To_Char(Requested_Start_Date, 'MM-DD-YY HH24:MI') Start_At
from fnd_concurrent_requests Fcr, Fnd_Concurrent_Programs_TL Fcp,
     fnd_oracle_userid O
where Status_Code = 'Q'
  And (
       Fcr.Concurrent_Program_Id = Fcp.Concurrent_Program_Id And
          Program_Application_ID = Application_ID )
  And  Hold_Flag = 'N'
  And  Fcr.Oracle_ID = O.Oracle_ID
  And  Requested_Start_Date <= Sysdate
Order By  Requested_Start_Date Asc,
          Decode(Priority, Null, 9999999, Priority) Asc,
          Request_ID Asc
/


TTITLE 'Pending for Conflict Resolution Manager|( requested to run at a later time )'

select request_id Reqst,
       User_Concurrent_Program_Name Program,
       To_Char(Request_Date, 'MM-DD-YY HH24:MI') Req_Date,
       To_Char(Requested_Start_Date, 'MM-DD-YY HH24:MI') Start_At
from fnd_concurrent_requests Fcr, Fnd_Concurrent_Programs_TL Fcp,
     fnd_oracle_userid O
where Status_Code = 'Q'
  And (
       Fcr.Concurrent_Program_Id = Fcp.Concurrent_Program_Id And
          Program_Application_ID = Application_ID )
  And  Hold_Flag = 'N'
  And  Fcr.Oracle_ID = O.Oracle_ID
  And  Requested_Start_Date > Sysdate
Order By  Requested_Start_Date Asc,
          Decode(Priority, Null, 9999999, Priority) Asc,
          Request_ID Asc
/


TTITLE 'Pending for Conflict Resolution Manager|( on Hold )'

select request_id Reqst,
       User_Concurrent_Program_Name Program,
       To_Char(Request_Date, 'MM-DD-YY HH24:MI') Req_Date,
       To_Char(Requested_Start_Date, 'MM-DD-YY HH24:MI') Start_At
from fnd_concurrent_requests Fcr, Fnd_Concurrent_Programs_TL Fcp,
     fnd_oracle_userid O
where Status_Code = 'Q'
  And (
       Fcr.Concurrent_Program_Id = Fcp.Concurrent_Program_Id And
          Program_Application_ID = Application_ID )
  And  Hold_Flag = 'Y'
  And  Fcr.Oracle_ID = O.Oracle_ID
Order By  Requested_Start_Date Asc,
          Decode(Priority, Null, 9999999, Priority) Asc,
          Request_ID Asc
/


TTITLE 'Waiting Concurrent Requests'

select request_id Reqst,
       User_Concurrent_Program_Name Program,
       To_Char(Request_Date, 'MM-DD-YY HH24:MI') Req_Date,
       To_Char(Requested_Start_Date, 'MM-DD-YY HH24:MI') Start_At
from fnd_concurrent_requests Fcr, Fnd_Concurrent_Programs_TL Fcp,
     fnd_oracle_userid O
where Status_Code = 'A'
  And (
       Fcr.Concurrent_Program_Id = Fcp.Concurrent_Program_Id And
          Program_Application_ID = Application_ID )
  And  Fcr.Oracle_ID = O.Oracle_ID
Order By  Requested_Start_Date Asc,
          Decode(Priority, Null, 9999999, Priority) Asc,
          Request_ID Asc
/


TTITLE OFF
Set Pages 60
Set Linesize 80
Clear Breaks
Clear Computes

lock.sql

declare cursor c1 is
 select * from v$lock where request != 0 order by id1, id2;
 wid1            number := -999999;
 wid2            number := -999999;
 wholder_detail  varchar2(200);
 v_err_msg          varchar2(80);
 wsid            number(5);
 wstep           number(2);
 wtype           varchar2(10);
 wobject_name    varchar2(180);
 wobject_name1   varchar2(80);
 wlock_type      varchar2(50);
 w_lastcallet     varchar2(11);
 h_lastcallet     varchar2(11);
   begin
     for c1_rec in c1 loop
       if c1_rec.id1 = wid1 and c1_rec.id2 = wid2 then
          null;
       else
          wstep  := 10;
          select sid , type into wsid , wtype
            from v$lock
            where id1  = c1_rec.id1
              and id2  = c1_rec.id2
              and request = 0
              and lmode != 4;
         dbms_output.put_line('  ');
          wstep  := 20;
         select 'Holder DBU: '||s.username ||' OSU: '||s.osuser ||' DBP:'||p.spid||' APP: '|| s.process ||
              ' SID:' || s.sid || ' Status: ' || s.status  ||
              ' (' ||         floor(last_call_et/3600)||':'||
                                      floor(mod(last_call_et,3600)/60)||':'||
                                      mod(mod(last_call_et,3600),60) ||
              ') Module:'|| module ||
                 ' AppSrvr: ' || substr(replace(machine,'GEIPS-AMER\',null),1,15)
             into wholder_detail
             from v$session s, v$process p
             where s.sid= wsid
               and s.paddr = p.addr;
         dbms_output.put_line(wholder_detail);

         begin
      select decode(wtype,'TX', 'Transaction',
                               'DL', 'DDL Lock',
                               'MR', 'Media Recovery',
                               'RT', 'Redo Thread',
                               'UN', 'User Name',
                               'TX', 'Transaction',
                               'TM', 'DML',
                               'UL', 'PL/SQL User Lock',
                               'DX', 'Distributed Xaction',
                               'CF', 'Control File',
                               'IS', 'Instance State',
                               'FS', 'File Set',
                               'IR', 'Instance Recovery',
                               'ST', 'Disk Space Transaction',
                               'TS', 'Temp Segment',
                               'IV', 'Library Cache Invalida-tion',
                               'LS', 'Log Start or Switch',
                               'RW', 'Row Wait',
                               'SQ', 'Sequence Number',
                               'TE', 'Extend Table',
                               'TT', 'Temp Table',
                               'Un-Known Type of Lock')
              into wlock_type
              from dual;
           declare
             cursor c3 is
               select object_id from v$locked_object
                 where session_id = wsid;
           begin
             wobject_name := '';
             for c3_rec in c3 loop
               select object_type||': '||owner||'.'||object_name
                into wobject_name
                from dba_objects
                where object_id = c3_rec.object_id;
              wobject_name := wobject_name ||' '||wobject_name1;
            end loop;
          exception
            when others then
              wobject_name := wobject_name ||' No Object Found';
          end;
          dbms_output.put_line('Lock Held: '||wlock_type||' for Object :'||wobject_name);
        exception
          when no_data_found then
            dbms_output.put_line('Lock Held: '||wlock_type||' No object found in DBA Objects');
        end;
      end if;
         wstep  := 30;
      select '....   Requestor DBU: '||s.username ||' OSU: '||s.osuser ||' DBP:'||p.spid||' APP: '|| s.process ||
                  ' SID:' || s.sid || ' Status: ' || s.status  ||
                  ' (' ||         floor(last_call_et/3600)||':'||
                                  floor(mod(last_call_et,3600)/60)||':'||
                                  mod(mod(last_call_et,3600),60) ||
                  ') Module:'|| module ||
                ' AppSrvr: ' || substr(replace(machine,'GEIPS-AMER\',null),1,15)
            into wholder_detail
            from v$session s, v$process p
            where s.sid= c1_rec.sid
              and s.paddr = p.addr;
      dbms_output.put_line(wholder_detail);
      wid1  := c1_rec.id1;
      wid2  := c1_rec.id2;
    end loop;
    if wid1 = -999999 then
         wstep  := 40;
      dbms_output.put_line('No one requesting locks held by others');
    end if;
  exception
    when others then
           v_err_msg := (sqlerrm ||'  '|| sqlcode||' step='||to_char(wstep));
      DBMS_OUTPUT.PUT_LINE(v_err_msg);
 end;
/

select 'Alter tablespace ' || tablespace_name || ' end backup' from dba_tablespaces;

lock_on_obj.sql

set lines 250
col OBJECT_NAME for a30
col MACHINE for a20
select
   c.owner,
   c.object_name,
   c.object_type,
   b.sid,
   b.serial#,
   b.status,
   b.osuser,
   b.machine
from
   v$locked_object a ,
   v$session b,
   dba_objects c
where
   b.sid = a.session_id
and
   a.object_id = c.object_id;

crtime.sql

rem ********************************************************************
rem * ccmreq.sql scripts list all the running request .
rem ********************************************************************
set lines 155
col os form A7 head AppProc
col spid form a6 head DBProc
col program form A43 trunc
set pages 60
col time form 9999.99 head Elapsed
col "Req Id" form 9999999999
col "Parent" form a9
col "Prg Id" form 999999
col qname head "Concurrent Manager Queue" format a25 trunc
col sid format 99999 head SID
set recsep off
select q.concurrent_queue_name || ' - ' || target_node qname
      ,a.request_id "Req Id"
      ,decode(a.parent_request_id,-1,NULL,a.parent_request_id) "Parent"
      ,a.concurrent_program_id "Prg Id"
      ,a.phase_code,a.status_code
      ,b.os_process_id "OS"
      ,vs.sid
      ,vp.spid
      ,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 "Time"
      ,c.concurrent_program_name||' - '||
       c2.user_concurrent_program_name "program"
from APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
    ,applsys.fnd_concurrent_queues q
    ,APPLSYS.fnd_concurrent_programs_tl c2
    ,APPLSYS.fnd_concurrent_programs c
    ,v$session vs
    ,v$process vp
where a.controlling_manager = b.concurrent_process_id
  and a.concurrent_program_id = c.concurrent_program_id
  and a.program_application_id = c.application_id
  and c2.concurrent_program_id = c.concurrent_program_id
  and a.phase_code in ('I','P','R','T')
  and b.queue_application_id = q.application_id
  and b.concurrent_queue_id = q.concurrent_queue_id
  and c2.language = 'US'
  and vs.process (+) = b.os_process_id
  and vs.paddr = vp.addr (+)
--order by 9,2,1
order by 9
/

inact.sql

select sid, b.serial#, osuser, b.username, status, process, paddr, spid, module, b.program
from v$process a,v$session b
where status = 'INACTIVE'
and a.addr = b.paddr
and b.username != ' '
order by 1
/

analyzereq.sql

conc_running.sql

SELECT   f.request_id,
         pt.user_concurrent_program_name                             user_concurrent_program_name,
         f.actual_start_date                                         actual_start_date,
         f.actual_completion_date                                    actual_completion_date,
         Floor(((f.actual_completion_date - f.actual_start_date) * 24 * 60 * 60) / 3600)
         ||' HOURS '
         ||Floor((((f.actual_completion_date - f.actual_start_date) * 24 * 60 * 60) - Floor(((f.actual_completion_date - f.actual_start_date) * 24 * 60 * 60) / 3600) * 3600) / 60)
         ||' MINUTES '
         ||Round((((f.actual_completion_date - f.actual_start_date) * 24 * 60 * 60) - Floor(((f.actual_completion_date - f.actual_start_date) * 24 * 60 * 60) / 3600) * 3600 - (Floor((((f.actual_completion_date - f.actual_start_date) * 24 * 60 * 60) - Floor(((f.actual_completion_date - f.actual_start_date) * 24 * 60 * 60) / 3600) * 3600) / 60) * 60)))
         ||' SECS ' time_difference,
         Decode(p.concurrent_program_name,'ALECDC',p.concurrent_program_name
                                                   ||'['
                                                   ||f.description
                                                   ||']',
                                          p.concurrent_program_name) concurrent_program_name,
         Decode(f.phase_code,'R','Running',
                             'C','Complete',
                             f.phase_code) phase,
         f.status_code
FROM     apps.fnd_concurrent_programs p,
         apps.fnd_concurrent_programs_tl pt,
         apps.fnd_concurrent_requests f
WHERE    f.concurrent_program_id = p.concurrent_program_id
         AND f.program_application_id = p.application_id
         AND f.concurrent_program_id = pt.concurrent_program_id
         AND f.program_application_id = pt.application_id
         AND pt.language = Userenv('Lang')
         AND f.actual_start_date IS NOT NULL
         AND f.phase_code = 'R'
ORDER BY f.actual_completion_date - f.actual_start_date DESC;

long.sql

select  sid||','||serial# "Sess" , qcsid , username , opname , target , message from v$session_longops
where totalwork <>sofar;

ses.sql

select a.name, b.statistic#, b.value, b.sid
from v$sysstat a, v$sesstat b
where a.statistic# = b.statistic#
and b.sid = &1
and a.name like '%table%' ;

session_wait_info.sql

ttitle 'Events Sessions are Waiting For'

column   event ON format   a30
column   p3text ON format   a15
column   p2text ON format   a15
column   p1text ON format   a15

set linesize 132
set pagesize 54

select event, p1, p1text, p2, p2text, p3, p3text, count(*)
from   v$session_wait
group by event, p1, p1text, p2, p2text, p3, p3text
order by count(*)
/

session_cache_hit_ratio.sql

select v$sess_io.* , 1-(PHYSICAL_READS/(BLOCK_GETS+CONSISTENT_GETS)) cache_hit_ratio, username
from   v$sess_io, v$session
where physical_reads > 0
and    1-(PHYSICAL_READS/(BLOCK_GETS+CONSISTENT_GETS)) < .90
and (block_gets  > 0 or consistent_gets > 0)
and v$session.sid = v$sess_io.sid
order by  1-(PHYSICAL_READS/(BLOCK_GETS+CONSISTENT_GETS))
/

sessdtl.sql

select s.sid , s.serial# , s.process , s.status ,
       OSUser "osuser" , p.spid , s.USERNAME  "user"
  from v$process p, v$session s
 where addr = paddr
 and s.sid = &Session_ID
 order by status , sid
/

sesswait.sql

select s.sid,s.serial#,
s.status,w.event,
w.p1,w.p2,w.state,
w.wait_time,
w.seconds_in_wait
from v$session s,v$session_wait w where
s.sid=w.sid
and w.seconds_in_wait >3600;

conc_det_new.sql

set line 80
set head off
set trimspool on
set verify off

col request        format  A80

ttitle center 'Concurrent Request Details' skip 2

SELECT 'Request Id:          ' ||to_char(a.request_id) request,
       'Parent Request Id:   ' ||decode(to_char(parent_request_id),'-1',null,to_char(parent_request_id)) request,
       'Program Name:        ' ||substr(b.user_concurrent_program_name,1,50) request,
       'Req Start Time:      ' ||to_char(a.requested_start_date,'DD-MON-RR HH24:MI:SS') request,
       'Start Time:          ' ||to_char(a.actual_start_date,'DD-MON-RR HH24:MI:SS') request,
       'End Time:            ' ||to_char(a.actual_completion_date,'DD-MON-RR HH24:MI:SS') request,
       'Execution Time:      ' || to_char(round(trunc(((actual_completion_date-actual_start_date)*24*60*60)/60)+(((actual_completion_date-actual_start_date)*24*60*60)-(trunc(((actual_completion_date-actual_start_date)*24*60*60)/60)*60))/100,2)) request,
       'User:                ' || fu.user_name request,
       'Phase:               ' || decode(a.phase_code,'R','Running','P','Inactive','C','Completed', a.phase_code) request,
       'Status:              ' || decode(a.status_code,'E','Error',   'C','Normal',    'X','Terminated', 'Q','On Hold', 'D','Cancelled', 'G','Warning', 'R','Normal',  'W', 'Paused', a.status_code) request,
       'Parameters:          ' || substr(a.argument_text,1,55) request,
       '                     ' || substr(a.argument_text,56,55) request,
       '                     ' || substr(a.argument_text,111,55) request
FROM apps.fnd_user fu,
     apps.fnd_concurrent_programs_vl b,
     apps.fnd_concurrent_requests a
WHERE  a.concurrent_program_id = b.concurrent_program_id
and fu.user_id = a.requested_by
AND a.request_id = &request_id
;
set pagesize 24
set verify on
set head on
ttitle off


conc_process.sql

select b.request_id,a.USER_CONCURRENT_PROGRAM_NAME
from apps.fnd_concurrent_programs_vl a, apps.fnd_concurrent_requests b
where a.concurrent_program_id = b.concurrent_program_id
  and b.status_code = 'R'
  and b.phase_code = 'R'
order by 1
/


Definition of any DDL
======================
select dbms_metadata.get_ddl('TABLE','FND_LOGINS','APPLSYS') from dual;

Find Product Patch Level / Version
==================================
select PATCH_LEVEL from fnd_product_installations where
APPLICATION_ID in (select APPLICATION_ID from fnd_application where APPLICATION_SHORT_NAME like '%AD%');

select PATCH_NAME from ad_applied_patches where PATCH_NAME='3453499';


Select name, name into: visible_option_value, :profile_option_value from v$database
select node_name,target_node from fnd_concurrent_queues;


insert into table apps.fnd_concurrent_requests (select * from fnd_concurrent_requests_bkp);

select LOGFILE_NAME, LOGFILE_NODE_NAME, OUTFILE_NAME, OUTFILE_NODE_NAME from fnd_concurrent_requests where REQUEST_ID=1091889;
select max_processes, running_processes, CONCURRENT_QUEUE_NAME from FND_CONCURRENT_QUEUES where CONCURRENT_QUEUE_NAME  like 'XDP%' and CONCURRENT_QUEUE_NAME not like 'XDP_MANAGER';
select max_processes, running_processes, CONCURRENT_QUEUE_NAME from FND_CONCURRENT_QUEUES
    where CONCURRENT_QUEUE_NAME  like 'XDP%';

Validating a user Passowrd
===========================
select fnd_web_sec.validate_login('sysadmin','br3adman') from dual;
select fnd_web_sec.validate_login('bol-support','****************') from dual;

   
update wf_event_subscriptions set system_guid =wf_core.translate('WF_SYSTEM_GUID') where
    system_guid <> wf_core.translate('WF_SYSTEM_GUID');

select name, status from wf_events
    where guid in (select event_filter_guid from wf_event_subscriptions where
    system_guid <> wf_core.translate('WF_SYSTEM_GUID'));


Select profile_option_value from
applsys.fnd_profile_option_values where profile_option_id in
(Select a.profile_option_id from  applsys.fnd_profile_option_values A, applsys.fnd_profile_options B
where a.profile_option_id=b.profile_option_id
and profile_option_name like '%ICX%REPORT%SERVER');


select MESSAGE_NAME, MESSAGE_TEXT
  from FND_NEW_MESSAGES, FND_APPLICATION
  where FND_NEW_MESSAGES.LANGUAGE_CODE ='xx'
        and FND_NEW_MESSAGES.APPLICATION_ID = FND_APPLICATION.APPLICATION_ID
        and FND_APPLICATION.APPLICATION_SHORT_NAME = 'MSD'
        and message_text like '%Search String%'

WF PARAMETERS
=============

SELECT
 c.parameter_name,
 a.parameter_value
 FROM fnd_svc_comp_param_vals a,
 fnd_svc_components b,
 fnd_svc_comp_params_b c
 WHERE b.component_id = a.component_id
 AND b.component_type = c.component_type
 AND c.parameter_id = a.parameter_id
 AND c.encrypted_flag = 'N'
 AND b.component_name = 'Workflow Notification Mailer'
 ORDER BY c.parameter_name;

Query To Set Open notifications
==============================
Update wf_notifications set status = 'CLOSED', mail_status = 'SENT',end_date = sysdate;  372933.1

bash-2.05b$ diff htmlvars_apps.js .snapshot/AUTOHOTSNAP_auohscoar16_TCOA6I_CODE_20070128/htmlvars_apps.js
$APACHE_TOP/Apache/htdocs/discwb4/scripts/

Using the System Administrator responsibility, navigate to Globalpreferences, change the Workflow administrator field from SYSADMIN to
workflow administrator web applications (TAR # 4499451.993).
   
SQL> update wf_resources set text='FND_RESP|FND|FNDWF_ADMIN_WEB_NEW|STANDARD' where name = 'WF_ADMIN_ROLE';

select COMPONENT_TYPE,COMPONENT_NAME,COMPONENT_STATUS  from FND_SVC_COMPONENTS;

update apps.fnd_concurrent_requests set hold_flag = 'Y', status_code = 'H', phase_code = 'I' where phase_code = 'P';


select user_end_date from wf_local_user_roles where user_name='SYSADMIN';
update wf_local_user_roles set USER_END_DATE=null where USER_NAME='SYSADMIN' and USER_END_DATE='28-OCT-06';
select effective_end_date from wf_local_user_roles where effective_end_date
='28-OCT-06';
update wf_local_user_roles set EFFECTIVE_END_DATE='31-MAR-12' where EFFECTIVE_END_DATE='28-OCT-06';

select tablespace_name,sum(maxbytes/1024/1024/1024) " size in GB " from dba_temp_files group by tablespace_name;
select tablespace_name,sum(maxbytes/1024/1024/1024) " size in GB " from dba_data_files group by tablespace_name;

Submit a concurrent request using CONCSUB
------------------------------------------
CONCSUB apps/BvGUWXhk SYSADMIN 'System Administrator' SYSADMIN CONCURRENT FND FNDCPPUR START="$PURGE_TIME" REPEAT_DAYS=1 REPEAT_INTERVAL_TYPE="START" "ALL" "Age" "30" '""' '""' '""' '""' '""' '""' '""' '""' "Yes" "Yes"



update alr_alerts set   enabled_flag='N' , end_date_active = sysdate, last_update_date = sysdate
where alert_name like 'ALERTNAME'

COL USER_CONCURRENT_QUEUE_NAME FOR A35
COL CONCURRENT_QUEUE_NAME FOR A23


SELECT APPLSYS.FND_CONCURRENT_QUEUES_TL.USER_CONCURRENT_QUEUE_NAME,
APPLSYS.FND_CONCURRENT_QUEUES.CONCURRENT_QUEUE_NAME,
APPLSYS.FND_CONCURRENT_QUEUES.RUNNING_PROCESSES
FROM   APPLSYS.FND_CONCURRENT_QUEUES, APPLSYS.FND_CONCURRENT_QUEUES_TL
WHERE  APPLSYS.FND_CONCURRENT_QUEUES.CONCURRENT_QUEUE_NAME
IN ('FNDICM', 'STANDARD', 'FNDCRM', 'FNDSM_AUTHENTICATION',
'INVMGR', 'WFMLRSVC', 'WFWSSVC', 'WFALSNRSVC', 'XDP_Q_EVENT_SVC', 'FNDSCH',
'FNDSM_AUOHSF5NE01', 'FNDICM', 'INVTMRPM', 'PODAMGR', 'ZIPQM')
AND   (APPLSYS.FND_CONCURRENT_QUEUES.CONCURRENT_QUEUE_ID=APPLSYS.FND_CONCURRENT_QUEUES_TL.CONCURRENT_QUEUE_ID)
ORDER BY APPLSYS.FND_CONCURRENT_QUEUES_TL.USER_CONCURRENT_QUEUE_NAME ASC
/

select profile_option_id, profile_option_name from fnd_profile_options where profile_option_name = 'SITENAME'
select * from fnd_profile_option_values where  profile_option_id = 125;

Assign new value:

update fnd_profile_option_values
   set profile_option_value = '&new_value '|| trunc(sysdate),
       last_update_date = sysdate,
       last_updated_by = (select user_id from fnd_user where user_name = 'SYSADMIN')
 where profile_option_id = (select profile_option_id
                              from fnd_profile_options
                             where profile_option_name = 'SITENAME');


==================================================

VERTEXT INSTALLATION USING vprtmupd

/sroi1i/applmgr/1200/pay/12.0.0/vendor/quantum/data

$ cd /sroi1i/applmgr/1200/pay/12.0.0/vendor/quantum/utils/utils
cbmaint  diagnos  diagnos.tst  L6909641.log  locupd  vprtmupd  vprtrep
$ cbmaint
Vertex Payroll Tax Q Series - ISAM Database Maintenance Program

Version 3.0.1 2011/12/16

Copyright 1999-2012 Vertex Inc.


Select an ISAM Maintenance Function
-----------------------------------

1.  - Create Database
2.  - Migrate Database
3.  - Reindex Database

9.  - Exit Program

Select: 1

Select a Database to Create
----------------------------

1.  - GeoCoder Database
2.  - Payroll Tax Database
3.  - All Databases
9.  - Main Menu

Select: 2

Enter Datasource Directory: /sroi1i/applmgr/1200/pay/12.0.0/vendor/quantum/data

Database Created
Payroll database creation successful (36 .dbf files).

Select a Database to Create
----------------------------

1.  - GeoCoder Database
2.  - Payroll Tax Database
3.  - All Databases
9.  - Main Menu

Select: 9

Select an ISAM Maintenance Function
-----------------------------------

1.  - Create Database
2.  - Migrate Database
3.  - Reindex Database

9.  - Exit Program

Select: 9
$ vprtmupd


Vertex Payroll Tax Q Series - Monthly Update Program

Version 3.0.1    2011/12/16

Copyright (c) Vertex Inc.  1994-2011.  All Rights Reserved.

WARNING! The update process will destroy the contents of the
current database before it creates a new one.  Please make
a backup before proceeding.


Select a Monthly Update Function
--------------------------------
1.  - Update Payroll Tax Database
2.  - Exit Program

Select: 1

Beginning Payroll Tax Monthly Update Process.

Enter Data Source:   /sroi1i/applmgr/1200/pay/12.0.0/vendor/quantum/data
Enter the path to the update file:  /sroi1i/applmgr/1200/pay/12.0.0/vendor/quantum/data

Updating database. Please wait. It may take a while...

A database error occurred during the update process. Restore the old database
and retry.
Press Enter to continue...

Any issues we can also diagnose as follows
==========================================

$ diagnos


Vertex Payroll Tax Q Series - Diagnostic Program

Version 3.0.1    2011/12/16

Copyright (c) Vertex Inc.  1994-2011.  All Rights Reserved.


Select Diagnostic Function
--------------------------

1.  - Test Vertex Payroll Tax Q Series Payroll Database Connection
2.  - Test Vertex Payroll Tax Q Series Location Database Connection
3.  - Test Vertex Payroll Tax Q Series Calculation

9.  - Exit Program

Select:  1

Enter database connection information ==>
Datasource:  /sroi1i/applmgr/1200/pay/12.0.0/vendor/quantum/data

==> Vertex Payroll Tax Q Series: payroll database open successful. <==

>> Payroll database schema version 3.0.0 and
program schema version 3.0.0 match. <<

>> Payroll database is not populated. <<

==> Vertex Payroll Tax Q Series: payroll database close successful. <==


Select Diagnostic Function
--------------------------

1.  - Test Vertex Payroll Tax Q Series Payroll Database Connection
2.  - Test Vertex Payroll Tax Q Series Location Database Connection
3.  - Test Vertex Payroll Tax Q Series Calculation

9.  - Exit Program

Select:9

Queries for Applications

To Know the definition of a DDL Commands.
=========================================
select  dbms_metadata.get_ddl ('TABLE', 'TABLE_NAME', 'SCHEMA_NAME') from dual

Tablspace Size Info
==================

select a.TABLESPACE_NAME, a.MAX, b.USED,
round((b.USED/a.MAX)*100,2) PCT  from
(select TABLESPACE_NAME, sum(MAXBYTES/1024/1024)
MAX from dba_Data_files group by TABLESPACE_NAME) a,(select tablespace_name, sum(bytes/1024/1024) USED from dba_segments group by tablespace_name) b where a.TABLESPACE_NAME=b.TABLESPACE_NAME and b.USED > a.MAX *(60/100)
===========================================================================
USER RESPONSIBILITY

select t1.user_name, t1.end_date, t3.responsibility_name, t2.creation_date
from fnd_user t1, fnd_user_resp_groups t2, fnd_responsibility_vl t3
where t1.user_id = t2.user_id
and t2.responsibility_id = t3.responsibility_id
and t3.responsibility_name like '&responsibility_name';
===========================================================================

To Find a Provile Value
=======================
SELECT PROFILE_OPTION_NAME, PROFILE_OPTION_VALUE FROM APPS.FND_PROFILE_OPTIONS
A,APPS.FND_PROFILE_OPTION_VALUES B WHERE A.PROFILE_OPTION_ID=B.PROFILE_OPTION_ID AND
PROFILE_OPTION_NAME LIKE 'SITENAME';
=============================================================================
Find a SQL Query by using SID
============================

select s.username, s.status,s.SQL_ADDRESS,t.SQL_TEXT,s.last_call_et/3600
 from v$session s,v$sqltext t
 where t.ADDRESS=s.SQL_ADDRESS and s.sid=&sid order by piece;
===================================================================
To See the profile values for a particular USER
===============================================
select
n.profile_option_name,
n.user_profile_option_name NAME,
decode(v.level_id,
10001, 'Site',
10002, 'Application',
10003, 'Responsibility',
10004, 'User',
10005, 'Server',
10006, 'Org',
10007, 'SERVRESP',
'UnDef') LEVEL_SET,
decode(to_char(v.level_id),
'10001', '',
'10002', app.application_short_name,
'10003', rsp.responsibility_key,
'10004', usr.user_name,
'10005', svr.node_name,
'10006', org.name,
'10007', 'Serv/resp',
'UnDef') "CONTEXT",
v.profile_option_value VALUE,
usr.user_name
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 usr.user_name in ('&UserName')
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 1,2,3,6;
=====================================================
To Update any profile Values
=====================================================
update /*+parallel (a,8) */ fnd_profile_option_values a
2 set a.profile_option_value= replace(a.profile_option_value,'appsnidcprod-nmcmotor.oracle.com','appsnidcprod.nmcmotor.com')
3 where upper(a.profile_option_value) like upper('%appsnidcprod-nmcmotor.oracle.com%');

========================================
select dbms_metadata.get_ddl('TABLE','FND_LOGINS','APPLSYS') from dual;
select 'alter system kill session ''' || sid||','||serial#||''' IMMEDIATE ' || ';' from v$session where status ='INACTIVE';
select 'alter PACKAGE ''' || OWNER||'.'|| OBJECT_NAME|| '''  '  ||  'compile body ;' from dba_objects where status ='INVALID';
select to_char(STARTUP_TIME,'dd-mon-yyyy hh24:mi:ss') from v$instance;
================================================

PCP CM CHECKS
=============
select distinct node_name,node_name2 from fnd_concurrent_queues;
SELECT COUNT(*) FROM WF_NOTIFICATIONS WHERE MAIL_STATUS IN ('MAIL', 'INVALID', 'OPEN');
Select running_processes,max_processes from apps.fnd_concurrent_queues where running_processes > 0 or max_processes > 0;
select * from fnd_profile_option_values where PROFILE_OPTION_ID=125;

select t.PROFILE_OPTION_ID , z.USER_PROFILE_OPTION_NAME , v.PROFILE_OPTION_VALUE, v.level_id
  from fnd_profile_options t, fnd_profile_option_values v, fnd_profile_options_tl z
  where v.PROFILE_OPTION_ID = t.PROFILE_OPTION_ID
  and z.PROFILE_OPTION_NAME = t.PROFILE_OPTION_NAME
  and t.PROFILE_OPTION_NAME in ('WF_MAIL_WEB_AGENT') ;