We regularly get the request for a refresh of an OTA database.
Usually, the end-users would like for their settings to survive the refresh.
We use a couple of scripts to save these setting (eg. passwords and such), however, the database links didn’t survive last time.
Probably because the passwords aren’t visible anymore in 10g.
Update!: Not true, just had a peek in SYS.LINK$ ver 10.1.05 and column AUTHPWD still had (unencrypted!) values.
Update2!: Somewhat true, just had a peek in SYS.LINK$ ver 10.2.0.1 & 10.2.0.2 and column PASSWORDX has encrypted values.
It is possible to retrieve the passwords using dbms_metadata.get_ddl.
More after the jump:
[ZZXJFF@OCP04:W2ZZXJFF02]
SQL> CREATE DATABASE LINK "JEROEN"
2 connect to "USERNAME"
3 IDENTIFIED BY "PASSWORD"
4 using 'CONN_DESCR';
[ZZXJFF@OCP04:W2ZZXJFF02]
SQL> select * from dba_db_links;
OWNER DB_LINK
------------------------------ ---------------------------------
HOST
----------------------------------------------------------------
CREATED
----------
ZZXJFF JEROEN.W2ZZXJFF02.NL
CONN_DESCR
29-12-2008
[ZZXJFF@OCP04:W2ZZXJFF02]
SQL> select dbms_metadata.get_ddl('DB_LINK','JEROEN.W2ZZXJFF02.NL','ZZXJFF') from dual;
DBMS_METADATA.GET_DDL('DB_LINK','JEROEN.W2ZZXJFF02.NL','ZZXJFF')
--------------------------------------------------------------------------------
CREATE DATABASE LINK "JEROEN.W2ZZXJFF02.NL"
CONNECT TO "USERNAME" IDENTIFIED BY "PASSWORD"
USING 'CONN_DESCR'
Use the following “as SYSDBA” to get the required info about existing database links:
COLUMN link_owner FORMAT A14 COLUMN db_link FORMAT A21 COLUMN remote_host FORMAT A8 COLUMN remote_user FORMAT A16 COLUMN remote_password FORMAT A15 SELECT u.username as link_owner , l.name as db_link , d.host as remote_host , l.userid as remote_user , l.password as remote_password FROM DBA_USERS u , LINK$ l , DBA_DB_LINKS d WHERE l.owner# = u.user_id AND d.owner = u.username AND l.name = d.db_link / LINK_OWNER DB_LINK REMOTE_HOST REMOTE_USER REMOTE_PASSWORD -------------- ---------------------------- ----------- ----------- --------------- JEROEN JEROEN.W2ZZXJFF02.NL JEROEN RJEROEN RPASSWORD JEROEN2 JEROEN2.W2ZZXJFF02.NL JEROEN2 RJEROEN2 RPASSWORD2 JEROEN3 JEROEN3.W2ZZXJFF02.NL JEROEN3 RJEROEN3 RPASSWORD3
It is still very good possible, to recreate the lost database links after the restore using the encrypted values stored in SYS.LINK$.
[ZZXJFF@OCP04:W2ZZXJFF02]
SQL> CREATE DATABASE LINK "JEROEN.W2ZZXJFF02.NL"
2 CONNECT TO "USERNAME" IDENTIFIED BY "PASSWORD"
3 USING 'CONN_DESCR';
Database link created
[SYS@OCP04:W2ZZXJFF02]
SQL> select OWNER#,NAME,HOST,USERID,PASSWORDX,AUTHPWDX from LINK$;
OWNER# NAME
---------- ----------------------------------------------------------
HOST
---------------------------------------------------------------------------------
USERID PASSWORDX
------------------------------ --------------------------------------------------
61 JEROEN.W2ZZXJFF02.NL
CONN_DESCR
USERNAME 05F9E6AA34E07D2B1FEADE0DDE0003AECF32C924318416C593
[ZZXJFF@OCP04:W2ZZXJFF02]
SQL> CREATE DATABASE LINK "JEROEN2.W2ZZXJFF02.NL"
2 CONNECT TO "USERNAME" IDENTIFIED BY VALUES '05F9E6AA34E07D2B1FEADE0DDE0003AECF32C924318416C593'
3 USING 'CONN_DESCR'
Database link created
[SYS@OCP04:W2ZZXJFF02]
SQL> select OWNER#,NAME,HOST,USERID,PASSWORDX,AUTHPWDX from LINK$;
OWNER# NAME
---------- ----------------------------------------------------------
HOST
-----------------------------------------------------------------------------------
USERID PASSWORDX
------------------------------ ----------------------------------------------------
AUTHPWDX
-----------------------------------------------------------------------------------
61 JEROEN.W2ZZXJFF02.NL
CONN_DESCR
USERNAME 05F9E6AA34E07D2B1FEADE0DDE0003AECF32C924318416C593
61 JEROEN2.W2ZZXJFF02.NL
CONN_DESCR
USERNAME 05F9E6AA34E07D2B1FEADE0DDE0003AECF32C924318416C593
Easy as pie
P.s.: The hashing of the passwords in the SYS.LINK$ is different than the one used in SYS.USER$.
My colleague tried to use the value of the hash of his password from SYS.USER$ and got the following result:
[VZC79Q@DFR07:W2VZC79Q01]
SQL> CREATE DATABASE LINK MARCEL.W2VZC79Q01.NL
2 CONNECT TO "USERNAME2" IDENTIFIED BY VALUES '7G0E9C8K8B3C3E69' USING 'CONN_DESCR';
Database link created.
[VZC79Q@DFR07:W2VZC79Q01]
SQL> select * from dual@marcel
2 ;
select * from dual@marcel
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kzdlk_zt2 err],
[18446744073709551603], [], [], [], [], [], []
I tried to look up the ORA-00600 using my first Oracle-related post and this is the explanation that Oracle gives, seems fair enough:
The only possible fix for this bug is to raise a user error rather than an internal error in this case. This won’t make the error go away but it might be more informative to the user, however, seeing as this functionality is only intended to be used by internal Oracle utilities it seems that an internal error may be more appropriate.
Therefore this exception is not a bug.