Tuesday, July 31, 2018

adpatch: Drop FND_INSTALL_PROCESSES table [No] ?

When using adpatch to apply a patch, it reports an issue and I had to make the choice: 

Creating the AD_UTIL_PARAMS table...
Table AD_UTIL_PARAMS already exists, so dropping the table and recreating it.

Creating FND_INSTALL_PROCESSES table...
The table FND_INSTALL_PROCESSES created by AD Administration already exists.
If you are sure you do not want to keep the information from the failed
AD Administration session,you may drop FND_INSTALL_PROCESSES table and
continue with AutoPatch.

If you choose not to drop FND_INSTALL_PROCESSES table, AutoPatch
cannot continue successfully.

Drop FND_INSTALL_PROCESSES table [No] ?

The problem is an error from a previous session of adadmin. But the error was ignored.

After I answered Yes in above adpatch session, the session completed with the real error. 

AutoPatch error:
The following ORACLE error:

ORA-04045: errors during recompilation/revalidation of SYSTEM.LOGIN_AUDIT
ORA-01031: insufficient privileges
ORA-01434: private synonym to be dropped does not exist


occurred while executing the SQL statement:
DROP SYNONYM AD_DEFERRED_JOBS
AutoPatch error:
Error dropping FND_INSTALL_PROCESSES table


You should check the file
$APPL_TOP/admin/<SID>/log/xxxxx.log

for errors.

I checked back adadmin log and saw adadmin had the same error which is caused by custom trigger SYSTEM.LOGIN_AUDIT in the database. If the problem is not fixed, AD utilities (adadmin, adpatch, etc) will keep getting the error. After I dropped the trigger from database, the problem does not show up again.

The lesson is I should check adadmin log carefully even for a simple run.



Friday, July 13, 2018

SQL scripts for checking database tablespce

When a database tablespace is full, EBS will not work correctly. For example, I got below warnings from adpatch:

AutoPatch warning:
Error while Inserting into temp table (AD_PATCH_HIST_TEMP)

AutoPatch warning:
ORA-01653: unable to extend table APPLSYS.AD_PATCH_HIST_TEMP by 16 in tablespace APPS_TS_INTERFACE


Unable to Create Patch Objects
Error calling adphistStoreCurPatchHistInDB2


That came from last stage of applying a patch, and seems it is ignorable. But, it is good idea to check tablespace availability before applying apps patch.

Here are scripts check if free space (in 11g and 12c) is available:

SQL> SELECT ddf.tablespace_name,
       SUM( distinct ddf.ddfbytes )/1048576 avail_MB,
       SUM( NVL( ds.bytes , 0 ) / 1048576 ) used_MB
FROM
   ( SELECT tablespace_name, SUM( bytes ) ddfbytes
     FROM dba_data_files
     GROUP BY tablespace_name ) ddf,
   dba_segments ds
WHERE ddf.tablespace_name = ds.tablespace_name (+)
GROUP BY ddf.tablespace_name
;

SQL> select * from dba_data_files;

SQL> select * from dba_temp_files;

SQL> select tablespace_name,
(free_blocks*8)/1024/1024 FreeSpaceGB,
(used_blocks*8)/1024/1024 UsedSpaceGB,
(total_blocks*8)/1024/1024 TotalSpaceGB,
i.instance_name, i.host_name
from v$sort_segment ss, v$instance i
where ss.tablespace_name in
(select tablespace_name from dba_tablespaces where contents='TEMPORARY')
 ;

Besides full tablespace, disk space for database archive logs could be full. In that case, the EBS login page may give error:

Unable to generate forwarding URL. Exception: oracle.apps.fnd.common.AppsException: oracle.apps.fnd.common.PoolException: Exception creating new Poolable object.

And Sqlplus login may fail with
ERROR:
ORA-00257: archiver error. Connect internal only, until freed.

When there is a tablespace or database problem, the EBS login page may hang and then give error in IE:

Internal Server Error
The server encountered an internal error or misconfiguration and was unable to complete your request.
Please contact the server administrator, appl_mgr@xxxx.com and inform them of the time the error occurred, and anything you might have done that may have caused the error.
More information about this error may be available in the server error log.