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 dual@cotdev
*
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 system@cotdev works.
From within a different database, I can successfuly: select sysdate from dual@cotdev;
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 dual@cotdev
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 dual@cotdev2
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.