How to manually delete archived logs

Microsoft Windows XP [version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C: \ Documents ents and Settings \ RequieM> sqlplus "/as sysdba"

SQL * Plus: Release 9.2.0.7.0-Production on Monday July 16 23:54:37 2007

Copyright (c) 1982,200 2, Oracle Corporation. All rights reserved.

Already connected to the idle routine.

SQL/IDLE>Startup;
The ORACLE routine has been started.

Total System Global Area 101785284 bytes
Fixed Size 454340 bytes
Variable Size 75497472 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
The database has been loaded.
The database has been opened.
SQL/IDLE> @ glogin
SQL>Show parameter log_archive_dest

NAME TYPE VALUE
-----------------------------------------------------------------------------
Log_archive_dest string
Log_archive_dest_1 string LOCATION = F: \ oracle \ oradata \ MYDB \ archive
Log_archive_dest_10 string
Log_archive_dest_2 string
Log_archive_dest_3 string
Log_archive_dest_4 string
Log_archive_dest_5 string
Log_archive_dest_6 string
Log_archive_dest_7 string
Log_archive_dest_8 string
Log_archive_dest_9 string
Log_archive_dest_state_1 string enable
Log_archive_dest_state_10 string enable
Log_archive_dest_state_2 string enable
Log_archive_dest_state_3 string enable
Log_archive_dest_state_4 string enable
Log_archive_dest_state_5 string enable
Log_archive_dest_state_6 string enable
Log_archive_dest_state_7 string enable
Log_archive_dest_state_8 string enable
Log_archive_dest_state_9 string enable

SQL>Host dir F: \ oracle \ oradata \ MYDB \ archive

Volume in drive F is APPLICATION
Volume Serial Number is 684B-6ADC
Directory of F: \ ORACLE \ ORADATA \ MYDB \ ARCHIVE

. <DIR> 07-06-24 16: 55
.. <DIR> 07-06-24 16: 55
Listen 10 DBF 50688 07-06-28
Listen 11 DBF 180224 07-06-28
Listen 12 DBF 131584 07-06-29
Listen 13 DBF 122368 07-06-30
Listen 14 DBF 167936 07-07-01
1_3 DBF 91852288 07-06-25
1_4 DBF 60416 07-06-25
Listen 5 DBF 89600 07-06-26
Listen 6 DBF 99840 07-06-27
Listen 7 DBF 50176 07-06-27
Listen 8 DBF 53760 07-06-27
Listen 9 DBF 129024 07-06-28
14 file (s) 92987904 bytes
1023932928 bytes free

SQL>Host del F: \ oracle \ oradata \ MYDB \ archive \ mongo5.dbf
SQL>Host dir F: \ oracle \ oradata \ MYDB \ archive

Volume in drive F is APPLICATION
Volume Serial Number is 684B-6ADC
Directory of F: \ ORACLE \ ORADATA \ MYDB \ ARCHIVE

. <DIR> 07-06-24 16: 55
.. <DIR> 07-06-24 16: 55
Listen 10 DBF 50688 07-06-28
Listen 11 DBF 180224 07-06-28
Listen 12 DBF 131584 07-06-29
Listen 13 DBF 122368 07-06-30
Listen 14 DBF 167936 07-07-01
1_3 DBF 91852288 07-06-25
1_4 DBF 60416 07-06-25
Listen 6 DBF 99840 07-06-27
Listen 7 DBF 50176 07-06-27
Listen 8 DBF 53760 07-06-27
Listen 9 DBF 129024 07-06-28
13 file (s) 92898304 bytes
1023932928 bytes free

SQL>Exit

C: \ Documents ents and Settings \ RequieM>Rman target/nocatalog

Recovery MANAGER: Version 9.2.0.7.0-Production

Copyright (c) 1995,200 2, Oracle Corporation. All rights reserved.

Connect to the target database: MYDB (DBID = 2541111196)
Using the target database control file instead of restoring directory

RMAN>Backup archivelog all delete input;

Start backup in month-07
The current log is archived.
Allocated channel: ORA_DISK_1
Channel ORA_DISK_1: sid = 15 devtype = DISK
RMAN-00571: ========================================================== ==============================
RMAN-00569: ==================== error message stack follows ==========================
RMAN-00571: ========================================================== ==============================
RMAN-03002: failure of backup command at 07/17/2007 00:02:45
RMAN-06059: expected archived log not found, lost of archived log compromises re
Coverability
ORA-19625: Identifying file F: \ ORACLE \ ORADATA \ MYDB \ ARCHIVE \ login 5.dbf Error
ORA-27041: Unable to open file
OSD-04002 :???????
O/S-Error: (OS 2 )????????????????

RMAN>Exit

The recovery manager is complete.

C: \ Documents ents and Settings \ RequieM>Sqlplus "/as sysdba"

SQL * Plus: Release 9.2.0.7.0-Production on Tuesday July 17 00:11:35 2007

Copyright (c) 1982,200 2, Oracle Corporation. All rights reserved.

Connect:
Oracle9i Enterprise Edition Release 9.2.0.7.0-Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0-Production

SQL>Select name, sequence #, archived, deleted, status from v $ archived_log;

Name sequence # ARCHIVED DELETED STATUS
---------------------------------------------------------------------------------------------------------------
F: \ ORACLE \ ORADATA \ MYDB \ ARCHIVE \ Alibaba 3.dbf 3 YES NO
F: \ ORACLE \ ORADATA \ MYDB \ ARCHIVE \ ipv4.dbf 4 YES NO
F: \ ORACLE \ ORADATA \ MYDB \ ARCHIVE \ Alibaba 5.dbf 5 YES NO
F: \ ORACLE \ ORADATA \ MYDB \ ARCHIVE \ versions 6.dbf 6 YES NO
F: \ ORACLE \ ORADATA \ MYDB \ ARCHIVE \ release 7.dbf 7 YES NO
F: \ ORACLE \ ORADATA \ MYDB \ ARCHIVE \ 1_8.DBF 8 YES NO
F: \ ORACLE \ ORADATA \ MYDB \ ARCHIVE \ release 9.dbf 9 YES NO
F: \ ORACLE \ ORADATA \ MYDB \ ARCHIVE \ Alibaba 10.dbf 10 YES NO
F: \ ORACLE \ ORADATA \ MYDB \ ARCHIVE \ release 11.dbf 11 YES NO
F: \ ORACLE \ ORADATA \ MYDB \ ARCHIVE \ Alibaba 12.dbf 12 YES NO
F: \ ORACLE \ ORADATA \ MYDB \ ARCHIVE \ login 13.dbf 13 YES NO
F: \ ORACLE \ ORADATA \ MYDB \ ARCHIVE \ login 14.dbf 14 YES NO
F: \ ORACLE \ ORADATA \ MYDB \ ARCHIVE \ 15.15.dbf 15 YES NO

13 rows selected

SQL>Exit

From Oracle9i Enterprise Edition Release 9.2.0.7.0-Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0-Production disconnected

C: \ Documents ents and Settings \ RequieM>Rman target/nocatalog

Recovery MANAGER: Version 9.2.0.7.0-Production

Copyright (c) 1995,200 2, Oracle Corporation. All rights reserved.

Connect to the target database: MYDB (DBID = 2541111196)
Using the target database control file instead of restoring directory

RMAN>Crosscheck archivelog all;

Allocated channel: ORA_DISK_1
Channel ORA_DISK_1: sid = 18 devtype = DISK
An error occurred while verifying the archived logs.
ARCHIVE log file name = F: \ ORACLE \ ORADATA \ MYDB \ ARCHIVE \ logs 3.dbf Record ID = 1 timestamp = 6261619
41
An error occurred while verifying the archived logs.
ARCHIVE log file name = F: \ ORACLE \ ORADATA \ MYDB \ ARCHIVE \ mongo4.dbf Record ID = 2 timestamp = 6262164
73
The archived log is verified successfully.
ARCHIVE log file name = F: \ ORACLE \ ORADATA \ MYDB \ ARCHIVE \ logs 5.dbf Record ID = 3 timestamp = 6262915
00
An error occurred while verifying the archived logs.
ARCHIVE log file name = F: \ ORACLE \ ORADATA \ MYDB \ ARCHIVE \ records 6.dbf Record ID = 4 timestamp = 6263381
89
An error occurred while verifying the archived logs.
ARCHIVE log file name = F: \ ORACLE \ ORADATA \ MYDB \ ARCHIVE \ 1_7.DBF Record ID = 5 timestamps = 6263584
60
An error occurred while verifying the archived logs.
ARCHIVE log file name = F: \ ORACLE \ ORADATA \ MYDB \ ARCHIVE \ logs 8.dbf Record ID = 6 timestamp = 6263675
48
An error occurred while verifying the archived logs.
ARCHIVE log file name = F: \ ORACLE \ ORADATA \ MYDB \ ARCHIVE \ logs 9.dbf Record ID = 7 timestamp = 6264248
15
An error occurred while verifying the archived logs.
ARCHIVE log file name = F: \ ORACLE \ ORADATA \ MYDB \ ARCHIVE \ logs 10.dbf Record ID = 8 timestamps = 626440
874
An error occurred while verifying the archived logs.
ARCHIVE log file name = F: \ ORACLE \ ORADATA \ MYDB \ ARCHIVE \ dig 11.dbf Record ID = 9 timestamp = 626483
071
An error occurred while verifying the archived logs.
ARCHIVE log file name = F: \ ORACLE \ ORADATA \ MYDB \ ARCHIVE \ records 12.dbf Record ID = 10 timestamp = 62655
2999
An error occurred while verifying the archived logs.
ARCHIVE log file name = F: \ ORACLE \ ORADATA \ MYDB \ ARCHIVE \ logs 13.dbf Record ID = 11 timestamp = 62665
7288
An error occurred while verifying the archived logs.
ARCHIVE log file name = F: \ ORACLE \ ORADATA \ MYDB \ ARCHIVE \ logs 14.dbf Record ID = 12 timestamp = 62678
5441
An error occurred while verifying the archived logs.
ARCHIVE log file name = F: \ ORACLE \ ORADATA \ MYDB \ ARCHIVE \ logs 15.dbf Record ID = 13 timestamp = 62812
8164
13 objects that have been cross-checked

RMAN>List copy of archivelog all;

List of archived log copies
Keyword Thrd Seq S short time name
---------------------------------
1 1 3 A 24-6 month-07 F: \ ORACLE \ ORADATA \ MYDB \ ARCHIVE \ g03.dbf
2 1 4 A 25-6 month-07 F: \ ORACLE \ ORADATA \ MYDB \ ARCHIVE \ ipv4.dbf
3 1 5X25-6 month-07 F: \ ORACLE \ ORADATA \ MYDB \ ARCHIVE \ release 5.dbf
4 1 6 A 26-6 month-07 F: \ ORACLE \ ORADATA \ MYDB \ ARCHIVE \ release 6.dbf
5 A 27-6-07 F: \ ORACLE \ ORADATA \ MYDB \ ARCHIVE \ 2.167.dbf
6 1 8 A 27-6 month-07 F: \ ORACLE \ ORADATA \ MYDB \ ARCHIVE \ mongo8.dbf
7 1 9 A 27-6 month-07 F: \ ORACLE \ ORADATA \ MYDB \ ARCHIVE \ release 9.dbf
8 1 10 A 28-6 month-07 F: \ ORACLE \ ORADATA \ MYDB \ ARCHIVE \ 20.10.dbf
9 1 11 A 28-6 month-07 F: \ ORACLE \ ORADATA \ MYDB \ ARCHIVE \ Jun 11.dbf
10 1 12 A 28-6 month-07 F: \ ORACLE \ ORADATA \ MYDB \ ARCHIVE \ 201712.dbf
11 1 13 A 29-6 month-07 F: \ ORACLE \ ORADATA \ MYDB \ ARCHIVE \ login 13.dbf
12 1 14 A 30-6 months-07 F: \ ORACLE \ ORADATA \ MYDB \ ARCHIVE \ login 14.dbf
13 1 15 A 01-7 month-07 F: \ ORACLE \ ORADATA \ MYDB \ ARCHIVE \ 1_15.DBF

RMAN>Backup archivelog all delete input;

Start backup in month-07
The current log is archived.
Use channel ORA_DISK_1
Channel ORA_DISK_1: archiving log backup set is being started
Channel ORA_DISK_1: archiving logs in the specified backup set
Input archiving log thread = 1 sequence = 4 Record ID = 2 timestamp = 626216473
Input archiving log thread = 1 sequence = 6 Record ID = 4 timestamp = 626338189
Input archiving log thread = 1 sequence = 7 Record ID = 5 timestamp = 626358460
Input archiving log thread = 1 sequence = 8 Record ID = 6 timestamp = 626367548
Input archiving log thread = 1 sequence = 9 Record ID = 7 timestamp = 626424815
Input archiving log thread = 1 sequence = 10 records ID = 8 timestamps = 626440874
Input archiving log thread = 1 sequence = 11 Record ID = 9 timestamp = 626483071
Input archiving log thread = 1 sequence = 12 Record ID = 10 timestamp = 626552999
Input archiving log thread = 1 sequence = 13 Record ID = 11 timestamp = 626657288
Input archiving log thread = 1 sequence = 14 Record ID = 12 timestamp = 626785441
Input archiving log thread = 1 sequence = 15 records ID = 13 timestamp = 628128164
Input archiving log thread = 1 sequence = 16 record ID = 14 timestamp = 628129521
Channel ORA_DISK_1: Starting segment 1 from 17-7 to 07
Channel ORA_DISK_1: Completed segment 1 from 17-7 to 07
Segment handle = F: \ ORACLE \ ORA92 \ DATABASE \ 01IN0VNI_1_1 comment = NONE
Channel ORA_DISK_1: Backup set completed. elapsed time: 00: 00: 02
Channel ORA_DISK_1: deleting archived logs
ARCHIVE log file name = F: \ ORACLE \ ORADATA \ MYDB \ ARCHIVE \ mongo4.dbf Record ID = 2 timestamp = 6262164
73
ARCHIVE log file name = F: \ ORACLE \ ORADATA \ MYDB \ ARCHIVE \ records 6.dbf Record ID = 4 timestamp = 6263381
89
ARCHIVE log file name = F: \ ORACLE \ ORADATA \ MYDB \ ARCHIVE \ 1_7.DBF Record ID = 5 timestamps = 6263584
60
ARCHIVE log file name = F: \ ORACLE \ ORADATA \ MYDB \ ARCHIVE \ logs 8.dbf Record ID = 6 timestamp = 6263675
48
ARCHIVE log file name = F: \ ORACLE \ ORADATA \ MYDB \ ARCHIVE \ logs 9.dbf Record ID = 7 timestamp = 6264248
15
ARCHIVE log file name = F: \ ORACLE \ ORADATA \ MYDB \ ARCHIVE \ logs 10.dbf Record ID = 8 timestamps = 626440
874
ARCHIVE log file name = F: \ ORACLE \ ORADATA \ MYDB \ ARCHIVE \ dig 11.dbf Record ID = 9 timestamp = 626483
071
ARCHIVE log file name = F: \ ORACLE \ ORADATA \ MYDB \ ARCHIVE \ records 12.dbf Record ID = 10 timestamp = 62655
2999
ARCHIVE log file name = F: \ ORACLE \ ORADATA \ MYDB \ ARCHIVE \ logs 13.dbf Record ID = 11 timestamp = 62665
7288
ARCHIVE log file name = F: \ ORACLE \ ORADATA \ MYDB \ ARCHIVE \ logs 14.dbf Record ID = 12 timestamp = 62678
5441
ARCHIVE log file name = F: \ ORACLE \ ORADATA \ MYDB \ ARCHIVE \ logs 15.dbf Record ID = 13 timestamp = 62812
8164
ARCHIVE log file name = F: \ ORACLE \ ORADATA \ MYDB \ ARCHIVE \ records 16.dbf Record ID = 14 timestamp = 62812
9521
Backup completed in month-07-07

RMAN>Delete copy of archivelog sequence 5;

Released channel: ORA_DISK_1
Allocated channel: ORA_DISK_1
Channel ORA_DISK_1: sid = 18 devtype = DISK

List of archived log copies
Keyword Thrd Seq S short time name
---------------------------------
3 1 5X25-6 month-07 F: \ ORACLE \ ORADATA \ MYDB \ ARCHIVE \ release 5.dbf

Are you sure you want to delete the above objects (enter YES or NO )? Y
Deleted archived logs
ARCHIVE log file name = F: \ ORACLE \ ORADATA \ MYDB \ ARCHIVE \ logs 5.dbf Record ID = 3 timestamp = 6262915
00
1. The object has been deleted.

 

Supplement: list copy of archivelog all in rman; j is used to query select name, sequence #, archived, deleted, status from v $ archived_log; if you do not need to manually delete archivelog due to insufficient space in the future, you can use the delete copy of archivelog from sequence XXXX until squence XXX of rman to delete archivelog and release the space in time, you can write a corner of the detection space in the crontab, and use v $ archived_log to automatically generate the preceding command to delete archivelog and release the space in time. Then You made it too complicated. Simply add the following to the script, then the archive log files will be removed. no need to find the sequence # s at all:

Crosscheck archivelog all;
Delete noprompt expired backupset;
Delete noprompt obsolete;