Remove unwanted files from FLASH_RECOVERY_AREA

Move backups from the flash recovery area to tertiary storage such as tape.

RMAN> BACKUP RECOVERY AREA;

Note : Flashback logs cannot be backed up outside the recovery area and so are not backed up by BACKUP RECOVERY AREA.

NOTE: Removing these files with an operating system utility will physically remove these files, but the database will still think the files exist and the problem of archival error may still occur. Use the following commands to synchronize the files with the database and use rman to remove these files.

RMAN>CROSSCHECK BACKUP;
RMAN>CROSSCHECK ARCHIVELOG ALL;

RMAN>Delete expired backup;
RMAN>Delete expired archivelog all;
RMAN>Delete force obsolete;

4. Make sure that your guaranteed restore points are necessary. If not, delete them

SQL>Drop restore point restore_point_name

5. If flashback logs are enabled then make sure you have enough space for all the flashback logs. If its not required then you can turn off flashback.

SQL> Alter database FLASHBACK OFF;

6. Review your backup retention policy and if required change the RMAN RETENTION POLICY

RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

Exceptions :

- If RMAN is not part of backup strategy and archivelogs are going to FRA then manual intervention required for deletion of archivelogs. Periodically purse old archivelogs

for example

RMAN> Delete archivelog all completed before 'SYSDATE-7';

- By default RMAN backup goes to FRA. While taking RMAN backup if backup location explictly specified to flash recovery area location then those backup pieces are not considered as part of FRA for auto managment.

- For Archivelogs backup to FRA use USE_DB_RECOVERY_FILE_DEST rather than giving explict path of FRA

SQL> alter system set log_archive_dest_10='LOCATION=USE_DB_RECOVERY_FILE_DEST' scope=both;

- Bug 4911954

Details:
V$RECOVERY_FILE_DEST SPACE_USED and NUMBER_OF_FILES values may be wrong.
eg: Number of files in V$FLASH_RECOVERY_AREA_USAGE and V$RECOVERY_FILE_DEST are different.
This problem can lead to recovery area files being deleted even though there is no space pressure.
Fixed in : 10.2.0.3 , 11.1.0.6


- Bug 5106952

Details:
Flashback logs are not reclaimed by flash recovery area when using guaranteed
restore point (after dropping guaranteed restore point) or when changing
db_flashback_retention_target to a lower value.


convert scn numbers

The scn number is very important. Whenever a change to the database is made (including the data dictionary) then this scn number is incremented. Oracle uses the scn number for backup / recovery purposes, flashback, recycle bin etc etc etc.

There are a few functions that can be used to convert scn numbers to dates and vice a versa, which may make life easier when working out what date these scn numbers refer to. These functions must look through control file as they will fail if the scn number is too old.

select dbms_flashback.get_system_change_number from dual;
select current_scn from v$database;

select scn_to_timestamp(to_char(timestamp_to_scn(sysdate),'99999999999')) from dual;
select to_char(timestamp_to_scn(sysdate),'99999999999') from dual

note:
These queries will convert the scn number to a timestamp and vice a versa.

recycle bin

The concept of the recycle bin is essentially the same as the recycle bin on the mac or windows.

if you drop a table, the table is not renamed and not dropped. You can query the recyclebin view to look at what objects are currently in the recycle bin. You can actually select from the table that was dropped, although it is quite an ungamely name. i.e.: Something like BIN$HGnc55/7rRPgQPeM/qQoRw==$0

You can restore the table by issuing:
flashback table "bla" to before drop;