APP_USER Not Created in XE on 18-slim

[theory]

I got a GitHub Workflow working for 11-slim but not 18-slim. Here is the run. The error on 18-slim is:

ORA-01918: user 'SQITCHTEST' does not exist

This user should be created by the service configuration:

    services:
      oracle:
        image: gvenzl/oracle-xe:${{ matrix.oracle.version }}-slim
        ports: [ 1521 ]
        env:
          ORACLE_PASSWORD: sqitchtest
          APP_USER: sqitchtest
          APP_USER_PASSWORD: sqitchtest
        options: >-
          --health-cmd healthcheck.sh
          --health-interval 20s
          --health-timeout 10s
          --health-retries 10

This works well in 11-slim but not 18-slim. Note that for both I'm using the SID XE; maybe the user is created in some other database in 18-slim? I've tried connecting to XEPDB1 instead, but that does not seem to exist, returning

ORA-12505: TNS:listener does not currently know of SID given in connect descriptor (DBD ERROR: OCIServerAttach)

[theory]

I see looking at the entry point that the user is created in XEPDB1 on 18-slim; Is there a SID I can use to connect to that database instead?

[gvenzl]

Hi @theory, Oracle Database changed its architecture in 12c to provide what's called Pluggable Database. The new architecture is known as Multitenant Architecture where one Oracle Database instance (RDBMS, now known as CDB or Container Database) can manage many databases (now known as PDBs or Pluggable Databases). User data can hence no longer reside on the SID (now the CDB) level but has to reside in a pluggable database (although there is a concept of common users, but that's more to provide users that can connect across pluggable databases).

As you correctly stated, the user is being created inside the XEPDB1 pluggable database. The reason why you are getting TNS:listener does not currently know of SID given in connect descriptor is because an Oracle Database instance has only one SID, XE in this case. The error is informing the user that the Oracle DB Listener does not know of a SID called XEPDB1, which is correct because it doesn't exist. Instead, the XEPDB1 pluggable database is exposed via a Net Service called XEPDB1. To connect via that service name instead of the SID one usually needs to change the :<SID> to /<service name> at the end of the connect string (notice the / instead of the :).

[theory]

Him, sounds like I might need to update URI::oracle to support this case. Right now it assumes the path part of the URI is a SID, e.g.,

db:oracle://localhost/XEPDB1

Converts XEPDB1 to sid=XEPDB1 in the DBD::Oracle connection string. I'll have to think about how to tell a SID from a net service name.

[gvenzl]

Connecting to a SID is generally discouraged these days, although I do not find an explicit statement in the documentation right now – action item for Monday :)
Connecting to the SID was the way to go prior to Oracle 8.1 (release 1998).
Starting with Oracle 9i (released in 2001), the recommended approach to connect was via service names instead of the SID which has several advantages. A good write-up and the reasons can be found under Oracle Database Features That Use Database Services of the Oracle Database Administrator’s Guide. In a nutshell, there are a couple of advantages by using services :

• A database can have many service names, hence allowing to logically partition workloads from different applications. For example, the sales system could connect to the SALES service, the end-of-week reporting to the REPORTING service and the delivery system to the DELIVERY service.
• Different resource management profiles can be associated with different services which is one of the reasons why partitioning the workloads like above now makes sense. The SALES service and all connections connecting via that service could have the highest priority to be scheduled on the CPU, etc. while the connection coming in via the REPORTING service may be allowed to run SQL statements in parallel to speed up the report, etc. More about consumer groups and how a service name can be leveraged to associate incoming connections/sessions with a consumer group can be found in the Oracle Database Administrator's Guide.
• The SID identifies a given Oracle Database instance. In a clustered environment with Real Application Clusters that means that the SID points to one specific instance, i.e. node, of the cluster. An application connecting via the SID would hence connect to a specific instance of the cluster and would never be able to failover to another instance or be load-balanced across cluster nodes.
• Likewise, as the SID connects to the Oracle Database instance, an application will never be able to connect to a pluggable database.

Note that during database creation a default service name is created that has the same name as the SID, meaning that applications can still connect to the instance if they like, via a service name. This is the reason why, e.g., @localhost:1521:XE and @localhost:1521/XE both work, just that the former connects via the SID while the latter connects to a service named XE that happens to service the SID.

Hope this helps to give you some background!

I definitely recommend URI::oracle to be enhanced to support service names.
Without supporting a service name an application will never be able to:

• Connect to a pluggable database
• Failover and/or load balance across cluster nodes
• Connect to an Autonomous Database on Shared Infrastructure in the Oracle Cloud

[theory]

Those are some delightfully detailed notes, @gvenzl. I appreciate it as an Oracle dilettante. From my point of view, there are three things I care about:

• Connecting to an Oracle database in for testing, e.g., in this GitHub workflow. For this use-case, it makes no different whether I connect via a SID or a service name, as long as I can connect.
• Figuring out how to represent a service name in a DBD::Oracle DSN. DBD-Oracle#131 suggests it's not possible without using TWO_TASK, but it sure seems to me like it should be do-able with a service name. Once I work that out, I can update URI::oracle to generate the appropriate DSN. Perhaps the path part of the URI should cease to be considered a SID, but a service name, instead, and tell anyone using it to use sid=xxx in the Query string to use the old behavior. I think that would be fine.
• Accurately documenting how to configure these things for Sqitch users in the tutorial, something like this.

The second bullet is the key one right now; requiring the use of TWO_TASK seems fussier than I'd like.

[gvenzl]

Hey @theory, you are very welcome!

Can you point me to DBD-Oracle#131, please? I can't find it via Google but would like to take a look. Other drivers just create a DSN and use service_name= instead of sid=, for example Python:

For example, to connect to the Oracle Database service orclpdb1 that is running on the host dbhost.example.com with the default Oracle Database port 1521, use:

dsn = cx_Oracle.makedsn("dbhost.example.com", 1521, service_name="orclpdb1")
connection = cx_Oracle.connect(user="hr", password=userpwd, dsn=dsn,
                               encoding="UTF-8")

Note the use of the named argument service_name. By default, the third parameter of makedsn() is a database System Identifier (SID), not a service name. However, almost all current databases use service names.

The value of dsn in this example is the connect descriptor string:

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbhost.example.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orclpdb1)))

But frankly, most just use an EZConnect string these days, best explained over here: https://www.orafaq.com/wiki/EZCONNECT

[theory]

Oh, sorry, I edited too much of the link. It's perl5-dbi/DBD-Oracle#131.

[theory]

And now I'm searching for "service" in the docs and of course it's right there. 🤦🏻

The connection string is always of the form: "dbi:Oracle:" There are several ways to identify a database:

1. If the database is local, specifying the SID or service name will be enough.
2. If the database is defined in a TNSNAMES.ORA file, you can use the service name given in the file
3. To connect without TNSNAMES.ORA file, you can use an EZCONNECT url, of the form: //host[:port][/service_name]

Feeling like an idiot.

[gvenzl]

Nah, it's Sunday night for you, all good! :)
Besides, where is the fun if we don't have these "forest for the trees" moments from time to time ;)

[gvenzl]

Hey @theory, do you mind if I add sqitch to the list of Users here: https://github.com/gvenzl/oci-oracle-xe/blob/main/README.md#users-of-these-images

[theory]

Not at all, go for it.

[gvenzl]

Awesome, thanks a lot!

[gvenzl]

And added! :)