Forum Stats

  • 3,837,196 Users
  • 2,262,234 Discussions
  • 7,900,219 Comments

Discussions

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

975799
975799 Member Posts: 12 Blue Ribbon

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.

Best Answer

Answers

  • 975799
    975799 Member Posts: 12 Blue Ribbon

    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).

     Instance "CDB19CT1", status READY, has 1 handler(s) for this service...

    Service "CDB19CT1.XX.LOCAL" has 1 instance(s).

     Instance "CDB19CT1", status READY, has 1 handler(s) for this service...

    Service "c369f3267311505ce05400144ff85319.XX.LOCAL" has 1 instance(s).

     Instance "CDB19CT1", status READY, has 1 handler(s) for this service...

    Service "cotdev.XX.LOCAL" has 1 instance(s).

     Instance "CDB19CT1", status READY, has 1 handler(s) for this service...

    Service "pdb19ct1.XX.LOCAL" has 1 instance(s).

     Instance "CDB19CT1", status READY, has 1 handler(s) for this service...


    This provides more information where it's going and why it can't find the listener.

    Still don't know why, and is there anything I can do to change this behaviour?

  • 975799
    975799 Member Posts: 12 Blue Ribbon

    I'm pretty sure I have this posted in the wrong group.

    Do you know how I can get it moved to Oracle Database (MOSC) Database Administration?

    Thank you.

  • Timo Hahn
    Timo Hahn Senior Principal Technical Consultant - Oracle ACE Director Member, Moderator Posts: 38,563 Red Diamond
    Answer ✓

    @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
    975799 Member Posts: 12 Blue Ribbon

    Thanks Timo. I will do that.