9、oracle网络配置

oracle网络配置：

1、监听的相关概念

路径：

$ORACLE_HOME/network/admin/listener.ora

$ORACLE_HOME/network/admin/sqlnet.ora

$ORACLE_HOME/network/admin/samples/tnsnames.ora

tnsping orcl 请求服务名

用户进程

名词解析(user process)----请求连接请求-----listener-服务进程(server process)

配置和管理 oracle NetWork工具

Enterprise Manager

Oracle Net Manager 命令行：netmgr

Oracle Net Configuration Assistant 命令行：netca

命令行： lsnrctl

netca:创建网络相关

dbca：创建数据库

asmca：asm创建

dbua：数据库升级

如果安装了grid 监听受到grid监管，要在grid环境下执行lsnrctl

没有安装grid的话直接在database环境下执行lsnrctl

lsnrctl status/start/stop;

LSNRCTL> help

The following operations are available

An asterisk (*) denotes a modifier or extended command:

start stop status

services version reload

save_config trace spawn

change_password quit exit

set* show*

查看监听具体信息(安装grid)：

LSNRCTL> status

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-2017 18:51:25

Uptime 4 days 0 hr. 19 min. 44 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /u01/app/oracle/product/11.2.0/grid/network/admin/listener.or

aListener Log File /u01/app/oracle/diag/tnslsnr/oel/listener/alert/log.xml

Listening Endpoints Summary...

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

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oel)(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",has 1 handler(s) for this service...

Service "orclXDB" has 1 instance(s).

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

The command completed successfully

LSNRCTL>

目前有 LISTENER 这个监听

多个监听，如何配置？

配置工具：

netmgr 或者 netca

我刚刚创建了2个监听aec及test，包括LISTENER就有3个监听

查看监听

lsnrctl status LISTENER; //1521

lsnrctl status aec; //1522

lsnrctl status test; //1523

启动监听

lsnrctl start aec;

lsnrctl start test;

配置信息你可以通过管理界面配置好之后学习一下：

vim /u01/app/oracle/product/11.2.0/grid/network/admin/listener.ora

ADR_BASE_AEC = /u01/app/oracle

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

)

(DESCRIPTION =

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

)

ADR_BASE_LISTENER = /u01/app/oracle

AEC =

(DESCRIPTION =

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

)

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON

TEST =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = oel)(PORT = 1523))

)

ADR_BASE_TEST = /u01/app/oracle

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_TEST=ON

2、管理监听

简单连接命名：tcp/ip连接字符串 (一般中小公司)

本地命名：本地配置文件

外部命名：使用受支持的非oracle命名服务

conn hr/hr@orcl

测试监听(简单连接名称解析)

tnsping hostname:1521/orcl

或者

tnsping 192.168.175.60:1521/listener

[oracle@oel ~]$ tnsping oel:1521/listener

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 28-MAY-2017 20:40:05

Used parameter files:

/u01/app/oracle/product/11.2.0/grid/network/admin/sqlnet.ora

Used EZCONNECT adapter to resolve the alias

Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=listener))(ADDRESS=(PROT

OCOL=TCP)(HOST=127.0.0.1)(PORT=1521)))OK (10 msec)

支持本地和目录命名：

tnsping listener

客户端配置工具：

netmgr

/u01/app/oracle/product/11.2.0/grid/network/admin/tnsnames.ora

ORCL =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVICE_NAME = orcl)

)

在配置一个

OAEC =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVICE_NAME = orcl)

)

测试：

sqlplus scott/tiger@orcl

sqlplus scott/tiger@oaec

如果 tnsnames.ora 什么都不写

那么客户端要连接手写比较麻烦

sqlplus scott/tiger@192.168.175.60:1521/orcl

3、服务器模式

共享服务器：在SGA中存放用户会话数据

调整SGA大小时，请考虑将共享服务器内存考虑在内。

设置共享服务器时，必须要设置 large_pool 大小(UGA)

什么时候设置呢？（餐馆服务人员太少）

不能设置共享服务器的情况：(下面操作情况的时间都好长！！！)

1、数据库管理

2、备份和恢复操作

3、批处理和批量加载

4、数据仓库操作

不允许长连接操作。

如何设置共享服务器呢？

共享服务器：

alter system set shared_servers=5;

alter system set shared_server_sessions=20;

