Came across these entries (example) in TNSNAMES.ORA on a client machine; and need some clarifications from the gurus here.
10g TNSNAMES.ORA Server 123.456.789.111
DEV.SERVER.WORLD.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 123.456.789.111)(PORT = 1521))
)
(CONNECT_DATA =
(SID = DEVWORLD)
(SERVER = DEDICATED)
)
)
PROD.SERVER.WORLD.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 123.456.789.111)(PORT = 1521))
)
(CONNECT_DATA =
(SID = PRODWORLD)
(SERVER = DEDICATED)
)
)
10g LISTENER.ORA (Server 123.456.789.111
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = DEVWORLD)
(ORACLE_HOME=C:\oracle\product\10.2.0\db_1)
)
(SID_DESC =
(SID_NAME = PRODWORLD)
(ORACLE_HOME=C:\oracle\product\10.2.0\db_1)
)
(SID_DESC =
(SID_NAME = BLAHBLAH)
(ORACLE_HOME=C:\oracle\product\10.2.0\db_1)
)
)
11g TNSNAMES.ORA (server 123.456.789.000)
PRODSRV =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 123.456.789.000)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PROD.SERVER.WORLD.COM)
)
)
11g LISTENER.ORA (Server 123.456.789.000)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = C:\Oracle\product\11.2.0\dbhome_1)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:C:\Oracle\product\11.2.0\dbhome_1\bin\oraclr11.dll")
)
(SID_DESC =
(SID_NAME = PROD)
(ORACLE_HOME=C:\Oracle\product\11.2.0\dbhome_1\)
)
)
Questions
1) Server level
In my 10g example, it looks like the link from LISTENER and TNSNAMES is SID_NAME. However, on the 11g, I'm seriously not sure how does it link considering SID_NAME is not on the TNSNAMES.
a) How does one suppose to specify TNSNAMES to correspond with the LISTENER so connections can be established?
b) Also, can 2 same SID exists on the same server?
c) What is that CLRExtProc on the 11g Listener? I don't think I have that database.
Client TNSNAMES.ORA
PRODSRV =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 123.456.789.000)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PROD.SERVER.WORLD.COM)
)
)
DEVWORLD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 123.456.789.111)(PORT = 1521))
)
(CONNECT_DATA =
(SID = DEVWORLD)
(SERVER = DEDICATED)
)
)
PRODWORLD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 123.456.789.111)(PORT = 1521))
)
(CONNECT_DATA =
(SID = PRODWORLD)
(SERVER = DEDICATED)
)
)
2) Server - Client level
a) how does one normally sets up client connections?
As you can see from my client machine, some of the entries are with SID and some don't.
I can connect using alias the following: SQLPLUS SYS@PRODSRV or @DEVWORLD or @PRODWORLD
b) I cannot connect even if I issue SET ORACLE_SID=PRODWORLD and then try SQLPLUS SYS as SYSDBA. Is this method used when connecting within server level?
Thanks.