Why do I get ORA-12541: TNS:no listener when trying to select from a CDB to its PDB using a dblink.

975799 · Apr 7, 2022 6:21PM

We're on oracle 19.7 and starting to convert our stand alone databases to multitenant.

For reference here is the CDB and its PDBs:

SQL> @whereami

INSTANCE CONTAINER USERNAME

---------- --------------- ------------------------------

CDB19CT1 CDB$ROOT SYS

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED READ ONLY NO

3 COTDEV READ WRITE NO

4 PDB19CT1 READ WRITE NO

When I'm connected to the CDB in sqlplus, I can not connect to its PDB using an implicit database link. I get ORA-12541: TNS:no listener.

SQL> @whereami

INSTANCE CONTAINER USERNAME

-------- -------- --------

CDB19CT1 CDB$ROOT SYSTEM

select sysdate from [email protected]

*

ERROR at line 1:

ORA-12541: TNS:no listener

The message says there is no listener, but I can connect to this database multiple ways, so there is a listener.

I can connect directly to the PDB: sqlplus [email protected] works.

From within a different database, I can successfuly: select sysdate from [email protected];

I can also connect to other PDB's using an implicit dblink, from within this CDB.

I tried modifying the tnsnames.ora entries several ways including/excluding the domain, with no luck.

I also tried creating a second listener on port 1521 just in case, (because we don't use the default port), and changing all the appropriate address, but still didn't work.

So I thought I'd experiment with creating an explicit db_link in the CDB pointing to the PDB and found some interesting things:

create public database link cotdev using 'cotdev'

*

ERROR at line 1:

ORA-02011: duplicate database link name

Since the only db_link that shows up in dba_db_links is SYS_HUB, this was a surprise.

Tried dropping it, even though I can't see it:

drop public database link cotdev

*

ERROR at line 1:

ORA-65230: internal database link cannot be altered or dropped

So, I created a private database link owned by SYSTEM, and that worked!

SQL> show user

USER is "SYSTEM"

SQL> create database link cotdev using 'cotdev'

2 /

Database link created.

SQL> select sysdate from [email protected]

2 /

SYSDATE

-----------------

20220407 10:45:16

Finally, I tried creating a db_link with a different name pointing to the PDB, and that also worked:

SQL> create public database link cotdev2 using 'cotdev'

2 /

Database link created.

SQL> select sysdate from [email protected]

2 /

SYSDATE

-----------------

20220407 10:46:26

Does anyone know what might be going on?

And is there any way to see what that internal database link looks like?

Thanks for any suggestions.

Timo Hahn · Apr 8, 2022 6:45AM

@975799 If you want to see this in MOSC you have to post the question there again. Posts from the public part can't be moved over to MOSC.

Timo

975799 · Apr 7, 2022 8:12PM

I discovered this in the alert log: (modified to remove names).

I repeated my test to make sure it's related. I also tried my test in a different cdb/pdb/server, where I had not been testing with a listener on port 1521, just to confirm I had not left anything behind from my testing.

Fatal NI connect error 12541, connecting to:

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxxxxxxxxxx)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=3a83e706011d539ce05400144ffd1b34.XX.LOCAL)(CID=(PROGRAM=oracle)(HOST=xxxxxxxxxxx)(USER=oracle))))

These are the cdb/pdb services shown in the lsnrctl status. The listener is not on port 1521.

Services Summary...

Service "3a83e706011d539ce05400144ffd1b34.XX.LOCAL" has 1 instance(s).