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

Related Tags: 10g, database, dblink, ORA-600, oracle

Previous Topic: Creating Oracle Extended RAC on Oracle VM

Next Topic: Input is too long (> 2499 characters) – line ignored

4 Comments for “Recreating Database Link”

Gary says:
January 29, 2009 at 12:17 am

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 dual@hr;

Reply
- Amit says:
  January 30, 2009 at 5:21 pm
  
  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 !!
  
  Reply
coskan says:
March 10, 2009 at 9:08 pm

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 ?

Reply
Amit says:
March 12, 2009 at 6:25 pm

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’ ;

Reply