Roberto Sobrinho

02/08/2024

Aprimorando a Auditoria de Dados com o Flashback Data Archive no Oracle Database

Olá, pessoal! Como 6 tão? Já pensaram em uma maneira eficaz de auditar e recuperar dados no Oracle Database? Com o Flashback Data Archive (FDA), posso manter um histórico detalhado das mudanças nos meus dados, garantindo que nada se perca ou seja alterado indevidamente. Vou explorar como configurar e utilizar essa poderosa funcionalidade com exemplos práticos!

O que é o Flashback Data Archive?

O Flashback Data Archive é uma funcionalidade poderosa do Oracle que automaticamente registra todas as mudanças feitas nas tabelas e guarda essas informações de maneira segura. Com isso, você pode consultar o estado dos dados em um momento específico do passado, o que é extremamente útil para auditorias e para recuperar dados que foram perdidos ou alterados incorretamente. Em essência, ele permite que você tenha um histórico detalhado das modificações feitas nos dados, facilitando a conformidade com políticas de segurança e requisitos legais de retenção de dados.

Configurar o Flashback Data Archive

Passo 1: Criar o Tablespace

Criar um tablespace dedicado para armazenar os dados históricos do Flashback Data Archive::

CREATE TABLESPACE TBS_Flashback_Data_Archive
DATAFILE 'TBS_Flashback_Data_Archive.dbf'
SIZE 100M
AUTOEXTEND ON
NEXT 10M MAXSIZE UNLIMITED;

Este comando cria um tablespace chamado TBS_Flashback_Data_Archive com um arquivo de dados inicial de 100MB que pode crescer automaticamente em incrementos de 10MB, sem um limite máximo definido.

Passo 2: Criar o Flashback Data Archive

Criar um Flashback Data Archive utilizando o tablespace criado:

CREATE FLASHBACK ARCHIVE arquivo_financeiro
TABLESPACE TBS_FLASHBACK_DATA_ARCHIVE
RETENTION 1 YEAR;

Neste exemplo, o Flashback Archive é criado com o nome arquivo_financeiro, armazenando dados no tablespace TBS_Flashback_Data_Archive e mantendo os dados históricos por 1 ano.

Passo 3: Criar Tabelas para Teste

Antes de associar as tabelas ao Flashback Data Archive, vou criar algumas tabelas para testes no usuário hr e inserir dados.

CREATE TABLE hr.funcionarios TABLESPACE users AS
SELECT 
    LEVEL AS user_id,
    'User_' || LEVEL AS username,
    'Password_' || LEVEL AS password,
    'ACTIVE' AS account_status,
    SYSDATE AS lock_date,
    SYSDATE + 365 AS expiry_date,
    'USERS' AS default_tablespace,
    'TEMP' AS temporary_tablespace,
    SYSDATE AS created,
    'DEFAULT' AS profile,
    'DEFAULT_GROUP' AS initial_rsrc_consumer_group,
    'External_Name_' || LEVEL AS external_name
FROM dual
CONNECT BY LEVEL <= 4000;

CREATE TABLE hr.transacoes TABLESPACE users AS
SELECT 
    LEVEL AS transaction_id,
    'Object_' || LEVEL AS object_name,
    'Owner_' || LEVEL AS owner,
    'TABLE' AS object_type,
    SYSDATE AS created,
    SYSDATE + 30 AS last_ddl_time,
    'ENABLED' AS status
FROM dual
CONNECT BY LEVEL <= 6000;

Esses comandos criarão as tabelas hr.funcionarios e hr.transacoes no tablespace users com dados fictícios.

Passo 4: Associar Tabelas ao Flashback Data Archive

Para começar a armazenar dados históricos de uma tabela específica, vou associá-la ao Flashback Data Archive criado.

ALTER TABLE hr.funcionarios FLASHBACK ARCHIVE arquivo_financeiro;

ALTER TABLE hr.transacoes FLASHBACK ARCHIVE arquivo_financeiro;

Realizar Modificações nos Dados

Agora, vou realizar algumas modificações nos dados para que eu possa analisar as mudanças detalhadas posteriormente.

