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


segunda-feira, 28 de julho de 2008

Um pouco do Flashback Versions Query e do Flashback Transaction Query disponíveis a partir do Oracle 10g ...

Por Eduardo Legatti

Olá,

A funcionalidade do recurso Flashback Query disponível desde a versão Oracle 9i e abordada também nos artigos de Maio/2007 e Maio/2008, foi aperfeiçoada no Oracle 10g para incluir dois novos tipos de consultas: Flashback Versions Query e Flashback Transaction Query. O Flashback Versions Query fornece uma maneira simples e fácil de mostrar todas as versões de todas as linhas em uma tabela entre dois SCN's ou tempos de intervalo (time stamps), informando se as linhas foram inseridas, deletadas ou atualizadas. Na verdade, acredito que o Flashback Versions Query é uma extensão à linguagem SQL que permite ao DBA recuperar diferentes versões de linhas de tabela em qualquer intervalo de tempo. Uma nova versão de registro será criada toda vez que o comando COMMIT for emitido, e mesmo que uma linha seja deletada e re-inserida várias vezes, todas estas alterações estarão disponíveis para acesso. É importante lembrar que o parâmetro UNDO_RETENTION controla e especifica quanto tempo os blocos de dados Oracle alterados ficarão disponíveis no segmento de UNDO até serem sobre-gravados, portanto é bom certificar que não só este parâmetro esteja corretamente setado, mas também que o tablespace de UNDO seja grande suficiente para que as alterações realizadas no banco de dados estejam disponíveis para uso do Flashback Query quando necessário. No mais, para invocar esta funcionalidade é necessário utilizar a cláusula VERSIONS BETWEEN na sentença SELECT.

A sintaxe para uso do Flashback Versions Query é a seguinte:


 SELECT [pseudo_columns]...FROM table_name
   VERSIONS BETWEEN
  {SCN | TIMESTAMP {expr | MINVALUE} AND
  {expr | MAXVALUE}}
  [AS OF {SCN|TIMESTAMP expr}]
 WHERE [pseudo_column | column] . . .

Já o Flashback Transaction Query usa a view de dicionário de dados FLASHBACK_TRANSACTION_QUERY para recuperar informações de transações de banco de dados para todas as tabelas envolvidas em uma transação. Esta view possui uma coluna UNDO_SQL que fornece a sentença SQL que poderá ser utilizada para desfazer uma mudança feita anteriormente. A propósito, nas versões anteriores ao Oracle 10g, o LogMiner poderia ser utilizado para fornecer estas informações. É importante salientar que para ter acesso a esta view, o usuário de banco de dados deverá ter o privilégio de sistema SELECT ANY TRANSACTION. Em resumo, caso seja identificado que um dado em uma linha de tabela foi modificado erroneamente, poderemos então usar o Flashback Transaction Query para identificar o histórico das sentenças de undo SQL e utilizá-las para reverter estas modificações.

A view FLASHBACK_TRANSACTION_QUERY contém as seguintes colunas:

Nome             Descrição
---------------- ---------------------------------------------------------------------------
XID              Identificador da transação.
START_SCN        Número do SCN que inicia a transação.
START_TIMESTAMP  Horário de início da transação.
COMMIT_SCN       SCN gerado durante o COMMIT da transação. Será nulo para transações ativas.
COMMIT_TIMESTAMP Horário do COMMIT. Será nulo para transações ativas.
LOGON_USER       Usuário de banco de dados logado que realizou a transação.
UNDO_CHANGE#     Identificador de UNDO.
OPERATION        Operações realizadas pela transação (insert, delete, update).
TABLE_NAME       Nome da tabela onde as operações DML foram aplicadas.
TABLE_OWNER      Nome do usuário de banco de dados proprietário da tabela.
ROW_ID           O ROWID que foi modificado pela operação DML.
UNDO_SQL         SQL que poderá ser usado para desfazer a operação DML realizada.

Vale a pena salientar que a coluna LOGON_USER mostrada acima, poder ser útil para filtrar as transações realizadas por um usuário específico que possui privilégios de acesso às tabelas de propriedade de outro usuário. Vamos então a um pequeno exemplo prático onde usarei a procedure DBMS_LOCK.SLEEP apenas para gerar um intervalo de tempo (60 segundos) entre as transações:

-- Criando um usuário para teste
SYS> create user scott identified by tiger
 2  default tablespace users
 3  quota unlimited on users;

Usuário criado.

SYS> grant connect,resource to scott;

Concessão bem-sucedida.

SYS> grant select any transaction to scott;

