Oracle Database GLOBAL_NAME, DB_DOMAIN and database links
20/07/2020 Leave a comment
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…