ORA-02024: database link not found (Cannot drop a database link after changing the global_name)

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 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 There are two solutions: 1. Update sys.link$ and rename the name column to make it match so that 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 not found , that means the domain name is 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 be changed back to the desired name containing domain part using 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 be 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; This above document has been taken from the Oracle Metalink Doc ID 382994.1

ORA-09968 error when tried to start standby database

Issue :

Below error reported in alert log when tried to start physical standby database.


Error:

Tue Aug 13  03:33:12 2017
sculkget: failed to lock /vac/database/app/oracle/11.2.0.4/dbs/lkORCL exclusive
sculkget: lock held by PID: 21604
ORA-09968: unable to lock file
HPUX-ia64 Error: 13: Permission denied
Additional information: 21604
ORA-1102 signalled during: alter database mount standby database


Solution:

Remove 'lkORCL' file or rename it and restart the database.  It will automatically created when database restarts.

mv /vac/database/app/oracle/11.2.0.4/dbs/lkORCL lkORCL_old

Database started successfully after that.

Refer oracle metalink Doc ID 467251.1 for more information.

Oracle Database Patching : Patch 20760997 - Database Patch Set Update 11.2.0.3.15 (Includes CPUJul2015)

Patch Information:

Patch Set Update (PSU) patches are cumulative. That is, the content of all previous PSUs is included in the latest PSU patch.

To install the PSU 11.2.0.3.15 patch, the Oracle home must have the 11.2.0.3.0 Database installed. Subsequent PSU patches can be installed on Oracle Database 11.2.0.3.0 or any PSU with a lower 5th numeral version than the one being installed.

This patch is Data Guard Standby-First Installable. See My Oracle Support Document 1265700.1 Oracle Patch Assurance - Data Guard Standby-First Patch Apply for details on how to remove risk and reduce downtime when applying this patch.

Prerequisites for patching:

Database : 11.2.03
OS : HP-UX Itanium 64 Bit

Login to Oracle meta-link support.

Download patch as mentioned below:

Goto OPatch directory and check for applied patches.

Cd $ORACLE_HOME/OPatch

opatch lsinventory

Check opatch version:

Cd $ORACLE_HOME/OPatch

opatch -v

You must use the OPatch utility version 11.2.0.3.0 or later to apply this patch. Oracle recommends that you use the latest released OPatch version for 11.2, which is available for download from My Oracle Support patch 6880880 by selecting the 11.2.0.0.0 release.

For information about OPatch documentation, including any known issues, see My Oracle Support Document 293369.1 OPatch documentation list.

Follow below metalink ID to install latest version of OPatch.

How To Download And Install The Latest OPatch(6880880) Version (Doc ID 274526.1)

After installing latest opatch:

check oraInst.loc file pointing to your current $ORACLE_HOME or not.

[ /vac/msm6dbs1/app/oracle ]

Host:SID=SID=>cat /var/opt/oracle/oraInst.loc

inventory_loc=/vac/msm6dbs1/app/oracle/oraInventory

inst_group=msm6dbs1

check free space on $ORACLE_HOME 

Patching main steps: 

unzip the patch 

unzip p20760997_112030_HPUX-IA64.zip

In my case below is patch location

Patch Location: /vac/msm6dbs1/app/oracle/20760997

cd $ORACLE_HOME/OPatch

Invoke opatch apply command with patch location as mentioned below.

opatch apply /vac/msm6dbs1/app/oracle/20760997

After Patching output of ‘opatch lsinventory’ as mentioned below.

Below is the full output:

[ /vac/msm6dbs1/app/oracle/product/11.2.0.3/OPatch ]

nh1d0t21:SID=SID=>opatch lsinventory

Oracle Interim Patch Installer version 11.2.0.3.16

Copyright (c) 2017, Oracle Corporation.  All rights reserved.

Oracle Home       : /vac/msm6dbs1/app/oracle/product/11.2.0.3

Central Inventory : /vac/msm6dbs1/app/oracle/oraInventory

   from           : /vac/msm6dbs1/app/oracle/product/11.2.0.3/oraInst.loc

OPatch version    : 11.2.0.3.16

OUI version       : 11.2.0.3.0

Log file location : /vac/msm6dbs1/app/oracle/product/11.2.0.3/cfgtoollogs/opatch/opatch2017-06-12_15-14-11PM_1.log

