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.

ORA-00704: bootstrap process failure故障修复

近日,同事反馈一套测试测试数据库宕机,进行重启数据库操作时发现数据库无法open,抛出ORA-00704: bootstrap process failure错误,版本为11.2.0.4

SQL> startup;
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size		    2260088 bytes
Variable Size		  671089544 bytes
Database Buffers	  390070272 bytes
Redo Buffers		    5517312 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist
Process ID: 57338
Session ID: 96 Serial number: 3

错误一并抛出了ORA-00942: table or view does not exist错误,应该是系统表丢失导致的bootstrap失败,并且alert日志中没有任何其他日志输出。

将数据库启动到nomount与mount状态,利用10046 event跟踪bootstrap过程:
SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/opt/app/ora11g/albert/diag/rdbms/prod3/PROD3/trace/PROD3_ora_57585.trc
SQL> oradebug event 10046 trace name context forever,level 12;
Statement processed.
SQL> alter database mount;

Database altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist
Process ID: 57585
Session ID: 96 Serial number: 3

访问/opt/app/ora11g/albert/diag/rdbms/prod3/PROD3/trace/PROD3_ora_57585.trc发现访问access$表时抛出异常:

继续阅读