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

Source: Internet
Author: User
Tags sqlplus


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)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.