Oracle flashback zone management

Errors in file/home/oracle/diag/rdbms/orarpt/trace/orarpt_mmon_22508.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 2147483648 bytes is 98.55% used, and has 31102976 remaining bytes available.
**************************************** ********************************
You have following choices to free up space from flash 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
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.
**************************************** ********************************

When the database is restarted today, you can view the alert Log and find the above error. Although the database can be started and run normally now, when db_recovery_file_dest_size is 100%,
The ORA-19809: limit exceeded for recovery files will be reported, and the database will be suspended for archiving failure.
 
View the information of the flash back area

Select * from v $ recovery_file_dest;
 
NAME/home/oracle/flash_recovery_area
SPACE_LIMIT 2147483648
SPACE_USED 2116380672
SPACE_RECLAIMABLE 0
NUMBER_OF_FILES 1

 
View alert alarms and oracle suggestions.

Select reason, object_type, suggested_action from dba_outstanding_alerts;
 
REASON db_recovery_file_dest_size byte (2147483648 bytes in total) already used 98.55%, 31102976 bytes available.
OBJECT_TYPE RECOVERY AREA
SUGGESTED_ACTION "you can choose one of the following operations to release space from the quick recovery zone:
1. consider changing the rman retention policy. If Data Guard is used,
Change the rman archivelog deletion policy.
2. Use RMAN
The backup recovery area command backs up files to third-level storage such as tapes.
3. Add disk space and increase the db_recovery_file_dest_size parameter to reflect
This new space.
4. Run the rman delete command to DELETE unnecessary files. If the operating system is used
Command to delete files, then use rman crosscheck and
Delete expired command. "
 
Here I only tried 3rd and 4 Methods
3rd modification methods to extend the flash back area

SQL> show parameter db_recovery_file_dest_size;
 
NAME TYPE VALUE
-----------------------------------------------------------------------------
Db_recovery_file_dest_size big integer 2G
 
SQL> alter system set db_recovery_file_dest_size = 4G scope = both;
 
The system has been changed.
 
SQL> show parameter db_recovery_file_dest_size;
 
NAME TYPE VALUE
-----------------------------------------------------------------------------
Db_recovery_file_dest_size big integer 4G

Now the flash back space has been expanded to 4 GB, that is, 2 GB is added.
 
4th modification methods: Clear the flash back area
Note: The file in the directory db_recovery_file_dest is deleted directly, and SPACE_USED will not be released.

Oracle @ linux-35: ~> Rman target/
 
Recovery Manager: Release 11.1.0.6.0-Production on Wed Oct 12 12:35:09 2011
 
Copyright (c) 1982,200 7, Oracle. All rights reserved.
 
Connected to target database: ORARPT (DBID = 2356255833)
 
RMAN> crosscheck archivelog all;
 
Using target database control file instead of recovery catalog
Allocated channel: ORA_DISK_1
Channel ORA_DISK_1: SID = 1138 device type = DISK
Validation failed for archived log
Archived log file name =/home/oracle/flash_recovery_area/ORARPT/archivelog/20151106_08/o1_mf_1_11_6yz1mw4l _. arc RECID = 2 STAMP = 753316072
Validation failed for archived log
Archived log file name =/home/oracle/product/11.1.0/db_1/dbs/arch1_12_750871897.dbf RECID = 3 STAMP = 753397594
Validation failed for archived log
Archived log file name =/home/oracle/product/11.1.0/db_1/dbs/arch1_13_750871897.dbf RECID = 4 STAMP = 753404604
Validation failed for archived log
Archived log file name =/home/oracle/product/11.1.0/db_1/dbs/arch1_14_750871897.dbf RECID = 5 STAMP = 753490060
Validation failed for archived log
Archived log file name =/home/oracle/product/11.1.0/db_1/dbs/arch1_15_750871897.dbf RECID = 6 STAMP = 753530695
Validation failed for archived log
Archived log file name =/home/oracle/product/11.1.0/db_1/dbs/arch1_16_750871897.dbf RECID = 7 STAMP = 753599698
Validation failed for archived log
Archived log file name =/home/oracle/product/11.1.0/db_1/dbs/arch1_17_750871897.dbf RECID = 8 STAMP = 753646377
Validation failed for archived log
Archived log file name =/home/oracle/product/11.1.0/db_1/dbs/arch1_18_750871897.dbf RECID = 9 STAMP = 753747854
Validation failed for archived log
Archived log file name =/home/oracle/product/11.1.0/db_1/dbs/arch1_19_750871897.dbf RECID = 10 STAMP = 753809369
Validation failed for archived log
......

 

