Cannot drop a database link after changing the global_name ORA-02024 [ID 382994.1]

Cannot drop a database link after changing the global_name ORA-02024 [ID 382994.1]
	Modified22-nov-2010TypeProblemStatusModerated

In this document
Symptoms
Cause
Solution

Platforms: 1-914cu;

This document is being delivered to you via Oracle Support's rapid visibility (RAV) process and therefore has not been subject to an independent technical review.

Applies:

Oracle Server-Enterprise Edition-version: 9.2.0.6 and later [release: 9.2 and later]
Information in this document applies to any platform.
* ** Checked for relevance on 14-Jan-2010 ***

Symptoms

Not able to drop a database link after changing the global_name of the database

Earlier global_name had did not have domain name attached to it. The newly added
Global_name has a domain name attached to it

When trying to drop the database link after this change throws the following error

ORA-02024: database link not found

But database link is present and the query on user_db_links displays the value

Example :-

SQL> select * From global_name;

Global_name
---------------------------------------------------------
Db10gr2

SQL> CREATE DATABASE LINK L1 connect to Scott identified by tiger;

Database link created.

SQL> select db_link from user_db_links;

Db_link
---------------------------------------------------------
L1

SQL> alter database rename global_name to db10gr2. World;

Database altered.

SQL> select * From global_name;

Global_name
---------------------------------------------------------
Db10gr2. World

SQL> DROP DATABASE LINK L1;
Drop database link L1
*
Error at line 1:
ORA-02024: database link not found

Even if the global_name is changed back to the original value, the same errors
Occurs.

Cause

Initially when a database is created without domain in the global name, null will
Used from domain as opposed to. World in earlier releases

Later on when the global_name is altered to contain the domain part also, this
Domain remains even when the global_name is altered back a name without Domain Name

Example :-

SQL> select * From global_name;

Global_name
---------------------------------------------------------
Db10gr2

SQL> alter database rename global_name to db10gr2. World;

Database altered.

SQL> select * From global_name;

Global_name
---------------------------------------------------------
Db10gr2. World

SQL> alter database rename global_name to db10gr2;

Database altered.

SQL> select * From global_name;

Global_name
---------------------------------------------------------
Db10gr2. World

The only option left to correct this is to update the base table props $

Solution

To implement the solution, Please execute the following steps:

1) take a complete consistent backup of the database

2) execute the following:

$ Sqlplus/nolog
Connect/As sysdba

SQL> Update props $ set value $ = '<globalname without domain> 'where name = 'Global _ db_name ';

SQL> commit;

3)

A) then connect as the schema user that owns the dblink and try to drop it.

If you still get ORA-2024: database link not found, that means the domain name is in your cache and needs to be cleared.

B) flush Shared Pool thrice and retry DROP DATABASE LINK.
Alter system flush shared_pool;
Alter system flush shared_pool;
Alter system flush shared_pool;

C) if step B doesn't help, you need to bounce your database and try to drop the database link.

4) Once the database link is dropped, the global_name can be changed back to
Desired name containing domain part using the alter database rename global_name
Statement

 

 

 

 

------------------------------------------------------------------------------

Blog: http://blog.csdn.net/tianlesoftware

Online Resources: http://tianlesoftware.download.csdn.net

Video: http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx

Dba1 group: 62697716 (full); dba2 group: 62697977 (full)

Dba3 group: 62697850 super DBA group: 63306533;

Chat group: 40132017

-- Add the group to describe the relationship between Oracle tablespace and data files in the remarks section. Otherwise, the application is rejected.