The flashback database feature allows you to reset the database to earlier point in time to resolve logical corruption. It is faster than point in time recovery and does not require backup and restore operations. Flashback database operations is available from RMAN or SQL with command: FLASHBACK DATABASE. To enable flashback database the FAST RECOVERY AREA must be configured and FLASHBACK RETENTION TARGET set. The DB_FLASHBACK_RETENTION_TARGET states how far in the past the database can be recovered to and Flashback must be setup in advance of need. (See article Enabling Flashback Database) The database periodically copies blocks from every datafile into recovery area and maintains logs of changes to those blocks. Additionally flashback logs, redo logs must be available for entire span of retention target. FLASHBACK DATABASE window is the time that is covered by flashback logs. FLASHBACK will delete oldest logs first if it runs low on space.
FLASHBACK has these limits:
- It can only undo changes to datafile made by Oracle Database, not media failure or deletion of file.
- It cannot undo shrink operations.
- If control file is recovered or recreated all flashback data is invalid.
- IF NOLOGGING is used, block corruption is likely if flashback is used.
Restore points can be used instead of time or SCN, Restore points are alias of scn or time.
Commands that support restore point are RECOVER DATABASE and FLASHBACK DATABASE in RMAN and FLASHBACK TABLE in SQL. Guaranteed restore points never age out of control file and must be explicitly dropped, it ensures that you can use flashback database to a single SCN even if flashback logging is not enabled. When enabled it enforces retention of flashback logs all the way to guaranteed SCN. This article will demonstrate database flashback in RMAN.
- Logon to server as the oracle software owner.
[larry@linux2 ~]$ su – oracle
Password:
Last login: Thu Jan 5 10:45:29 EST 2015 on pts/1
Enter database to use:
1 – ORCL
2 – CDB1
Option >
1
[oracle@linux2 ~]$
- Start RMAN and connect to your database.
[oracle@linux2 ~]$ rman
Recovery Manager: Release 12.1.0.2.0 – Production on Tue Jan 10 09:18:41 2015
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
RMAN> connect target /
connected to target database: ORCL (DBID=1457738340)
RMAN>
- Use timestamp_to_scn function to get SCN for 48 hours ago.
RMAN> select timestamp_to_scn(systimestamp-2) from dual;
TIMESTAMP_TO_SCN(SYSTIMESTAMP-2)
——————————–
4236799
RMAN>
- Shutdown the database with immediate option if open.
RMAN> shutdown immediate
using target database control file instead of recovery catalog
database closed
database dismounted
Oracle instance shut down
RMAN>
- Startup the database with mount.
RMAN> startup mount
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 549453824 bytes
Fixed Size 2926616 bytes
Variable Size 272631784 bytes
Database Buffers 268435456 bytes
Redo Buffers 5459968 bytes
RMAN>
- Execute the FLASHBACK DATABASE command with SCN retrieved above.
flashback database to scn 4236799;
RMAN> flashback database to scn 4236799;
Starting flashback at 10-JAN-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=22 device type=DISK
starting media recovery
archived log for thread 1 with sequence 2761 is already on disk as file /u04/oradata/ORCL/flash_recovery_area/ORCL/archivelog/2015_01_08/o1_mf_1_2761_d74og7tj_.arc
media recovery complete, elapsed time: 00:00:02
Finished flashback at 10-JAN-17
RMAN>
- Open the database with command ‘ALTER DATABASE OPEN READ ONLY;’
RMAN> alter database open read only;
Statement processed
RMAN>
- If not errors, shutdown the database with immediate option.
RMAN> shutdown immediate
database closed
database dismounted
Oracle instance shut down
RMAN>
- Startup the database with mount option.
RMAN> startup mount
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 549453824 bytes
Fixed Size 2926616 bytes
Variable Size 272631784 bytes
Database Buffers 268435456 bytes
Redo Buffers 5459968 bytes
RMAN>
- Open the database with resetlogs option: ‘ALTER DATABASE OPEN RESETLOGS;’
RMAN> ALTER DATABASE OPEN RESETLOGS;
Statement processed
RMAN>
- You should take a full backup at the end of any recovery.
- This completes flashing back a database in RMAN.
Larry Catt
OCP