Unable to drop Database Link After Clone and Upgrade to 11gR2 (11.2.0.1)

I wanted to post this issue because I know someone else will skip this important step as part of the upgrade to 11.2 and wonder if what is wrong.

Cloned a database using the RMAN duplicate command which is standard operating procedure for refreshing our non-production instances. After the clone I performed a manual upgrade from 10.2.0.4 to 11.2.0.1.  After the upgrade the database links no longer worked for any queries and they couldn’t be dropped either.  See the following MOS Note 382994.1: Cannot drop a database link after changing the global_name.

NOTE:  I hadn’t changed the global_name, I only upgraded the database, which affects  the global_name.

This was the syntax used to create the links in the original database (version 10.2.0.4), global_names was set to false.

SQL> create public database link LINKNAME connect to schema identified by "password"
using '(description=(address=(protocol =TCP)
(host=nodename)(port=1550))(connect_data=(SERVICE_NAME=ORACLE_SID)))';

I could query the link in the 11.2.0.1 database:

SQL>  select * from link$ where name ='LINKNAME';
SQL> 1
LINKNAME
17-OCT-09
ORACLE_SID
SCHEMA 2

obfusticated password

But I could not drop it...

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

The problem was that the RDBMS (in 11.2.0.1) identifies links with the Fully Qualified Host Name appended…in this example : LINKNAME.NODENAME.DOMAIN instead of LINKNAME.

It has something to do with global_names and the network changes in 11g.  11g automatically appends the global_name to the end of the ORACLE_SID, even if global_names is false.

Note: Oracle mentions in the upgrade guide, see the section as quoted:

Database Links with Passwords

During the upgrade to the new Oracle Database 11g release from Oracle9i Release 2

(9.2) or Oracle Database 10g Release 1 (10.1), any passwords in database links are

encrypted. To downgrade to the original release, all of the database links with

encrypted passwords must be dropped prior to the downgrade. Consequently, the

database links do not exist in the downgraded database. If you anticipate a

requirement to be able to downgrade to your original release, then save the

information about affected database links from the SYS.LINK$ table, so that you can

re-create the database links after the downgrade

I didn’t think it applied since I wasn’t using the new security enhancements of case sensitive passwords. Silly me….Oracle doesn’t always let on why they want you to do something.

Final Fix and Resolution: Drop the database links after the clone and then perform the upgrade.

Advertisements

About April C Sims

Oracle DBA for over a decade...enough said.
This entry was posted in Uncategorized and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s