Flashback Database

Flashback Database

The FLASHBACK DATABASE command is a fast alternative to performing an incomplete recovery. In order to flashback the database you must have SYSDBA privilege and the flash recovery area must have been prepared in advance.



If the database is in NOARCHIVELOG it must be switched to ARCHIVELOG mode.




-- initialization

SQL >alter system set db_recovery_file_dest_size = 20G; SQL >alter system set db_recovery_file_dest = '/home/oracle/FRA';  SQL > alter database archivelog SQL > alter database flashback on;

-- create restore point

SQL> CREATE RESTORE POINT before_upgrade GUARANTEE FLASHBACK DATABASE;

-- check

SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION# DI,GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE FROM V$RESTORE_POINT

check usage

SQL> select file_type, space_used*percent_space_used/100/1024/1024 used, space_reclaimable*percent_space_reclaimable/100/1024/1024 reclaimable, frau.number_of_files from v$recovery_file_dest rfd, v$flash_recovery_area_usage frau;

-- mount database and flash back

SQL> FLASHBACK DATABASE TO RESTORE POINT ‘BEFORE_UPGRADE’; SQL> ALTER DATABASE OPEN RESETLOGS;

-- drop flashback

SQL> drop restore point before_upgrade;

Flashback database - the RMAN way

1. create restore point
   
   

   RMAN> create restore point before_upgrade guarantee flashback database; Statement processed

2. list restore point
   
   

   RMAN> list restore point all; SCN              RSP Time  Type       Time      Name ---------------- --------- ---------- --------- ---- 2019135                    GUARANTEED 21-MAR-17 BEFORE_UPGRADE

3. flashback database to restore point
   
   

   RMAN> flashback database to restore point before_upgrade; Starting flashback at 21-MAR-17 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=22 device type=DISK starting media recovery media recovery complete, elapsed time: 00:00:07 Finished flashback at 21-MAR-17

4. drop restore point
   
   

   RMAN> drop restore point before_upgrade; Statement processed