Concessão bem-sucedida.

SYS> grant execute on dbms_lock to scott;

Concessão bem-sucedida.

SYS> connect scott/tiger
Conectado.

-- Criando de uma tabela para teste
SCOTT> create table t1 (id number);

Tabela criada.

-- Simulando operações DML's na tabela T1
SCOTT> set time on
09:12:34 SCOTT> insert into t1 values (1);

1 linha criada.

09:12:34 SCOTT> exec dbms_lock.sleep(60);

Procedimento PL/SQL concluído com sucesso.

09:13:34 SCOTT> commit;

Validação completa.

09:13:34 SCOTT> insert into t1 values (2);

1 linha criada.

09:13:34 SCOTT> exec dbms_lock.sleep(60);

Procedimento PL/SQL concluído com sucesso.

09:14:34 SCOTT> commit;

Validação completa.

09:14:34 SCOTT> insert into t1 values (3);

1 linha criada.

09:14:34 SCOTT> exec dbms_lock.sleep(60);

Procedimento PL/SQL concluído com sucesso.

09:15:34 SCOTT> commit;

Validação completa.

09:15:34 SCOTT> update t1 set id = id*10;

3 linhas atualizadas.

09:15:34 SCOTT> exec dbms_lock.sleep(60);

Procedimento PL/SQL concluído com sucesso.

09:16:34 SCOTT> commit;

Validação completa.

09:16:34 SCOTT> delete from t1;

3 linhas deletadas.

09:16:34 SCOTT> exec dbms_lock.sleep(60);

Procedimento PL/SQL concluído com sucesso.

09:17:34 SCOTT> commit;

Validação completa.

09:17:36 SCOTT> set time off

-- Obtendo informações das versões das linhas
SCOTT> select versions_starttime stime,
   2         versions_endtime endtime,
   3         versions_xid xid,
   4         case
   5           when versions_operation = 'I' then 'INSERT'
   6           when versions_operation = 'U' then 'UPDATE'
   7           when versions_operation = 'D' then 'DELETE'
   8         end as operation,
   9         id
  10  from t1 versions between timestamp minvalue and maxvalue
  11  order by stime;

STIME             ENDTIME            XID              OPERAT         ID
----------------- ------------------ ---------------- ------ ----------
28/07/08 09:13:32 28/07/08 09:16:37  06001F00E9000000 INSERT          1
28/07/08 09:14:32 28/07/08 09:16:37  01000500F3000000 INSERT          2
28/07/08 09:15:32 28/07/08 09:16:37  02001000EF000000 INSERT          3
28/07/08 09:16:37 28/07/08 09:17:32  03001100F1000000 UPDATE         10
28/07/08 09:16:37 28/07/08 09:17:32  03001100F1000000 UPDATE         20
28/07/08 09:16:37 28/07/08 09:17:32  03001100F1000000 UPDATE         30
28/07/08 09:17:32                    04001D00F4000000 DELETE         10
28/07/08 09:17:32                    04001D00F4000000 DELETE         20
28/07/08 09:17:32                    04001D00F4000000 DELETE         30

9 linhas selecionadas.

De acordo com o resultado acima, podemos perceber, por exemplo, que o valor 1 contido na coluna ID da tabela T1, foi inserido pela transação 06001F00E9000000 às 09:13:32 e permaneceu com este valor até às 09:16:37 quando teve seu valor alterado para 10 até ser deletada por volta das 09:17:32. Podemos perceber também que as operações DELETE e UPDATE foram realizadas em uma mesma transação, ou seja, a transação 03001100F1000000 para UPDATE e a 04001D00F4000000 para DELETE.

Agora, vamos ver abaixo o que podemos obter selecionando dados a partir da view FLASHBACK_TRANSACTION_QUERY:

SCOTT> select undo_sql
   2  from flashback_transaction_query
   3  where xid = HEXTORAW('06001F00E9000000');

UNDO_SQL
-------------------------------------------------------------------------
delete from "SCOTT"."T1" where ROWID = 'AAADsWAAEAAAMtlAAA';

O resultado acima mostra o comando DML necessário que deverá ser utilizado para desfazer a alteração realizada na transação identificada pelo id 06001F00E9000000.

SCOTT> select undo_sql
   2  from flashback_transaction_query
   3  where xid = HEXTORAW('03001100F1000000');

UNDO_SQL
-------------------------------------------------------------------------
update "SCOTT"."T1" set "ID" = '1' where ROWID = 'AAADsWAAEAAAMtlAAA';
update "SCOTT"."T1" set "ID" = '2' where ROWID = 'AAADsWAAEAAAMtlAAB';
update "SCOTT"."T1" set "ID" = '3' where ROWID = 'AAADsWAAEAAAMtlAAC';

