oracle10g和oracle11g双库监听及TNS配置

oracle10g和oracle11g双库监听及TNS配置
oracle10g和oracle11g一机双库的监听程序配置及TNSnames 文件配置
操作系统win7
闲来无事在win7上安装先后安装了oracle10g和oracle11g
Oracle10g下有sqlplusw工具，而oracle11g下面没有该工具，只有sqlpuls工具。

问题出现了：
使用oracle10g的sqlplusw工具连接11g的数据库
Conn sys/sa@orcl11g as sysdba
执行该命令后总是报错
ORA-12514：监听程序无法识别连接描述符中请求的服务。

Conn sys/sa@hptest as sysdba一切正常
使用oracle11g的sqlplus工具连接两个数据库均正常。

删除oracle10g的监听程序并重建后，问题依旧。

重新配置oracle10g的TNS命名，问题依旧。

奇怪的使用oracle10g的tnsping工具能够ping通orcl11g。

百思不得其解的情况下仔细看一下tnsnames文件。

发现监听的端口号不一样。

Oracle10g的监听端口是1521 oracle11g的监听端口是1522。

原因找到了：安装oracle11g的时候由于本机上已经存在了oracle10g的监听程序占用了1521端口。

11g的监听程序端口就自动变成了1522。

使用11g的oracle工具配置tnsnames的时候，其它服务默认是1521端口。

11g服务默认1522端口。

所以11g的工具sqlplus连接两个服务器都正常。

使用10g的oracle工具配置tnsnames的时候，所有的服务它都认为是1521端口。

这就解释了能够ping通，确连接不上的原因，端口不对嘛~~~！
找到10g的tnsnames配置，把到11g的端口改为1522问题解决。

惯性思维导致解决问题花了一定的时间，总是认为oracle监听的端口是1521，其实不然。

Oracle10g监听文件
# listener.ora Network Configuration File: E:\oracle\product\10.2.0\db_1\network\admin\listener.ora # Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = E:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = hp_he_inter)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
Oracle10g tnsnames文件
# tnsnames.ora Network Configuration File: E:\oracle\product\10.2.0\db_1\network\admin\tnsnames.ora # Generated by Oracle configuration tools.
ORCL11G =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1522))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl11g)
)
)
HPTEST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = hptest)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
Oracle11g监听配置
# listener.ora Network Configuration File: d:\app\hp_he\product\11.2.0\dbhome_1\network\admin\listene r.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = d:\app\hp_he\product\11.2.0\dbhome_1) (PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:d:\app\hp_he\product\11.2.0\dbhome_ 1\bin\oraclr11.dll") )
(SID_DESC =
(GLOBAL_NAME=ORCL11G)
(SID_NAME = ORCL11G)
(ORACLE_HOME = d:\app\hp_he\product\11.2.0\dbhome_1) )
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522))
)
)
ADR_BASE_LISTENER = d:\app\hp_he
Oracle11g tnsname配置
# tnsnames.ora Network Configuration File:
d:\app\hp_he\product\11.2.0\dbhome_1\NETWORK\ADMIN\tns names.ora
# Generated by Oracle configuration tools.
ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
)
(CONNECT_DATA =
(SID = CLRExtProc)
(PRESENTATION = RO)
)
)
ORCL11G =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1522))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl11g)
)
)
HPTEST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = hptest)
)
)
LISTENER_ORCL11G =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522))。