RMAN : Freeing FRA Space in an Emergency

The flashback recovery area has run out of space. You see a message in the alert log similar to the following :

Can not open flashback thread because there is no more space in flash recovery area.

sql>alter database open;
alter database open
*
ERROR at line 1:
ORA-38760 : This database instance failed to turn on flashback database.

Solution :

1.Increase space:-

You can increase the size of the flashback area.

sql>alter system set db_recovery_file_dest_size=10G;

2.Remove Restore Points

The alternative to increasing the size of the flashback area is to remove some of the older restore points that you no longer need.

sql>col name format a25
sql>select name , storage_size from v$restore_points;
Name                              Storage_size
RP0                                207028224
RP1                                                0
RP2                                915701760
PRE_TEST1                                    0
POST_TEST1                                  0
GOOD_ONE                                   0
QA_GOLD                                      0
BRANCH_1                                    0
AFTER_BRANCH_2                        0
AFTER_BRANCH_3                        0

10 rows selected.

restore points RP0 and RP2 have torage associated with them.this is because guaranteed restore points. You should remove them to make some room in the flash recovery area.

sql>drop restore point rp2;

Restore point dropped.

sql>drop restore point rp0;

Restore poing rp0;

Restore point dropped.

you may be able to start the database.
3.Disable Flashback

If solutions 1 and 3 fail or not applicable, you may want to disable flashback in the database temporarily.

sql>shutdown immediate

Oracle instance shutdown.

sql>startup mount

Database mounted.

sql>alter database flashback off;
Database altered.

This will stop the flashback operations and will stop generating flashback logs. To free up some space , you may want to delete some more files such as archived redo logs, unneeded backups and so on.

$ rman target /

Recovery Manager: Release 10.2.0.1.0 – Production on Wed Dec 23 14:20:52 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: RIYADH (DBID=3624306176)

RMAN> delete noprompt archivelog all;

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=153 devtype=DISK

List of Archived Log Copies
Key     Thrd Seq     S Low Time  Name
——- —- ——- – ——— —-
1       1    2       A 28-OCT-09 D:\ORACLE\ARCHIVE_01\ARC00002_0701448453.001
2       1    2       A 28-OCT-09 D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\RI
YADH\ARCHIVELOG\2009_12_19\O1_MF_1_2_5LRWHYC7_.ARC
3       1    3       A 19-DEC-09 D:\ORACLE\ARCHIVE_01\ARC00003_0701448453.001
4       1    3       A 19-DEC-09 D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\RI
YADH\ARCHIVELOG\2009_12_20\O1_MF_1_3_5LVJOJ5C_.ARC
5       1    4       A 20-DEC-09 D:\ORACLE\ARCHIVE_01\ARC00004_0701448453.001
6       1    4       A 20-DEC-09 D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\RI
YADH\ARCHIVELOG\2009_12_23\O1_MF_1_4_5M3FN1N0_.ARC
7       1    5       A 23-DEC-09 D:\ORACLE\ARCHIVE_01\ARC00005_0701448453.001
deleted archive log
archive log filename=D:\ORACLE\ARCHIVE_01\ARC00002_0701448453.001 recid=1 stamp=
706008375
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\RIYADH\ARCHIVE
LOG\2009_12_19\O1_MF_1_2_5LRWHYC7_.ARC recid=2 stamp=706008375
deleted archive log
archive log filename=D:\ORACLE\ARCHIVE_01\ARC00003_0701448453.001 recid=3 stamp=
706094569
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\RIYADH\ARCHIVE
LOG\2009_12_20\O1_MF_1_3_5LVJOJ5C_.ARC recid=4 stamp=706094569
deleted archive log
archive log filename=D:\ORACLE\ARCHIVE_01\ARC00004_0701448453.001 recid=5 stamp=
706353596
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\RIYADH\ARCHIVE
LOG\2009_12_23\O1_MF_1_4_5M3FN1N0_.ARC recid=6 stamp=706353596
deleted archive log
archive log filename=D:\ORACLE\ARCHIVE_01\ARC00005_0701448453.001 recid=7 stamp=
706370138
Deleted 7 objects.

Similarly…

RMAN>delete noprompt backup of database;
RMAN>delete noprompt copy of database;

sql>alter database open;
Database altered.

The database is now fully functional , but without the flashback ability. if you want to reenable flashback later ,you can do. because you’ve cleared unneeded files, the flash recovery area is fully usable whenever you choose to againg enable flashback.

I asked Mr.Surachart about Restore Point…

Mohamed Says : What is Restore point?
Surachart Says : A restore point is a user defined name that can be substituted for an SCN or clock time when used in conjuction with flashback database, flashback table and RMAN.

Mohamed Says : If i am go to delete restore point means m what’s happen?

Surachart Says : If you delete restore point , flashback not guarantee to that time or scn.