Golden Gate Maintenance: remove old archives

From Robs_Wiki
Jump to: navigation, search

Introduction

  • In this post it is explained how to remove archives from an Oracle database that is part of a Golden Gate configuration. The Golden Gate extract process (or Oracle Log Miner processes in the case of integrated extract) query the online redo log files for any changes, but may also query archived redo log files in case there are any open long running transactions.
  • It is recommended by Oracle to keep 8 hours of archives on disks. They may be required by Extract during an Extract recovery. You don't need to keep any older archives, as that information is stored in the Bounded Recovery checkpoints. You can read more about Bounded Recovery on another post of this website.
  • You can simply delete the archives with rman. In case the archive is still required by Golden Gate you will receive an error message (see below), which prevents rman to delete the archives. Therefore, you should always remove the archives with rman:
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process

Step 1 Check which archives are still required by Golden Gate

SQL> select MIN_REQUIRED_CAPTURE_CHANGE# from v$database;
MIN_REQUIRED_CAPTURE_CHANGE#
----------------------------
6721156206136

Alternatively, you can run the following query:

SQL> col capture_name format a30
col capture_scn format 999999999999999
col applied_scn format 999999999999999
col oldest_scn format 999999999999999
select capture_name, CAPTURED_SCN,APPLIED_SCN,OLDEST_SCN from dba_capture;
CAPTURE_NAME CAPTURED_SCN APPLIED_SCN OLDEST_SCN
------------------------------ ---------------- ---------------- ----------------
OGG$CAP_PROBTEST 6721156206150 0 0
OGG$CAP_XROBTEST 6721373015541 6721372959658 6721360317099

As you can see, the above query returns a different SCN. I may have been hitting but "Bug 14521465 : RMAN NOT ABLE TO DELETE ARCHIVE LOGS AFTER GOLDENGATE IS SETUP".

Step 2 Check which archives correspond with the SCN

column first_change# format 99999999999999
column next_change# format 99999999999999
col name format a50
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';

select sequence#, status, first_time, next_time, first_change#, next_change#, name from v$archived_log where 6721156206136 between first_change# and next_change#;
SEQUENCE#  FIRST_TIME           NEXT_TIME          FIRST_CHANGE#   NEXT_CHANGE#   NAME
-------------------------------------------------- ---------- ---------- - -------------------- -------------------- --------------- ---------------
342        28-JUL-2017 07:56:31 28-JUL-2017 07:56:31 6721156206129 6721156206136  /oracle/archive/PRD12C_343_1_950178022.arc 

So, in this example, the archive# 342 is still required by Extract. In case the query above does not return any rows, it is possible that the oldest SCN is not yet archived and is still part of the online redo log files. In tthat case you can check with query:

SQL> select thread#, sequence#, status, first_time, next_time, first_change#, next_change# from v$log;

Step 3 Remove the archives with rman

RMAN> delete archivelog until time 'sysdate-1';