Hi Guys,
I have actually resolved this issue but I wonder is this because of an Oracle database defects or something.
This issue can be reproduced easily, just follow below steps:
1, download sqlplus instant client and basic lite instant client (Windows XP SP2 32bit, Database Server is RHEL5.8 32bit with 11gR2)
2, the sqlplus instant client actually does not have all the dll files needed to run the program, this gives me a bit of headache, I wonder which genius in Oracle comes up with this idea? Why not just include all files? Isn't when we download something we are expecting to double click it to run? All right, let's go on.
3, copy files "oci.dll", "orannzsbb11.dll" and "oraociicus11.dll" from basic lite instance client to the same directory as the sqlplus
4, create an tnsnames.ora file just like everybody else, here is the file:
orcl =
(description=
(address=(protocol=tcp)(host=192.168.56.58)(port=1521))
(connect_data=(service_name=orcl.localdomain))
)
5, set enviroment variable SQLPATH, TNS_ADMIN, PATH to the path of the sqlplus
6, now go to command prompt, connect to sqlplus with command:
sqlplus admin/password1@orcl
the result is error message:
ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA
7, ok, then I tried to use the connection description in the sqlplus command like this:
sqlplus admin/password1@\" (description=(address=(protocol=tcp)(host=192.168.56.58)(port=1521))(connect_data=(service_name=orcl.localdomain)))\"
this time it worked perfectly
8, then after about an hour of online searching, I finally resolved it, I added this line to the listener.ora file on the database server:
default_service_listener=orcl.localdomain
9, then remove the "connect_data" from the tnsnames.ora file of my sqlplus client folder. So the new tnsname.ora looks like this:
orcl =
(description=
(address=(protocol=tcp)(host=192.168.56.58)(port=1521))
)
10, now I can connect to the database with below command, no more error message
sqlplus admin/password1
what happen is that I specified a default service for the listener, so now I can connect. The draw back is that I can only connect to one database service right now.
The problem here is that when using tnsnames.ora file, the sqlplus seems to unable correctly identify the "service_name" provided in the file. As in the example shows, there is absolutely no problem with the network or any other configurations but the sqlplus parsing the tnsnames.ora file.
Please let me know if this is due to a defect or something? I downloaded the lastest instant client from Oracle site just a moment ago.