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

1- One of the obsolete integrated extract was causing Rman not to delete expired archive logs.


orcl:/u02/DBA/scripts ->./delete_arch.sh
The Oracle base remains unchanged with value /u01/app/oracle

Recovery Manager: Release 12.1.0.2.0 - Production on Fri Feb 9 15:25:15 2018

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1450870477)

RMAN> 2> 3> 4> 5> 6>
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=255 device type=DISK
validation succeeded for archived log
archived log file name=/u02/fast_recovery_area/ORCL/archivelog/2018_02_09/o1_mf_1_153_f7w7xolv_.arc RECID=139 STAMP=967649093
validation succeeded for archived log
archived log file name=/u02/fast_recovery_area/ORCL/archivelog/2018_02_09/o1_mf_1_154_f7w7xtxh_.arc RECID=140 STAMP=967649098
Crosschecked 2 objects


released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=255 device type=DISK
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=/u02/fast_recovery_area/ORCL/archivelog/2018_02_09/o1_mf_1_153_f7w7xolv_.arc thread=1 sequence=153
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=/u02/fast_recovery_area/ORCL/archivelog/2018_02_09/o1_mf_1_154_f7w7xtxh_.arc thread=1 sequence=154

RMAN>

 

2- Deleted the extracts but didn’t unregister from the database


orcl:/u01/ogg ->./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.2.2 OGGCORE_12.2.0.2.0_PLATFORMS_170630.0419_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Jun 30 2017 16:12:28
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2017, Oracle and/or its affiliates. All rights reserved.



GGSCI (oradb1.localdomain) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     ABENDED     E1TEST      00:00:09      601:55:43
REPLICAT    ABENDED     R1TEST      00:00:00      601:56:10


GGSCI (oradb1.localdomain) 2> dblogin USERID GGUSER, PASSWORD gguser
Successfully logged into database.

GGSCI (oradb1.localdomain as GGUSER@orcl) 3> delete *test
Are you sure you want to delete all groups? y
Deleted EXTRACT E1TEST.

Deleted REPLICAT R1TEST.


GGSCI (oradb1.localdomain as GGUSER@orcl) 4> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING

 

3- Check the database to see what is the extract holding the arch logs


SQL> SELECT CAPTURE_NAME, 
            CAPTURE_TYPE, STATUS, 
            to_char(REQUIRED_CHECKPOINT_SCN,'999999999999999')as REQ_SCN ,
            to_char(OLDEST_SCN,'999999999999999')as OLDEST_SCN 
     FROM DBA_CAPTURE;

CAPTURE_NAME             CAPTURE_TY STATUS   REQ_SCN          OLDEST_SCN
------------------------ ---------- -------- ---------------- ----------------
OGG$CAP_E1TEST           LOCAL      ENABLED  2255523          2255523

1 row selected.

 

4- Because database still have the GoldenGate integrated capture registered, the database won’t delete the arch logs required


orcl:/u02/DBA/scripts ->./delete_arch.sh
The Oracle base remains unchanged with value /u01/app/oracle

Recovery Manager: Release 12.1.0.2.0 - Production on Fri Feb 9 15:23:00 2018

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1450870477)

RMAN> 2> 3> 4> 5> 6>
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=284 device type=DISK
validation succeeded for archived log
archived log file name=/u02/fast_recovery_area/ORCL/archivelog/2018_02_09/o1_mf_1_150_f7w7q3tq_.arc RECID=136 STAMP=967648886
validation succeeded for archived log
archived log file name=/u02/fast_recovery_area/ORCL/archivelog/2018_02_09/o1_mf_1_151_f7w7q3st_.arc RECID=137 STAMP=967648886
validation succeeded for archived log
archived log file name=/u02/fast_recovery_area/ORCL/archivelog/2018_02_09/o1_mf_1_152_f7w7q7t7_.arc RECID=138 STAMP=967648893
Crosschecked 3 objects


released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=284 device type=DISK
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=/u02/fast_recovery_area/ORCL/archivelog/2018_02_09/o1_mf_1_150_f7w7q3tq_.arc thread=1 sequence=150
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=/u02/fast_recovery_area/ORCL/archivelog/2018_02_09/o1_mf_1_151_f7w7q3st_.arc thread=1 sequence=151
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=/u02/fast_recovery_area/ORCL/archivelog/2018_02_09/o1_mf_1_152_f7w7q7t7_.arc thread=1 sequence=152

