This blog lives a lot from customer’s feedback and updates. And these days, Nicolas Jardot from dbi-services in Switzerland sent me an email asking: Where does the database link SYS_HUB come from? And if he can delete it or if it has any deeper purpose. Neither Google nor MOS reveal a lot of useful information
Where does the database link SYS_HUB come from?
I took my vanilla 18.3.0 database in our hands-on lab and check:
column owner format a5 column db_link format a12 column username format a12 column host format a12 select con_id, owner, db_link, username, host from cdb_db_links; CON_ID OWNER DB_LINK USERNAME HOST ---------- ----- ------------ ------------ ------------ 1 SYS SYS_HUB CDB2 2 SYS SYS_HUB CDB2
So there’s a standard out-of-the-box database link in every 12.2 and 18c (and 19c) database. And in a Multitenant environment it is in every container, CDB$ROOT
, PDB$SEED
and then in all provisioned PDBs as well.
But what is the purpose?
Actually this database link comes in from the RAC side. It is used to allow rerouting of DML from LEAF nodes which
can’t write to the read only instances.You RAC experts may know better about this than I. In case you need to read more about hub and leaf nodes, please see the documentation.
Can you delete this database link?
I’m not Oracle Support giving official recommendations. Hence, if you are seeking for an official statement, please ask via an SR. Currently there’s no MOS note available. But there’s an unpublished bug 23568687. The database link does exist in all Oracle 12.2.0.1, Oracle 18c and Oracle 19c databases.
But in my understanding, as long as you not using RW/RO instance configuration, you can delete the db link with no side effects.
Additional Information
Maybe somebody in Support got triggered by my blog post 😉 A MOS note got published in January 2019 (thanks, Ernest Kalwa!) – but as far as I can see it doesn’t give more background information than I did:
- MOS Note:2413797.1 – What Is The Use Of DataBase Link SYS.SYS_HUB And Can It Be Deleted
- MOS Note: 2442938.1 – How to Drop and Create SYS_HUB (Database Link)
–Mike
MOS Note is available now:
What Is The Use Of DataBase Link SYS.SYS_HUB And Can It Be Deleted (Doc ID 2413797.1)
Awesome – thanks 🙂
Cheers,
Mike
Thanks Mike, this is very helpful.
Ben
Well, at least they could have mentioned you in the REFERENCES section 😉
One more, but usefull MOS note: How to Drop and Create SYS_HUB (Database Link) ? (Doc ID 2442938.1)
Thanks for the hint, Andrey _ I added the MOS note to the blog post.
Wasn’t aware of it.
Cheers
Mike