Category: flashback

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

Recovering Through the OPEN RESETLOGS Statement

Data Guard allows recovery on a physical standby database to continue after the primary database has been opened with the RESETLOGS option. When an ALTER DATABASE OPEN RESETLOGS statement is issued on the primary database, the incarnation of the database changes, creating a new branch of redo data.When a physical standby database receives a new branch of redo data, Redo Apply automatically takes the new branch of redo data. For physical standby databases, no manual intervention is required if the standby database did not apply redo data past the new resetlogs SCN (past the start of the new branch of redo data). The following table describes how to resynchronize the standby database with the primary database branch.

image

See Oracle Database Backup and Recovery Advanced User’s Guide for more information about database incarnations, recovering through an OPEN RESETLOGS operation, and
Flashback Database.

ref : http://docs.oracle.com/cd/B19306_01/server.102/b14239/toc.htm

ORA-19809: limit exceeded for recovery files

Surprisingly got this error after I started my home-database after a very long time.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-16038: log 3 sequence# 78 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 3 thread 1:
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\DM4\ONLINELOG\O1_MF_3_71BD88XD_.LOG'
ORA-00312: online log 3 thread 1:
'C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\DM4\ONLINELOG\O1_MF_3_71BD8B1F_.LOG

Cause : Seems like my flashback logs is giving the Issue.

select name,
floor(space_limit / 1024 / 1024) "Size MB",
ceil(space_used  / 1024 / 1024) "Used MB"
from v$recovery_file_dest
order by name;

NAME                                                            Size MB    Used MB
------------------------------------------------------------ ---------- ----------
C:\oracle\product\10.2.0\flash_recovery_area                       2048       2021

Quick fix :  Run the rman to backup the archivelogs
or alternately increase the space of db_recovery_file_dest_size

RMAN> run {
2> allocate channel ch1 device type disk;
3> backup archivelog all delete input;
4> release channel ch1;
5> }

Alternately :

SQL> alter system set db_recovery_file_dest_size=4050M scope=both;
System altered.