Oracle Flashback Technologies - Complete Recovery Guide

Oracle Flashback Technologies - Complete Recovery Guide

Oracle Flashback Technologies provide a comprehensive set of features for recovering from human errors, viewing historical data, and performing point-in-time recovery without traditional backup restoration. These features enable quick recovery from logical corruption while maintaining database availability.

🎯 Overview

What is Oracle Flashback?

Oracle Flashback is a suite of technologies that allow you to:

• Recover from human errors quickly without restoring from backup
• View historical data at any point in time
• Undo DDL operations like DROP TABLE
• Analyze data changes over time
• Perform point-in-time recovery while database remains online

Flashback Technologies Matrix

Technology	Oracle Version	Scope	Dependency
Flashback Query	9i+	Row-level	Undo data
Flashback Table	10g+	Table-level	Undo data
Flashback Drop	10g+	Object-level	Recycle Bin
Flashback Versions	10g+	Row-level	Undo data
Flashback Transaction	10g+	Transaction-level	Undo data
Flashback Database	10g+	Database-level	Flashback logs

🔄 Flashback Query

Basic Flashback Query

View Data at Specific Time

-- View table data as of 2 hours ago
SELECT employee_id, first_name, last_name, salary
FROM employees AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '2' HOUR)
WHERE department_id = 50;

-- Compare current vs historical data
SELECT
  current.employee_id,
  current.salary as current_salary,
  historical.salary as salary_2hrs_ago,
  current.salary - historical.salary as salary_change
FROM employees current,
     employees AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '2' HOUR) historical
WHERE current.employee_id = historical.employee_id
  AND current.salary != historical.salary;

Using SCN (System Change Number)

-- Get current SCN
SELECT current_scn FROM v$database;

-- Query using specific SCN
SELECT * FROM employees AS OF SCN 1234567
WHERE department_id = 10;

-- Find SCN for specific timestamp
SELECT scn, timestamp
FROM v$archived_log
WHERE timestamp BETWEEN
  TO_DATE('2024-01-15 10:00:00', 'YYYY-MM-DD HH24:MI:SS') AND
  TO_DATE('2024-01-15 11:00:00', 'YYYY-MM-DD HH24:MI:SS');

Advanced Flashback Query Applications

Data Auditing and Forensics

-- Find when salary was changed
SELECT
  e1.employee_id,
  e1.first_name,
  e1.last_name,
  e1.salary as current_salary,
  e2.salary as morning_salary,
  CASE
    WHEN e1.salary > e2.salary THEN 'INCREASED'
    WHEN e1.salary < e2.salary THEN 'DECREASED'
    ELSE 'NO CHANGE'
  END as change_type
FROM employees e1,
     employees AS OF TIMESTAMP (TRUNC(SYSDATE) + 8/24) e2
WHERE e1.employee_id = e2.employee_id (+)
  AND (e1.salary != e2.salary OR e2.employee_id IS NULL);

-- Track record insertions during specific period
SELECT employee_id, first_name, hire_date
FROM employees
WHERE employee_id NOT IN (
  SELECT employee_id
  FROM employees AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY)
);

📊 Flashback Versions Query

Tracking Row Changes Over Time

Basic Versions Query

-- See all versions of a row within time range
SELECT
  versions_starttime,
  versions_endtime,
  versions_xid,
  versions_operation,
  employee_id,
  first_name,
  last_name,
  salary,
  department_id
FROM employees
VERSIONS BETWEEN TIMESTAMP
  (SYSTIMESTAMP - INTERVAL '7' DAY) AND SYSTIMESTAMP
WHERE employee_id = 100
ORDER BY versions_starttime;

Advanced Versions Analysis

-- Complete audit trail for critical table
SELECT
  versions_startscn,
  versions_endscn,
  versions_starttime,
  versions_endtime,
  versions_xid,
  versions_operation,
  employee_id,
  salary,
  LAG(salary) OVER (ORDER BY versions_starttime) as previous_salary,
  salary - LAG(salary) OVER (ORDER BY versions_starttime) as salary_change
FROM employees
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
WHERE employee_id = 100
  AND versions_operation IS NOT NULL
ORDER BY versions_starttime;

-- Find who made changes using transaction details
SELECT DISTINCT
  v.versions_xid,
  v.versions_starttime,
  v.employee_id,
  v.salary,
  t.username,
  t.terminal,
  t.os_user
FROM employees VERSIONS BETWEEN TIMESTAMP
     (SYSTIMESTAMP - INTERVAL '24' HOUR) AND SYSTIMESTAMP v,
     v$transaction t
WHERE v.versions_xid = HEXTORAW(t.xid)
  AND v.employee_id = 100;

