Search My Oracle Blog

Custom Search

16 February, 2015

Database Flashback -- 3

Continuing my series on Oracle Database Flashback


As I pointed out in my previous post, the ability to flashback is NOT strictly specified by db_flashback_retention_target.  The actual scope may be greater than or even less than the target.

[oracle@localhost Hemant]$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.2.0 Production on Sun Feb 15 23:39:24 2015

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


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 sysdate from dual;        

SYSDATE
---------
15-FEB-15

SYS>show parameter db_flashback_retention_target 

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target        integer     1440
SYS>select * from v$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- --------- ---------------- -------------- ------------------------
            14573520 15-FEB-15             1440       24576000                        0

SYS>
SYS>select to_char(oldest_flashback_time,'DD-MON HH24:MI')     
  2  from v$flashback_database_log
  3  /

TO_CHAR(OLDEST_FLASHB
---------------------
15-FEB 23:31

SYS>

In my previous post, the OLDEST_FLASHBACK_TIME was a week ago. Now, it doesn't appear to be so !

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                      29.12                       .12               6
IMAGE COPY                            0                         0               0
FLASHBACK LOG                       .61                         0               3
FOREIGN ARCHIVED LOG                  0                         0               0

7 rows selected.

SYS>

After the blog of 08-Feb, my database had been SHUTDOWN. The instance was STARTED at 23:24 today and the database was OPENed at 23:25.

Sun Feb 08 23:08:21 2015
Shutting down instance (immediate)
....
....
Sun Feb 08 23:08:37 2015
ARCH shutting down
ARC0: Archival stopped
Thread 1 closed at log sequence 1
Successful close of redo thread 1
Completed: ALTER DATABASE CLOSE NORMAL
ALTER DATABASE DISMOUNT 
Completed: ALTER DATABASE DISMOUNT 
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled 
Sun Feb 08 23:08:38 2015
Stopping background process VKTM
Sun Feb 08 23:08:40 2015
Instance shutdown complete
....
....
Sun Feb 15 23:24:46 2015
Starting ORACLE instance (normal)
....
....
Sun Feb 15 23:25:33 2015
QMNC started with pid=34, OS id=2449
Completed: ALTER DATABASE OPEN

So, it seems that, this time, Oracle says I cannot Flashback to 08-Feb. Although, on 08-Feb, it did say that I could Flashback to 01-Feb. I strongly recommend periodically query V$FLASH_RECOVERY_AREA_USAGE and V$FLASHBACK_DATABASE_LOG.
I have seen DBAs only referring to the parameter db_flashback_retention_target without querying these views.
.
.
.

No comments:

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016