Oracle Flashback Database
Version 12.1.0.1

Flashback Database
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
An alternative strategy to the demo presented here is to use Recovery Manager (RMAN)

RMAN> FLASHBACK DATABASE TO SCN = <system_change_number>;

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

Note also at the bottom of the page of demo that uses Flashback Database without explicitly enabling flashback database logging.
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;
ON 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;

-- the following statement can be run while the database is open providing archive logging is enabled.
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 before_damage;

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 before_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';
 
Flashback Database w/o Flashback Logging Enabled
-- I want to thank Rich Harrison for the inspiration for this demo which I found in his November 2013 blog.

conn / as sysdba

SHUTDOWN IMMEDIATE;

STARTUP MOUNT EXCLUSIVE;

ALTER DATABASE ARCHIVELOG;

ALTER DATABASE OPEN;

SELECT log_mode, flashback_on
FROM v$database;
-- note flashback logging is not enabled

CREATE TABLE fbd (testcol date);

INSERT INTO fbd VALUES (SYSDATE);
COMMIT;

ALTER SYSTEM SWITCH LOGFILE;

-- take a look at the file system ... flashback logs have been created even though flashback was not enabled.
-- drop the archived redo log


SHUTDOWN IMMEDIATE;

STARTUP MOUNT EXCLUSIVE;

FLASHBACK DATABASE TO RESTORE POINT before_damage;

ALTER DATABASE OPEN RESETLOGS;

desc fbd
-- it is now gone as it was created after the guaranteed restore point was created

DROP RESTORE POINT before_damage;

Related Topics
Flashback Archive
Flashback Drop
Flashback Query
Flashback Table
Flashback Transaction Backout
Flashback Transaction Query
Flashback Version Query
Recycle Bin
Restore Points

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved