下面操作默认在安装Oralce数据库的服务器上运行。
1）确保Oracle 基本服务都已启动
OracleDBConsoleorcl
OracleOraDb11g_home1TNSListener
OracleServiceORCL
2）打开命令行，使用 sqlplus nokia_user/oracle@202.206.0.233/orcl 命令登录 oracle ，显示如下：
C:\Documents and Settings\Administrator>sqlplus nokia_user/oracle@202.206.0.233/orcl SQL*Plus: Release Production on 星期五 12月 :: Copyright (c) , , Oracle. All rights reserved. ERROR: ORA-: TNS: 无监听程序 在ORA-: TNS: 无监听程序 错误下，无论是否在本地登录都是会提示ORA-: TNS: 无监听程序错误的
3）在命令行中，执行lsnrctl status
C:\Documents and Settings\Administrator>lsnrctl status
LSNRCTL -bit Windows: Version - Production on -12月- :: Copyright (c) , , Oracle. All rights reserved. 正在连接到 (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521))) LISTENER 的 STATUS ------------------------ 别名 LISTENER 版本 TNSLSNR -bit Windows: Version - Production 启动日期 -12月- :: 正常运行时间 天 小时 分 秒 跟踪级别 off 安全性 ON: Local OS Authentication SNMP OFF 监听程序参数文件 C:\Program_Files\Oracle\DataBase_11G_R2\product\\dbhome_1\network\admin\listener.ora 监听程序日志文件 c:\program_files\oracle\database_11g_r2\diag\tnslsnr\dbserver-7ec4a9\listener\alert\log.xml 监听端点概要... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=))) 服务摘要.. 服务 个实例。 实例 个处理程序... 服务 个实例。 实例 个处理程序... 服务 个实例。 实例 个处理程序... 命令执行成功
在这里可以看到监听程序参数文件的位置
4）修改监听程序参数文件
初次打开时，内容如下
# listener.ora Network Configuration File: C:\Program_Files\Oracle\DataBase_11G_R2\product\\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = C:\Program_Files\Oracle\DataBase_11G_R2\product\\dbhome_1)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:C:\Program_Files\Oracle\DataBase_11G_R2\product\11.2.0\dbhome_1\bin\oraclr11.dll")
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST =localhost)(PORT = ))
)
)
ADR_BASE_LISTENER = C:\Program_Files\Oracle\DataBase_11G_R2
修改为：
# listener.ora Network Configuration File: C:\Program_Files\Oracle\DataBase_11G_R2\product\\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = orcl)
(ORACLE_HOME = C:\Program_Files\Oracle\DataBase_11G_R2\product\\dbhome_1)
#(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:C:\Program_Files\Oracle\DataBase_11G_R2\product\11.2.0\dbhome_1\bin\oraclr11.dll")
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = orcl))
(ADDRESS = (PROTOCOL = TCP)(HOST =))
)
)
ADR_BASE_LISTENER = C:\Program_Files\Oracle\DataBase_11G_R2
SID_NAME，KEY 的值修改为要访问的oracle实例的名称，HOST 修为 oracle 服务器使用的地址
5）重启listener,在命令行中依次执行 lsnrctl stop，lsnrctl start
C:\Documents and Settings\Administrator>lsnrctl stop
LSNRCTL -bit Windows: Version - Production on -12月- :: Copyright (c) , , Oracle. All rights reserved. 正在连接到 (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=orcl))) TNS-: TNS: 无监听程序 TNS-: TNS: 协议适配器错误 TNS-: 无监听程序 -bit Windows Error: : No such file or directory 正在连接到 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=))) 命令执行成功
C:\Documents and Settings\Administrator>lsnrctl start
LSNRCTL -bit Windows: Version - Production on -12月- :: Copyright (c) , , Oracle. All rights reserved. 启动tnslsnr: 请稍候... TNSLSNR -bit Windows: Version - Production 系统参数文件为C:\Program_Files\Oracle\DataBase_11G_R2\product\\dbhome_1\network\admin\listener.ora 写入c:\program_files\oracle\database_11g_r2\diag\tnslsnr\dbserver-7ec4a9\listener\alert\log.xml的日志信息 监听: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\orclipc))) 监听: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=))) 正在连接到 (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=orcl))) LISTENER 的 STATUS ------------------------ 别名 LISTENER 版本 TNSLSNR -bit Windows: Version - Production 启动日期 -12月- :: 正常运行时间 天 小时 分 秒 跟踪级别 off 安全性 ON: Local OS Authentication SNMP OFF 监听程序参数文件 C:\Program_Files\Oracle\DataBase_11G_R2\product\\dbhome_1\network\admin\listener.ora 监听程序日志文件 c:\program_files\oracle\database_11g_r2\diag\tnslsnr\dbserver-7ec4a9\listener\alert\log.xml 监听端点概要... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\orclipc))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=))) 服务摘要.. 服务 个实例。 实例 个处理程序... 命令执行成功
6）查看状态 lsnrctl start
C:\Documents and Settings\Administrator>lsnrctl start
LSNRCTL -bit Windows: Version - Production on -12月- :: Copyright (c) , , Oracle. All rights reserved. 启动tnslsnr: 请稍候... TNSLSNR -bit Windows: Version - Production 系统参数文件为C:\Program_Files\Oracle\DataBase_11G_R2\product\\dbhome_1\network\admin\listener.ora 写入c:\program_files\oracle\database_11g_r2\diag\tnslsnr\dbserver-7ec4a9\listener\alert\log.xml的日志信息 监听: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\orclipc))) 监听: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=))) 正在连接到 (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=orcl))) LISTENER 的 STATUS ------------------------ 别名 LISTENER 版本 TNSLSNR -bit Windows: Version - Production 启动日期 -12月- :: 正常运行时间 天 小时 分 秒 跟踪级别 off 安全性 ON: Local OS Authentication SNMP OFF 监听程序参数文件 C:\Program_Files\Oracle\DataBase_11G_R2\product\\dbhome_1\network\admin\listener.ora 监听程序日志文件 c:\program_files\oracle\database_11g_r2\diag\tnslsnr\dbserver-7ec4a9\listener\alert\log.xml 监听端点概要... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\orclipc))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=))) 服务摘要.. 服务 个实例。 实例 个处理程序... 命令执行成功
7）再次登录 sqlplus nokia_user/oracle@202.206.0.233/orcl
C:\Documents and Settings\Administrator>sqlplus nokia_user/oracle@202.206.0.233/orcl SQL*Plus: Release Production on 星期五 12月 :: Copyright (c) , , Oracle. All rights reserved. 连接到: Oracle Database 11g Enterprise Edition Release - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL>
出现上面信息说明登录成功