ORA-44787-19c rac搭建pdb过程中，主库cdb设置service_names设置引发的血案！！！

wangxu · 发表于 2023-2-25 11:40:54

19c rac搭建pdb过程中，主库cdb设置service_names设置引发的血案！！！

1、背景

23年02月25日早上，突然接到用户电话，说某个业务系统不能使用了.

2、问题分析

2.1）通过远程过去分析，在rac1和rac2上使用show pdbs均能看到主库正常，

登录/注册后可看大图

317063f97aea5eb92.png (19.68 KB, 下载次数: 202)

下载附件

2023-2-25 11:05 上传

登录/注册后可看大图

3074963f97b433beca.png (19.66 KB, 下载次数: 210)

下载附件

2023-2-25 11:06 上传

登录/注册后可看大图

4738863f97b62bdc0e.png (19.58 KB, 下载次数: 200)

下载附件

2023-2-25 11:07 上传

2.2）使用连接字符串测试，报服务不存在(pdb1,pdb2均是这样)

SQL> conn sys/his@192.168.9.200/pdb1 as sysdba
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

Warning: You are no longer connected to ORACLE.

2.3）进入几个节点oracle和grid用lsnrctl status看服务，发现都没有pdb1和pdb2的服务，只有cdb的服务；

登录/注册后可看大图

391963f97c9d848ac.png (68.62 KB, 下载次数: 197)

下载附件

2023-2-25 11:12 上传

登录/注册后可看大图

6562863f97cb155865.png (69.03 KB, 下载次数: 202)

下载附件

2023-2-25 11:12 上传

登录/注册后可看大图

3842163f97cc7a344e.png (69.2 KB, 下载次数: 205)

下载附件

2023-2-25 11:13 上传

事情走到这里，我去检查了我修改的dg脚本，怀疑是修改了cdb下面的service_names='orcl';导致的，查看了对应的服务，又有
SQL> set line 200
SQL> col pdb for a15
SQL> col network_name for a15
SQL> col name for a15
SQL> select service_id,name,network_name,enabled,pdb from cdb_services;

SERVICE_ID NAME    NETWORK_NAME ENABLE PDB
---------- --------------- --------------- ------ ---------------
   1 SYS$BACKGROUND    NO    CDB$ROOT
   2 SYS$USERS    NO    CDB$ROOT
   5 orclXDB    orclXDB    NO    CDB$ROOT
   6 orcl    orcl    NO    CDB$ROOT
1 PDB1    PDB1    NO    PDB1
10 PDB2    PDB2    NO    PDB2

6 rows selected.

2.4）使用alter session set container切换到容器测试

登录/注册后可看大图

2308563f97f1811597.png (11.04 KB, 下载次数: 207)

下载附件

2023-2-25 11:23 上传

[oracle@his1 ~]$ oerr ORA 44787
44787, 0000, "Service cannot be switched into."
// *Document: Yes
// *Cause: The service did not exist in the new pluggable database or the service was not started in the pluggable database.
// *Action: Use a valid service name for the pluggable database or start the service in the pluggable database first.

3、问题解决

登录/注册后可看大图

1450563f97ff45c1e8.png (15.59 KB, 下载次数: 222)

下载附件

2023-2-25 11:26 上传

再次查看服务和登陆验证：
[oracle@his1 ~]$ lsnrctl status
...
  Instance "orcl1", status READY, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
  Instance "orcl1", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl1", status READY, has 1 handler(s) for this service...
Service "pdb1" has 1 instance(s).
  Instance "orcl1", status READY, has 1 handler(s) for this service...
Service "pdb2" has 1 instance(s).
  Instance "orcl1", status READY, has 1 handler(s) for this service...
The command completed successfully

[oracle@his2 ~]$ lsnrctl status
...
  Instance "orcl2", status READY, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
  Instance "orcl2", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl2", status READY, has 1 handler(s) for this service...
Service "pdb1" has 1 instance(s).
  Instance "orcl2", status READY, has 1 handler(s) for this service...
Service "pdb2" has 1 instance(s).
  Instance "orcl2", status READY, has 1 handler(s) for this service...
The command completed successfully

[oracle@his3 ~]$ lsnrctl status
...
  Instance "orcl3", status READY, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
  Instance "orcl3", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl3", status READY, has 1 handler(s) for this service...
Service "pdb1" has 1 instance(s).
  Instance "orcl3", status READY, has 1 handler(s) for this service...
Service "pdb2" has 1 instance(s).
  Instance "orcl3", status READY, has 1 handler(s) for this service...
The command completed successfully

SQL> conn sys/his@192.168.9.200/pdb1 as sysdba
Connected.
SQL> conn sys/his@192.168.9.200/pdb2 as sysdba
Connected.
SQL> conn sys/his@192.168.9.201/pdb2 as sysdba
Connected.
SQL> conn sys/his@192.168.9.202/pdb2 as sysdba
Connected.

4、总结
4.1 发生这个事情的本质还是对脚本审核不严谨，按照传统noncdb的方式来调整，实际上主库是不需要设置service_names=orcl；
4.2 为了更详细的验证是这个参数引起的问题，换其它19c 测试rac环境再次修改service_names也能够复现该问题，说明该cdb的参数修改了确实是会覆盖pdb的动态注册参数，引起业务不可用情况；

wangxu · 发表于 2023-2-25 11:44:58

标题写错了，ORA-44787-19c rac CDB搭建单机ADG CDB，主库cdb设置service_names引发的血案！！！

帐号		自动登录	找回密码
密码			注册

[Oracle] ORA-44787-19c rac搭建pdb过程中，主库cdb设置service_names设置引发的血案！！！