How to Add Services to PDB

  • by

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:

  1. DBMS_SERVICE Create Service
  2. It's an administration package for managing services of a database.

  3. SRVCTL Add Service
  4. For RAC databases or Oracle Restart, we can use it to manage services.

A. DBMS_SERVICE Create Service

For single-instance, we can only use DBMS_SERVICE to add services to a PDB.

1. 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.

2. 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 

3. 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.

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.

Save current state of this container.

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

4. 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.

5. Connection Test

We use SQL*Plus to test the connection from an external client.

C:\Users\edchen>sqlplus hr/hr@192.168.0.11:1521/erp
...
SQL> select count(*) from employees;

  COUNT(*)
----------
       107

We have connected to the PDB.

6. 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.

B. SRVCTL Add Service

1. Add a 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

There're more variations on adding a failover service to the cluster.

2. Start the Service

[oracle@primary01 ~]$ srvctl start service -d orclcdb -s crm

3. Check the Service

[oracle@primary01 ~]$ srvctl status service -d orclcdb -s crm
Service CRM is running on instance(s) ORCLCDB1,ORCLCDB2

Now we should check the statuses of both listeners.

Node 1

[oracle@primary01 ~]$ lsnrctl status
...
Service "CRM" has 1 instance(s).
  Instance "ORCLCDB1", status READY, has 1 handler(s) for this service...

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.

Leave a Reply

Your email address will not be published. Required fields are marked *