Recreating Database Link

I was trying to recreate database links as part of database migration activity. This involved mixture of 8i,9i and 10g database.

In case of 8i/9i it is very easy to find the old password which was used to create database link as passwords for database links are stored in Clear Text (decrypted format) in SYS.LINK$ .As a result we can easily re-create database links. Find below steps to perform the same

SQL> set pages 1000 lines 80
SQL> col db_link for a20
SQL> col host for a20
SQL> col user_id for a20
SQL> SELECT NAME,HOST,USERID,PASSWORD FROM SYS.LINK$;

NAME            HOST                 USERID                 PASSWORD
-------------- -------------------- ----------------- -----------------
TO_AMIT         AMITDB             	 AMIT                 AMIT123

create database link TO_AMIT connect to AMIT identified by AMIT123 using 'AMITDB';
Database link created.

In case of 10g passwords are stored in encrypted format in SYS.LINK$. I tried to workaround this by recreating using identified by values.

SQL> create database link TO_AMIT connect to AMIT identified by values '14573425Z6D15503' using 'AMITDB';

Database link created.

SQL> select sysdate from [email protected]_AMIT;
select sysdate from [email protected]_APREAD
                         *
ERROR at line 1:
ORA-00600: internal error code, arguments: [kzdlk_zt2 err], [18446744073709551603], [], [], [], [], [], []

I researched metalink and found Note 456320.1 according to which this is expected behavior and 'identified by values' can be used only by Oracle Internal utilities. Therefore only way to re-create database link is to use exp/imp. As this is 10g, EXPDP and IMPDP is better suited as it will help in  exporting/importing database links only.


SQL> select * from DATABASE_EXPORT_OBJECTS where object_path like '%LINK%';

OBJECT_PATH                              COMMENTS                                           N
---------------------------------------- -------------------------------------------------- -
DATABASE_EXPORT/SCHEMA/DB_LINK           Private and public database links
DB_LINK                                  Private and public database links                  Y
SCHEMA/DB_LINK                           Private and public database links

From above we can find that we need to use DB_LINK in INCLUDE clause for exporting Database Links. Find below steps which I used to re-create database link in another schema

SQL> conn test/test123
Connected.
SQL> create database link TO_AMIT connect to AMIT identified by amit123 using 'test10';

Database link created.

SQL> select sysdate from [email protected]_amit;

SYSDATE
---------
18-JAN-09

SQL> select owner,db_link,host from dba_db_links;

OWNER                          DB_LINK                                                      HOST
------------------------------ ------------------------------------------------------------ --------------------
TEST                           TO_AMIT.REGRESS.RDBMS.DEV.US.ORACLE.COM                      test10

D:\>expdp system/oracle schemas=TEST include=DB_LINK dumpfile=expdp.dmp

Export: Release 10.2.0.1.0 - Production on Sunday, 18 January, 2009 16:18:06

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** schemas=TEST include=DB_LINK dumpfile=expdp.dmp
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/DB_LINK
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  G:\ORACLE\PRODUCT\10.2.0\ADMIN\TEST10\DPDUMP\EXPDP.DMP
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 16:18:10

D:\>impdp system/oracle remap_schema=TEST:AMIT include=DB_LINK dumpfile=expdp.dmp

Import: Release 10.2.0.1.0 - Production on Sunday, 18 January, 2009 16:20:38

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** remap_schema=TEST:AMIT include=DB_LINK dumpfile=expdp.dmp
Processing object type SCHEMA_EXPORT/DB_LINK
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 16:20:40

D:\>exit

SQL> l
  1* select owner,db_link,host from dba_db_links
SQL> /

OWNER                          DB_LINK                                                      HOST
------------------------------ ------------------------------------------------------------ --------------------
TEST                           TO_AMIT.REGRESS.RDBMS.DEV.US.ORACLE.COM                      test10
AMIT                           TO_AMIT.REGRESS.RDBMS.DEV.US.ORACLE.COM                      test10

SQL> conn amit/amit123
Connected.
SQL> select sysdate from [email protected]_Amit;

SYSDATE
---------
18-JAN-09

4 thoughts on “Recreating Database Link

  1. I read the metalink note.
    I think it boils down to the string for IDENTIFIED BY VALUES isn’t the one in DBA_USERS PASSWORD column.
    However DBMS_METADATA.GET_DDL does get the appropriate value.
    Try the following :

    create database link hr connect to hr identified by hr using ‘XE’;
    select dbms_metadata.get_ddl(‘DB_LINK’,’HR’) from dual;
    CREATE DATABASE LINK “HR”
    CONNECT TO “HR” IDENTIFIED BY VALUES ’05C5ECC5A6E55600878A1D2614915CA506′
    USING ‘XE’
    select * from [email protected];

    • Yeah.. I didn’t realize that DBMS_METADATA.GET_DDL can also be used. I tried the same and it worked 🙂

      SQL> select dbms_metadata.get_ddl(‘DB_LINK’,’TO_AMIT.REGRESS.RDBMS.DEV.US.ORACLE.COM’,’TEST’) from dual;

      SQL> select dbms_metadata.get_ddl(‘DB_LINK’,’TO_AMIT.REGRESS.RDBMS.DEV.US.ORACLE.COM’,’TEST’) from dual;

      Thanks Gary !!

  2. HI Amit,

    Thank you very much for the info and Gary thank you very much for the update.

    One question ;

    you said;

    In case of 10g passwords are stored in encrypted format in SYS.LINK$.

    I checked ;

    The password column on both 10.2.0.4/10.2.0.3 is empty. Do you use a lower version ?

  3. Actually password field is empty in link$ in 10g. I was trying to use password field from DBA_USERS view. While checking further I found that we can also get the encrypted password from field “passwordx” from SYS.LINK$

    
    SQL> SELECT NAME,HOST,USERID,PASSWORDX FROM SYS.LINK$;
    
    NAME                                     HOST       USERID     PASSWORDX
    ---------------------------------------- ---------- ---------- ----------------------------------------
    TO_AMIT.REGRESS.RDBMS.DEV.US.ORACLE.COM  test10     AMIT       0524F74E2CD3E3B4E10F290B25DBE943B6
    TO_AMIT.REGRESS.RDBMS.DEV.US.ORACLE.COM  test10     AMIT       0524F74E2CD3E3B4E10F290B25DBE943B6
    TO_AMIT.REGRESS.RDBMS.DEV.US.ORACLE.COM  test10     AMIT       0524F74E2CD3E3B4E10F290B25DBE943B6
    

    This is same as the password which we get from dbms_metadata.get_ddl function. Meanwhile in case of 8i you can use below query to extract script for creating database link for particular user

    select ‘create database link ‘||a.db_link||’ connect to ‘||a.username||’ identified by ‘||b.password||’ using ”’||a.HOST||”’;’ from dba_db_links a, sys.link$ b, dba_users c where a.owner=c.username and b.owner#=c.user_id and a.db_link=b.name and a.host=b.host and a.owner=’&user’ ;

Leave a Reply