How to determine flashback database progress

After issuing FLASHBACK DATABASE command, how should we monitor the progress of the flashback?

I could never figure out how much time flashback will take in Oracle 10g. There is no entry in v$session_longops and nothing logged in alert.log. Even v$datafile_header (checkpoint_change#,checkpoint_time) doesn’t help much.

But in 11g (I only checked this in R2) we can track this using v$session_longops

A small example of flashback

SQL> select name from v$restore_point;
NAME
--------------
RESTORE_POINT1
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

SQL> flashback database to restore point RESTORE_POINT1;
Flashback complete.

set pages 100
set line 200
column message format a50
SQL> select  sid,message from  v$session_longops where 
 sofar <>  totalwork
/
       SID MESSAGE                                            
---------- -------------------------------------------------- 
        98 Flashback Database: Flashback Data Applied : 50772 
            out of 174626 Megabytes done
       SID MESSAGE                                            
---------- -------------------------------------------------- 
        98 Flashback Database: Flashback Data Applied : 60862 
            out of 174626 Megabytes done

So in approximately 7 mins  10090 operations completed.  Remaining  113764 will take another ~ 79 mins

--------------- alert.log ------------------------------
Flashback Restore Complete
Flashback Media Recovery Start
 started logmerger process
Parallel Media Recovery started with 16 slaves
Flashback Media Recovery Log +ORARECO1/orcl/archivelog
/2011_06_27/thread_1_seq_11.4651.754938223
Wed Aug 03 00:17:26 2011
Incomplete Recovery applied until change 65580886 time 
06/27/2011 11:29:51
Flashback Media Recovery Complete
Completed: flashback database to restore point RESTORE_POINT1
Advertisements
This entry was posted in Oracle 11.2.0.x and tagged . Bookmark the permalink.

3 Responses to How to determine flashback database progress

  1. any way we can check the same in 10.2.0.3 ?

  2. ram says:

    that is awesome…thanks for the info

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s