Sunday, March 12, 2017

Clonning Oracle EBS Applications and Database

Hi DBAs,

Clonning is one of the regular tasks done by Apps DBA & DBAs. I have done lot of clonning
in various ways,below is one such task.Hope you will like it. Theses steps are applicable for
Oracle EBS Release 12.1.3 and Database version is

Step 1: Source Environment Preparation Steps:
Run on DB and Application Tier
DB Tier:
cd $ORACLE_HOME/appsutil/scripts/$CONTEXT_NAME
perl dbtier
Application Tier:
perl appsTier

Step 2: Take Backup of Database and Applications:
Use the below script to take full rman backup taken can be used for cloning on source Environment.
configure device type disk parallelism 6;
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
allocate channel c4 device type disk;
allocate channel c5 device type disk;
allocate channel c6 device type disk;
backup full database format '/backup/rman/EBSTEST/juneDD/EBSTEST_Full%d_%s_%T_%U' database;
backup format '/backup/rman/EBSTEST/juneNN/EBSTEST_ARCH%d_%s_%T_%U' archivelog all;
backup current controlfile format '/backup/rman/EBSTEST/juneNN/control%d%t_control.ctl';
release channel c1;
release channel c2;
release channel c3;
release channel c4;
release channel c5;
release channel c6;

Note: Replace DD with the date or any other directory name as required.

Application Tier:
Compress the top directories using the below commands:
tar –cf /backup/PROD/appl/appsst<date>.tar /erpapp/apps/apps_st
tar –cf /backup/PROD/appl/techst<date>.tar /erpapp/apps/tech_st

Step 3:Copy and Extract Files to DEV Node:

Copy the appsst.tar and techst.tar to respective locations in DEV and extract using:
tar –xf appsst.tar
tar –xf techst.tar

Step 4:Preparing DEV for Refresh:

a) Shutdown Applications and Database Services
b) Take DEV backup if required
c) Take a backup of pfile
d) sqlplus ‘/as sysdba’
startup mount
alter system enable restricted session;
drop database;
e) cp initTESTEBS.ora initEBSTEST.ora
vi initEBSTEST.ora
control_files='+DATA/TESTEBS/CONTROLFILE/ctr<new1>.dbf','+DATA/TESTEBS/CONTROLFILE/ctr<new2>.dbf ','+DATA/TESTEBS/CONTROLFILE/ctr<new3>.dbf '
startup nomount

Step 5: Restore and Recover Database
Restore controlfile from backup of source Database which should be copied using scp to the
target DB server.

rman target /
restore controlfile from ‘<PATH OF CONTROLFILE BACKUP>’
alter database mount;
crosscheck backup;
delete expired backup;
crosscheck backup;
list backup;
Note:Find the latest sequence and thread number and use them in place of “SEQUENCE” and “N” in below script.

set until sequence <SEQUENCE> thread <N>;
configure device type disk parallelism 6;
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
allocate channel c4 device type disk;
allocate channel c5 device type disk;
allocate channel c6 device type disk;
set newname for database to '+DATA';
restore database;
switch datafile all;
recover database;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
release channel c5;
release channel c6;

This completes the database restore and recovery.

Step 6: Configuring DEV Database

Now the Database is in Mount State with EBSTEST name.
Change the redolog file names of Thread 1 to +RECO
a. alter database rename file ‘+RECO/EBSTEST…’ to ‘NEW PATH’
alter database backup controlfile to trace
shut immediate
b. Rename trace file to ctrl.sql
vi ctrl.sql
Change “REUSE” to “SET”
Change “EBSTEST” to “EBSDEV”
Change “noresetlogs” to “resetlogs”
Change “archivelog” to “noarchivelog”
c. cp initEBSTEST.ora initEBSDEV.ora
Change db_name=AMDEV in initEBSDEV.ora
e. sqlplus ‘/as sysdba’
alter database open resetlogs;
f. Run Autoconfig

This completes the Database Cloning

Before starting cloning, modify FND Template as below:
cd $FND_TOP/admin/driver/fndtmpl.drv INSTE8_SETUP to INSTALL_SETUP

Step 7:Clone Application Tier:
cd /erpapp/apps/apps_st/comn/clone/bin
perl appsTier
Answer the prompts and do not start services once apply is complete

Step 8:Take a backup of context file 
Change the load balancing related parameters

