Change db_recovery_file_dest_size - Configure FRA

Change db_recovery_file_dest_size 
=================================
Intiacl checks

1. Select * from v$flash_recovery_area_usage;

2. show parameter db_recovery

3.  SELECT GROUP_NUMBER,NAME,TOTAL_MB,FREE_MB,USABLE_FILE_MB FROM V$ASM_DISKGROUP;


NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +FLASHDG
db_recovery_file_dest_size big integer 13000M


alter system set db_recovery_file_dest_size=1650G scope=both;
alter system set  db_recovery_file_dest = '+DATA01' SCOPE=BOTH;

Select Substr(Name,1,40)  name,
Space_Limit/1024/1024/1024  Space_Limit,
Space_Used/1024/1024/1024   Spce_Used,
Space_Reclaimable/1024/1024/1024  Space_Reclaimable,
Number_Of_Files   Number_files
from V$RECOVERY_FILE_DEST;

Final Checks:

4. Select * from v$flash_recovery_area_usage;

5. show parameter db_recovery

6.  SELECT GROUP_NUMBER,NAME,TOTAL_MB,FREE_MB,USABLE_FILE_MB FROM V$ASM_DISKGROUP;

Configuring FRA

Following are the three initialization parameters that should be defined in order to set up the flash recovery area:

• DB_RECOVERY_FILE_DEST_SIZE
• DB_RECOVERY_FILE_DEST
• DB_FLASHBACK_RETENTION_TARGET

DB_RECOVERY_FILE_DEST_SIZE specifies the total size of all files that can be stored in the Flash Recovery Area. The size of the flash recovery area should be large enough to hold a copy of all data files, all incremental backups, online redo logs, archived redo log not yet backed up on tape, control files, and control file auto backups.

 ALTER SYSTEM SET db_recovery_file_dest_size = 10g SCOPE = OTH;

DB_RECOVERY_FILE_DEST parameter is to specify the physical location where all the flash recovery files are to be stored. Oracle recommends that this be a separate location from the datafiles, control files, and redo logs.

 ALTER SYSTEM SET db_recovery_file_dest = '+DG1' SCOPE = BOTH;

If the database is using Automatic Storage Management (ASM) feature, then the shared disk area that ASM manages can be targeted for the Flashback Recovery Area.

 ALTER SYSTEM SET db_recovery_file_dest = '+DG1' SCOPE = BOTH;

The DB_RECOVERY_FILE_DEST_SIZE and DB_RECOVERY_FILE_DEST are defined to make the flash recovery area usable without shutting down and restarting the database instance i.e. these two parameters are dynamic.

 ALTER SYSTEM SET db_flashback_retention_target = 1440 SCOPE = BOTH;