How to avoid ORA-02153 when creating database links on 11.2.0.4 (the unsupported way)

Disclaimer (wow, most of my recent posts start with a disclaimer, I guess it’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.

Before Oracle Database 11.2.0.4, it was possible to create a database link using the following syntax:

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.

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.

This is clearly stated in the MOS note:

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)

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.  Do you?)

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 (… identified by values ‘:1’), but Oracle only allows impdp to use such statement.

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.

There is one case where you really need of the old syntax.

  • You don’t know the password

AND

  • You MUST change the database link name.

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).

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.

Because I believe that no problems exist that cannot be solved by my Trivadis’ colleagues, I’ve checked internally. A colleague came out with a dead simple (and unsupported) solution:

Insert/update sys.link$, flush the shared_pool.

Remember, use it at your own risk (or don’t use it at all) 😉

HTH

Ludovico

The following two tabs change content below.

Ludovico

Oracle ACE Director and Computing Engineer at CERN
Ludovico is an Oracle ACE Director, frequent speaker and community contributor, working as Computing Engineer at CERN, the European Organization for Nuclear Research, in Switzerland.

7 thoughts on “How to avoid ORA-02153 when creating database links on 11.2.0.4 (the unsupported way)

  1. Pingback: How to avoid ORA-02153 when creating database links on 11.2.0.4 (the unsupported way) - Ludovico Caldara - Blogs - triBLOG

  2. Hi Ludo,
    Here is another unsupported alternative:
    replace OH/rdbms/xml/xsl/kudblink.xsl with the 11.2.0.3 one and use dbms_metadata.get_ddl as in the good old times…
    Cheers,
    Franck.

  3. I did replace OH/rdbms/xml/xsl/kudblink.xsl with the 11.2.0.3 one, but this didn’t allow me to recreate the DB link the old fashioned way either…Got ORA-02153.

  4. Pingback: Finding Oracle DB link password : Memory analysis | Hatem Mahmoud Oracle's blog

  5. I needed to refresh a clone, but apply a link from the old clone version to the new one. This needed a temporary cache on another database, so I did it like this.

    CacheDB:
    create table luser.mylink as select * from sys.link$ where 1=0;

    OldCloneDB:
    create table luser.mylink as select * from sys.link$ where name=’NEEDTHISLINK’;
    conn luser
    create database link tocache connect to luser … using ‘CacheDB’;
    insert into mylink@tocache select * from mylink;
    commit;
    (erase the old clone)

    NewCloneDB:
    delete from sys.link$ where name=’NEEDTHISLINK’;
    create database link tocache connect to luser … using ‘CacheDB’;
    insert into link$ select * from mylink@tocache;
    commit;
    shutdown immediate
    startup

    It might also be possible to do this with export/import with a WHERE clause.

  6. Pingback: [Oracle] 在不知道密碼狀況下複製DB Link | 寰葛格的分享空間

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.