How to Put Concurrent Requests in Hold Status?

During some scheduled maintenance activities, oracle apps database administrator would require to keep the Pending Jobs on Hold before bounce and release them after the scheduled activity.
This process help to bring down Concurrent Manager quickly and the pending jobs are preserved for running after the maintenance is complete.

1) Create table apps.conc_req_on_hold as select * from fnd_Concurrent_requests where PHASE_CODE=’P’ and hold_flag=’N’;
2) select count(*) from apps.conc_req_on_hold
3) update fnd_Concurrent_requests set hold_flag=’Y’ where PHASE_CODE=’P’ and hold_flag=’N’ and request_id in (select request_id from apps.conc_req_on_hold);

NOTE: You have to commit if select & update are same number of records. Otherwise rollback and try again till the numbers are same

4) Commit;

To Release hold on Concurrent Requests patching, run the below sql :

5) update fnd_Concurrent_requests set hold_flag=’N’ where request_id in (select request_id from apps.conc_req_on_hold);

6)Commit the changes

 commit;


ONE NODE RAC Features in Oracle Database 11g Edition

•Available
from 11gr2, Express Edition.
•High
availability feature.
•Provides
Cold failover Solution.
•Automates
instance relocation incase of instance failure or fault in the first node.
•Facilitates
rolling upgrade for single instance database.
•Live
migration of instances across servers.

•Uses omotion
utility to migrate the instance.

Query to Find Free Space in a Tablespace

undefine tbsp
set lines 152
set echo off

col tablespace_name for a20
select utbs.tablespace_name,
round(utbs.mb) “Allocated Used/Unused MB”,
round(Ftbs.mb) “Allocated_Free MB”,
round((100/utbs.mb)*Ftbs.mb) “%Allocated_Free MB”,
decode(sign(round(utbs.Maxmb-utbs.mb)),-1,0,round(utbs.Maxmb-utbs.mb)) “Space_AutoExtensible MB”,
Ftbs.MaxBytes “MaxChunk MB”
from
(select ddf.tablespace_name,sum(ddf.bytes)/1048576 MB,sum(ddf.maxbytes)/1048576 MaxMB
from dba_data_files ddf
group by ddf.tablespace_name) Utbs,
(select dfs.tablespace_name,sum(dfs.bytes)/1048576 MB,max(dfs.bytes)/1048576 MaxBytes
from dba_free_space dfs
group by dfs.tablespace_name) Ftbs
where utbs.tablespace_name=ftbs.tablespace_name
and utbs.tablespace_name like ‘%&&TBSP%’
order by round(Ftbs.mb)
/

Useful Queries for Oracle Applications DBA’S





Script to check if datainstall / hrglobal was effective in completing its actions.

select
  substr(APPLICATION_SHORT_NAME,1,11) APPLICATION,
  substr(LEGISLATION_CODE,1,3) LEG,
  decode(nvl(STATUS,’NULL’)
  ,’I’,’Installed’
               ,’ ‘,’Not Installed’) STATUS,
  decode(action,’F’,’Force Install’
                ,’C’,’Clear’
                ,’U’,’Upgrade’
                ,’I’,’Install’) ACTION,
  last_update_date
from hr_legislation_installations
where (status is not null or action is not null) order by ACTION, STATUS, legislation_code;



Sample Output

APPLICATI LEG    STATUS        ACTION        LAST_UPDATE_DAT
——— —— ————- ————- —————
PAY       AE     Installed                   18-OCT-15
PER       AE     Installed                   18-OCT-15
PAY       AU     Installed                   18-OCT-15
PER       AU     Installed                   18-OCT-15
PER       CA     Installed                   18-OCT-15
PAY       CA     Installed                   18-OCT-15
PER       CN     Installed                   18-OCT-15
PAY       CN     Installed                   18-OCT-15
PER       ES     Installed                   18-OCT-15
CM        GB     Installed                   18-OCT-15
PAY       GB     Installed                   18-OCT-15
PER       GB     Installed                   18-OCT-15
PER       IE     Installed                   18-OCT-15
PAY       IE     Installed                   18-OCT-15
PER       NL     Installed                   18-OCT-15
PAY       SA     Installed                   18-OCT-15
PER       SA     Installed                   18-OCT-15
PER       SG     Installed                   18-OCT-15
PAY       SG     Installed                   18-OCT-15
GHR       US     Installed                   18-OCT-15
CM        US     Installed                   18-OCT-15
PAY       US     Installed                   18-OCT-15
PER       US     Installed                   18-OCT-15
PER              Installed                   18-OCT-15