🔧 Flashback Transaction Query

Transaction-Level Analysis

Identify Transaction Details

-- Get transaction information
SELECT
  xid,
  start_scn,
  commit_scn,
  logon_user,
  undo_change#,
  operation,
  table_name,
  table_owner,
  row_id,
  undo_sql
FROM flashback_transaction_query
WHERE xid = HEXTORAW('0A001500B4010000')
ORDER BY start_scn, undo_change#;

-- Find all transactions affecting specific table in time period
SELECT DISTINCT
  xid,
  logon_user,
  start_timestamp,
  commit_timestamp,
  operation
FROM flashback_transaction_query
WHERE table_owner = 'HR'
  AND table_name = 'EMPLOYEES'
  AND start_timestamp > SYSTIMESTAMP - INTERVAL '1' DAY
ORDER BY start_timestamp;

Generate Undo SQL

-- Generate compensating transactions
SELECT
  xid,
  operation,
  undo_sql
FROM flashback_transaction_query
WHERE table_owner = 'HR'
  AND table_name = 'EMPLOYEES'
  AND logon_user = 'SCOTT'
  AND start_timestamp BETWEEN
    TO_TIMESTAMP('2024-01-15 14:00:00', 'YYYY-MM-DD HH24:MI:SS') AND
    TO_TIMESTAMP('2024-01-15 15:00:00', 'YYYY-MM-DD HH24:MI:SS')
ORDER BY start_scn DESC, undo_change# DESC;

📋 Flashback Table

Table-Level Recovery

Basic Table Flashback

-- Enable row movement (required for flashback table)
ALTER TABLE employees ENABLE ROW MOVEMENT;

-- Flashback table to specific timestamp
FLASHBACK TABLE employees
TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '2' HOUR);

-- Flashback table to SCN
FLASHBACK TABLE employees TO SCN 1234567;

-- Flashback multiple tables
FLASHBACK TABLE employees, departments
TO TIMESTAMP TO_TIMESTAMP('2024-01-15 10:00:00', 'YYYY-MM-DD HH24:MI:SS');

Advanced Table Recovery Scenarios

-- Create restore point before flashback
CREATE RESTORE POINT before_flashback;

-- Flashback with verification
-- 1. Check data before flashback
SELECT COUNT(*), MAX(salary), MIN(salary) FROM employees;

-- 2. Perform flashback
FLASHBACK TABLE employees
TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '4' HOUR);

-- 3. Verify flashback results
SELECT COUNT(*), MAX(salary), MIN(salary) FROM employees;

-- 4. If needed, flashback to restore point
FLASHBACK TABLE employees TO RESTORE POINT before_flashback;

-- Clean up restore point
DROP RESTORE POINT before_flashback;

Handling Constraints During Flashback

-- Disable referential constraints before flashback
ALTER TABLE employees DISABLE CONSTRAINT emp_dept_fk;
ALTER TABLE job_history DISABLE CONSTRAINT jhist_emp_fk;

-- Perform flashback
FLASHBACK TABLE employees TO SCN 1234567;

-- Re-enable constraints
ALTER TABLE employees ENABLE CONSTRAINT emp_dept_fk;
ALTER TABLE job_history ENABLE CONSTRAINT jhist_emp_fk;

-- Check for constraint violations
SELECT constraint_name, status
FROM user_constraints
WHERE table_name = 'EMPLOYEES'
  AND status = 'DISABLED';

🗑️ Flashback Drop (Recycle Bin)

Managing Dropped Objects

Basic Recycle Bin Operations

-- View objects in recycle bin
SELECT object_name, original_name, type, droptime, space
FROM recyclebin
ORDER BY droptime DESC;

-- Show recycle bin contents (SQL*Plus command)
SHOW RECYCLEBIN;

-- Flashback dropped table
FLASHBACK TABLE employees TO BEFORE DROP;

-- Flashback with rename
FLASHBACK TABLE employees TO BEFORE DROP RENAME TO employees_recovered;

Advanced Recycle Bin Management

-- Flashback specific version (if multiple drops)
SELECT object_name, original_name, droptime, space
FROM recyclebin
WHERE original_name = 'EMPLOYEES'
ORDER BY droptime DESC;

-- Flashback specific version
FLASHBACK TABLE "BIN$ABC123XYZ=" TO BEFORE DROP RENAME TO employees_v2;

-- Purge specific object from recycle bin
PURGE TABLE employees;

-- Purge entire recycle bin
PURGE RECYCLEBIN;

-- Purge tablespace recycle bin
PURGE TABLESPACE users;

-- Check recycle bin space usage
SELECT tablespace_name,
       SUM(space) * 8192 / 1024 / 1024 as space_mb
