静态注册和动态注册-创新互联

 创建侦听器

侦听器在listener.ora文件中定义，这个文件的默认存储位置为ORACLE_HOME/network/admin目录。listener.ora文件至少必须声明一个侦听器的名称及其使用的协议与侦听地址。可以在一个文件中配置多个侦听器，不过这些侦听器必须具有不同的名称与地址。
与其他用于配置Oracle Net 的文件一样，listener.ora文件非常在意看似很小的语法问题。

[oracle@localhost admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
 )

ADR_BASE_LISTENER = /u01/app/oracle

[oracle@localhost admin]$ vi ./listener.ora
[oracle@localhost admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
 )

ADR_BASE_LISTENER = /u01/app/oracle

WYH =
   (DESCRIPTION =
      (ADDRESS_LIST =
         (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1522))
      )
   )

[oracle@localhost admin]$

数据库注册

向数据库注册实例的方法有静态注册和动态注册两种。对于静态注册来说，我们可以将一列实例硬编码在listener.ora文件中。动态注册意味着实例在启动时需要定位侦听器并注册到侦听器中。

1、静态注册

在listener.ora文件中添加条目

SID_LIST_WYH =
   (SID_LIST =
      (SID_DESC =
         (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
         (SID_NAME = orcl)
      )
   )

测试静态注册

[oracle@localhost admin]$ lsnrctl

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 14-APR-2016 11:17:49

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

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> start WYH
Starting /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.3.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/localhost/wyh/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.localdomain)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                    WYH
Version                  TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date               14-APR-2016 11:17:58
Uptime                   0 days 0 hr. 0 min. 0 sec
Trace Level              off
Security                 ON: Local OS Authentication
SNMP                     OFF
Listener Parameter File  /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File        /u01/app/oracle/diag/tnslsnr/localhost/wyh/alert/log.xml
Listening Endpoints Summary...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522)))
Services Summary...
Service "orcl" has 1 instance(s).
 Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL>

2、动态测试

初始化参数local_listener会告知实例其应当进行联系的，从而能够查找到注册实例的侦听器的网络地址。在启动实例时，PMON进程会使用该参数来定位一个侦听器，并向其通知实例名称以及实例提供的服务名称。
[oracle@localhost admin]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 14 12:12:37 2016

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

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initorcl.ora'
ORACLE instance started.

Total System Global Area 1436389376 bytes
Fixed Size         2228384 bytes
Variable Size       855641952 bytes
Database Buffers    570425344 bytes
Redo Buffers         8093696 bytes
Database mounted.
Database opened.
SQL> alter system register ;

System altered.

SQL> ho lsnrctl

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 14-APR-2016 12:14:17

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

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> status
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
 TNS-00511: No listener
  Linux Error: 111: Connection refused
LSNRCTL> start
Starting /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.3.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                    LISTENER
Version                  TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date               14-APR-2016 12:14:38
Uptime                   0 days 0 hr. 0 min. 0 sec
Trace Level              off
Security                 ON: Local OS Authentication
SNMP                     OFF
Listener Parameter File  /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File        /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
The listener supports no services
The command completed successfully
LSNRCTL> start
TNS-01106: Listener using listener name LISTENER has already been started
LSNRCTL> status
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                    LISTENER
Version                  TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date               14-APR-2016 12:14:38
Uptime                   0 days 0 hr. 0 min. 43 sec
Trace Level              off
Security                 ON: Local OS Authentication
SNMP                     OFF
Listener Parameter File  /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File        /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
Services Summary...
Service "orcl" has 1 instance(s).
 Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
 Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL>
[oracle@localhost admin]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 14 12:12:37 2016

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

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initorcl.ora'
ORACLE instance started.

Total System Global Area 1436389376 bytes
Fixed Size         2228384 bytes
Variable Size       855641952 bytes
Database Buffers    570425344 bytes
Redo Buffers         8093696 bytes
Database mounted.
Database opened.
SQL> alter system register ;

System altered.

SQL> ho lsnrctl

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 14-APR-2016 12:14:17

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

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> status
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
 TNS-00511: No listener
  Linux Error: 111: Connection refused
LSNRCTL> start
Starting /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.3.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                    LISTENER
Version                  TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date               14-APR-2016 12:14:38
Uptime                   0 days 0 hr. 0 min. 0 sec
Trace Level              off
Security                 ON: Local OS Authentication
SNMP                     OFF
Listener Parameter File  /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File        /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
The listener supports no services
The command completed successfully
LSNRCTL> start
TNS-01106: Listener using listener name LISTENER has already been started
LSNRCTL> status
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                    LISTENER
Version                  TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date               14-APR-2016 12:14:38
Uptime                   0 days 0 hr. 0 min. 43 sec
Trace Level              off
Security                 ON: Local OS Authentication
SNMP                     OFF
Listener Parameter File  /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File        /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
Services Summary...
Service "orcl" has 1 instance(s).
 Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
 Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL>
LSNRCTL>
LSNRCTL> exit

SQL> alter system set local_listener='(address=(pro=tcp)(host=localhost.localdomain)(port=1522))';

System altered.

SQL> ho lsnrctl

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 14-APR-2016 12:20:35

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

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> stop
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
The command completed successfully
LSNRCTL> start WYH
TNS-01106: Listener using listener name WYH has already been started
LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.localdomain)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                    WYH
Version                  TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date               14-APR-2016 11:23:13
Uptime                   0 days 0 hr. 58 min. 5 sec
Trace Level              off
Security                 ON: Local OS Authentication
SNMP                     OFF
Listener Parameter File  /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File        /u01/app/oracle/diag/tnslsnr/localhost/wyh/alert/log.xml
Listening Endpoints Summary...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522)))
Services Summary...
Service "orcl" has 2 instance(s).
 Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
 Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
 Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL>
LSNRCTL> exit

SQL>
SQL>
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name '((ADDRESS_LIST= (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1522)))'
SQL> startup pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initorcl.ora'
ORACLE instance started.

Total System Global Area 1436389376 bytes
Fixed Size         2228384 bytes
Variable Size       855641952 bytes
Database Buffers    570425344 bytes
Redo Buffers         8093696 bytes
Database mounted.
Database opened.
SQL> create spfile from pfile ;

File created.

SQL>
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1436389376 bytes
Fixed Size         2228384 bytes
Variable Size       855641952 bytes
Database Buffers    570425344 bytes
Redo Buffers         8093696 bytes
Database mounted.
Database opened.
SQL> create pfile from spfile ;

File created.

SQL>

另外有需要云服务器可以了解下创新互联cdcxhl.cn，海内外云服务器15元起步，三天无理由+7*72小时售后在线，公司持有idc许可证，提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案，具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势，专为企业上云打造定制，能够满足用户丰富、多元化的应用场景需求。