RMAN> delete expired archivelog all;
 
Released channel: ORA_DISK_1
Allocated channel: ORA_DISK_1
Channel ORA_DISK_1: SID = 1138 device type = DISK
List of Archived Log Copies for database with db_unique_name ORARPT
========================================================== ==================================
 
Key Thrd Seq S Low Time
----------------------------
2 1 11X07-JUN-11
Name:/home/oracle/flash_recovery_area/ORARPT/archivelog/201%06_08/o%mf_%11_6yz1mw4l _. arc
 
3 1 12X08-JUN-11
Name:/home/oracle/product/11.1.0/db_1/dbs/arch1_12_750871897.dbf
 
4 1 13X09-JUN-11
Name:/home/oracle/product/11.1.0/db_1/dbs/arch1_13_750871897.dbf
 
5 1 14X09-JUN-11
Name:/home/oracle/product/11.1.0/db_1/dbs/arch1_14_750871897.dbf
 
6 1 15X10-JUN-11
Name:/home/oracle/product/11.1.0/db_1/dbs/arch1_15_750871897.dbf
 
7 1 16X11-JUN-11
Name:/home/oracle/product/11.1.0/db_1/dbs/arch1_16_750871897.dbf
 
8 1 17X12-JUN-11
Name:/home/oracle/product/11.1.0/db_1/dbs/arch1_17_750871897.dbf
......
Do you really want to delete the above objects (enter YES or NO )? Yes
Deleted archived log
Archived log file name =/home/oracle/flash_recovery_area/ORARPT/archivelog/20151106_08/o1_mf_1_11_6yz1mw4l _. arc RECID = 2 STAMP = 753316072
Deleted archived log
Archived log file name =/home/oracle/product/11.1.0/db_1/dbs/arch1_12_750871897.dbf RECID = 3 STAMP = 753397594
Deleted archived log
Archived log file name =/home/oracle/product/11.1.0/db_1/dbs/arch1_13_750871897.dbf RECID = 4 STAMP = 753404604
Deleted archived log
Archived log file name =/home/oracle/product/11.1.0/db_1/dbs/arch1_14_750871897.dbf RECID = 5 STAMP = 753490060
Deleted archived log
Archived log file name =/home/oracle/product/11.1.0/db_1/dbs/arch1_15_750871897.dbf RECID = 6 STAMP = 753530695
Deleted archived log
Archived log file name =/home/oracle/product/11.1.0/db_1/dbs/arch1_16_750871897.dbf RECID = 7 STAMP = 753599698
Deleted archived log
Archived log file name =/home/oracle/product/11.1.0/db_1/dbs/arch1_17_750871897.dbf RECID = 8 STAMP = 753646377
Deleted archived log
Archived log file name =/home/oracle/product/11.1.0/db_1/dbs/arch1_18_750871897.dbf RECID = 9 STAMP = 753747854
 
 
RMAN> exit
 
 
Recovery Manager complete.

 
Check the information of the flash back area. SPACE_USED has been released.

Select * from v $ recovery_file_dest;
 
NAME/home/oracle/flash_recovery_area
SPACE_LIMIT 2147483648
SPACE_USED 0
SPACE_RECLAIMABLE 0
NUMBER_OF_FILES 0

By: Accumulation