Automatic cleaning mechanism after full use of Fast Recovery Area space 04/06 Update SLTechnology News&Howtos

Automatic cleaning mechanism after full use of Fast Recovery Area space

2025-04-06 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)06/01 Report--

The following simulates the scene in which the FRA space is full, and then uses different processing methods to make FRA free up available space again, so as to experience how oracle cleans up automatically when FRA is full.

Prepare the data:

CONFIGURE RETENTION POLICY TO REDUNDANCY 3

Backup database

List backup of database by summary

RMAN > list backup of database summary

List of Backups

Key TY LV S Device Type Completion Time # Pieces # Copies Compressed Tag

23 B F A DISK 20150506 15:10:28 1 1 NO TAG20150506T150958

25 B F A DISK 20150506 15:11:29 11 NO TAG20150506T151059

27 B F A DISK 20150506 15:13:24 1 1 NO TAG20150506T151252

SYS@tstdb1-SQL > select * from v$recovery_area_usage

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES

CONTROL FILE 0 0 0

REDO LOG 0 0 0

ARCHIVED LOG 4.07 3.38 56

BACKUP PIECE 24.22 0 6

IMAGE COPY 0 0 0

FLASHBACK LOG. 78 0 2

FOREIGN ARCHIVED LOG 0 0 0

SYS@tstdb1-SQL > select * from v$recovery_file_dest

NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES

/ oradata06/fra 34359738368 9990663168 1162465280 64

Alter system set db_recovery_file_dest_size=10G scope=memory

Create table t0506 / 3 tablespace TS0422_1 as select * from dba_objects

Insert into t0506room3 select * from t0506room3;-execute several times

Commit

Create table t0506_tpl as select * from t0506 / 3

-cycle delete- > insert to generate Archivelog and fill FRA

Declare

Begin

While (true) loop

Delete t0506_3

Commit

Insert into t0506 / 3 select * from t0506_tpl

Commit

End loop

End

-FRA will soon reach 100% utilization:

SYS@tstdb1-SQL > select * from v$recovery_area_usage

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES

CONTROL FILE 0 0 0

REDO LOG 0 0 0

ARCHIVED LOG 13.01. 12 35

BACKUP PIECE 77.52 0 6

IMAGE COPY 0 0 0

FLASHBACK LOG 9.14 0 8

FOREIGN ARCHIVED LOG 0 0 0

-alert.log constantly prompts the output of FRA full information.

ARC3: Error 19809 Creating archivelog file to'/ oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_63_%u_.arc'

Errors in file / oracle/app/oracle/diag/rdbms/tstdb1/tstdb1/trace/tstdb1_arc0_42205562.trc:

ORA-19815: WARNING: db_recovery_file_dest_size of 10737418240 bytes is 100.005% 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. Test whether the backup exceeding the retention policy specification will be deleted when the space is full.

RMAN > list backup of database summary

List of Backups

Key TY LV S Device Type Completion Time # Pieces # Copies Compressed Tag

23 B F A DISK 20150506 15:10:28 1 1 NO TAG20150506T150958

25 B F A DISK 20150506 15:11:29 11 NO TAG20150506T151059

27 B F A DISK 20150506 15:13:24 1 1 NO TAG20150506T151252

RMAN > show RETENTION POLICY

RMAN configuration parameters for database with db_unique_name TSTDB1 are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 3

-change redundancy 3 to redundancy 2 to see if a version of backup will be deleted

CONFIGURE RETENTION POLICY TO REDUNDANCY 2

ARC3: Error 19809 Creating archivelog file to'/ oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_14_%u_.arc'

Deleted Oracle managed file / oradata06/fra/TSTDB1/archivelog/2015_05_02/o1_mf_1_3_1jQKKtsWK_.arc

Deleted Oracle managed file / oradata06/fra/TSTDB1/archivelog/2015_05_02/o1_mf_1_2_1jQKKtmNK_.arc

Deleted Oracle managed file / oradata06/fra/TSTDB1/archivelog/2015_05_02/o1_mf_1_1_1jQKKtU5p_.arc

