Administer Flashback Data Archive and Schema Evolution

1. Documentación en Tahiti -> Oracle Database, 11g Release 2 (11.2) -> Masters Book List -> Advanced Application Developer’s Guide -> 12 Using Oracle Flashback Technology -> Using Flashback Data Archive (Oracle Total Recall)

2. Flashback Data Archive, también conocido como Oracle Total Recall, permite registrar de forma transparente y automática todos los cambios que se producen en un juego de tablas en una BD Oracle 11g. Además se pueden consultar los datos de estas tablas en cualquier punto del tiempo hasta un máximo determinado definido por nosotros (RETENTION).

Por defecto, FBA (Flasback Data Archive) está desactivado para todas las tablas. Podemos activarlo para cualquier tabla siempre y cuando se den las siguientes condiciones:

· Debemos tener el privilegio FLASHBACK ARCHIVE sobre dicha tabla
· La tabla no puede ser de tipo NESTED, CLUSTER, TEMPORARY, REMOTE o EXTERNAL
· La tabla no puede contener columnas LONG o NESTED

El privilegio que nos permite manipular una configuración FBA es «FLASHBACK ARCHIVE ADMINISTER», pero nosotros vamos a utilizar SYSDBA así que no debemos preocuparnos por ello.

3. Realizamos las pruebas sobre el esquema HR. En primer lugar necesitamos configurar FBA y habilitar alguna tabla.

-- Lo primero que necesitamos es un TBS donde se almacenará los datos de FBA
CREATE TABLESPACE FBA DATAFILE '/u02/app/oacle/oradata/OCM/fba01.dbf' SIZE 500M;

-- Creamos el archivo por defecto para FBA
CREATE FLASHBACK ARCHIVE DEFAULT FLA1 TABLESPACE FBA QUOTA 500M RETENTION 1 YEAR;

-- Activamos FBA para la tabla EMPLOYEES
ALTER TABLE HR.EMPLOYEES FLASHBACK ARCHIVE;

-- Podemos consultar la configuración de FBA en las siguientes vistas
SELECT * FROM DBA_FLASHBACK_ARCHIVE;
SELECT * FROM DBA_FLASHBACK_ARCHIVE_TS;
SELECT * FROM DBA_FLASHBACK_ARCHIVE_TABLES;

4. Ahora ya podemos realizar pruebas sobre la tabla EMPLOYEES.

-- Obtenemos la fecha y hora actual para realizar un seguimiento de nuestras pruebas
--   Ej. "2013/06/21 08:31:36"
-- También podemos usar el SCN con DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER
ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI:SS';
SELECT SYSDATE FROM DUAL;
SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;

-- Modificamos algunos registros
-- Borramos un empleado
DELETE FROM HR.EMPLOYEES WHERE EMPLOYEE_ID=192;
COMMIT;

-- Modificamos el salario de un trabajador varias veces
UPDATE HR.EMPLOYEES SET SALARY=12000 WHERE EMPLOYEE_ID=168;
COMMIT;
UPDATE HR.EMPLOYEES SET SALARY=12500 WHERE EMPLOYEE_ID=168;
COMMIT;
UPDATE HR.EMPLOYEES SET SALARY=12550 WHERE EMPLOYEE_ID=168;
COMMIT;

-- Realizamos consultas sobre el histórico de datos almacenados en FBA
-- Veamos que trabajadores han abandonado la empresa desde que activamos FBA
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME
  FROM HR.EMPLOYEES
  AS OF TIMESTAMP TO_TIMESTAMP('2013/06/21 08:31:36','YYYY/MM/DD HH24:MI:SS')
MINUS
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME
  FROM HR.EMPLOYEES;

-- Obtenemos todos los incrementos salariales de Lisa Ozer (EMPLOYEE_ID=168)
-- En este ejemplo obtenemos todas las versiones entre dos fechas
-- Ademas obtenemos las pseudcolumnas VERSIONS_STARTTIME y VERSIONS_STARTSCN
COL VERSIONS_STARTTIME FORMAT A40
SELECT VERSIONS_STARTTIME, VERSIONS_STARTSCN, FIRST_NAME, LAST_NAME, SALARY
  FROM HR.EMPLOYEES
  VERSIONS BETWEEN TIMESTAMP
  TO_TIMESTAMP('2013/06/21 08:31:36','YYYY/MM/DD HH24:MI:SS') AND
  SYSTIMESTAMP
WHERE
  EMPLOYEE_ID=168;

-- También podemos consultar los datos sobre un SCN dado
SELECT FIRST_NAME, LAST_NAME, SALARY
  FROM HR.EMPLOYEES
  AS OF SCN 4686793
WHERE
  EMPLOYEE_ID=168;

5. FBA nos permite capturar la evolución de un esquema registrando los siguientes tupos de cambios:

· Añadir, Borrar, Renombrar o Modificar una columna
· Borrar o Truncar una partición
· Renombrar o Truncar una tabla (Borrar una tabla con FBA produce error ORA-55610)

Algunos cambios (Ej.: MOVE/SPLIT/CHANGE PARTITIONS) requieren de los métodos del paquete DBMS_FLASHBACK_ARCHIVE para poder realizarlo. Veamos como por ejemplo, cómo podemos desasociar y asociar una tabla a su histórico por si tenemos que realizar alguna de estas operaciones.

-- Creamos una tabla clon de EMPLOYEES con una CONSTRAINT
CREATE TABLE HR.EMPLOYEES_FBA AS SELECT * FROM HR.EMPLOYEES;
ALTER TABLE HR.EMPLOYEES_FBA ADD CONSTRAINT employee_pk PRIMARY KEY (employee_id);

-- Activamos FBA sobre ella
ALTER TABLE HR.EMPLOYEES_FBA FLASHBACK ARCHIVE;

-- Modificamos un registro
UPDATE HR.EMPLOYEES_FBA SET SALARY=10000 WHERE EMPLOYEE_ID=203;
COMMIT;

-- Deshabilitamos la CONSTRAINT
ALTER TABLE HR.EMPLOYEES_FBA DISABLE CONSTRAINT EMPLOYEE_PK;

-- Volvemos a habilitarla (ERROR ORA-55610)
ALTER TABLE HR.EMPLOYEES_FBA ENABLE CONSTRAINT EMPLOYEE_PK;

-- Para poder habilitar la CONSTRAINT necesitamos desasociar la tabla de su histórico
-- Primero vamos a obtener cuál es la tabla histórica asociada
--   Ej. "SYS_FBA_HIST_89835"
SELECT * FROM DBA_FLASHBACK_ARCHIVE_TABLES WHERE TABLE_NAME='EMPLOYEES_FBA';

-- Desasociamos la tabla
EXEC DBMS_FLASHBACK_ARCHIVE.DISASSOCIATE_FBA('HR','EMPLOYEES_FBA');

-- Habilitamos la CONSTRAINT
ALTER TABLE HR.EMPLOYEES_FBA ENABLE CONSTRAINT EMPLOYEE_PK;

-- Asociamos la tabla para resumir las operaciones de FBA
EXEC DBMS_FLASHBACK_ARCHIVE.REASSOCIATE_FBA('HR','EMPLOYEES_FBA');

6. Limpiamos el entorno.

-- Se puede purgar los datos de un fichero FBA (opcional)
ALTER FLASHBACK ARCHIVE FLA1 PURGE BEFORE TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY);

-- Deshabilitamos FBA para las tablas utilizadas
ALTER TABLE HR.EMPLOYEES NO FLASHBACK ARCHIVE;
ALTER TABLE HR.EMPLOYEES_FBA NO FLASHBACK ARCHIVE;

-- Borramos el archivo FBA
DROP FLASHBACK ARCHIVE FLA1;

-- Borramos el TBS
DROP TABLESPACE FBA INCLUDING CONTENTS AND DATAFILES;

-- Borramos la tabla temporal EMPLOYEES_FBA
DROP TABLE HR.EMPLOYEES_FBA;