Setting the retention time for flashback files (in minutes)
-- 2 days db_flashback_retention_target=2880
Demo
conn sys@pdbdev 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=2880;
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 sys@pdbdev 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@pdbdev
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'2018-03-05 14:00:00';
FLASHBACK DATABASE
TO TIMESTAMP to_timestamp('2018-03-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