Para melhor visualização, recomendo resolução de no mínimo 1024 x 768 e navegadores Mozilla Firefox ou Google Chrome


quarta-feira, 1 de outubro de 2014

Concedendo privilégios de objetos no Oracle com a stored procedure CREATE_SYNONYM_GRANT_DML_PRIVS

Por Eduardo Legatti

Olá,

No artigo de Setembro/2014 eu compartilhei aqui no blog a stored procedure DROP_USER_DISCONNECT_SESSION que tem como objetivo fazer o trabalho de desconectar as sessões correntes de um determinado usuário de banco de dados, bloquear o schema (account lock) enquanto as sessões correntes vão sendo eliminadas, e por fim, dropar o schema. Neste artigo irei compartilhar uma outra stored procedure chamada de CREATE_SYNONYM_GRANT_DML_PRIVS que foi desenvolvida com o intuito de facilitar a criação/exclusão de sinônimos e a concessão/revogação de privilégios de objetos (grant e revoke) aos usuários de bancos de dados. Por questões de segurança, é comum ter aplicações que se conectam no banco de dados à partir de múltiplos usuários. Por exemplo, imagine uma aplicação de RH que contém os objetos de schema de banco de dados criados no schema chamado RH. A aplicação não irá se conectar com o usuário RH, mas sim com um outros usuários criados no banco de dados que possuem privilégios para acessar os objetos do schema RH. Neste caso, esses usuários teriam apenas sinônimos apontando para os objetos do schema RH e privilégios DML (SELECT, INSERT, UPDATE, DELETE). Para exemplificar, teríamos o schema RH que seria o owner (dono) das tabelas, índices, etc e outros usuários como PAULA, MARCELO e JOAO.

Os usuários PAULA, MARCELO e JOAO somente teriam privilégios DML (SELECT, INSERT, UPDATE, DELETE) para acessarem os objetos do schema RH. Por questões de segurança, nenhum privilégio DDL como ALTER, DROP, CREATE seriam concedidos a estes usuários. Para evitar que os objetos tenham que ser qualificados com o nome do schema, sinônimos privados serão criados para cada usuário.

Enfim, para facilitar a concessão de privilégios para usuários que deverão ter acessos DML a objetos de um outro usuário, eu criei a stored procedure CREATE_SYNONYM_GRANT_DML_PRIVS que permitirá que à partir de um OWNER e um USER passados como parâmetros, sejam criados scripts ou executados os comandos de criação de sinônimos e de concessão de privilégios DML sobre todos os objetos do OWNER para o USER.

Irei criar a procedure CREATE_SYNONYM_GRANT_DML_PRIVS como demonstrado abaixo. Para evitar quaisquer problemas com privilégios, irei criá-la no usuário SYS. Se desejar, a criação de um sinônimo e a concessão do privilégio EXECUTE poderá ser concedido a um outro usuário no banco de dados, como por exemplo, o SYSTEM.
 
C:\>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Qua Out 1 08:51:01 2014

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Conectado a:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning option 
SQL> create or replace procedure create_synonym_grant_dml_privs
  2    (p_owner VARCHAR2,
  3     p_user VARCHAR2,
  4     p_operation VARCHAR2,
  5     p_type VARCHAR2,
  6     p_access VARCHAR2 DEFAULT 'A'
  7    )
  8  as
  9     l_word VARCHAR2(10);
 10     l_operation VARCHAR2(10);
 11     l_type VARCHAR2(10);
 12     l_errm VARCHAR2 (2000);
 13     l_dml VARCHAR2 (100);
 14     l_qtd_owner NUMBER;
 15     l_qtd_user NUMBER;
 16     invalid_parameter EXCEPTION;
 17     invalid_owner EXCEPTION;
 18     invalid_user EXCEPTION;
 19     invalid_operation EXCEPTION;
 20     invalid_type EXCEPTION;
 21     invalid_owneruser EXCEPTION;
 22     invalid_schema EXCEPTION;
 23  BEGIN
 24    IF p_owner is NULL or p_user is NULL or p_operation is NULL or p_type is NULL THEN
 25       RAISE invalid_parameter;
 26    END IF;
 27
 28    IF UPPER (p_owner) IN ('PUBLIC','SYSTEM','SYS') OR UPPER (p_user) IN ('PUBLIC','SYSTEM','SYS') THEN
 29        RAISE invalid_schema;
 30    END IF;
 31
 32    SELECT COUNT (*) INTO l_qtd_owner FROM all_users WHERE username = UPPER (p_owner);
 33      IF l_qtd_owner = 0 THEN
 34          RAISE invalid_owner;
 35    END IF;
 36
 37    IF UPPER (p_owner) = UPPER (p_user) THEN
 38       RAISE invalid_owneruser;
 39    END IF;
 40
 41    l_operation := UPPER (p_operation);
 42    l_type := UPPER (p_type);
 43
 44    IF l_operation NOT IN ('GRANT','REVOKE') THEN
 45       RAISE invalid_operation;
 46    END IF;
 47
 48    IF l_type NOT IN ('EXECUTE','SPOOL') THEN
 49       RAISE invalid_type;
 50    END IF;
 51
 52    IF l_operation = 'GRANT' THEN
 53       l_word := 'TO';
 54       IF upper(p_access) = 'R' THEN
 55          l_dml := 'SELECT';
 56       ELSE
 57          l_dml := 'SELECT, INSERT, UPDATE, DELETE';
 58       END IF;
 59    ELSIF l_operation = 'REVOKE' THEN
 60       l_word := 'FROM';
 61       l_dml := 'ALL';
 62    END IF;
 63
 64    IF l_type = 'EXECUTE' THEN
 65       SELECT COUNT (*) INTO l_qtd_user FROM all_users WHERE username = UPPER (p_user);
 66       IF l_qtd_user = 0 THEN
 67           RAISE invalid_user;
 68       END IF;
 69    END IF;
 70
 71    FOR C1 IN (SELECT OBJECT_NAME, OBJECT_TYPE
 72                 FROM ALL_OBJECTS
 73                WHERE OWNER = UPPER (p_owner)
 74                      AND OBJECT_TYPE IN ('TABLE')
 75                      AND OBJECT_NAME NOT LIKE ('MLOG$\_%') ESCAPE '\'
 76                      AND OBJECT_NAME NOT LIKE ('RUPD$\_%') ESCAPE '\'
 77                      AND OBJECT_NAME NOT IN
 78                             (SELECT OBJECT_NAME FROM ALL_OBJECTS
 79                               WHERE OWNER = UPPER (p_owner)
 80                                     AND OBJECT_TYPE IN ('MATERIALIZED VIEW')
 81                              UNION ALL
 82                              SELECT OBJECT_NAME FROM DBA_RECYCLEBIN
 83                               WHERE OWNER = UPPER (p_owner))
 84               UNION ALL
 85               SELECT OBJECT_NAME, OBJECT_TYPE
 86                 FROM ALL_OBJECTS
 87                WHERE OWNER = UPPER (p_owner) AND OBJECT_TYPE IN ('MATERIALIZED VIEW')
 88               UNION ALL
 89               SELECT OBJECT_NAME, OBJECT_TYPE
 90                 FROM ALL_OBJECTS
 91                WHERE OWNER = UPPER (p_owner)
 92                      AND OBJECT_TYPE IN ('VIEW', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'SEQUENCE')
 93               ORDER BY OBJECT_TYPE, OBJECT_NAME)
 94    LOOP
 95      IF C1.OBJECT_TYPE IN ('TABLE') THEN
 96        IF l_type = 'EXECUTE' THEN
 97          BEGIN
 98            EXECUTE IMMEDIATE l_operation||' '||l_dml||' ON "'||upper(p_owner)||'"."'||C1.OBJECT_NAME||'" '||l_word||' "'||upper(p_user)||'"';
 99          EXCEPTION
100            WHEN OTHERS THEN NULL;
101          END;
102        ELSIF l_type = 'SPOOL' THEN
103          dbms_output.put_line (l_operation||' '||l_dml||' ON "'||upper(p_owner)||'"."'||C1.OBJECT_NAME||'" '||l_word||' "'||upper(p_user)||'";');
104        END IF;
105      ELSIF C1.OBJECT_TYPE IN ('SEQUENCE','VIEW','MATERIALIZED VIEW') THEN
106        IF l_type = 'EXECUTE' THEN
107          BEGIN
108            EXECUTE IMMEDIATE l_operation||' SELECT'||' ON "'||upper(p_owner)||'"."'||C1.OBJECT_NAME||'" '||l_word||' "'||upper(p_user)||'"';
109          EXCEPTION
110            WHEN OTHERS THEN NULL;
111          END;
112        ELSIF l_type = 'SPOOL' THEN
113          dbms_output.put_line (l_operation||' SELECT'||' ON "'||upper(p_owner)||'"."'||C1.OBJECT_NAME||'" '||l_word||' "'||upper(p_user)||'";');
114        END IF;
115      ELSIF C1.OBJECT_TYPE IN ('PROCEDURE','FUNCTION','PACKAGE') THEN
116        IF l_type = 'EXECUTE' THEN
117          BEGIN
118            EXECUTE IMMEDIATE l_operation||' EXECUTE ON "'||upper(p_owner)||'"."'||C1.OBJECT_NAME||'" '||l_word||' "'||upper(p_user)||'"';
119          EXCEPTION
120            WHEN OTHERS THEN NULL;
121          END;
122        ELSIF l_type = 'SPOOL' THEN
123          dbms_output.put_line (l_operation||' EXECUTE ON "'||upper(p_owner)||'"."'||C1.OBJECT_NAME||'" '||l_word||' "'||upper(p_user)||'";');
124        END IF;
125      END IF;
126
127      IF l_operation = 'GRANT' THEN
128        IF l_type = 'EXECUTE' THEN
129          BEGIN
130            EXECUTE IMMEDIATE 'CREATE OR REPLACE SYNONYM "'||upper(p_user)||'"."'||C1.OBJECT_NAME||'" FOR "'||upper(p_owner)||'"."'||C1.OBJECT_NAME||'"';
131          EXCEPTION
132            WHEN OTHERS THEN NULL;
133          END;
134        ELSIF l_type = 'SPOOL' THEN
135          dbms_output.put_line ('CREATE OR REPLACE SYNONYM "'||upper(p_user)||'"."'||C1.OBJECT_NAME||'" FOR "'||upper(p_owner)||'"."'||C1.OBJECT_NAME||'";');
136        END IF;
137      ELSIF l_operation = 'REVOKE' THEN
138        IF l_type = 'EXECUTE' THEN
139          BEGIN
140            EXECUTE IMMEDIATE 'DROP SYNONYM "'||upper(p_user)||'"."'||C1.OBJECT_NAME||'"';
141          EXCEPTION
142            WHEN OTHERS THEN NULL;
143          END;
144        ELSIF l_type = 'SPOOL' THEN
145          dbms_output.put_line ('DROP SYNONYM "'||upper(p_user)||'"."'||C1.OBJECT_NAME||'";');
146        END IF;
147      END IF;
148    END LOOP;
149  EXCEPTION
150     WHEN invalid_parameter THEN
151        raise_application_error (-20001,'Invalid parameters. ');
152     WHEN invalid_owner THEN
153        raise_application_error (-20001,'The OWNER especified does not exist.');
154     WHEN invalid_user THEN
155        raise_application_error (-20001,'The USER especified does not exist.');
156     WHEN invalid_owneruser THEN
157        raise_application_error (-20001,'The OWNER/USER cannot be the same.');
158     WHEN invalid_schema THEN
159        raise_application_error (-20001,'The OWNER/USER specified is not permitted.');
160     WHEN invalid_operation THEN
161        raise_application_error (-20001,'Invalid operation. Use (GRANT/REVOKE).');
162     WHEN invalid_type THEN
163        raise_application_error (-20001,'Invalid type. Use (EXECUTE/SPOOL).');
164     WHEN OTHERS THEN
165        l_errm := SQLERRM;
166        raise_application_error (-20001, l_errm);
167  END;
168  /

Procedimento criado.

Segue abaixo os parâmetros que precisarão ser informados: 

SQL> desc create_synonym_grant_dml_privs
PROCEDURE create_synonym_grant_dml_privs
 Nome do Argumento                  Tipo                In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_OWNER                        VARCHAR2                IN
 P_USER                         VARCHAR2                IN
 P_OPERATION                    VARCHAR2                IN
 P_TYPE                         VARCHAR2                IN
 P_ACCESS                       VARCHAR2                IN     DEFAULT
 

  • P_OWNER: Nome do OWNER dos objetos.
  • P_USER: Nome do USER para o qual serão criados/excluídos os sinônimos dos objetos e concedidos/revogados os privilégios sobre os mesmos.
  • P_OPERATION: Operação a ser realizada. Possui suas opções:
    • GRANT: concede privilégios de acordo com o tipo de cada objeto e cria de sinônimos.
    • REVOKE: revoga privilégios concedidos e exclui sinônimos criados.
  • P_TYPE: Tipo do resultado da execução da stored procedure. Possui duas opções:
    • EXECUTE: executa os comandos de criação de sinônimos e concessão de privilégios.
    • SPOOL: gera uma saída (spool) dos comandos de criação de sinônimos e concessão de privilégios na tela.
  • P_ACCESS: Intruções DML que serão concedidas. Possui duas opções:
    • A: É o valor o default, ou seja, todas as instruções DML (SELECT, INSERT, DELETE , UPDATE) serão concedidas ao usuário.
    • R: Concede ao usuário apenas acesso leitura (SELECT).

Vale a pena salientar que somente serão aceitos parâmetros válidos. Por exemplo, NULL não é um valor válido. Deverão ser especificados usuários OWNER/USER existentes e distintos. Usuários como PUBLIC, SYSTEM e SYS não poderão ser utilizados.

Segue abaixo os objetos que a stored procedure irá analisar.

+----------------------------------------------------+
|Objeto             |  Privilégios                   |
|----------------------------------------------------|
| Table             | Select, Insert, Update, Delete |
| View              | Select                         |
| Materialized View | Select                         |
| Sequence          | Select                         |
| Procedure         | Execute                        |
| Function          | Execute                        |
| Package           | Execute                        |
+----------------------------------------------------+

OBS: Caso seja passado no parâmetro P_ACCESS o valor 'R', então apenas o privilégio SELECT será concedido ao usuário.

A stored procedure irá gerar os comandos de concessão de privilégios e criação de sinônimos dos objetos do OWNER para o USER de acordo com o tipo do objeto. Para demonstração, irei usar o schema RH como owner e o usuário JOAO como user. Segue abaixo os objetos existentes e de propriedade do schema RH.

SQL> select owner,object_name,object_type from dba_objects where owner='RH';

OWNER                          OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------------------ -------------------
RH                             CUSTOMER                       TABLE
RH                             DEPT_VIEW                      VIEW
RH                             SEQUENCE_CUSTOMER              SEQUENCE
RH                             FC_CONCATENATE                 FUNCTION
RH                             SP_INCREASE_SALARY             PROCEDURE
RH                             MVIEW_EMPLOYEE                 TABLE
RH                             MVIEW_EMPLOYEE                 MATERIALIZED VIEW
RH                             SALARY                         TABLE

8 linhas selecionadas.  

Irei agora executar a procedure conforme exemplo abaixo:

SQL> set serveroutput on size 1000000
SQL> exec create_synonym_grant_dml_privs ('RH','JOAO','GRANT','SPOOL');

GRANT EXECUTE ON "RH"."FC_CONCATENATE" TO "JOAO";
CREATE OR REPLACE SYNONYM "JOAO"."FC_CONCATENATE" FOR "RH"."FC_CONCATENATE";
GRANT SELECT ON "RH"."MVIEW_EMPLOYEE" TO "JOAO";
CREATE OR REPLACE SYNONYM "JOAO"."MVIEW_EMPLOYEE" FOR "RH"."MVIEW_EMPLOYEE";
GRANT EXECUTE ON "RH"."SP_INCREASE_SALARY" TO "JOAO";
CREATE OR REPLACE SYNONYM "JOAO"."SP_INCREASE_SALARY" FOR "RH"."SP_INCREASE_SALARY";
GRANT SELECT ON "RH"."SEQUENCE_CUSTOMER" TO "JOAO";
CREATE OR REPLACE SYNONYM "JOAO"."SEQUENCE_CUSTOMER" FOR "RH"."SEQUENCE_CUSTOMER";
GRANT SELECT, INSERT, UPDATE, DELETE ON "RH"."CUSTOMER" TO "JOAO";
CREATE OR REPLACE SYNONYM "JOAO"."CUSTOMER" FOR "RH"."CUSTOMER";
GRANT SELECT, INSERT, UPDATE, DELETE ON "RH"."SALARY" TO "JOAO";
CREATE OR REPLACE SYNONYM "JOAO"."SALARY" FOR "RH"."SALARY";
GRANT SELECT ON "RH"."DEPT_VIEW" TO "JOAO";
CREATE OR REPLACE SYNONYM "JOAO"."DEPT_VIEW" FOR "RH"."DEPT_VIEW";

Procedimento PL/SQL concluído com sucesso.


No exemplo acima eu pedi apenas para gerar os comandos, em vez de executá-los diretamente. Para executar os comandos diretamente bastará apenas informar a opção EXECUTE:

SQL> exec create_synonym_grant_dml_privs ('RH','JOAO','GRANT','EXECUTE');

Procedimento PL/SQL concluído com sucesso.
 

Após a execução da procedure, poderemos verificar abaixo que foram criados sinônimos privados no usuário JOAO e que os privilégios foram concedidos com sucesso.

SQL> select owner,object_name,object_type from dba_objects where owner='JOAO';

OWNER                          OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------------------ -------------------
JOAO                           EXAMPLE                        SYNONYM
JOAO                           FC_CONCATENATE                 SYNONYM
JOAO                           MVIEW_EMPLOYEE                 SYNONYM
JOAO                           SP_INCREASE_SALARY             SYNONYM
JOAO                           SEQUENCE_CUSTOMER              SYNONYM
JOAO                           CUSTOMER                       SYNONYM
JOAO                           SALARY                         SYNONYM
JOAO                           DEPT_VIEW                      SYNONYM

8 linhas selecionadas.
  
SQL> select grantee,owner,table_name,grantor,privilege
  2    from dba_tab_privs
  3   where grantee='JOAO';

GRANTEE      OWNER        TABLE_NAME           GRANTOR            PRIVILEGE
------------ ------------ -------------------- ------------------ ----------------------
JOAO         RH           CUSTOMER             RH                 UPDATE
JOAO         RH           CUSTOMER             RH                 SELECT
JOAO         RH           CUSTOMER             RH                 INSERT
JOAO         RH           CUSTOMER             RH                 DELETE
JOAO         RH           DEPT_VIEW            RH                 SELECT
JOAO         RH           FC_CONCATENATE       RH                 EXECUTE
JOAO         RH           MVIEW_EMPLOYEE       RH                 SELECT
JOAO         RH           SALARY               RH                 UPDATE
JOAO         RH           SALARY               RH                 SELECT
JOAO         RH           SALARY               RH                 INSERT
JOAO         RH           SALARY               RH                 DELETE
JOAO         RH           SEQUENCE_CUSTOMER    RH                 SELECT
JOAO         RH           SP_INCREASE_SALARY   RH                 EXECUTE

13 linhas selecionadas.
  
Para fazer o inverso, ou seja, remover os sinônimos que foram criados no usuário JOAO e revogar os privilégios concedidos, bastará apenas informar a opção REVOKE. No exemplo abaixo irei usar a opção SPOOL para mostrar os comandos.

SQL> exec create_synonym_grant_dml_privs ('RH','JOAO','REVOKE','SPOOL');

REVOKE EXECUTE ON "RH"."FC_CONCATENATE" FROM "JOAO";
DROP SYNONYM "JOAO"."FC_CONCATENATE";
REVOKE SELECT ON "RH"."MVIEW_EMPLOYEE" FROM "JOAO";
DROP SYNONYM "JOAO"."MVIEW_EMPLOYEE";
REVOKE EXECUTE ON "RH"."SP_INCREASE_SALARY" FROM "JOAO";
DROP SYNONYM "JOAO"."SP_INCREASE_SALARY";
REVOKE SELECT ON "RH"."SEQUENCE_CUSTOMER" FROM "JOAO";
DROP SYNONYM "JOAO"."SEQUENCE_CUSTOMER";
REVOKE ALL ON "RH"."CUSTOMER" FROM "JOAO";
DROP SYNONYM "JOAO"."CUSTOMER";
REVOKE ALL ON "RH"."SALARY" FROM "JOAO";
DROP SYNONYM "JOAO"."SALARY";
REVOKE SELECT ON "RH"."DEPT_VIEW" FROM "JOAO";
DROP SYNONYM "JOAO"."DEPT_VIEW";
 
Caso seja necessário conceder apenas privilégios de leitura para o usuário, bastará apenas informar a opção 'R' como último parâmetro.

SQL> exec create_synonym_grant_dml_privs ('RH','JOAO','GRANT','SPOOL','R');

GRANT EXECUTE ON "RH"."FC_CONCATENATE" TO "JOAO";
CREATE OR REPLACE SYNONYM "JOAO"."FC_CONCATENATE" FOR "RH"."FC_CONCATENATE";
GRANT SELECT ON "RH"."MVIEW_EMPLOYEE" TO "JOAO";
CREATE OR REPLACE SYNONYM "JOAO"."MVIEW_EMPLOYEE" FOR "RH"."MVIEW_EMPLOYEE";
GRANT EXECUTE ON "RH"."SP_INCREASE_SALARY" TO "JOAO";
CREATE OR REPLACE SYNONYM "JOAO"."SP_INCREASE_SALARY" FOR "RH"."SP_INCREASE_SALARY";
GRANT SELECT ON "RH"."SEQUENCE_CUSTOMER" TO "JOAO";
CREATE OR REPLACE SYNONYM "JOAO"."SEQUENCE_CUSTOMER" FOR "RH"."SEQUENCE_CUSTOMER";
GRANT SELECT ON "RH"."CUSTOMER" TO "JOAO";
CREATE OR REPLACE SYNONYM "JOAO"."CUSTOMER" FOR "RH"."CUSTOMER";
GRANT SELECT ON "RH"."SALARY" TO "JOAO";
CREATE OR REPLACE SYNONYM "JOAO"."SALARY" FOR "RH"."SALARY";
GRANT SELECT ON "RH"."DEPT_VIEW" TO "JOAO";
CREATE OR REPLACE SYNONYM "JOAO"."DEPT_VIEW" FOR "RH"."DEPT_VIEW";

Procedimento PL/SQL concluído com sucesso.
 
Para finalizar, segue algumas validações que stored procedure realiza.

SQL> exec create_synonym_grant_dml_privs ('RH','MARIA','GRANT','EXECUTE');
BEGIN create_synonym_grant_dml_privs ('RH','MARIA','GRANT','EXECUTE'); END;
*
ERRO na linha 1:
ORA-20001: The USER especified does not exist.
ORA-06512: em "SYS.CREATE_SYNONYM_GRANT_DML_PRIVS", line 155
ORA-06512: em line 1

SQL> exec create_synonym_grant_dml_privs ('RH','RH','GRANT','EXECUTE');
BEGIN create_synonym_grant_dml_privs ('RH','RH','GRANT','EXECUTE'); END;
*
ERRO na linha 1:
ORA-20001: The OWNER/USER cannot be the same.
ORA-06512: em "SYS.CREATE_SYNONYM_GRANT_DML_PRIVS", line 157
ORA-06512: em line 1

SQL> exec create_synonym_grant_dml_privs ('SYSTEM','RH','GRANT','EXECUTE');
BEGIN create_synonym_grant_dml_privs ('SYSTEM','RH','GRANT','EXECUTE'); END;
*
ERRO na linha 1:
ORA-20001: The OWNER/USER specified is not permitted.
ORA-06512: em "SYS.CREATE_SYNONYM_GRANT_DML_PRIVS", line 159
ORA-06512: em line 1

SQL> exec create_synonym_grant_dml_privs ('RH','JOAO','GRANTS','SPOOL');
BEGIN create_synonym_grant_dml_privs ('RH','JOAO','GRANTS','SPOOL'); END;
*
ERRO na linha 1:
ORA-20001: Invalid operation. Use (GRANT/REVOKE).
ORA-06512: em "SYS.CREATE_SYNONYM_GRANT_DML_PRIVS", line 161
ORA-06512: em line 1

SQL> exec create_synonym_grant_dml_privs ('RH','JOAO','GRANT','SPOOLS');
BEGIN create_synonym_grant_dml_privs ('RH','JOAO','GRANT','SPOOLS'); END;
*
ERRO na linha 1:
ORA-20001: Invalid type. Use (EXECUTE/SPOOL).
ORA-06512: em "SYS.CREATE_SYNONYM_GRANT_DML_PRIVS", line 163
ORA-06512: em line 1

SQL> exec create_synonym_grant_dml_privs ('RH','JOAO','GRANT',NULL);
BEGIN create_synonym_grant_dml_privs ('RH','JOAO','GRANT',NULL); END;
*
ERRO na linha 1:
ORA-20001: Invalid parameters.
ORA-06512: em "SYS.CREATE_SYNONYM_GRANT_DML_PRIVS", line 151
ORA-06512: em line 1
 

segunda-feira, 1 de setembro de 2014

DROP USER: Removendo um schema no Oracle com a stored procedure DROP_USER_DISCONNECT_SESSION

Por Eduardo Legatti

Olá,

