Cannot drop a database link after changing the global_name ORA-02024 (document ID 382994.1)

cannot drop a database link after changing the global_name ORA-02024 (document ID 382994.1)applies To:Oracle database-enterprise edition-version 9.2.0.6 and later Information in this document applies to any platform. Checked for relevance on 14-nov-2014*** Symptoms Able to drop a database link after changing the global_name of the database Earlier Global_name had did not has domain name attached to it. The newly added global_name have a domain name attached to it. When trying to drop the database link after the throws the following error Ora-02024:database Link not found But database link was 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 was created without domain in the global name, NULL would used FR Om Domain as opposed to earlier releases , later on when the global_name was altered to contain the domain Part also, this domain remains even when the global_name are 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 to correct the base table props$ Solution There is solutions: 1. Update sys.link$ and rename the Name column to make it match so, the command to drop public database link works. 1.1. Take a complete consistent backup of the database 1.2. Execute the following: $sqlplus/nolog Connect/as SYSDBA sql> Update props$ Set value$ = ' <globalname without domain> ' WHERE name = ' Global_db_name '; sql>commit; 1.3 Drop the database link: 1.3.1. Connect as the schema user that owns the DBLINK and try to drop it. If you still get ora-2024:database link isn't found, that means the domain name was in your cache and needs to be cleared. 1.3.2. Flush shared pool thrice and retry drop database link. alter system flush Shared_pool; alter system flush Shared_pool; alter system flush Shared_pool; 1.3.3. If Step b doesn ' t help, you need to bounce your database and try to drop the database link. 1.3.4 Once The database link is dropped, the global_name can being changed back to the desired name containing domain part U Sing the ALTER DATABASE rename Global_name statement 2. The second solution consists on deleting the database link directly from sys.link$: 2.1. Take a complete consistent backup of the database or use CTAS can is used to backup sys.link$: $sqlplus/nolog Connect/as SYSDBA Sql> CREATE TABLE backup_link$ as SELECT * from sys.link$: 2.2. Delete the DBLINK as follows: $sqlplus/nolog Connect/as SYSDBA sql> Delete sys.link$ where name= ' db_link_name> '; sql>commit; 2.3. Verify if the operaion s correctly proceeded: Select Db_link, username, host from User_db_links; Reference Bug 3675157-ora-02024:cannot DROP DBLINK after the Global_name PARAMETER is CHANGED. Still have questions? To discuss the information further with Oracle experts and industry peers, we encourage you to review, join or start A discussion in the My Oracle support Streams and distributed Database Community REFERENCESbug:3675157-Ora-02024:cannot DROP DBLINK after the Global_name PARAMETER is CHANGED.

This article is from "Technical achievement dream!" "Blog, be sure to keep this provenance http://369day.blog.51cto.com/845412/1976816

Cannot drop a database link after changing the global_name ORA-02024 (document ID 382994.1)