How long will my flashback database command take?!!

Oracle 10g introduced the wonderful (and I mean wonderful) ability to “rewind” a database back to a point in time, rather than restoring a backup and rolling it forward in time using transaction logs. This technology is called flashback database, and googling it will provide far more useful information than I can in my small blog.

One limitation is that when you issue the flashback database command, you have literally no clue how long it will take to complete. It doesn’t show up in v$session_longops, and it only shows “control file sequential read” in v$session_event. I did find a way to come very close to estimating how long it has left to complete, though.

Basically, you compare the statistic value for “physical read total bytes” for the session and compare it to the size of the flashback restore point you created. When these equal, you are a minute or two from completion.

#!/home/oracle/local/bin/python
import cx_Oracle
import sys
import time

con = cx_Oracle.Connection("rman","*****","wcprod",cx_Oracle.SYSDBA)
cursor = con.cursor()

cursor.execute("select value from v$sesstat where sid = 1634 and statistic# = (select statistic# from v$statname where name = 'physical read total bytes')")
for row in cursor.fetchall():
  total=int(row[0])

cursor.execute("select to_char(storage_size) from v$restore_point")
for row in cursor.fetchall():
  print str(int(row[0])/1024/1024) + "mb flashback size, and " + str(total/1024/1024) + "mb read, for a difference of " + str((int(row[0]) - total) / 1024 / 1024) + "mb."