Recriando um DBLink sem conhecimento da password

Objetivo:

Precisamos recriar alguns DBLinks porem não temos as senhas dos usuários utilizados nesses DBLinks, para as versões 8i e 9i podemos utilizar SYS.LINK$, mas na versão 10g o campo de senha esta criptografado, nesse caso teremos que utilizar o DATAPUMP.

Ambiente:

SO: Linux 2.6.9-67.0.0.0.1.EL i686 i386 GNU/Linux
Base: Oracle Database 9.2.0.4.0 e Oracle Database 10.2.0.5.0

Tópicos: Base 8i/9i

1. Setup;
2. Listar o conteudo da tabela SYS.LINK$;
3. Gerar o comando para criação do DBLink;
4. Recriar o DBLink em outro esquema;

1. Setup

Vamos criar um DBLink numa base 9i para o usuário SCOTT que irá acessar tabelas do esquema HR:

SQL> conn scott/oracle
Connected.
SQL> create database link HR_LINK connect to hr identified by oracle USING 'LAB1';

Database link created.

SQL> select count(*) from EMPLOYEES@HR_LINK;

  COUNT(*)
----------
       107

SQL>

.

2. Listar o conteudo da tabela SYS.LINK$

SQL> set lines 120 pages 66
col name for a25
col host for a10
col userid for a10
col password for a20
SELECT NAME,HOST,USERID,PASSWORD FROM SYS.LINK$;

NAME                      HOST       USERID     PASSWORD
------------------------- ---------- ---------- --------------------
HR_LINK.US.ORACLE.COM     LAB1       HR         ORACLE

SQL>

.

3. Gerar o comando para criação do DBLink

SQL> SELECT 'create '||DECODE(U.NAME,'PUBLIC','public ')||'database link '||CHR(10)
  2         ||DECODE(U.NAME,'PUBLIC',Null, U.NAME||'.')|| L.NAME||chr(10)
  3         ||'connect to '|| L.USERID || ' identified by "'
  4         ||L.PASSWORD||'" using ''' || L.host || ''';' AS "CREATE DBLINKS"
  5    FROM sys.link$ L, sys.user$ U
  6   WHERE L.OWNER# = U.USER#;

CREATE DBLINKS
-------------------------------------------------------------------------------------
create database link
SCOTT.HR_LINK.US.ORACLE.COM
connect to HR identified by "ORACLE" using 'LAB1';
SQL>

.

4. Recriar o DBLink em outro esquema

SQL> conn teste/oracle
Connected.
SQL> create database link
  2  HR_LINK.US.ORACLE.COM
  3  connect to HR identified by "ORACLE" using 'LAB1';

Database link created.

SQL> select count(*) from EMPLOYEES@HR_LINK;

  COUNT(*)
----------
       107

SQL>

.

Tópicos: Base 10g

1. Setup;
2. Listar o conteudo da tabela SYS.LINK$;
3. Export do DBLink com o DATAPUMP;
4. Import do DBLink com o DATAPUMP;
5. Verificar se novo DBLINK esta funcionando;

1. Setup

Vamos criar um DBLink numa base 10g para o usuário SCOTT que irá acessar tabelas do esquema HR:

SQL> conn sys/oracle as sysdba;
Connected.
SQL> grant create database link to scott;

Grant succeeded.

SQL> conn scott/oracle
Connected.
SQL> create database link HR_LINK connect to hr identified by oracle USING 'LAB1';

Database link created.

SQL> select count(*) from EMPLOYEES@HR_LINK;

  COUNT(*)
----------
       107

SQL>

.

2. Listar o conteudo da tabela SYS.LINK$

Quando listamos a tabela SYS.LINK$ , verificamos que a password não aparece no formato caracter:

SQL> set lines 120 pages 66
col name for a40
col host for a10
col userid for a10
col password for a20
SELECT NAME,HOST,USERID,PASSWORD FROM SYS.LINK$;

NAME                                     HOST       USERID     PASSWORD
---------------------------------------- ---------- ---------- --------------------
HR_LINK.REGRESS.RDBMS.DEV.US.ORACLE.COM  LAB1       HR

SQL>

.

3. Export do DBLink com o DATAPUMP

Para recriar o DBLink em outro esquema podemos utilizar o DATAPUMP com a opção INCLUDE para selecionar somente DBLinks:

[oracle@odbsrv02 ~]$ expdp system/oracle SCHEMAS=scott INCLUDE=DB_LINK DUMPFILE=scott_dblink.dmp DIRECTORY=DATA_PUMP_DIR

Export: Release 10.2.0.5.0 - Production on Wednesday, 07 July, 2010 10:18:48

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** SCHEMAS=scott INCLUDE=DB_LINK DUMPFILE=scott_dblink.dmp DIRECTORY=DATA_PUMP_DIR
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:
  /u01/app/oracle/product/10.2.0/db_1/admin/lab1/dpdump/scott_dblink.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 10:19:01

[oracle@odbsrv02 ~]$

.

4. Import do DBLink com o DATAPUMP

No import podemos utilizar a opção REMAP_SCHEMA para indicar o esquema onde queremos que o DBLink seja criado:

[oracle@odbsrv02 ~]$ impdp system/oracle REMAP_SCHEMA=scott:tstuser INCLUDE=DB_LINK DUMPFILE=scott_dblink.dmp DIRECTORY=DATA_PUMP_DIR

Import: Release 10.2.0.5.0 - Production on Wednesday, 07 July, 2010 10:21:11

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** REMAP_SCHEMA=scott:tstuser INCLUDE=DB_LINK DUMPFILE=scott_dblink.dmp DIRECTORY=DATA_PUMP_DIR
Processing object type SCHEMA_EXPORT/DB_LINK
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 10:21:15
[oracle@odbsrv02 ~]$

.

5. Verificar se novo DBLINK esta funcionando

SQL> conn tstuser/oracle
Connected.
SQL> select count(*) from EMPLOYEES@HR_LINK;

  COUNT(*)
----------
       107

SQL>