ORA-00604: error occurred at recursive SQL level %s

Oracle Enterprise Manager was showing that agent is not able to connect to the target database. To troubleshoot and fix this, following was done.

First of all look at the agent trace file and logs to see what is happening, in my case it is was the following message.

Thread-2506293 WARN  vpxoci: OCI Error -- ErrorCode(604): ORA-00604: error occurred at recursive SQL level %s
SQL = "                                                                   OCISessionGet"...
LOGIN = dbsnmp/<PW>@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<HOSTNAME>)(PORT=<PORT NUMBER>))(CONNECT_DATA=(SID=<SID NAME>)))
Thread-2506293 ERROR vpxoci: ORA-00604: error occurred at recursive SQL level %s
Thread-2506293 WARN  vpxoci: Login 0x457f7e0 failed, error=ORA-00604: error occurred at recursive SQL level %s
Thread-2506293 ERROR engine: [oracle_database,<SID NAME>,latest_hdm_metric_helper] : nmeegd_GetMetricData failed : ORA-00604: error occurred at recursive SQL level %s
Thread-2506293 WARN  collector: <nmecmc.c> Error exit. Error message: ORA-00604: error occurred at recursive SQL level %s
Thread-2506293 WARN  vpxoci: OCI Error -- ErrorCode(604): ORA-00604: error occurred at recursive SQL level %s
SQL = "                                                                   OCISessionGet"...
LOGIN = dbsnmp/<PW>@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<HOSTNAME>)(PORT=<PORT NUMBER>))(CONNECT_DATA=(SID=<SID NAME>)))
Thread-2506293 ERROR vpxoci: ORA-00604: error occurred at recursive SQL level %s
Thread-2506293 WARN  vpxoci: Login 0x457f7e0 failed, error=ORA-00604: error occurred at recursive SQL level %s
Thread-2506293 ERROR engine: [oracle_database,<SID NAME>,latest_hdm_findings] : nmeegd_GetMetricData failed : ORA-00604: error occurred at recursive SQL level %s
Thread-2506293 WARN  collector: <nmecmc.c> Error exit. Error message: ORA-00604: error occurred at recursive SQL level %s

After looking at the message you can see that there are some issues with the user dbsnmp not able to connect to the SID.

LOGIN = dbsnmp/<PW>@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<HOSTNAME>)(PORT=<PORT NUMBER>))(CONNECT_DATA=(SID=<SID NAME>)))
Thread-2506293 ERROR vpxoci: ORA-00604: error occurred at recursive SQL level %s
Thread-2506293 WARN  vpxoci: Login 0x457f7e0 failed, error=ORA-00604: error occurred at recursive SQL level %s

Trying using SQL PLUS and connect to database using the dbsnmp

SQL> conn DBSNMP/<PASSWORD>
ERROR:ORA-00604: error occurred at recursive SQL level 1
ORA-01653: unable to extend table SYS.AUD$ by 8192 in tablespace SYSTEM
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

This means that the tablespace SYSTEM is running out of the space. Either add a datafile or extend the datafile to fix the issue.

alter database datafile '<PATH OF DATAFILE>' autoextend on maxsize 2000M;

SQL> conn dbsnmp/<PASSWORD>

 Connected.

Now you will see that under the trace file of agent the error message have gone and it issue is fixed. Oracle Enterprise manager should be able to see the database up and running.