ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
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] TIMESTAMP <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
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
