oracle 备库归档日志某一天突发暴增到2000多个

检查归档产生量,12号以后


SQL> col name for a55

SQL> SELECT count(*) from  V$ARCHIVED_LOG where next_time>'2025-10-12' and APPLIED='YES';


  COUNT(*)

----------

      2203



1 确定2025-10-13 19:48:00以前都应用过了

SQL>alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

 SQL> col name for a55

SQL> SELECT THREAD#, name,SEQUENCE#, applied,next_time FROM V$ARCHIVED_LOG

  2  where next_time<'2025-10-13 19:48:00' and next_time>'2025-10-13 00:48:00' and applied='NO' order by next_time desc;

---19:48  刚好是8小时以前的时间点

no rows selected   代表这个时间段都应用过了(2025-10-13 00:48---2025-10-13 19:48    全部应用过了applied=yes,否则查询结果不止1条记录)


2 确认应用最新的时间:APPLIED=yes最新时间2025-10-14 03:59:23

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

col name for a55

SELECT THREAD#, name,SEQUENCE#, applied,next_time FROM V$ARCHIVED_LOG where next_time>'2025-10-14';




[oracle@mbisjt1 ~]$ cat   /home/oracle/scripts/delete_arch_mlllsj.sh

#!/bin/sh

. /home/oracle/.bash_profile

export ORACLE_SID=mbllll1


echo "******Begin delete*****";date

$ORACLE_HOME/bin/rman target / <

run {

allocate channel c1 device type disk;

allocate channel c2 device type disk;

crosscheck archivelog all;

delete noprompt expired archivelog all;

#delete noprompt archivelog until time 'sysdate -3';

delete noprompt archivelog until time 'sysdate-8/24';

release channel c1;

release channel c2;

}

exit;

EOF

echo "**********end delete*************"

date



sh 运行脚本/home/oracle/scripts/delete_arch_mllj.sh



grid asmcmd 检查lsdg  剩余空间651G

ASMCMD> lsdg

State    Type    Rebal  Sector  Logical_Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name

MOUNTED  EXTERN  N         512             512   4096  4194304   1048576   651264                0          651264              0             N  DGARCH/



成功后,修改脚本/home/oracle/scripts/delete_arch_mlllj.sh回原来until time 'sysdate -3';



4 持续观察日志应用情况


SELECT PROCESS, STATUS, THREAD#, SEQUENCE#,BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;











请使用浏览器的分享功能分享到微信等