O resultado acima mostra os comandos DML's necessários que deverão ser utilizados para desfazerem as alterações realizadas na transação identificada pelo id 03001100F1000000.

SCOTT> select undo_sql
   2  from flashback_transaction_query
   3  where xid = HEXTORAW('04001D00F4000000');

UNDO_SQL
-------------------------------------------------------------------------
insert into "SCOTT"."T1"("ID") values ('10');
insert into "SCOTT"."T1"("ID") values ('20');
insert into "SCOTT"."T1"("ID") values ('30');

O resultado acima mostra os comandos DML's necessários que deverão ser utilizados para desfazerem as alterações realizadas na transação identificada pelo id 04001D00F4000000.

Por fim, irei obter abaixo, o histórico de todos os comandos DML's que poderão, de alguma forma, serem utilizados para desfazerem as alterações nos registros da tabela T1 desde a sua criação:

SCOTT> select to_char(commit_timestamp,'hh24:mi:ss') time,
   2         operation,
   3         undo_sql
   4  from flashback_transaction_query
   5  where table_name='T1' order by 1;

TIME     OPERATION UNDO_SQL
-------- --------- ----------------------------------------------------------------------
09:13:32 INSERT    delete from "SCOTT"."T1" where ROWID = 'AAADsWAAEAAAMtlAAA';
09:14:32 INSERT    delete from "SCOTT"."T1" where ROWID = 'AAADsWAAEAAAMtlAAB';
09:15:32 INSERT    delete from "SCOTT"."T1" where ROWID = 'AAADsWAAEAAAMtlAAC';
09:16:32 UPDATE    update "SCOTT"."T1" set "ID" = '1' where ROWID = 'AAADsWAAEAAAMtlAAA';
09:16:32 UPDATE    update "SCOTT"."T1" set "ID" = '2' where ROWID = 'AAADsWAAEAAAMtlAAB';
09:16:32 UPDATE    update "SCOTT"."T1" set "ID" = '3' where ROWID = 'AAADsWAAEAAAMtlAAC';
09:17:32 DELETE    insert into "SCOTT"."T1"("ID") values ('10');
09:17:32 DELETE    insert into "SCOTT"."T1"("ID") values ('20');
09:17:32 DELETE    insert into "SCOTT"."T1"("ID") values ('30');

9 linhas selecionadas.

Para maiores informações e exemplos de uso sobre ambas as tecnologias, você poderá acessar a documentação (em inglês) disponível no site da Oracle: Usando Flashback Version Query e Usando Flashback Transaction Query.

Google+

7 comentários:

Wilson Nadim disse...

Artigo legal, mas vc esqueceu de comentar sobre a ativação dos logs adicionais para usar O FBTQ. Abcs

Eduardo Legatti disse...

Olá Wilson,

Obrigado pelo comentário. Este artigo foi baseado usando o Oracle 10g R2. As coisas mudaram um pouco com Oracle 11g R2.

Sobre os logs adicionais:

SQL> alter database add supplemental log data;
SQL> alter database add supplemental log data (primary key) columns;
SQL> alter database add supplemental log data (foreign key) columns;

Como eu disse anteriormente, particularmante, no Oracle 10g, eu nunca precisei ativá-los, apesar da documentação mencioná-los. A tecnologia FLASHBACK confia nas informações de UNDO e não de REDO. Esses logs suplementares colocam informações adicionais de dados de colunas nos arquivos de redo log online.

Portanto, todas as informações que precisamos para usar o flashback... estão nos segmentos de UNDO.

Acredito que à partir do Oracle 11g R2 a documentação continuou a mesma, mas o comportamento mudou, ou seja, executar o comando abaixo se tornou obrigatório.

SQL> alter database add supplemental log data;

Abraços

Legatti

Tércio Costa disse...

Olá! gostei muito do seu trabalho e vou acompanhar de perto. Acontece que estou estudando para tirar a minha primeira certificação Oracle, a SQL Expert. Estava estudando sobre flashback, mais especificamente sobre a view flashback_transaction_query. Entendi completamente o funcionamento dela, mas não estou conseguindo reproduzir nada no meu ambiente de testes, com o Oracle Enterprise 12c, a view sempre fica vazia. Nos meus testes eu fiz o seguinte:

