This morning I got a question that - Does archivelog deletion (manually or RMAN) records entries to the database alert log?
Well, this was asked in an interview to him.
Answer is "NO" - until you use OMF/FRA and configured the ARCHIVE DELETION POLICY in the RMAN.
Let me explain step by step, how archivelogs get deleted automatically when we have the "archivelog deletion policy" configured in the RMAN and archive deletion details gets recorded in the database alert log.
1 - First of all, Flash/Fast Recovery area must be configured - in my case, it is already configured.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 0
Next log sequence to archive 0
Current log sequence 0
SQL>let's see the current usage of recovery area - it is 82.47% used as of now.
SQL> select * from v$recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE .14 0 1 0
REDO LOG 0 0 0 0
ARCHIVED LOG 82.47 81 376 0
BACKUP PIECE 0 0 0 0
IMAGE COPY 0 0 0 0
FLASHBACK LOG 0 0 0 0
FOREIGN ARCHIVED LOG 0 0 0 0
AUXILIARY DATAFILE COPY 0 0 0 02 - I have the the ARCHIVELOG DELETION POLICY set to delete archivelogs from the flash recovery area once that is applied on the standby database.
Remember, RMAN deletes the archives automatically once there is space pressure on the FRA, for example: when it crosses the 85% used percentage of recovery area(but not necessarily that deletion triggers immediately when it crosses 85% usage)
RMAN> show archivelog deletion policy;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name MAXMP76AD are:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
RMAN>Since flash recovery area usage is currently at 82.47%, i.e. below the default warning threshold, so let's reduce the db_recovery_file_dest_size parameter value to lower so recovery area usage goes high and when it crosses 85% then RMAN assumes there is space pressure in the FRA and it started deleting the archive logs which are applied on the standby and - here in this case, it records the entry in the alert log for all the archives being deleted here.
SQL> show parameter recovery
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
db_recovery_file_dest string +RECOC1
db_recovery_file_dest_size big integer 700G
recovery_parallelism integer 0
SQL>I am reducing the db_recovery_file_dest_size parameter to 600GB from 700GB to create pressure on the FRA.
SQL> alter system set db_recovery_file_dest_size=600g sid='*';
System altered.
SQL> show parameter recovery
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
db_recovery_file_dest string +RECOC1
db_recovery_file_dest_size big integer 600G
recovery_parallelism integer 0
SQL>
SQL> select * from v$recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE .16 0 1 0
REDO LOG 0 0 0 0
ARCHIVED LOG 92.95 92.37 363 0
BACKUP PIECE 0 0 0 0
IMAGE COPY 0 0 0 0
FLASHBACK LOG 0 0 0 0
FOREIGN ARCHIVED LOG 0 0 0 0
AUXILIARY DATAFILE COPY 0 0 0 0
8 rows selected.Here you can see in the alert log that OMG manged archive log deletion logs recorded in the database alert log.
Wed Aug 21 03:15:22 2019
Deleted Oracle managed file +RECOC/standdb/ARCHIVELOG/2019_08_20/thread_2_seq_736154.35917.1016826721
Deleted Oracle managed file +RECOC/standdb/ARCHIVELOG/2019_08_20/thread_2_seq_736155.94249.1016826805
Deleted Oracle managed file +RECOC/standdb/ARCHIVELOG/2019_08_20/thread_1_seq_744387.232116.1016826855
Deleted Oracle managed file +RECOC/standdb/ARCHIVELOG/2019_08_20/thread_1_seq_744388.1236.1016826899
Deleted Oracle managed file +RECOC/standdb/ARCHIVELOG/2019_08_20/thread_2_seq_736156.157025.1016826901
Deleted Oracle managed file +RECOC/standdb/ARCHIVELOG/2019_08_20/thread_2_seq_736157.82181.1016827067
Deleted Oracle managed file +RECOC/standdb/ARCHIVELOG/2019_08_20/thread_2_seq_736158.30084.1016827149
Deleted Oracle managed file +RECOC/standdb/ARCHIVELOG/2019_08_20/thread_1_seq_744389.10761.1016827153
Deleted Oracle managed file +RECOC/standdb/ARCHIVELOG/2019_08_20/thread_2_seq_736159.68978.1016827319
Deleted Oracle managed file +RECOC/standdb/ARCHIVELOG/2019_08_20/thread_2_seq_736160.49575.1016827399
Deleted Oracle managed file +RECOC/standdb/ARCHIVELOG/2019_08_20/thread_1_seq_744390.71236.1016827423
Deleted Oracle managed file +RECOC/standdb/ARCHIVELOG/2019_08_20/thread_1_seq_744391.93291.1016827513
Deleted Oracle managed file +RECOC/standdb/ARCHIVELOG/2019_08_20/thread_1_seq_744392.164222.1016827587
Deleted Oracle managed file +RECOC/standdb/ARCHIVELOG/2019_08_20/thread_2_seq_736161.150312.1016827589
Deleted Oracle managed file +RECOC/standdb/ARCHIVELOG/2019_08_20/thread_1_seq_744393.186625.1016827667
Deleted Oracle managed file +RECOC/standdb/ARCHIVELOG/2019_08_20/thread_1_seq_744394.229726.1016827751
Deleted Oracle managed file +RECOC/standdb/ARCHIVELOG/2019_08_20/thread_1_seq_744395.120898.1016827825
Deleted Oracle managed file +RECOC/standdb/ARCHIVELOG/2019_08_20/thread_2_seq_736162.184211.1016827825
Deleted Oracle managed file +RECOC/standdb/ARCHIVELOG/2019_08_20/thread_1_seq_744396.112965.1016827903
Deleted Oracle managed file +RECOC/standdb/ARCHIVELOG/2019_08_20/thread_2_seq_736163.46774.1016828035
Deleted Oracle managed file +RECOC/standdb/ARCHIVELOG/2019_08_20/thread_1_seq_744397.82871.1016828125
Deleted Oracle managed file +RECOC/standdb/ARCHIVELOG/2019_08_20/thread_1_seq_744398.69882.1016828209
Deleted Oracle managed file +RECOC/standdb/ARCHIVELOG/2019_08_20/thread_1_seq_744399.61173.1016828287
Deleted Oracle managed file +RECOC/standdb/ARCHIVELOG/2019_08_20/thread_2_seq_736164.126761.1016828287
Note: It is not necessary that RMAN would immediately start deleting the archive logs when it crosses 85% or so.
Twitter : @rajsoft8899
Linkedin : https://www.linkedin.com/in/raj-kumar-kushwaha-5a289219/
this is well explained. thank you
ReplyDelete