Saturday, 22 October 2011

GLOBAL_NAME and db links

Once we created a database for migration from other older system with some domain_name. We set a db_domain in order to be compliant with other databases we managed. However the database to be migrated worked without such setting. Thus we hit a problem when creating database links, as those new were created with some suffix even provided with double quotes and in result some code did not want to compile.

What is behind the scene?
It seems at the database creation the GLOBAL_NAME setting for this database is created as well based on db_name and db_domain settings. Any changes to those base parameter are not reflected further in this GLOBAL_NAME value. And that is GLOBAL_NAME which influence database links name - its domain part is added to every database link.

How to deal with it?
http://dbatips4u.blogspot.com/2009/04/tip21-db-link-name-suffix.html provides the details.
I add here small note to it - I was especially interested in cancelling the global_name domain part at all, which is less trivial than just setting it to something new as the simple ALTER DATABASE RENAME GLOBAL_NAME TO "{db_name}"; does not work as planned (the domain part stays in place). I followed the notes from the link above. I run UPDATE GLOBAL_NAME SET GLOBAL_NAME='{db_name}'; but even though the GLOBAL_NAME content has changed, still db links' names were creates with suffix I did not want.
The important part was a database restart, after which all started work as planned.
Btw. our GLOBAL_NAMES was set to FALSE, which is different from the settings presented in the tip from the link above - may be that is the clue, why I needed to restart.