By William Sescu

Have you ever experienced the following error?

SQL> alter pluggable database pdb2 open;
alter pluggable database pdb2 open
*
ERROR at line 1:
ORA-44304: service  does not exist
ORA-44777: Pluggable database service cannot be started.

The error says, that Oracle is not able to open the pluggable database because a service is missing. At least the default service, which is the name of the pluggable database itself should be there. But in my case, it is not.  To be honest, I was not aware, that Oracle even allows it, to drop the default service which comes with every PDB. But it is possible.

Let’s create a simple test case.

SQL> create pluggable database pdb2 admin user pdb2admin identified by manager ROLES = (dba);

Pluggable database created.

SQL> alter pluggable database pdb2 open;

Pluggable database altered.

SQL> select con_id, name from v$services where con_id = 5;

    CON_ID NAME
---------- ----------------------------------------------------------------
         5 pdb2

Looks good. Have created a pluggable database named pdb2 and it comes with a default service named pdb2. For this test case, I am not using any dictionary hacks or any other crazy stuff like that. I am using the standard dbms_service package to delete my default service.

-- Delete service manually

SQL> alter session set container=pdb2;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
PDB2

SQL> exec dbms_service.STOP_SERVICE(SERVICE_NAME=>'PDB2');

PL/SQL procedure successfully completed.

SQL> exec dbms_service.DELETE_SERVICE(SERVICE_NAME=>'PDB2');

PL/SQL procedure successfully completed.

After dropping the service out of the pdb, the CDB still shows it in the v$services view.

SQL> select con_id, name from v$services where con_id = 5;

    CON_ID NAME
---------- ----------------------------------------------------------------
         5 pdb2

Ok. Let’s shutdown the pdb and start it again.

SQL> alter pluggable database pdb2 close;

Pluggable database altered.

SQL> alter pluggable database pdb2 open;
alter pluggable database pdb2 open
*
ERROR at line 1:
ORA-44304: service  does not exist
ORA-44777: Pluggable database service cannot be started.

SQL>

Here we go. Now I got the ORA-44777 and the service is also gone.

SQL> select con_id, name from v$services where con_id = 5;

no rows selected

What is strange, is that the pdb was opened read-write, however, I am not able to connect to it in any way. Of course, not with sqlplus via service, but also not via the alter session set container command. Oracle immediately kicks me out with the famous ORA-03113 error.

SQL> select name, open_mode from v$pdbs where name = 'PDB2';

NAME OPEN_MODE
-------------------------------- ----------
PDB2 READ WRITE

SQL> alter session set container=pdb2;
alter session set container=pdb2
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 4926
Session ID: 46 Serial number: 58059

But now, that we got the issue, and someone accidently dropped the default service, the question is how to recover from it? The only workaround I know, is to unplug and plug it again. By plugging a PDB in, Oracle automatically creates the service.

SQL> alter pluggable database pdb2 close;

Pluggable database altered.

SQL> alter pluggable database pdb2 unplug into '/u01/app/oracle/admin/CDB/xml/pdb2.xml';

Pluggable database altered.

SQL> select name, open_mode from v$pdbs where name = 'PDB2';

NAME                     OPEN_MODE
------------------------ ----------
PDB2                     MOUNTED

SQL> drop pluggable database PDB2;

Pluggable database dropped.

SQL> select name, open_mode from v$pdbs where name = 'PDB2';

no rows selected

SQL> create pluggable database pdb2 using '/u01/app/oracle/admin/CDB/xml/pdb2.xml' nocopy tempfile reuse;

Pluggable database created.

SQL> alter pluggable database pdb2 open;

Pluggable database altered.

SQL> select name, open_mode from v$pdbs where name = 'PDB2';

NAME                     OPEN_MODE
------------------------ ----------
PDB2                     READ WRITE

SQL> select con_id, name from v$services where name = 'pdb2';

    CON_ID NAME
---------- ----------------------------------------------------------------
         6 pdb2

Conclusion

Applications should not work with the default PDB service which comes out of the box with any PDB. This service is for internal use only. And dropping the default service of the pluggable database is also not a good idea, even it works. 😉 From my point of view, Oracle should not allow a DBA to do that.