oracle新增端口后注册服务,oracle监听【非1521端口】动态注册

动态注册

Oracle9i起实例使用动态服务注册来通知监听程序有关其数据库服务的信息。

服务注册依赖PMON 进程向监听程序注册实例信息 注册间隔为1分钟左右

手动注册命令 alter system register;

无需在listener.ora 文件中设置任何信息 此文件可以不存在,如下：

]$ rm -rf listener.ora

]$ lsnrctl start

....

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db1)(PORT=1521)))

The listener supports no services

The command completed successfully

]$ sql

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Apr 2 15:59:20 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

ERROR:

ORA-12514: TNS:listener does not currently know of service requested in connect

descriptor

Enter user-name:

]$  因为没有注册 所以不能连接

手动注册一次

SQL> alter system register;

System altered.

SQL>

]$ sql

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Apr 2 15:59:42 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> 连接成功

------------------------------------------------------------------------------

注意：

如果listener.ora中定义了监听端口(非1521)此时还想使用动态注册 需要设置local_listener参数［连接描述符］

如：

]$ lsnrctl start

......

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db1)(PORT=1522)))

Services Summary...

Service "PLSExtProc" has 1 instance(s).

Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

此时手动注册无效PMON默认只能注册默认端口1521

SQL> alter system register;

System altered.

SQL>

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db1)(PORT=1522)))

Services Summary...

Service "PLSExtProc" has 1 instance(s).

Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

修改参数local_listener 后注册成功

SQL> alter system set local_listener=‘DBSM’;

--local_listener的值一定要在tnsname.ora中设置

System altered.

SQL> alter system register;

System altered.

SQL>

~]$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 03-AUG-2012 11:33:38

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=db1)(PORT=1522)))

.....................

Service "PLSExtProc" has 1 instance(s).

Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...

Service "dbsm" has 1 instance(s).

Instance "dbsm", status READY, has 1 handler(s) for this service...

Service "dbsmXDB" has 1 instance(s).

Instance "dbsm", status READY, has 1 handler(s) for this service...

Service "dbsm_XPT" has 1 instance(s).

Instance "dbsm", status READY, has 1 handler(s) for this service...

The command completed successfully

local_listener的值一定要在tnsname.ora中设置否则报错

~]$ cat /oracle/ora10/product/10.2.0/db_1/network/admin/tnsnames.ora

# tnsnames.ora Network Configuration File: /oracle/ora10/product/10.2.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

DBSM=

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = sdw0)(PORT = 1522))

)

(CONNECT_DATA =

(SERVICE_NAME = dbsm)

)

)

EXTPROC_CONNECTION_DATA =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

)

(CONNECT_DATA =

(SID = PLSExtProc)

(PRESENTATION = RO)

)

)