Lsinventory Output file location : /vac/msm6dbs1/app/oracle/product/11.2.0.3/cfgtoollogs/opatch/lsinv/lsinventory2017-06-12_15-14-11PM.txt

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

Local Machine Information::

Hostname: 

ARU platform id: 197

ARU platform description:: HP-UX Itanium

Installed Top-level Products (1):

Oracle Database 11g                                                  11.2.0.3.0

There are 1 products installed in this Oracle Home.

Interim patches (1) :

Patch  20760997     : applied on Mon Jun 12 15:02:55 EDT 2017

Unique Patch ID:  18926826

Patch description:  "Database Patch Set Update : 11.2.0.3.15 (20760997)"

   Created on 11 Jun 2015, 09:33:38 hrs PST8PDT

Sub-patch  20299017; "Database Patch Set Update : 11.2.0.3.14 (20299017)"

Sub-patch  19769496; "Database Patch Set Update : 11.2.0.3.13 (19769496)"

Sub-patch  19121548; "Database Patch Set Update : 11.2.0.3.12 (19121548)"

Sub-patch  18522512; "Database Patch Set Update : 11.2.0.3.11 (18522512)"

Sub-patch  18031683; "Database Patch Set Update : 11.2.0.3.10 (18031683)"

Sub-patch  17540582; "Database Patch Set Update : 11.2.0.3.9 (17540582)"

Sub-patch  16902043; "Database Patch Set Update : 11.2.0.3.8 (16902043)"

Sub-patch  16619892; "Database Patch Set Update : 11.2.0.3.7 (16619892)"

Sub-patch  16056266; "Database Patch Set Update : 11.2.0.3.6 (16056266)"

Sub-patch  14727310; "Database Patch Set Update : 11.2.0.3.5 (14727310)"

Sub-patch  14275605; "Database Patch Set Update : 11.2.0.3.4 (14275605)"

Sub-patch  13923374; "Database Patch Set Update : 11.2.0.3.3 (13923374)"

Sub-patch  13696216; "Database Patch Set Update : 11.2.0.3.2 (13696216)"

Sub-patch  13343438; "Database Patch Set Update : 11.2.0.3.1 (13343438)"

   Bugs fixed:

     18681866, 13931044, 14318397, 19517437, 20134036, 20134035, 16279211

     20134034, 20134033, 12879027, 13499412, 12748240, 16101465, 13566938

     12845115, 10625145, 16462834, 13429702, 13399435, 18440047, 13503204

     13419660, 14191508, 14781609, 13092220, 13035804, 13812031, 14609690

     14383007, 13369579, 11732473, 7509451, 16524926, 19358317, 12925089

     12595606, 13804294, 14138130, 14226599, 18641419, 10256843, 16694777

     14668670, 12620823, 13072654, 13612575, 13528551, 15853081, 13914613

     13911821, 14459552, 14275605, 9095696, 13427062, 13918644, 16231699

     13370330, 13063120, 13923374, 13903046, 13544396, 14258925, 13454210

     13483354, 19854461, 12919564, 14098509, 17437634, 14035825, 13424216

     16382448, 14237793, 14695377, 13724193, 14230270, 14262913, 13840704

     12658411, 13596521, 14003090, 12755231, 14762511, 13705338, 9812682

     19554106, 19458377, 13699124, 11708510, 14127231, 12387467, 13786142

     12938841, 12983611, 13857111, 18641461, 12917230, 12813641, 14657740

     13736413, 14222403, 14406648, 16382353, 13807411, 13911711, 19309466

     12312133, 21146680, 9163477, 13686047, 16014985, 14398795, 14472647

     12971775, 18641451, 12974860, 12535346, 12796518, 12898558, 16710363

     13936424, 13042639, 16314470, 14052871, 9761357, 14637368, 13848402

     20657441, 16710753, 13916709, 13397104, 16294378, 13593999, 12571991

     15869211, 11071989, 13502183, 16314466, 16314467, 16314468, 16048375

     16314469, 14176879, 18139695, 12784406, 13060271, 12345082, 13944971

     16347904, 13579992, 18673342, 14207163, 13354082, 13945708, 13912931

     13923995, 12940620, 9703627, 13958038, 13719292, 14220725, 13496884

     13642044, 8547978, 13719081, 16038929, 16019955, 13732226, 14063280

     14063281, 12899768, 12940637, 18740215, 9397635, 13466801, 16794240

     20803576, 16794242, 20074391, 16794241, 13596581, 16794244, 16794243

     13326736, 13011409, 12411746, 13366202, 14841409, 20334344, 19972198

     19972199, 13257247, 13787482, 16794239, 12320556, 19006849, 16794238

     20477068, 12594032, 20477069, 12588744, 13467683, 10242202, 12998795

     13584130, 19710542, 14512189, 16944698, 13972394, 14121009, 12821418

     11868640, 17762296, 13340388, 12857027, 16088176, 13420224, 16450169

     17019974, 19211724, 13035360, 14343501, 20477071, 13814739, 14023636

     12614359, 15994107, 13038684, 12797620, 18673304, 14755945, 14613900

     20004021, 12797765, 13791364, 12847466, 13794550, 15905421, 15996344

     13250244, 14369664, 19433747, 19433745, 12964067, 19433746, 11877623

     20296213, 13553883, 19699191, 13489024, 12755116, 13477790, 13338048

     13146182, 13855490, 13907462, 12693626, 14548763, 17333202, 17333200

     12747740, 13099577, 18096714, 14128555, 12873183, 17333203, 13457582

     12747437, 16530565, 18673325, 19827973, 13026410, 14188650, 17540582

     14508968, 16864562, 13066936, 16362358, 13913630, 12861463, 13098318

     13632717, 12582664, 16344871, 17478415, 16272008, 14841558, 12945879

     12829021, 13773133, 16563678, 15856660, 14841812, 12894807, 12815057

     10182005, 16872333, 13561750, 16844086, 10133521, 14263036, 14390252

     13772618, 16850197, 12764337, 12834027, 14038787, 12960925, 14088346

     13554409, 15910002, 13484963, 13476583, 13872868, 10263668, 12744759

     14588746, 17165204, 12865902, 16212405, 13430938, 15987992, 13737888

     13723052, 11840910, 20299010, 13070939, 12976376, 19121548, 14546638

     14193240, 13448206, 13559697, 19259446, 14644185, 14273397, 11715084

     13616375, 6690853, 13718279, 13332439, 14488943, 13843646, 12395918

     13015379, 14489591, 15826962, 13492735, 15901852, 20477440, 13527323

     10400244, 13040943, 16279401, 14263073, 13649031, 18522512, 14182835

     13632809, 13358781, 14076523, 20558005, 12977562, 13464002, 9797851

     13901201, 12772404, 12731940, 13572659, 13810393, 13878246, 16372203

     12950644, 17332800, 12912137, 12780983, 17748831, 17748830, 17748833

     12959852, 17748832, 13790109, 17748835, 13647945, 17748834, 12718090

     13366199, 10110625, 16368108, 13361350, 9547706, 13259364, 12662040

     16338983, 14546575, 14207902, 13440516, 16299830, 14198511, 14040433

     13871316, 17088068, 13499128, 14546673, 20004087, 14207317, 13685544

     13065099, 10359307, 13591624, 14408859, 16902043, 13493847, 19915271

     18173595, 18173592, 18173593, 13696216, 13043012, 16747736, 14692762

     13605839, 14558880, 14727310, 9873405, 17721717, 12591399, 11883252

     15931756, 14799269, 14110275, 13657605, 15981698, 12621588, 13866372

     13524899, 17761775, 12588237, 9706792, 16721594, 12612118, 13059165

     18262334, 13551402, 12880299, 5144934, 14007968, 13550185, 17752121

     17230530, 14062792, 16056266, 14062793, 14062794, 13534412, 14062795

     14062796, 14052474, 13840711, 14062797, 13780035, 18325460, 16024441

     12780098, 14138823, 13890080, 16761566, 16923127, 9858539, 14053457

     15862024, 15862022, 15862023, 15862021, 19197175, 15862020, 13384182

     13362079, 16844448, 14571027, 13725395, 12791981, 17865671, 7276499

     16619892, 17343514, 15883525, 15841373, 15862019, 14205448, 14163397

     15862016, 15862017, 15862018, 13981051, 14664355, 13385346, 20331945

     13041324, 14751895, 12797420, 14189694, 14351566, 13001379, 13645875

     14095982, 14480676, 13680405, 14480675, 14480674, 19727057, 10350832

     17716305, 12794305, 19289642, 14589750, 12583611, 14409183, 12646784

     17767676, 16505333, 16175381, 14791477, 21151526, 21031414, 21031410

     21031411, 21031412, 21031413, 12923168, 12617123, 13853654, 14123213

     13582702, 12391034, 14393728, 13839336, 19638161, 13866822, 16306019

     11063191, 14497307, 13839641, 13860201, 14774091, 12913474, 13910420

     19769496, 13377816, 17082364, 16742095, 12401111, 12678920, 13624984

     12656535, 20777150, 13036331, 13645917, 14158012, 13834436, 13742464

     19537916, 13737746, 13853126, 13103913, 14467061, 12849688, 13080778

     14523004, 19271438, 17050888, 13343438, 13783957, 12596444, 13849733

     13503598, 12905053, 16992075, 18031683, 12985237, 12905058, 13742436

     13742437, 13561951, 13742438, 17333198, 17333197, 13742433, 12585543

     13916549, 17333199, 13742434, 9659614, 13742435, 12748538, 16344758

     13588248, 17468141, 18436647, 16703112, 13834065, 16710324, 14301592

     14469008, 16929165

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

