oracle監聽的靜态注冊，與動态注冊了解

監聽動态注冊時的 listener.ora的配置如下：（注意觀察 SID_LIST_LISTENER ）

[[email protected] bdump]$ more /oracle/network/admin/listener.ora

# listener.ora Network Configuration File: /oracle/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = /oracle)

(PROGRAM = extproc)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

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

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

)

監聽動态注冊時，監聽知道執行個體的具體狀态，是以在監聽啟動之初，其狀态顯示為（ status READY ）

如下所示：

[[email protected] admin]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 20-NOV-2013 21:27:48

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

STATUS of the LISTENER

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

Alias LISTENER

Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production

Start Date 23-SEP-2013 13:30:38

Uptime 58 days 8 hr. 57 min. 9 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /oracle/network/admin/listener.ora

Listener Log File /oracle/network/log/listener.log

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(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 "lixora" has 1 instance(s).

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

Service "lixoraXDB" has 1 instance(s).

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

Service "lixora_XPT" has 1 instance(s).

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

Service "ora11g" has 1 instance(s).

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

Service "ora11gXDB" has 1 instance(s).

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

The command completed successfully

---這時關閉 lixora 資料庫

SQL> shutdown abort

ORACLE instance shut down.

SQL>

---我們測試在用戶端連接配接

SQL> conn system/[email protected]

ERROR:

ORA-03113: end-of-file on communication channel

ERROR:

ORA-12514: TNS:listener does not currently know of service requested in connect

descriptor

Warning: You are no longer connected to ORACLE.

如上報錯資訊很明顯監聽無法獲得（lixora）service 的狀态

這時我們再來檢視服務端的監聽狀态：

[[email protected] admin]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 20-NOV-2013 21:33:16

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

STATUS of the LISTENER

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

Alias LISTENER

Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production

Start Date 23-SEP-2013 13:30:38

Uptime 58 days 9 hr. 2 min. 38 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /oracle/network/admin/listener.ora

Listener Log File /oracle/network/log/listener.log

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(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 "ora11g" has 1 instance(s).

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

Service "ora11gXDB" has 1 instance(s).

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

The command completed successfully

哈，service lixora 已經消失了，因為動态注冊是由pmon 來完成的，oracle 的執行個體一旦關閉，那麼pmon 等程序也就随之關閉了，自然而然就不能動态的将執行個體服務注冊到監聽了。

但是，如果我們使用靜态注冊結果就截然相反了，即使資料庫執行個體已關閉，具有sysdba 權限的使用者仍然可以通過監聽遠端連接配接到資料庫中進行維護操作。

那麼我們來測試下靜态注冊的效果如何？

以下為一個靜态注冊時監聽的配置檔案，注意觀察 SID_LIST_LISTENER 和動态注冊時的配置異同：

[[email protected] admin]$ vi /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/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.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME =/u01/app/oracle/product/11.2.0/dbhome_1)

(PROGRAM = extproc)

)

(SID_DESC=

(GLOBAL_DBNAME = mynewdb)

(ORALCE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)

(SID_NAME = mynewdb)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

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

)

ADR_BASE_LISTENER = /u01/app/oracle

首先，我們先确定下監聽的狀态：

[[email protected] admin]$ lsnrctl

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 20-NOV-2013 10:17:25

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> status

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

STATUS of the LISTENER

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

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production

Start Date 19-NOV-2013 02:19:24

Uptime 1 days 7 hr. 58 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/app/oracle/diag/tnslsnr/oracle11g/listener/alert/log.xml

Listening Endpoints Summary...

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

Services Summary...

Service "PLSExtProc" has 1 instance(s).

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

Service "mynewdb" has 1 instance(s).

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

The command completed successfully

這裡可以看到 Instance "mynewdb", status UNKNOWN 這裡和前面我們知道的結果一模一樣。

-----然後我們把資料庫關掉

[[email protected] ~]# sh oracle.sh

SQL*Plus: Release 11.2.0.1.0 Production on Wed Nov 20 10:17:41 2013

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown abort

ORACLE instance shut down.

SQL>

ok，資料庫已經成功關閉，這時我們再來看下監聽的狀态：

LSNRCTL> status

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

STATUS of the LISTENER

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

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production

Start Date 19-NOV-2013 02:19:24

Uptime 1 days 8 hr. 3 min. 9 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/app/oracle/diag/tnslsnr/oracle11g/listener/alert/log.xml

Listening Endpoints Summary...

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

Services Summary...

Service "PLSExtProc" has 1 instance(s).

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

Service "mynewdb" has 1 instance(s).

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

The command completed successfully

LSNRCTL>

這裡可以看到，資料庫雖然關閉了，但是相應的Service "mynewdb“ 還在的，status UNKNOWN 狀态依然為未知。

為了驗證，在靜态注冊下具有sysdba 權限使用者仍然可以通過監聽遠端連接配接到資料庫中進行維護操作。

這裡我将通過，自己的windows上oracle 10g 用戶端遠端連接配接到 oel5 oracle11g的資料庫中來啟動之前關的執行個體。

在windows的10g 用戶端上配置好 tnsnames.ora

然後測試如下：

Microsoft Windows [版本 6.1.7601]

C:\Users\Administrator>sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on 星期三 11月 20 23:24:42 2013

SQL> conn system/[email protected] as sysdba

ERROR:

ORA-01031: insufficient privileges

------這裡報了一個錯，很明顯system 有 dba 的role，但是（The DBA role does not include the SYSDBA or SYSOPER system privileges）沒有sysdba的系統權限

SQL> conn sys/[email protected] as sysdba

已連接配接到空閑例程。

SQL> select * from v$instance;

select * from v$instance

第 1 行出現錯誤:

ORA-01034: ORACLE not available

SQL> startup

ORACLE 例程已經啟動。

Total System Global Area 234344448 bytes

Fixed Size 1335696 bytes

Variable Size 146804336 bytes

Database Buffers 83886080 bytes

Redo Buffers 2318336 bytes

資料庫裝載完畢。

資料庫已經打開。

SQL>

至此，關于靜态注冊，動态注冊的對比和實驗結束。

oracle監聽的靜态注冊，與動态注冊了解

繼續閱讀

oracle 9i 10g 11g 更新路線圖

oracle 11.2.0.3 隐含參數

Oracle AFD 18c Supported Platforms

oracle　檢視表限制，列限制

檢視索引資訊