Imagine vários ambientes de bancos de dados como desenvolvimento, teste, homologação, produção, entre outros. Particularmente, em um ambiente de desenvolvimento, é comum na rotina de um DBA realizar tarefas de exportação e importação de schemas de bancos de dados. Dependendo da frequência desta tarefa, por exemplo, no caso de realizar uma importação para substituir um schema existente em um banco de dados, o schema atual precisará ser dropado. O problema acontece quando ao realizarmos o DROP de um schema, o erro "ORA-01940: não é possível eliminar um usuário conectado no momento" surge na tela. O que fazemos então? Acessamos a view dinâmica de desempenho V$SESSION para descobrir o SID e SERIAL# das sessões conectadas para então desconectá-las.

Bom, neste artigo irei compartilhar uma stored procedure chamada de DROP_USER_DISCONNECT_SESSION criada por mim para facilitar um pouco esta tarefa. O objetivo dela será basicamente dropar um schema no banco de dados. A vantagem de usá-la é que a mesma fará todo o trabalho de desconectar as sessões correntes, bloquear o schema (account lock) de forma a impedir que novas conexões sejam realizadas enquanto a stored procedure estiver desconectando as sessões atuais, e por fim, dropar o schema. Segue abaixo uma simulação para demonstrar o seu uso.

C:\>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Seg Set 1 11:58:37 2014

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Conectado a:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning option

SQL> drop user scott cascade;
drop user scott cascade
*
ERRO na linha 1:
ORA-01940: não é possível eliminar um usuário conectado no momento

Acima, é possível verificar  que, ao tentar o dropar o schema SCOTT, a mensagem de erro ORA-01940 foi emitida. No resultado do SQL abaixo poderemos verificar consultando a view de desempenho dinâmico V$SESSION que 3 sessões estão conectadas atualmente.
 

SQL> select sid,serial#,username,status,terminal
  2    from v$session
  3   where username='SCOTT';

       SID    SERIAL# USERNAME               STATUS      TERMINAL
---------- ---------- ---------------------- ----------- ----------------
        26         16 SCOTT                  INACTIVE    SERVER01
        28         17 SCOTT                  INACTIVE    MACHINE09
        34         41 SCOTT                  ACTIVE      MACHINE03

Irei criar abaixo a stored procedure DROP_USER_DISCONNECT_SESSION e executá-la. Para evitar quaisquer problemas com privilégios, irei criá-la no usuário SYS. Se desejar, a criação de um sinônimo e a concessão do privilégio EXECUTE poderá ser concedido a um outro usuário no banco de dados, como por exemplo, o SYSTEM.
 