Deleted Oracle managed file / oradata06/fra/TSTDB1/archivelog/2015_05_02/o1_mf_1_1_1jQIqfg-3_.arc

Deleted Oracle managed file / oradata06/fra/TSTDB1/archivelog/2015_05_02/o1_mf_1_2_1jQIqfl7C_.arc

Deleted Oracle managed file / oradata06/fra/TSTDB1/backupset/2015_05_06/o1_mf_nnndf_TAG20150506T150958_1jVEZMOAo_.bkp list backup of database summary

List of Backups

Key TY LV S Device Type Completion Time # Pieces # Copies Compressed Tag

25 B F A DISK 20150506 15:11:29 11 NO TAG20150506T151059

27 B F A DISK 20150506 15:13:24 1 1 NO TAG20150506T151252

From the above results, it is observed that the backupset of BS Key=23 has been cleaned up, but soon the archivelog generated by the above stored procedure takes up the FRA space again.

# 2. Test whether the backupset in the FRA will be deleted after backupset backup is moved to an area other than FRA.

-next, we will backup 25 of the remaining 25 and 27 to a path other than FRA to see if the 25 backups will be cleaned up after that.

Backup backupset 25 format'/ oradata06/vlib/%U'

Starting backup at 20150506 15:44:50

Using channel ORA_DISK_1

Channel ORA_DISK_1: input backup set: count=3, stamp=879001859, piece=1

Channel ORA_DISK_1: starting piece 1 at 20150506 15:44:51

Channel ORA_DISK_1: backup piece / oradata06/fra/TSTDB1/backupset/2015_05_06/o1_mf_nnndf_TAG20150506T151059_1jVEc_bTM_.bkp

Piece handle=/oradata06/vlib/03q69083_1_2 comment=NONE

Channel ORA_DISK_1: finished piece 1 at 20150506 15:45:26

Channel ORA_DISK_1: backup piece complete, elapsed time: 00:00:35

Finished backup at 20150506 15:45:26

Deleted Oracle managed file / oradata06/fra/TSTDB1/backupset/2015_05_06/o1_mf_ncsnf_TAG20150506T150958_1jVEbWU3n_.bkp list backup of database

List of Backup Sets

BS Key Type LV Size Device Type Elapsed Time Completion Time

25 Full 2.57G DISK 00:00:30 20150506 15:11:29

BP Key: 29 Status: AVAILABLE Compressed: NO Tag: TAG20150506T151059

Piece Name: / oradata06/vlib/03q69083_1_2 show RETENTION POLICY

RMAN configuration parameters for database with db_unique_name TSTDB1 are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 2

RMAN > report need backup; list backup of datafile 2 summary

List of Backups

Key TY LV S Device Type Completion Time # Pieces # Copies Compressed Tag

25 B F A DISK 20150506 15:11:29 11 NO TAG20150506T151059

27 B F A DISK 20150506 15:13:24 1 1 NO TAG20150506T151252

After a while, the FRA directory is full again. This time we try to back up the archivedlog of sequence 1 / 10 to the / oradata06/vlib/ directory to see if this part of the space can be freed.

# 3. Test whether the archivelog in the FRA will be deleted in the case of ARCHIVELOG DELETION POLICY TO NONE

Backup archivelog sequence between 1 and 10 format'/ oradata06/vlib/arc_%U'

Starting backup at 20150506 16:10:30

Using channel ORA_DISK_1

Channel ORA_DISK_1: starting archived log backup set

Channel ORA_DISK_1: specifying archived log (s) in backup set

Input archived log thread=1 sequence=1 RECID=38 STAMP=879000498

Input archived log thread=1 sequence=2 RECID=2 STAMP=878999036

Input archived log thread=1 sequence=3 RECID=57 STAMP=879002329

Input archived log thread=1 sequence=4 RECID=58 STAMP=879002333

Input archived log thread=1 sequence=5 RECID=59 STAMP=879002472

Input archived log thread=1 sequence=6 RECID=60 STAMP=879002478

Input archived log thread=1 sequence=7 RECID=61 STAMP=879002481

Input archived log thread=1 sequence=8 RECID=62 STAMP=879002818

Input archived log thread=1 sequence=9 RECID=63 STAMP=879002828

Input archived log thread=1 sequence=10 RECID=64 STAMP=879002837

Channel ORA_DISK_1: starting piece 1 at 20150506 16:10:30

Channel ORA_DISK_1: finished piece 1 at 20150506 16:10:45

Piece handle=/oradata06/vlib/arc_08q693nm_1_1 tag=TAG20150506T161030 comment=NONE

Channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15

Finished backup at 20150506 16:10:45

-archivelog of 1: 10 was observed in alert.log to be deleted

Deleted Oracle managed file / oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_1_1jVB-byQo_.arc

Deleted Oracle managed file / oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_2_1jVB-hUR3_.arc

Deleted Oracle managed file / oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_1_1jV0O3oPw_.arc

Deleted Oracle managed file / oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_3_1jVF2uK7X_.arc

Deleted Oracle managed file / oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_4_1jVF38mNH_.arc

Deleted Oracle managed file / oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_5_1jVFBN_2Y_.arc

Deleted Oracle managed file / oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_6_1jVFBlUyY_.arc

Deleted Oracle managed file / oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_7_1jVFB_ZRX_.arc

Deleted Oracle managed file / oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_8_1jVFW46iI_.arc

Deleted Oracle managed file / oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_9_1jVFWfjwo_.arc

Deleted Oracle managed file / oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_10_1jVFXC84I_.arc

-although our policy for archivelog is never deleted, archivelog will still be deleted when the FRA is full.

RMAN > show ARCHIVELOG DELETION POLICY

RMAN configuration parameters for database with db_unique_name TSTDB1 are:

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

Soon archivelog is full again, and this time we configure archivelog's delete policy to back up at least twice to disk.

# 4. When testing ARCHIVELOG DELETION POLICY TO backed up X times, under what conditions will the archivelog in FRA be deleted?

CONFIGURE ARCHIVELOG DELETION POLICY TO backed up 2 times to device type disk

RMAN > show ARCHIVELOG DELETION POLICY

RMAN configuration parameters for database with db_unique_name TSTDB1 are:

CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 2 TIMES TO DISK

-first back up the archivelog of sequence: 11: 20 to / oradata06/vlib/, to see if these archivelog will be deleted directly

Backup archivelog sequence between 11 and 20 format'/ oradata06/vlib/arc_%U'

Starting backup at 20150506 16:24:07

Using channel ORA_DISK_1

Channel ORA_DISK_1: starting archived log backup set

Channel ORA_DISK_1: specifying archived log (s) in backup set

Input archived log thread=1 sequence=11 RECID=65 STAMP=879002846

Input archived log thread=1 sequence=12 RECID=66 STAMP=879002855

Input archived log thread=1 sequence=13 RECID=68 STAMP=879003131

Input archived log thread=1 sequence=14 RECID=67 STAMP=879003130

Input archived log thread=1 sequence=15 RECID=69 STAMP=879003132

Input archived log thread=1 sequence=16 RECID=70 STAMP=879003141

Input archived log thread=1 sequence=17 RECID=71 STAMP=879003149

Input archived log thread=1 sequence=18 RECID=72 STAMP=879003153

Input archived log thread=1 sequence=19 RECID=73 STAMP=879003159

Input archived log thread=1 sequence=20 RECID=74 STAMP=879003168

Channel ORA_DISK_1: starting piece 1 at 20150506 16:24:08

Channel ORA_DISK_1: finished piece 1 at 20150506 16:24:23

Piece handle=/oradata06/vlib/arc_09q694h8_1_1 tag=TAG20150506T162408 comment=NONE

Channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15

Finished backup at 20150506 16:24:23

-after a backup, we do not see the deleted archivelog information of 11x20 in alert.log. Back it up again.

Backup archivelog sequence between 11 and 20 format'/ oradata06/vlib/arc_%U'

Starting backup at 20150506 16:25:41