Connection issue from

While connecting to Database from client or using DB link if you face below issue , Please follow below mentioned steps.

ORA-00604: error occurred at recursive SQL level 1
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 13

I wrote this piece after a team of operation at my workplace required a partial database. When I prepared what they wanted and sent them a TNS to connect, they informed me that they could not  connect because of our login trigger.

That was quite easy to understand the problem. Our login trigger was recording sessions in a table that resides on a tablespace that I have not restored =)

If you wat to disable all the system triggers in your database, you must set an undocumented parameter in your parameter file as below:

_system_trig_enabled=FALSE

 alter system set "_system_trig_enabled"=FALSE; --- On Database side.

Thanks.

UtilSession failed: Prerequisite check "CheckActiveFilesAndExecutables" failed.

Patch Log file:

 Stack Description: java.lang.RuntimeException: Prerequisite check "CheckActiveFilesAndExecutables" failed.
StackTrace: oracle.opatch.OPatchSessionHelper.runApplyPrereqs(OPatchSessionHelper.java:5931)
StackTrace: oracle.opatch.opatchutil.NApply.process(NApply.java:644)
StackTrace: oracle.opatch.opatchutil.OUSession.napply(OUSession.java:1088)
StackTrace: sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
StackTrace: sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
StackTrace: sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
StackTrace: java.lang.reflect.Method.invoke(Method.java:592)
StackTrace: oracle.opatch.UtilSession.process(UtilSession.java:317)
StackTrace: oracle.opatch.OPatchSession.main(OPatchSession.java:2581)
StackTrace: oracle.opatch.OPatch.main(OPatch.java:626)


Cause:

Linux/Unix:

According to the above  error message, oracle binary ($ORACLE_HOME/bin/oracle) is still active or in use.
It can also  fail with other ORACLE_HOME active files/libraries .
Example:
/u01/app/oracle/10.2.0/bin/tnslsnr
/u01/app/oracle/11.2.0.3/lib/libclntsh.so.11.1

Windows:

Please be aware that before applying patches, you must shutdown the database, listener, sqlplus, EM dbconsole/dbcontrol and any and all processes, including 3rd-party processes, which use files in the ORACLE_HOME.  The error messages in the OPatch log files show that there are one or more programs still running that are using files in the ORACLE_HOME you are trying to patch.

Linux/Unix:

Verify and ensure that all database instances/services that are running from the $ORACLE_HOME being patched are cleanly shutdown.
Also  identify the process that is using these active files/libraries. Then stop the process who is still accessing it and then apply/rollback the patch.

To identify active process, execute

$ fuser -c $ORACLE_HOME

Windows:

If the database and Oracle services are already shutdown, then perhaps it is a 3rd-party program.  If you do not already have it, you should consider installing "Process Explorer" from Microsoft.  After starting procexp.exe, press Ctl-F (Find) and type in one of the DLLs from the "active" list (in the OPatch log). For this example, based on the "active" list shown above, the first DLL to check is orageneric11.dll.  That should tell you what process is using that DLL so you can shut it down. Process Explorer is fairly easy to use, but if you need help with downloading or using it, please contact Microsoft Support as usage of Process Explorer is outside the scope of Oracle Support.

There are a variety of non-Oracle 3rd-party programs which could be using files in the ORACLE_HOME.  One culprit is WMI (Windows Management Instrumentation).  Once you shutdown the 3rd-party process(es) using files in the ORACLE_HOME, and Process Explorer comes back clean, then attempt to apply the patch again and the OUI-67074 error should not reoccur.

Thanks.