Step 9:Start the EBS applications
5.11 apps/<password>

Post Cloning Steps:

a) Change logo
Upload the below logo to $OA_MEDIA/COMPLOGO_SC.jpg

b) Run the Data masking script:

 c) Cancel the Running/Pending Concurrent Requests
UPDATE fnd_concurrent_requests
SET phase_code = 'C', status_code = 'X'
WHERE status_code IN ('Q','I')
AND requested_start_date > SYSDATE
AND hold_flag = 'N';

UPDATE fnd_concurrent_requests
SET phase_code = 'C', status_code = 'X'
WHERE phase_code IN ('R','P');

d) Disable Workflow Notification Mailer. Set the override address from OAM. Purge pending notifications.

where status in ('OPEN', 'CANCELED')
and mail_status in ('MAIL', 'INVALID');

sqlplus apps/appspwd@db @$FND_TOP/patch/115/sql/wfntfqup.sql apps appspwd applsys

update wf_roles set NOTIFICATION_PREFERENCE=’DISABLED’,email_address=null;

e) Update per_all_people_f set email_address=null where email_address is not null;

f) Change the profile options:
Site Name to “DEV Instance”
Java Color Scheme to a color which is not same as PROD

e) Change the system, apps, sysadmin, xxcomp passwords
f) Add tempfile to Temp Tablespace
g) Change the URLs previously in Context File configured for Load Balancing in PROD and run autoconfig.

s_webentryhost testebs

h) Update the Node information for all Concurrent Managers from Concurrent Manager Define to current node name and restart concurrent managers using
i) DEACTIVATE “Periodic Alert Scheduler”
j) Update Discoverer URL in Context File ( Application Tier - s_disco_url):
k) shall be used as the Email ID for notifications in case if WF Notification Mailer is being enabled

Hope you followed clonning steps. Enjoy Apps DBA tasks.



Wednesday, July 6, 2016

Changing Weblogic user password in Oracle EBS Applications 12.2.5

Hi All,

We can change the 'weblogic' user password in EBS Release 12.2.5 using perl script. Before changing the 'weblogic'  user password we need to shutdown all the Middle Tier applications using '' script.

Below are the steps involved.

Step 1:Source the run filesystem  in the Oracle EBS Applications 12.2 or higher.

. EBSapps.env run

Step 2:Change the password using  perl script.

applmgr@host1:/erpapp/testebs/upgrade$ perl /erpapp/testebs/upgrade/fs1/EBSapps/appl/fnd/12.0.0/patch/115/bin/ -action=updateAdminPassword

Program: started at Wed Mar 23 13:51:40 2016

AdminServer will be re started after changing WebLogic Admin Password
All Mid Tier services should be SHUTDOWN before changing WebLogic Admin Password
Confirm if all Mid Tier services are in SHUTDOWN state. Enter "Yes" to proceed or anything else to exit: yes

Enter the full path of Applications Context File [DEFAULT - /erpapp/testebs/upgrade/fs1/inst/apps/testebs_host1/appl/admin/testebs_host1.xml]:
Enter the WLS Admin Password:
Enter the new WLS Admin Password:
Enter the APPS user password:

Executing: /erpapp/testebs/upgrade/fs1/FMW_Home/webtier/perl/bin/perl /erpapp/testebs/upgrade/fs1/EBSapps/appl/ad/12.0.0/patch/115/bin/  ebs-get-serverstatus -contextfile=/erpapp/testebs/upgrade/fs1/inst/apps/testebs_host1/appl/admin/testebs_host1.xml -servername=AdminServer -promptmsg=hide -logfile=/erpapp/testebs/upgrade/fs1/inst/apps/testebs_host1/logs/appl/rgf/TXK/txkUpdateEBSDomain_Wed_Mar_23_13_51_40_2016/EBSProvisioner.log
ERROR : Admin Server is not RUNNING, cannot proceed further.
Exiting..applmgr@host1:/erpapp/testebs/upgrade$ cd $ADMIN_SCRIPTS_HOME
applmgr@host1:/erpapp/testebs/upgrade/fs1/inst/apps/testebs_host1/admin/scripts$ ./ start

You are running version 120.10.12020000.10

Enter the WebLogic Admin password:
Enter the APPS Schema password:
Starting WLS Admin Server...

Domain updated successfully
Restarting AdminServer with new Admin Password.

You are running version 120.10.12020000.10

