預設及非預設埠的動態監聽/靜態監聽實驗彙總

步驟大致是：
實驗1.配置一個非預設和預設的動態監聽同時存在，非預設監聽執行在1525埠
實驗2：配置一個非預設埠的動態監聽--使用local_listener=的值伺服器端的tnsnames.ora裡的本地名的方法
實驗3.配置一個靜態監聽，執行在1526埠

資料庫內一些引數：
SYS@ bys3>show parameter service
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      bys3
SYS@ bys3>show parameter db_na
NAME                                TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      bys3
SYS@ bys3>show parameter db_unique
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      bys3
SYS@ bys3>show parameter db_domain
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_domain                            string
SYS@ bys3>show parameter instance_na

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_name                        string      bys3
關於這幾個引數的設定及替換，詳見：ORACLE監聽之動態監聽與靜態監聽特點

配置預設監聽加非預設動態監聽示例

1.配置listener.ora，增加非預設動態監聽的地址、埠條目

[oracle@bys3 admin]$ more listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = bys3.bys.com)(PORT = 1525))
      (ADDRESS = (PROTOCOL = TCP)(HOST = bys3.bys.com)(PORT = 1521))
    )
  )
ADR_BASE_LISTENER = /u01

2.在資料庫中配置 local_listener引數,語句如下：--就是把listener.ora中的幾行復制過來，注意換行的括號不要少了。

alter system set local_listener='(DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = bys3.bys.com)(PORT = 1525))
      (ADDRESS = (PROTOCOL = TCP)(HOST = bys3.bys.com)(PORT = 1521)))';
SYS@ bys3>alter system register;  --手動註冊，或者等上一分鐘，讓PMON自動進行動態監聽註冊。
因為這裡是local_listener的值是兩行ADDRESS，要把DESCRIPTION =也加上。如果是一行，不需要加。如：alter system set local_listener='(ADDRESS = (PROTOCOL = TCP)(HOST = bys3.bys.com)(PORT = 1525))';

3.檢視監聽狀態，動態監聽已經註冊成功：

[oracle@bys3 ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 14-JAN-2014 22:20:16
Copyright (c) 1991, 2013, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=bys3.bys.com)(PORT=1525)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                14-JAN-2014 22:02:22
Uptime                    0 days 0 hr. 17 min. 54 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/diag/tnslsnr/bys3/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=bys3.bys.com)(PORT=1525)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=bys3.bys.com)(PORT=1521)))
Services Summary...
Service "bys3" has 1 instance(s).
  Instance "bys3", status READY, has 1 handler(s) for this service...
The command completed successfully

4.使用SQLPLUS進行登陸測試：

[oracle@bys3 admin]$ cat tnsnames.ora
bys3 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = bys3.bys.com)(PORT =1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = bys3)
    )
  )
test =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = bys3.bys.com)(PORT =1525))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = bys3)
    )
  )
[oracle@bys3 admin]$ tnsping test
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 14-JAN-2014 22:30:42
Copyright (c) 1997, 2013, Oracle.  All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = bys3.bys.com)(PORT =1525)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = bys3)))
OK (0 msec)
[oracle@bys3 admin]$ tnsping bys3
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 14-JAN-2014 22:30:46
Copyright (c) 1997, 2013, Oracle.  All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = bys3.bys.com)(PORT =1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = bys3)))
OK (10 msec)
[oracle@bys3 admin]$ sqlplus bys/bys@bys3
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 14 22:32:02 2014
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
BYS@ bys3>exit

[oracle@bys3 admin]$ sqlplus bys/bys@test
BYS@ test>exit
###################################################

配置單一一個非預設監聽的示例：--引數local_listener值改為tnsnames.ora中的本地名

1.需要把非預設的監聽器新增到listener.ora:

[oracle@bys3 admin]$ cat listener.ora
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = bys3.bys.com)(PORT = 1529))
    )
  )
 

2.伺服器端的tnsnames.ora裡新增：

[oracle@bys3 admin]$ cat tnsnames.ora
hello =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = bys3.bys.com)(PORT =1529))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = hello)
    )
  )
在這個實驗中，local_listener='hello';這樣配置時，tnsnames.ora中 (SERVICE_NAME = hello)這一行是無效的，也就是不會應用這個服務名。。  sqlplus aa/bb@hello  這樣用本地名連線時，當然會用到了。

3.修改local_listener=的值為伺服器端的tnsnames.ora裡的本地名

SYS@ bys3>alter system set local_listener='hello';
System altered.
SYS@ bys3>show parameter local_l
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------

local_listener                       string      hello

注意注意：如果在配置好之後，tnsnames.ora裡內容發生了變化，hello =本地名被刪改了總之是找不到這個欄位，重啟資料庫會報錯：
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'aa'
但是如果資料庫未重啟，而刪除tnsnames.ora或hello =本地名的欄位是沒影響的，應該是資訊已經載入到記憶體了。

4.重啟監聽後檢視監聽狀態：  --這裡的服務名還是來自資料庫引數service_names

[oracle@bys3 admin]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 17-JAN-2014 13:53:14
Copyright (c) 1991, 2013, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=bys3.bys.com)(PORT=1529)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                17-JAN-2014 13:51:10
Uptime                    0 days 0 hr. 2 min. 4 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/diag/tnslsnr/bys3/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=bys3.bys.com)(PORT=1529)))
Services Summary...
Service "bys3" has 1 instance(s).
  Instance "bys3", status READY, has 1 handler(s) for this service...
The command completed successfully
#################

配置靜態監聽：

1.配置listener.ora，增加相應條目

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

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = bys3.bys.com)(PORT = 1525))
      (ADDRESS = (PROTOCOL = TCP)(HOST = bys3.bys.com)(PORT = 1521))
    )
  )

l3 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = bys3.bys.com)(PORT = 1526))
    )
  )
SID_LIST_l3=
  (SID_LIST=
   (SID_DESC=
   (GLOBAL_DBNAME=bys9)
    (SID_NAME=bys3)
    (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)))
ADR_BASE_LISTENER = /u01

2.啟動監聽，檢視狀態：

[oracle@bys3 ~]$ lsnrctl status l3
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 14-JAN-2014 22:19:49
Copyright (c) 1991, 2013, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=bys3.bys.com)(PORT=1526)))
STATUS of the LISTENER
------------------------
Alias                     l3
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                14-JAN-2014 22:15:15
Uptime                    0 days 0 hr. 4 min. 34 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/diag/tnslsnr/bys3/l3/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=bys3.bys.com)(PORT=1526)))
Services Summary...
Service "bys9" has 1 instance(s).
  Instance "bys3", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
#################

3.使用SQLPLUS進行登陸測試

[oracle@bys3 admin]$ cat tnsnames.ora
test9 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = bys3.bys.com)(PORT =1526))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = bys9)
        )
  )
[oracle@bys3 ~]$ tnsping test9
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 14-JAN-2014 22:37:56
Copyright (c) 1997, 2013, Oracle.  All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = bys3.bys.com)(PORT =1526))(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = bys9)))
OK (10 msec)
[oracle@bys3 ~]$ sqlplus bys/bys@test9
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 14 22:38:05 2014
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
BYS@ test9>