SQLPLUS 连接 Oracle 18c CDB和PDB方法

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