FROM dba_recyclebin
GROUP BY tablespace_name;

Recycle Bin Administration

-- Disable recycle bin
ALTER SYSTEM SET recyclebin = OFF;

-- Enable recycle bin
ALTER SYSTEM SET recyclebin = ON;

-- Check recycle bin status
SHOW PARAMETER recyclebin;

-- Set recycle bin size limit
ALTER SYSTEM SET "_recyclebin_space_limit" = 1000; -- MB

🏢 Flashback Database

Database-Level Point-in-Time Recovery

Configuring Flashback Database

-- Check flashback database status
SELECT flashback_on, log_mode FROM v$database;

-- Enable archiving (prerequisite)
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

-- Set flash recovery area
ALTER SYSTEM SET db_recovery_file_dest_size = 10G;
ALTER SYSTEM SET db_recovery_file_dest = '/u01/oracle/flash_recovery_area';

-- Configure flashback retention
ALTER SYSTEM SET db_flashback_retention_target = 1440; -- 24 hours in minutes

-- Enable flashback database
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE FLASHBACK ON;
ALTER DATABASE OPEN;

Performing Flashback Database

-- Create guaranteed restore point
CREATE RESTORE POINT before_upgrade GUARANTEE FLASHBACK DATABASE;

-- Check available flashback time
SELECT oldest_flashback_scn, oldest_flashback_time
FROM v$flashback_database_log;

-- Perform flashback database (as SYSDBA)
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;

-- Flashback to timestamp
FLASHBACK DATABASE TO TIMESTAMP
  TO_TIMESTAMP('2024-01-15 08:00:00', 'YYYY-MM-DD HH24:MI:SS');

-- Or flashback to restore point
FLASHBACK DATABASE TO RESTORE POINT before_upgrade;

-- Open database with resetlogs
ALTER DATABASE OPEN RESETLOGS;

Monitoring Flashback Database

-- Check flashback database statistics
SELECT * FROM v$flashback_database_stat
ORDER BY begin_time DESC;

-- Monitor flashback log generation
SELECT
  TO_CHAR(begin_time, 'YYYY-MM-DD HH24:MI') as hour,
  SUM(flashback_data) / 1024 / 1024 as flashback_mb,
  SUM(db_data) / 1024 / 1024 as db_data_mb,
  SUM(redo_data) / 1024 / 1024 as redo_mb
FROM v$flashback_database_stat
WHERE begin_time > SYSDATE - 1
GROUP BY TO_CHAR(begin_time, 'YYYY-MM-DD HH24:MI')
ORDER BY hour;

-- Check flash recovery area usage
SELECT * FROM v$recovery_file_dest;

-- View flashback logs
SELECT * FROM v$flashback_database_logfile;

🔍 Undo Management for Flashback

Configuring Undo for Flashback Features

Undo Tablespace Sizing

-- Check current undo configuration
SELECT tablespace_name, retention, status
FROM dba_tablespaces
WHERE tablespace_name LIKE '%UNDO%';

SHOW PARAMETER undo;

-- Calculate undo space requirements
SELECT
  maxquerylen,
  maxquerysqlid,
  undoblks,
  maxconcurrency
FROM v$undostat
WHERE begin_time > SYSDATE - 1
ORDER BY maxquerylen DESC;

-- Set undo retention
ALTER SYSTEM SET undo_retention = 3600; -- 1 hour

-- Enable undo retention guarantee
ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;

Monitoring Undo for Flashback

-- Check undo retention and usage
SELECT
  to_char(begin_time,'MM/DD/YYYY HH24:MI:SS') as begin_time,
  to_char(end_time,'MM/DD/YYYY HH24:MI:SS') as end_time,
  tuned_undoretention,
  maxquerylen,
  unxpstealcnt,
  unxpblkrelcnt
FROM v$undostat
WHERE begin_time > SYSDATE - 1
ORDER BY begin_time;

-- Check for ORA-01555 potential
SELECT
  tablespace_name,
  status,
  count(*) as extent_count,
  sum(bytes) / 1024 / 1024 as size_mb
FROM dba_undo_extents
GROUP BY tablespace_name, status;

📈 Performance Considerations

Optimizing Flashback Performance

Monitoring Flashback Overhead

-- Monitor flashback query performance
SELECT
  sql_id,
  executions,
  elapsed_time / 1000000 as elapsed_sec,
  buffer_gets,
  disk_reads,
  rows_processed
FROM v$sql
WHERE sql_text LIKE '%AS OF%'
   OR sql_text LIKE '%VERSIONS%'
ORDER BY elapsed_time DESC;

-- Check undo block read patterns
SELECT
  name,
  value
