RMAN delete archive log

Issues:

RMAN> delete archivelog sequence 12;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 device type=DISK
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2024_01_18/o1_mf_1_12_ltk03cmf_.arc thread=1 sequence=12

Reason:

################
OGG抽取进程占用
################
SQL> select name, sequence#, first_change#, next_change# from v$archived_log where sequence#=12;

NAME                                                                                                  SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
---------------------------------------------------------------------------------------------------- ---------- ------------- ------------
/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2024_01_18/o1_mf_1_12_ltk03cmf_.arc                       12       1037498      1073128

SQL> select capture_name, status, captured_scn, applied_scn, capture_type from dba_capture;

CAPTURE_NAME                   STATUS   CAPTURED_SCN APPLIED_SCN CAPTURE_TY
------------------------------ -------- ------------ ----------- ----------
OGG$CAP_EXTDB11                ENABLED       1073130     1053039 LOCAL

SQL> 


################
通常保存在FRA
################
SQL> select * from v$recovery_file_dest;

NAME                                               SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------------------------------------- ----------- ---------- ----------------- ---------------
/u01/app/oracle/fast_recovery_area                  4385144832 1544192000                 0              47

#################
使用了35.21%
#################
SQL>  SELECT * FROM   V$RECOVERY_AREA_USAGE;

FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE                          0                         0               0
REDO LOG                              0                         0               0
ARCHIVED LOG                      35.21                         0              47
BACKUP PIECE                          0                         0               0
IMAGE COPY                            0                         0               0
FLASHBACK LOG                         0                         0               0
FOREIGN ARCHIVED LOG                  0                         0               0

7 rows selected.

Solution:

注意：我的环境是OGG不需要了，直接删掉。生产环境请勿按照该方法操作

SQL> exec dbms_capture_adm.drop_capture(capture_name =>'OGG$CAP_EXTDB11');
BEGIN dbms_capture_adm.drop_capture(capture_name =>'OGG$CAP_EXTDB11'); END;

*
ERROR at line 1:
ORA-01338: Other process is attached to LogMiner session
ORA-06512: at "SYS.DBMS_CAPTURE_ADM_INTERNAL", line 829
ORA-06512: at "SYS.DBMS_CAPTURE_ADM_INTERNAL", line 800
ORA-06512: at "SYS.DBMS_CAPTURE_ADM", line 566
ORA-06512: at line 1


SQL> exec dbms_capture_adm.stop_capture('OGG$CAP_EXTDB11',true);

PL/SQL procedure successfully completed.

SQL> exec dbms_capture_adm.drop_capture('OGG$CAP_EXTDB11',true);

PL/SQL procedure successfully completed.

RMAN> delete archivelog sequence 12;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 device type=DISK
List of Archived Log Copies for database with db_unique_name ORCL
=====================================================================

Key     Thrd Seq     S Low Time 
------- ---- ------- - ---------
8       1    12      A 16-JAN-24
        Name: /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2024_01_18/o1_mf_1_12_ltk03cmf_.arc


Do you really want to delete the above objects (enter YES or NO)? yes
deleted archived log
archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2024_01_18/o1_mf_1_12_ltk03cmf_.arc RECID=8 STAMP=1158572140
Deleted 1 objects