SQL> create or replace procedure drop_user_disconnect_session (owner varchar)
  2  as
  3  begin
  4     declare
  5        l_cnt   integer;
  6     begin
  7        begin
  8           execute immediate 'alter user ' || upper (owner) || ' account lock';
  9        exception
 10           when others
 11           then
 12              null;
 13        end;
 14
 15        for c1 in (select *
 16                    from v$session
 17                   where username = upper (owner))
 18        loop
 19           begin
 20              execute immediate
 21                    'alter system disconnect session '''
 22                 || c1.sid
 23                 || ','
 24                 || c1.serial#
 25                 || ''' immediate';
 26           exception
 27              when others
 28              then
 29                 null;
 30           end;
 31        end loop;
 32
 33        loop
 34           select count (*)
 35             into l_cnt
 36             from v$session
 37            where username = upper (owner);
 38
 39           exit when l_cnt = 0;
 40           dbms_lock.sleep (10);
 41        end loop;
 42
 43        begin
 44           execute immediate 'drop user ' || owner || ' cascade';
 45        exception
 46           when others
 47           then
 48              null;
 49        end;
 50     end;
 51  exception
 52     when others
 53     then
 54        null;
 55  end;
 56  /

Procedimento criado.

Pronto. Após a criação da mesma, segue abaixo sua execução passando como parâmetro o schema SCOTT.
 

SQL> exec drop_user_disconnect_session('SCOTT');

Procedimento PL/SQL concluído com sucesso.

Após a execução da mesma, poderemos observar abaixo que as sessões foram desconectadas e o schema SCOTT foi dropado com sucesso.
 
SQL> select sid,serial#,username,status,terminal
  2    from v$session
  3   where username='SCOTT';

não há linhas selecionadas

SQL> select * from all_users where username='SCOTT';

não há linhas selecionadas


segunda-feira, 4 de agosto de 2014

Oracle Multitenant: Alterando parâmetros de inicialização do Container Database (CDB) e Pluggable Databases (PDBs) no Oracle 12c

Por Eduardo Legatti

Olá,

Nos artigos de Fevereiro/2014, Março/2014 e Abril/2014 e abordei sobre a arquitetura Multitenant do Oracle 12c no que se refere aos conceitos básicos de administração e de backups dos containers (CDBs) e dos bancos de dados plugáveis (PDBs). Neste artigo irei demonstrar de forma prática a alteração dos parâmetros da instância container (CDB) e dos bancos de dados plugáveis (PDBs).

Para começar, é importante frisar que só existe um arquivo de inicialização SPFILE para a instância CDB. Isso quer dizer que não existe um arquivo de inicialização SPFILE separado para os bancos de dados plugáveis (PDBs). Neste caso, todos os bancos de dados PDBs herdam os valores dos parâmetros do banco de dados CDB. Alguns parâmetros dos bancos de dados PDBs podem ser alterados, outros não. o valor da coluna ISPDB_MODIFIABLE da view V$SYSTEM_PARAMETER irá nos informar se o parâmetro em questão poderá ser modificado. Segue a demonstração abaixo.
 
C:\>set ORACLE_SID=CDB01

C:\>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Seg Ago 4 09:43:13 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Conectado a:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show con_name;

CON_NAME
-----------------------
CDB$ROOT

SQL> select a.con_id,a.name,a.dbid,
  2         b.status,a.open_mode,a.total_size
  3    from v$pdbs a, dba_pdbs b
  4   where a.con_id=b.pdb_id;

    CON_ID NAME                    DBID STATUS        OPEN_MODE  TOTAL_SIZE
---------- ----------------- ---------- ------------- ---------- ----------
         2 PDB$SEED          4076210644 NORMAL        READ ONLY   283115520
         3 PDB01             3898013363 NORMAL        READ WRITE  288358400
         4 PDB02             3947824578 NORMAL        READ WRITE  283115520
         5 PDB03             4001514065 NORMAL        READ WRITE  288358400

SQL> show parameter spfile;

NAME      TYPE        VALUE
--------- ----------- --------------------------------------------------------------
spfile    string      C:\ORACLE\APP\PRODUCT\12.1.0\DBHOME_1\DATABASE\SPFILECDB01.ORA

Acima podemos verificar que a instância CDB foi aberta com o parâmetro de inicialização SPFILECDB01.ORA e que 3 bancos de dados PDBs estão abertos. Abaixo irei alterar o parâmetro "open_cursors" banco de dados de container CDB$ROOT.
 
SQL> alter system set open_cursors=210;

Sistema alterado.

O comando ALTER SYSTEM acima modificou o parâmetro "open_cursors" tanto em memória quanto no arquivo SPFILE. No Oracle 12c foi incluído uma nova cláusula no comando ALTER SYSTEM que indica se a alteração realizada no parâmetro deverá afetar apenas o banco de dados de container atual (CURRENT) ou em todos os containers (ALL). Se o mesmo não for especificado, então o padrão será CURRENT.
 
SQL> show parameter open_cursors;

NAME                            TYPE        VALUE
------------------------------- ----------- ---------------------------
open_cursors                    integer     210

SQL> select name,value,display_value,ispdb_modifiable
  2    from v$system_parameter
  3   where name = 'open_cursors';

NAME            VALUE      DISPLAY_VA ISPDB
--------------- ---------- ---------- -----
open_cursors    210        210        TRUE

Acima podemos verificar que o parâmetro "open_cursors" foi alterado no banco de dados de container CDB$ROOT e que o mesmo pode ser modificado também nos bancos de dados plugáveis. Irei realizar abaixo a alteração no banco de dados plugável PDB01.
 
SQL> alter session set container = PDB01;

Sessão alterada.

SQL> show con_name;

CON_NAME
------------------------------
PDB01

SQL> alter system set open_cursors=220;

Sistema alterado.

SQL> select name,value,display_value,ispdb_modifiable
  2    from v$system_parameter
  3   where name = 'open_cursors';

NAME            VALUE      DISPLAY_VA ISPDB
--------------- ---------- ---------- -----
open_cursors    220        220        TRUE

Após a alteração do parâmetro no banco de dados plugável PDB01, irei conectar novamente no container CDB$ROOT e consultar a view V$SYSTEM_PARAMETER.
 
SQL> alter session set container = CDB$ROOT;

Sessão alterada.

SQL> select name,value,display_value,con_id,ispdb_modifiable
  2    from v$system_parameter
  3   where name = 'open_cursors';

NAME           VALUE      DISPLAY_VA     CON_ID ISPDB
-------------- ---------- ---------- ---------- -----
open_cursors   210        210                 0 TRUE
open_cursors   220        220                 3 TRUE

O resultado acima demonstra que é possível consultar os valores dos parâmetros dos banco de dados plugáveis que tiveram seus valores alterados à partir do container CDB$ROOT. Vale a pena salientar que o banco de dados plugável precisa estar aberto para que a consulta retorne os valores dos parâmetros. Bom, após a alteração do parâmetro "open_cursors" tanto no container CDB quanto no banco de dados PDB01, segue abaixo o conteúdo o SPFILE após o mesmo ser convertido para PFILE.
 
C:\>type INITcdb01.ORA

cdb01.__data_transfer_cache_size=0
cdb01.__db_cache_size=729808896
cdb01.__java_pool_size=4194304
cdb01.__large_pool_size=12582912
cdb01.__oracle_base='C:\oracle\app'
cdb01.__pga_aggregate_target=314572800
cdb01.__sga_target=1073741824
cdb01.__shared_io_pool_size=50331648
cdb01.__shared_pool_size=264241152
cdb01.__streams_pool_size=0
*.audit_file_dest='C:\oracle\app\admin\CDB01\adump'
*.audit_trail='db'
*.compatible='12.1.0.0.0'
*.control_files='C:\oracle\CDB01\control01.ctl','C:\oracle\CDB01\control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='CDB01'
*.db_recovery_file_dest_size=53687091200
*.db_recovery_file_dest='C:\oracle\app\fast_recovery_area'
*.diagnostic_dest='C:\oracle\app'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=CDB01XDB)'
*.enable_pluggable_database=true
*.nls_language='BRAZILIAN PORTUGUESE'
*.nls_territory='BRAZIL'
*.open_cursors=210
*.pga_aggregate_target=300m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1024m
*.undo_tablespace='UNDOTBS1'

Verificando o resultado acima é possível perceber que apenas o valor do parâmetro "open_cursors" do container CDB01 se encontra armazenado no SPFILE. Portanto, é possível concluir que os valores dos parâmetros dos bancos de dados PDBs não ficam armazenados no arquivo de inicialização SPFILE e sim nas tabelas de dicionário de dados do banco de dados de container CDB$ROOT como demonstrado pela consulta SQL abaixo. Com ela é possível obter os valores do parâmetros de inicialização dos bancos de dados plugáveis consultando a tabela de dicionário de dados PDB_SPFILE$:
 
SQL> select a.name,value$,con_id 
  2    from pdb_spfile$ a 
  3    join v$pdbs b on (a.pdb_uid=b.con_uid);

NAME                 VALUE$         CON_ID
-------------------- ---------- ----------
open_cursors         220                 3
 

terça-feira, 1 de julho de 2014

Configurando um Script Master para execução de scripts no SQL*Plus

Por Eduardo Legatti

Olá,

Quando precisamos executar vários scripts SQL em um banco de dados, geralmente automatizamos a sua execução para não ter que ficarmos executando script por script. Dependendo do ambiente, como no caso de um banco de produção, a boa prática é colher todas as informações da execução realizada afim de se ter uma evidência ou prova do que foi executado. Portanto, um arquivo de log contendo as informações da execuções dos scripts é fundamental. Dentre as informações que eu considero importante que se tenha no arquivo de log, eu posso citar a instrução SQL que foi executada, o nome do script, a hora da execução, o tempo de execução, o nome do servidor e o nome do banco de dados. No artigo de Janeiro/2011 eu mostrei como habilitar o suporte a acentuação no DOS para execução de scripts SQL. Neste artigo irei compartilhar um modelo de script que utilizo com uma certa frequência que tem como objetivo chamar outros scripts e gerar uma evidência da execução realizada. Apesar do script que utilizo fazer mais validações, irei demonstrar apenas o que considero básico para um Script Master. Segue abaixo o conteúdo do mesmo na qual irei realizar a execução de 9 scripts.
 
C:\SQL>type 00-ScriptMaster.sql

WHENEVER OSERROR EXIT 1;
WHENEVER SQLERROR EXIT 1;
SET ECHO ON
SET DEFINE OFF
SET FEEDBACK ON
SET TIME ON
SET TIMING ON
SET APPINFO ON
SET SERVEROUTPUT ON SIZE 1000000
SPOOL 00-ScriptMaster.log
select host_name,instance_name from v$instance;
alter session set nls_territory='BRAZIL';
alter session set current_schema=SCOTT;
@01-script01.sql
@02-script02.sql
@03-script03.sql
@04-script04.sql
@05-script05.sql
@06-script06.sql
@07-script07.sql
@08-script08.sql
@09-script09.sql
spool off
quit;
 
Como demonstrado acima, o script 00-ScriptMaster.sql é o nome do Script Master. Em resumo, ele contém algumas validações como o uso das cláusulas WHENEVER nas duas primeiras linhas. Elas indicam que que se algum script não for encontrado ou se ocorrer algum erro na execução de algum script, a execução do Script Master será abortada. Na minha opinião esse comportamento é fundamental de forma a evitar que os scripts subsequentes sejam afetados por um erro ocasionado na execução de um script anterior. Dessa forma, um script que der algum problema poderá ser tratado pontualmente.

Outro ponto que acho importante se refere ao SET DEFINE OFF que impede que o caracter "&" seja tratado como "host variable" no SQL*Plus. Em relação ao horário de execução e o tempo de execução de cada instrução, SET TIME ON e SET TIMING ON são fundamentais. Na linha 8, o SET APPINFO ON é útil para fins de monitoramento porque o script em execução será visível na coluna MODULE da view dinâmica de desempenho V$SESSION. Portanto, mesmo que o Script Master esteja em execução no servidor de banco de dados, será possível acompanhar de outra máquina qual script está sendo executado no momento consultando a view V$SESSION. O SET SERVEROUTPUT ON é útil para mostrar resultados de códigos PL/SQL que utilizam o comando DBMS_OUTPUT.

Na linha 10, o comando SPOOL irá gerar o arquivo de log na qual darei o nome de 00-ScriptMaster.log. Para evitar qualquer problema com formatos de data, pontos decimais e de milhar, eu costumo forçar o NLS_TERRITORY como demonstrado na linha 12.

Para evitar qualquer problema em relação à falta de priviégios durante a execução dos scripts, eu prefiro executar o Script Master com um usuário DBA, como por exemplo o SYSTEM. Como a conexão é feita pelo SYSTEM, eu forço a sessão para o schema que será alvo da execução dos scripts (SCOTT) como demonstrado na linha 13. Caso o Script Master envolva a execução de scripts em mais de um schema, então vários "alter session set current_schema" poderão ser inseridos ao longo do Script Master. Por fim, as linhas que começam com @ são exatamente os scripts que serão executados conforme demonstrado abaixo:

C:\SQL>dir
 O volume na unidade C não tem nome.
 O Número de Série do Volume é 1C51-B278

 Pasta de C:\SQL

01/07/2014  15:27    [DIR]          .
01/07/2014  15:27    [DIR]          ..
01/07/2014  15:43               517 00-ScriptMaster.sql
01/07/2014  15:37               118 01-script01.sql
01/07/2014  15:13                20 02-script02.sql
01/07/2014  15:14                38 03-script03.sql
01/07/2014  15:48               103 04-script04.sql
01/07/2014  15:53               202 05-script05.sql
01/07/2014  15:15                36 06-script06.sql
01/07/2014  15:17                97 07-script07.sql
01/07/2014  15:17                37 08-script08.sql
01/07/2014  15:35               256 09-script09.sql
              10 arquivo(s)         1.424 bytes
               2 pasta(s)   102.755.786.752 bytes disponíveis

No mais, segue uma execução do Script Master na qual eu simulo um erro de execução do script 04-script04.sql. Neste caso, a execução do Script Master será abortada e a saída gravada no arquivo de log 00-ScriptMaster.log.

C:\SQL>sqlplus system/manager @00-ScriptMaster.sql

SQL*Plus: Release 11.2.0.1.0 Production on Ter Jul 1 15:47:12 2014

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Conectado a:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

15:47:21 SQL> select host_name,instance_name from v$instance;

HOST_NAME                         INSTANCE_NAME
--------------------------------- -------------
server01                          BD01

1 linha selecionada.

Decorrido: 00:00:00.01
15:47:21 SQL> alter session set nls_territory='BRAZIL';

Sessão alterada.

Decorrido: 00:00:00.00
15:47:21 SQL> alter session set current_schema=SCOTT;

Sessão alterada.

Decorrido: 00:00:00.00
15:47:21 SQL> @01-script01.sql
15:47:21 SQL> create table t1 (id number);

Tabela criada.

Decorrido: 00:00:00.12
15:47:21 SQL> create table t2 (id number);

Tabela criada.

Decorrido: 00:00:00.03
15:47:21 SQL> create table t3 (id number);

Tabela criada.

Decorrido: 00:00:00.00
15:47:21 SQL> create table t4 (id number);

Tabela criada.

Decorrido: 00:00:00.00
15:47:21 SQL> @02-script02.sql
15:47:21 SQL> drop table t3 purge;

Tabela eliminada.

Decorrido: 00:00:00.02
15:47:22 SQL> @03-script03.sql
15:47:22 SQL> alter table t1 add nome varchar2(100);

Tabela alterada.

Decorrido: 00:00:00.08
15:47:22 SQL> @04-script04.sql
15:47:22 SQL> insert into t1 values (1);
insert into t1 values (1)
            *
ERRO na linha 1:
ORA-00947: não há valores suficientes
 
Enfim, segue abaixo o conteúdo do arquivo de log após uma execução completa do Script Master.

C:\SQL>sqlplus system/manager @00-ScriptMaster.sql
15:54:35 SQL> select host_name,instance_name from v$instance;

HOST_NAME                         INSTANCE_NAME
--------------------------------- ----------------
server01                          BD01

1 linha selecionada.

Decorrido: 00:00:00.00
15:54:35 SQL> alter session set nls_territory='BRAZIL';

Sessão alterada.

Decorrido: 00:00:00.00
15:54:35 SQL> alter session set current_schema=SCOTT;

Sessão alterada.

Decorrido: 00:00:00.00
15:54:35 SQL> @01-script01.sql
15:54:35 SQL> create table t1 (id number);

Tabela criada.

Decorrido: 00:00:00.06
15:54:35 SQL> create table t2 (id number);

Tabela criada.

Decorrido: 00:00:00.01
15:54:35 SQL> create table t3 (id number);

Tabela criada.

Decorrido: 00:00:00.00
15:54:35 SQL> create table t4 (id number);

Tabela criada.

Decorrido: 00:00:00.00
15:54:35 SQL> @02-script02.sql
15:54:35 SQL> drop table t3 purge;

Tabela eliminada.

Decorrido: 00:00:00.03
15:54:35 SQL> @03-script03.sql
15:54:35 SQL> alter table t1 add nome varchar2(100);

Tabela alterada.

Decorrido: 00:00:00.01
15:54:35 SQL> @04-script04.sql
15:54:35 SQL> insert into t1 values (1,'x');

1 linha criada.

Decorrido: 00:00:00.04
15:54:35 SQL> insert into t1 values (2,'y');

1 linha criada.

Decorrido: 00:00:00.01
15:54:35 SQL> insert into t1 values (3,'z');

1 linha criada.

Decorrido: 00:00:00.01
15:54:35 SQL> commit;

Commit concluído.

Decorrido: 00:00:00.01
15:54:36 SQL> @05-script05.sql
15:54:36 SQL> insert into t2 values (10);

1 linha criada.

Decorrido: 00:00:00.01
15:54:36 SQL> commit;

Commit concluído.

Decorrido: 00:00:00.01
15:54:36 SQL> 
15:54:36 SQL> declare
15:54:36   2    tdata varchar2(8);
15:54:36   3  begin
15:54:36   4   select to_char(sysdate,'hh24:mi:ss') into tdata  from dual;
15:54:36   5   dbms_output.put_line(tdata);
15:54:36   6   dbms_lock.sleep(300);
15:54:36   7  end;
15:54:36   8  /
15:54:36

Procedimento PL/SQL concluído com sucesso.

Decorrido: 00:05:00.07
15:59:36 SQL> @06-script06.sql
15:59:36 SQL> create view v_1 as select * from t1;

View criada.

Decorrido: 00:00:00.07
15:59:36 SQL> @07-script07.sql
15:59:36 SQL> insert into t2 values (200);

1 linha criada.

Decorrido: 00:00:00.03
15:59:36 SQL> insert into t2 values (300);

1 linha criada.

Decorrido: 00:00:00.02
15:59:36 SQL> insert into t2 values (400);

1 linha criada.

Decorrido: 00:00:00.03
15:59:36 SQL> commit;

Commit concluído.

Decorrido: 00:00:00.03
15:59:36 SQL> @08-script08.sql
15:59:36 SQL> delete from t2 where id=200;

1 linha deletada.

Decorrido: 00:00:00.03
15:59:36 SQL> commit;

Commit concluído.

Decorrido: 00:00:00.05
15:59:36 SQL> @09-script09.sql
15:59:36 SQL> update t1 set id=id*10;

3 linhas atualizadas.

Decorrido: 00:00:00.06
15:59:36 SQL> commit;

Commit concluído.

Decorrido: 00:00:00.04
15:59:36 SQL> 
15:59:36 SQL> begin
15:59:36   2   for i in 1..10 loop
15:59:36   3     insert into t4 select level id from dual connect by level <=10;
15:59:36   4     if mod(i, 2) = 0 then
15:59:36   5        commit;
15:59:36   6        dbms_output.put_line('commit');
15:59:36   7     end if;
15:59:36   8   end loop;
15:59:36   9   commit;
15:59:36  10  end;
15:59:36  11  /
commit
commit
commit
commit
commit

Procedimento PL/SQL concluído com sucesso.

Decorrido: 00:00:00.12
15:59:37 SQL> spool off


Durante a execução do Script Master, foi possível identificar o script 05-script05.sql sendo executado consultado a view V$SESSION como demonstrado abaixo:

C:\>sqlplus system/manager

SQL*Plus: Release 11.2.0.1.0 Production on Ter Jul 1 15:55:04 2014

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Conectado a:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SELECT sid,serial#,username,schemaname,
  2  osuser,program,module
  3  FROM V$SESSION
  4  WHERE schemaname='SCOTT';

    SID  SERIAL# USERNAME     SCHEMANAME      OSUSER    PROGRAM      MODULE
------- -------- ------------ --------------- --------- ------------ -------------------
     68    53805 SYSTEM       SCOTT           legatti   sqlplus.exe  02@ 05-script05.sql

1 linha selecionada.

domingo, 1 de junho de 2014

Abordando o Table-Point-In-Time Recovery (TPITR) na prática com o RMAN do Oracle 12c

Por Eduardo Legatti

Olá,  

No artigo de Março/2010 eu abordei um pouco da técnica de recuperação pontual de dados já bem conhecida entre os DBAs Oracle chamada de Tablespace Point-in-Time Recovery (TSPITR) disponível desde a versão do Oracle 8 e também uma outra técnica chamada Database Point-in-Time Recovery (DBPITR). Como já explicado anteriormente naquele artigo, basicamente o TSPITR (Recuperação Pontual de Tablespace) é uma funcionalidade que fornece ao DBA a opção de recuperar um conjunto de dados em um ponto específico no tempo sem afetar o restante do banco de dados. Já o DBPITR possibilita um banco de dados inteiro "voltar no tempo" da mesma forma que o recurso Flashback Database (10g) possibilita. Neste artigo irei abordar um novo recurso que veio no RMAN do Oracle 12c chamado de Table-Point-In-Time Recovery. Daqui pra frente irei fazer referência a ele apenas como TPITR. Da mesma forma que no TSPITR, iremos notar que o TPITR também se utilizará não só de uma instância auxiliar, como também do utilitário Datapump (expdp/impdp) no processo de restore/recover. Vamos então a prática.
 
[oracle@linux1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Sun Jun 1 15:36:29 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Conectado a:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select * from scott.t1;

        ID
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 linhas selecionadas.

SQL> select sysdate from dual;

SYSDATE
-------------------
01/06/2014 15:36:55

Podemos ver pelo resultado acima, que a tabela T1 de propriedade do usuário SCOTT contém 10 linhas e que a mesma existia às 15:36:55 do dia 01/06/2014. Após realização de um backup do banco de dados com o RMAN, irei dropar a tabela T1 conforme demonstrado abaixo.

SQL> drop table scott.t1 purge;

Tabela eliminada.

SQL> select * from scott.t1;
select * from scott.t1
                    *
ERRO na linha 1:
ORA-00942: a tabela ou view não existe

Pelo fato de eu ter utilizado a opção purge do comando drop table, a mesma não foi para a lixeira (recycle bin). Portanto, não será possível recuperá-la através do flashback drop. Bom, o objetivo então será recuperar a tabela diretamente do backup do banco de dados que fiz previamente através do RMAN. Para me certificar, segue abaixo a prova de que o backup foi realizado.
 
RMAN> list backup;
List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7       Full    404.21M    DISK        00:01:59     01/06/2014
        BP Key: 7   Status: AVAILABLE  Compressed: NO  Tag: TAG20140508T153228
        Piece Name: /u01/app/oracle/fast_recovery_area/BD01/backupset/2014_06_01/o1_mf_nnndf_TAG20140508T153228_9pqmfxl6_.bkp
  List of Datafiles in backup set 7
  File LV Type Ckp SCN    Ckp Time   Name
  ---- -- ---- ---------- ---------- ----
  1       Full 365502     01/06/2014 /u01/app/oracle/oradata/BD01/system01.dbf
  2       Full 365502     01/06/2014 /u01/app/oracle/oradata/BD01/sysaux01.dbf
  3       Full 365502     01/06/2014 /u01/app/oracle/oradata/BD01/undotbs01.dbf
  4       Full 365502     01/06/2014 /u01/app/oracle/oradata/BD01/users01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
8       Full    9.61M      DISK        00:00:03     01/06/2014
        BP Key: 8   Status: AVAILABLE  Compressed: NO  Tag: TAG20140508T153228
        Piece Name: /u01/app/oracle/fast_recovery_area/BD01/backupset/2014_06_01/o1_mf_ncnnf_TAG20140508T153228_9pqmkyfx_.bkp
  Control File Included: Ckp SCN: 365548       Ckp time: 01/06/2014

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
9       Full    9.64M      DISK        00:00:02     01/06/2014
        BP Key: 9   Status: AVAILABLE  Compressed: NO  Tag: TAG20140508T153440
        Piece Name: /u01/app/oracle/fast_recovery_area/BD01/autobackup/2014_06_01/o1_mf_s_847035280_9pqml24o_.bkp
  SPFILE Included: Modification time: 01/06/2014
  SPFILE db_unique_name: BD01
  Control File Included: Ckp SCN: 365556       Ckp time: 01/06/2014

Assim como nos métodos DBPITR e TSPITR, para que o processo de recuperação utilizando a técnica TPITR seja possível, será necessário criar um diretório no sistema de arquivos para uso da instância auxiliar que será criada no processo de recover.

[oracle@linux1 ~]$ mkdir /u01/aux_dest

Como eu sei que a tabela existia exatamente às 15:36:35, irei utilizar esse mesmo horário para recuperar a tabela T1. Segue abaixo o comandos necessário para realizar essa operação.

[oracle@linux1 ~]$ rman target /

Recovery Manager: Release 12.1.0.1.0 - Production on Sun Jun 1 15:42:17 2014

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

connected to target database: BD01 (DBID=3099510927)

RMAN> recover table SCOTT.T1
2> until time = "to_date('01/06/2014 15:36:55','DD/MM/YYYY HH24:MI:SS')"
3> auxiliary destination '/u01/aux_dest';
Starting recover at 01/06/2014
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=272 device type=DISK
RMAN-05026: WARNING: presuming following set of tablespaces applies to specified Point-in-Time

List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace UNDOTBS1

Creating automatic instance, with SID='hgkF'

initialization parameters used for automatic instance:
db_name=BD01
db_unique_name=hgkF_pitr_BD01
compatible=12.1.0.0.0
db_block_size=8192
db_files=200
sga_target=1G
processes=80
diagnostic_dest=/u01/app/oracle
db_create_file_dest=/u01/aux_dest
log_archive_dest_1='location=/u01/aux_dest'
#No auxiliary parameter file used


starting up automatic instance BD01

Oracle instance started

Total System Global Area    1068937216 bytes

Fixed Size                     2296576 bytes
Variable Size                281019648 bytes
Database Buffers             780140544 bytes
Redo Buffers                   5480448 bytes
Automatic instance created

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('01/06/2014 15:36:55','DD/MM/YYYY HH24:MI:SS')";
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log
sql 'alter system archive log current';
}
executing Memory Script

executing command: SET until clause

Starting restore at 01/06/2014
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=83 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/BD01/autobackup/2014_06_01/o1_mf_s_847035280_9pqml24o_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/BD01/autobackup/2014_06_01/o1_mf_s_847035280_9pqml24o_.bkp tag=TAG20140508T153440
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
output file name=/u01/aux_dest/BD01/controlfile/o1_mf_9pqn27bq_.ctl
Finished restore at 01/06/2014

sql statement: alter database mount clone database

sql statement: alter system archive log current

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('01/06/2014 15:36:55','DD/MM/YYYY HH24:MI:SS')";
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile  1 to new;
set newname for clone datafile  3 to new;
set newname for clone datafile  2 to new;
set newname for clone tempfile  1 to new;
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  1, 3, 2;
switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /u01/aux_dest/BD01/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 01/06/2014
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/aux_dest/BD01/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/aux_dest/BD01/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /u01/aux_dest/BD01/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/BD01/backupset/2014_06_01/o1_mf_nnndf_TAG20140508T153228_9pqmfxl6_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/BD01/backupset/2014_06_01/o1_mf_nnndf_TAG20140508T153228_9pqmfxl6_.bkp tag=TAG20140508T153228
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:02:39
Finished restore at 01/06/2014

datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=847035974 file name=/u01/aux_dest/BD01/datafile/o1_mf_system_9pqn2mfs_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=847035975 file name=/u01/aux_dest/BD01/datafile/o1_mf_undotbs1_9pqn2mmr_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=847035976 file name=/u01/aux_dest/BD01/datafile/o1_mf_sysaux_9pqn2mld_.dbf

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('01/06/2014 15:36:55','DD/MM/YYYY HH24:MI:SS')";
# online the datafiles restored or switched
sql clone "alter database datafile  1 online";
sql clone "alter database datafile  3 online";
sql clone "alter database datafile  2 online";
# recover and open database read only
recover clone database tablespace  "SYSTEM", "UNDOTBS1", "SYSAUX";
sql clone 'alter database open read only';
}
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile  1 online

sql statement: alter database datafile  3 online

sql statement: alter database datafile  2 online

Starting recover at 01/06/2014
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 26 is already on disk as file /u01/app/oracle/fast_recovery_area/BD01/archivelog/2014_06_01/o1_mf_1_26_9pqmqrk0_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/BD01/archivelog/2014_06_01/o1_mf_1_26_9pqmqrk0_.arc thread=1 sequence=26
media recovery complete, elapsed time: 00:00:02
Finished recover at 01/06/2014

sql statement: alter database open read only

contents of Memory Script:
{
   sql clone "create spfile from memory";
   shutdown clone immediate;
   startup clone nomount;
   sql clone "alter system set  control_files =
  ''/u01/aux_dest/BD01/controlfile/o1_mf_9pqn27bq_.ctl'' comment=
 ''RMAN set'' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
# mount database
sql clone 'alter database mount clone database';
}
executing Memory Script

sql statement: create spfile from memory

database closed
database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    1068937216 bytes

Fixed Size                     2296576 bytes
Variable Size                285213952 bytes
Database Buffers             775946240 bytes
Redo Buffers                   5480448 bytes

sql statement: alter system set  control_files =   ''/u01/aux_dest/BD01/controlfile/o1_mf_9pqn27bq_.ctl'' comment= ''RMAN set'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    1068937216 bytes

Fixed Size                     2296576 bytes
Variable Size                285213952 bytes
Database Buffers             775946240 bytes
Redo Buffers                   5480448 bytes

sql statement: alter database mount clone database

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('01/06/2014 15:36:55','DD/MM/YYYY HH24:MI:SS')";
# set destinations for recovery set and auxiliary set datafiles
set newname for datafile  4 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  4;
switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

Starting restore at 01/06/2014
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=11 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/aux_dest/HGKF_PITR_BD01/datafile/o1_mf_users_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/BD01/backupset/2014_06_01/o1_mf_nnndf_TAG20140508T153228_9pqmfxl6_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/BD01/backupset/2014_06_01/o1_mf_nnndf_TAG20140508T153228_9pqmfxl6_.bkp tag=TAG20140508T153228
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 01/06/2014

datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=847036181 file name=/u01/aux_dest/HGKF_PITR_BD01/datafile/o1_mf_users_9pqng2l9_.dbf

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('01/06/2014 15:36:55','DD/MM/YYYY HH24:MI:SS')";
# online the datafiles restored or switched
sql clone "alter database datafile  4 online";
# recover and open resetlogs
recover clone database tablespace  "USERS", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile  4 online

Starting recover at 01/06/2014
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 26 is already on disk as file /u01/app/oracle/fast_recovery_area/BD01/archivelog/2014_06_01/o1_mf_1_26_9pqmqrk0_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/BD01/archivelog/2014_06_01/o1_mf_1_26_9pqmqrk0_.arc thread=1 sequence=26
media recovery complete, elapsed time: 00:00:01
Finished recover at 01/06/2014

database opened

contents of Memory Script:
{
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/u01/aux_dest''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/u01/aux_dest''";
}
executing Memory Script

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/aux_dest''

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/aux_dest''

Performing export of tables...
   EXPDP> Starting "SYS"."TSPITR_EXP_hgkF_glDz":
   EXPDP> Estimate in progress using BLOCKS method...
   EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
   EXPDP> Total estimation using BLOCKS method: 64 KB
   EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
   EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
   EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
   EXPDP> . . exported "SCOTT"."T1"                                5.093 KB      10 rows
   EXPDP> Master table "SYS"."TSPITR_EXP_hgkF_glDz" successfully loaded/unloaded
   EXPDP> ******************************************************************************
   EXPDP> Dump file set for SYS.TSPITR_EXP_hgkF_glDz is:
   EXPDP>   /u01/aux_dest/tspitr_hgkF_20324.dmp
   EXPDP> Job "SYS"."TSPITR_EXP_hgkF_glDz" successfully completed at Sun Jun 1 15:52:23 2014 elapsed 0 00:00:45
Export completed


contents of Memory Script:
{
# shutdown clone before import
shutdown clone abort
}
executing Memory Script

Oracle instance shut down

Performing import of tables...
   IMPDP> Tabela-mestre "SYS"."TSPITR_IMP_hgkF_znug" carregada/descarregada com sucesso
   IMPDP> Iniciando "SYS"."TSPITR_IMP_hgkF_znug":
   IMPDP> Processando o tipo de objeto TABLE_EXPORT/TABLE/TABLE
   IMPDP> Processando o tipo de objeto TABLE_EXPORT/TABLE/TABLE_DATA
   IMPDP> . . importou "SCOTT"."T1"                                5.093 KB      10 linhas
   IMPDP> Processando o tipo de objeto TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
   IMPDP> Processando o tipo de objeto TABLE_EXPORT/TABLE/STATISTICS/MARKER
   IMPDP> O job "SYS"."TSPITR_IMP_hgkF_znug" foi concluido com sucesso em Dom Jun 1 15:55:09 2014 elapsed 0 00:01:49
Import completed


Removing automatic instance
Automatic instance removed
auxiliary instance file /u01/aux_dest/BD01/datafile/o1_mf_temp_9pqn8x8x_.tmp deleted
auxiliary instance file /u01/aux_dest/HGKF_PITR_BD01/onlinelog/o1_mf_3_9pqngcjb_.log deleted
auxiliary instance file /u01/aux_dest/HGKF_PITR_BD01/onlinelog/o1_mf_2_9pqngbsx_.log deleted
auxiliary instance file /u01/aux_dest/HGKF_PITR_BD01/onlinelog/o1_mf_1_9pqng9r7_.log deleted
auxiliary instance file /u01/aux_dest/HGKF_PITR_BD01/datafile/o1_mf_users_9pqng2l9_.dbf deleted
auxiliary instance file /u01/aux_dest/BD01/datafile/o1_mf_sysaux_9pqn2mld_.dbf deleted
auxiliary instance file /u01/aux_dest/BD01/datafile/o1_mf_undotbs1_9pqn2mmr_.dbf deleted
auxiliary instance file /u01/aux_dest/BD01/datafile/o1_mf_system_9pqn2mfs_.dbf deleted
auxiliary instance file /u01/aux_dest/BD01/controlfile/o1_mf_9pqn27bq_.ctl deleted
auxiliary instance file tspitr_hgkF_20324.dmp deleted
Finished recover at 01/06/2014

Pronto. Após a execução do processo de restore/recover, poderemos ver abaixo que a tabela foi recuperada com sucesso.
 
[oracle@linux1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Sun Jun 1 16:24:10 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Conectado a:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select * from scott.t1;

        ID
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 linhas selecionadas.
  
Bom, da mesma forma que podemos restaurar uma tabela com o nome original, podemos também restaurá-la com um nome diferente. Para isso basta apenas utilizarmos a opção REMAP TABLE. Segue abaixo alguns trechos do processo de recuperação na qual eu irei restaurar a tabela com T1 com o nome de T1_DROP.
 
RMAN> recover table SCOTT.T1
2> until time = "to_date('01/06/2014 15:36:55','DD/MM/YYYY HH24:MI:SS')"
3> auxiliary destination '/u01/aux_dest'
4> remap table SCOTT.T1:T1_DROP;

Performing export of tables...
   EXPDP> Starting "SYS"."TSPITR_EXP_isFB_xdwa":
   EXPDP> Estimate in progress using BLOCKS method...
   EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
   EXPDP> Total estimation using BLOCKS method: 64 KB
   EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
   EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
   EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
   EXPDP> . . exported "SCOTT"."T1"                               5.093 KB      10 rows
   EXPDP> Master table "SYS"."TSPITR_EXP_isFB_xdwa" successfully loaded/unloaded
   EXPDP> *****************************************************************************
   EXPDP> Dump file set for SYS.TSPITR_EXP_isFB_xdwa is:
   EXPDP>   /u01/aux_dest/tspitr_isFB_93535.dmp
   EXPDP> Job "SYS"."TSPITR_EXP_isFB_xdwa" successfully completed
Export completed

contents of Memory Script:
{
# shutdown clone before import
shutdown clone abort
}
executing Memory Script

Oracle instance shut down

Performing import of tables...
   IMPDP> Tabela-mestre "SYS"."TSPITR_IMP_isFB_EpCe" carregada/descarregada com sucesso
   IMPDP> Iniciando "SYS"."TSPITR_IMP_isFB_EpCe":
   IMPDP> Processando o tipo de objeto TABLE_EXPORT/TABLE/TABLE
   IMPDP> Processando o tipo de objeto TABLE_EXPORT/TABLE/TABLE_DATA
   IMPDP> . . importou "SCOTT"."T1_DROP"                        5.093 KB      10 linhas
   IMPDP> Processando o tipo de objeto TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
   IMPDP> Processando o tipo de objeto TABLE_EXPORT/TABLE/STATISTICS/MARKER
   IMPDP> O job "SYS"."TSPITR_IMP_isFB_EpCe" foi concluido com sucesso
Import completed
  
Pronto. Após a finalização do processo de recover podemos ver abaixo a tabela T1_DROP.
 
RMAN> select owner,table_name from dba_tables where owner = 'SCOTT';

using target database control file instead of recovery catalog

OWNER      TABLE_NAME
---------- ---------------
SCOTT      T1
SCOTT      T1_DROP

Para finalizar, existe uma outra opção que podemos utilizar no processo TPITR. Essa opção consiste em recuperar a tabela, mas não importá-la de volta para o banco de dados. Neste caso, apenas um dump contendo a tabela será gerado. Caso queiramos importá-la posteriormente poderemos fazer isso manualmente. Segue abaixo as opções que devemos utilizar para realizar essa operação.
 
RMAN> recover table SCOTT.T1
2> until time = "to_date('01/06/2014 15:36:55','DD/MM/YYYY HH24:MI:SS')"
3> auxiliary destination '/u01/aux_dest'
4> datapump destination '/tmp'
5> dump file 't1.dmp'
6> notableimport;

Performing export of tables...
   EXPDP> Starting "SYS"."TSPITR_EXP_pBbn_otbe":
   EXPDP> Estimate in progress using BLOCKS method...
   EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
   EXPDP> Total estimation using BLOCKS method: 64 KB
   EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
   EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
   EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
   EXPDP> . . exported "SCOTT"."T1"                               5.093 KB      10 rows
   EXPDP> Master table "SYS"."TSPITR_EXP_pBbn_otbe" successfully loaded/unloaded
   EXPDP> *****************************************************************************
   EXPDP> Dump file set for SYS.TSPITR_EXP_pBbn_otbe is:
   EXPDP>   /tmp/t1.dmp
   EXPDP> Job "SYS"."TSPITR_EXP_pBbn_otbe" successfully completed
Export completed

Not performing table import after point-in-time recovery

Ao final, poderemos ver que o dump foi gerado no diretório especificado.
 
[oracle@linux1 tmp]$ ls -l /tmp/*.dmp
-rw-r----- 1 oracle oinstall 131072 Mai  8 17:03 /tmp/t1.dmp

Postagens populares