dblink

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 dual@TO_AMIT;
select sysdate from dual@TO_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 dual@to_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 dual@to_Amit;

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