Issues With Database Service Names Starting With Pluggable Database (PDB) Name

Posted in: Oracle, Technical Track

In this post I’ll describe a problem which occurs when creating and using a database service name which starts with the pluggable database’s (PDB) name, and has a dot (“.”) after it: “<pdb_name>.<optional_suffix>.” I noticed this issue when I was trying to reset an expired user’s password by connecting to that service.

I’ll also describe other anomalies, for example, being able to stop the service, but not start it, as well as the service remaining active even if the PDB gets dropped. I also acknowledge there may be other side effects in addition to those I describe here. The workaround is relatively simple — don’t create services with the format “<pdb_name>.<optional_suffix>.” Or, said differently, don’t start the service name with “<pdb_name>.”

It’s worth noting that the problem reproduces on 18c and 19c, but not on 12.1 and 12.2.

As of now, the development team has created a predefect — “Bug 32009641: ORA-01017 WHEN CONNECTING TO A PDB USING A SERVICE NAME WHICH STARTS WITH THE PDB” — though at the time of writing this post, it’s not published yet, as the team has to confirm whether it is a bug, and whether it can be published.

While it’s unlikely that many users will run into the issue described in this post, it’s good to be aware of it, and to avoid creating service names starting with “<pdb_name>.” until this issue is fixed. Mine is only a simple use case, and there could be other side effects not discussed here.

Setting up the test environment to reproduce the problem

I performed the steps below on a 19.8.0 DB; no ASM or GI managed resources are present. I start with a new PDB named PDB1, and open it:

SQL> create pluggable database pdb1 admin user pdb_adm identified by oracle create_file_dest='/u02/oradata/CDB1';

Pluggable database created.

The default CDB services are started, and PDB1’s default service “pdb1,” associated with CON_ID=3. This identifies the initial current container for the service:

CDB$ROOT:

SQL> select con_id, name from v$services order by con_id, name;

    CON_ID NAME
---------- ---------------
         1 CDB2
         1 CDB2XDB
         1 SYS$BACKGROUND
         1 SYS$USERS
         3 pdb1

SQL> select con_id, name from cdb_services order by con_id, name;

    CON_ID NAME
---------- -----------------
         1 CDB2
         1 CDB2.localdomain
         1 CDB2XDB
         1 SYS$BACKGROUND
         1 SYS$USERS
         3 PDB1

6 rows selected.

PDB1:

SQL> select con_id, name from v$services order by con_id, name;

    CON_ID NAME
---------- -----
         3 pdb1

SQL> select con_id, name from cdb_services order by con_id, name;

    CON_ID NAME
---------- -----
         3 PDB1

Next, I create and start services in PDB1. I chose service names to include various combinations of names using “pdb1” and “pdb1.”:

SQL> alter session set container=pdb1;

exec dbms_service.create_service(service_name=>'pdb1.test1',network_name=>'pdb1.test1');
exec dbms_service.create_service(service_name=>'test2.pdb1',network_name=>'test2.pdb1');
exec dbms_service.create_service(service_name=>'test3.pdb1.app',network_name=>'test3.pdb1.app');
exec dbms_service.create_service(service_name=>'pdb1_A',network_name=>'pdb1_A');
exec dbms_service.create_service(service_name=>'pdb1_B.app',network_name=>'pdb1_B.app');
exec dbms_service.create_service(service_name=>'custom_service.app',network_name=>'custom_service.app');

exec dbms_service.start_service(service_name=>'pdb1.test1');
exec dbms_service.start_service(service_name=>'test2.pdb1');
exec dbms_service.start_service(service_name=>'test3.pdb1.app');
exec dbms_service.start_service(service_name=>'pdb1_A');
exec dbms_service.start_service(service_name=>'pdb1_B.app');
exec dbms_service.start_service(service_name=>'custom_service.app');

I’ll be using the below script to display the services’ status:

SQL> !cat s.sql
set lin 500 pagesize 9999
col name for a50

