Persistent, cross database and cross region pipe in Oracle Database 23ai

We know pipes in Oracle Database to be an in memory mechanism, ephemeral with loss of data when the database instance is stopped. With limitations on size of messages (4KB until 12.1, 8KB as of 12.2). And messages in pipes are available only within the database instance in which they have been sent.

In Oracle Autonomous Database 23ai, there is a new type of pipe. With messages backed by Cloud Object Storage. With messages that can be very large, that are persisted across database instance restart and that can be shared between databases, even databases in different cloud regions.

The messages published to a pipe are persistently stored in Cloud Object Store– and this allows one or more database sessions to communicate in the same region or across regions. This makes it possible to send and retrieve very large messages, send a large number of pipe messages and send and receive messages within a single database, across multiple databases and across databases in different regions.

DBMS_PIPE uses DBMS_CLOUD to access Cloud Object Store. Messages can be stored in any of the supported Cloud Object Stores – including Oracle Cloud Infrastructure, Amazon S3, Azure Blob Storage.

To use a pipe (either to send or to receive) that is backed by cloud object storage, a credential object is required for the specified cloud object storage endpoint; the database user that creates the pipe requires execute privilege on this credential object. Note: for a database running on OCI, access can also be granted through a service principal (a combination of a dynamic group that contains the database and permissions to that group to manage buckets on OCI Object Storage. Even easier is the use of a Pre Authenticated Request URL on an OCI Object Storage bucket. By using such a PAR Url – no dynamic group or policy for permissions are required at all. Simply tell dbms_pipe where to store the persisted messages using this URL and it will happily write the messages to the bucket.

The simplest sequence of steps for my 23ai Autonomous Database for a pipe backed by a local (same OCI region) object storage now become:

Create a bucket to store the piped messages in

Create a read-write enabled Pre Authenticated Request URL for this bucket

Create a Pipe

At this point, to mention of persistence and backing object storage

DECLARE

  l_status INTEGER;

BEGIN

  l_status := DBMS_PIPE.CREATE_PIPE(

                pipename => ‘MY_PERSISTENT_PIPE’,

                private => FALSE  — public pipe

              );

END;

Pack and Send a Message – set the location_url to the Pre Authenticated Request URL

(optionally send multiple messages)

Each message should be written to the bucket on OCI Object Storage and will remain there – regardless of database restarts – until read again. However, it seems that only the first message I send to the pipe gets really written to the bucket. Note: after I manually remove the lock file, the second message is written as well.

On inspecting the message that is written:

and the contents:

And of the lock file:

Receive (and Unpack) a Message from this pipe – set the location_url to the Pre Authenticated Request URL

Here my experiment fails. Using both the Pre Authenticated Request URL and a dynamic group that contains the database and has policies that grant permission to manage buckets and object, I keep running into:

ORA-20404: Object not found – https://objectstorage.us-ashburn-1.oraclecloud.com/n/idtwlqf2hanz/b/bucket-database-pipe-storage/o/MY_PERSISTENT_PIPE.lock ORA-06512: at “SYS.DBMS_PIPE”, line 913 ORA-06512: at “SYS.DBMS_PIPE”, line 452 ORA-06512: at “C##CLOUD$SERVICE.DBMS_CLOUD”, line 2060

I wonder how the code knows which file to look for – does it keep track (I do not think so because the pipe can also be read on different databases)? Does it inspect the bucket’s contents prior to trying to read the file? And if it could read the directory contents, how could it not also read the file itself?

The corresponding message file should now be removed from OCI Object Storage. Unless the pipe is a singleton – that retains the message regardless how many times it is read. Since my experiment failed earlier on, I never got to this stage.

One problem seems to be that the lock file is created as it should but is then not removed as it should.

Resources

Oracle Database documentation – Use Persistent Messaging with Messages Stored in Cloud Object Store –  Docs.

Oracle Database docs – DBMS_CLOUD – https://docs.oracle.com/en/cloud/paas/autonomous-database/serverless/adbsb/file-uri-formats.html#GUID-26978C37-BFCE-4E0B-8C39-8AF399F2067B

Oracle Database docs – Use Resource Principal to Access Oracle Cloud Infrastructure Resources – https://docs.oracle.com/en/cloud/paas/autonomous-database/serverless/adbsb/resource-principal.html#GUID-D4FEBDB0-B9CD-45B2-8DBE-A83EFE1DD8A2