RMAN>

 

5- Unregister the GoldenGate Integrated capture from the database


orcl:/u01/ogg ->./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.2.2 OGGCORE_12.2.0.2.0_PLATFORMS_170630.0419_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Jun 30 2017 16:12:28
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2017, Oracle and/or its affiliates. All rights reserved.



GGSCI (oradb1.localdomain) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING


GGSCI (oradb1.localdomain) 2> dblogin userid gguser, password gguser
Successfully logged into database.

GGSCI (oradb1.localdomain as gguser@orcl) 3> UNREGISTER EXTRACT e1test database

2018-02-09 15:28:41  INFO    OGG-01750  Successfully unregistered EXTRACT E1TEST from database.

 

6- Delete the arch logs successfully


orcl:/u02/DBA/scripts ->./delete_arch.sh
The Oracle base remains unchanged with value /u01/app/oracle

Recovery Manager: Release 12.1.0.2.0 - Production on Fri Feb 9 15:29:04 2018

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1450870477)

RMAN> 2> 3> 4> 5> 6>
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=255 device type=DISK
validation succeeded for archived log
archived log file name=/u02/fast_recovery_area/ORCL/archivelog/2018_02_09/o1_mf_1_153_f7w7xolv_.arc RECID=139 STAMP=967649093
validation succeeded for archived log
archived log file name=/u02/fast_recovery_area/ORCL/archivelog/2018_02_09/o1_mf_1_154_f7w7xtxh_.arc RECID=140 STAMP=967649098
Crosschecked 2 objects


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

Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
139     1    153     A 09-FEB-18
        Name: /u02/fast_recovery_area/ORCL/archivelog/2018_02_09/o1_mf_1_153_f7w7xolv_.arc

140     1    154     A 09-FEB-18
        Name: /u02/fast_recovery_area/ORCL/archivelog/2018_02_09/o1_mf_1_154_f7w7xtxh_.arc

deleted archived log
archived log file name=/u02/fast_recovery_area/ORCL/archivelog/2018_02_09/o1_mf_1_153_f7w7xolv_.arc RECID=139 STAMP=967649093
deleted archived log
archived log file name=/u02/fast_recovery_area/ORCL/archivelog/2018_02_09/o1_mf_1_154_f7w7xtxh_.arc RECID=140 STAMP=967649098
Deleted 2 objects


RMAN>

Recovery Manager complete.
Advertisements

redo log generation during unrecoverable/nologging

# Capture amount of redo logs generated when table is created in UNRECOVERABLE
SQL> select vs.name, vm.value
from v$mystat vm, v$statname vs
where vm.statistic# = vs.statistic#
and vs.name like '%redo size%';

NAME VALUE
—————————————————————- ———-
redo size 597736

SQL> create table test_all unrecoverable as (select * from all_objects);

Table created.

SQL> select vs.name, vm.value
from v$mystat vm, v$statname vs
where vm.statistic# = vs.statistic#
and vs.name like '%redo size%';

NAME VALUE
—————————————————————- ———-
redo size 648936

CREATE TABLE with NOLOGGING option
SQL> select vs.name, vm.value
from v$mystat vm, v$statname vs
where vm.statistic# = vs.statistic#
and vs.name like '%redo size%';

NAME VALUE
—————————————————————- ———-
redo size 1193320

SQL> create table test_all nologging as (select * from all_objects);

Table created.

SQL> select vs.name, vm.value
from v$mystat vm, v$statname vs
where vm.statistic# = vs.statistic#
and vs.name like '%redo size%';

NAME VALUE
—————————————————————- ———-
redo size 1244436

Amount of redo logs generated for CREATE TABLE AS
– without UNRECOVERABLE/NOLOGGING option = 478904
– UNRECOVERABLE option = 51200
– NOLOGGING option = 51116
The amount of redo size is considerable less when nologging/unrecoverable is used.
Note: The database has to be in ARCHIVELOG mode to see the difference, if the database is in NOARCHIVELOG mode you won't see the difference.