ORDS Issues in Oracle 19c? Check your Service Names

We upgraded our first database from 12.2 to 19c last week, and encountered a nasty issue with ORDS.  Credit goes to my colleagues Mingda Lu and Au Chun Kei for doing the hard work in understanding what was causing the issue.

The issue can be demonstrated with the Oracle DB Developer VM.  I have created a RESTful Web Service following the oracle-base guide.

A quick test with wget shows that everything is OK with the default settings:

[oracle@localhost ~]$ wget http://localhost:8080/ords/hr/hrmod/employees/100
--2020-02-15 04:23:46-- http://localhost:8080/ords/hr/hrmod/employees/100
Resolving localhost (localhost)... 127.0.0.1
Connecting to localhost (localhost)|127.0.0.1|:8080... connected.
HTTP request sent, awaiting response... 200 OK

Note however that ORDS is configured with the PDB default service name ‘orcl’

[oracle@localhost ~]$ grep servicename /u01/userhome/oracle/ords/vmconfig/ords/defaults.xml
<entry key="db.servicename">orcl</entry>

What happens if use a different service name for ORDS?  I’ll create a couple of services to demonstrate the issue.

[oracle@localhost ~]$ sql system/oracle@localhost:1521/orcl

SQLcl: Release 19.1 Production on Sat Feb 15 04:58:57 2020
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Last Successful login time: Sat Feb 15 2020 04:59:02 -05:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> exec sys.dbms_service.create_service('orcl_ords', 'orcl_ords');
PL/SQL procedure successfully completed.

SQL> exec sys.dbms_service.start_service('orcl_ords');
PL/SQL procedure successfully completed.

SQL> exec sys.dbms_service.create_service('orcl.ords', 'orcl.ords');
PL/SQL procedure successfully completed.

SQL> exec sys.dbms_service.start_service('orcl.ords');
PL/SQL procedure successfully completed.

If I change db.servicename entry to ‘orcl_ords’ in defaults.xml, restart ORDS and retest, all is OK.

[oracle@localhost wget http://localhost:8080/ords/hr/hrmod/employees/100
--2020-02-15 05:03:17-- http://localhost:8080/ords/hr/hrmod/employees/100
Resolving localhost (localhost)... 127.0.0.1
Connecting to localhost (localhost)|127.0.0.1|:8080... connected.
HTTP request sent, awaiting response... 200 OK

However if I change the service name to the other new service, orcl.ords, and restart ORDS, then it starts up without any problems, but when testing the service we get an error.

[oracle@localhost ~]$ wget http://localhost:8080/ords/hr/hrmod/employees/100
--2020-02-15 05:05:57-- http://localhost:8080/ords/hr/hrmod/employees/100
Resolving localhost (localhost)... 127.0.0.1
Connecting to localhost (localhost)|127.0.0.1|:8080... connected.
HTTP request sent, awaiting response... 503 Service Unavailable
2020-02-15 05:06:01 ERROR 503: Service Unavailable.

The error stacks from ORDS seems to gives some clues as to what’s going on.

WARNING: The database user for the connection pool named |apex|pu|, is not authorized to proxy to the schema named HR
oracle.dbtools.common.jdbc.ConnectionPoolConfigurationException: The database user for the connection pool named |apex|pu|, is not authorized to proxy to the schema named HR
at oracle.dbtools.common.jdbc.ConnectionPoolExceptions.from(ConnectionPoolExceptions.java:46)
at oracle.dbtools.common.jdbc.ConnectionPoolExceptions.from(ConnectionPoolExceptions.java:53)

Caused by: oracle.dbtools.common.ucp.ConnectionLabelingException: Error occurred when attempting to configure url: jdbc:oracle:thin:@//localhost:1521/orcl.ords with labels: {oracle.dbtools.jdbc.label.schema=HR}
at oracle.dbtools.common.ucp.LabelingCallback.handle(LabelingCallback.java:147)
at oracle.dbtools.common.ucp.LabelingCallback.proxyToSchema(LabelingCallback.java:210)
at oracle.dbtools.common.ucp.LabelingCallback.configure(LabelingCallback.java:76)

Caused by: java.sql.SQLException: ORA-01017: invalid username/password; logon denied
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:494)
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:441)
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:436)
at oracle.jdbc.driver.T4CTTIfun.processError(T4CTTIfun.java:1027)

[oracle@localhost ords]$ sql ords_public_user[hr]/oracle@localhost:1521/orcl.ords

SQLcl: Release 19.1 Production on Sat Feb 15 05:09:46 2020

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

Last Successful login time: Fri May 31 2019 16:29:03 -04:00

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


SQL>

From our testing, any service with a name with the format <pdb_name>.<any_text> exhibits the problem. We have used service names with such a format in 12.2 without issues, so it seems this is new behaviour introduced in 18c or 19c.

We’ve also noticed that when checking v$services, the value for con_id for the ‘problem’ service is 1 which may give a clue as to what’s going on, although it only seems to cause a problem for ORDS.

SQL> select con_id, network_name from v$services where network_name in ('orcl_ords', 'orcl.ords');
CON_ID NETWORK_NAME
_________ _______________
1 orcl.ords
3 orcl_ords