1、背景
23年02月25日早上，突然接到用户电话，说某个业务系统不能使用了.
2、问题分析
2.1）通过远程过去分析，在rac1和rac2上使用show pdbs均能看到主库正常，
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的服务；
事情走到这里，我去检查了我修改的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切换到容器测试
[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、问题解决
再次查看服务和登陆验证：
[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
标题写错了，ORA-44787-19c rac CDB搭建单机ADG CDB，主库cdb设置service_names引发的血案！！！
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, 下载次数: 244)
2023-2-25 11:12 上传
6562863f97cb155865.png (69.03 KB, 下载次数: 250)
2023-2-25 11:12 上传
3842163f97cc7a344e.png (69.2 KB, 下载次数: 251)
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, 下载次数: 256)
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, 下载次数: 273)
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
标题写错了，ORA-44787-19c rac CDB搭建单机ADG CDB，主库cdb设置service_names引发的血案！！！
| 欢迎光临 重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/) | Powered by Discuz! X3.2 |