Delete archivelog all cannot completely delete archived logs?

Source: Internet
Author: User

Recently, due to the surge in archive logs, deleting archivelog all seems to be unable to clear all archived logs. Why?
[Python]
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; --> two nodes 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 flash back feature is not enabled for the database. That is to say, the flash back feature is not enabled even though the flash back zone is specified.
--> Correspondingly, when the archived logs are full of the entire flash back area, the flash back area space will not be reused.
FLASHBACK_ON
------------------
NO
 
2. View and clear existing archived log files
Oracle @ bo2dbp: ~> Export ORACLE_SID = + ASM1
Oracle @ bo2dbp: ~> Asmcmd
ASMCMD> cd + REV/GOBO4/ARCHIVELOG
ASMCMD> ls
2012_10_08/
....
Arch_7951920.20.20.10.arc
Arch_7951920.00000000100.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,200 5, Oracle. All rights reserved.
 
Connected to target database: GOBO4 (DBID = 921286879)
 
# Use rman backup archivelog 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_7951920.1_139.arc recid = 214 stamp = 797450261
Archive log filename = + REV/gobo4/archivelog/arch_7951920.20.140.arc recid = 215 stamp = 797450292
Archive log filename = + REV/gobo4/archivelog/arch_7951920.20.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_795191_1_2_141.arc recid = 427 stamp = 800547491
Archive log filename = + REV/gobo4/archivelog/arch_795191_1_2_142.arc recid = 429 stamp = 800549193
Archive log filename = + REV/gobo4/archivelog/arch_795191_1_2_143.arc recid = 433 stamp = 800578944
Archive log filename = + REV/gobo4/archivelog/arch_795191_1_2_144.arc recid = 437 stamp = 800641679
Finished backup at 29-NOV-12
 
# Check again that there are still many archived log files, all of which were created before January 1, 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_7951920.00000000100.arc
Arch_7951920.00000000101.arc
Arch_7951920.00000000102.arc
............
 
# Delete the log file again and run a command to delete all the archivelog files directly. The recently added archivelog file is 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_7951920.20.294.arc
Deleted archive log
Archive log filename = + REV/gobo4/archivelog/arch_7951920.20.294.arc recid = 453 stamp = 800662185
Deleted 1 objects www.2cto.com
 
# Why is the output result that only one archived log is deleted?
# Here is an analysis of delete noprompt archivelog all and the delete input used to back up archived logs.
# Review the Oracle control file and Oracle RMAN backup and recovery principles.
# We know that the Oracle control file records a lot of information about the database name, id, and creation timestamp.... Of course, there are also indispensable archive information and backup information.
# If you do not know what the control file has? For more information, see the Oracle control file. A link is provided at the end of the article.
 
# Second, all the information recovered by Oracle RMAN backup depends on two items, either a control file or a directory ).
# Because all the backup and recovery information will be stored in these two locations based on the backup method.
# Of course, for any operations on the backup sets, image copies, archiving logs, and other objects that can be backed up in these two items, we will first refer to the recorded information of these objects.
# Second, update the recorded object when it changes.
 
3. The reason why deep analysis cannot be cleared
# Let's take a look at gv $ archived_log. If you use v $ archived_log for a single instance
# The following query shows that two new archive logs are generated, one from the first instance and the other from the second instance.
SQL> select name, status, count (*) from gv $ archived_log group by name, status;
 
Name s count (*)
-------------------------------------------------------------
D 444
+ REV/gobo4/archivelog/arch_7951920.20.295.arc A 2
+ REV/gobo4/archivelog/arch_795192131_2_150.arc A 2
 
# From the preceding query, we can see that there are only two archived logs on the current two nodes, and the other 444 nodes have NULL names.
# Check the description of the NAME column in view v $ archived_log.
# 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 preceding section indicates that the current log files are manually cleared or deleted by the delete input option of rman.
# The "D" field in the "status" column also indicates that these empty archive logs have been Deleted. That is to say, 444 archive logs have been Deleted.
 
# Try to delete archive logs again. Archive logs with the ending number of 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_295.arc
455 2 150 A 29-NOV-12 + REV/gobo4/archivelog/arch_795192131_2_150.arc
Deleted archive log
Archive log filename = + REV/gobo4/archivelog/arch_7951920.20.295.arc recid = 454 stamp = 800712037
Deleted archive log
Archive log filename = + REV/gobo4/archivelog/arch_795191_1_2_150.arc recid = 455 stamp = 800712038
Deleted 2 objects
 
