【数据库】ORACLE网络

一起养成写作习惯！这是我参与「掘金日新计划 · 4 月更文挑战」的第2天，点击查看活动详情。

一、基本概念

二、配置文件

• tnsnames.ora：Net service names in the tnsnames.ora file

• listener.ora：Listeners in the listener.ora file

• sqlnet.ora：Profile in the sqlnet.ora file

• 所在位置：$ORACLE_HOME/network/admin

• 他们之间的服务配置对应关系
  

• 基本查看命令

SQL> select instance_name from  V$instance;
INSTANCE_NAME
----------------
CDB1

SQL> show parameter service_name;
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
service_names                 string     CDB1.us.oracle.com

2.1 listener.ora

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = odd.us.oracle.com)(PORT = 1521))
  )

ADR_BASE_LISTENER = /u01/app/oracle

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = **Oracle8**)
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0.2/db_1)
      (SID_NAME = **CDB1.us.oracle.com**)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = **Oracle9**)
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0.2/db_1)
      (SID_NAME = **CDB1**)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = **Oracle10.odd.oracle.com**)
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0.2/db_1)
      (SID_NAME = **CDB1**)
    )
   )
处理客户端连接请求时，将GLOBAL_DBNAME的值与客户端连接描述符中的 SERVICE_NAME 参数的值相匹配

lsnrctl status

Services Summary...
Service "CDB1.us.oracle.com" has 1 instance(s).
  Instance "CDB1", status READY, has 5 handler(s) for this service...
Service "Oracle10.odd.oracle.com" has 1 instance(s).
  Instance "CDB1", status UNKNOWN, has 1 handler(s) for this service...
Service "Oracle8" has 1 instance(s).
 Instance "CDB1.us.oracle.com", status UNKNOWN, has 1 handler(s) for this service...
Service "Oracle9" has 1 instance(s).
  Instance "CDB1", status UNKNOWN, has 1 handler(s) for this service...

alter system set local_listener=lsnr2;
可设置数据库指向非1521端口的监听器，pmon默认将数据库注册到1521的监听器。如新建一个lsnr2是1526，将listener加到tns中，然后设置执行该命令 ，则默认指向lsnr2监听器

alter system set service_name = name1,name2;
设置数据库对外提供的service_name

2.1.1 配置PDB service name

不能直接配置service_names，需要使用dbms_service包

SQL> exec dbms_service.create_service('pdb1_extra','pdb1_extra.us.oracle.com'); 

SQL> select service_id, name, network_name, pdb, con_id from cdb_services order by con_id; 
SERVICE_ID NAME NETWORK_NAME PDB CON_ID 
_____________ _____________________ _____________________
6 pdb1.us.oracle.com pdb1.us.oracle.com PDB1 3 
1 pdb1_extra pdb1_extra.us.oracle.com PDB1 3

SQL> exec dbms_service.start_service('pdb1_extra');

2.1.2 配置RAC单节点连接，但仍然实现高可用（用来减少GC等待）

ser1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.187.22.152)(PORT = 1522))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.187.22.154)(PORT = 1522))
    (LOAD_BALANCE = OFF)
    (FAILOVER = ON)
  )
    (CONNECT_DATA =
      (SERVICE_NAME = xzyydb_ser1)
      (FAILOVER_MODE =
        (TYPE = select)
        (METHOD = basic)
        (RETRIES = 60)
        (DELAY = 3)
      )
    )
  )

2.2 tnsnames.ora

test =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = odd.us.oracle.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = CDB1.us.oracle.com)
      (server=shared)
    )
  )

test2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = odd.us.oracle.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = Oracle9)
    )
  )

2.3sqlnet.ora

sqlnet.ora内容举例

#sqlnet.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/sqlnet.ora
#Generated by Oracle configuration tools.
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
NAMES.DEFAULT_DOMAIN = odd.oracle.com
ADR_BASE = /u01/app/oracle

