SQL*Net and the CONTAINERS clause

Posted by

Complete separation of pluggable databases is naturally a good thing when it comes to protecting the rights and data of the stakeholders of those databases.  For example, on a shared infrastructure system, you really want that isolation from your unknown neighbours sharing the hardware.

But for many customers, in particular, those running on-premises, whilst the separation of pluggables is still important, there are also benefits to be gained from being able to view those databases as a single entity. The multi-tenant option provides this already for things like backups and background processes etc, but it is also possible access data from multiple pluggables with a single SQL using the CONTAINERS clause.

Here’s a quick example of that where the “DB19_CDB” connection equates to my root container, and “DB19_PDB1” equates to a pluggable database sitting within it.


SQL> conn system/admin@db19_cdb -- root container (CON=1)
Connected.

SQL> create table ttt ( x int );

Table created.

SQL> insert into ttt values (1);

1 row created.

SQL> commit;

Commit complete.

SQL>  select * from containers(ttt);

         X     CON_ID
---------- ----------
         1          1

SQL> insert into containers(ttt) (con_id,x) values (1,100);

1 row created.

SQL> commit;

Commit complete.

SQL> conn system/admin@db19_pdb1 -- pluggable (CON=3)
Connected.

SQL> create table ttt ( x int );

Table created.

SQL> insert into ttt values (2);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into containers(ttt) (con_id,x) values (3,200);

1 row created.

SQL> commit;

Commit complete.

SQL> conn system/admin@db19_cdb
Connected.

SQL> select * from containers(ttt);

         X     CON_ID
---------- ----------
         1          1
       100          1
         2          3
       200          3

4 rows selected.

I’ve created the table TTT in each container, and yes before you take me to task in the comments, I know I shouldn’t be using SYSTEM but I wanted to focus here on the CONTAINERS clause, not get distracted by a local versus common user discussion.

Notice some of the cool things about this facility

  • I automatically get a CON_ID column to tell me which container the row belongs to
  • When inserting a row, I can also reference the CON_ID column even those the table does not physically have that column defined.
  • We still protect the security of the data, namely, a pluggable database only sees its own data, whilst the root can span all of its child pluggables.  (You’ll see similar behaviour if you delve into the realm of application containers)

The fact that I can nominate the container ID when using an INSERT statement suggests the possibility of being able to insert data from the root into a pluggable database’s copy of the table. If you’re using a database configured with all of the defaults, then this will probably work straight out of the box. However, you might also get this


SQL> conn system/admin@db19_cdb
Connected.

SQL> insert into containers(ttt) (con_id,x) values (3,300);
insert into containers(ttt) (con_id,x) values (3,300)
*
ERROR at line 1:
ORA-12541: TNS:no listener

It seems very odd that I would encounter any kind of SQL*Net error given that this is all occurring within the same database on the same node. This is the first hint as to what the CONTAINERS clause might be doing “under the covers” to implement the cross-pluggable functionality.  If you take a look in the alert.log after you get this error, you’ll see messages similar to the following.


Fatal NI connect error 12541, connecting to:
 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=gtx)(PORT=1521))
 (CONNECT_DATA=(SERVICE_NAME=0cecdca0ba694c2ba0c9a4f691e29489)
 (CID=(PROGRAM=c:\oracle\product\19\bin\ORACLE.EXE)(HOST=GTX)(USER=hamcdc))))

Notice that there was a connection attempt to the host “gtx” (the laptop I’m running this demo on) through port 1521. That struck as very odd because the database and listener on this box are all configured to use a port of 1519! (I do this to allow me to run various versions of listeners and databases on the same machine – 1518 for 18c, 1519 for 19c and so forth). You can see from the database parameters and LSNRCTL output, the 1519 is the port I’ve configured.


SQL> show parameter local_liste

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------------
local_listener                       string      LISTENER_DB19

## from tnsnames.ora
LISTENER_DB19 =
  (ADDRESS = (PROTOCOL = TCP)(HOST = gtx)(PORT = 1519))



C:\>lsnrctl status listener19

LSNRCTL for 64-bit Windows: Version 19.0.0.0.0 - Production on 29-APR-2020 11:55:21

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=gtx)(PORT=1519)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER19
Version                   TNSLSNR for 64-bit Windows: Version 19.0.0.0.0 - Production
Start Date                20-APR-2020 10:07:08
Uptime                    9 days 1 hr. 48 min. 13 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   C:\oracle\product\19\network\admin\listener.ora
Listener Log File         C:\oracle\diag\tnslsnr\gtx\listener19\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=gtx)(PORT=1519)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1519ipc)))
Services Summary...
...
...

Thus the big question is – where does the usage of 1521 come from in the CONTAINERS query clause? After much digging around, I discovered that these are properties that can you see for the pluggable databases themselves:


SQL> ALTER PLUGGABLE DATABASE CONTAINERS PORT=1519;

Pluggable database altered.

SQL> ALTER PLUGGABLE DATABASE CONTAINERS HOST='gtx';

Pluggable database altered.

Once this was done, I could now run DML from the root into my pluggable database without getting the error.


SQL> insert into containers(ttt) (con_id,x) values (3,300);

1 row created.

The fact that this operation is being done over SQL*Net reveals another interesting discovery. It would appear that queries are being done via separate database connections, because whilst I have just inserted the value “300” in the INSERT command above, it does not appear in the subsequent query


SQL> select * from containers(ttt);

         X     CON_ID
---------- ----------
         1          1
       100          1
         2          3
       200          3

4 rows selected.

Only once I commit the transaction does the row appear in the CONTAINERS clause query.


SQL> commit;

Commit complete.

SQL> select * from containers(ttt);

         X     CON_ID
---------- ----------
         1          1
       100          1
         2          3
       200          3
       300          3

5 rows selected.

So keep these things in mind if you’re building application infrastructure around the CONTAINERS clause. It is not “just another SQL query” Smile

For a longer form treatment of the CONTAINERS clause, check out the video below.

One comment

  1. Hi Connor, thanks for the article -very interesting!
    Where in the datadictionary do these PDB containers properties show up?
    Would have expected them in DATABASE_PROPERTIES but that’s not the case (I’m on 19.8)

Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.