Oracle Database GLOBAL_NAME, DB_DOMAIN and database links

I needed to create a database link on a 19.7C Oracle DB recently. Importantly, the database link name must match the database name as security dictates that GLOBAL_NAMES=TRUE for this set of databases. The DB was created using the gui on an ODA, meaning there were quite a few initialisation parameters set by the gui.

Lets see what happened and what I had to do to actually get a database link with the same name as the database.

> create database link ORA11A connect to neil identified by neil using 'ORA11A';
Database link created.

> select owner,db_link,username,host from dba_db_links;

OWNER DB_LINK                 USERNAME     HOST         
----- ----------------------- ------------ ------------ 
SYS   ORA11A.CHANDLER.UK.COM  NEIL         ORA11A       

> drop database link ORA11A;

What? Why was a domain being appended to the database link name?

> select name,value,description from v$parameter where name = 'db_domain';

NAME       VALUE            DESCRIPTION
---------- ---------------- --------------------------------------------------------------------------------
db_domain  chandler.uk.com  directory part of global database name stored with CREATE DATABASE

> alter system reset db_domain scope=spfile sid='*';
(restart db)
> select name,value,description from v$parameter where name = 'db_domain';

NAME       VALUE            DESCRIPTION
---------- ---------------- --------------------------------------------------------------------------------
db_domain                   directory part of global database name stored with CREATE DATABASE

> create database link ORA11A connect to neil identified by neil using 'ORA11A';
> select owner,db_link,username,host from dba_db_links;

OWNER DB_LINK                 USERNAME     HOST        
----- ----------------------- ------------ ------------
SYS   ORA11A.CHANDLER.UK.COM  NEIL         ORA11A      

So it’s not coming from the DB_DOMAIN! But there’s a clue in the description “directory part of global database name stored with CREATE DATABASE“. Lets check the global name:

select * from global_name;
GLOBAL_NAME
------------------------------
ORA19A.CHANDLER.UK.COM

So maybe it’s taking the domain from here? Can I prove that?

>alter database rename global_name to "ORA19A.COM";
Database altered.

> select * from global_name;
GLOBAL_NAME
------------------------------
ORA19A.COM

> create database link ORA11A connect to neil identified by neil using 'ORA11A';
Database link created.

> select owner,db_link,username,host from dba_db_links;
OWNER DB_LINK                 USERNAME     HOST 
----- ----------------------- ------------ ------------ 
SYS   ORA11A.CHANDLER.UK.COM   NEIL        ORA11A
SYS   ORA11A.COM               NEIL        ORA11A 

[note that I had not deleted the previous DB link so it was still there!]

So, all  I needs to do now is remove the “.COM”

> alter database rename global_name to "ORA19A";
Database altered.

> select * from global_name;
GLOBAL_NAME
------------------------------
ORA19A.COM

Oh dear. You can’t remove a domain suffix from a global name. If you don’t specify the domain, Oracle helpfully adds it right back in there for you.

You need to correct this in an unorthodox way.
The GLOBAL_NAME is really a value in SYS.PROPS$

> select text from dba_views where view_name = 'GLOBAL_NAME';

TEXT
-----------------------------------------------------------
select value$ from sys.props$ where name = 'GLOBAL_DB_NAME'

So we will have to update the value in SYS.PROPS$ directly.

THIS IS NOT A SUPPORTED ACTION.
DON’T DO IT WITHOUT A CONVERSATION WITH ORACLE SUPPORT.
[Check MOS note: 1018063.102]
***updating sys.props$ incorrectly can corrupt your entire database ***

> select value$,comment$ from sys.props$ where name = 'GLOBAL_DB_NAME';

VALUE$                         COMMENT$
------------------------------ --------------------------------------------------------------------------------
ORA19A.COM                     Global database name

> update sys.props$ set value$='ORA19A' where name = 'GLOBAL_DB_NAME';
1 row updated.

> commit;
Commit complete.

select value$,comment$ from sys.props$ where name = 'GLOBAL_DB_NAME';
VALUE$                         COMMENT$
------------------------------ --------------------------------------------------------------------------------
ORA19A                         Global database name

> create database link ORA11A connect to neil identified by neil using 'ORA11A';
Database link created.

> select owner,db_link,username,host from dba_db_links;
OWNER DB_LINK                 USERNAME     HOST 
----- ----------------------- ------------ ------------ 
SYS ORA11A.CHANDLER.UK.COM    NEIL         ORA11A
SYS ORA11A.COM                NEIL         ORA11A
SYS ORA11A                    NEIL         ORA11A

Finally I can use the DB_LINK with GLOBAL_NAME=TRUE!

If only I’d created the database without specifying a db_domain in the first place…