Sunday 27 December 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;

Saturday 26 December 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.

Thursday 24 December 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.