FROM v$sysstat
WHERE name LIKE '%undo%'
   OR name LIKE '%consistent%';

Index Considerations

-- Identify tables frequently used with flashback
SELECT
  owner,
  table_name,
  COUNT(*) as flashback_queries
FROM v$sql_plan p, v$sql s
WHERE p.sql_id = s.sql_id
  AND p.operation = 'TABLE ACCESS'
  AND s.sql_text LIKE '%AS OF%'
GROUP BY owner, table_name
ORDER BY flashback_queries DESC;

-- Consider adding indexes for flashback queries
-- Example: Index on timestamp columns often used in flashback conditions
CREATE INDEX idx_emp_hire_date ON employees(hire_date);

🛡️ Security and Best Practices

Security Considerations

Privilege Management

-- Grant flashback privileges
GRANT FLASHBACK ANY TABLE TO dba_user;
GRANT SELECT ANY TABLE TO recovery_user;

-- Check flashback privileges
SELECT grantee, privilege, admin_option
FROM dba_sys_privs
WHERE privilege LIKE '%FLASHBACK%';

-- Object-level privileges
GRANT FLASHBACK ON employees TO hr_manager;

Audit Flashback Operations

-- Enable auditing for flashback operations
AUDIT FLASHBACK ANY TABLE;
AUDIT SELECT ANY TABLE;

-- Review flashback audit trail
SELECT
  username,
  timestamp,
  action_name,
  object_name,
  sql_text
FROM dba_audit_trail
WHERE action_name LIKE '%FLASHBACK%'
ORDER BY timestamp DESC;

Best Practices

Design Guidelines

1. Undo Management

   • Size undo tablespace appropriately
   • Set adequate undo retention
   • Monitor undo usage patterns

2. Performance Optimization

   • Use indexes on frequently queried columns
   • Limit flashback query time ranges
   • Monitor undo block reads

3. Operational Procedures

   • Create restore points before major changes
   • Test flashback procedures regularly
   • Document recovery procedures

4. Monitoring and Maintenance

   • Monitor flash recovery area space
   • Archive old flashback logs
   • Regular undo statistics analysis

Common Use Cases

-- Before major data changes
CREATE RESTORE POINT before_salary_update;

-- After problematic changes
FLASHBACK TABLE employees TO RESTORE POINT before_salary_update;

-- For data analysis
SELECT
  dept_id,
  COUNT(*) as current_count,
  (SELECT COUNT(*)
   FROM employees AS OF TIMESTAMP (SYSDATE - 30)
   WHERE department_id = e.department_id) as count_30_days_ago
FROM employees e
GROUP BY dept_id;

-- For compliance reporting
SELECT
  employee_id,
  versions_starttime as change_date,
  versions_operation as change_type,
  salary,
  LAG(salary) OVER (ORDER BY versions_starttime) as previous_salary
FROM employees
VERSIONS BETWEEN TIMESTAMP
  (TRUNC(SYSDATE, 'YEAR')) AND SYSDATE
WHERE employee_id = 100
  AND versions_operation IS NOT NULL;

🚨 Troubleshooting Common Issues

Error Resolution

ORA-01555: Snapshot Too Old

-- Increase undo retention
ALTER SYSTEM SET undo_retention = 7200; -- 2 hours

-- Add undo tablespace space
ALTER TABLESPACE undotbs1 ADD DATAFILE
'/u01/oracle/oradata/undo02.dbf' SIZE 1G AUTOEXTEND ON;

-- Enable retention guarantee
ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;

ORA-38301: Cannot Perform DDL/DML

-- Enable row movement for flashback table
ALTER TABLE employees ENABLE ROW MOVEMENT;

-- Check table constraints
SELECT constraint_name, status, validated
FROM user_constraints
WHERE table_name = 'EMPLOYEES';

Flashback Database Issues

-- Check flashback database status
SELECT flashback_on, log_mode FROM v$database;

-- Verify flash recovery area
SELECT * FROM v$recovery_file_dest;

-- Check for space issues
SELECT
  space_limit / 1024 / 1024 as limit_mb,
  space_used / 1024 / 1024 as used_mb,
  space_reclaimable / 1024 / 1024 as reclaimable_mb,
  number_of_files
FROM v$recovery_file_dest;

Oracle Flashback Technologies provide powerful capabilities for data protection and recovery. When properly configured and managed, they significantly reduce recovery time objectives (RTO) and provide comprehensive audit trails for compliance and forensic analysis.

📖 Related Topics

• RMAN Backup and Recovery - Traditional backup methods
• Data Guard Configuration - Disaster recovery
• Oracle Architecture - Understanding undo and recovery
• Performance Monitoring - System performance analysis