aix_oracle11g_数据库动态注册问题

最新推荐文章于 2024-11-12 20:35:48 发布

cuiwenliao6197

最新推荐文章于 2024-11-12 20:35:48 发布

阅读量106

点赞数

文章标签：数据库网络

os:aix 6.1 64位
oracle:11.1.0.6.0
项目上在oracle10g时由于缺少系统包，为了省事不装这些缺少的包，就安装了11g，数据库安装好后，配置完监听后
数据库无论是动态注册或静态注册，启动监听时都提示“The listener supports no services”
在数据库主机上执行 tnsping HHHDB是ok的，但是执行sqlplus system/*******@HHHDB就报错:
===================
$ lsnrctl status

LSNRCTL for IBM/AIX RISC System/6000: Version 11.1.0.6.0 - Production on 03-9月 -2010 21:42:19

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=HHHDB)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for IBM/AIX RISC System/6000: Version 11.1.0.6.0 - Production
Start Date                03-9月 -2010 21:41:14
Uptime                    0 days 0 hr. 1 min. 4 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      ON
Listener Parameter File   /oracle/product/11.1/db/network/admin/listener.ora
Listener Log File         /oracle/diag/tnslsnr/HHHDB/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=HHHDB)(PORT=1521)))
The listener supports no services
The command completed successfully

====================
$ tnsping HHHDB

TNS Ping Utility for IBM/AIX RISC System/6000: Version 11.1.0.6.0 - Production on 03-9月 -2010 20:44:26

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 100.222.149.191)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = db01)))
OK (60 msec)

=========================
$ sqlplus system/********@HHHDB

SQL*Plus: Release 11.1.0.6.0 - Production on 星期五 9月 3 20:48:45 2010

ERROR:
ORA-12514: TNS: 监听程序当前无法识别连接描述符中请求的服务

Enter user-name:

===========================
解决办法:

步骤1:用netca重建监听解决监听启动时HOST为空的问题 "Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))"
$ lsnrctl start

LSNRCTL for IBM/AIX RISC System/6000: Version 11.1.0.6.0 - Production on 03-9月 -2010 20:53:54

Starting /oracle/product/11.1/db/bin/tnslsnr: please wait...

TNSLSNR for IBM/AIX RISC System/6000: Version 11.1.0.6.0 - Production
Log messages written to /oracle/diag/tnslsnr/HHHDB/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=HHHDB)(PORT=1521)))

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for IBM/AIX RISC System/6000: Version 11.1.0.6.0 - Production
Start Date                03-9月 -2010 20:53:54
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      ON
Listener Log File         /oracle/diag/tnslsnr/HHHDB/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=HHHDB)(PORT=1521)))
The listener supports no services
The command completed successfully

===========重建后的listener.ora文件
$ cat listener.ora
# listener.ora Network Configuration File: /oracle/product/11.1/db/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
(DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = HHHDB)(PORT = 1521))
    )
)

步骤2:给LOCAL_LISTENER参数赋值，手动向监听注册实例，实例成功注册到监听
ALTER SYSTEM SET LOCAL_LISTENER = '(ADDRESS = (PROTOCOL = TCP)(HOST = 100.222.149.191)(PORT = 1521))' SID = 'db01';
alter system register;
===========================
$ lsnrctl status

LSNRCTL for IBM/AIX RISC System/6000: Version 11.1.0.6.0 - Production on 03-9月 -2010 21:46:17

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=HHHDB)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for IBM/AIX RISC System/6000: Version 11.1.0.6.0 - Production
Start Date                03-9月 -2010 21:41:14
Uptime                    0 days 0 hr. 5 min. 2 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      ON
Listener Parameter File   /oracle/product/11.1/db/network/admin/listener.ora
Listener Log File         /oracle/diag/tnslsnr/HHHDB/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=HHHDB)(PORT=1521)))
Services Summary...
Service "db01" has 1 instance(s).
Instance "db01", status READY, has 1 handler(s) for this service...
Service "db01XDB" has 1 instance(s).
Instance "db01", status READY, has 1 handler(s) for this service...
Service "db01_XPT" has 1 instance(s).
Instance "db01", status READY, has 1 handler(s) for this service...
The command completed successfully

步骤3:执行sqlplus system/SysteM@hqnms成功登陆数据库

步骤4:重启数据库和监听

数据库实例成功动态注册到监听，通过监听可以成功连接到数据库

总结: 此问题的主要原因在LOCAL_LISTENER参数，数据库实例通过读取该参数，得到向其注册的监听器的地址和端口号。
在实例启动时，PMON进程会使用该参数来定位监听器的地址，并向其通知isnstance_name和service_name参数的值。
只要监听器的端口默认用的是1521时，该参数是不需要配置的。只有端口不是1521才需要配置LOCAL_LISTENER，至少在oracle9i和10g
是这样的。在oracle11g里也许就是要配置该参数吧(印象中以前装的一个11g的库时，没有配置该参数，客户端也可以用监听连接上数据库，
可能这个项目上的数据库在安装时有特殊的地方吧)。

该参数还有另一种配置方法：
步骤1. ALTER SYSTEM SET LOCAL_LISTENER = 'LISTENERS_SYS1' SID = 'sys1';
步骤2.在数据库主机的tnsnames.ora中添加如下信息：
LISTENERS_SYS1 =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 100.100.100.100)(PORT = 1521))
)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16396821/viewspace-672570/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16396821/viewspace-672570/