Oracle關於監聽器串連方面的總結

Oracle監聽器的各種問題總結：

1）系統斷電異常重啟後導致Oracle的監聽無法正常啟動，此時可以通過手動修改一下，使用靜態註冊監聽：
如：
# listener.ora Network Configuration File: /var/local/u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /home/u01/app/oracle/product/11.2.0/dbhome_2)
      (PROGRAM = extproc)
    )
以下為增加的內容
   (SID_DESC =
      (GOLBAL_DBNAME = orcl)
      (ORACLE_HOME = /home/u01/app/oracle/product/11.2.0/dbhome_2)
      (SID_NAME = orcl)
    )
  )
以上為增加的內容

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
  )

2）ora-3136
修改listener的inbound_connect_timeout參數的方法

方法一：
LSNRCTL> show inbound_connect_timeout

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
LISTENER parameter "inbound_connect_timeout" set to 60
The command completed successfully

LSNRCTL> set inbound_connect_timeout 0
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
LISTENER parameter "inbound_connect_timeout" set to 0
The command completed successfully

LSNRCTL> show inbound_connect_timeout
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
LISTENER parameter "inbound_connect_timeout" set to 0
The command completed successfully

LSNRCTL> set save_config_on_stop on   #表示修改參數永久生效，否則只是臨時生效，下次重啟監聽又還原為原來的值了

方法二：
修改listener.ora檔案，加入:  INBOUND_CONNECT_TIMEOUT_LISTENER_NAME=0

3）為了資料庫的安全，我們不僅可以限制遠程登陸作業系統的ip，也可以限制通過監聽串連資料庫伺服器的ip
在sqlnet.ora中增加如下配置 【幫客之家 http://www.bkjia.com 】
tcp.validnode_checking = yes
tcp.invited_nodes = (192.168.1.102,192.168.1.222,192.168.1.0/24)  #表示只允許192.168.1.102和192.168.1.222以及192.168.1.0、24網段的地址通過監聽串連資料庫
別的地址串連就會報如下錯誤
ERROR:
ORA-12537: TNS: 串連關閉

4）禁止通過作業系統認證串連資料庫（sqlplus / as sysdba）
在sqlnet.ora中加入如下內容：
SQLNET.AUTHENTICATION_SERVICES=NONE
或者SQLNET.AUTHENTICATION_SERVICES=(NTS)
此時使用sqlplus / as sysdba登入，就會報 ORA-01031: 許可權不足錯誤

5）本人線上安全的監聽服務組態檔如下：
--cat sqlnet.ora
# sqlnet.ora Network Configuration File: /home/faxc/app/faxc/product/11.2.0/dbhome_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
SQLNET.INBOUND_CONNECT_TIMEOUT = 0
SQLNET.RECV_TIMEOUT = 30
SQLNET.SEND_TIMEOUT = 30
DIAG_ADR_ENABLED = OFF
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
tcp.validnode_checking = yes
tcp.invited_nodes = (192.168.1.102,192.168.1.222,192.168.1.0/24)
ADR_BASE = /home/faxc/app/faxc

--cat listener.ora
# listener.ora Network Configuration File: /home/faxc/app/faxc/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /home/faxc/app/faxc/product/11.2.0/dbhome_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GOLBAL_DBNAME = orcl)
      (ORACLE_HOME = /home/faxc/app/faxc/product/11.2.0/dbhome_1)
      (SID_NAME = orcl)
    )
  )
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.222)(PORT = 1521))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
      )
    )
  )

ADR_BASE_LISTENER = /home/faxc/app/faxc

--cat tnsnames.ora
ORCL=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.222)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )
200=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.200)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

6）如果允許的話，可以通過iptables禁止1521連接埠對外訪問，只需本機通過1521連接埠訪問即可

-A RH-Firewall-1-INPUT -s 127.0.0.1 -p tcp -m state --state NEW -m tcp --dport 1521 -j ACCEPT  #只允許本機通過1521連接埠訪問
-A RH-Firewall-1-INPUT -p tcp -m state --state NEW -m tcp --dport 1521 -j ACCEPT  #允許外網和本機通過1521連接埠訪問。