--NAMES.DIRECTORY_PATH：支持的naming method，顺序有影响
--请求时会自动加上NAMES.DEFAULT_DOMAIN配置的后缀，如sqlplus system/oracle@PROD1,其实请求的是PROD1.odd.oracle.com,如果是sqlplus system/oracle@PROD1.  （有个点）表示不加后缀

2.3.1 naming methods

Oracle Net provides the naming methods:

• Local naming (TNSNAMES)
• Directory naming (LDAP)
• Host naming (HOSTNAME)
• Easy connect naming (EZCONNECT)
• Network Information Service (NIS) 配置连接方式：netmgr或者vim sqlnet.ora

（1）easy connect
easy connect不需要tnsnames.ora,它连接的service_name取的是listener中的服务名

连接符语法：
(DESCRIPTION=
  (ADDRESS=(PROTOCOL=tcp)(HOST=host)(PORT=port))
  (CONNECT_DATA=
    (SERVICE_NAME=service_name)
    (SERVER=server)
    (INSTANCE_NAME=instance_name)))


例子：
(DESCRIPTION=
  (ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521))
  (CONNECT_DATA=
    (SERVICE_NAME=sales.us.example.com)))


Easy Connect Strings
CONNECT scott@sales-server:1521/sales.us.example.com
CONNECT scott@//sales-server/sales.us.example.com
CONNECT scott@//sales-server.us.example.com/sales.us.example.com

1、启动sqlplus，但不连接任何数据库（这种模式下用connect命令可以用easy connect方式）
sqlplus /nolog

2、执行连接
SQL>connect username/password@host[:port][/service_name][:server][/instance_name]
Server:指定要使用的数据库服务器类型dedicated、shared、pooled. 结果：

connect sys/oracle@odd.us.oracle.com:1521/Oracle9 as sysdba--成功
connect sys/oracle@odd.us.oracle.com:1521/Oracle10.odd.oracle.com as sysdba--成功

connect sys/oracle@odd.us.oracle.com:1521/Oracle8 as sysdba
--ORA-01017: invalid username/password; logon denied：Oracle8对应的实例不对，不能成功连接

（2）Local Naming Method
The local naming method adds network service names to the tnsnames.ora file. Each network service name maps to a connect descriptor.
tnsnames.ora是oracle客户端所需要的一个文件，通过该文件可以配置数据库的连接地址，配好后，不用再输入完整的oracle地址，直接用简易的字符串代替即可。
本地sqlplus------监听器------数据库

sales=
(DESCRIPTION=
  (ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521))
  (CONNECT_DATA=
     (SERVICE_NAME=sales.us.example.com)))

连接本地实例（oracle用户字段映射到数据库sys用户，Local OS Authentication）： sqlplus / as sysdba
连接远程实例：sqlplus 用户名/密码@监听器主机ip:端口/服务名 或者sqlplus 用户名/密码@tns中alias名字
（如果是sys用户，需要指定登录身份，一般为sysdba---as sysdba）
PS.如果指定的是PDB数据库，会直接连接到PDB中

sqlplus sys/oracle@odd.us.oracle.com:1521/Oracle9 as sysdba
sqlplus sys/oracle@odd.us.oracle.com:1521/CDB1.us.oracle.com as sysdba--成功，监听器主机ip:端口/服务名语法
sqlplus sys/oracle@test as sysdba
sqlplus system/oracle@test2--成功，tns中alias名字

sqlplus sys/oracle@CDB1.us.oracle.com as sysdba
--ORA-12154: TNS:could not resolve the connect identifier specified：CDB1.us.oracle.com在tns中没有配置

sqlplus sys/oracle@test2 as sysdba
--ORA-12523: TNS:listener could not find instance appropriate for the client connection：Oracle9在odd上无实例

（3）LDAP

（4）service information repository

2.3.2 白名单

