Records complex knowledge about Oralce.
Basic Format of tnsnames.ora File
tcpnew1 = (description=
(source_route=yes)
(address=(protocol=tcp)(port=1521)(host=spcstn))
(address=(protocol=tcp)(port=1580)(host=spcstn)))
(connect_data=(service=wikidb.bndy.net))
)
spx2tcp = (description=
(source_route=yes)
(address=(protocol=spx)(service=orasrvc1))
(address=(protocol=tcp)(port=1580)(host=spcstn)))
(connect_data=(service_name=wikidb2.bndy.net))
)
SID and SERVICE_NAME
Question: What is the difference between the SID and SERVICE_NAME in my tnsnames.ora file? Why does Oracle have three parameters for the same thing, instance_name, service_name, db_name, and oracle_sid?
Answer: Part of the reason for this separation is in the evolution of Oracle, but we need to remember that Oracle specifies database locations in many files. For TNS networking, the SERVICE_NAME and SERVICE_NAMES parameters were first introduced in Oracle 8i and supersede the older SID designation.
Difference The SID is the same as the $ORACLE_SID environment variable in the sense that the SID is the system is for a unique instance on the server.
On the other hand, the SERVICE_NAME is used to register an instance with the listener. In most all cases, Oracle recommends that the value of the service_name be the same as the SID. However, a SERVICE_NAME can point to more than one instance, and the DBA can gen-in additional SID's into a SERVICE_NAME .
>sqlplus username/password@service_name // service_name in tnsnames.ora
>sqlplus username/password@//host:port/sid // sqlplus oracleUser/password@//localhost:1521/wiki.bndy.net
SQL>CONN username/password@service_name
NOTE: If @ in password, please use sqlplus user/\"password@123\"@TEST_DB