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　查看表约束，列约束

查看索引信息