Tried most methods. The only one that seems to work correctly is dump the 2 tables from another system and import them into the current one. Then run FNDCPASS.
Some useful documents from Oracle
One of the things customers want to do is replace the logo. Personalize Oracle Apps 11i (E-business Suite)
Below steps to get the forgotten apps user password in oracle apps R12.
Step 1: Connect to sys user
SQL> show user
USER is "SYS"
Step 2: Create function to know the encrypted password
SQL> create FUNCTION apps.decrypt_pin_func(in_chr_key IN VARCHAR2,in_chr_encrypted_pin IN VARCHAR2) RETURN VARCHAR2 AS LANGUAGE JAVA NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';
/
Function created.
Step 3: Query for password
SQL> set linesize 200 long 300
SQL> select ENCRYPTED_FOUNDATION_PASSWORD from apps.fnd_user where USER_NAME='GUEST';
ENCRYPTED_FOUNDATION_PASSWORD
----------------------------------------------------------------------------------------------------
ZGC679A64D8394F23E12CA4EB288F264FC09EBC9144C06181E921F88A972E231E9B530E7810DE42AC6103FC3CCD317CA3391
Step 4: Apps password using encrypted guest password
SQL> SELECT apps.decrypt_pin_func('GUEST/ORACLE','ZGC679A64D8394F23E12CA4EB288F264FC09EBC9144C06181E921F88A972E231E9B530E7810DE42AC6103FC3CCD317CA3391') from dual;
APPS.DECRYPT_PIN_FUNC('GUEST/ORACLE','ZGC679A64D8394F23E12CA4EB288F264FC09EBC9144C06181E921F88A972E231E9B530E7810DE42AC6103FC3CCD317CA3391')
---------------------------------------------------------------------------------------------------------------------------------------------
PASSWORD
Step 5: Test the password is working fine or not
SQL> conn apps/PASSWORD;
Connected
These user passwords must be changed using FNDCPASS otherwise they (and the Application) will become unusabe.
If FNDCPASS does not work but you think it should (ie: you're pretty sure the password is correct), check the sec_case_sensitive parameter! It should be false for EBS.
Trying to change the password without using FNDCPASS will fail
cat L5217782.log +---------------------------------------------------------------------------+ Application Object Library: Version : 12.1 Copyright (c) 1998, 2013, Oracle and/or its affiliates. All rights reserved. FNDCPASS: +---------------------------------------------------------------------------+ Current system time is 26-SEP-2023 16:06:36 +---------------------------------------------------------------------------+ Arguments FNDCPASS system/***** SYSTEM APPLSYS ***** +----------------------------------------------------------------------------+ Working... Error in password verification for APPS. +---------------------------------------------------------------------------+ Concurrent request completed Current system time is 26-SEP-2023 16:06:37 +---------------------------------------------------------------------------+
It used to be possible to recover the situation by updating the fnd_oracle_userid table with a known encrypted password
alter user apps identified by apps account unlock;
update apps.fnd_oracle_userid
set encrypted_oracle_password ='ZGA34EA20B5C4C9726CC95AA9D49EA4DBA8EDB705CB7673E645EED570D5447161491D78D444554655B87486EF537ED9843C8'
where oracle_username in ('APPS','APPLSYS');
but this doesn't seem to work any more.
A way that still works (if there is no table-level backup), is to export the tables from a good database and import them into the problem database.
The passwords will obviously then be the same as those on the source database.
expdp \'system/password@EBSP\' directory=data_pump_dir tables="applsys.fnd_user, applsys.fnd_oracle_userid" dumpfile=fnd_passwords.dmp logfile=expdp_fnd_passwords.log exclude=statistics
impdp \'system/password@EBSA\' directory=data_pump_dir dumpfile=fnd_passwords.dmp logfile=impdp_fnd_passwords.log table_exists_action=replace
alter user apps identified by "password" account unlock; alter user applsys identified by "password" account unlock;
FNDCPASS apps/"password" 0 Y system/manager SYSTEM APPLSYS "someotherpassword" cat L5218783.log +---------------------------------------------------------------------------+ Application Object Library: Version : 12.1 Copyright (c) 1998, 2013, Oracle and/or its affiliates. All rights reserved. FNDCPASS: +---------------------------------------------------------------------------+ Current system time is 26-SEP-2023 16:30:43 +---------------------------------------------------------------------------+ Arguments FNDCPASS system/***** SYSTEM APPLSYS ***** +----------------------------------------------------------------------------+ Working... FNDCPASS completed successfully. +---------------------------------------------------------------------------+ Concurrent request completed successfully Current system time is 26-SEP-2023 16:30:44 +---------------------------------------------------------------------------+
Include this info when creating a technical SR
includes the version / release of Oracle Applications (fnd_product_groups)
conn apps/apps
set lines 1000
col release_name for a20
col codelevel for a10
select release_name from fnd_product_groups;
select version from v$instance;
select abbreviation, codelevel from ad_trackable_entities where abbreviation in ('txk','ad');
As detailed in SRDC - Data Collection for E-Business Suite Cloning / Rapid Cloning Issues ( Doc ID 2108567.1 ) above, these things do not necessarily work properly. For example, TWO_TASK is not defined on the DB Tier so:
TWO_TASK=$ORACLE_SID
before running the zip command here. But then it still won't work if you used adcfgclone.pl with soemthing other than DBTier as a parameter (like me as I used Section 4, Option 6 in the Cloning document).
1. On the target database tier, issue the following zip command: $ zip -r /tmp/$TWO_TASK'_'`uname -n`_`date +%m%d%y.%H%M`_DB_Clone_logs.zip \ /tmp/adcfgclone_*.res \ /tmp/adcfgclone_*.err \ $ORACLE_HOME/appsutil/temp/portpool.lst \ $ORACLE_HOME/clone/bin/CloneContext_*.log \ $ORACLE_HOME/appsutil/log/$CONTEXT_NAME/ohclone.log \ $ORACLE_HOME/appsutil/log/$CONTEXT_NAME/make_*.log \ $ORACLE_HOME/appsutil/log/$CONTEXT_NAME/ApplyDBTier_*.log \ $ORACLE_HOME/appsutil/log/$CONTEXT_NAME/NetServiceHandler.log \ $ORACLE_HOME/appsutil/log/$CONTEXT_NAME/adcrdb_*.txt
The following command works unmodified.
2. On the target application tier, issue the following zip command: $ zip -r /tmp/$TWO_TASK'_'`uname -n`_`date +%m%d%y.%H%M`_APP_Clone_logs.zip \ /tmp/adcfgclone_*.res \ /tmp/adcfgclone_*.err \ $COMMON_TOP/clone/bin/CloneContext_*.log \ $INST_TOP/admin/out/portpool.lst \ $INST_TOP/admin/log/ohclone.log \ $INST_TOP/admin/log/NetServiceHandler.log \ $INST_TOP/admin/log/ApplyAppsTier_*.log \ $LOG_HOME/ora/10.1.3/install/make_*.log \ $LOG_HOME/ora/10.1.2/install/make_*.log \ $LOG_HOME/ora/10.1.2/network/apps_*.log
Then they request you do this…
3. Download and run the Clone Log Parser. This is a tool used to consolidate error information from various Rapidclone related log files into an HTML report. A single cloning session may generate up to a dozen separate log files, the Log Parser allows you to view relevant snippets of each log at one time. Additionally the Log Parser will do some basic configuration and health checks of the environment and also may provide leads to solving problems. Review note EBS Clone Log Parser Utility (Rapid Clone 11i,12.0, 12.1)[VIDEO] ( Doc ID 1447553.1 ).
Following the instructions in that document, it asks you to unset PERL5LIB but then perl cannot find strict.pm!
Don't forget Database tier can be a different platform from the Apps tier so 2 versions may be needed (one AIX and one Linux?)!
In order to check your OS prerequisites in automatic way please use Note 250262.1 RDA 4 - Health Check / Validation Engine Guide . After following “Installation Instructions” please follow “Instructions for UNIX type operating systems”. I’ll attach an example : download the patch unzip cd rda [applmgr@soatest rda]$ ./rda.sh -T hcve Processing HCVE tests ... Available Pre-Installation Rule Sets: From the menu select the number corresponding to Oracle E-Business Suite Release 12.x Preinstall 1. Upload the generated html file
select release_name from fnd_product_groups;
select
BUG_NUMBER,
LAST_UPDATE_DATE,
decode(bug_number,2728236, 'OWF.G INCLUDED IN 11.5.9',
3031977, 'POST OWF.G ROLLUP 1 - 11.5.9.1',
3061871, 'POST OWF.G ROLLUP 2 - 11.5.9.2',
3124460, 'POST OWF.G ROLLUP 3 - 11.5.9.3',
3126422, '11.5.9 Oracle E-Business Suite Consolidated Update 1',
3171663, '11.5.9 Oracle E-Business Suite Consolidated Update 2',
3316333, 'POST OWF.G ROLLUP 4 - 11.5.9.4.1',
3314376, 'POST OWF.G ROLLUP 5 - 11.5.9.5',
3409889, 'POST OWF.G ROLLUP 5 Consolidated Fixes For OWF.G RUP 5', 3492743, 'POST OWF.G ROLLUP 6 - 11.5.9.6',
3868138, 'POST OWF.G ROLLUP 7 - 11.5.9.7',
3262919, 'FMWK.H',
3262159, 'FND.H INCLUDE OWF.H',
3258819, 'OWF.H INCLUDED IN 11.5.10',
3438354, '11i.ATG_PF.H INCLUDE OWF.H',
3140000, 'ORACLE APPLICATIONS RELEASE 11.5.10 MAINTENANCE PACK',
3240000, '11.5.10 ORACLE E-BUSINESS SUITE CONSOLIDATED UPDATE 1',
3460000, '11.5.10 ORACLE E-BUSINESS SUITE CONSOLIDATED UPDATE 2',
3480000, 'ORACLE APPLICATIONS RELEASE 11.5.10.2 MAINTENANCE PACK',
4017300 , 'ATG_PF:11.5.10 Consolidated Update (CU1) for ATG Product Family',
4125550 , 'ATG_PF:11.5.10 Consolidated Update (CU2) for ATG Product Family',
5121512, 'AOL USER RESPONSIBILITY SECURITY FIXES VERSION 1',
6008417, 'AOL USER RESPONSIBILITY SECURITY FIXES 2b',
6047864, 'REHOST JOC FIXES (BASED ON JOC 10.1.2.2) FOR APPS 11i',
4334965, '11i.ATG_PF.H RUP3',
4676589, '11i.ATG_PF.H.RUP4',
5473858, '11i.ATG_PF.H.RUP5',
5903765, '11i.ATG_PF.H.RUP6',
6241631, '11i.ATG_PF.H.RUP7',
4440000, 'Oracle Applications Release 12 Maintenance Pack',
5082400, '12.0.1 Release Update Pack (RUP1)',
5484000, '12.0.2 Release Update Pack (RUP2)',
6141000, '12.0.3 Release Update Pack (RUP3)',
6435000, '12.0.4 RELEASE UPDATE PACK (RUP4)',
5907545, 'R12.ATG_PF.A.DELTA.1',
5917344, 'R12.ATG_PF.A.DELTA.2',
6077669, 'R12.ATG_PF.A.DELTA.3',
6272680, 'R12.ATG_PF.A.DELTA.4',
7237006, 'R12.ATG_PF.A.DELTA.6',
6728000, '12.0.6 RELEASE UPDATE PACK (RUP6)',
7303030, '12.1.1 Maintenance Pack',
7651091, 'Oracle Applications Technology Release Update Pack 2 for 12.1 (R12.ATG_PF.B.DELTA.2)',
7303033, 'Oracle E-Business Suite 12.1.2 Release Update Pack (RUP2)',
9239089, 'Oracle Applications DBA 12.1.3 Product Release Update Pack',
8919491, 'Oracle Applications Technology 12.1.3 Product Family Release Update Pack',
9239090, 'ORACLE E-BUSINESS SUITE 12.1.3 RELEASE UPDATE PACK',
bug_number),
ARU_RELEASE_NAME
from AD_BUGS b
where b.BUG_NUMBER in ('2728236', '3031977','3061871','3124460','3126422','3171663','3316333','3314376','3409889', '3492743', '3262159', '3262919', '3868138', '3258819','3438354','32400
00', '3460000', '3140000','3480000','4017300', '4125550', '6047864', '6008417','5121512', '4334965', '4676589', '5473858', '5903765', '6241631', '4440000','5082400','5484000','6141000',
'6435000',
'5907545','5917344','6077669','6272680','7237006','6728000','7303030', '7651091', '7303033', '9239089', '8919491', '9239090')
order by BUG_NUMBER,LAST_UPDATE_DATE,ARU_RELEASE_NAME
/
BUG_NUMBER LAST_UPDATE_DATE DECODE(BUG_NUMBER,2728236,'OWF.GINCLUDEDIN11.5.9',3031977,'POSTOWF.GROLLUP1-11.5.9.1 ARU_RELEASE_NAME ------------------------------ ------------------------ ------------------------------------------------------------------------------------ ------------------------------ 2728236 26-JUN-03 01:53:29 OWF.G INCLUDED IN 11.5.9 11i 3031977 01-NOV-04 04:09:18 POST OWF.G ROLLUP 1 - 11.5.9.1 11i 3061871 01-NOV-04 04:10:06 POST OWF.G ROLLUP 2 - 11.5.9.2 11i 3124460 01-NOV-04 04:10:05 POST OWF.G ROLLUP 3 - 11.5.9.3 11i 3140000 01-NOV-04 04:01:22 ORACLE APPLICATIONS RELEASE 11.5.10 MAINTENANCE PACK 11i 3258819 01-NOV-04 04:09:18 OWF.H INCLUDED IN 11.5.10 11i 3262159 01-NOV-04 04:10:06 FND.H INCLUDE OWF.H 11i 3262919 01-NOV-04 04:10:27 FMWK.H 11i 3314376 01-NOV-04 04:10:27 POST OWF.G ROLLUP 5 - 11.5.9.5 11i 3316333 01-NOV-04 04:10:27 POST OWF.G ROLLUP 4 - 11.5.9.4.1 11i 3409889 01-NOV-04 04:10:10 POST OWF.G ROLLUP 5 Consolidated Fixes For OWF.G RUP 5 11i 3492743 01-NOV-04 04:09:37 POST OWF.G ROLLUP 6 - 11.5.9.6 11i 4440000 13-JAN-07 12:34:21 Oracle Applications Release 12 Maintenance Pack R12 4440000 24-JAN-08 05:50:13 Oracle Applications Release 12 Maintenance Pack R12 5907545 23-OCT-07 13:43:59 R12.ATG_PF.A.DELTA.1 R12 5907545 23-JAN-08 21:58:30 R12.ATG_PF.A.DELTA.1 R12 5917344 23-OCT-07 13:43:58 R12.ATG_PF.A.DELTA.2 R12 5917344 23-JAN-08 21:58:31 R12.ATG_PF.A.DELTA.2 R12 6077669 23-OCT-07 13:43:58 R12.ATG_PF.A.DELTA.3 R12 6077669 23-JAN-08 21:58:30 R12.ATG_PF.A.DELTA.3 R12 6141000 23-OCT-07 13:42:12 12.0.3 Release Update Pack (RUP3) R12 6272680 23-JAN-08 21:58:31 R12.ATG_PF.A.DELTA.4 R12 6435000 23-JAN-08 21:58:32 12.0.4 RELEASE UPDATE PACK (RUP4) R12 7303030 03-APR-09 16:28:06 12.1.1 Maintenance Pack R12 7303033 30-MAR-12 19:49:55 Oracle E-Business Suite 12.1.2 Release Update Pack (RUP2) R12 7651091 30-MAR-12 19:50:02 Oracle Applications Technology Release Update Pack 2 for 12.1 (R12.ATG_PF.B.DELTA.2) R12 8919491 30-MAR-12 19:49:56 Oracle Applications Technology 12.1.3 Product Family Release Update Pack R12 9239089 30-MAR-12 11:10:16 Oracle Applications DBA 12.1.3 Product Release Update Pack R12 9239090 30-MAR-12 19:47:48 ORACLE E-BUSINESS SUITE 12.1.3 RELEASE UPDATE PACK R12 29 rows selected.
set lines 1000 pages 100 col patch_name for a15 col patch_type for a20 col maint_pack_level for a10 select applied_patch_id , patch_name , patch_type , maint_pack_level , source_code , creation_date from apps.ad_applied_patches order by creation_date /
APPLIED_PATCH_ID PATCH_NAME PATCH_TYPE MAINT_PACK SOU CREATION_DATE
---------------- --------------- -------------------- ---------- --- ------------------------
19401 10096077 ONE-OFF DRV 04-APR-12 13:50:45
19400 10096077 ONE-OFF DRV 04-APR-12 13:50:03
18394 10163753 ONE-OFF DRV 02-APR-12 10:55:29
18395 10163753 ONE-OFF DRV 02-APR-12 11:03:01
19391 10229047 ONE-OFF DRV 04-APR-12 13:38:25
19390 10229047 ONE-OFF DRV 04-APR-12 13:34:13
20390 10318122 ONE-OFF DRV 12-JUL-12 18:15:38
20391 10318122 ONE-OFF DRV 12-JUL-12 18:16:05
20397 10328890 ONE-OFF DRV 30-JUL-12 21:58:00
20398 10328890 ONE-OFF DRV 30-JUL-12 21:58:14
19419 10358055 ONE-OFF DRV 04-APR-12 14:54:58
19418 10358055 ONE-OFF DRV 04-APR-12 14:54:13
19422 10380225 ONE-OFF DRV 04-APR-12 15:38:08
19423 10380225 ONE-OFF DRV 04-APR-12 15:38:44
22406 10408339 ONE-OFF DRV 21-SEP-14 03:50:53
22405 10408339 ONE-OFF DRV 21-SEP-14 03:49:45
18396 11071569 ONE-OFF DRV 02-APR-12 11:06:47
20393 11768127 ONE-OFF DRV 30-JUL-12 21:50:22
20392 11768127 ONE-OFF DRV 30-JUL-12 21:39:43
19420 11792958 ONE-OFF DRV 04-APR-12 15:29:46
19421 11792958 ONE-OFF DRV 04-APR-12 15:30:11
...
15391 9239089 PATCH-SET DRV 30-MAR-12 11:10:14
16390 9239090 MAINTENANCE-PACK 12.1.3 DRV 30-MAR-12 13:21:38
17390 9239090 MAINTENANCE-PACK 12.1.3 DRV 30-MAR-12 19:47:38
17391 9239095 ONE-OFF DRV 30-MAR-12 21:17:10
17392 9239095 ONE-OFF DRV 30-MAR-12 21:34:34
19396 9524997 ONE-OFF DRV 04-APR-12 13:46:03
19397 9524997 ONE-OFF DRV 04-APR-12 13:46:32
19409 9590326 ONE-OFF DRV 04-APR-12 14:10:46
19410 9590326 ONE-OFF DRV 04-APR-12 14:11:32
19394 9679595 ONE-OFF DRV 04-APR-12 13:44:15
19395 9679595 ONE-OFF DRV 04-APR-12 13:44:40
19392 9701260 ONE-OFF DRV 04-APR-12 13:41:44
19393 9701260 ONE-OFF DRV 04-APR-12 13:42:46
18397 9738085 ONE-OFF DRV 02-APR-12 11:09:49
19411 9742747 ONE-OFF DRV 04-APR-12 14:13:39
19412 9742747 ONE-OFF DRV 04-APR-12 14:14:29
17393 9817770 ONE-OFF DRV 30-MAR-12 22:12:51
18398 9852070 ONE-OFF DRV 02-APR-12 11:12:45
18392 9868229 ONE-OFF DRV 02-APR-12 10:45:39
18393 9868229 ONE-OFF DRV 02-APR-12 10:53:14
19398 9926320 ONE-OFF DRV 04-APR-12 13:47:41
19399 9926320 ONE-OFF DRV 04-APR-12 13:48:25
20394 9930308 ONE-OFF DRV 30-JUL-12 21:52:53
20395 9930308 ONE-OFF DRV 30-JUL-12 21:53:02
17394 9966055 ONE-OFF DRV 31-MAR-12 11:09:06
24405 preRH_dest1 ONE-OFF DRV 05-MAY-16 10:32:02
24406 preRH_dest1 ONE-OFF DRV 05-MAY-16 10:34:34
125 rows selected.
http server error 500 or another issue? Perform some connection tests with this
http://host.domain:port/OA_HTML/jsp/fnd/aoljtest.jsp
CONTEXT_NAME is ${ORACLE_SID}_$(hostname|awk -F'.' {print $1}')
$INST_TOP/appl/admin/${CONTEXT_NAME}.xml
$ORACLE_HOME/appsutil/${CONTEXT_NAME}.xml
On Apps tier is found in $INST_TOP/admin/scripts (= $ADMIN_SCRIPTS_HOME)
This file is a wrapper script that passes the name of the context file to adconfig.sh, which in turn calls $AD_TOP/bin/adconfig.pl (which calls java) to carry out the actual configuration tasks.
On the Database Tier, it is found in $ORACLE_HOME/appsutil/scripts/${CONTEXT_NAME}/
Backups of the files created by adautocfg.sh can be found in:
Apps Tier: $INST_TOP/admin/out/MMDDhhmm\\ Database Tier: $ORACLE_HOME/appsutil/out/$CONTEXT_NAME/MMDDhhmm
There are many different ways of cloning an E-Business suite database. This is a long-winded version. Trying to trim it down…
This is Oracle's way of doing it
This is the Claremont way of doing it
This is the Perficient way of doing it
All Concurrent Manger log files (including ICM) are located in the $APPLCSF/$APPLLOG
cd $APPLCSF/$APPLLOG ls -altr *$TWO_TASK*
Starting up with adstrtal.sh seems to end successfully but all the FND processes soon all crash.
Essentially, on the application server setup the environment, then
Stop what's left of the apps processes
cd $ADMIN_SCRIPTS_HOME ./adstpall.sh apps/apps
on the database server setup the environment, then
Run autoconfig appspass=apps
cd $ORACLE_HOME/appsutil/scripts/$CONTEXT_NAME ./adautocfg.sh
then back on the application server
Run autoconfig and restart apps
cd $ADMIN_SCRIPTS_HOME ./adautocfg.sh ./adstrtal.sh apps/apps
As a final check, have a look at the contents of FND_NODES
set pages 1000
set linesize 135
col PROPERTY_NAME for a25
col PROPERTY_VALUE for a15
col DESCRIPTION for a35
col DIRECTORY_PATH for a70
col directory_name for a25
col OWNER for a10
col DB_LINK for a40
col HOST for a20
col "User_Concurrent_Queue_Name" format a50 heading 'Manager'
col "Running_Processes" for 9999 heading 'Running'
set head off
set feedback off
set echo off
break on utl_file_dir
select '--------------------------------------------------------------------------------' from dual;
select '----------------------- Database Checks ---------------------------------' from dual;
select '--------------------------------------------------------------------------------' from dual;
Prompt
select '************************ Getting Database Information *************' from dual ;
select 'Database Name..................... : '||name from v$database;
select 'Database Status................... : '||open_mode from v$database;
select 'Archiving Status.................. : '||log_mode from v$database;
select 'Global Name....................... : '||global_name from global_name;
select 'Creation Date..................... : '||to_char(created,'DD-MON-YYYY HH24:MI:SS') from v$database;
select 'Checking For Missing File......... : '||count(*) from v$recover_file;
select 'Checking Missing File Name ....... : '||count(*) from v$datafile where name like '%MISS%';
select 'Total SGA ........................ : '||round(sum(value)/(1024*1024))||' MB' from v$sga ;
select 'Database Version.................. : '||version from v$instance;
select 'Temporary Tablespace.............. : '||property_value from database_properties
where property_name like 'default_temp_tablespace';
select 'Apps Temp Tablespace.............. : '||temporary_tablespace from dba_users where username like '%APPS%';
select 'Temp Tablespace size.............. : '||sum(maxbytes/1024/1024/1024)||' GB' from dba_temp_files group by tablespace_name;
select 'No of Invalid Object ............. : '||count(*) from dba_objects where status = 'INVALID' ;
select 'service Name...................... : '||value from v$parameter2 where name='service_names';
select 'plsql code type................... : '||value from v$parameter2 where name='plsql_code_type';
select 'plsql subdir count................ : '||value from v$parameter2 where name='plsql_native_library_subdir_count';
select 'plsql native library dir.......... : '||value from v$parameter2 where name='plsql_native_library_dir';
select 'Shared Pool Size.........,........ : '||(value/1024/1024) ||' MB' from v$parameter where name='shared_pool_size';
select 'Log Buffer........................ : '||(value/1024/1024) ||' MB' from v$parameter where name='log_buffer';
select 'Buffer Cache...................... : '||(value/1024/1024) ||' MB' from v$parameter where name='db_cache_size';
select 'Large Pool Size................... : '||(value/1024/1024) ||' MB' from v$parameter where name='large_pool_size';
select 'Java Pool Size.................... : '||(value/1024/1024) ||' MB' from v$parameter where name='java_pool_size';
select 'utl_file_dir...................... : '||value from v$parameter2 where name='utl_file_dir';
select directory_name||'.................... : '||directory_path from all_directories where rownum < 15 ;
select '************************ Getting Apps Information *****************' from dual ;
select 'Home URL.......................... : '||home_url from apps.icx_parameters ;
select 'Session Cookie.................... : '||session_cookie from apps.icx_parameters ;
select 'Applicaiton Database ID........... : '||fnd_profile.value('apps_database_id') from dual;
select 'GSM Enabled....................... : '||fnd_profile.value('conc_gsm_enabled') from dual;
select 'Maintainance Mode................. : '||fnd_profile.value('apps_maintenance_mode') from dual;
select 'Site Name......................... : '||fnd_profile.value('Sitename')from dual;
select 'Bug Number........................ : '||bug_number from ad_bugs where bug_number='2728236';
select '************************ Doing Workflow Checks ********************' from dual ;
select 'No Open Notifications............. : '||count(*) from wf_notifications where mail_status in('MAIL','INVALID','OPEN');
select 'Name(wf_systems).................. : '||name from wf_systems;
select 'Display Name(wf_systems).......... : '||display_name from wf_systems;
select 'Address........................... : '||address from wf_agents;
select 'Workflow Mailer Status............ : '||component_status from applsys.fnd_svc_components
where component_name like 'Workflow Notification Mailer';
select 'Test Address...................... : '||b.parameter_value
from fnd_svc_comp_param_vals_v a, fnd_svc_comp_param_vals b
where a.parameter_id=b.parameter_id
and a.parameter_name in ('TEST_ADDRESS');
select 'From Address...................... : '||b.parameter_value
from fnd_svc_comp_param_vals_v a, fnd_svc_comp_param_vals b
where a.parameter_id=b.parameter_id
and a.parameter_name in ('FROM');
select 'WF Admin Role..................... : '||text from wf_resources where name = 'WF_ADMIN_ROLE' and rownum =1;
Prompt
Prompt Getting Apps Node Info
Prompt ************************
select Node_Name,'........................ : '||server_id from fnd_nodes;
select server_type||'......................: '||name from fnd_app_servers, fnd_nodes
where fnd_app_servers.node_id =fnd_nodes.node_id;
select '************************ Doing Conc Mgr Checks ********************' from dual ;
Column Target_Node Format A12
select User_Concurrent_Queue_Name,'....... : '||Target_Node||' ...... : '||Running_Processes
from fnd_concurrent_queues_vl
where Running_Processes = Max_Processes
and Running_Processes > 0;
Prompt
Prompt Getting Pending Request
Prompt ***********************
--select user_concurrent_program_name||'........ : '||request_id
-- from fnd_concurrent_requests r, fnd_concurrent_programs_vl p, fnd_lookups s, fnd_lookups ph
-- where r.concurrent_program_id = p.concurrent_program_id
-- and r.phase_code = ph.lookup_code
-- and ph.lookup_type = 'CP_PHASE_CODE'
-- and r.status_code = s.lookup_code
-- and s.lookup_type = 'CP_STATUS_CODE'
-- and ph.meaning ='Pending'
-- and rownum < 10
-- order by to_date(actual_start_date, 'dd-MON-yy hh24:mi');
--
Prompt
Prompt Getting Workflow Components Status
Prompt **********************************
set pagesize 1000
select fsc.COMPONENT_NAME,
fsc.STARTUP_MODE,
fsc.COMPONENT_STATUS,
fcq.MAX_PROCESSES TARGET,
fcq.RUNNING_PROCESSES ACTUAL
from APPS.FND_CONCURRENT_QUEUES_VL fcq, APPS.FND_CP_SERVICES fcs,
APPS.FND_CONCURRENT_PROCESSES fcp, fnd_svc_components fsc
where fcq.MANAGER_TYPE = fcs.SERVICE_ID
and fcs.SERVICE_HANDLE = 'FNDCPGSC'
and fsc.concurrent_queue_id = fcq.concurrent_queue_id(+)
and fcq.concurrent_queue_id = fcp.concurrent_queue_id(+)
and fcq.application_id = fcp.queue_application_id(+)
and fcp.process_status_code(+) = 'A'
order by fcp.OS_PROCESS_ID, fsc.STARTUP_MODE;
select '--------------------------------------------------------------------------------' from dual;
select '----------------------- End Of Database Checks ----------------------------' from dual;
select '--------------------------------------------------------------------------------' from dual;