Introduction

In an Oracle database, services have multiple purposes:

  • identifying group of users prior authenticating them
  • enabling/disabling access to a database for particular groups
  • managing preferred nodes on a RAC configuration
  • redirecting users to the primary after a switchover/failover when using Data Guard or Dbvisit Standby
  • redirecting read only connections to Standby database with Active Guard option

A customer asked me if services could be used for switching between PDBs without modifying client’s connexion, for a very specific usage. Let’s try to find out.

Initializing the test environment

Let’s create 2 PDBs. The goal is to share a service that can alternatively run on the first PDB or on the second one:

create pluggable database JDU1 admin user JDU identified by JDU2021 ROLES=(DBA) DEFAULT TABLESPACE data
DATAFILE SIZE 1G AUTOEXTEND ON MAXSIZE 8G TEMPFILE REUSE;
 
create pluggable database JDU2 admin user JDU identified by JDU2021 ROLES=(DBA) DEFAULT TABLESPACE data
DATAFILE SIZE 1G AUTOEXTEND ON MAXSIZE 8G TEMPFILE REUSE;
 
 
alter pluggable database jdu1 open;
 
alter pluggable database jdu2 open;

Let’s check current running services on these 2 PDBs:

alter session set container=JDU1;
 
select name from v$active_services;
NAME
----------------------------------------------------------------
jdu1
 
 
alter session set container=JDU2;
 
select name from v$active_services;
 
NAME
----------------------------------------------------------------
jdu2

Create a “shared” service and test it

Let’s go to the first container and create the “shared” service:

alter session set container=JDU1;
 
execute DBMS_SERVICE.CREATE_SERVICE (service_name => 'JDU_SVC', network_name => 'JDU_SVC', failover_method => 'BASIC', failover_type => 'SELECT', failover_retries => 1800, failover_delay => 1); ​
​
exec dbms_service.start_service (service_name => 'JDU_SVC');​
 
select name from v$active_services;
 
NAME
----------------------------------------------------------------
jdu1
JDU_SVC

Now, let’s also try to create the service inside the other PDB:

alter session set container=JDU2;
 
execute DBMS_SERVICE.CREATE_SERVICE (service_name => 'JDU_SVC', network_name => 'JDU_SVC', failover_method => 'BASIC', failover_type => 'SELECT', failover_retries => 1800, failover_delay => 1); ​
​
 
*
ERROR at line 1:
ORA-44303: service name exists
ORA-06512: at "SYS.DBMS_SERVICE_ERR", line 21
ORA-06512: at "SYS.DBMS_SERVICE", line 316
ORA-06512: at line 1

It does not work because service seems to be global. Let’s try to start it:

exec dbms_service.start_service (service_name => 'JDU_SVC');​
 
 
*
ERROR at line 1:
ORA-44786: Service operation cannot be completed.
ORA-06512: at "SYS.DBMS_SERVICE", line 76
ORA-06512: at "SYS.DBMS_SERVICE", line 483
ORA-06512: at line 1

It does not work because it’s already started on the first PDB. Let’s stop it on the first PDB:

alter session set container=JDU1;
 
exec dbms_service.stop_service (service_name => 'JDU_SVC');​

And start it on the second PDB:

alter session set container=JDU2;
 
exec dbms_service.start_service (service_name => 'JDU_SVC');​
 
*
ERROR at line 1:
ORA-44786: Service operation cannot be completed.
ORA-06512: at "SYS.DBMS_SERVICE", line 76
ORA-06512: at "SYS.DBMS_SERVICE", line 483
ORA-06512: at line 1

No, it doesn’t work.

Let’s now remove the service from the first PDB and create and start it on the second PDB:

alter session set container=JDU1;
 
exec dbms_service.delete_service (service_name => 'JDU_SVC');
 
PL/SQL procedure successfully completed.
 
alter session set container=JDU2;
 
Session altered.
 
execute DBMS_SERVICE.CREATE_SERVICE (service_name => 'JDU_SVC', network_name => 'JDU_SVC', failover_method => 'BASIC', failover_type => 'SELECT', failover_retries => 1800, failover_delay => 1); ​
​
exec dbms_service.start_service (service_name => 'JDU_SVC');​
 
select name from v$active_services;
 
NAME
----------------------------------------------------------------
JDU_SVC
jdu2

Findings

A service is global and cannot exist in more than one PDB. That’s quite obvious but I thought it was possible to declare it in multiple PDB and start only in one PDB.

Note that at the CDB level, querying the CDB_SERVICES will give you an overview of services and PDB associated:

col name for a30
col pdb for a30
select name, pdb from cdb_services;
 
NAME                           PDB
------------------------------ ------------------------------
SYS$BACKGROUND                 CDB$ROOT
SYS$USERS                      CDB$ROOT
CDBSYR01.test.ch               CDB$ROOT
CDBSYR01XDB                    CDB$ROOT
CDBSYR01_SITE1.test.ch         CDB$ROOT
CSYR01AXDB                     CDB$ROOT
CSYR01A.test.ch                CDB$ROOT
CFRD01AXDB                     CDB$ROOT
CFRD01A.test.ch                CDB$ROOT
JDU1                           JDU1
JDU2                           JDU2
JDU_SVC                        JDU2

Last question I’ve been asking: is it possible to stop the service from the CDB? No, you’ll have to switch to the correct container to do that.

conn / as sysdba
 
exec dbms_service.stop_service (service_name => 'JDU_SVC');
 
*
ERROR at line 1:
ORA-44786: Service operation cannot be completed.
ORA-06512: at "SYS.DBMS_SERVICE_ERR", line 91
ORA-06512: at "SYS.DBMS_SERVICE", line 519
ORA-06512: at line 1

Conclusion

Service is not manageable through CDB, although being a global mechanism. If you want to use services to switch from one PDB to another one, you’ll have to script that because it’s not a feature.

But would you really use this kind of script? Not sure this would be a good idea. If your script failed for some reason, your application will use a database that may not be the expected one, without being aware. Quite dangerous in my opinion.

Post Views: 3,509
Thumbnail [60x60]
by
Jérôme Dubar