Support of Oracle Container Architecture (CDB/PDB)

[rolandstirnimann]

Hello

It seems to me that SynchDB does not support Oracle's container database architecture. Can you confirm that?
The documentation does not mention it explicitly and since there are examples where a common user (prefix c##) is used I assumed it will probably work. I have the following structure:

• Oracle container instance DBT01 (service name DBT01_A.dbstar.info)
  • Pluggable database TIB (service name tib.dbstar.info)
    • Schema called TIBREPO (owner of the table)
      • Table called SITUATION

I tested two scenarios:

1. Connection to the PDB (pluggable database) TIB directly: The connection works but using logminer inside a PDB is not supported by Oracle. Attached you can find the log output (pdb.log) after starting the background worker.
   java.sql.SQLException: ORA-65040: operation not allowed from within a pluggable database

2. Connection to the CDB (root container): Connection and Logminer session work but it does not find my table. Details can be found in the attached log (cdb.log). The problem here, I cannot specify the PDB. So, SynchDB is not able to find the table TIBREPO.SITUATION in the root container. There should be an additional parameter to specify the PDB (here called TIB). I also tried TIB.TIBREPO.SITUATION for the table name in the function below, but it did not work. From Debezium Oracle Connector I now, there are distinctive parameters database.pdb.name (TIB) and database.name (DBT01_A). Is this configuration currently supported in SynchDB?

SELECT synchdb_add_conninfo(
    'tibconn'
  , '192.168.173.25'
  , 1521
  , 'c##openlogreplicator_reader'
  , 'manager'
  , 'DBT01_A.dbstar.info'
  , 'tib'
  , 'TIBREPO.SITUATION'
  , 'null'
  , 'oracle'
);

Maybe I have just overlooked something.
Thank you for your clarification.

Regards
Roland

[caryhuang]

Hi Roland

Thank you so much for testing and reporting the issue! It is a known issue/limitation that currently, the user cannot specify a PDB for oracle connector to connect to Oracle running in container database architecture. This causes the connection to always go to CDB$ROOT, that does not necessarily contain the desired user tables.

The underlining Debezium engine does support container architecture; it just needs the correct PDB name settings.

Currently, since there is only a "srcdb" field in synchdb_add_conninfo(), if you put CDB there, it will just connect to the CDB$ROOT container as mentioend, and it won't find your TIBREPO.SITUATION table because it is not there as you have observed.

If you put PDB there, it will indeed receive "operation not allowed from within a pluggable database" as you have also observed because logmining session can only start from CDB.

So yes indeed, there should be an additional parameter to specify the PDB. I am thinking to reuse the same "srcdb" field (for compatibility with other supported connectors), except that it could take something like 'CDB/PDB' so we could have both specified. If there is no slash, then it is assumed to be CDB or non-container (single db) oracle instance.

If PDB is given, then SynchDB shall set it to the Debezium engine in DebeziumRunner.java:

props.setProperty("database.pdb.name", "PDB");

With this value set, then we should not see "operation not allowed from within a pluggable database" error message.

We will conduct a fix for this issue, thank you so much again for reporting!
Have a great one!

Cary