TCP.VALIDNODE_CHECKING=yes 
# allowlist   
TCP.INVITED_NODES=(192.168.18.18,ip2,ip3) 
# blocklist   
TCP.EXCLUDED_NODES=(10.10.10.10,ip2,ip3)

三、配置和管理oracle network的工具

3.1监听器管理lsnrctl

查看监听器状态：lsnrctl status 监听器名称（没有则默认）
启动监听器：lsnrctl start
关闭监听器：lsnrctl stop

3.2网络管理工具

• oracle net manager：netmgr
• oracle net configuration assistant：nteca
• 命令行 配置文件路径：cd $ORACLE_HOME/network/admin/
  示例路径：cd $ORACLE_HOME/network/admin/samples/

四、高级配置

负载均衡 LOAD_BALANCE=on、故障转移FAILOVER=on、SOURCE_ROUTE=on(使用特定路径到达目的地时需要此参数, 此参数用于启用与 Oracle Connection Manager 的连接)

Option	Parameter Setting
Try each address, in order, until one succeeds.	FAILOVER=on
Try each address, randomly, until one succeeds.	LOAD_BALANCE=on FAILOVER=on
Try one address, selected at random.	LOAD_BALANCE=on
Use each address in order until destination reached.	SOURCE_ROUTE=on
Use only the first address.	LOAD_BALANCE=off FAILOVER=off SOURCE_ROUTE=off

配置tnsnames.ora

client load balancing:
sales.us.example.com=
(DESCRIPTION=
  (ADDRESS_LIST=
   (LOAD_BALANCE=off)
   (FAILOVER=on)
   (ADDRESS=(PROTOCOL=tcp)(HOST=sales1-server)(PORT=1521))
   (ADDRESS=(PROTOCOL=tcp)(HOST=sales2-server)(PORT=1521)))
  (CONNECT_DATA=
   (SERVICE_NAME=sales.us.example.com)))

connect-time failover:
sales.us.example.com=
(DESCRIPTION=
  (ADDRESS_LIST=
   (LOAD_BALANCE=off)
   (FAILOVER=on)
   (ADDRESS=(PROTOCOL=tcp)(HOST=sales1-server)(PORT=1521))
   (ADDRESS=(PROTOCOL=tcp)(HOST=sales2-server)(PORT=1521)))
  (CONNECT_DATA=
   (SERVICE_NAME=sales.us.example.com)))

Oracle Connection Manager and load balancing:
sales.us.example.com=
(DESCRIPTION=
   (SOURCE_ROUTE=ON)
   (ADDRESS=(PROTOCOL=tcp)(HOST=cman-pc1)(PORT=1630))
   (ADDRESS=
     (LOAD_BALANCE=ON)
     (ADDRESS=(PROTOCOL=tcp)(HOST=cman-pc2)(PORT=1521))
     (ADDRESS=(PROTOCOL=tcp)(HOST=cman-pc3)(PORT=1521)))
   (CONNECT_DATA=
     (SERVICE_NAME=sales.us.example.com)))

五、Shared&Dedicated Server Architecture

区分当前是什么模式，结果大于0就是shared server模式
SQL> select count(*) from v$shared_server;

eg.修改dedicated为shared 1、在tns中将数据库改成shared

test =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = odd.us.oracle.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME =CDB1.us.oracle.com)
      (server=shared)
    )
  )

ps.如果sqlnet.ora配置了下列选项，无论tns怎么配置，都会使用dedicated USE_DEDICATED_SERVER=on

2、数据库更改成shared server

alter system set shared_servers=2;
alter system set dispatchers="(protocol=TCP)(dispatchers=4)";
alter system set max_shared_servers=3;

----读取tnsname.ora的方式连接的
sqlplus sys/oracle@test as sysdba

----easy connect方式连接
sqlplus /nolog
SQL>connect sys/oracle@odd.us.oracle.com:1521/CDB1.us.oracle.com:shared as sysdba