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.
- Oracle Multitenant DB 4 : Parameters/SGA/PGA management in CDB-PDB - July 18, 2020
- Details of running concurrent requests in Oracle EBS - May 26, 2020
- Oracle Multitenant DB 3 : Data Dictionary Architecture in CDB-PDB - March 20, 2020