Category: Recovery

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.

How to get SCN from database. Timestamp against SCN

In many cases we need to know SCN of database. Also we may need to know the previous SCN of the database suppose before 10 minutes ago. I will try to write we can can get these two and how is this correlated.


A)Determine current SCN of The database.

You can query from V$DATABASE or using GET_SYSTEM_CHANGE_NUMBER procedure of DBMS_FLASHBACK package.

1)SQL> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER() FROM DUAL;
DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER()
—————————————–
1053658

2)SQL> SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
———–
1053668
B)Determine current timestamp value.

SQL> SELECT SYSTIMESTAMP FROM DUAL;
SYSTIMESTAMP
—————————————————————————
14-MAY-08 11.00.08.374107 PM -04:00
C)Convert SCN to Timestamp.

SQL> SELECT SCN_TO_TIMESTAMP(1053639) FROM DUAL;
SCN_TO_TIMESTAMP(1053639)
—————————————————————————
14-MAY-08 10.52.15.000000000 PM
D)Convert Timestamp to SCN

SQL> SELECT TIMESTAMP_TO_SCN(’14-MAY-08 11.00.08.374107 PM’) FROM DUAL;
TIMESTAMP_TO_SCN(’14-MAY-0811.00.08.374107PM’)
———————————————-
1054516