Oracle-28-网络管理

于 2018-07-19 12:53:10 发布
阅读量487 收藏
点赞数
分类专栏： Oracle 文章标签： Oracle-网络配置
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/Paul_George/article/details/81106619
版权

在配置网络前，必须保证访问数据库的client端与Oracle数据库的Server端网络连接通常。

 

专用连接

一、实例的动态注册

由于测试环境监听是配置在grid用户下，因此先切换用户到grid下

su - grid

查看服务器端监听程序的状态

lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 24-MAY-2018 15:35:27

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 2: No such file or directory
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused

我们的监听程序是启动了的，如果监听程序没有启动，可以执行以下命令启动监听程序

lsnrctl start

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 24-MAY-2018 15:36:03

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

Starting /u01/app/grid/product/11.2.0/grid/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /u01/app/grid/product/11.2.0/grid/network/admin/listener.ora
Log messages written to /u01/app/grid/diag/tnslsnr/RHEL6/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                24-MAY-2018 15:36:03
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/grid/product/11.2.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/RHEL6/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
The listener supports no services
The command completed successfully

我们用oracle用户登录数据库，看下服务器端的监听服务是否启动

su - oracle
sqlplus / as sysdba

做一个注册的动作

alter system register;

 

在grid用户一端查看监听服务

lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 24-MAY-2018 15:31:59

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                24-MAY-2018 15:09:36
Uptime                    0 days 0 hr. 22 min. 22 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/grid/product/11.2.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/RHEL6/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...
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...
The command completed successfully

接下来我们用Windows客户端访问监听程序

开始->运行->cmd

sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on 星期三 7月 18 22:50:10 2018

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

SQL>
conn scott/scott@2.2.2.2:1521/orcl
已连接。
SQL>

这种通过 conn scott/scott@2.2.2.2:1521:orcl访问数据库的简单连接在数据库关闭后是无法访问数据库的

Microsoft Windows [版本 10.0.17134.48]
(c) 2018 Microsoft Corporation。保留所有权利。

C:\Users\George>sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on 星期四 7月 19 11:23:59 2018

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

SQL> conn sys/sys@2.2.2.2:1521/orcl as sysdba
已连接。
SQL> shut immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORA-12514: TNS: 监听程序当前无法识别连接描述符中请求的服务
SQL>

当我们以动态注册的方式对远程访问进行服务时，一旦实例消失，那么也就无法访问到数据库

为此我们可以对服务器端进行监听的静态注册

二、监听的静态注册

在数据库服务器图形界面启动命令行，执行

netmgr

接下来我们重启监听程序

[grid@RHEL6 ~]$ lsnrctl stop

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 25-MAY-2018 04:06:12

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
The command completed successfully
[grid@RHEL6 ~]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 25-MAY-2018 04:08:58

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

Starting /u01/app/grid/product/11.2.0/grid/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /u01/app/grid/product/11.2.0/grid/network/admin/listener.ora
Log messages written to /u01/app/grid/diag/tnslsnr/RHEL6/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                25-MAY-2018 04:08:58
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/grid/product/11.2.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/RHEL6/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
Services Summary...
Service "orcl" has 1 instance(s).
  Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

接下来我们再用windows模拟远程连接

C:\Users\George>sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on 星期四 7月 19 11:51:44 2018

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

SQL> conn sys/sys@2.2.2.2:1521/sundb as sysdba
已连接到空闲例程。
SQL> startup
ORACLE 例程已经启动。

Total System Global Area  939495424 bytes
Fixed Size                  2218952 bytes
Variable Size             624952376 bytes
Database Buffers          306184192 bytes
Redo Buffers                6139904 bytes
数据库装载完毕。
数据库已经打开。
SQL>

 

在客户端，我们可以配置tnsnames.ora，这样我们在连接数据库的时候就不用写主机名：端口号/服务名了

例如，在windows主机上找到oracle客户端安装目录下的tnsnames.ora

比如：D:\app\George\product\11.2.0\dbhome_1\NETWORK\ADMIN

加入如下内容

orcl =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 2.2.2.2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

之后我们在windows命令行下执行

C:\Users\George>sqlplus scott/scott@orcl

SQL*Plus: Release 11.2.0.1.0 Production on 星期四 7月 19 12:00:29 2018

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL>

以上是专用连接，一台主机一个连接，接下来我们看共享连接配置

 

在服务器端打开共享服务进程，首先查看当前数据库共享服务器配置


SYS@ orcl>show parameter shared

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address             integer     0
max_shared_servers                   integer
shared_memory_address                integer     0
shared_pool_reserved_size            big integer 8808038
shared_pool_size                     big integer 0
shared_server_sessions               integer
shared_servers                       integer     1
SYS@ orcl>alter system set shared_server_sessions=5;

System altered.

Elapsed: 00:00:00.06
SYS@ orcl>show parameter dispatchers

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dispatchers                          string      (PROTOCOL=TCP) (SERVICE=sundbX
                                                 DB)
max_dispatchers                      integer
SYS@ sundb>select * from v$dispatcher;

NAME
----
NETWORK
------------------------------------------------------------------------------------------------------------------------
PADDR            STATUS           ACC   MESSAGES      BYTES     BREAKS      OWNED    CREATED       IDLE       BUSY
---------------- ---------------- --- ---------- ---------- ---------- ---------- ---------- ---------- ----------
  LISTENER  CONF_INDX
---------- ----------
D000
(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=12741))
000000009788C010 WAIT             YES          0          0          0          0          0      98251     0
         0          0


Elapsed: 00:00:00.01
SYS@ sundb>alter system set dispatchers='(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=12741)(DISPATCHERS=2))';

System altered.

Elapsed: 00:00:00.03
SYS@ orcl>show parameter local

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string
log_archive_local_first              boolean     TRUE
parallel_force_local                 boolean     FALSE
SYS@ orcl>alter system set local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))';

System altered.

Elapsed: 00:00:00.02

接下来我们在windows下的客户端配置tnsnames.ora配置服务器类型，如下

orcl =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 2.2.2.2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = SHARED)
      (SERVICE_NAME = orcl)
    )
  )

我们重启下数据库

startup force

在windows下登录

C:\Users\George>sqlplus scott/scott@orcl

SQL*Plus: Release 11.2.0.1.0 Production on 星期四 7月 19 12:42:07 2018

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL>
SYS@ sundb>select username,server from v$session where username='SCOTT';

USERNAME                       SERVER
------------------------------ ---------
SCOTT                          NONE

Elapsed: 00:00:00.01

我们再连接一个scott用户

[oracle@RHEL6 ~]$ sqlplus scott/scott

SQL*Plus: Release 11.2.0.1.0 Production on Fri May 25 04:56:07 2018

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SCOTT@ orcl>

执行更新操作，先在server端做，后在client端做

SCOTT@ sundb>update e01 set sal=1;

14 rows updated.

Elapsed: 00:00:00.03
SQL> update e01 set sal =1 ;

在查看连接状态

SYS@ sundb>select username,server from v$session where username='SCOTT';

USERNAME                       SERVER
------------------------------ ---------
SCOTT                          SHARED
SCOTT                          DEDICATED

Elapsed: 00:00:00.00

专用模式中服务器进程和客户端进程是一对一的，共享模式中一个服务器进程会服务多个客户端进程。

 

 

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值