Skip to content

RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process

In one of our database where GoldenGate setup is done, RMAN process was not able to delete the archive logs. The error message that coming up while running RMAN archive log deletion command was:

archived log file name=+ARCH/DB/archivelog/2017_11_01/thread_1_seq_240409.574.961612255 thread=1 sequence=240409
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process

 

Above error means that the GoldenGate process is holding up the archives and not allowing the archives to delete since it believe that those archives are required. Note that we are using GoldenGate integrated capture which means that GoldenGate is integrated with Database/RMAN and RMAN will take into account the GoldenGate process before deleting archives.

We next verified the long running transactions in database if any such is stopping the archivelog deletion.
Below query was used to find any long running transaction which may be holding up the GoldenGate process and archivelog deletion.

select vs.inst_id, vt.xidusn, vt.xidslot, vt.xidsqn, vs.sid,vs.serial#,vs.client_identifier, vs.username, 
 vs.program, vs.machine, vt.used_ublk,
 vt.start_time, TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS') Current_Time
 from gv$transaction vt, gv$session vs where vt.addr = vs.taddr order by vt.start_time ;

There were few long running transactions but all were read-only OBI related.

We next verified the GoldenGate Extracts status

GGSCI > info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
EXTRACT RUNNING EDB01A 00:00:04 00:00:08
EXTRACT RUNNING EDB02A 00:00:03 00:00:00
EXTRACT RUNNING EDB03A 00:00:03 00:00:05
EXTRACT RUNNING PDB01A 00:00:00 00:00:08
EXTRACT RUNNING PDB02A 00:00:00 00:00:02
EXTRACT RUNNING PDB03A 00:00:00 00:00:04


GGSCI > dblogin USERIDALIAS aogg
Successfully logged into database.

GGSCI > info EDB01A

EXTRACT EDB01A Last Started 2017-10-13 03:08 Status RUNNING
Checkpoint Lag 00:00:03 (updated 00:00:09 ago)
Process ID 95426
Log Read Checkpoint Oracle Integrated Redo Logs
 2017-11-03 10:47:55
 SCN 3494.1378090545 (15007993822769)


GGSCI > info EDB02A

EXTRACT EDB02A Last Started 2017-10-13 03:09 Status RUNNING
Checkpoint Lag 00:00:03 (updated 00:00:08 ago)
Process ID 96227
Log Read Checkpoint Oracle Integrated Redo Logs
 2017-11-03 10:48:03
 SCN 3494.1378099909 (15007993832133)


GGSCI > info EDB03A

EXTRACT EDB03A Last Started 2017-10-30 15:45 Status RUNNING
Checkpoint Lag 00:00:02 (updated 00:00:08 ago)
Process ID 91506
Log Read Checkpoint Oracle Integrated Redo Logs
 2017-11-03 10:48:09
 SCN 3494.1378104669 (15007993836893)

We found that above all three extracts had recent Redo logs date so the three extracts were not stopping the archive logs deletion.

Next we verified all three registered GoldenGate capture process

Usually killing any long running transaction after verification helps in clearing up the archive log but this time issue was with a new test extract as shown below.

select capture_name, CAPTURED_SCN,APPLIED_SCN,OLDEST_SCN from dba_capture;

As shown OGG$CAP_INITLD which is EXTRACT INITLD was at old SCN. This new EXTRACT was added to fix an issue with the out-of-sync tables with EDWP. This extract was later on not used but was stuck at that SCN and this was hindering archive log deletion.

Unregistered this non-required extract:

GGSCI > dblogin  USERIDALIAS aogg
Successfully logged into database.

GGSCI > unregister extract INITLD database
2017-11-03 10:49:37  INFO    OGG-01750  Successfully unregistered EXTRACT INITLD from database.

 

Ran the archive purge job again and this time it successfully purged the archive logs.

 

[Post Views: 365]

Brijesh Gogia

I’m an experienced Oracle Applications DBA with more than a decade of full-time DBA experience. I have gained a wide knowledge of the Oracle software stack and have worked on several big projects for multi-national companies. I enjoy working with the leading-edge technology and have passion for database performance and stability. Thankfully my work allows me time for researching new technologies (and to write about them).
  
You can connect with me on LinkedIn.

Be First to Comment

Leave a Reply

Required fields are marked *

error: Alert: Content is protected !!