Oracle Flashback Database

Oracle Flashback Database Version 11.2.0.3

Flashback Database Demo

An alternative strategy to the demo presented here is to use Recovery Manager

RMAN> FLASHBACK DATABASE TO SCN = <system_change_number>;

If you open the database in READ ONLY mode you can shut it down, reopen it, and flashback again.

Data Dictionary Objects

GV_$FLASHBACK_DATABASE_LOG	GV_$FLASHBACK_DATABASE_STAT	V_$FLASHBACK_DATABASE_LOGFILE
GV_$FLASHBACK_DATABASE_LOGFILE	V_$FLASHBACK_DATABASE_LOG	V_$FLASHBACK_DATABASE_STAT

Exceptions

Error Code	Reason
ORA-19804	Can not reclaim ___ bytes of disk space from ___ limit
ORA-19804	Limit exceeded for recovery files

Syntax 1: SCN

FLASHBACK [STANDBY] DATABASE [<database_name>]
TO [BEFORE] SCN <system_change_number>;

Syntax 2: TIMESTAMP

FLASHBACK [STANDBY] DATABASE [<database_name>]
TO [BEFORE] TIMESTMP <system_timestamp_value>;

Syntax 3: RESTORE POINT

FLASHBACK [STANDBY] DATABASE [<database_name>]
TO [BEFORE] RESTORE POINT <restore_point_name>;

Flashback Syntax Elements

OFF

ALTER DATABASE FLASHBACK OFF;

alter database flashback off;

ALTER DATABASE FLASHBACK ON;

alter database flashback on;

Set Retention Target

ALTER SYSTEM SET db_flashback_retention_target = <number_of_minutes>;

alter system set DB_FLASHBACK_RETENTION_TARGET = 2880;

Start flashback on a tablespace

ALTER TABLESPACE <tablespace_name> FLASHBACK ON;

alter tablespace example flashback on;

Stop flashback on a tablespace

ALTER TABLESPACE <tablespace_name> FLASHBACK OFF;

alter tablespace example flashback off;

Initialization Parameters

Setting the location of the flashback recovery area

db_recovery_file_dest=/app/oracle/product/flash_recovery_area

Setting the size of the flashback recovery area

-- 4GB
db_recovery_file_dest_size=4096000000

Setting the retention time for flashback files (in minutes)

-- 2 days
db_flashback_retention_target=2880

Demo

conn / as sysdba

SELECT *
FROM v$flash_recovery_area_usage;

SELECT flashback_on, log_mode
FROM v$database;

set linesize 121
col name format a30
col value format a30

SELECT name, value
FROM gv$parameter
WHERE name LIKE '%flashback%';

shutdown immediate;

startup mount exclusive;

alter database archivelog;

alter database flashback on;

alter database open;

SELECT flashback_on, log_mode
FROM v$database;

SELECT name, value
FROM gv$parameter
WHERE name LIKE '%flashback%';

-- 2 days
alter system set DB_FLASHBACK_RETENTION_TARGET=1440;

SELECT name, value
FROM gv$parameter
WHERE name LIKE '%flashback%';

SELECT estimated_flashback_size
FROM gv$flashback_database_log;

SELECT *
FROM v$flash_recovery_area_usage;

As SYS	As UWCLASS
conn / as sysdba SELECT current_scn FROM v$database; SELECT oldest_flashback_scn, oldest_flashback_time FROM gv$flashback_database_log; GRANT flashback any table TO uwclass;
	conn uwclass/uwclass CREATE TABLE t ( mycol VARCHAR2(20)) ROWDEPENDENCIES; INSERT INTO t VALUES ('ABC'); INSERT INTO t VALUES ('DEF'); COMMIT; CREATE RESTORE POINT bef_damage9; INSERT INTO t VALUES ('GHI'); COMMIT; SELECT ora_rowscn, mycol FROM t;
SELECT * FROM v$flash_recovery_area_usage; SHUTDOWN immediate; startup mount exclusive; -- be sure to substitute your SCN FLASHBACK DATABASE TO SCN 19513917; or FLASHBACK DATABASE TO RESTORE POINT bef_damage; /* FLASHBACK DATABASE TO TIMESTAMP (SYSDATE-1/24); FLASHBACK DATABASE TO TIMESTAMP timestamp'2002-11-05 14:00:00'; FLASHBACK DATABASE TO TIMESTAMP to_timestamp('2002-11-11 16:00:00', 'YYYY-MM-DD HH24:MI:SS'); */ -- this will fail alter database open; -- this will succeed alter database open resetlogs;
	SELECT ora_rowscn, mycol FROM t;
SELECT * FROM gv$flashback_database_stat; ALTER SYSTEM switch logfile; shutdown immediate; startup mount exclusive; ALTER DATABASE flashback off; ALTER DATABASE noarchivelog; ALTER DATABASE open; SELECT flashback_on, log_mode FROM v$database;
host RMAN target sys/pwd@orabase RMAN> crosscheck archivelog all; RMAN> delete archivelog all; RMAN> list archivelog all;

-- if out of disk space
ORA-16014: log 2 sequence# 4163 not archived, no available destinations
ORA-00312: online log 2 thread 1: 'c:\oracle\oradata\orabase\redo02.log'

-- what happens
The error ora-16014 is the real clue for this problem. Once the archive destination becomes full the location also becomes invalid. Oracle does not do a recheck to see if space has been made available.

-- then
shutdown abort;

-- clean up disk space: then
startup

alter system archive log all to '/oracle/flash_recovery_area/ORABASE/ARCHIVELOG';