Applications Manager SQL Jobs fail with "ORA-01017: invalid username/password" AND "SP2-0306: Invalid option" errors

Products

• CA Automic Applications Manager (AM)

Issue/Introduction

When running any Applications Manager SQL Job, the below error is seen in the output file.

ERROR:
ORA-01017: invalid username/password; logon denied

SP2-0306: Invalid option.
Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM|SYSBACKUP|SYSDG|SYSKM|SYSRAC}] [edition=value]]
where <logon> ::= <username>[/<password>][@<connect_identifier>]
      <proxy> ::= <proxyuser>[<username>][/<password>][@<connect_identifier>]
SP2-0306: Invalid option.
Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM|SYSBACKUP|SYSDG|SYSKM|SYSRAC}] [edition=value]]
where <logon> ::= <username>[/<password>][@<connect_identifier>]
      <proxy> ::= <proxyuser>[<username>][/<password>][@<connect_identifier>]
SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

Environment

Applications Manager 9.x.x 

Cause

SQL*Plus doesn't recognize as a valid connection string (like an extra space, a missing slash, or a special character in the password that isn't being escaped). 

Resolution

To troubleshoot the actual username/password value that is being passed, which is normally hidden, follow the below steps to print out the clear text username/password to the output file. This will allow you to confirm if the username password value has the correct password and is in the correct format:

1. Enable BODY and SQLP debug by creating a two 0 byte files in the $AW_HOME/debug directory with the filename BODY and SQLP.
   
   
2. Edit the $AW_HOME/exec/SQLP script (make a backup). Locate lines 22 to 25. It should look like below:
   
   logtest=login.$$
   set -
   echo "$login
   set termout off
   
   
3. change "set -" to "set -x" and add one additional line under this line and add the line "echo "DEBUG: Login value is: $login""
   
   The resulting lines should now look like below:
   
   logtest=login.$$
   set -x
   echo "DEBUG: Login value is: $login"
   echo "$login
   set termout off
   
   This will echo out the login and password when running a SQL Job.
   
   
4. Re-run the SQL Job and review the output file. If needed, correct the login and password. If the login is the Applications Manager Oracle login or if further assistance is required, open a case with Broadcom Support and provide the output file for review.