Hello Thomas,
We had some weird issues with this, and just could not revert the configuration after giving a database a GLOBAL_NAME with a domain name.
e.g DOMAIN_NAME=DGKO01.COMPANY.COM
After setting this, encountering really weird issues, reverting and go back to the way it was before was not possible, as we could not get rid of the domain name, and old database links that we had created was not possible to delete.
We are running Oracle 10.2.0.3 on HPUX.
dba_tzhluma3@DGKO01> select banner from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for HPUX: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
Well, I sort of got a bit upset and spent some time to find a workaround, which I found after a couple of hours of googling and trial and error, which in the end was just an
UPDATE GLOBAL_NAME set GLOBAL_NAME=<DBNAME>
as the sys user, which was the only workaround I could find, which allowed me to remove the domain name completely.
Following my step by step description below should reproduce the problem. I would like to file this as a BUG, or is it really meant to be so that I will not be able to remove a domain name from the GLOBAL_NAME?
Also, DROP DATABASE LINK seems to fetch this domain name concatenating it to the database link name when I try to drop a database link that was created before the change of GLOBAL_NAME.
I sort of got to the solution by grepping for our domain name in the system01.dbf datafile, which sort of got me into the idea that the domain name is NOT in the control file, but rather in a table.
[DGKO01]mluebeck@hp06a:/db/DGKO01/ctrl $
> sudo -u oracle strings control01.ctl | grep -i company
[DGKO01]mluebeck@hp06a:/db/DGKO01/ctrl $
[DGKO01]mluebeck@hp06a:/db/DGKO01/data $
> sudo -u oracle strings system01.dbf | grep -i company
ASDF.COMPANY.COM
DGKO01.COMPANY.COM
WHATEVER.COMPANY.COM
PGKO01.COMPANY.COM
DASD.COMPANY.COM
DGKO01.COMPANY.COM
ASDF.COMPANY.COM
Then I started doing some targeted google.com searches.
http://www.google.ch/search?hl=de&q=alter+database+rename+global_name++%22database+link+name+expected%22&btnG=Suche&meta= (google on: alter database rename global_name "database link name expected")
But first a short walk through the issue:
1) When assigning a domain name to a GLOBAL_NAME, we encountered issues
* Old db links not possible to drop
* We had issues in applications with hardcoded db link names (not using the domain name).
Setting the global name was done like this:
cd /tmp; sudo -u oracle sqlplus "/ as sysdba"
SQL> alter database rename global_name to DGKO01.company.com;
Database altered.
SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
DGKO01.COMPANY.COM
Now we will try to create a database link without the domain name (LOG OUT and log in again as a normal user):
dba_tzhluma3> create database link malu connect to dba_tzhluma3 identified by XXXXX using 'DMAN01';
Database link created.
dba_tzhluma3> select db_link from user_db_links;
DB_LINK
--------------------------------------------------------------------------------
MALU.COMPANY.COM
This is where our issues start and we cannot revert.
What we wanted now was to remove the COMPANY.COM domain name from the global_name.
#==============================
#================= THE ATTEMPTED SOLUTION
#==============================
The solution was found here:
http://www.dbasupport.com/forums/showpost.php?s=1c9f6f9c23163d61f1e263cb3d9a3d8c&p=68077&postcount=2 By using the "UPDATE GLOBAL_NAME" clause and a "COMMIT" we were able to revert to a GLOBAL_NAME without a domain name.
Connect to the database using SYS:
sys> update global_name set global_name = 'DGKO01';
1 row updated.
sys> commit;
sys> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
DGKO01
Just make sure you shut down all connections/restart the database so that it will affect all sessions.
This actually helped us solve another (by metalink never solved) issue with database links.
2) Old database links without domain name impossible to drop
First we create a db-link as a normal user:
dba_tzhluma3> create database link malu connect to dba_tzhluma3 identified by XXXXX using 'DMAN01';
Database link created.
dba_tzhluma3> select db_link from user_db_links;
DB_LINK
--------------------------------------------------------------------------------
MALU
Then we change the global_name:
cd /tmp; sudo -u oracle sqlplus "/ as sysdba"
SQL> alter database rename global_name to DGKO01.company.com;
Database altered.
sys> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
DGKO01.COMPANY.COM
Now we switch back to our normal user (NOTE, you MUST LOG OUT AND LOG IN AGAIN) and try to drop the database link:
dba_tzhluma3> select db_link from user_db_links;
DB_LINK
--------------------------------------------------------------------------------
MALU
dba_tzhluma3> drop database link malu;
drop database link malu
*
ERROR at line 1:
ORA-02024: database link not found
This is because the database tries to add company.com to the database link name before dropping it.
Try this:
LOG OUT, LOG IN AS sys
sys> update global_name set global_name = 'DGKO01';
1 row updated.
sys> commit;
Commit complete.
LOG OUT, LOG IN AS normal user:
dba_tzhluma3> select db_link from user_db_links;
DB_LINK
--------------------------------------------------------------------------------
MALU
dba_tzhluma3> drop database link malu;
Database link dropped.