客服微信
本文为云贝教育 刘峰 原创，请尊重知识产权，转发请注明出处，不接受任何抄袭、演绎和未经注明出处的转载。
学习目标：
1、19c中监听由哪个进程负责管理
2、什么是动态监听，什么是动态监听
3、如何在非1521端口上注册监听
登陆一个网站，需要用到 HTTP 协议；从网站下载，需要用到 FTP 协议；同样，Oracle 客户端与 Oracle 服务端的连接也有自己的网络协议，就要用到 OracleNet 或称作SQLNet.
Oracle Net 基本要素包括三类文件：基于服务端的 Listener.ora、基于客户端的Tnsnames.ora 和基于服务端或客户端（两端） 的 Sqlnet.ora（可选）。
注意： Linux 环境下，安装后默认没有此三类文件。 需要通过手工编辑或工具进行建立。默认存储位置$ORACLE_HOME/network/admin
什么是监听（侦听）？
服务器端配置文件 listener.ora，监听器启动后，负责接收 User Process 并派生 ServerProcess，与 User Process 建立起 Session。
创建 Listener 三种方式： NETMGR、 NETCA 和手工编辑。
提问：在默认没有 listener.ora 配置文件的情况下，客户端是否可以连接服务端？
查看监听状态： [oracle@henry ~]$ lsnrctl status 启动监听命令： [oracle@henry ~]$ lsnrctl start 停止监听命令： [oracle@henry ~]$ lsnrctl stop 重加载监听命令： [oracle@henry ~]$ lsnrctl reload
监听（默认端口 1521） 是负责统筹安排用户进程为其分发服务器进程，并指引哪些数据库实例可以连接。 如果是非 1521 端口，又想通过改监听端口访问实例信息，那么实例信息必须提前登记注册在这个非 1521 端口的监听里面，这样监听才知道通过非 1521 端口可以访问哪些实例。
如果 Listener 如果使用非标准端口（如 1588），需要在 listener.ora 文件里手工注册，添加 GLOBAL_DBNAME、 ORACLE_HOME 和 INSTANCE NAME.
静态注册不需要打开数据库，通过读取 listener.ora 配置文件来完成监听器的注册，因为不需要打开数据库，所以如果服务端一旦启动了静态监听，便可以通过 sqlplus 以 sys用户连接到服务端，实现远程启动和关闭数据库的任务。
静态注册可以使用自定义的端口号（非 1521 默认），相对隐蔽安全。静态注册实例状态为 UNKNOWN
说明：
1、oracle实例运行后，监听程序启动时，根据listener.ora的配置注册相应的服务。
2、其中global_dbname对应的是oracle对外的服务名，即初始化参数里的service_names
3、sid_name对应的是oralce实例的名称，即初始化参数里的instance_name
查看global_dbname
--pdb下查询 SYS@pdb> select global_name from global_name; GLOBAL_NAME ------------------------------ ORCLPDB1 --cdb下查询 SYS@ORCLCDB> select global_name from global_name; GLOBAL_NAME -------------------------------------------------------------------------------- ORCLCDB
修改global_name
SYS@ORCLCDB> ALTER DATABASE RENAME GLOBAL_NAME TO ORCLCDB; Database altered
查看默认的监听
Services Summary... Service "ORCLCDB" has 1 instance(s). -->这个连接CDB Instance "ORCLCDB", status READY, has 1 handler(s) for this service... Service "ORCLCDBXDB" has 1 instance(s). Instance "ORCLCDB", status READY, has 1 handler(s) for this service... Service "fe8cf63d04b91f62e053814aa8c03b8a" has 1 instance(s). Instance "ORCLCDB", status READY, has 1 handler(s) for this service... Service "noncdb" has 1 instance(s). Instance "noncdb", status READY, has 1 handler(s) for this service... Service "noncdbXDB" has 1 instance(s). Instance "noncdb", status READY, has 1 handler(s) for this service... Service "orclpdb1" has 1 instance(s).-->这个连接PDB Instance "ORCLCDB", status READY, has 1 handler(s) for this service... The command completed successfully
示例1：
1、配置一个连接CDB的静态监听
2、端口绑定到1621
3、默认格式SID_LIST_DEMO1和DEMO1
SID_LIST_LISTENER1 = -->LISTENER1和下面的配置一样 (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = ORCLCDB) (ORACLE_HOME = /opt/oracle/product/19c/dbhome_1) (SID_NAME= ORCLCDB) ) ) --这里的命名格式要和上面一致 LISTENER1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = database)(PORT = 1621)) )
静态注册实例状态为 UNKNOWN
[oracle@database admin]$ lsnrctl status listener1 LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 04-JUL-2023 01:00:07 Copyright (c) 1991, 2019, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=database)(PORT=1621))) STATUS of the LISTENER ------------------------ Alias listener1 Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production Start Date 04-JUL-2023 00:56:46 Uptime 0 days 0 hr. 3 min. 20 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /opt/oracle/product/19c/dbhome_1/network/admin/listener.ora Listener Log File /opt/oracle/diag/tnslsnr/database/listener1/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=database)(PORT=1621))) Services Summary... Service "ORCLCDB" has 1 instance(s). Instance "ORCLCDB", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully
通过静态监听连接数据库，此时连接的是CDB
[oracle@database admin]$ sqlplus sys/oracle@database:1621/ORCLCDB as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jul 4 01:01:34 2023 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 Session altered. SYS@database:1621/ORCLCDB> show con_name CON_NAME ------------------------------ CDB$ROOT
示例2：
1、配置一个连接PDB的静态监听
2、端口绑定到1721
PDB的静态配置
SID_LIST_LISTENER2 = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = orclpdb1) (ORACLE_HOME = /opt/oracle/product/19c/dbhome_1) (SID_NAME= ORCLCDB) ) ) LISTENER2 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = database)(PORT = 1721)) )
通过静态监听连接PDB
[oracle@database admin]$ sqlplus sys/oracle@database:1721/orclpdb1 as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jul 4 01:10:48 2023 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 Session altered. SYS@database:1721/orclpdb1> show con_name CON_NAME ------------------------------ ORCLPDB1 SYS@database:1721/orclpdb1> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 3 ORCLPDB1 READ WRITE NO
提问：静态监听和动态监听，可以使用同一个端口吗，比如1521
测试如下
1）配置一个静态监听，使用1521端口 [oracle@database admin]$ cat listener.ora # listener.ora Network Configuration File: /opt/oracle/product/19c/dbhome_1/network/admin/listener.ora # Generated by Oracle configuration tools. SID_LIST_LISTENER2 = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = orclpdb1) (ORACLE_HOME = /opt/oracle/product/19c/dbhome_1) (SID_NAME= ORCLCDB) ) ) LISTENER2 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = database)(PORT = 1521)) ) 2）关闭默认的动态监听，再次启动动态监听 [oracle@database admin]$ lsnrctl start LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 04-JUL-2023 02:04:14 Copyright (c) 1991, 2019, Oracle. All rights reserved. TNS-01106: Listener using listener name listener2 has already been started -->这里提示是因为1521注册的监听已经被静态监听listener2启动 3）修改静态监听端口即可
那静态监听的核心作用是什么？主要是可以让远端在数据库未启动的情况下操作数据，比如从远端拉起数据库
Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=database)(PORT=1621))) Services Summary... Service "ORCLCDB" has 1 instance(s). Instance "ORCLCDB", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully [oracle@database ~]$ sqlplus sys/oracle@database:1621/ORCLCDB as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jul 4 07:16:41 2023 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to an idle instance. ALTER SESSION SET nls_date_format = 'YYYY-MM-DD HH:MI:SS' * ERROR at line 1: ORA-01034: ORACLE not available Process ID: 0 Session ID: 0 Serial number: 0 SYS@database:1621/ORCLCDB> startup ORACLE instance started. Total System Global Area 759166168 bytes Fixed Size 9139416 bytes Variable Size 583008256 bytes Database Buffers 58720256 bytes Redo Buffers 3440640 bytes In-Memory Area 104857600 bytes Database mounted. Database opened. SYS@database:1621/ORCLCDB> show con_name CON_NAME ------------------------------ CDB$ROOT
Listener 采用默认端口 1521，当实例启动时，由 PMON 每分钟自动将实例信息注册到监听里。 需要数据库打开才能注册成功，所以动态注册无法使用 sysdba 身份远程启动数据库。一般都是通过远程登陆服务器，再转入 oracle 以 sysdba 身份打开数据库，最后PMON 进程（12c 开始由 LREG 进程接管） 才可以动态的注册。
注意：如果采用默认监听名 LISTENER 和端口号（1521），可以不使用 listener.ora 文件， 以默认方式运行监听器， PMON 每隔 60 秒查看 listener 是否启动，如果启动就注册相关信息到监听。 建议始终配置 listener.ora 文件，使 Oracle Net 环境可以自我记录。
动态注册实例状态为 READY，示例如下
Service "PROD1" has 1 instance(s). Instance "PROD1", status READY, has 1 handler(s) for this service...
默认参数：LOCAL_LISTENER
在默认情况下，进程监视器 PMON（12c 开始由 LREG 进程接管）会根据参数LOCAL_LISTENER 的值动态的将实例信息注册到监听端口上。如果没有设置LOCAL_LISTENER 参数， PMON（12c 开始由 LREG 进程接管） 会将实例信息注册到监听名为 LISTENER（默认）下的所有端口上。 注意：一旦更改了参数值，表示在动态注册的时候只针对更改的参数值起作用。
查看设置 LOCAL_LISTENER 参数：
SYS@database:1621/ORCLCDB> show parameter local_listener NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ local_listener string LISTENER_ORCLCDB 查看tnsnames.ora，有如下定义 LISTENER_NONCDB = (ADDRESS = (PROTOCOL = TCP)(HOST = database)(PORT = 1521))
此参数两种设置方法，可以直接设置监听描述，也可以与 tnsnames.ora 文件搭配使用。
1、listener.ora--注意，不要出现重名的配置文件，否则为以最下面的配置为主 LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = database)(PORT = 1521)) ) (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = database)(PORT = 1522)) ) (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = database)(PORT = 1523)) ) (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = database)(PORT = 1524)) ) ) 2、重启启动监听 [oracle@database admin]$ lsnrctl stop listener [oracle@database admin]$ lsnrctl start listener [oracle@database admin]$ lsnrctl status LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 04-JUL-2023 07:48:44 Copyright (c) 1991, 2019, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=database)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias listener Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production Start Date 04-JUL-2023 07:48:17 Uptime 0 days 0 hr. 0 min. 29 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /opt/oracle/product/19c/dbhome_1/network/admin/listener.ora Listener Log File /opt/oracle/diag/tnslsnr/database/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=database)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=database)(PORT=1522))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=database)(PORT=1523))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=database)(PORT=1524))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=database)(PORT=5500))(Security=(my_wallet_directory=/opt/oracle/admin/ORCLCDB/xdb_wallet))(Presentation=HTTP)(Session=RAW)) Services Summary... Service "ORCLCDB" has 1 instance(s). Instance "ORCLCDB", status READY, has 1 handler(s) for this service... Service "ORCLCDBXDB" has 1 instance(s). Instance "ORCLCDB", status READY, has 1 handler(s) for this service... Service "fe8cf63d04b91f62e053814aa8c03b8a" has 1 instance(s). Instance "ORCLCDB", status READY, has 1 handler(s) for this service... Service "orclpdb1" has 1 instance(s). Instance "ORCLCDB", status READY, has 1 handler(s) for this service... The command completed successfully 3、验证登录 sqlplus sys/oracle@database:1521/orclpdb1 as sysdba sqlplus sys/oracle@database:1522/orclpdb1 as sysdba sqlplus sys/oracle@database:1523/orclpdb1 as sysdba sqlplus sys/oracle@database:1524/orclpdb1 as sysdba
实验题：新建监听名为 LSNR2_152 和LSNR3_1523 ，对应端口号分别为 1522 和 1533，分别采用动态注册和静态注册。
需要注意的是，如果采用动态注册，则需修改参数 LOCAL_LISTENER 参数，且注意参数的值不是监听名（如果配置 tnsnames.ora，可使用别名进行解析）。而是监听下端点的具体描述值。
如果采用静态注册，手工编辑 listener.ora 文件即可。
配置过程如下
1）动态监听LSNR2 配置1522端口
1.listener.ora配置 LSNR2_1522 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = database)(PORT = 1522)) ) 2、tnsnames.ora配置 LSNR2_1522 = (ADDRESS = (PROTOCOL = TCP)(HOST = database)(PORT = 1522)) 3、参数配置 SYS@ORCLCDB> alter system set local_listener=LSNR2_1522; System altered.
这样配置完的效果
1、默认的1521端口依然存在，但是没有服务往1521端口注册
[oracle@database admin]$ lsnrctl status listener LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 04-JUL-2023 08:22:24 Copyright (c) 1991, 2019, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production Start Date 04-JUL-2023 08:14:15 Uptime 0 days 0 hr. 8 min. 8 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /opt/oracle/product/19c/dbhome_1/network/admin/listener.ora Listener Log File /opt/oracle/diag/tnslsnr/database/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=database)(PORT=1521))) The listener supports no services The command completed successfully 2、尝试手工注册监听 SYS@ORCLCDB> alter system register; System altered. 3、1521监听仍然无服务 Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=database)(PORT=1521))) The listener supports no services The command completed successfully
2、新增的1522端口生效，需要启动监听
[oracle@database admin]$ lsnrctl status lsnr2_1522 LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 04-JUL-2023 08:14:32 Copyright (c) 1991, 2019, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=database)(PORT=1522))) STATUS of the LISTENER ------------------------ Alias lsnr2_1522 Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production Start Date 04-JUL-2023 08:11:33 Uptime 0 days 0 hr. 2 min. 59 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /opt/oracle/product/19c/dbhome_1/network/admin/listener.ora Listener Log File /opt/oracle/diag/tnslsnr/database/lsnr2_1522/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=database)(PORT=1522))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=database)(PORT=5500))(Security=(my_wallet_directory=/opt/oracle/admin/ORCLCDB/xdb_wallet))(Presentation=HTTP)(Session=RAW)) Services Summary... Service "ORCLCDB" has 1 instance(s). Instance "ORCLCDB", status READY, has 1 handler(s) for this service... Service "ORCLCDBXDB" has 1 instance(s). Instance "ORCLCDB", status READY, has 1 handler(s) for this service... Service "fe8cf63d04b91f62e053814aa8c03b8a" has 1 instance(s). Instance "ORCLCDB", status READY, has 1 handler(s) for this service... Service "orclpdb1" has 1 instance(s). Instance "ORCLCDB", status READY, has 1 handler(s) for this service... The command completed successfully [oracle@database admin]$
2）静态监听LSNR3_1523 配置1523端口
1、监听listener.ora SID_LIST_LSNR3_1523 = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = orclpdb1) (ORACLE_HOME = /opt/oracle/product/19c/dbhome_1) (SID_NAME= ORCLCDB) ) ) LSNR3_1523 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = database)(PORT = 1523)) ) 2、启动LSNR3_1523监听并查看 [oracle@database admin]$ lsnrctl status LSNR3_1523 LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 04-JUL-2023 08:25:07 Copyright (c) 1991, 2019, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=database)(PORT=1523))) STATUS of the LISTENER ------------------------ Alias LSNR3_1523 Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production Start Date 04-JUL-2023 08:17:47 Uptime 0 days 0 hr. 7 min. 20 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /opt/oracle/product/19c/dbhome_1/network/admin/listener.ora Listener Log File /opt/oracle/diag/tnslsnr/database/lsnr3_1523/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=database)(PORT=1523))) Services Summary... Service "orclpdb1" has 1 instance(s). Instance "ORCLCDB", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully
思考：如果监听器停止运行，对目前已经建立起的会话是否会自动断开？
客户端配置文件 tnsnames.ora 主要作用就是提供名称解析。 Linux 系统直接使用NETMGR 命令进行图形化界面配置。 Windows 系统点击开始—Oracle 安装目录—配置和移植工具—Net Manager。
只要有客户端，就可以进行简单（轻松） 连接(无需 tnsnames.ora)。
连接语法：
sqlplus u1/u1@database:1521/orclpdb1
常见的两种方式： 一是通过sqlplus命令连接数据库; 二是通过工具，如PLSQL DEV连接。
想通过TNSNAME连接数据库，一般是单独安装客户端工具，新建network/admin目录，再新建tnsnames.ora，通过tnsping解析tns成功，就可以连接数据库了。
sqlnet.ora 可以在客户端，也可以在服务端。
主要作用有两个：
指定客户端名称解析的顺序（NAMES.DIRECTORY_PATH）；
认证方式的限制（SQLNET.AUTHENTICATION_SERVICES）
将客户端 sqlnet.ora 文件中 NAMES.DIRECTORY_PATH 参数更改为NAMES.DIRECTORY_PATH=(tnsnames)，此时连接只能使用基于 tnsnames.ora 里面定义好的本地别名，无法使用简易连接 ezconnect。
将客户端 sqlnet.ora 文件中 NAMES.DIRECTORY_PATH 参数更改为NAMES.DIRECTORY_PATH=(tnsnames,ezconnect)，此时使用 tnsnames 中本地别名和简易连接都可以使用。
1)NAMES.DIRECTORY_PATH= (TNSNAMES)
1、修改sqlnet.ora NAMES.DIRECTORY_PATH= (TNSNAMES) 2、验证连接 [oracle@database admin]$ sqlplus sys/oracle@database:1523/orclpdb1 as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jul 4 08:39:33 2023 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. ERROR: ORA-12154: TNS:could not resolve the connect identifier specified Enter user-name: ERROR: ORA-01017: invalid username/password; logon denied
补充说明NAMES.DIRECTORY_PATH=hostname
如果设置NAMES.DIRECTORY_PATH=（tnsnames），那么客户端就只会从tnsnames.ora查找你要连接的字符串（如orcl）记录，如果tnsname.ora文件中没有此记录，则连接不上数据库。
如果设置NAMES.DIRECTORY_PATH=（tnsnames，hostname），那么客户端首先会从tnsnames.ora查找你要连接的字符串（如orcl）记录，如果tnsname.ora文件中没有此记录，则尝试把你要连接的字符串（如orcl）当作一个主机名，通过网络的途径去解析它的ip地址然后去连接这个ip上GLOBAL_DBNAME=连接字符串（如orcl）这个实例，当然这里连接字符串（如orcl）并不是一个主机名，最后会尝试以ezconnect的方式连接数据库
SQLNET.AUTHENTICATION_SERVICES 有三个参数：
NONE：禁止 OS 系统认证，必须通过用户名密码登陆。 ALL：既可以 OS 系统认证，也可以用户名密码登陆。 NTS：基于 Windows 系统的本地系统认证。（只有 Windows 上有用）
服务端设置为 NONE，服务端登陆无法使用 OS 系统认证方式登陆。
[oracle@database admin]$ cat sqlnet.ora SQLNET.AUTHENTICATION_SERVICES=(NONE)
1、在服务端尝试以系统身份认证登陆
[oracle@database admin]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jul 4 08:47:55 2023 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. ERROR: ORA-01017: invalid username/password; logon denied Enter user-name:
2、在服务端尝试以密码文件认证登陆
1、输入正确的密码 [oracle@database admin]$ sqlplus sys/oracle as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jul 5 19:13:28 2023 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 Session altered. SYS@ORCLCDB> 2、输入错误的密码 [oracle@database admin]$ sqlplus sys/oracle1 as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jul 5 19:13:48 2023 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. ERROR: ORA-01017: invalid username/password; logon denied Enter user-name:
服务端设置为 NONE，服务端登陆无法使用 OS 系统认证方式登陆。
[oracle@database admin]$ cat sqlnet.ora SQLNET.AUTHENTICATION_SERVICES=(ALL)
1、在服务端尝试以系统身份认证登陆
[oracle@database admin]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jul 5 19:47:21 2023 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 Session altered. SYS@ORCLCDB>
2、在服务端尝试以密码文件认证登陆
[oracle@database admin]$ sqlplus sys/oracle1 as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jul 5 19:47:32 2023 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 Session altered. SYS@ORCLCDB>
总结：
对于Linux平台，参数SQLNET.AUTHENTICATION_SERVICES主要是以下几种情况： (1). sqlnet.ora文件为空，或用#注释掉 --操作系统验证通过，密码文件认证无法通过，EZ连接通过 (2). SQLNET.AUTHENTICATION_SERVICES = (NTS) --操作系统验证无法通过，密码文件认证也无法通过,EZ连接通过 (3). SQLNET.AUTHENTICATION_SERVICES = (NONE) --操作系统验证无法通过，密码文件认证也无法通过,EZ连接通过 (4). SQLNET.AUTHENTICATION_SERVICES = (NONE,NTS) --基于操作系统验证(前后顺序颠倒也一样)不通过，密码文件认证也能通过，EZ连接通过 (5). SQLNET.AUTHENTICATION_SERVICES = (ALL) --操作系统验证通过，密码文件认证无法通过，EZ连接不通过
test = (DESCRIPTION = (ADDRESS_LIST = (LOAD_BALANCE = ON) (FAILOVER = ON) (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.0.102 )(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.0.104 )(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = test1) (FAILOVER_METHOD = (TYPE = SESSION) (METHOD = BASIC) ) ) ) 设置本地服务命名相应的参数(LOAD_BALANCE=ON 和FAILOVER=ON 、FAILOVER_MODE参数)来启用客户端负载均衡和TAF功能。客户端负载均衡并不衡量RAC节点CPU的使用率，它只是随机的连接一个节点的监听。由于是随机连接，所以大体上做到连接数的负载均衡。在RAC环境中设置tnsnames.ora时，应使用SERVICE_NAME。 FAILOVER_MODE的子参数含义： TYPE： 1)session---如果用户连接丢失,将创建一个新的会话连接到备用节点,用户的所有未提交的操作必须回滚,然后再次执行,Select操作也被中止. 2)select---如果用户连接丢失,将使用游标和之前的快照继续执行Select操作,叫做SQL接管,SQL接管能够无缝接管理的事务类型只有Select语句.其它的操作也必须要回滚然后再次执行. 3)none---客户端默认值,禁止SQL接管功能，主要用于测试的目地，在实际应用中TYPE应指定为session 或者select METHOD： 1)basic---仅仅在FAILOVER发生时才连接备用节点 2)preconnect---在用户连接到主节点时同时也在备用节点产生一个连接会话,设置预连接模式,能够快速接管SQL
DBLINK 用于查询不同数据库之间的数据。 DBLINK 分为私有和公有。
例如，在 PROD1 库查询 PROD2 库的数据，可以在 PROD1 库建立 DBLINK。
create database link link_pdb1 connect to u1 identified by u1 using 'PDB1'; select * from dba_db_links;
注意后面的 USING ‘PROD2’，这个是需要在本机查找 tnsnames.ora 文件的别名PROD2。
DBLINK 和 tnsnames.ora 都设置好了，启动监听，就可以执行语句进行查询：
select count(1) from t1@link_pdb1;
默认私有 DBLINK 是当前用户用的，也可以使用 SYS 建立公有 DBLINK，当前所有用户都可以使用。
1、sys用户创建public dblink SYS@ORCLCDB> create public database link link_u1_pub connect to u1 identified by u1 using 'PDB1'; Database link created. 2、sys用户创建public dblink SYS@ORCLCDB> select * from dba_db_links; 3、创建公共用户测试dblink SYS@ORCLCDB> create user c##u2 identified by u2; User created. SYS@ORCLCDB> grant connect,resource to c##u2; Grant succeeded. 4、连接公共用户测试dblink SYS@ORCLCDB> conn c##u2/u2 Connected. C##U2@ORCLCDB> select count(1) from t1@link_u1_pub; COUNT(1) ---------- 10002
