oracle監聽動態註冊與靜態註冊

client端如果想要連線到遠端的資料庫伺服器，首先資料庫伺服器必須啟動監聽器

oracle監聽器的配置在$ORACLE_HOME/network/admin/listener.ora,開啟這個檔案，我們看到配置如下：

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0.1)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
  )
動態註冊：oracle instance的pmon程式會在instance啟動之後自動將instance_name和service_name註冊到1521埠中，我們只需要在listener中修改配置如下，即可實現
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.102)(PORT = 1521))
    )
  )
注意我們不用修改SID_LIST_LISTENER中的配置

我們首先啟動監聽器 lsnrctl
LSNRCTL> start
Starting /u01/app/oracle/product/10.2.0.1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/10.2.0.1/network/admin/listener.ora
Log messages written to /u01/app/oracle/product/10.2.0.1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.102)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date                22-FEB-2012 07:40:42
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/10.2.0.1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/10.2.0.1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.102)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
由於我們沒有啟動instance，所以沒有instance的註冊資訊

啟動oracle例項
[oracle@localhost ~]$ export ORACLE_SID=prod
[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Feb 22 07:42:49 2012

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  180355072 bytes
Fixed Size                  1218364 bytes
Variable Size              88082628 bytes
Database Buffers           83886080 bytes
Redo Buffers                7168000 bytes
Database mounted.
Database opened.
prod instance已經啟動，稍等一會我們檢視監聽器狀態
LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date                22-FEB-2012 07:40:42
Uptime                    0 days 0 hr. 3 min. 15 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/10.2.0.1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/10.2.0.1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.102)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "prod" has 1 instance(s).
  Instance "prod", status READY, has 1 handler(s) for this service...
Service "prod_XPT" has 1 instance(s).
  Instance "prod", status READY, has 1 handler(s) for this service...
The command completed successfully
發現prod服務已經被動態註冊進去，其中status為READY說明動態註冊成功

靜態註冊：由於oracle的pmon程式會自動將instance的instance_name和service_name註冊到1521埠，如果我們想把instance 註冊到1521之外的埠，我們只能使用靜態註冊
下面我們修改listener.ora配置如下：
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0.1)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
  )
staticReg =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.102)(PORT = 1522))
    )
  )
SID_LIST_staticReg =
    (SID_LIST =
    (SID_DESC =
        (GLOBAL_DBNAME= prod)
        (ORACLE_HOME = /u01/app/oracle/product/10.2.0.1)
        (SID_NAME = prod)
      )
)
我們首先增加了監聽配置：
staticReg =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.102)(PORT = 1522))
    )
  )
不同於動態註冊，我們必須指定listenername,這裡為staticReg
然後增加了SID_LIST關於staticReg的指定配置
SID_LIST_staticReg =
    (SID_LIST =
    (SID_DESC =
        (GLOBAL_DBNAME= prod)
        (ORACLE_HOME = /u01/app/oracle/product/10.2.0.1)
        (SID_NAME = prod)
      )
)

由於是非標準埠號，我們必須指定SID_LIST配置
儲存之後，啟動監聽：
[oracle@localhost admin]$ lsnrctl

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 22-FEB-2012 07:51:03

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

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> start
Starting /u01/app/oracle/product/10.2.0.1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/10.2.0.1/network/admin/listener.ora
Log messages written to /u01/app/oracle/product/10.2.0.1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date                22-FEB-2012 07:51:06
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/10.2.0.1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/10.2.0.1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
之後啟動instance prod
[oracle@localhost ~]$ export ORACLE_SID=prod
[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Feb 22 07:42:49 2012

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  180355072 bytes
Fixed Size                  1218364 bytes
Variable Size              88082628 bytes
Database Buffers           83886080 bytes
Redo Buffers                7168000 bytes
Database mounted.
Database opened.

無論我們等待多久，檢視狀態都是下面顯示的內容
LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date                22-FEB-2012 07:51:06
Uptime                    0 days 0 hr. 1 min. 37 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/10.2.0.1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/10.2.0.1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

由於我們採用的監聽埠是1522，pmon程式是不會自動註冊是1521之外的埠
我們需要啟動1522埠的監聽
LSNRCTL> start staticReg
Starting /u01/app/oracle/product/10.2.0.1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/10.2.0.1/network/admin/listener.ora
Log messages written to /u01/app/oracle/product/10.2.0.1/network/log/staticreg.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.102)(PORT=1522)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.102)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     staticReg
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date                22-FEB-2012 07:56:18
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/10.2.0.1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/10.2.0.1/network/log/staticreg.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.102)(PORT=1522)))
Services Summary...
Service "prod" has 1 instance(s).
  Instance "prod", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
由於prod採用的是靜態註冊，所以狀態永遠都是UNKNOW
等一會，我們就可以直接採用sqlplus連線，當然客戶端必須有對應的tnsname的配置
C:\Users\lei_dong>sqlplus sys/prod@port1522 as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on 星期三 2月 22 23:58:06 2012

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

連線到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL>
連線成功，

最後我們需要在client端配置tnsname,這時無論啟動的是1521還是其他埠的監聽，差別都不大，基本上只有埠號的差異

linux =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.102)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = prod)
    )
  )
 
port1522 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.102)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = prod)
    )
  )