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.