Non-CDB Convert to PDB -ORA-12518

日前，听到客户反馈说。生产环境在年前执行迁移，由 Non-CDB 转换成 PDB 后。数据库各项指标均正常。但通过SQL-Developer连接数据库时显示 ORA-12518 报错，导致年中无法使用生产环境，所幸当前生产环境仅部分人使用，没有造成大面积生产事故。

1. 何为ORA-12518错误，出现该错误有几个原因？
   1. 其一：由于数据库短时间内出现大量的会话连接，导致进程数和会话数（processes和sessions）参数值被打爆，导致出现该问题
   2. 其二：也是本文所提及的问题，就是用户在转换PDB的过程中，虽然数据库角色转换成功，但是没有在CDB下配置对应的listener和TNS文件信息。所以用户在连接PDB时，监听无法根据原服务找到对应的库，就出现了当前问题

1. 校验CDB中是否能够成功打开新的PDB。新建PDB的名称是 “ORCLPDB”

$ sqlplus / as sysdba
SQL> show pdbs
   CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB                            MOUNTED
         4 ORCLPDB                        MOUNTED    NO

SQL> alter pluggable database ORCLPDB open;
Pluggable database altered.

2.查看监听信息
注意：默认情况下，CDB的listener和tnsnames文件中均不注册PDB相关信息，仅有CDB的监听信息。若CDB是新建的此处需要重点关注。也是当前文章侧重讲解的点

# 默认 CDB 中 listener.ora 监听内容
CDB =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = testcdb.localdomain.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

# 默认 CDB 中 tnsname.ora 的服务内容
LISTENER_CDB =
  (ADDRESS = (PROTOCOL = TCP)(HOST = testcdb.localdomain.com)(PORT = 1521))

CDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = testcdb.localdomain.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cdb)
    )
  )

可以看到，其中并没有注册任何PDB的有关信息。Oracle数据库中利用"listener.ora"文件来监听数据库，利用"tnsnames.ora"对外暴露服务，提供给第三方软件进行访问。如果tns中没有暴露对应库的服务，则第三方软件无法连接至对应的数据库。

3、添加PDB注册信息及服务
注意：在添加前，最好是先备份 “listener.ora” 和 “tnsname.ora” 文件。稳如老狗，切记勿给自己留坑！！！

# tnsname.ora 文件：文件中将需要对外暴露的库信息进行书写。按照当前对应的格式编辑即可

LISTENER_CDB =
  (ADDRESS = (PROTOCOL = TCP)(HOST = testcdb.localdomain.com)(PORT = 1521))


CDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = testcdb.localdomain.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cdb)
    )
  )
PDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = testcdb.localdomain.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = PDB)
      (INSTANCE_NAME = CDB)
    )
  )

ORCLPDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = testcdb.localdomain.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = ORCLPDB)
      (INSTANCE_NAME = CDB)
    )
  )

# 注意：listener.ora文件：其中注册对应的PDB库信息并标明PDB所对应的容器是哪个即可。
CDB =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = testcdb.localdomain.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
SID_LIST_TEST =
   (SID_LIST =
     (SID_DESC =
        (ORACLE_HOME=/u01/db/CDBOH/cdb)
        (SID_NAME = CDB)
     )
     (SID_DESC =
        (GLOBAL_DBNAME = PDB)
        (SID_NAME = CDB)
    )
     (SID_DESC =
        (GLOBAL_DBNAME = ORCLPDB)
        (SID_NAME = CDB)
    )
   )

4、重启监听即可

$ lsnrctl start [SID]