Stopping WLS Admin Server...
Refer /erpapp/testebs/upgrade/fs1/inst/apps/testebs_host1/logs/appl/admin/log/adadminsrvctl.txt for details

AdminServer logs are located at /erpapp/testebs/upgrade/fs1/FMW_Home/user_projects/domains/EBS_domain_testebs/servers/AdminServer/logs exiting with status 0 check the logfile /erpapp/testebs/upgrade/fs1/inst/apps/testebs_host1/logs/appl/admin/log/adadminsrvctl.txt for more information ...

You are running version 120.11.12020000.12

NodeManager log is located at /erpapp/testebs/upgrade/fs1/FMW_Home/wlserver_10.3/common/nodemanager/nmHome1 exiting with status 0 check the logfile /erpapp/testebs/upgrade/fs1/inst/apps/testebs_host1/logs/appl/admin/log/adnodemgrctl.txt for more information ...

You are running version 120.10.12020000.10

Starting WLS Admin Server...
Refer /erpapp/testebs/upgrade/fs1/inst/apps/testebs_host1/logs/appl/admin/log/adadminsrvctl.txt for details

AdminServer logs are located at /erpapp/testebs/upgrade/fs1/FMW_Home/user_projects/domains/EBS_domain_testebs/servers/AdminServer/logs exiting with status 0 check the logfile /erpapp/testebs/upgrade/fs1/inst/apps/testebs_host1/logs/appl/admin/log/adadminsrvctl.txt for more information ...

*************** IMPORTANT ****************
WebLogic Admin Password is changed.
Restart all application tier services using control scripts.

Step 3: Start the EBS applications using '' 

Step 4:Login to admin console for Oracle EBS environment and verify the 'weblogic' user password.

Verify by login to admin console using the 'weblogic' user and make sure all the managed servers are up & running.

Happy Apps DBA Tasks,....



Tuesday, January 12, 2016

Exciting opportunity for oracle dba + siebel admin for UAE based company

Hi All,

Please email your resume @ for  exciting opportunity for oracle dba + sieble admin in UAE for reputed company. Looking for 5 yrs + experience.

Note: Siebel administration is mandatory.



Saturday, August 29, 2015

Difference in location of Log files in Oracle EBS Release 12.1.3 and Oracle EBS Release 12.2.4

Hi All,

Lot of companies planning to Upgrade from Oracle EBS Release 12.1.3(Rel 12.1.x)  to Oracle EBS Release 12.2.4(Rel 12.2.x). Few of the companies already upgraded.

The Log files locations in Oracle EBS Release 12.1.3 and  Oracle EBS R 12.2.4  are given below:

1.Instance startup and configuration Log files are located for INST_TOP in Oracle Release 12.1.3 are below:

Startup/Shutdown error message related to tech stack (10.1.2, 10.1.3 forms/reports/web)
$INST_TOP/logs/ora/ (10.1.2 & 10.1.3)
$INST_TOP/logs/ora/10.1.3/Apache/error_log[timestamp](Apache log files)
$INST_TOP/logs/ora/10.1.3/opmn/ (OC4J, oa*, opmn.log)
$INST_TOP/logs/ora/10.1.2/network/ (listener log)
$INST_TOP/apps/$CONTEXT_NAME/logs/appl/conc/log (CM log files)

2. Log files related to cloning in R12.1.3 are as below:

 Preclone log files in source instance
Database Tier – $ORACLE_HOME/appsutil/log/$CONTEXT_NAME/(StageDBTier_MMDDHHMM.log)
Application Tier –

Clone log files in target instance
Database Tier – $ORACLE_HOME/appsutil/log/$CONTEXT_NAME/ApplyDBTier_.log
Apps Tier – $INST_TOP/admin/log/ApplyAppsTier_.log

3. Patching related log files in R12.1.3 are as below:

i) Application Tier adpatch log – $APPL_TOP/admin/$SID/log/
ii) Developer (Developer/Forms & Reports 10.1.2) Patch – $ORACLE_HOME/.patch_storage
iii) Web Server (Apache) patch – $IAS_ORACLE_HOME/.patch_storage
iv) Database Tier opatch log – $ORACLE_HOME/.patch_storage

4. Autoconfig related log files in R12.1.3 are as below:

a) Database Tier Autoconfig log :

b) Application Tier Autoconfig log : 

