Delete archivelog all cannot delete archive logs completely Solution

Recently, due to the surge in archive logs, deleting archivelog all seems to be unable to clear all archived logs. Why?

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

# 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

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.

Oracle video tutorial follow: http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html