Flashback Database

User Rating: 5 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Active
 

 

Check if flashback restore point exists in the database

SQL> 
SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE='YES';

Delete restore point

SQL> drop restore point BEFORE_PATCH;

Create flashback restore point, RAC

SQL>
srvctl stop database -d ORCL
startup mount; alter database flashback on; create restore point BEFORE_PATCH guarantee flashback database; STARTUP MOUNT FORCE; shutdown immediate srvctl start database -d ORCL

Go to the restore point, RAC

If something wrong and you need forward back to the restore point

SQL> srvctl stop database -d ORCL

Open database using one of the RAC database node

SQL>
startup mount;
flashback database to restore point BEFORE_PATCH; startup mount force; shu immediate;

Estimated size

SQL> SELECT estimated_flashback_size, FROM V$FLASHBACK_DATABASE_LOG;

Value of the current flashback logs

SQL> SELECT flashback_size/1024/1024 as Size_Mb FROM V$FLASHBACK_DATABASE_LOG;

Check  database flashback status

SQL> select flashback_on from v$database;

Swiths on FLASH BACK, change size and path

SQL> 
shutdown immediate;
startup mount exclusive; alter database flashback on; alter system set db_recovery_file_dest_size="4G"; alter system set db_recovery_file_dest="/u03/oracle/oradata/orcl/backups"; alter database open; select flashback_on from v$database;

FlashBack is usefull, when we need to foуward back database or to see previous state of the objects in the database. As a result we have additional load on the server because there are many information must to be saved

UNDO_RETENTION

show parameter UNDO_RETENTION
alter system set UNDO_RETENTION = 1800;
alter tablespace UNDO RETENTION GUARANTEE;
show parameter UNDO_RETENTION

(when FLASHBACK if ON) Defines minimum time (sec) when we can cancel or see change in the database. The data are holding in the UNDO_TABLESPACE (we must have enough size in the tablespace) and rewrites when no free space left, giving us minimal value of UNDO_RETENTION

Not for LOB data

1800 is 30 minutes of UNDO_RETENTION

Newsletter

Enter your email to join our mailing list

Quick Contact