{"id":1135,"date":"2015-08-31T14:16:47","date_gmt":"2015-08-31T12:16:47","guid":{"rendered":"http:\/\/www.ludovicocaldara.net\/dba\/?p=1135"},"modified":"2020-08-18T16:31:26","modified_gmt":"2020-08-18T14:31:26","slug":"ora-02153-create-database-link","status":"publish","type":"post","link":"https:\/\/www.ludovicocaldara.net\/dba\/ora-02153-create-database-link\/","title":{"rendered":"How to avoid ORA-02153 when creating database links on 11.2.0.4 (the unsupported way)"},"content":{"rendered":"<p><em><strong>Disclaimer<\/strong> (wow, most of my recent posts start with a disclaimer, I guess it&#8217;s bad): this post explains an UNSUPPORTED workaround for an error enforced by recent Oracle security checks. You should never use it in production! Forewarned is forearmed.<\/em><\/p>\n<p>Before Oracle Database 11.2.0.4, it was possible to create a database link using the following syntax:<\/p>\n<pre class=\"lang:plsql decode:true \">create database link XX connect to YY \r\nidentified by values 'DEA2G0D1A57B0071057A11DA7A' using 'ZZZ';<\/pre>\n<p>It was possible to get the password hash by either selecting dbms_metadata.get_ddl for the database link or by querying directly the link$ table.<\/p>\n<p>Starting with Oracle 11.2.0.4, Oracle is enforcing a check that prevents to use such syntax. Every newly created database link must have the password explicitly set.<\/p>\n<p>This is clearly stated in the MOS note:<\/p>\n<p><strong><span id=\"kmPgTpl:r1:0:ol22\" class=\"p_AFHoverTarget xq\">ORA-02153: Invalid VALUES Password String When Creating a Database Link Using BY VALUES With Obfuscated Password After Upgrade To 11.2.0.4 (Doc ID 1905221.1)<\/span><\/strong><\/p>\n<p>This is seen as a security enhancement. In my opinion, it forces also to specify clear text passwords somewhere in the scripts that create the db links. (You do not create the db links by hand in sql*plus every time you need one.\u00a0 Do you?)<\/p>\n<p>The only exception is when using the expdp\/impdp. If you expdp a schema, the dumpfile contains the password hash and the statement needed to recreate the database link (&#8230; identified by values &#8216;:1&#8217;), but Oracle only allows impdp to use such statement.<\/p>\n<p>So, simple workaround, just create the database links on a dev\/staging environment, export them using expdp and then provide your dba the dumpfile so he\/she can import it and create the dblinks. Right? Not always.<\/p>\n<p>There is one case where you really need of the old syntax.<\/p>\n<ul>\n<li>You don&#8217;t know the password<\/li>\n<\/ul>\n<p>AND<\/p>\n<ul>\n<li>You MUST change the database link name.<\/li>\n<\/ul>\n<p>As you may know, there are no ways to change a database link name (even through impdp, there is no remap_dblink or anything like that).<\/p>\n<p>E.g., you need to keep the db link and intend to use it for a check BUT you want to prevent the application from using it with the old name.<\/p>\n<p>Because<strong> I believe that no problems exist that cannot be solved by my Trivadis&#8217; colleagues<\/strong>, I&#8217;ve checked internally. A colleague came out with a dead simple (and unsupported) solution:<\/p>\n<p>Insert\/update sys.link$, flush the shared_pool.<\/p>\n<pre class=\"lang:plsql decode:true\">SQL&gt; select * from dba_db_links;\r\n\r\nOWNER                          DB_LINK              USERNAME                       HOST       CREATED\r\n------------------------------ -------------------- ------------------------------ ---------- ---------\r\nSCOTT                          REMOTEDB             SCOTT                           remotedb   10-APR-15\r\n\r\nSQL&gt; select * from sys.link$;\r\n\u00a0\u00a0\u00a0 OWNER# NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CTIME\u00a0\u00a0\u00a0\u00a0 HOST\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 USERID\u00a0\u00a0\u00a0\u00a0 PASSWORD\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FLAG AUTHUSR\r\n---------- -------------------- --------- ---------- ---------- ------------------------------ ---------- ------------------------------\r\nAUTHPWD\r\n------------------------------\r\nPASSWORDX\r\n------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\r\nAUTHPWDX\r\n------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 REMOTEDB\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 10-APR-15 remotedb\u00a0\u00a0 SCOTT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2\r\n\r\n061D009E40A5981668DFEE1C710CF68E20B1A4DEE898857B2C3C458C3DEA042675E6CC98CC8D7B72C2F21314D94872D32882BECDE0594B3A525E342B8958BDF37ACE0DE3CE0A4D153AF41EEAF8391A9D84924521C45BA79FF2A2\r\nCEA78709E3BD7775DB9B79A2B4D2F742472B7B5733E142CBCBA2A73511B81F3840611737351\r\n\r\nSQL&gt; insert into sys.link$ (OWNER#, NAME, CTIME, HOST, USERID, PASSWORD, FLAG, AUTHUSR, AUTHPWD, PASSWORDX, AUTHPWDX)\r\n  2  select OWNER#, 'NEWDBLINK', CTIME, HOST, USERID, PASSWORD, FLAG, AUTHUSR, AUTHPWD, PASSWORDX, AUTHPWDX \r\n  3  from sys.link$ where name='REMOTEDB';\r\n\r\n1 row created.\r\n\r\nSQL&gt; commit;\r\n\r\nCommit complete.\r\n\r\nSQL&gt; alter system flush shared_pool;\r\n\r\nSystem altered.\r\n\r\nSQL&gt; select * from dba_db_links;\r\n\r\nOWNER                          DB_LINK              USERNAME                       HOST       CREATED\r\n------------------------------ -------------------- ------------------------------ ---------- ---------\r\nSCOTT                          REMOTEDB             SCOTT                          remotedb   10-APR-15\r\nSCOTT                          NEWDBLINK            SCOTT                          remotedb   10-APR-15\r\n<\/pre>\n<p>Remember, use it at your own risk (or don&#8217;t use it at all) \ud83d\ude09<\/p>\n<p>HTH<\/p>\n<p>&#8212;<\/p>\n<p>Ludovico<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Disclaimer (wow, most of my recent posts start with a disclaimer, I guess it&#8217;s bad): this post explains an UNSUPPORTED workaround for an error enforced by recent Oracle security checks. You should never use it in production! Forewarned is forearmed. &hellip; <a href=\"https:\/\/www.ludovicocaldara.net\/dba\/ora-02153-create-database-link\/\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[326,309,3,52,330,132],"tags":[240,239,241],"class_list":["post-1135","post","type-post","status-publish","format-standard","hentry","category-oracle","category-oracle-cloud","category-oracledb","category-12c","category-oracle-inst-upg","category-triblog","tag-database-link","tag-dblink","tag-identified-by-values"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/1135","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/comments?post=1135"}],"version-history":[{"count":4,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/1135\/revisions"}],"predecessor-version":[{"id":1148,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/1135\/revisions\/1148"}],"wp:attachment":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/media?parent=1135"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/categories?post=1135"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/tags?post=1135"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}