01 February, 2015

Database Flashback -- 1

A first post on Database Flashback.

Enabling Database Flashback in 11.2 non-RAC

SQL*Plus: Release 11.2.0.2.0 Production on Sun Feb 1 23:13:17 2015

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter user-name: / as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS>select version, status, database_status
  2  from v$instance;

VERSION           STATUS       DATABASE_STATUS
----------------- ------------ -----------------
11.2.0.2.0        OPEN         ACTIVE

SYS>select flashback_on, database_role   
  2  from v$database;

FLASHBACK_ON       DATABASE_ROLE
------------------ ----------------
NO                 PRIMARY

SYS>
SYS>show parameter db_recovery_file

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /home/oracle/app/oracle/flash_
                                                 recovery_area
db_recovery_file_dest_size           big integer 3852M
SYS>   
SYS>select * from v$flash_recovery_area_usage;

FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE                          0                         0               0
REDO LOG                              0                         0               0
ARCHIVED LOG                        .95                       .94               5
BACKUP PIECE                      28.88                       .12               5
IMAGE COPY                            0                         0               0
FLASHBACK LOG                         0                         0               0
FOREIGN ARCHIVED LOG                  0                         0               0

7 rows selected.

SYS>

So, the above output shows that the database is OPEN but Flashback is not enabled.
Let me enable Flashback now.
SYS>alter database flashback on;

Database altered.

SYS>select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

SYS>select * from v$flash_recovery_area_usage;

FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE                          0                         0               0
REDO LOG                              0                         0               0
ARCHIVED LOG                        .95                       .94               5
BACKUP PIECE                      28.88                       .12               5
IMAGE COPY                            0                         0               0
FLASHBACK LOG                       .41                         0               2
FOREIGN ARCHIVED LOG                  0                         0               0

7 rows selected.

SYS>

Immediately after enabling Flashback, Oracle shows usage of the FRA for Flashback Logs. Note : Although 11.2 allows you to enable Flashback in an OPEN Database, I would suggest doing so when the database is not active.

4 comments:

jay vardhan said...

Hi Hemant,

Thanks for the post. It is suggested not to enable flashback when DB is active, any analysis for the reason.

Regards,
Jay

Hemant K Chitale said...

Setting FLASHBACK ON when the database is OPEN is a new feature. I'd rather not attempt it on an active database.

Hemant

Anonymous said...

Hi Mr Hemant,

what the difference between flashback and "flashback archive"

Regards
Leon

Hemant K Chitale said...

There are many different types of "FLASHBACK". Flashback Query, Flashback Versions, Flashback Transaction, Flashback Table, Flashback Database. They are all *different* from Flashback Archive. All of these allow you to revert to or view a prior state of a table / transaction / database. They may rely on Undo or Flashback Logs (in the case of Flahback Database). Flashback Archive, on the other hand, actually copies data that is modified so your queries do not have to go to the undo but can query the copied data.
This particular blog post is about Flashback Database. Not using Undo. Not using Flashback Archive. But using Flashback Logs and ArchiveLogs.

See http://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#ADFNS1008