Flashback Database

> flashback logs
> flash recovery area
> flashback retention target
db_recovery_file_dest
db_recovery_file_dest_size
db_flashback_retention_target
SQL> alter database flashback on
SQL> SELECT * FROM V$RECOVERY_FILE_DEST;
SQL> SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;
* What Do All 10g Flashback Features Rely on and what are their Limitations                  Note 435998.1
* Flashback Database          Note 249319.1
* Flashback Drop         Note 265253.1
* Flashback Table         Note 246421.1
* Flashback Versions Query     Note 270270.1
* Flashback Transaction Query     Note 270270.1, Note 317499.1
* (Flashback Query 9i)         Note.174425.1
How long will my flashback database take
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.”

In 11G now there is a direct way to identify using v$session_longops

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
Advertisements

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