12c, backup/recovery, flashback, RMAN

Oracle 12c – Steps to perform a Flashback Database Operation

Leave a comment

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:

  1. It can only undo changes to datafile made by Oracle Database, not media failure or deletion of file.
  2. It cannot undo shrink operations.
  3. If control file is recovered or recreated all flashback data is invalid.
  4. 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.

 

  1. 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 ~]$

 

  1. 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>

 

  1. 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>

 

  1. 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>

 

  1. 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>

 

  1. 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>

 

  1. Open the database with command ‘ALTER DATABASE OPEN READ ONLY;’

 

RMAN> alter database open read only;

Statement processed

RMAN>

 

  1. If not errors, shutdown the database with immediate option.

 

RMAN> shutdown immediate

database closed

database dismounted

Oracle instance shut down

RMAN>

 

  1. 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>

 

  1. Open the database with resetlogs option: ‘ALTER DATABASE OPEN RESETLOGS;’

 

RMAN> ALTER DATABASE OPEN RESETLOGS;

Statement processed

RMAN>

 

  1. You should take a full backup at the end of any recovery.
  2. This completes flashing back a database in RMAN.

 

Larry Catt

OCP

Leave a Reply