How to identified active files/executables when using opatch to deinstall software

During a opatch rollback operation, we saw the following error message in the opatch logfile:

[Feb 19, 2023 2:29:51 PM] [INFO]    Prerequisite check "CheckActiveFilesAndExecutables" failed.
                                    The details are:


                                    Following active files/executables/libs are used by ORACLE_HOME :/sw/oracle/product/19.18
                                    /sw/oracle/product/19.18/lib/libclntsh.so.19.1

Reason:
Some processes are still using the /sw/oracle/product/19.18/lib/libclntsh.so.19.1.
Use the fuser utility with verbose output to find the process:

fuser -v /sw/oracle/product/19.18/lib/libclntsh.so.19.1
                     USER        PID ACCESS COMMAND
/sw/oracle/product/19.18/lib/libclntsh.so.19.1:
                     oracle    48439 ....m prometheus_orac
                     oracle    595787 ....m ggsci

There were two open processes using the file libclntsh.so.19.1: 1. the Golden Gate Manager 2. a utility called prometheus_oracle_exporter

Solution:

log in as the Golden Gate software owner
ggsci --> info all --> list all processes
stop mgr !

One of two processes quit its handler on the file:

 fuser -v /sw/oracle/product/19.18/lib/libclntsh.so.19.1
                     USER        PID ACCESS COMMAND
/sw/oracle/product/19.18/lib/libclntsh.so.19.1:
                     oracle    48439 ....m prometheus_orac

For the prometheus agent, we simply kill the agent, and the output from fuser now reveals that no file handlers are open:

kill 48439
fuser -v /sw/oracle/product/19.18/lib/libclntsh.so.19.1

Using the procedure SEARCHFILES in DBMS_BACKUP_RESTORE

I found the following great article about how to list files in directory from within sqlplus here, written by David Marco. As pointed out by several oracle bloggers, there is very little documentation around for this package.

It just so happened that a user was in need of such functionality, and his code could be used straight-off.

However, my customer had already ammended a very useful functionality, by adding support for sending the directory name in as a parameter.

The code as it looks now:

CREATE OR REPLACE FUNCTION LIST_FILES( p_directory IN VARCHAR2, p_file_pattern IN VARCHAR2 default null)
RETURN file_array pipelined AS

l_path VARCHAR2(1024);
l_dummy VARCHAR2(1024);

BEGIN

   SELECT directory_path
   INTO l_path
   FROM all_directories
   WHERE directory_name = p_directory;

   sys.DBMS_BACKUP_RESTORE.SEARCHFILES(l_path, l_dummy);

   FOR file_list IN (SELECT FNAME_KRBMSFT AS file_name
                     FROM X$KRBMSFT
                     WHERE FNAME_KRBMSFT LIKE '%'|| NVL(p_file_pattern, FNAME_KRBMSFT)||'%' ) LOOP
      PIPE ROW(file_list.file_name);
   END LOOP;

END;
/

The call will thus be:

SELECT * FROM TABLE(LIST_FILES('MY_DIR','%*.dmp%'));

How to use the DBMS_FILE_TRANSFER.PUT_FILE procedure

BEGIN
  SYS.DBMS_FILE_TRANSFER.PUT_FILE(
   source_directory_object      => 'DPUMP',
   source_file_name             => 'myfile.txt',
   destination_directory_object => 'REMOTE_DPDUMP',
   destination_file_name        => 'myfile.txt',
   destination_database         => 'REFRESH.MYDOMAIN.COM');
END;
/

Used in a script:

export DMP_NAME=`echo $1 | tr '[a-z]' '[A-Z]'`
export DPDIR=`echo $4 | tr '[a-z]' '[A-Z]'`

#####################################################
# Transfer to remote server using DBMS_FILE_TRANSFER
#####################################################

cat << EoF > ${DBA_ADMIN}/sql/copy_file.sql
set trimspool on
spool copy_file.log
Prompt Transferring Dumpfiles;
define file_name=&1


BEGIN
  SYS.DBMS_FILE_TRANSFER.PUT_FILE(
   source_directory_object      => 'DPUMP',
   source_file_name             => '&file_name',
   destination_directory_object => '${DPDIR}',
   destination_file_name        => '&file_name',
   destination_database         => 'REFRESH.MYDOMAIN.COM');
END;
/
exit
EoF

for dmpfile in $(ls /oracle/datapump/${DMP_NAME}_*.dmp); do
 file_name=`echo $dmpfile | cut -d / -f 7`

sqlplus -s / as sysdba @${DBA_ADMIN}/sql/copy_file.sql ${file_name} > ${BATCHDIR}/file_name.log 2> ${BATCHDIR}/file_name.err &
done
wait

rm -f  ${DBA_ADMIN}/sql/copy_file.sql