SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-38760: This database instance failed to turn on flashback database
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
If the database has aborted earlier because of any flashback errors and you attempt to
start it, you get the following error:
You want to correct the problem, or at least shut the flashback down so that the normal
database operations can continue.
There are three solutions. Which to choose depends upon the nature of the emergency
and the resources you have at your disposal.
Solution 1 :
--------------
You can increase the size of the flashback area dynamically. To increase it to, say, 10GB, you
would issue the following:
SQL> alter system set db_recovery_file_dest_size = 10G;
Solution 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. The following is a query to list the restore points you currently have:
SQL> col name format a25
SQL> select name, storage_size from v$restore_point;
NAME STORAGE_SIZE
------------------------- ------------
RP0 207028224
RP1 0
RP2 915701760
PRE_TEST1 0
These results show that restore points RP0 and RP2 have storage associated with them.
This is because they are guaranteed restore points You should remove them to make some room in the flash recovery area. To remove a restore point, issue a drop restore point command:
SQL> drop restore point rp2;
Restore point dropped.
SQL> drop restore point rp0;
Restore point dropped.
Restore points are created by executing SQL statements such as the following:
SQL> create restore point rp1;
This statement creates a named point in time to which you can flash back the database,
through the SQL statement (provided, of course, that you have turned on the flashback for the
database). Once you have a restore point, you can rewind or flash back to that point in time
using a statement such as this:
SQL> flashback database to rp1;
There are two types of restore points—normal and guaranteed. The preceding example of
creating a restore point creates a normal one. You may be able to flash back to that point, provided
enough flashback logs are available. If the flashback logs are not available (perhaps
because the space in the flashback recovery area ran out and Oracle had to delete some flashback
logs to make room for the newer occupants), then your flashback operation will fail. The
solution—a guaranteed restore point. To create a guaranteed restore point, you will have to
specifically ask for the guarantee:
A guaranteed restore point stores information needed to flash back in a special way. When
space pressures in the flash recovery area force the database to remove the unneeded files,
flashback logs are the first to go, unless these are for a guaranteed restore point. The flashback
logs of the guaranteed restore points are stored even when the flash recovery area runs out of
space. The only way to reclaim the space is to drop the guaranteed restore point. Dropping the
guaranteed restore points frees up that space.
Dropping restore point should clear up space, and you may be able to start the database.
Solution 3: Disable Flashback
If solutions 1 and 2 fail or are not applicable, you may want to disable flashback in the database
temporarily. First shut down the database (if not down already):
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 67111156 bytes
Database Buffers 96468992 bytes
Redo Buffers 2973696 bytes
Database mounted.
Then disable flashback in the database:
SQL> alter database flashback off;
Database altered.
This will stop the flashback operations and will stop generating flashback logs. This
should reduce the space requirement on the flash recovery area. To free up some space, you
may want to delete some more files such as archived redo logs, unneeded backups, and so on.
alter database open
*
ERROR at line 1:
ORA-38760: This database instance failed to turn on flashback database
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
If the database has aborted earlier because of any flashback errors and you attempt to
start it, you get the following error:
You want to correct the problem, or at least shut the flashback down so that the normal
database operations can continue.
There are three solutions. Which to choose depends upon the nature of the emergency
and the resources you have at your disposal.
Solution 1 :
--------------
You can increase the size of the flashback area dynamically. To increase it to, say, 10GB, you
would issue the following:
SQL> alter system set db_recovery_file_dest_size = 10G;
Solution 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. The following is a query to list the restore points you currently have:
SQL> col name format a25
SQL> select name, storage_size from v$restore_point;
NAME STORAGE_SIZE
------------------------- ------------
RP0 207028224
RP1 0
RP2 915701760
PRE_TEST1 0
These results show that restore points RP0 and RP2 have storage associated with them.
This is because they are guaranteed restore points You should remove them to make some room in the flash recovery area. To remove a restore point, issue a drop restore point command:
SQL> drop restore point rp2;
Restore point dropped.
SQL> drop restore point rp0;
Restore point dropped.
Restore points are created by executing SQL statements such as the following:
SQL> create restore point rp1;
This statement creates a named point in time to which you can flash back the database,
through the SQL statement (provided, of course, that you have turned on the flashback for the
database). Once you have a restore point, you can rewind or flash back to that point in time
using a statement such as this:
SQL> flashback database to rp1;
There are two types of restore points—normal and guaranteed. The preceding example of
creating a restore point creates a normal one. You may be able to flash back to that point, provided
enough flashback logs are available. If the flashback logs are not available (perhaps
because the space in the flashback recovery area ran out and Oracle had to delete some flashback
logs to make room for the newer occupants), then your flashback operation will fail. The
solution—a guaranteed restore point. To create a guaranteed restore point, you will have to
specifically ask for the guarantee:
A guaranteed restore point stores information needed to flash back in a special way. When
space pressures in the flash recovery area force the database to remove the unneeded files,
flashback logs are the first to go, unless these are for a guaranteed restore point. The flashback
logs of the guaranteed restore points are stored even when the flash recovery area runs out of
space. The only way to reclaim the space is to drop the guaranteed restore point. Dropping the
guaranteed restore points frees up that space.
Dropping restore point should clear up space, and you may be able to start the database.
Solution 3: Disable Flashback
If solutions 1 and 2 fail or are not applicable, you may want to disable flashback in the database
temporarily. First shut down the database (if not down already):
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 67111156 bytes
Database Buffers 96468992 bytes
Redo Buffers 2973696 bytes
Database mounted.
Then disable flashback in the database:
SQL> alter database flashback off;
Database altered.
This will stop the flashback operations and will stop generating flashback logs. This
should reduce the space requirement on the flash recovery area. To free up some space, you
may want to delete some more files such as archived redo logs, unneeded backups, and so on.
