in Oracle

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.