# Querying the gv $ archived_log view indicates that all existing archivelog files have been deleted.
SQL> select name, status, count (*) from gv $ archived_log group by name, status;
 
Name s count (*)
-------------------------------------------------------------
D 448
# The archived log file we just deleted cannot be found in the asmcmd command.
ASMCMD> pwd
+ REV/GOBO4/ARCHIVELOG
ASMCMD> ls-l arch_7951920.20.295.arc
Asmcmd: entry 'arch _ 7951920.20.295.arc 'does not exist in directory' + REV/GOBO4/ARCHIVELOG /'
ASMCMD> ls-l arch_795192131_2_150.arc
Asmcmd: entry 'arch _ 795191_1_2_150.arc 'does not exist in directory' + REV/GOBO4/ARCHIVELOG /'
 
# Switch again on node
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_795191_1_296.arc 1
2 + REV/gobo4/archivelog/arch_795191_1_296.arc 1
1 223

-- The preceding query shows that the current archive log arch_7951920.1_296.arc has one based on Inst_id as 1, and one based on Inst_id as 2.
 
-- When you directly query v $ archived_log, there is only one current archive log. In fact, the arch_795194241_1_296.arc file is generated by the first instance.
-- The value 1 before the value 296 indicates that it is generated for the first instance.
 
SQL> select name from v $ archived_log where name = '+ REV/gobo4/archivelog/arch_7951920.1_296.arc ';
 
NAME
--------------------------------------------------
+ REV/gobo4/archivelog/arch_7951920.20.296.arc
 
# I personally think this should be used for recovery.
# When the RAC database is restored, no matter how many nodes are there, only the collection of all archived logs can fully express the changes of the database.
# At this time, you can view the archived log no matter which node you want to recover or from.
# The instance generation is determined by the '% t' redo thread number.
 
# Next let's take a look at the control file
 
SQL> select * from gv $ controlfile_record_section where type = 'archived ';
 
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
# The RECORDS_TOTAL column indicates the total number of storage resources allocated for the current TYPE, which is 224 on both instances.
# From the query results of the last log switch, we can see that there are 223 Deleted logs, and the newly added one is arch_795194241_1_296.arc. The total number of logs is 224.
# Where can I add another entry to the next log switch? Archive logs that have exceeded the default retention period are overwritten and reused.
# Who determines the retention time of the archived log stored in the control file? The control_file_record_keep_time parameter is 7 days by default.
# This means that the archive log and backup information seven 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 why 2012_10_23 and the previous logs are not deleted.
# Because the archive logs after 18:04:53 20121023 have been overwritten, you cannot clear the previous logs when using delete archivelog all.
# The delete archivelog all method in rman does not delete the archived log information in the control file, but sets the delete status in the control file,
# That is, the status column of the v $ archived_log view is 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
 
# How can this be done?
# Delete the file directly under the asmcmd command line. Rm-rf 2012_09_30/
# Mo urgent, Mo urgent, accidentally Deleted, I dizzy, ORA-00254/ORA-15173 Archive_log Directory On Asm Being Deleted in a waiting.

Summary
A. delete archivelog all will delete all archived logs without reservation (corresponding records are recorded in the control file)
B. the archived log information is recorded in the control file. the lifetime and total number of retainable logs are also limited by the control file creation and the control_file_record_keep_time parameter.
C. This method does not work for archived logs that have been overwritten in the control file. Using backup archivelog all delete input also does not work.
D. Note that there are some differences between delete input and delete all input in backup archivelog all. The former deletes only archived logs that have been backed up, and the latter deletes multiple archive locations.
All archived logs under are deleted.
E. View v $ archived_log or gv $ archived_log provides detailed information about archived logs.
F. We recommend that you back up and archive logs before deleting them. Note: The archivedlog folder cannot be empty under RAC + ASM. Otherwise, the entire folder, together with the upper-level empty directory, will be deleted.
Some reference articles involved in this article:
Oracle Control File)
Oracle archiving logs
Parameters: CONTROL_FILE_RECORD_KEEP_TIME and MAXLOGHISOTRY
Use ASMCMD to manage the ASM directory and files
ORA-00254/ORA-15173 Archive_log Directory On Asm Being Deleted
 

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.