CNDBA社区
1 场景
sqlplus 连接oracle 数据库提示如下错误：
sqlplus 连接cdb 提示无法识别连接的描述符
[oracle@www.cndba.cn admin]$ sqlplus system/oracle@cndba
SQL*Plus: Release 18.0.0.0.0 Production on Tue Jun 19 13:54:22 2018
Version 18.1.0.0.0
Copyright (c) 1982, 2017, Oracle. All rights reserved.
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor
2 查看监听状态，判断是否是监听的问题
[oracle@www.cndba.cn admin]$ lsnrctl status
LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 19-JUN-2018 13:58:14
Copyright (c) 1991, 2017, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 18.0.0.0.0 - Production
Start Date 19-JUN-2018 09:39:15
Uptime 0 days 4 hr. 18 min. 58 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/18.1.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/host1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=host1)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully
从中可以看出监听正常，但是The listener supports no services这个是不识别service_name
3 查看参数
SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
cdb_cluster_name string
cell_offloadgroup_name string
db_file_name_convert string
db_name string cndba
db_unique_name string cndba
global_names boolean FALSE
instance_name string cndba
lock_name_space string
log_file_name_convert string
pdb_file_name_convert string
processor_group_name string
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
service_names string cndba
4 正确的配置方法
4.1 在listener.ora文件里面添加SID_LIST_LISTENER
[oracle@www.cndba.cn admin]$ cd /u01/app/oracle/product/18.1.0/dbhome_1/network/admin
[oracle@www.cndba.cn admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/18.1.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = cndba)--db_unique_name
(ORACLE_HOME = /u01/app/oracle/product/18.1.0/dbhome_1)
(SID_NAME = cndba)--instance_name
)
(SID_DESC =
(GLOBAL_DBNAME = pdbcndba)--pdbname
(ORACLE_HOME = /u01/app/oracle/product/18.1.0/dbhome_1)
(SID_NAME = cndba)--instance_name
)
)
重启listener服务
[oracle@www.cndba.cn admin]$ lsnrctl start
LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 19-JUN-2018 14:29:27
Copyright (c) 1991, 2017, Oracle. All rights reserved.
Starting /u01/app/oracle/product/18.1.0/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 18.0.0.0.0 - Production
System parameter file is /u01/app/oracle/product/18.1.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/host1/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=host1)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 18.0.0.0.0 - Production
Start Date 19-JUN-2018 14:29:27
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/18.1.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/host1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=host1)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "cndba" has 1 instance(s).
Instance "cndba", status UNKNOWN, has 1 handler(s) for this service...
Service "pdbcndba" has 1 instance(s).
Instance "cndba", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
4.2 修改tnsnames.ora，添加以下内容
[oracle@www.cndba.cn admin]$ cd /u01/app/oracle/product/18.1.0/dbhome_1/network/admin
[oracle@www.cndba.cn admin]$ cat tnsnames.ora
tnsnames.ora 添加以下内容
CNDBA =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = host1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = cndba)
)
)
pdbcndba =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = host1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdbcndba)-- pdbname
)
)
5 测试连接是否成功
5.1 SQLPLUS连接CDB
[oracle@www.cndba.cn admin]$ sqlplus sys/oracle@cndba as sysdba
SQL*Plus: Release 18.0.0.0.0 Production on Tue Jun 19 14:15:42 2018
Version 18.1.0.0.0
Copyright (c) 1982, 2017, Oracle. All rights reserved.
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.1.0.0.0
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
4 PDBCNDBA READ WRITE NO
5.2 SQLPLUS连接PDB
[oracle@www.cndba.cn admin]$ sqlplus sys/oracle@pdbcndba as sysdba
SQL*Plus: Release 18.0.0.0.0 Production on Tue Jun 19 14:15:53 2018
Version 18.1.0.0.0
Copyright (c) 1982, 2017, Oracle. All rights reserved.
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.1.0.0.0
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
4 PDBCNDBA READ WRITE NO
版权声明：本文为博主原创文章，未经博主允许不得转载。
