Managing Service in PDB

Managing Services

-----------------

a) Each PDB will be having a default services.

SQL> select name, pdb from cdb_services;

NAME                                                             PDB

---------------------------------------------------------------- ------------

hrdept                                                           HRDEPT

sal_pdb                                                          SAL_PDB

SYS$BACKGROUND                                                   CDB$ROOT

SYS$USERS                                                        CDB$ROOT

fircdbXDB                                                        CDB$ROOT

fir_cdb                                                          CDB$ROOT

Adding services to an pdb.

--------------------------

We can use dbms_service package to create services if the database is not managed using Oracle restart or Oracle Clusterware.

SQL> alter session set container=hrdept;

Session altered.

SQL> exec dbms_service.CREATE_SERVICE('hr_manager','hr_manager');

PL/SQL procedure successfully completed.

SQL> exec dbms_service.start_service('hr_manager');

PL/SQL procedure successfully completed.

SQL> select name, pdb from cdb_services;

NAME                                                             PDB

---------------------------------------------------------------- ------------------------------

hrdept                                                           HRDEPT

hr_manager                                                       HRDEPT

SQL> alter session set container=cdb$root;

Session altered.

SQL> select name, pdb from cdb_services;

NAME                                                             PDB

---------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------

hrdept                                                           HRDEPT

hr_manager                                                       HRDEPT

sal_pdb                                                          SAL_PDB

SYS$BACKGROUND                                                   CDB$ROOT

SYS$USERS                                                        CDB$ROOT

fircdbXDB                                                        CDB$ROOT

fir_cdb                                                          CDB$ROOT

SQL> ! lsnrctl status LISTENER_FIRCDB

LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 24-AUG-2015 09:12:59

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rhel11gr2rac1.manzoor.com)(PORT=1522)))

STATUS of the LISTENER

------------------------

Alias                     LISTENER_FIRCDB

Version                   TNSLSNR for Linux: Version 12.1.0.1.0 - Production

Start Date                24-AUG-2015 07:10:59

Uptime                    0 days 2 hr. 2 min. 0 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /oracle/product/12.1/db_1/network/admin/listener.ora

Listener Log File         /oracle/product/diag/tnslsnr/rhel11gr2rac1/listener_fircdb/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rhel11gr2rac1.manzoor.com)(PORT=1522)))

Services Summary...

Service "fir_cdb" has 1 instance(s).

  Instance "fircdb", status READY, has 1 handler(s) for this service...

Service "fircdbXDB" has 1 instance(s).

  Instance "fircdb", status READY, has 1 handler(s) for this service...

Service "hr_manager" has 1 instance(s).

  Instance "fircdb", status READY, has 1 handler(s) for this service...

Service "hrdept" has 1 instance(s).

  Instance "fircdb", status READY, has 1 handler(s) for this service...

Service "sal_pdb" has 1 instance(s).

  Instance "fircdb", status READY, has 1 handler(s) for this service...

The command completed successfully

The service hr_manager has been created and registered in the listener.

Deleting a service

------------------

SQL> alter session set container=hrdept;

Session altered.

SQL> exec dbms_service.STOP_SERVICE('hr_manager');

PL/SQL procedure successfully completed.

SQL> exec dbms_service.DELETE_SERVICE('hr_manager');

PL/SQL procedure successfully completed.

SQL> alter session set container=CDB$ROOT;

Session altered.

SQL> select name, pdb from cdb_services

NAME                                                             PDB

---------------------------------------------------------------- ---------------------

hrdept                                                           HRDEPT

sal_pdb                                                          SAL_PDB

SYS$BACKGROUND                                                   CDB$ROOT

SYS$USERS                                                        CDB$ROOT

fircdbXDB                                                        CDB$ROOT

fir_cdb                                                          CDB$ROOT

6 rows selected.

Connections to the PDB should be made via tns or via Ez-connect.

Chaning the Containers.

SQL> alter session set container = PDB_PURCHASE;

Session altered.

SQL> show con_name;

CON_NAME

------------------------------

PDB_PURCHASE

SQL> alter session set container = PDB_HR;

Session altered.

SQL> show con_name;

CON_NAME

------------------------------

PDB_HR

When using alter session set container below scenario will be applied.

a) Alter session set container can be executed only by the common user who has given set container privilege.

b) Be aware after logon triggers will not fire.

c) Transaction that are not committed nor rollbacked after switching from one containr to another container.