delete archivelog all cannot clear archive log solution
- 2020-12-21 18:13:28
- OfStack
With the recent explosion of archived logs, delete archivelog all seems unable to clear all archived logs. What is the reason?
1. Demo environment
2. View and clear existing archive log files
3. In-depth analysis of the reasons why it cannot be removed
summary
a, delete archivelog all will delete all archived logs without reservation (as recorded in the control file)
The information for b, archived logs is recorded in the control file, and its lifetime and the total amount that can be retained are also limited by the creation of the control file and the parameter control_file_record_keep_time
This does not work for archived logs that have been overwritten in the control file, nor does using backup archivelog all delete input
Note that there is some difference between delete input and delete all input when backup archivelog all. The former will delete only the archive logs that have been backed up, while the latter will delete all the archive logs under multiple archive locations.
e, View v$archived_log or gv$archived_log provide details about archived logs
It is recommended to back up the archive log before deleting it. Note that RAC+ASM must not make the archivedlog folder empty, otherwise the entire folder will be deleted along with the parent empty directory
1. Demo environment
SQL> select * from v$version where rownum<2;
BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.3.0 - 64bit Production
SQL> select inst_id,instance_name from gv$instance; --> The two nodes RAC
INST_ID INSTANCE_NAME
---------- ----------------
1 GOBO4A
2 GOBO4B
SQL> show parameter db_recovery -->+REV, Using the ASM storage
NAME TYPE VALUE
------------------------------------ ----------- -------------
db_recovery_file_dest string +REV
db_recovery_file_dest_size big integer 1G
SQL> select flashback_on from v$database; --> The database does not have the flashback feature on, that is, the flashback feature is not enabled even though a flashback area is specified
--> Accordingly, the flashback space is not reused when the archived log fills the entire flashback space
FLASHBACK_ON
------------------
NO
2. View and clear existing archive log files
oracle@bo2dbp:~> export ORACLE_SID=+ASM1
oracle@bo2dbp:~> asmcmd
ASMCMD> cd +REV/GOBO4/ARCHIVELOG
ASMCMD> ls
2012_10_08/
....
arch_795194241_1_10.arc
arch_795194241_1_100.arc
....
oracle@bo2dbp:~> export ORACLE_SID=GOBO4A
oracle@bo2dbp:~> rman target /
Recovery Manager: Release 10.2.0.3.0 - Production on Thu Nov 29 16:23:15 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: GOBO4 (DBID=921286879)
# Now by using rman backup archivelog Method to delete all archived log files
RMAN> backup format '/install_source/rman_bak/arch_%d_%U'
2> archivelog all delete input;
Starting backup at 29-NOV-12
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=1058 instance=GOBO4A devtype=DISK
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=139 recid=214 stamp=797450261
input archive log thread=1 sequence=140 recid=215 stamp=797450292
input archive log thread=1 sequence=141 recid=216 stamp=797450308
input archive log thread=1 sequence=142 recid=218 stamp=797450347
input archive log thread=1 sequence=143 recid=219 stamp=797450372
input archive log thread=1 sequence=144 recid=220 stamp=797450409
channel ORA_DISK_1: starting piece 1 at 29-NOV-12
channel ORA_DISK_1: finished piece 1 at 29-NOV-12
piece handle=/install_source/rman_bak/arch_GOBO4_1dnrhkn4_1_1 tag=TAG20121129T162806 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:15
channel ORA_DISK_1: deleting archive log(s)
archive log filename=+REV/gobo4/archivelog/arch_795194241_1_139.arc recid=214 stamp=797450261
archive log filename=+REV/gobo4/archivelog/arch_795194241_1_140.arc recid=215 stamp=797450292
archive log filename=+REV/gobo4/archivelog/arch_795194241_1_141.arc recid=216 stamp=797450308
........
piece handle=/install_source/rman_bak/arch_GOBO4_1hnrhli2_1_1 tag=TAG20121129T162806 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:09
channel ORA_DISK_1: deleting archive log(s)
archive log filename=+REV/gobo4/archivelog/arch_795194241_2_141.arc recid=427 stamp=800547491
archive log filename=+REV/gobo4/archivelog/arch_795194241_2_142.arc recid=429 stamp=800549193
archive log filename=+REV/gobo4/archivelog/arch_795194241_2_143.arc recid=433 stamp=800578944
archive log filename=+REV/gobo4/archivelog/arch_795194241_2_144.arc recid=437 stamp=800641679
Finished backup at 29-NOV-12
# Again, many archive log files still exist, and they are 10 month 23 The day before
ASMCMD> pwd
+REV/GOBO4/ARCHIVELOG
ASMCMD> ls
2012_09_30/
2012_10_09/
2012_10_10/
2012_10_11/
2012_10_12/
2012_10_13/
2012_10_14/
2012_10_15/
2012_10_16/
2012_10_17/
2012_10_18/
2012_10_22/
2012_10_23/
arch_795194241_1_100.arc
arch_795194241_1_101.arc
arch_795194241_1_102.arc
............
# Delete the log file again, to a more aggressive command, directly delete All of the archivelog , which was recently added 1 a archivelog Be deleted
RMAN> delete noprompt archivelog all;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=1081 instance=GOBO4A devtype=DISK
List of Archived Log Copies
Key Thrd Seq S Low Time Name
------- ---- ------- - --------- ----
453 1 294 A 29-NOV-12 +REV/gobo4/archivelog/arch_795194241_1_294.arc
deleted archive log
archive log filename=+REV/gobo4/archivelog/arch_795194241_1_294.arc recid=453 stamp=800662185
Deleted 1 objects
# The output above is only 1 An archived log was deleted. Why?
# This is our analysis 1 Under the delete noprompt archivelog all And when backing up archive logs delete input
# review 1 Under the Oracle Control file and Oracle RMAN The principle of backup recovery.
# We know that, Oracle The name of the database is recorded in the control file, id, Created timestamp ....1 A lot of information, but also indispensable archived information and backup information.
# If you don't know what the control file is? It is the reference :Oracle The control file is provided with a link at the end of the article.
# Second, Oracle RMAN The backup and restore of all information depends on two things, either the control file, or the restore directory (catalog) .
# Because all backup and restore information will be stored in these two locations depending on how the backup is performed.
# Of course, any operation on any of these objects, such as backup sets, mirrored copies, archived logs, etc., that can be backed up, will first refer to the information recorded by those objects.
# The second is to update when the recorded object changes.
3. In-depth analysis of the reasons why it cannot be removed
# Let's look at gv$archived_log If it is a single instance v$archived_log
# As you can see from the following query, two new archived logs are generated, 1 From the first 1 a instance To produce, 1 From the first 2 a instance Is generated.
SQL> select name,status,count(*) from gv$archived_log group by name,status;
NAME S COUNT(*)
-------------------------------------------------- - ----------
D 444
+REV/gobo4/archivelog/arch_795194241_1_295.arc A 2
+REV/gobo4/archivelog/arch_795194241_2_150.arc A 2
# From the above query, the current two nodes have only archived logs 2 One, the rest 444 All the names NULL Value.
# Look at the view v$archived_log In the NAME Column explanation
# Archived log file name. If set to NULL, either the log file was cleared before it was archived or an RMAN backup command
# with the "delete input" option was executed to back up archivelog all (RMAN> backup archivelog all delete input;).
# The above paragraph indicates that the current log files have either been cleared manually or by rman the delete input Option cleared.
# The second status The column D The field also indicates that these archive logs with empty names have been accessed Deleted. That is to say there are 444 Five archive logs have been deleted.
# Try again to delete the archive log, mantissa is 295 and 150 The archive log was also deleted
RMAN> delete noprompt archivelog all;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=1081 instance=GOBO4A devtype=DISK
List of Archived Log Copies
Key Thrd Seq S Low Time Name
------- ---- ------- - --------- ----
454 1 295 A 29-NOV-12 +REV/gobo4/archivelog/arch_795194241_1_295.arc
455 2 150 A 29-NOV-12 +REV/gobo4/archivelog/arch_795194241_2_150.arc
deleted archive log
archive log filename=+REV/gobo4/archivelog/arch_795194241_1_295.arc recid=454 stamp=800712037
deleted archive log
archive log filename=+REV/gobo4/archivelog/arch_795194241_2_150.arc recid=455 stamp=800712038
Deleted 2 objects
# The query gv$archived_log View, indicating all existing archivelog They've been deleted
SQL> select name,status,count(*) from gv$archived_log group by name,status;
NAME S COUNT(*)
-------------------------------------------------- - ----------
D 448
# in asmcmd The archived log file we just deleted could not be found under the command
ASMCMD> pwd
+REV/GOBO4/ARCHIVELOG
ASMCMD> ls -l arch_795194241_1_295.arc
asmcmd: entry 'arch_795194241_1_295.arc' does not exist in directory '+REV/GOBO4/ARCHIVELOG/'
ASMCMD> ls -l arch_795194241_2_150.arc
asmcmd: entry 'arch_795194241_2_150.arc' does not exist in directory '+REV/GOBO4/ARCHIVELOG/'
# in A Switch again on the node 1 time
SQL> alter system switch logfile;
System altered.
SQL> select inst_id,name,count(*) from gv$archived_log group by inst_id,name;
INST_ID NAME COUNT(*)
---------- -------------------------------------------------- ----------
2 223
1 +REV/gobo4/archivelog/arch_795194241_1_296.arc 1
2 +REV/gobo4/archivelog/arch_795194241_1_296.arc 1
1 223
-- The above query can see the current 1 An archive log arch_795194241_1_296.arc Based on the Inst_id for 1 The have 1 Student: a, but based on Inst_id for 2 Also has a 1 a
-- And direct query v$archived_log Only when the 1 Two current archive logs, in fact arch_795194241_1_296.arc The file is governed by the 1 a instance .
-- digital 296 Before the 1 That is, it can be indicated as the control 1 a instance .
SQL> select name from v$archived_log where name='+REV/gobo4/archivelog/arch_795194241_1_296.arc';
NAME
--------------------------------------------------
+REV/gobo4/archivelog/arch_795194241_1_296.arc
# This is a place where I personally think this should be used for recovery purposes.
# RAC When the database is restored, only a collection of archived logs can adequately represent the database transition, regardless of the number of nodes.
# At this point, the archived logs are visible from whichever node you look at, or restore from, whichever node.
# And which one instance Produced by the '%t' Redo the thread number to determine.
# Now let's look at the control file
SQL> select * from gv$controlfile_record_section where type='ARCHIVED LOG';
INST_ID TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID
---------- ---------------------------- ----------- ------------- ------------ ----------- ---------- ----------
1 ARCHIVED LOG 584 224 224 149 148 456
2 ARCHIVED LOG 584 224 224 149 148 456
# RECORDS_TOTAL : Number of records allocated for the section
# column RECORDS_TOTAL Indicate as current TYPE The total number of stores that can be allocated in two instance Both for 224 article
# From the recent 1 The query results of the switch log show that the deleted ones are 223 Bar, new 1 Article for the arch_795194241_1_296.arc , the total number of bars is 224 The article.
# If the next log switch is added 1 Where do I put the bars? Archive logs that have exceeded the default retention period are overwritten, that is, reused.
# The user saves it in the control file ARCHIVED LOG Who decides the retention time of the part? Parameter control_file_record_keep_time The default for 7 day
# This means that 7 Archived logs and backup information from days ago may no longer exist in the control file
SQL> show parameter control_file_record_keep_time
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
SQL> select count (*) from v$archived_log;
COUNT(*)
----------
224
# Author : Robinson
# Blog : http://blog.csdn.net/robinson_0612
SQL> alter session set nls_date_format='yyyymmdd hh24:mi:ss';
Session altered.
# The following query shows exactly why 2012_10_23 And why were the previous logs not deleted
# because 20121023 18:04:53 The archive logs that follow have been overwritten, so use delete archivelog all When it is impossible to clear the log before, powerless a.
# for rman Under the delete archivelog all Mode does not delete the corresponding archived log information in the control file, but is set in the control file delete State,
# namely v$archived_log The view of status As a deleted
SQL> select min (FIRST_TIME), min (COMPLETION_TIME), max (FIRST_TIME), max (COMPLETION_TIME) from
2 v$archived_log;
MIN(FIRST_TIME) MIN(COMPLETION_TI MAX(FIRST_TIME) MAX(COMPLETION_TI
----------------- ----------------- ----------------- -----------------
20121023 18:03:12 20121023 18:04:53 20121130 12:00:26 20121130 12:14:51
SQL> select min (FIRST_TIME), min (COMPLETION_TIME), max (FIRST_TIME), max (COMPLETION_TIME) from
2 gv$archived_log;
MIN(FIRST_TIME) MIN(COMPLETION_TI MAX(FIRST_TIME) MAX(COMPLETION_TI
----------------- ----------------- ----------------- -----------------
20121023 18:03:12 20121023 18:04:53 20121130 12:00:26 20121130 12:14:51
# So what's the best thing to do?
# That's right there asmcmd Delete from the command line. 1 Violences delete rm -rf 2012_09_30/
# Don't hurry, don't hurry, 1 Accidentally deleted, I was dizzy, ORA-00254/ORA-15173 Archive_log Directory On Asm Being Deleted Waiting.
summary
a, delete archivelog all will delete all archived logs without reservation (as recorded in the control file)
The information for b, archived logs is recorded in the control file, and its lifetime and the total amount that can be retained are also limited by the creation of the control file and the parameter control_file_record_keep_time
This does not work for archived logs that have been overwritten in the control file, nor does using backup archivelog all delete input
Note that there is some difference between delete input and delete all input when backup archivelog all. The former will delete only the archive logs that have been backed up, while the latter will delete all the archive logs under multiple archive locations.
e, View v$archived_log or gv$archived_log provide details about archived logs
It is recommended to back up the archive log before deleting it. Note that RAC+ASM must not make the archivedlog folder empty, otherwise the entire folder will be deleted along with the parent empty directory