27 Dec 2015

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

Take Backup of a Package and Package Body

Login to sqlplus as the user who owns the package/package body.

SQL>spool package_name.bkp

SQL>select text from user_source where name='&PACKAGE NAME' and type='PACKAGE';

SQL>select text from user_source where name='&PACKAGE NAME' and type='PACKAGE BODY';

SQL>Spool off;

26 Dec 2015

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.

24 Dec 2015

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.

19 Dec 2015

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.