Oracle監聽器的靜态注冊與動态注冊

這次實驗所用的資料庫的DB_NAME=orcl，SID=orcl

一、靜态注冊

靜态注冊的好處在于在資料庫沒有啟動的時候，也能通過注冊服務遠端啟動資料庫。

配置示例：

listener.ora檔案：

[[email protected] ~]$ cat $ORACLE_HOME/network/admin/listener.ora

# listener.ora Network Configuration File: /oracle/product/10.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 = /oracle/product/10.2.0/db_1)

(PROGRAM = extproc)

)

#靜态注冊，增加SID_DESC清單，GLOBAL_DBNAME參數可選

(SID_DESC =

#(GLOBAL_DBNAME = orcl_pd)

(ORACLE_HOME = /oracle/product/10.2.0/db_1)

(SID_NAME = orcl)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.147)(PORT = 1521))

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

)

運作lsnrctl status，看到如下傳回值：

[[email protected] ~]$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 17-JUN-2014 10:29:35

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.147)(PORT=1521)))

STATUS of the LISTENER

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

Alias LISTENER

Version TNSLSNR for Linux: Version 10.2.0.4.0 - Production

Start Date 17-JUN-2014 10:27:42

Uptime 0 days 0 hr. 1 min. 53 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /oracle/product/10.2.0/db_1/network/admin/listener.ora

Listener Log File /oracle/product/10.2.0/db_1/network/log/listener.log

Listening Endpoints Summary...

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

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

Services Summary...

Service "PLSExtProc" has 1 instance(s).

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

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...

Service "orcl_XPT" has 1 instance(s).

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

The command completed successfully

這裡的，狀态UNKOWN即表明為靜态注冊，不保證能連接配接資料庫。

tnsnames.ora檔案：

[[email protected] ~]$ cat $ORACLE_HOME/network/admin/tnsnames.ora

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

# Generated by Oracle configuration tools.

ORCL =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.147)(PORT = 1521))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl)

)

這裡的ORCL為服務命名。可以為任意值，在用戶端連接配接伺服器時，填寫的主機字元串，即為此服務命名ORCL。

注意：

1、修改監聽配置後可以不重新開機監聽，直接執行lsnrctl reload生效。

2、靜态注冊監聽，用戶端在配置tnsnames.ora服務命名時，“（Oracle 8i或更高版本）服務名”裡填寫内容要與服務端靜态注冊監聽器時的全局資料庫名一緻。

3、配置監聽時，可以設定全局資料庫名，GLOBAL_DBNAME 可以不等于參數檔案中的全局資料庫名（資料庫名+資料庫域）。

二、動态注冊

1）、預設的動态注冊

pmon在資料庫啟動到nomount、mount或open時，動态從參數檔案中讀取service_names值；

并且在資料庫運作時，pmon會每隔一段時間進行動态注冊。

service_names 預設為全局資料庫名（資料庫名+資料庫域），可以通過alter system set service_names='A,B,C'設定多個。

[[email protected] ~]$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 17-JUN-2014 10:29:35

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.147)(PORT=1521)))

STATUS of the LISTENER

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

Alias LISTENER

Version TNSLSNR for Linux: Version 10.2.0.4.0 - Production

Start Date 17-JUN-2014 10:27:42

Uptime 0 days 0 hr. 1 min. 53 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /oracle/product/10.2.0/db_1/network/admin/listener.ora

Listener Log File /oracle/product/10.2.0/db_1/network/log/listener.log

Listening Endpoints Summary...

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

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

Services Summary...

Service "PLSExtProc" has 1 instance(s).

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

Service "eas" has 1 instance(s).

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

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...

Service "orcl_XPT" has 1 instance(s).

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

The command completed successfully

狀态為READY的記錄，表明為動态注冊（由PMON程序自動從參數檔案擷取）。

注意：

1、不管參數service_names為何值，pmon都會自動以全局資料庫名(這裡為orcl)為服務名動态注冊。

2、資料庫啟動到nomount、mount狀态時，動态注冊的服務所對應的執行個體狀态為BLOCKED。

2）、自定義端口的動态注冊監聽

若要啟用非預設端口的動态注冊監聽，必須設定local_listener參數。

步驟如下：

a）新增監聽器

方法一：運作netmgr，配置監聽程式，監聽端口為1522（非預設端口），儲存配置

方法二：直接編輯監聽檔案，增加以下内容

LISTENER1 =

(DESCRIPTION_LIST =

(DESCRIPTION =

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

)

b）指定監聽參數

方法一：直接通過修改local_listener參數指定

SQL>alter system set LOCAL_LISTENER='(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.147)(PORT=1522))';

System altered

SQL>alter system register

System altered

方法二：在Oracle伺服器端檔案$ORACLE_HOME/network/admin/tnsnames.ora填入如下内容

mytest =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

SQL>alter system set local_listener=mytest;

System altered

SQL>alter system register;

3)、檢視監聽器狀态

[[email protected] ~]$ lsnrctl status listener1

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 17-JUN-2014 11:45:10

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

STATUS of the LISTENER

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

Alias listener1

Version TNSLSNR for Linux: Version 10.2.0.4.0 - Production

Start Date 17-JUN-2014 11:41:54

Uptime 0 days 0 hr. 3 min. 15 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /oracle/product/10.2.0/db_1/network/admin/listener.ora

Listener Log File /oracle/product/10.2.0/db_1/network/log/listener1.log

Listening Endpoints Summary...

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

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...

Service "orcl_XPT" has 1 instance(s).

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

The command completed successfully

三、當你輸入sqlplus sys/[email protected]的時候，執行過程如下

1. 查詢sqlnet.ora看看名稱的解析方式，發現是TNSNAME

2. 則查詢tnsnames.ora檔案，從裡邊找orcl的記錄，并且找到主機名，端口和service_name

3. 如果listener程序沒有問題的話，建立與listener程序的連接配接。

4. 根據不同的伺服器模式如專用伺服器模式或者共享伺服器模式，listener采取接下去的動作。預設是專用伺服器模式，沒有問題的話用戶端就連接配接上了資料庫的server process。

5. 這時候網絡連接配接已經建立，listener程序的曆史使命也就完成了。

四、幾種連接配接用到的指令形式

1.sqlplus / as sysdba 這是典型的作業系統認證，不需要listener程序

2.sqlplus sys/oracle 這種連接配接方式隻能連接配接本機資料庫，同樣不需要listener程序

3.sqlplus sys/[email protected] 這種方式需要listener程序處于可用狀态。最普遍的通過網絡連接配接。

備注：可以使用具有sysdba權限的使用者本地作業系統認證啟動資料庫和遠端服務連接配接啟動資料庫。

Oracle監聽器的靜态注冊與動态注冊

繼續閱讀

快速删除ORACLE重複記錄

oracle海量資料表删除重複記錄

[引]在Oracle中如何利用Rowid查找和删除表中的重複記錄

oralce與mysql中，如何删除重複記錄

查詢表中重複記錄

ORACLE 表壓縮

oracle中的start with connect by用法

使用JDeveloper Remote debug PLSQL程式

LoadRunner 的多種Vuser類型

性能測試（并發負載壓力）測試分析－簡要篇

賦予使用者dba權限--解釋以上：

Oracle的基本操作

為什麼要選擇UniDAC

windows不能在本地計算機上運作oracleDbConsoleorcl

SQL語言基礎：常用的資料查詢語句

Oracle 批量查詢傳入List 傳回List