By default, Container Database (CDB) provides only one service for every Pluggable Database (PDB) for connections, but we can create our own services for various entries of applications.
There're two ways to add a service to a PDB:
- DBMS_SERVICE Create Service to PDB
- Srvctl Add Service to PDB
It's an administration package for managing services of a database.
For RAC databases or Oracle Restart, we can use it to manage services.
To add a service to a normal, non-CDB database, you may go for that post.
DBMS_SERVICE.CREATE_SERVICE to PDB
For single-instance, we can only use DBMS_SERVICE to add services to a PDB.
Check Listener
Let's see current status of listener.
[oracle@test ~]$ lsnrctl status
...
Services Summary...
Service "ORCLCDB" has 1 instance(s).
Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
Service "ORCLCDBXDB" has 1 instance(s).
Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
Service "aa736f65d66215cce053992aa8c08959" has 1 instance(s).
Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
Service "orclpdb" has 1 instance(s).
Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
The command completed successfully
As you can see, the default service ORCLPDB of the PDB now registers with listener.
Switch Container
Let's see what PDB we have.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB READ WRITE NO
Let's switch container to the PDB.
SQL> alter session set container=ORCLPDB;
Session altered.
Make sure that we are in the right container.
SQL> show con_name
CON_NAME
------------------------------
ORCLPDB
Let's see current services of the container before adding a service to it.
SQL> column con_id format 99
SQL> column pdb format a15
SQL> column network_name format a30
SQL> select con_id, pdb, network_name from cdb_services where pdb is not null and con_id > 2 order by pdb;
CON_ID PDB NETWORK_NAME
------ --------------- ------------------------------
3 ORCLPDB ORCLPDB
Where dynamic view CDB_SERVICES is derived from DBA_SERVICES.
Add a Service to PDB
Create a service
SQL> exec dbms_service.create_service('ERP', 'ERP');
PL/SQL procedure successfully completed.
The service is added to current PDB, no matter a single-instance or a RAC database.
There're more variations on adding a failover service to the cluster by DBMS_SERVICE.
Start the service
SQL> exec dbms_service.start_service('ERP');
PL/SQL procedure successfully completed.
For RAC database, you should start the services on all instances in the cluster:
SQL> exec dbms_service.start_service('ERP', DBMS_SERVICE.ALL_INSTANCES);
PL/SQL procedure successfully completed.
DBMS_SERVICE.START_SERVICE Automatically in PDB
To automatically start the service after PDB is open, you just need to save current state for the PDB.
SQL> alter pluggable database orclpdb save state;
Pluggable database altered.
For RAC database, you should save the state of PDB on all instances in the cluster:
SQL> alter pluggable database orclpdb save state instances=all;
Pluggable database altered.
There're more variations on saving state of a PDB or all PDB.
Check services of the container
Let's see current services of the container after adding a service to it.
SQL> select con_id, pdb, network_name from cdb_services where pdb is not null and con_id > 2 order by pdb;
CON_ID PDB NETWORK_NAME
------ --------------- ------------------------------
3 ORCLPDB ERP
3 ORCLPDB ORCLPDB
Let's check online services of current PDB.
SQL> column name format a30;
SQL> column network_name format a30;
SQL> select name, network_name from v$active_services;
NAME NETWORK_NAME
------------------------------ ------------------------------
orclpdb orclpdb
ERP ERP
For RAC databases, you may see the information like this:
SQL> select inst_id, name, network_name from gv$active_services;
INST_ID NAME NETWORK_NAME
---------- ------------------------------ ------------------------------
1 orclpdb orclpdb
1 ERP ERP
2 orclpdb orclpdb
2 ERP ERP
Check Listener Again
Let's see current status of listener.
[oracle@test ~]$ lsnrctl status
...
Services Summary...
Service "ERP" has 1 instance(s).
Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
Service "ORCLCDB" has 1 instance(s).
Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
Service "ORCLCDBXDB" has 1 instance(s).
Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
Service "aa736f65d66215cce053992aa8c08959" has 1 instance(s).
Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
Service "orclpdb" has 1 instance(s).
Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
The command completed successfully
We have a new service which registers with the new listener.
Connection Test
We use SQL*Plus to test the connection from an external client.
C:\Users\scott>sqlplus hr/[email protected]:1521/erp
...
SQL> select count(*) from employees;
COUNT(*)
----------
107
We have connected to the PDB.
Remove Service
If you want to remove the service from the PDB, you have to stop it first.
SQL> show con_name
CON_NAME
------------------------------
ORCLPDB
SQL> exec dbms_service.stop_service('ERP', DBMS_SERVICE.ALL_INSTANCES);
PL/SQL procedure successfully completed.
Then delete it.
SQL> exec dbms_service.delete_service('ERP');
PL/SQL procedure successfully completed.
If you don't stop it before deleting it, you shall see ORA-44305: service is running.
Srvctl Add Service to PDB
srvctl add service
For a RAC database, you can use the following command to create a service for a PDB.
[oracle@primary01 ~]$ srvctl add service -d orclcdb -s CRM -pdb ORCLPDB -preferred ORCLCDB1,ORCLCDB2
Please note that, the preferred instance names should be listed as uppercase ones.
There're more variations on adding a failover service to the cluster by srvctl.
srvctl config service
We should make sure all services in the database is correctly configured.
[oracle@primary01 ~]$ srvctl config service -d orclcdb
Service name: CRM
Server pool:
Cardinality: 2
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Global: false
Commit Outcome: false
Failover type:
Failover method:
Failover retries:
Failover delay:
Failover restore: NONE
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: NONE
Edition:
Pluggable database name: ORCLPDB
Hub service:
Maximum lag time: ANY
SQL Translation Profile:
Retention: 86400 seconds
Replay Initiation Time: 300 seconds
Drain timeout:
Stop option:
Session State Consistency: DYNAMIC
GSM Flags: 0
Service is enabled
Preferred instances: ORCLCDB1,ORCLCDB2
Available instances:
CSS critical: no
As we can see, the newly created service belongs to the PDB.
srvctl start Service
At least 2 parameters should be added to the command to start the service.
[oracle@primary01 ~]$ srvctl start service -d orclcdb -s crm
We don't have to specify which PDB should be used in the command.
srvctl status service
Then we check its status.
[oracle@primary01 ~]$ srvctl status service -d orclcdb -s crm
Service CRM is running on instance(s) ORCLCDB1,ORCLCDB2
As we can see, we started the service on both nodes.
Now we should check the statuses of both listeners.
lsnrctl status on Node 1
[oracle@primary01 ~]$ lsnrctl status
...
Service "CRM" has 1 instance(s).
Instance "ORCLCDB1", status READY, has 1 handler(s) for this service...
lsnrctl status on Node 2
[oracle@primary02 ~]$ lsnrctl status
...
Service "CRM" has 1 instance(s).
Instance "ORCLCDB2", status READY, has 1 handler(s) for this service...
The service is ready to accept connections.
srvctl stop service
If you want to remove service from the pluggable database, you have to stop it first.
[oracle@primary01 ~]$ srvctl stop service -d orcl -s crm
[oracle@primary01 ~]$ srvctl status service -d orcl -s crm
Service CRM is not running.
srvctl remove service
We can remove the service once it's no longer used.
[oracle@primary01 ~]$ srvctl remove service -d orcl -s crm
[oracle@primary01 ~]$ srvctl config service -d orcl
There's nothing left. We have completed the life cycle of a pluggable database service by the above 4 steps.