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

by Jure Bratina on Nov 5, 2020 12:00:00 AM

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.

UPDATE, APRIL 2021

The bug description is accessible in this My Oracle Support document: "Bug 32032887 - Unable to Start Service on An Instance and Receive ORA-44773 (Doc ID 32032887.8)"

Topics: SQL Server Microsoft SQL Server Technical Track Michelle Gutzait October 8Th 2015

No Comments Yet

Let us know what you think

Share this