[Solved] Oracle Delete the Archive Error: RMAN-08137

Rman-08137 reports an error when Oracle deletes the archive.

RMAN-08137: warning: archived log not deleted, needed for standby or upstream capture process
archived log file name=/u02/prod/archivelog/1_938000_1004292720.dbf thread=1 sequence=938000
RMAN-08137: warning: archived log not deleted, needed for standby or upstream capture process
archived log file name=/u02/prod/archivelog/1_938001_1004292720.dbf thread=1 sequence=938001
RMAN-08137: warning: archived log not deleted, needed for standby or upstream capture process
archived log file name=/u02/prod/archivelog/1_938002_1004292720.dbf thread=1 sequence=938002
RMAN-08137: warning: archived log not deleted, needed for standby or upstream capture process
archived log file name=/u02/prod/archivelog/1_938003_1004292720.dbf thread=1 sequence=938003
RMAN-08137: warning: archived log not deleted, needed for standby or upstream capture process
archived log file name=/u02/prod/archivelog/1_938004_1004292720.dbf thread=1 sequence=938004
RMAN-08137: warning: archived log not deleted, needed for standby or upstream capture process
archived log file name=/u02/prod/archivelog/1_938005_1004292720.dbf thread=1 sequence=938005
RMAN-08137: warning: archived log not deleted, needed for standby or upstream capture process
archived log file name=/u02/prod/archivelog/1_938006_1004292720.dbf thread=1 sequence=938006
RMAN-08137: warning: archived log not deleted, needed for standby or upstream capture process
archived log file name=/u02/prod/archivelog/1_938007_1004292720.dbf thread=1 sequence=938007

The error message shows that the archive to be deleted cannot be deleted because the backup database still needs to be deleted. Check the archive number applied to the backup database

SQL> select open_mode,database_role from v$database;

OPEN_MODE                           DATABASE_ROLE
----------------------------------- ------------------------------------------------
READ ONLY WITH APPLY                PHYSICAL STANDBY

SQL> select process,sequence# from v$managed_standby;

PROCESS                      SEQUENCE#
--------------------------- ----------
DGRD                                 0
ARCH                            939246
DGRD                                 0
ARCH                            939252
ARCH                            939248
ARCH                            939253
RFS                                  0
MRP0                            939254
DGRD                                 0
RFS                                  0
RFS                                  0

Deleting 938000 from the main database shows that the standby database still needs to be, and the standby database query has been applied to the 939254 archive
query the status of the standby database on the primary database:

SQL> select open_mode,database_role from v$database;

OPEN_MODE                                     DATABASE_ROLE
--------------------------------------------- ------------------------------------------------
READ WRITE                                    PRIMARY

SQL> select dest_name,PROTECTION_MODE,GAP_STATUS,APPLIED_THREAD#,APPLIED_SEQ# from gV$ARCHIVE_DEST_STATUS where type='PHYSICAL';

DEST_NAME                 PROTECTION_MODE                                              GAP_STATUS                APPLIED_THREAD# APPLIED_SEQ#
------------------------- ------------------------------------------------------------ ------------------------- --------------- ------------
LOG_ARCHIVE_DEST_2        MAXIMUM PERFORMANCE                                          RESOLVABLE GAP                          1       939258

The main library shows that the backup library has GAP and the status is RESOLVABLE_GAP
Through the related information query, the solution is:

  1. Mount the primary database.

    Issue the following SQL statement at the primary database:
    SQL> ALTER SYSTEM FLUSH REDO TO <target_db_name>;
    target_db_name is the db_unique_name of the backup database

    View the backup db_unique_name

SQL> select open_mode,database_role from v$database;

OPEN_MODE                           DATABASE_ROLE
----------------------------------- ------------------------------------------------
READ ONLY WITH APPLY                PHYSICAL STANDBY

SQL> show parameter db_unique_name

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
db_unique_name                       string                            standby

Discuss the downtime with the business and restart the production database to mount status

SQL> select open_mode,database_role from v$database;

OPEN_MODE                                     DATABASE_ROLE
--------------------------------------------- ------------------------------------------------
MOUNTED                                          PRIMARY

SQL> ALTER SYSTEM FLUSH REDO TO standby;

System alted.

SQL> alter database open;
 
 Database alted.

Query gap status of Zhu Bei database on the main database

SQL> select open_mode,database_role from v$database;

OPEN_MODE                                     DATABASE_ROLE
--------------------------------------------- ------------------------------------------------
READ WRITE                                    PRIMARY

SQL> select dest_name,PROTECTION_MODE,GAP_STATUS,APPLIED_THREAD#,APPLIED_SEQ# from gV$ARCHIVE_DEST_STATUS where type='PHYSICAL';

DEST_NAME                 PROTECTION_MODE                                              GAP_STATUS                APPLIED_THREAD# APPLIED_SEQ#
------------------------- ------------------------------------------------------------ ------------------------- --------------- ------------
LOG_ARCHIVE_DEST_2        MAXIMUM PERFORMANCE                  NO GAP                          1       939258

GAP_STATUS is now NO GAP, continue to execute the archive delete command, the deletion is normal

Read More: