Oracle如何删除归档日志文件？

  Oracle启用归档日志的状态下，会在指定的归档日志目录生成大量的日志文件，而且这些日志文件默认是不会定期清理。长时间运行下，会占用大量的存储空间，最终可能导致Oracle没有足够的空间维持启动状态。

  在Oracle的控制文件中记录着每一个归档日志文件得相关信息。当手动删除了归档日志的物理文件后，控制文件中依然记录着已删除的归档日志信息。所以当我们删除归档日志文件时，不仅需要删除物理文件，还要删除控制文件中对应的信息。

一、切换Oracle用户

[root@ea9446c43596 /]# su - oracle

二、查看归档日志路径

归档日志路径不一定相同，如果不清楚归档日志在哪，可以通过命令find / -name *.dbf查找

[oracle@ea9446c43596 ~]$ cd /home/oracle/app/oracle/oradata/archlogs/[oracle@ea9446c43596 archlogs]$ ls1_10_900206934.dbf1_12_900206934.dbf1_14_900206934.dbf1_5_900206934.dbf1_7_900206934.dbf1_9_900206934.dbf1_11_900206934.dbf1_13_900206934.dbf1_4_900206934.dbf 1_6_900206934.dbf1_8_900206934.dbf

三、方法一：删除归档日志物理文件

如果之前手动删除过物理文件，可以查看这个方法，将控制文件中归档日志记录删除

1 删除物理文件

删除物理文件：1_4_900206934.dbf

[oracle@ea9446c43596 archlogs]$ rm -f 1_4_900206934.dbf

2 查看控制文件

通过如下命令查看Oracle控制文件记录归档日志信息，发现"1_4_900206934.dbf"还存在

  • rman target /

    rman登录命令

  • list archivelog all;

    查看所有的归档日志

[oracle@ea9446c43596 archlogs]$ rman target /Recovery Manager: Release 11.2.0.1.0 - Production on Mon Dec 12 10:01:18 2022Copyright (c) 1982, 2009, Oracle and/or its affiliates.All rights reserved.connected to target database: HELOWIN (DBID=1384114315)RMAN> list archivelog all;using target database control file instead of recovery catalogList of Archived Log Copies for database with db_unique_name HELOWIN=====================================================================Key Thrd Seq S Low Time------- ---- ------- - ---------3 14 A 07-DEC-22Name: /home/oracle/app/oracle/oradata/archlogs/1_4_900206934.dbf4 15 A 07-DEC-22Name: /home/oracle/app/oracle/oradata/archlogs/1_5_900206934.dbf5 16 A 08-DEC-22Name: /home/oracle/app/oracle/oradata/archlogs/1_6_900206934.dbf6 17 A 09-DEC-22Name: /home/oracle/app/oracle/oradata/archlogs/1_7_900206934.dbf7 18 A 09-DEC-22Name: /home/oracle/app/oracle/oradata/archlogs/1_8_900206934.dbf8 19 A 10-DEC-22Name: /home/oracle/app/oracle/oradata/archlogs/1_9_900206934.dbf9 110A 10-DEC-22Name: /home/oracle/app/oracle/oradata/archlogs/1_10_900206934.dbf10111A 10-DEC-22Name: /home/oracle/app/oracle/oradata/archlogs/1_11_900206934.dbf11112A 11-DEC-22Name: /home/oracle/app/oracle/oradata/archlogs/1_12_900206934.dbf12113A 11-DEC-22Name: /home/oracle/app/oracle/oradata/archlogs/1_13_900206934.dbf13114A 11-DEC-22Name: /home/oracle/app/oracle/oradata/archlogs/1_14_900206934.dbf

3 对比检查归档日志物理文件和控制文件一致性

如果你之前已经删除过物理文件，并没有删除控制文件中的信息，请看如下操作：

  • crosscheck archivelog all;

    优先使用这个命令，核验比对物理文件和归档日志记录信息一致性

  • list expired archivelog all;

    crosscheck后，可以使用这个命令，查看有哪些过期的日志文件

RMAN> crosscheck archivelog all;allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=772 device type=DISKvalidation failed for archived logarchived log file name=/home/oracle/app/oracle/oradata/archlogs/1_4_900206934.dbf RECID=3 STAMP=1122847256validation succeeded for archived logarchived log file name=/home/oracle/app/oracle/oradata/archlogs/1_5_900206934.dbf RECID=4 STAMP=1122894018validation succeeded for archived logarchived log file name=/home/oracle/app/oracle/oradata/archlogs/1_6_900206934.dbf RECID=5 STAMP=1122948041validation succeeded for archived logarchived log file name=/home/oracle/app/oracle/oradata/archlogs/1_7_900206934.dbf RECID=6 STAMP=1122980424validation succeeded for archived logarchived log file name=/home/oracle/app/oracle/oradata/archlogs/1_8_900206934.dbf RECID=7 STAMP=1123029988validation succeeded for archived logarchived log file name=/home/oracle/app/oracle/oradata/archlogs/1_9_900206934.dbf RECID=8 STAMP=1123066818validation succeeded for archived logarchived log file name=/home/oracle/app/oracle/oradata/archlogs/1_10_900206934.dbf RECID=9 STAMP=1123095615validation succeeded for archived logarchived log file name=/home/oracle/app/oracle/oradata/archlogs/1_11_900206934.dbf RECID=10 STAMP=1123124827validation succeeded for archived logarchived log file name=/home/oracle/app/oracle/oradata/archlogs/1_12_900206934.dbf RECID=11 STAMP=1123153640validation succeeded for archived logarchived log file name=/home/oracle/app/oracle/oradata/archlogs/1_13_900206934.dbf RECID=12 STAMP=1123184937validation succeeded for archived logarchived log file name=/home/oracle/app/oracle/oradata/archlogs/1_14_900206934.dbf RECID=13 STAMP=1123211251Crosschecked 11 objectsRMAN> list expired archivelog all;List of Archived Log Copies for database with db_unique_name HELOWIN=====================================================================Key Thrd Seq S Low Time------- ---- ------- - ---------3 14 X 07-DEC-22Name: /home/oracle/app/oracle/oradata/archlogs/1_4_900206934.dbf

4 删除过期文件

  • delete expired archivelog all;

    删除过期日志文件，在执行命令后，会提示你将要删除哪些过期的日志文件

RMAN> delete expired archivelog all;released channel: ORA_DISK_1allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=772 device type=DISKList of Archived Log Copies for database with db_unique_name HELOWIN=====================================================================Key Thrd Seq S Low Time------- ---- ------- - ---------3 14 X 07-DEC-22Name: /home/oracle/app/oracle/oradata/archlogs/1_4_900206934.dbfDo you really want to delete the above objects (enter YES or NO)" />四、方法二：rman删除归档日志 
这种方法，可以将控制文件中记录信息和物理文件同步删除，更为方便。
 
1 查看控制文件记录
 
  •  
    rman target /
     
    登录rman
     
  •  
    list archivelog all;
     
    查看所有归档日志信息
     
 
[oracle@ea9446c43596 archlogs]$ rman target /Recovery Manager: Release 11.2.0.1.0 - Production on Mon Dec 12 10:14:25 2022Copyright (c) 1982, 2009, Oracle and/or its affiliates.All rights reserved.connected to target database: HELOWIN (DBID=1384114315)RMAN> list archivelog all;using target database control file instead of recovery catalogList of Archived Log Copies for database with db_unique_name HELOWIN=====================================================================Key Thrd Seq S Low Time------- ---- ------- - ---------4 15 A 07-DEC-22Name: /home/oracle/app/oracle/oradata/archlogs/1_5_900206934.dbf5 16 A 08-DEC-22Name: /home/oracle/app/oracle/oradata/archlogs/1_6_900206934.dbf6 17 A 09-DEC-22Name: /home/oracle/app/oracle/oradata/archlogs/1_7_900206934.dbf7 18 A 09-DEC-22Name: /home/oracle/app/oracle/oradata/archlogs/1_8_900206934.dbf8 19 A 10-DEC-22Name: /home/oracle/app/oracle/oradata/archlogs/1_9_900206934.dbf9 110A 10-DEC-22Name: /home/oracle/app/oracle/oradata/archlogs/1_10_900206934.dbf10111A 10-DEC-22Name: /home/oracle/app/oracle/oradata/archlogs/1_11_900206934.dbf11112A 11-DEC-22Name: /home/oracle/app/oracle/oradata/archlogs/1_12_900206934.dbf12113A 11-DEC-22Name: /home/oracle/app/oracle/oradata/archlogs/1_13_900206934.dbf13114A 11-DEC-22Name: /home/oracle/app/oracle/oradata/archlogs/1_14_900206934.dbf
2 删除指定日期前日志
  • delete archivelog all completed before 'SYSDATE - 3';
    删除3天前的归档日志，在删除控制文件记录的同时，也会将物理文件删除。
RMAN> delete archivelog all completed before 'SYSDATE - 3';allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=772 device type=DISKList of Archived Log Copies for database with db_unique_name HELOWIN=====================================================================Key Thrd Seq S Low Time------- ---- ------- - ---------4 15 A 07-DEC-22Name: /home/oracle/app/oracle/oradata/archlogs/1_5_900206934.dbf5 16 A 08-DEC-22Name: /home/oracle/app/oracle/oradata/archlogs/1_6_900206934.dbfDo you really want to delete the above objects (enter YES or NO)? yesdeleted archived logarchived log file name=/home/oracle/app/oracle/oradata/archlogs/1_5_900206934.dbf RECID=4 STAMP=1122894018deleted archived logarchived log file name=/home/oracle/app/oracle/oradata/archlogs/1_6_900206934.dbf RECID=5 STAMP=1122948041Deleted 2 objects
3 查看物理文件
在上一步中删除了1_5_900206934.dbf和1_6_900206934.dbf文件，观察物理文件列表后，可以看到物理文件也会被同步删除。
[oracle@ea9446c43596 archlogs]$ ls1_10_900206934.dbf1_12_900206934.dbf1_14_900206934.dbf1_8_900206934.dbf1_11_900206934.dbf1_13_900206934.dbf1_7_900206934.dbf 1_9_900206934.dbf
五、使用到的命令集合
  • 登录rman
    rman target /
  • 查看所有归档日志
    list archivelog all;
  • 对比控制文件、物理文件
    crosscheck archivelog all;
  • 查看过期归档日志
    list expired archivelog all;
  • 删除过期归档日志
    delete expired archivelog all;
  • 删除指定日期归档日志
    delete archivelog all completed before 'SYSDATE - n';
  • 退出rman
    exit;