Pages

Thursday, January 29, 2015

Database link cannot be dropped because of ORA-02024

When dropping database link got following error:
ORA-02024: database link not found.
SQL> select * from user_db_links;

DB_LINK USERNAME PASSWORD HOST CREATED
----------------- ------------- ---------- ----------- ---------
REFRESH_DBLINK SYSTEM DBPROD 15-OCT-13

SQL> drop database link REFRESH_DBLINK;
drop database link REFRESH_DBLINK
*
ERROR at line 1:
ORA-02024: database link not found

Can not find an existing database link? What if create a new link with same name?
SQL> create database link REFRESH_DBLINK
2 connect to system identified by Ax356 using 'DBPROD';

Database link created.

SQL> select * from user_db_links;

DB_LINK USERNAME PASSWORD HOST CREATED
------------------------ ------------- ---------- ----------- ---------
REFRESH_DBLINK SYSTEM DBPROD 15-OCT-13
REFRESH_DBLINK.WORLD SYSTEM DBPROD 29-JAN-15

The name of new database link is appended with '.WORLD'. Did more test and found that all new created database link will be added suffix '.WORLD' in the name. Try DROP DATABASE LINK to see what is going to happen:
SQL> drop database link REFRESH_DBLINK;

Database link dropped.

SQL> select * from user_db_links;

DB_LINK USERNAME PASSWORD HOST CREATED
------------------------ ------------- ---------- ----------- ---------
REFRESH_DBLINK SYSTEM DBPROD 15-OCT-13

See, I was dropping REFRESH_DBLINK, but REFRESH_DBLINK.WORLD was dropped. I always get REFRESH_DBLINK.WORLD dropped no matter whether I include '.WORLD' in the DROP DATABASE LINK command. Why did we get the weird term '.WORLD'? It was popular long time ago as default database domain and often found in global_name & db_domain of the database:
SQL> show parameter db_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string DBPROD

SQL> show parameter db_domain

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_domain string

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------
DBPROD.WORLD

It does be a part of database global_name. Does domain part in global_name affect database link name space? Unfortunately, it does. Delete the domain part from global_name:

 
SQL> alter database rename global_name to DBPROD;

Database altered.

SQL> select * from global_name;

GLOBAL_NAME
------------------------------------------------
DBPROD.WORLD

Interesting, the ALTER DATABASE command does not help. Let us manually change global_name bypass ALTER DATABASE command,
Note: Take a complete consistent backup of the database before you continue:
SQL> conn / as sysdba
Connected.

SQL> select name,value$ from props$ where GLOBAL_name='GLOBAL_DB_NAME';

NAME VALUE$
------------------------------ --------------------------------------------------
GLOBAL_DB_NAME DBPROD.WORLD

SQL> update props$ set value$='DBPROD' where name='GLOBAL_DB_NAME';

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from global_name;

GLOBAL_NAME
---------------------
DBPROD

Oracle never suggests direct update on props$. We are not supposed to do that. Hmmm, today is special :)

Drop the database link:
SQL> connect system

SQL> drop datbaetabase link REFRESH_DBLINK;

Database link dropped.

SQL> select * from user_db_links;

no rows selected

Finally, database link is really dropped.

No comments: