| 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. |
| Dependent Objects |
| GV_$FLASHBACK_DATABASE_LOG |
V_$FLASHBACK_DATABASE_LOG |
| GV_$FLASHBACK_DATABASE_LOGFILE |
V_$FLASHBACK_DATABASE_LOGFILE |
| GV_$FLASHBACK_DATABASE_STAT |
V_$FLASHBACK_DATABASE_STAT |
|
| Exceptions |
| Exception # |
Description |
| 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;
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 |
SELECT current_scn
FROM v$database;
SELECT oldest_flashback_scn,
oldest_flashback_time
FROM gv$flashback_database_log;
GRANT flashback any table TO 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; |
|
| |
conn uwclass/uwclass
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'; |
