19C中的SERVICE_NAME

我们在12c之前的版本,添加或修改应用SERVICE_NAME的方式是通过修改数据库参数”SERVICE_NAMES”,但是此参数意在19c版本中废弃,并且再次利用此参数会出现alter session set container=pdb命令失效,无法在sqlplus中切换PDB的影响

官方文档提示:

Note:The SERVICE_NAMES initialization parameter is deprecated in Oracle Database 19c and may be desupported in a future release.
Use of the SERVICE_NAMES parameter is no longer actively supported. It must not be used for high availability (HA) deployments and it is not supported for HA operations. This restriction includes FAN, load balancing, FAILOVER_TYPE, FAILOVER_RESTORE, SESSION_STATE_CONSISTENCY, and any other uses.

To manage your services, Oracle recommends that you instead use the SRVCTL command-line utility, the GDSCTL command-line utility, or the DBMS_SERVICE PL/SQL package.

实验测试过程:

SQL> select name,open_mode,database_role from v$database;

NAME               OPEN_MODE                                DATABASE_ROLE
------------------ ---------------------------------------- --------------------------------
ALBERTCDB           READ WRITE                               PRIMARY

SQL> show parameter service 

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
service_names                        string                 ALBERTCDB
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB9A                          READ WRITE NO
         4 PDB113A                        READ WRITE NO
SQL> 

监听注册SERVICE情况,pdb9a与pdb113a默认serivce已注册并且可以正常对外提供服务:
Service "+ASM" has 1 instance(s).
  Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "+ASM_DG_ARC" has 1 instance(s).
  Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "+ASM_DG_DATA" has 1 instance(s).
  Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "+ASM_DG_MGMT" has 1 instance(s).
  Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "+ASM_DG_OCR" has 1 instance(s).
  Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "+ASM_DG_REDO" has 1 instance(s).
  Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "+ASM_DG_VOTE" has 1 instance(s).
  Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "c36f7bedb52420e5e053d06c050a5e9a" has 1 instance(s).
  Instance "ALBERTCDB2", status READY, has 1 handler(s) for this service...
Service "c3d979c76e1b0f6de053d06c050a6730" has 1 instance(s).
  Instance "ALBERTCDB2", status READY, has 1 handler(s) for this service...
Service "ALBERTCDB" has 2 instance(s).
  Instance "ALBERTCDB2", status UNKNOWN, has 1 handler(s) for this service...
  Instance "ALBERTCDB2", status READY, has 1 handler(s) for this service...
Service "ALBERTCDBXDB" has 1 instance(s).
  Instance "ALBERTCDB2", status READY, has 1 handler(s) for this service...
Service "ALBERTCDB_CFG" has 1 instance(s).
  Instance "ALBERTCDB2", status READY, has 1 handler(s) for this service...
Service "ALBERTCDB_dgmgrl" has 1 instance(s).
  Instance "ALBERTCDB2", status UNKNOWN, has 1 handler(s) for this service...
Service "pdb113a" has 1 instance(s).
  Instance "ALBERTCDB2", status READY, has 1 handler(s) for this service...
Service "pdb9a" has 1 instance(s).
  Instance "ALBERTCDB2", status READY, has 1 handler(s) for this service...
The command completed successfully

正常切换PDB:
SQL> alter session set container=PDB9A;

Session altered.

SQL> alter session set container=PDB113A;

Session altered.

修改SERVICE_NAMES参数,添加ALBERT的应用SERVICE:
SQL> show parameter service

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
service_names                        string                 ALBERTCDB

SQL> alter system set service_names='ALBERTCDB,albert';

System altered.

SQL>
SQL>
SQL> show parameter service

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
service_names                        string                 ALBERTCDB,albert

修改完毕后,可以看到监听中PDB默认的SERVICE已经不见了:
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "+ASM_DG_ARC" has 1 instance(s).
  Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "+ASM_DG_DATA" has 1 instance(s).
  Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "+ASM_DG_MGMT" has 1 instance(s).
  Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "+ASM_DG_OCR" has 1 instance(s).
  Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "+ASM_DG_REDO" has 1 instance(s).
  Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "+ASM_DG_VOTE" has 1 instance(s).
  Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "albert" has 1 instance(s).
  Instance "ALBERTCDB2", status READY, has 1 handler(s) for this service...
Service "ALBERTCDB" has 2 instance(s).
  Instance "ALBERTCDB2", status UNKNOWN, has 1 handler(s) for this service...
  Instance "ALBERTCDB2", status READY, has 1 handler(s) for this service...
Service "ALBERTCDBXDB" has 1 instance(s).
  Instance "ALBERTCDB2", status READY, has 1 handler(s) for this service...
Service "ALBERTCDB_CFG" has 1 instance(s).
  Instance "ALBERTCDB2", status READY, has 1 handler(s) for this service...
Service "ALBERTCDB_dgmgrl" has 1 instance(s).
  Instance "ALBERTCDB2", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

并且CDB中是无法切换PDB的:
SQL> alter session set container=PDB9A;
ERROR:
ORA-44787: Service cannot be switched into.


SQL> alter session set container=PDB113A;
ERROR:
ORA-44787: Service cannot be switched into.

修复方式为:
将SERVICE_NAMES中将SERVICE取出,并且重启CDB

此时需要重启CDB
SQL> 
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1.3100E+11 bytes
Fixed Size                 23312360 bytes
Variable Size            3.3286E+10 bytes
Database Buffers         9.7442E+10 bytes
Redo Buffers              245121024 bytes
Database mounted.
Database opened.

SQL> alter session set container=PDB9A;

Session altered.

SQL> alter session set container=PDB113A;

Session altered.