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 188.8.131.52. 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 184.108.40.206 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 220.127.116.11) 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.