Dei um shutdown immediate no banco e dei apenas um startup mount, após isso habilitei o archivelog, assim: alter database archivelog; e depois um: alter database open;

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER SYSTEM ARCHIVE LOG CURRENT;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;

Após isso testei com vários usuários, sys, system, e usuários que criei apenas para teste com os privilégios necessários.

No pdborcl dava um erro ao tentar fazer um select na view flashback_transaction_query, dizendo que um arquivo de log era diferente de outro, algo do tipo. Em orcl eu conseguia fazer a query, mas sempre não retornava linha nenhuma. teste com vários usuários, tanto no pdborcl e orcl.

Já aumentei o meu UNDO_RETENTION, coloquei o tablespace como RETENTION GUARANTEE e nada também.

Alguma ajuda? Ou precisa de mais alguma informação? Me desculpe mas não sou um DBA.

Após tudo isto também fiz um: alter session set temp_undo_enabled=true; mas sem sucesso também.

Eduardo Legatti disse...

Olá Tércio,

Tudo bem? Não estou no momento com um ambiente Oracle 12c Multitentant, mas na documentação no link abaixo existem algumas restrições quanto ao uso da tecnologia flahsback no Oracle 12c em um banco de dados de Container.

http://docs.oracle.com/database/121/ADFNS/adfns_flashback.htm#ADFNS98765

For Oracle Database 12c Release 1 (12.1.0.1), Flashback Data Archive (FDA) is not supported in a CDB.
For Oracle Database 12c Release 1 (12.1.0.2), this restriction is removed.
Flashback Transaction Query is not supported in a CDB.
Flashback Transaction Backout is not supported in a CDB.

Não diz nada sobre PDBs, então você precisa verificar qual o erro (ORA-) que está sendo emitifo quando você usao o banco PDBORCL.

Enfim, você já fez o teste criando um banco no Oracle 12c sem ser do tipo Container CDB? Ajá que você não é DBA, acho que vai facilitar um pouco o seu estudo. ;-)


Por exemplo, um teste simples seria:


C:\>sqlplus adam/adam

SQL*Plus: Release 11.2.0.1.0 Production on Seg Jun 29 16:54:06 2015

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> insert into t1 values (10);

1 linha criada.

SQL> commit;

Commit concluído.

SQL> delete from t1;

1 linha deletada.

SQL> commit;

Commit concluído.

SQL> select distinct VERSIONS_XID from t1 VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE;

VERSIONS_XID
----------------
2B000600A4700000
2C0020009F810000

2 linhas selecionadas.

C:\>sqlplus system/*****

SQL*Plus: Release 11.2.0.1.0 Production on Seg Jun 29 16:55:28 2015

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 operation,table_name from flashback_transaction_query where table_owner='ADAM' and xid=HEXTORAW('2B000600A4700000');

OPERATION TABLE_NAME
----------------- -------------
DELETE T1

1 linha selecionada.

Abraços,

Legatti

Tércio Costa disse...

Muito obrigado pela resposta, e desculpe pela demora. estive bem ocupado esses dias. Quando estou conectado em um PDB o erro é o ORA-01295. Que diz que existe uma incompatibilidade de DB_ID entre o dicionário USE_ONLINE_CATALOG e os arquivos de log.

Como faço para criar um banco que não seja cdb? Visto que o cdb esta deprecated.

Eduardo Legatti disse...

Olá Tércio,

O termo "deprecated" não é o mesmo que "não suportado". Veja como exemplo o tipo de dado LONG. Ele está deprecated desde a versão do Oracle 8, mas existe e é suportado até hoje, inclusive no Oracle 12c.

From the Upgrade Guide:
By deprecate, we mean that the feature is no longer being enhanced but is still supported for the full life of the 12.1 release. By desupported, we mean that Oracle will no longer fix bugs related to that feature and may remove the code altogether. Where indicated, a deprecated feature may be desupported in a future major release.

Para criar um banco de dados single-tenant, ou seja, Non-CDB, basta você desmarcar a opção (Criar como Banco de dados Container) no DBCA dutrante a criação do banco de dados. No artigo abaixo eu abordo a arquitetura Multi-tentant e tem uma tela do DBCA que mostra essa opção.

http://eduardolegatti.blogspot.com.br/2014/02/oracle-multitenant-abordando.html

Abraços,

Legatti

Tércio Costa disse...

Muito obrigado pela explicação Eduardo. Li o artigo lá completo, parece que isto é uma opção na hora da instalação. Acho que deu pra entender bem apesar de não estar me focando em ser um DBA e sim um Developer.

Mais uma vez obrigado pela ajuda!

Postagens populares