OPatch succeeded.

Post Patching steps:

Loading modified sqlfiles into the database.

cd $ORACLE_HOME/rdbms/admin

sqlplus / as sysdba

SQL> CONNECT / AS SYSDBA

SQL> STARTUP

SQL> @catbundle.sql psu apply

The catbundle.sql execution is reflected in the dba_registry_history view by a row associated with bundle series PSU.

For information about the catbundle.sql script, see My Oracle Support Document 605795.1 Introduction to Oracle Database catbundle.sql.

run utlrp.sql to re-validate INVALID objects.

cd $ORACLE_HOME/rdbms/admin

sqlplus /nolog

SQL> CONNECT / AS SYSDBA

SQL> @utlrp.sql

Confirm that patch has been applied successfully

col action_time for a40
col action for a15
col namespace for a15
col version for a15
col comments for a40
set pages 1000
set lines 170
select * from registry$history ;

_________________________________________________________________________________

Patch Rollback instructions provided in read-me document attached in the downloaded patch. Below are the steps.

Patch Deinstallation Instructions for a Non-RAC Environment

Follow these steps:

1. Verify that an $ORACLE_HOME/rdbms/admin/catbundle_PSU_<database SID>_ROLLBACK.sql file exists for each database associated with this ORACLE_HOME. If this is not the case, you must execute the steps in Section 3.3.2, "Loading Modified SQL Files into the Database" against the database before deinstalling the PSU.
2. Shut down all instances and listeners associated with the Oracle home that you are updating. For more information, see Oracle Database Administrator's Guide.
3. Run the OPatch utility specifying the rollback argument as follows.

   opatch rollback -id 20760997

Patch Post-Deinstallation Instructions for a Non-RAC Environment

1. Start all database instances running from the Oracle home. (For more information, see Oracle Database Administrator's Guide.)
2. For each database instance running out of the ORACLE_HOME, connect to the database using SQL*Plus as SYSDBA and run the rollback script as follows:

   cd $ORACLE_HOME/rdbms/admin
   sqlplus /nolog
   SQL> CONNECT / AS SYSDBA
   SQL> STARTUP
   SQL> @catbundle_PSU_<database SID>_ROLLBACK.sql
   SQL> QUIT

3. If the OJVM PSU was applied for a previous PSU patch, you may see invalid Java classes after execution of the catbundle.sql script in the previous step. If this is the case, run utlrp.sql to re-validate these Java classes.

   cd $ORACLE_HOME/rdbms/admin
   sqlplus /nolog
   SQL> CONNECT / AS SYSDBA
   SQL> @utlrp.sql
   

4. Check the log file for any errors. The log file is found in $ORACLE_BASE/cfgtoollogs/catbundle and is named catbundle_PSU_<database SID>_ROLLBACK_<TIMESTAMP>.log where TIMESTAMP is of the form YYYYMMMDD_HH_MM_SS.