Error received when connecting to Spotlight on Oracle:
"soo5049: The following error occurred in rana during connection: ORA-12154: TNS: could not resolve the connect identifier specifier."
This error usually occurs when there is a 10g client with other multiple Oracle homes on the client PC whether these are known to exist or not. Sometimes the Oracle clients are not removed completely and Spotlight detects them in the registry.
There are a few ways to resolve this error:
1. Verify you can TNSPING from command line and also connect via SQL*Plus or any other database application tool.
2. Delete and re-create the Spotlight connection profile from the Spotlight Connection Manager, then try to reconnect.
3. Ensure the default Oracle home is listed first in the PATH on the client e.g. Start | Run | cmd | type PATH. If the default Oracle home is not listed first in the PATH edit the PATH from the Environment Variables field in Control Panel | System | Advanced | Environment Variables | System User Variables | PATH, edit the path placing the default Oracle_home to the front of the list before any other Oracle_home in the list.
4. You can also deploy the TNS_ADMIN Key in the registry e.g. Start | Run | type REGEDIT | HKEY_Local_Machine | Software | Oracle, then right-click and select New Key, enter the default Oracle_home path.
****WARNING: Using Registry Editor incorrectly can cause serious problems that can damage the operating system and the software installed. Quest cannot guarantee that problems resulting from the incorrect use of Registry Editor can be solved. Use Registry Editor at your own risk.***
5. You may also set the Oracle Home as the Default Home in the registry e.g. Start | Run | type REGEDIT | HKEY_Local_Machine | Software | Oracle. On the right, Double-click the [Default] key and enter the default Oracle_home path. Click <F5> to refresh the registry.
6. To find which Tnsnames.ora Spotlight is using when having multilpe Oracle homes, run TNSPING and look for the default Oracle Home path under "Used Parameter Files" line.
7. If "Used Parameter Files" line is blank, then ...
a. Create a new TNS entry using the Oracle Configuration Assistant like "test_SoO",
b. Connect to Spotlight and look in the Spotlight database dropdown list to see if "test_SoO" is present.
c. Perform a complete search for this TNS entry inside all folders/files on the client PC.
NOTE: This should return the Tnsnames.ora file that Spotlight is using to connect yet it may not be the Default Oracle Home defined on the client computer.
In this case, it is the users discretion to:
- remove any unnecessary Oracle Home(s) from the Oracle Uninversal Installer <or>
- set the correct Oracle Home as the Default <or>
- reinstall the default Oracle Home.
NOTE: As always, when making 'any' changes, please re-create the Spotlight connection profile and re-connect to Spotlight.
8. Parentheses are not permitted in installation path names: The Oracle client does not support an installation path for Spotlight that contains the brackets "(" and ")". For example: C:\Program Files (x86)\...
Reinstall Spotlight and choose a different installation directory. Make sure that the installation directory does not have parentheses in it.