Category Archives: Dataguard

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

Posted on by
1

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

How to tell/find if the instance is Primary/Standby?

Posted on by
Reply

By querying v$database one can tell if the host is primary or standby

For primary
testdb> select database_role from v$database;

DATABASE_ROLE
—————-
PRIMARY

For Standby – Note you may need to connect to as sys as sysdba if the instance is in mount state
testd> select database_role from v$database;

DATABASE_ROLE
—————-
PHYSICAL STANDBY

OR
On Primary database the value of controlfile_type in V$database is “CURRENT” and standby is “STANDBY”

Value on Primary:
SQL> SELECT controlfile_type FROM V$database;

CONTROL
——–
CURRENT

Value on Standby:
SQL> SELECT controlfile_type FROM V$database;

CONTROL
——–
STANDBY

Script to delete arch logs from the Standby database host after applied

Posted on by
7

#!/usr/bin/ksh

##################################################################################################################
#
# This script is to delete the arch logs for the standby database after it has applied the logs to the instance.
#
##################################################################################################################

script=`basename $0`

export ORACLE_SID=$1
dir=/oracle/EPW/${ORACLE_SID}/temp/arch

tmpf=$dir/.$script.tmp

logcount=50

function GetAppliedLogfileSequenceNumber
{
sqlplus -S /nolog <<EOF > $tmpf
connect / as sysdba
set head off
set pages 0
select max(sequence#) from v\$archived_log where applied = ‘YES’;
select resetlogs_id from v\$database_incarnation where status = ‘CURRENT’;
exit
EOF
return
}

if [ -d $dir ]
then
cd $dir
GetAppliedLogfileSequenceNumber

if [ -s $tmpf ]
then
count=`cat $tmpf | awk ‘{print $1}’ | sed -n ‘1p’;`
db_incarnation=`cat $tmpf | awk ‘{print $1}’ | sed -n ‘3p’;`

if [ ${#count} -ne 0 ]
then
let count=$count-$logcount

if ((count <= 0))
then
echo “$script: log count is set to (non)zero no log(s) to remove”
exit 0
fi
else
exit 0
fi
else
echo “$script: no archive log(s) to remove”
exit 0
fi

while [ -f ${ORACLE_SID}_${count}_1_${db_incarnation}.arc ]
do
rm -f ${ORACLE_SID}_${count}_1_${db_incarnation}.arc
#ls ${ORACLE_SID}_${count}_1_${db_incarnation}.arc
rcode=$?

if ((rcode != 0))
then
echo “$script: cannot remove: ${ORACLE_SID}_${count}_1_${db_incarnation}.arc”
exit 1
else
let count=$count-1
fi
done

rm -f $tmpf

else
print “$script: $dir no such file or directory”
exit 1
fi
exit 0