Modificar Dados na Tabela `funcionarios` e `transacoes`

!date

UPDATE hr.funcionarios SET username = 'NEW_USER' WHERE user_id = 101;
DELETE FROM hr.funcionarios WHERE user_id = 102;
INSERT INTO hr.funcionarios (user_id, username) VALUES (999, 'TEST_USER');
COMMIT;

UPDATE hr.transacoes SET object_name = 'NEW_OBJECT' WHERE transaction_id = 1;
DELETE FROM hr.transacoes WHERE transaction_id = 2;
INSERT INTO hr.transacoes (transaction_id, object_name) VALUES (999, 'TEST_OBJECT');
COMMIT;

!date

Exemplos de Consultas de Dados Históricos

Uma vez configurado o Flashback Data Archive e realizadas as modificações, posso consultar os dados históricos usando a cláusula VERSIONS BETWEEN.

Exemplo 1: Consultar Alterações em um Período Específico

COLUMN user_id FORMAT 99999
COLUMN username FORMAT A20
COLUMN VERSIONS_STARTTIME FORMAT A30
COLUMN VERSIONS_ENDTIME FORMAT A40

WITH version_times AS (
    SELECT 
        NVL(MAX(VERSIONS_STARTTIME), TO_TIMESTAMP('2024-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) AS max_starttime,
        NVL(MAX(VERSIONS_ENDTIME), TO_TIMESTAMP('2024-12-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS')) AS max_endtime
    FROM hr.funcionarios VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE
)
SELECT user_id, username, VERSIONS_STARTTIME, VERSIONS_ENDTIME
FROM hr.funcionarios VERSIONS BETWEEN TIMESTAMP 
    (SELECT max_starttime FROM version_times)
    AND 
    (SELECT max_endtime FROM version_times)
WHERE user_id = 101;

Esta consulta me permitirá ver todas as versões das linhas com user_id = 101 que foram modificadas entre 1º de janeiro de 2024 e 31 de dezembro de 2024, incluindo os horários de início e término das versões.

Exemplo 2: Recuperar o Estado de uma Linha em um Momento Específico

--Dado em 02/ago/24 as 11:30
SELECT 'Historico' DADO, user_id, username
FROM hr.funcionarios AS OF TIMESTAMP TO_TIMESTAMP('2024-08-02 11:30:00', 'YYYY-MM-DD HH24:MI:SS')
WHERE user_id = 101

union all

--Dado Atual
SELECT  'Atual' DADO, user_id, username
FROM hr.funcionarios
WHERE user_id = 101

A consulta retorna duas linhas para o user_id 101: uma com os dados como estavam em 02 de agosto de 2024, às 11:30 (históricos) e outra com os dados atuais. Isso permite comparar o estado dos dados em um ponto no tempo específico com o estado atual.

Exemplo 3: Visualizar o Histórico Completo de uma Linha

COLUMN user_id FORMAT 99999
COLUMN username FORMAT A20
COLUMN VERSIONS_STARTTIME FORMAT A40
COLUMN VERSIONS_ENDTIME FORMAT A40

SELECT VERSIONS_OPERATION, user_id, username, VERSIONS_STARTTIME, VERSIONS_ENDTIME
FROM hr.funcionarios VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
WHERE user_id = 101;

A consulta retornará todas as versões da linha com user_id = 101, mostrando cada alteração que foi feita, quando a alteração ocorreu e o tipo de operação realizada. Isso permite que eu acompanhe toda a trajetória da linha ao longo do tempo, fornecendo um registro completo de todas as mudanças.

Neste exemplo, posso ver que o usuário com user_id = 101 foi inicialmente inserido com o username “User_101”. O VERSIONS_STARTTIME está NULL, indicando que a data de criação inicial da linha não está registrada no Flashback Data Archive. Isso pode ocorrer se a tabela foi associada ao Flashback Data Archive após a criação inicial da linha. Às 11:34:03 AM do dia 2 de agosto de 2024, o username foi atualizado para “NEW_USER”. Esta versão permanece válida até o momento atual (indicado pelo VERSIONS_ENDTIME NULL).

VERSIONS_OPERATION: Esta coluna indica o tipo de operação que foi realizada na linha.
- I para inserção (insert)
- U para atualização (update)
- D para exclusão (delete)
VERSIONS_STARTTIME e VERSIONS_ENDTIME: Estas colunas mostram os timestamps de início e fim da validade de cada versão da linha. Se a linha ainda estiver válida no momento da consulta, o VERSIONS_ENDTIME será NULL.
SCN MINVALUE AND MAXVALUE: A cláusula VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE especifica que desejo visualizar todas as versões da linha desde a criação do Flashback Data Archive até o momento atual.

Gerenciar o Flashback Data Archive

Alterar a Retenção de Dados

Posso alterar o período de retenção de dados para o Flashback Archive:

ALTER FLASHBACK ARCHIVE arquivo_financeiro MODIFY RETENTION 2 YEAR;

Remover uma Tabela do Flashback Data Archive

Caso eu não precise mais rastrear as mudanças de uma tabela, posso removê-la do Flashback Data Archive:

ALTER TABLE hr.funcionarios NO FLASHBACK ARCHIVE;

Excluir o Flashback Data Archive

Se for necessário excluir um Flashback Archive:

ALTER TABLE hr.funcionarios NO FLASHBACK ARCHIVE;

Licenciamento

Para detalhes sobre o licenciamento, consulte: Informações sobre Licenciamento Oracle.

Pontos de Atenção

Ao usar o Flashback Data Archive, considere os seguintes pontos:

Espaço em Disco: O FDA pode usar bastante espaço em disco, especialmente com muitas alterações. Monitore e ajuste a política de retenção conforme necessário.
Performance: Pode haver impacto na performance em sistemas com alto volume de transações. Teste e ajuste conforme necessário.
Retenção de Dados: Defina um período de retenção que balanceie a necessidade de auditoria com o uso de espaço em disco.
Segurança: Restrinja o acesso aos dados arquivados para proteger informações sensíveis.
Configuração e Monitoramento: Configure e monitore regularmente o FDA para garantir que funcione corretamente.

Por que Usar o Flashback Data Archive?

Aqui estão os principais motivos para usar o FDA:

Auditoria e Conformidade: Mantém um histórico completo das mudanças nos dados, essencial para auditorias e conformidade.
Recuperação de Dados: Facilita a recuperação de dados alterados ou excluídos sem necessidade de backups completos.
Análise Histórica: Permite analisar como os dados mudaram ao longo do tempo, útil para identificar tendências.
Facilidade de Uso: Fácil de configurar e utilizar com comandos SQL simples.
Integração com Oracle: Funciona bem com outras ferramentas do Oracle, oferecendo uma solução completa de auditoria e segurança.

O Flashback Data Archive é uma ferramenta indispensável para manter e auditar dados históricos no Oracle Database. Ele não só simplifica a recuperação de dados, permitindo reverter alterações indesejadas, como também assegura a conformidade regulatória ao manter um registro detalhado das mudanças nos dados. Além disso, o FDA facilita a análise histórica, ajudando a identificar tendências e a tomar decisões informadas com base em dados passados.

Integrar o Flashback Data Archive no seu ambiente Oracle Database oferece uma solução eficiente para gestão e proteção de dados, garantindo maior integridade, segurança e disponibilidade das informações. Adotar o FDA é um passo importante para qualquer organização que busca aprimorar suas práticas de auditoria, recuperação e análise de dados..

🎢🛝 #20240802 #GuinaNãoTinhaDó #Férias #CarroLimpo #SeReagirBúmViraPó #OlimpíadasParis2024 #600Conto 🛝🎢

Compartilhe

Pesquisar

Roberto Sobrinho

Sou Roberto Fernandes Sobrinho, também conhecido como Sobrinho DBA , pós graduado em “Architecture and Database Administration”, entusiasta, dedicado e com 20 anos de experiência com Oracle Database e suas diversas distribuições e variações.