show con_name
prompt
prompt ** Output from v$active_services: **
select con_id, name from v$active_services where lower(name) like '%pdb%' or lower(name) like '%app%' order by con_id, name;

prompt ** Output from cdb_services: **
select con_id, name from cdb_services where lower(name) like '%pdb%' or lower(name) like '%app%' order by con_id, name;

Let’s check the status of the above-created services:

CDB$ROOT:

** Output from v$active_services: **

    CON_ID NAME
---------- ------------------
         1 pdb1.test1        <<--
         3 custom_service.app
         3 pdb1
         3 pdb1_A
         3 pdb1_B.app
         3 test2.pdb1
         3 test3.pdb1.app

7 rows selected.

** Output from cdb_services: **

    CON_ID NAME
---------- ------------------
         3 PDB1
         3 custom_service.app
         3 pdb1.test1        <<--
         3 pdb1_A
         3 pdb1_B.app
         3 test2.pdb1
         3 test3.pdb1.app

PDB1:

** Output from v$active_services: **

    CON_ID NAME
---------- ------------------
         3 custom_service.app
         3 pdb1
         3 pdb1_A
         3 pdb1_B.app
         3 test2.pdb1
         3 test3.pdb1.app

6 rows selected.

** Output from cdb_services: **

    CON_ID NAME
---------- ------------------
         3 PDB1
         3 custom_service.app
         3 pdb1.test1
         3 pdb1_A
         3 pdb1_B.app
         3 test2.pdb1
         3 test3.pdb1.app

7 rows selected.

As you can see, the “pdb1.test1” service is apparently associated to the root container (v$active_services.con_id=1), and not with PDB1 as configured (cdb_services.con_id=3). All the other created services are being reported as running in PDB1 (v$active_services.con_id=3).

Why is this a problem?

Let’s create two local users in PDB1 — u1 and u2 — and set u1’s password to expired:

SQL> grant create session to u1 identified by oracle;
SQL> grant create session to u2 identified by oracle;
SQL> alter user u1 password expire;

If I try to connect as u2, everything works without problems:

$ sqlplus u2/oracle@localhost/pdb1.test1

SQL> select sys_context('userenv','cdb_name') cdb_name, sys_context('userenv','con_name') con_name, sys_context('userenv','service_name') service_name from dual;

CDB_NAME             CON_NAME             SERVICE_NAME
-------------------- -------------------- --------------------
CDB2                 PDB1                 pdb1.test1

However, if I try to connect to u1 and change its password, this is what happens:

$ sqlplus u1/oracle@localhost/pdb1.test1

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Oct 26 11:57:56 2020
Version 19.8.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

ERROR:
ORA-28001: the password has expired


Changing password for u1
New password:
Retype new password:
ERROR:
ORA-01017: invalid username/password; logon denied


Password unchanged
Enter user-name:

Analysis

Tracing the session with oradebug before entering the new password (with events 10046 and 10079), the session gets established via the pdb1.test1 service, and it’s traced under “CDB$ROOT(1)”:

*** 2020-10-26T12:01:31.706897+01:00 (CDB$ROOT(1))                    <<-- traced at "CDB$ROOT(1)" level
*** SESSION ID:(269.54910) 2020-10-26T12:01:31.706934+01:00
*** SERVICE NAME:(pdb1.test1) 2020-10-26T12:01:31.706937+01:00        <<-- service pdb1.test1 was used when connecting
*** MODULE NAME:(sqlplus@hol.localdomain (TNS V1-V3)) 2020-10-26T12:01:31.706940+01:00
*** ACTION NAME:() 2020-10-26T12:01:31.706943+01:00
*** CONTAINER ID:(1) 2020-10-26T12:01:31.706945+01:00

Not going into the details of the trace, we might get a clue at what’s happening by checking the audit log.

From PDB1:

SQL> select * from (
select ntimestamp#, userid, userhost, terminal, action#, returncode, current_user, comment$text
from sys.aud$
where userid = 'U1'
  and returncode <> 0
order by ntimestamp# desc
) where rownum = 1;

NTIMESTAMP#                    USERID     USERHOST             TERMINAL          ACTION# RETURNCODE CURRENT_USER    COMMENT$TEXT
------------------------------ ---------- -------------------- --------------- --------- ---------- --------------- ----------------------------------------------------------------------
26-OCT-20 10.57.56.687258 AM   U1         hol.localdomain      pts/6                 100      28001 U1              Authenticated by: DATABASE;AUTHENTICATED IDENTITY: U1; Client address:
                                                                                                                     (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=58922))

From CDB$ROOT:

SQL> select * from (
select ntimestamp#, userid, userhost, terminal, action#, returncode, current_user, comment$text
from sys.aud$
where userid = 'U1'
  and returncode <> 0
order by ntimestamp# desc
) where rownum = 1;  2    3    4    5    6    7

NTIMESTAMP#                    USERID   USERHOST         TERMINAL   ACTION# RETURNCODE CURRENT_USER    COMMENT$TEXT
------------------------------ -------- ---------------- ---------- ------- ---------- --------------- -----------------------------------------------------------------
26-OCT-20 11.01.32.725820 AM   U1       hol.localdomain  pts/6          100       1017 U1              Authenticated by: DATABASE;AUTHENTICATED IDENTITY: U1; Client add
                                                                                                       ress: (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=58922))

As you can see, when entering the password for the u1 user, we get “ORA-28001: the password has expired,” which is recorded in the PDB’s audit log. Then, after changing the password, an attempt is made to connect to the root container as u1. Since u1 is not a common user, the login fails, and we get the “ORA-01017: invalid username/password; logon denied” error.

What happens if I try to repeat the same steps using the “test2.pdb1” service name, which according to v$active_services maps to CON_ID=3, so PDB1?

This time the password change succeeds:

$ sqlplus u1/oracle@localhost/test2.pdb1

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Oct 26 12:38:04 2020
Version 19.8.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

ERROR:
ORA-28001: the password has expired


Changing password for u1
New password:
Retype new password:
Password changed

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0

SQL>

Enabling tracing on the session before the initial password is provided, this time the trace gets generated in the context of “PDB1(3).” Notice the difference with the previous trace header, where “CDB$ROOT(1)” was reported:

*** 2020-10-26T12:39:18.045405+01:00 (PDB1(3))                     <<-- traced at "CDB$ROOT(1)" level
*** SESSION ID:(269.12918) 2020-10-26T12:39:18.045547+01:00
*** SERVICE NAME:(test2.pdb1) 2020-10-26T12:39:18.045557+01:00     <<-- service test2.pdb1 was used when connecting
*** MODULE NAME:(sqlplus@hol.localdomain (TNS V1-V3)) 2020-10-26T12:39:18.045564+01:00
*** ACTION NAME:() 2020-10-26T12:39:18.045570+01:00
*** CONTAINER ID:(3) 2020-10-26T12:39:18.045574+01:00

Checking PDB1’s audit log contents, there’s an initial ORA-28001, followed by RETURNCODE=0, thus a successful login:

SQL> select * from (
select ntimestamp#, userid, userhost, terminal, action#, returncode, current_user, comment$text
from sys.aud$
where userid = 'U1'
order by ntimestamp# desc
) where rownum <=2;  2    3    4    5    6

NTIMESTAMP#                    USERID   USERHOST         TERMINAL   ACTION# RETURNCODE CURRENT_USER    COMMENT$TEXT
------------------------------ -------- ---------------- ---------- ------- ---------- --------------- -----------------------------------------------------------------
26-OCT-20 11.39.18.069634 AM   U1       hol.localdomain  pts/6          100          0 U1              Authenticated by: DATABASE;AUTHENTICATED IDENTITY: U1; Client add
                                                                                                       ress: (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=59244))