24 rows selected.

Script to Check size of fnd_lobs table






SELECT sum( bytes)/1024/1024 size_in_MB
FROM user_segments
WHERE (segment_name = ‘FND_LOBS’
OR segment_name in (
SELECT segment_name
FROM user_lobs
WHERE table_name = ‘FNS_LOBS’
UNION
SELECT index_name
FROM user_lobs
WHERE table_name = ‘FND_LOBS’

) );

Sample Output:

   SIZE_GB SEGMENT_NAME                                                                      SEGMENT_TYPE
———- ——————————————————————————— ——————
.873779297 SYS_LOB0000034032C00004$$                                                         LOBSEGMENT

Autoconfig Error during SETUP Phase (Oracle Applications DBA 11i)

Autoconfig failed during setup phase:

While running the script adgendbc.sh

Unable to update GUEST_USER_PWD in database to GUEST/ORACLE1 –
java.sql.SQLException: ORA-06550: line 1, column 323:
PLS-00201: identifier ‘FND_VAULT.PUT’ must be declared
ORA-06550: line 1, column 323:
PL/SQL: Statement ignored

Cause

AdminAppServer.java 115.32 calls fnd_app_server_pkg.update_server passing 5 parameters.
The versions of FND_APP_SERVER_PKG (AFSCASRS.pls/AFSCASRB.pls) accepts 5 parameters.

These version used in the database is older than the version at OS filesystem level.

Solution

1. Run following SQL query :

select text
from all_source
where name =’FND_APP_SERVER_PKG’ and line < 7;

2. If the versions in the database are older than the ones at the OS level, go to the

$FND_TOP/patch/115/sql directory and recreate the spec/body by running :

– sqlplus apps/<passwd> @AFSCASRS.pls

– sqlplus apps/<passwd> @AFSCASRB.pls

3. Rerun Autoconfig.

Concurrent Requests Failing with APP-FND-00362

Issue:

All the custom concurrent requests failing to run.
Logfile shows the following error:

APP-FND-00362: Routine afpbep cannot execute request &REQUEST for program &PROGRAM,
because the environment variable &BASEPATH is not set for the application to which
the concurrent program executable &EXECUTABLE belongs.

Cause: BASEPATH dir not set as environment variable.

Solution:

Find the application for which concurrent program is defined..

Go to Application Developer–> Concurrent Program –> Define
and query for the concurrent request(which is failing)

(For e.g. If you get the Application : Business Online)

Now go to Application Developer–>Register Application

Query for the Application to get BASEPATH.

(e.g XX_TOP is the basedirectory for Custom Application)

At the application tier check if XX_TOP is not set..
$echo $XX_TOP

Add the env variable XX_TOP=/../../..
 to applTop env or custom env and restart concurrent manager

Resubmit the request which have failed.

Oracle Applications Database Administrator Interview Questions





1. How to verify if a patch is applied or not (11i and R12)


A: Query from ad_bugs table.(11i and r12)
select bug_number,created.last_update_date from ad_bugs where bug_number=’&patchnum’;


R12.2


select ad_patch.is_patch_applied(‘R12’,-1,20034256) from dual;


expected results:
EXPLICIT = applied
NOT APPLIED = not applied / aborted


2.What is the difference between ad_bugs and ad_applied_patches?


A: If a patch is applying multiple bug fixes, the details of all bugs fixed by the patch can be found from ad_bugs,
ad_applied_patches has information only about patches applied using adpatch.




Two tables to check if the patch is applied or not:


This table includes the defined bugs on the system: 
SELECT   bug_number 
FROM     apps.ad_bugs
WHERE   bug_number LIKE ‘%’&patchnum’%’;


This table includes patches applied on the system:
SELECT patch_name 
FROM   apps.ad_applied_patches
WHERE patch_name LIKE ‘%’&patchnum’%’


3. How to check if a forms patch is applied?


A: There is no specific way to check if a forms patch is applied. Forms Patches are usually applied through Shell scripts.