Renomear usuários no Oracle 11g com DBLink de Loopback

This post is also available in: English

Nesse artigo, iremos lidar com uma situação muito comum em que o DBA recebe a tarefa de renomear ou efetuar a cópia de um usuário no próprio Banco de Dados. Nas linhas abaixo, vou explicar como efetuar esta cópia sem a necessidade de maiores complexidades e de gerar arquivos de dumps intermediários, utilizando o import DataPump com um dblink de loopback.

Artigo Publicado na OTN: http://www.oracle.com/technetwork/pt/articles/sql/clonando-usuarios-dblink-loopback-2354073-ptb.html

Essa tarefa pode ser feita em 3 etapas rápidas:

1. Criar um dblink de loopback temporário.
2. Executar o DataPump Import para clonar o usuário.
3. Remover o dblink temporário criado no item 1.

Vamos então começar:

1) Criar um dblink de loopback temporário.

Neste exemplo, iremos clonar todo o usuário SCOTT com seus objetos e dados para um novo, SCOTT_BKP. Faremos esta tarefa com o usuário "DBADMIN", que é um DBA desta base.

O primeiro passo será criar o dblink de loopback temporário. Supondo que o SERVICE_NAME do BD seja "ORCL" e ele esteja executando na porta "1521":

create database link TMP_DBLINK
using '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL= TCP) (HOST=127.0.0.1)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=ORCL)))';

Você também pode usar apenas o nome da entrada de TNS, caso exista uma que já aponte localmente para o próprio BD. Supondo, por exemplo, que há um TNS Names com nome "ORCL":

create database link TMP_DBLINK using 'ORCL';

Note que em ambos os casos omiti a cláusula "connect to" para forçar uma conexão ao banco com o usuário local que está conectado (DBAMIN, no caso). Depois de criá-lo, teste-o executando:

SQL> select * from dual@TMP_DBLINK;

D
-
X

2) Executar o DataPump Import para clonar o usuário.

O próximo passo será efetuar diretamente o clone do usuário. Se você estiver usando este tutorial para renomear um usuário, garanta que não existe qualquer transação, DDL ou DML em seus objetos.

Execute o bloco PL/SQL:

DECLARE
	H1 NUMBER;
	HSTATUS VARCHAR2(200);
	FROMUSER VARCHAR2(30);
	TOUSER VARCHAR2(30);
BEGIN
	FROMUSER := 'SCOTT';
	TOUSER := 'SCOTT_BKP';
	H1 := DBMS_DATAPUMP.OPEN(OPERATION => 'IMPORT', JOB_MODE => 'SCHEMA', REMOTE_LINK => 'TMP_DBLINK');
	DBMS_DATAPUMP.METADATA_FILTER(HANDLE => H1, NAME => 'SCHEMA_LIST', VALUE => '''' || FROMUSER || '''');
	DBMS_DATAPUMP.METADATA_REMAP(HANDLE => H1, NAME => 'REMAP_SCHEMA', OLD_VALUE => FROMUSER, VALUE => TOUSER);
	DBMS_DATAPUMP.START_JOB(HANDLE => H1);
	DBMS_DATAPUMP.WAIT_FOR_JOB(HANDLE => H1, JOB_STATE => HSTATUS);
	DBMS_OUTPUT.PUT_LINE('STATUS = ' || HSTATUS);
END;
/

O tempo de execução do script varia de acordo com a quantidade de objetos e dados do usuário. Verifique se o output foi "STATUS = COMPLETED". Caso não tenha sido, siga as etapas da última parte deste artigo.

3) Remover o dblink temporário criado no item 1.

Por fim, confira se todos os objetos foram duplicados com o SQL abaixo:

select   owner,object_type,status,count(*)
from     dba_objects
where    owner in ('SCOTT','SCOTT_BKP')
group by owner,object_type,status
order by 2,1,3;

Se tudo estiver idêntico, remova o dblink temporário criado:

drop database link TMP_DBLINK;

Pronto, agora você tem uma cópia idêntica do seu schema no seu BD! Se você estiver usando este tutorial para renomear um usuário, você pode agora remover o usuário antigo ou guarda-lo como um backup.

Verificando em caso de Falhas

Se tiver problemas durante a checagem de objetos, é possível ativar o log do import datapump para identificar a causa da falha. Para tanto, será necessário criar um diretório temporário para o log:

create directory TMP_DIR as '/tmp';

Em seguida, adicione a instrução abaixo após a linha do PL/SQL com o comando DBMS_DATAPUMP.OPEN:

DBMS_DATAPUMP.ADD_FILE(
	HANDLE => H1,
	FILENAME => 'imp.log',
	DIRECTORY => 'TMP_DIR',
	FILETYPE => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE
);

Por fim, após corrigir o erro e executar o comando corretamente, não esqueça de remover o diretório temporário:

drop directory TMP_DIR;

+9