程序博客网 > js 工作台布局

oracle监听的静态注册，与动态注册理解

来源：互联网 发布：js 工作台布局 编辑：程序博客网 时间：2024/05/16 08:13

                                  oracle监听的静态注册，与动态注册理解

 

监听动态注册时的 listener.ora的配置如下：（注意观察 SID_LIST_LISTENER ）
[oracle@localhost 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 ）
如下所示：
[ora11@localhost admin]$ lsnrctl status

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

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

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/oracle@lixora
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 的状态


这时我们再来查看服务端的监听状态：
[ora11@localhost admin]$ lsnrctl status

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

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

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 和动态注册时的配置异同：
[oracle@oracle11g 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

 

首先，我们先确定下监听的状态：
[oracle@oracle11g admin]$ lsnrctl

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

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

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  这里和前面我们知道的结果一模一样。


-----然后我们把数据库关掉
[root@oracle11g ~]# sh oracle.sh
SQL*Plus: Release 11.2.0.1.0 Production on Wed Nov 20 10:17:41 2013
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
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) 2009 Microsoft Corporation。保留所有权利。
C:\Users\Administrator>sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on 星期三 11月 20 23:24:42 2013
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> conn system/oracle@mynewdb 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/oracle@mynewdb 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>


至此，关于静态注册，动态注册的对比和实验结束。


 

js 工作台布局 js 工作台布局
原创粉丝点击
热门IT博客
mac canadamac canada
淘宝买电脑怎么分期淘宝买电脑怎么分期
linux sh 定义变量
centos smtp server
网络机票分销平台
excel数据分类统计分析
怎么做淘宝模版
中石化发布页岩气数据
js判断ie浏览器
全球网络电视直播
同步轮选型软件
iphone接电话变声软件
淘宝特种经营许可在那
sql insert语句怎么写
草图大师软件图标
香薰睡眠知乎
mac图片处理软件c#
rt809f编程器驱动下载
2017甘肃省网络研修
java web 在线考试
热门问题 老师的惩罚 人脸识别 我在镇武司摸鱼那些年 重生之率土为王 我在大康的咸鱼生活 盘龙之生命进化 天生仙种 凡人之先天五行 春回大明朝 姑娘不必设防，我是瞎子 稻花香酒 粉酒 芬酒 粉酒价格 酚酒 分酒 山西杏花村酒价格表 泥坑酒 600809 纷酒价格 十年陈酿 杏花村老酒价格表 杏花村白酒 杏花村酒价格表 杏花村酒价格表图片 山西杏花村 杏花村酒 杏花村酒价格表大全 52度杏花村酒价格表 53度杏花村酒价格表 杏花村酒价格表52度浓香型 宁夏红枸杞酒 老窖 红米酒 红星二锅头酒 老窖特曲 汾阳 郭汾阳 汾阳市 汾阳王 汾阳贾家庄 太原到汾阳 汾阳王酒 汾阳学院 汾阳医院 汾阳信息网 汾阳邮编 汾阳信息 山西汾阳 汾阳王白酒 汾阳花店

程序博客网，程序员的互联网技术博客家园。csdn论坛精品 msdn技术资料都在这里