26-OCT-20 11.38.04.992001 AM   U1       hol.localdomain  pts/6          100      28001 U1              Authenticated by: DATABASE;AUTHENTICATED IDENTITY: U1; Client add
                                                                                                       ress: (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=59244))

There are no new entries for the u1 user in CDB$ROOT’s audit log.

Other anomalies

Start / stop service

There are also other anomalies, e.g. the ‘pdb1.test1’ service can be started and stopped from PDB1, but then not started back:

From PDB1:

SQL> exec dbms_service.stop_service(service_name=>'pdb1.test1');

PL/SQL procedure successfully completed.

Querying v$active_services from PDB1 or CDB$ROOT, the service is not displayed. However, if I try to start it:

SQL> exec dbms_service.start_service(service_name=>'pdb1.test1');
BEGIN dbms_service.start_service(service_name=>'pdb1.test1'); END;

*
ERROR at line 1:
ORA-44773: Cannot perform requested service operation.
ORA-06512: at "SYS.DBMS_SERVICE_ERR", line 63
ORA-06512: at "SYS.DBMS_SERVICE", line 486
ORA-06512: at line 1


SQL>

Closing and / or dropping PDB1

Going to the initial setup, where all of the services I defined, except “pdb1.test1”, are mapped to PDB1:

SQL> @s

CON_NAME
------------------------------
CDB$ROOT

** Output from v$active_services: **

    CON_ID NAME
---------- -------------------
         1 pdb1.test1
         3 custom_service.app
         3 pdb1
         3 pdb1_A
         3 pdb1_B.app
         3 test2.pdb1
         3 test3.pdb1.app

7 rows selected.

** Output from cdb_services: **

    CON_ID NAME
---------- -------------------
         3 PDB1
         3 custom_service.app
         3 pdb1.test1
         3 pdb1_A
         3 pdb1_B.app
         3 test2.pdb1
         3 test3.pdb1.app

7 rows selected.

Let’s close PDB1, and check the services status:

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> alter pluggable database pdb1 close immediate;

Pluggable database altered.

SQL> @s

CON_NAME
---------
CDB$ROOT

** Output from v$active_services: **

    CON_ID NAME
---------- -----------
         1 pdb1.test1
         3 pdb1

** Output from cdb_services: **

no rows selected

SQL>

All of the user created services were closed, except for pdb1.test1. If I try to connect to PDB1 as user u2, I get “ORA-01109: database not open.”

Let’s drop the PDB:

SQL> drop pluggable database pdb1 including datafiles;

Pluggable database dropped.

SQL> @s

CON_NAME
---------
CDB$ROOT

** Output from v$active_services: **

    CON_ID NAME
---------- -----------
         1 pdb1.test1

** Output from cdb_services: **

no rows selected

SQL>

Connecting to PDB1 as user u2 now returns “ORA-01017: invalid username/password; logon denied” which indicates that an authentication attempt is performed. Since only CDB$ROOT is open, let’s see if I can connect as system:

$ sqlplus system/oracle@localhost/pdb1.test1

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Oct 26 13:05:59 2020
Version 19.8.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

Last Successful login time: Mon Oct 26 2020 11:38:01 +01:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL>

Conclusion

The above are relatively simple use cases, and until this issue is fixed, it’s advisable to avoid creating service names starting with “<pdb_name>.” as there could be other implications not discussed here. You may also want to keep an eye on the status of the predefect tracked as Bug 32009641.

email

Authors

Interested in working with Jure? Schedule a tech call.

About the Author

Oracle Database Consultant
Jure Bratina is an experienced Oracle DBA currently working as a Database Consultant with The Pythian Group. The areas he most likes to work on are troubleshooting and performance tuning of Oracle databases. Jure is also a member of the Executive Committee at the Slovenian Oracle User Group (SIOUG).

No comments

Leave a Reply

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