5.Autoconfig context file location in R12.1.3 :

6)R12.1.3 Installation Logs in R12.1.3 are as below:

 Database Tier Installation
RDBMS_ORACLE_HOME/appsutil/log/$CONTEXT_NAME/adcrdb_.log RDBMS_ORACLE_HOME/appsutil/log/$CONTEXT_NAME/ApplyDatabase_.log
Application Tier Installation
Inventory Registration:
$Global Inventory/logs/cloneActions.log
$Global Inventory/logs/oraInstall.log
$Global Inventory/logs/silentInstall.log

7) Log files related with relink,Network,OUT inventory logs for R12.1.3 are as below:
 1) Database Tier
1.1) Relink Log files :
1.2) Alert Log Files :
1.3) Network Logs :
1.4) OUI Logs :
OUI Inventory Logs :
2) Application Tier
Tech Stack Patch 10.1.3 (Web/HTTP Server)

In EBS R12.2.4 the log files locations are as below:

1)Log files file Online patching (adop) in EBS R12.2.4 are in below location:

The adop log files are located on the non-editioned file system (fs_ne), under:


This log directory will contain patch logs,patch worker logs.

adop(phase=fs_clone) Online pathcing filesystem cloning process related log files are found under:


2)Log files for Autoconfig process in Oracle EBS R12.2.4 are below:

On Applicaion Tier: $INST_TOP/admin/log/<MMDDhhmm>
On Database Tier: $ORACLE_HOME/appsutil/log/<CONTEXT_NAME>/<MMDDhhmm>

3)Log files for start/stop of services from $ADMIN_SCRIPTS_HOME

In below directory we will find log files related to start/stop process of oacore, forms, apache, opmn,
weblogic admin server/node manager:


4)Log/Out files for Concurrent programs/managers in Oracle R12.2.4 are in below location:

Log/Out files for Oracle Release 12.2 are stored in Non-Editioned filesystem(NE).

Log files: $APPLCSF/$APPLLOG (or $NE_BASE/inst/<CONTEXT_NAME>/logs/appl/conc/log)
Out files: $APPLCSF/$APPLOUT (or $NE_BASE/inst/<CONTEXT_NAME>/logs/appl/conc/out)

5)Log files for OPMN and OHS processes in Oracle R12.2.4 are in below location:

Below directory contains log files related OPMN process(opmn.log),
OPMN Debug logs(debug.log), HTTP Transaction logs (access.log),security settings related logs.


6)Log file for Weblogic Node Manager in Oracle R12.2.4 are in below location:

Log file is generated by Node Manager and contains data for all domains that
are controlled by Node Manager on a given physical machine.


7)Log file for Weblogic  in Oracle R12.2.4 for Oracle Management Service are below

Initial settings for AdminServer and Domain level information is written in this log file


8)Log files for server processes initiated through Weblogic in Oracle R12.2.4 are in below location:
Stdout and stderr messages generated by the forms, oafm and oacore services are located
at NOTICE severity level or higher are written by Weblogic Node Manager to below directory.


Enjoy Oracle EBS R12.2 learning and Performing tasks.



Saturday, May 30, 2015

Difference between Oracle EBS R12.1.3 and Oracle EBS R12.2.4


It's been a long time since I wrote a blog post, I was busy working with Oracle EBS upgrades,content server setup, and Oracle SOA 11g upgrade projects. We have recently upgraded Oracle EBS 12.1.3 to Oracle EBS R12.2.4.

There are lot of difference.Below are the key ones, I will try to extend this post based on difference noticed by me as I work more rigorously.

1)In R12.2 we have two kind of filesystems fs1(run filesystem) and fs2(patch filesystem) where as In R12.1.3 we only deal with one application filesystem.

2)In R12.2 we have the Application servers replaced by Weblogic server to manage the technology statck.
The 10.1.3 Home is replaced by FMW  (Fusion Middleware Home) i.e $FMW_HOME

The major change in R12.2 is involvement of Weblogic server to manager all the forms,oacore servers
where as in R12.1.3 we had the Application server 10.1.3 to manage the web home or Java Home

3)The  adpatch(patching) in R 12.1.3 is replaced by adop(online patching) in R 12.2.4

