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
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;
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 !!
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 ?
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$
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’ ;