show parameter dispatchers

select * from v$dispatcher;

alter system set dispatchers='(ADDRESS=(PROTOCOL=tcp)(HOST=172.25.0.10)(PORT=14597)(DISPATCHERS=5))';

alter system set local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.25.0.10)(PORT=7788)))';

共享服务器服务器端配置方法及参数讲解：

1、配置所有吃饭的人进来排成多少队等待被服务

dispatchers

alter system set dispatchers='(protocol=tcp)(dispatchers=3)';

2、配置多少个服务人提供服务

shared_servers

alter system set shared_servers=5;

当生意好的不能再好了，5个服务员不够用了，我还得增加几个，增加多少个到头呢？

max_shared_servers

alter system set max_shared_servers=20;

3、一楼大厅，散座便当可以做100人，二楼、3楼雅座可以做148人，

sessions 248

设置共享服务器和专用服务器并存

shared_server_sessions 100 共享模式

专门模式 248-100=148

##############################################################################

具体配置如下：

alter system set sessions=248 scope=spfile;

sql> show parameter sessions;

NAME TYPE VALUE

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

java_max_sessionspace_size integer 0

java_soft_sessionspace_limit integer 0

license_max_sessions integer 0

license_sessions_warning integer 0

sessions integer 248

shared_server_sessions integer

sql> alter system set dispatchers='(protocol=tcp)(dispatchers=3)';

System altered.

sql> show parameter dispatcher;

NAME TYPE VALUE

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

dispatchers string (protocol=tcp)(dispatchers=3)

max_dispatchers integer

sql> alter system set shared_servers=5;

System altered.

sql> show parameter shared_server;

NAME TYPE VALUE

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

max_shared_servers integer

shared_server_sessions integer

shared_servers integer 5

sql> alter system set max_shared_servers=20;

System altered.

sql> show parameter max_shared_servers;

NAME TYPE VALUE

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

max_shared_servers integer 20

sql> show parameter sessions;

NAME TYPE VALUE

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

java_max_sessionspace_size integer 0

java_soft_sessionspace_limit integer 0

license_max_sessions integer 0

license_sessions_warning integer 0

sessions integer 248

shared_server_sessions integer

sql> alter system set shared_server_sessions=100;

System altered.

sql> show parameter session;

NAME TYPE VALUE

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

java_max_sessionspace_size integer 0

java_soft_sessionspace_limit integer 0

license_max_sessions integer 0

license_sessions_warning integer 0

session_cached_cursors integer 50

session_max_open_files integer 10

sessions integer 248

shared_server_sessions integer 100

sql>

####################################################################################

客户端设置：

tnsnames.ora

共享设置：

orcl_sh =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVER = SHARED)

(SERVICE_NAME = orcl)

)

专有设置

orcl_sh =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl)

)

测试一下

tnsping orcl_sh

tnsping orcl_de

sqlplus scott/tiger@orcl_sh

sqlplus scott/tiger@orcl_de

查看当前有没有排队current

LSNRCTL> services

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))

Services Summary...

Service "+ASM" has 1 instance(s).

Instance "+ASM",has 1 handler(s) for this service...

Handler(s):

"DEDICATED" established:3 refused:0 state:ready

LOCAL SERVER

Service "orcl" has 1 instance(s).

Instance "orcl",has 4 handler(s) for this service...

Handler(s):

"D002" established:2 refused:0 current:3 max:1022 state:ready

DISPATCHER <machine: oel,pid: 2681>

(ADDRESS=(PROTOCOL=tcp)(HOST=oel)(PORT=18845))

"D001" established:3 refused:0 current:2 max:1022 state:ready

DISPATCHER <machine: oel,pid: 2677>

(ADDRESS=(PROTOCOL=tcp)(HOST=oel)(PORT=33604))

"D000" established:4 refused:0 current:1 max:1022 state:ready

DISPATCHER <machine: oel,pid: 5861>

(ADDRESS=(PROTOCOL=tcp)(HOST=oel)(PORT=45766))

"DEDICATED" established:1 refused:0 state:ready

LOCAL SERVER

Service "orclXDB" has 1 instance(s).

Instance "orcl",has 0 handler(s) for this service...

The command completed successfully

4、数据库连接

A数据库可以查询B数据库 dblink

9、oracle网络配置

猜你在找的Oracle相关文章