adop(online patching) utility involves 5 phases to apply a standard patch in Oracle EBS R12.2.
adop involves 5 phases
1)prepare => prepare phase involves synchronization of the filesystems fs1 (run ,filesystem) and fs2(patch filesystem), filesystems are inter changeable.
2)apply=>In this phase we apply all the patches
Note: These patches need to be copied in fs_ne (non editioned filesystems)
3)finalize =>In this phase we are getting ready for cutover phase
4)cutover =>In cutover phase the filesystem switchover takes place. Previously the filesystem which was patch filesystem will now become run filesystem.)
5) cleanup =>In cleanup phase all the obsolute objects gets compiled .

adop online patching utility doesn't require downtime. It involves minimal downtime during cutover phase where switching of filesystems happens. We can apply lot of patches and do cutover any time to minimize downtime

where as in  adpatch we just apply patch most of the times by bringing down applications or in hot patch mode.

4)'Apps' user Password change:
The 'apps' user password change involve 3 steps in R12.2.4
1)Change the password with FNDCPASS
2)Change the password in EBS Datasource from Weblogic admin console
3)Run AutoconfigCheck below link from Bala for detail steps:

Where as in R 12.1.3 It only involves 2 steps:
1)Change the password with FNDCPASS
2)Run Autoconfig
5)Log files locations:
All the oacore,forms log files are placed in $EBS_DOMAIN_HOME in R12.2. I will explain this in details in my upcoming posts.
Where as in R12.1.3 all the logs for oacore,forms are under $LOG_HOME/ora/10.1.3

I will come up with lot of difference in this thread so that we all gets comfortable with Oracle EBS Release 12.2.4
 Happy Oracle Apps DBA reading and learning EBS R12.2.4...

Note:Check for the services we offer.

Monday, September 1, 2014

Compressing the Oracle EBS TOPs and Uncompressing using tar and gzip

 Hi Apps DBA,

We usually do cloning by copying the Application tops from Source(TEST1) to Target(TEST2).
First we compress the files in source and than we copy those file from source to target and finally we uncompress followed by renaming and further process .Below are the useful commands and safe one to do this task.

Step 1:Tarring and zipping:

nohup tar -cvpf - ./test1appl 2> /u01/app/test1/test1appl.log | gzip -c -> /u01/app/test1/test1appl.gz &

nohup tar -cvpf - ./test1comn 2> /u01/app/test1/test1comn.log | gzip -c -> /u01/app/test1/test1comn.gz &

nohup tar -cvpf - ./test1ora 2> /u01/app/test1/test1ora.log | gzip -c -> /u01/app/test1/test1ora.gz &

Step 2:scp from source  to target:
Assuming we are on target server,we copy files from source to target as below:

scp -rp apptest1@uslaxorcap19:/u01/app/test1/*.tar.gz .


Step 3:Untarring and Unzipping:

nohup cat /u02/app/test2/test1appl.tar.gz|gunzip| tar xvf - >/u02/app/test2/test1appl.log &
nohup cat /u02/app/test2/test1comn.tar.gz|gunzip| tar xvf - >/u02/app/test2/test1comn.log &
nohup cat /u02/app/test2/test1ora.tar.gz|gunzip| tar xvf - >/u02/app/test2/test1ora_27Jun.log &

Note 1:Below command comes in single line in Unix shell prompt
Note 2:Advantage of above command is we can check logs and make sure the compressing and Uncompressing  is done successfully.

Enjoy doing Apps DBA tasks...

Best regards,


Note:Check for the services we offer.

Tuesday, August 19, 2014

Health check script for Oracle EBS Database and Application 11i/R12

Hi Apps DBA,

Lot of times we need to check  the Health of Oracle EBS Database and Application,The below script
can be very handy for it.

SQL>conn apps/***
SQL>spool health_check_apps_db.out

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;
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 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 ;

Prompt Getting Con Mgr Status
Prompt ************************
Prompt Manager Name                                                 Hostname          No of Proc Running
Prompt ~~~~~~~~~~~~                                                 ~~~~~~~~          ~~~~~~~~~~~~~~~~~~
set lines 145
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 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 Getting Workflow Components Status
Prompt **********************************

set pagesize 1000
set linesize 125
col COMPONENT_NAME for a45
col STARTUP_MODE for a12

select fsc.COMPONENT_NAME,
APPS.FND_CONCURRENT_PROCESSES fcp, fnd_svc_components fsc
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;

SQL>spool off

Enjoy Oracle Apps DBA tasks..



Note:Check for the services we offer.