ORA-19815: WARNING: db_recovery_file_dest_size 100%

9 Jan

Errors in file /u01/app/oracle/diag/rdbms/ubsweb/ubsweb/trace/ubsweb_arc3_4115.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 4070572032 bytes is 100.00% used, and has 0 remaining bytes available.
************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
system command was used to delete files, then use RMAN CROSSCHECK and
DELETE EXPIRED commands.

1. open terminal A
2. sqlplus / as sysdba
3. startup mount;
sql> select  name, floor(space_limit / 1024 / 1024) “Size MB”,  ceil(space_used  / 1024 / 1024) “Used MB” from  v$recovery_file_dest;
NAME                                                                                Size MB    Used MB
——————————————————————————–    ———- ———-
/u01/app/oracle/flash_recovery_area                                                  3382        3382

sql> alter system set db_recovery_file_dest_size = 7764M scope = both;

sql> select  name, floor(space_limit / 1024 / 1024) “Size MB”,  ceil(space_used  / 1024 / 1024) “Used MB” from  v$recovery_file_dest;
NAME                                                                                Size MB    Used MB
——————————————————————————–    ———- ———-
/u01/app/oracle/flash_recovery_area                                                  7764        3382

4. open terminal B
5. open rman
$ rman
RMAN> CONNECT TARGET sys/ORACLE0
RMAN> delete noprompt ARCHIVELOG UNTIL TIME ‘SYSDATE-4’;
RMAN> DELETE noprompt BACKUP COMPLETED BEFORE ‘SYSDATE-2’ DEVICE TYPE DISK;
RMAN> EXIT

6. back terminal A
sql> select  name, floor(space_limit / 1024 / 1024) “Size MB”,  ceil(space_used  / 1024 / 1024) “Used MB” from  v$recovery_file_dest;
NAME                                                                                Size MB    Used MB
——————————————————————————–    ———- ———-
/u01/app/oracle/flash_recovery_area                                                  7764        184

7. alter database open;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: