What is the reason for the recent explosion in the archive log, with the delete archivelog all seemingly unable to erase all archived logs?
1. Demo Environment
Copy Code code as follows:
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; --> two node RAC
INST_ID instance_name
---------- ----------------
1 gobo4a
2 gobo4b
Sql> Show parameter Db_recovery-->+rev, using the ASM storage method
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 turn on the flashback feature, which means that the Flashback feature is not enabled, even though the flash back zone is specified
--> Accordingly, the flash-back space is not reused when the archive logs are filled with the entire flash-back area
flashback_on
------------------
NO
2, view and clear the existing archive log files
Copy Code code as follows:
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, +, Oracle. All rights reserved.
Connected to target Database:gobo4 (dbid=921286879)
#下面通过使用rman backup Archivelog Way 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
#再次查看依然有很多归档日志文件存在, and it's all before October 23.
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
............
#再次删除日志文件, a more ruthless command, direct delete all Archivelog, recently added a archivelog was 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 results of the above output only one archive log was deleted, why?
# This is our analysis of the delete noprompt Archivelog all and the delete input used to back up the archive log
# Review the principles of Oracle control files and the backup recovery of Oracle Rman.
# We know that the Oracle control file records the name of the database, the ID, the timestamp created .... A lot of information, and of course, there are an essential amount of archival information and backup information.
# What if you don't know what the control file is? That reference: Oracle control files, the article has a link to the tail.
# Second, all information on the backup recovery of Oracle Rman is dependent on two dongdong, either a control file or a recovery directory (catalog).
# Because all backup and recovery information is stored in both locations, depending on how the backup is to be.
# Of course, for the backup set, mirror copy, archive log, and so on all the objects that can be backed up, the information of the records of those objects will be referenced first.
# The second is to make the corresponding updates when the recorded objects are changed.
3, depth analysis can not clear the reason
Copy Code code as follows:
#先来看看gv $archived _log, if it is a single instance using V$archived_log
#从下面的查询可知, two new archive logs are generated, one from the first instance and one from the second instance.
Sql> Select Name,status,count (*) from the 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 query above, the current two nodes have only 2 archived logs, and the remaining 444 names are null values.
# Look at the explanation for the Name column in the View V$archived_log
# archived log file name. If set to NULL, either the log file is cleared before it is archived or an RMAN backup command
# with the ' delete input ' option is executed to back up Archivelog all (rman> backup archivelog all delete input;).
# The above statement indicates that the current log files are either manually cleared or purged by the Rman Delete input option.
# The D field of the Status column also indicates that the archived logs with empty names have been deleted. That is, 444 archived logs have been deleted.
# try to delete the archive log again, and the archive logs with the Mantissa 295 and 150 are 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 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
# query Gv$archived_log view, indicating that all existing archivelog have been deleted
Sql> Select Name,status,count (*) from the Gv$archived_log group by Name,status;
NAME S COUNT (*)
-------------------------------------------------- - ----------
D 448
# under the Asmcmd command, we can't find the archive log file we just deleted.
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/'
# Switch again on Node A
sql> alter system switch logfile;
System altered.
Sql> Select Inst_id,name,count (*) from the 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 query above can see that one of the current archive logs Arch_795194241_1_296.arc is based on inst_id 1 and 1, and inst_id 2 is also a
-While querying v$archived_log directly, there are only 1 current archive logs, and Arch_795194241_1_296.arc files are actually generated by the first instance.
--1 before the number 296 to indicate that the first instance was produced.
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
# about this place personally think this should be used for recovery purposes.
# in the case of a RAC database recovery, no matter how many fewer nodes, only a collection of all archived logs can describe the changes in the database.
# At this point, you can see the archive log regardless of which node you're looking at, or from which node you want to recover from.
# and exactly which instance is produced is judged by the '%t ' redo thread number.
#下面再来看看控制文件
Sql> SELECT * from gv$controlfile_record_section where type= ' archived LOG ';
inst_id TYPE record_size records_total records_used first_index last_index
---------- ---------------------------- ----------- ------------- ------------ ----------- ---------- ----------
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
# column Records_total indicates the total number of stores that are allocated for the current type, 224 on two instance
# from the last change of the log query results know that there are 223 deleted, the new one is ARCH_795194241_1_296.ARC, the total number of 224 bar.
# What if you add another one to the next log switch? Archived logs that have exceeded the default retention period are overwritten and reused.
# who will decide the retention time of the archived log part of the user in the control file, the parameter control_file_record_keep_time, the default is 7 days
# This means that 7 days ago the archive logs and backup information may not 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 previous logs have not been deleted
# Since the archive log after 20121023 18:04:53 has been overwritten, there is no way to erase the previous log when using the delete archivelog all.
# The Delete archivelog all method under Rman does not delete the corresponding archived log information in the control file, but sets the delete state in the control file.
# that is, the status of the V$archived_log view is listed as 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
# Since then, what's the good?
# then delete it directly under the Asmcmd command line. A crazy delete Rm-rf 2012_09_30/
# Don't worry, don't worry, accidentally deleted, I faint, ora-00254/ora-15173 archive_log Directory on the Asm being Deleted waiting.
Summary
A, delete archivelog all will delete all archived logs without reservation (records in the control file)
b, archived log information is recorded in the control file, its lifetime and the total number of reservations are also subject to the creation of control files and parameter Control_file_record_keep_time restrictions
c, for archived logs that have been overwritten in the control file, this does not work, and using Backup Archivelog All delete input also does not work
D, note that there are some differences between delete input and delete all input for backup archivelog all, which deletes only archived logs that have been backed up, while the latter deletes all archived logs in multiple archive locations.
E, view V$archived_log or Gv$archived_log provides detailed information about the archive log
F, it is recommended that you back up the archive log before you delete it. Note, rac+asm must not make the Archivedlog folder empty, otherwise, the entire folder along with the superior empty directory will be deleted
