Goldengate Trail files not deleting using PURGEOLDEXTRACTS OGG12.2

Trail files not deleting automatically for all extracts specified as expected

DB version : 12.1.0.2.0

GG Version : 12.2.0.1.1

oradev01:NOTSET:/goldengate/home $ GG

Oracle GoldenGate Command Interpreter for Oracle

Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO

Linux, x64, 64bit (optimized), Oracle 12c on Dec 12 2015 02:56:48

Operating system character set identified as UTF-8.

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

GGSCI (oradev01) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING

EXTRACT     RUNNING     EDEV1D    00:00:05      00:00:05

EXTRACT     RUNNING     EDEV3D    00:00:05      00:00:09

EXTRACT     RUNNING     EDEV4D    00:00:04      00:00:08

EXTRACT     RUNNING     EDEV2D    00:00:02      00:00:02

EXTRACT     RUNNING     EDEV5D      00:00:10      00:00:04

EXTRACT     RUNNING     PDEV1D    00:00:00      00:00:05

EXTRACT     RUNNING     PDEV3D    00:00:00      00:00:00

EXTRACT     RUNNING     PDEV4D    00:00:00      00:00:03

EXTRACT     RUNNING     PDEV2D    00:00:04      00:00:08

EXTRACT     RUNNING     PDEV5D      00:00:00      00:00:09

GGSCI (oradev01) 2> view params mgr

PORT 7809

AUTOSTART ER *

AUTORESTART ER *,RETRIES 3, WAITMINUTES 5, RESETMINUTES 60

--dblogin useridalias ggadmin

ACCESSRULE, PROG *, IPADDR 10.49.66.99, ALLOW

--node4 below

ACCESSRULE, PROG *, IPADDR 10.49.66.120, ALLOW

PURGEOLDEXTRACTS /goldengate/home/dirdat/Source/dev03/st*, USECHECKPOINTS, MINKEEPFILES 100

PURGEOLDEXTRACTS /goldengate/home/dirdat/Source/dev04/st*, USECHECKPOINTS, MINKEEPFILES 20

PURGEOLDEXTRACTS /goldengate/home/dirdat/Source/dev01/st*, USECHECKPOINTS, MINKEEPFILES 20

PURGEOLDEXTRACTS /goldengate/home/dirdat/Source/dev05/st*,   USECHECKPOINTS, MINKEEPFILES 20

PURGEOLDEXTRACTS /goldengate/home/dirdat/Source/dev02/st*, USECHECKPOINTS, MINKEEPFILES 20

GGSCI (oradev01) 1> SEND MANAGER GETPURGEOLDEXTRACTS

Sending GETPURGEOLDEXTRACTS request to MANAGER ...

PurgeOldExtracts Rules

Fileset                              MinHours MaxHours MinFiles MaxFiles UseCP

/goldengate/home/dirdat/Source/dev03/s       0        0       20        0   Y

OK

Extract Trails

Filename                        Oldest_Chkpt_Seqno  IsTable  IsVamTwoPhaseCommit

/goldengate/home/dirdat/Source/dev02/st       80

/goldengate/home/dirdat/Source/dev01/st    4165

/goldengate/home/dirdat/Source/dev04/st     7275

/goldengate/home/dirdat/Source/dev03/st   180746

/goldengate/home/dirdat/Source/dev05/st      599

SYNTAX:

SEND MANAGER

[CHILDSTATUS [DEBUG]]

[GETPORTINFO [DETAIL]

[GETPURGEOLDEXTRACTS]

From the above output we see only DEV03 database which is FIRST line in mgr param file

Now, Lets try to Refresh manager using below command and see if it changes……….

GGSCI (oradev01) 7> refresh mgr

Sending REFRESH request to MANAGER ...

Mgr Params Updated

NOTE:   DO NOT Rely on above refresh mgr command as it has only LIMITED purpose and Functionality. so better STOP and START manager but careful this will bring down EXT and REPLICATS running associated with this Manager.

GGSCI (oradev01) 9> SEND MANAGER GETPURGEOLDEXTRACTS

Sending GETPURGEOLDEXTRACTS request to MANAGER ...

PurgeOldExtracts Rules

Fileset                                                 MinHours MaxHours MinFiles MaxFiles UseCP

/goldengate/home/dirdat/Source/d        0            0          20          0           Y

OK

Extract Trails

Filename                        Oldest_Chkpt_Seqno  IsTable  IsVamTwoPhaseCommit

/goldengate/home/dirdat/Source/dev02/st       80

/goldengate/home/dirdat/Source/dev01/st    4167

/goldengate/home/dirdat/Source/dev04/st     7282

/goldengate/home/dirdat/Source/dev03/st   180854

/goldengate/home/dirdat/Source/dev05/st      602

No Changes happened………….From the above output we can see that though I have specified multiple lines in the manager param file for deleting trail files using PURGEOLDEXTARCTS using checkpoints it still takes ONLY the first line in the manager param file and this behavior is concluded as a BUG # 22912874 in 12.2 and the fix is in 12.3 version.

According to the (Doc ID 2149579.1) the Work around is to use the Parent directory instead of multiple directories 

Workaround :

GGSCI (oradev01) 10> EDIT params mgr

GGSCI (oradev01) 11> view params mgr

PORT 7809

AUTOSTART ER *

AUTORESTART ER *,RETRIES 3, WAITMINUTES 5, RESETMINUTES 60

--dblogin useridalias ggadmin

ACCESSRULE, PROG *, IPADDR 10.49.66.99, ALLOW

--node4 below

ACCESSRULE, PROG *, IPADDR 10.49.66.120, ALLOW

PURGEOLDEXTRACTS /goldengate/home/dirdat/Source/*, USECHECKPOINTS, MINKEEPFILES 50

GGSCI (oradev01) 12> refresh mgr

Sending REFRESH request to MANAGER ...

Mgr Params Updated

GGSCI (oradev01) 13> SEND MANAGER GETPURGEOLDEXTRACTS

Sending GETPURGEOLDEXTRACTS request to MANAGER ...

PurgeOldExtracts Rules

Fileset                                                 MinHours MaxHours MinFiles MaxFiles UseCP

/goldengate/home/dirdat/Source/*        0            0          50          0           Y

OK

Extract Trails

Filename                        Oldest_Chkpt_Seqno  IsTable  IsVamTwoPhaseCommit

/goldengate/home/dirdat/Source/dev02/st       80

/goldengate/home/dirdat/Source/dev01/st    4167

/goldengate/home/dirdat/Source/dev04/st     7282

/goldengate/home/dirdat/Source/dev03/st   180854

/goldengate/home/dirdat/Source/dev05/st      602

WAIT for around 5 to 10 mins and it will Work as expected for all the directories ...........

GoldenGate Manager (MGR) Not Cleaning Up Trail Files When PURGEOLDEXTRACTS Defined Twice Or More (Doc ID 2149579.1)

E-Mail Notification grayed out OEM 13.2

My OEM: 13.2 version

Today while I’m trying to modify one of our existing job in my OEM 13c found some of the options are grayed out

Reason:

This is because I logged in as SYSMAN and trying to modify the job but SYSMAN user has NO Email setup in its configuration (Strange but this is our New OEM :) )

Solution:

Add EMAIL group in the SYSMAN configuration

Setup > Security > Administrators > [select SYSMAN and Edit]

Once you edit and save the SYSMAN email configuration then you can now able to modify the jobs

Refer same:

EM 13c: Email Notification Section is Grayed Out in Job Creation Access Page in Enterprise Manager 13.2 Cloud Control (Doc ID 2224915.1)

ERROR : OGG-01172 Discard file

My replicat ABENDED with below error

Error:

2018-01-29 12:33:13  ERROR   OGG-01172  Discard file (/goldengate/home/dirrpt/rmydev2.dsc) exceeded max bytes (3000000).

To fix this either we can add and specify space for discard file (shown below) OR go to GG_HOME/dirdat and backup the above discard file.

By default GG will automatically creates new discard file every time you stare the process but if you still see this error then discard file size has to be increased

Valid file size range is from 1 to 2147

DEFAULT file size is 1MB

perform these steps............

Open the paramfile à Modify à save à Start replicat

GGSCI (oradev01) 4> edit params RMYDEV2

OLD

REPLICAT rmydev2

DISCARDFILE /goldengate/home/dirrpt/rmydev2.dsc, PURGE

NEW

REPLICAT rmydev2

DISCARDFILE /goldengate/home/dirrpt/rmydev2.dsc, PURGE, MEGABYTES 10

GGSCI (oradev01) 5> start RMYDEV2

Sending START request to MANAGER ...

REPLICAT RMYDEV2 starting

Now my replicat started and processing changes

Refer more on Oracle DOC:

GGS ERROR 103 Discard file (<GG_PATH>/<discard_filename>) Exceeded Max Bytes (1000000000) (Doc ID 1366607.1)

acfsutil size: ACFS-03006: smallest size, without loss of data

oradev01:NOTSET:/opt/oracle $ df -h

Filesystem            Size  Used Avail Use% Mounted on

/dev/mapper/VGExaDb-LVDbSys1

                                           30G   24G  4.2G  86% /

tmpfs                 253G  124M  253G   1%    /dev/shm

/dev/sda1             504M   42M  437M   9% /boot

/dev/mapper/VGExaDb-LVDbOra1

                                          198G  152G   37G  81% /u01

/dev/asm/goldengate-303

                                         500G   46G  455G  10% /goldengate

/dev/asm/gg_vol-303    20G   19G  1G 99% /u01/app/oracle/acfsmount/gg_vol

/dev/asm/acfs_expdir-303

                                            2.0T  1.2T  920G  56% /export

Set env to +ASM1 and login to sysasm and check with below sql

SQL> select FS_NAME, VOL_DEVICE, TOTAL_MB , FREE_MB from V$ASM_ACFSVOLUMES;

FS_NAME                           VOL_DEVICE                          TOTAL_MB    FREE_MB

--------------------------------- --------------------------------- ------------------ ----------

/goldengate                       /dev/asm/goldengate-303               512000   46072.625

/u01/app/oracle/acfsmount/gg_vol  /dev/asm/gg_vol-303                    20480    2.19140625

/export                           /dev/asm/acfs_expdir-303             2097152    938916.852

Now, Trying to resize ACFS (removing 500G from 2TB)

oradev01:NOTSET:/opt/oracle $ /sbin/acfsutil size -500G -d  /dev/asm/acfs_expdir-303   /export

acfsutil size: ACFS-03006: smallest size, without loss of data, is: 2193043226624 (2091449MB)

oradev01:NOTSET:/opt/oracle $ acfsutil version

acfsutil version: 12.1.0.2.0

SOLUTION:

As per Oracle DOC ID 1597710.1 it is not possible to resize ACFS so the work around is to delete the filesystem and create with the required storage

Ref more:

ACFSUTIL resize operation fails with "ACFS-03006: smallest size" (Doc ID 1597710.1)

Shell script to cleanup Goldengate PROD extracts in NON-PROD

PRODUCTION DB: Oracle on linux with Goldengate extracts ( ex: ETESTDBQ2 ) running

LINUX: RHEL6

The below script used for cleanup non-prod databases where goldengate is running when it is refreshed (FULL refresh like SNAP copy, rman clone, any physical clone) from PROD databases where goldengate also running, which means when you refresh non-prod with prod database then you will see goldengate prod extract name in non-prod which will repeat errors in alert log and restricts archivelog from deleting.

You’ll see something like below in non-prod with prod extract group names which needs cleanup

SQL> select capture_name, status, captured_scn, applied_scn, capture_type,STATUS_CHANGE_TIME from dba_capture;

CAPTURE_NAME

--------------------------------------------------------------------------------

STATUS   CAPTURED_SCN  APPLIED_SCN   CAPTURE_TY   STATUS_CHAN

------------- -----------------  -------------------    -------------------  --------------

OGG$CAP_ETESTDBQ2

ENABLED    3.5062E+11  3.5062E+11 LOCAL      13-NOV-2017

#!/bin/ksh

####################################################################################

# This script will do the POST refresh activities for Databases having Goldengate

# It will uses the directory defined in the EXPDPDR directory

#

# Syntax:  Script.sh <database_name>

#

# PARAMETERS:

#     Source dbname    : (required) - database name

#

# EX: GG_POST.sh mydev

#

# AUTHOR: Chandra

####################################################################################

echo -e "\n*************************************"

echo  "DATE = `date`"

echo  "HOSTNAME = " `hostname`

echo -e "*************************************\n"

if [ "$1" = "" ]; then

  echo " "

  echo "Syntax: $0 <DB name>"

  exit 1

fi

DBTGT=`echo $1 | tr "[:upper:]" "[:lower:]"`

DBTAIL=`echo $DBTGT |awk '{print substr($0,3)}'`
SOURCE_SERVER=`hostname`

#echo "DBTAIL :" $DBTAIL

#echo "DBTGT : " $DBTGT

if [[ "$DBTAIL" = "prod"]]; then

echo -e "\nThis script CANNOT be run on $DBTGT PRODUCTION databases \n"

exit 1

fi

ORAENV_ASK=NO ; export ORAENV_ASK; export ORACLE_SID=$DBTGT

#. oraenv $ORACLE_SID

if [[ "${SOURCE_SERVER:0:3}" = "rac" ]] || [[ "${SOURCE_SERVER:0:3}" = "RAC" ]]; then

. /opt/oracle/RAC/oraenv

else

. /usr/local/bin/oraenv

fi

echo "ORACLE_HOME=" $ORACLE_HOME

CAP_NAME=`sqlplus -s /nolog << EOF

connect / as sysdba

set verify off trimspool on feed off echo off termout off head off

col capture_name for a15

col STATE for a33

select capture_name from dba_capture where capture_name like '%PROD%';

exit

EOF

`

CAP_NEW_NAME=$CAP_NAME

echo -e "\n CAP_NEW_NAME = " $CAP_NEW_NAME

C_ENV=`echo $CAP_NAME |awk '{print substr($0,9,14)}'`

echo " CAP_NAME= $CAP_NAME"

echo " C_ENV" =$C_ENV

stop_cap () {

$ORACLE_HOME/bin/sqlplus -s /nolog <<EOF

connect / as sysdba

exec DBMS_CAPTURE_ADM.STOP_CAPTURE('OGG\$CAP_${C_ENV}');

EOF

}

drop_cap () {

$ORACLE_HOME/bin/sqlplus -s /nolog <<EOF

connect / as sysdba

exec DBMS_CAPTURE_ADM.DROP_CAPTURE('OGG\$CAP_${C_ENV}');

EOF

}

CHECK_CAP () {

$ORACLE_HOME/bin/sqlplus -s /nolog <<EOF

connect / as sysdba

set line 280 pages 999

col CAPTURE_NAME for a22
col STATE for a25

select capture_name, status, captured_scn, applied_scn, capture_type from dba_capture;

select extract_name,CAPTURE_NAME, sid, serial#, STATE from  V\$GOLDENGATE_CAPTURE;

EOF

}

if [[ "$C_ENV" == "ESTPRODP" ]] || [[ "$C_ENV" == "EDVPRODP" ]] ; then

echo -e "\n*****************************************************"

echo -e "Database ${1} has PROD "${CAP_NEW_NAME}" capture name "

echo -e "*************************************************** \n"

echo -e " BEFORE Dropping :"

CHECK_CAP ;

echo -e " \nStopping "${CAP_NEW_NAME}" Capture process "

stop_cap;

echo -e "\nDROPPING "${CAP_NEW_NAME}" Capture process "

drop_cap;

echo -e " AFTER Dropping :"

CHECK_CAP ;

else
echo -e "\n*********************************************************"

echo -e "NOT found PROD extract process name in $1 database "
echo -e "*********************************************************\n"

CHECK_CAP ;

fi

echo -e "\nDATE = `date`"

Another option is to login to ggsci and do unregister, check below link to unregister manually

http://chandu208.blogspot.com/2017/11/rman-08137-warning-archived-log-not.html

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

When trying to delete Database archivelog where goldengate in running using rman i see below Warning  message.

RMAN> Delete archivelog all completed before  'SYSDATE-3';

released channel: ORA_DISK_1

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=2907 device type=DISK

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

archived log file name=+RECO/TESTDB/ARCHIVELOG/2017_10_07/thread_1_seq_3435.12440.956737243 thread=1 sequence=3435

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

archived log file name=+RECO/TESTDB/ARCHIVELOG/2017_10_07/thread_1_seq_3436.12438.956746549 thread=1 sequence=3436

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

archived log file name=+RECO/TESTDB/ARCHIVELOG/2017_10_07/thread_1_seq_3437.12437.956746819 thread=1 sequence=3437

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

archived log file name=+RECO/TESTDB/ARCHIVELOG/2017_10_07/thread_1_seq_3438.12436.956746869 thread=1 sequence=3438

SQL> select capture_name, status, captured_scn, applied_scn, capture_type,STATUS_CHANGE_TIME from dba_capture;

CAPTURE_NAME                                                                                                                     STATUS   CAPTURED_SCN    APPLIED_SCN   CAPTURE_TY   STATUS_CHAN

------------------------------------------------------------------------------------------------------------------- -------- ------------ 

 ----------- ---------- -----------

OGG$CAP_ETESTDB                                                                                                                   ENABLED    3.3555E+11  3.3555E+11 LOCAL      27-JUN-2017

OGG$CAP_ETESTDBQ2                                                                                                                 ENABLED    3.5047E+11  3.5047E+11 LOCAL      13-NOV-2017

I see one of the OLD Extract process(ETESTDB) is registered and blocking archivelog deletion using RMAN which is not used any more so I need to delete that to resolve the issue

SQL> exec DBMS_CAPTURE_ADM.STOP_CAPTURE('OGG$CAP_ETESTDB');

  

^CBEGIN DBMS_CAPTURE_ADM.STOP_CAPTURE('OGG$CAP_ETESTDB'); END;

*

ERROR at line 1:

ORA-01013: user requested cancel of current operation

ORA-06512: at "SYS.DBMS_LOGREP_UTIL", line 1310

ORA-06512: at "SYS.DBMS_CAPTURE_ADM", line 90

ORA-06512: at line 1

Hanging here ..............so killed

GGSCI (ORACLEDEV01) 2> dblogin useridalias gguser_dev

ERROR: Unable to connect to database using user GGUSER. Please check privileges.

Unable to initialize database connection because of error ORA-00257: Archiver error. Connect AS SYSDBA only until resolved..

GGSCI (ORACLEDEV01) 3> dblogin useridalias gguser_dev

Successfully logged into database.

GGSCI (ORACLEDEV01 as GGUSER@TESTDB) 4> unregister extract ETESTDB database

2017-11-14 11:14:21  INFO    OGG-01750  Successfully unregistered EXTRACT ETESTDB from database.

GGSCI (ORACLEDEV01 as GGUSER@TESTDB) 5> exit

oracledev01:TESTDB:/home/oracle $ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Nov 14 11:14:33 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics

and Real Application Testing options

SQL> select capture_name, status, captured_scn, applied_scn, capture_type,STATUS_CHANGE_TIME from dba_capture;

CAPTURE_NAME

--------------------------------------------------------------------------------

STATUS   CAPTURED_SCN APPLIED_SCN CAPTURE_TY STATUS_CHAN

-------- ------------ ----------- ---------- -----------

OGG$CAP_ETESTDBQ2

ENABLED    3.5062E+11  3.5062E+11 LOCAL      13-NOV-2017

Refer Oracle Doc:

RMAN-08137: WARNING: Archived Log Not Deleted, Needed For Standby Or Upstream Capture Proces (Doc ID 1993799.1)

ORA-29701: unable to connect to Cluster Synchronization Service during ASM startup

ORACLE  Version : 12.2 standalone with ASM

Trying to start ASM instance after server reboot and it is failing with error

    

oradev.domain.com:+ASM:/opt/oracle/12.1.0/grid/bin $ sqlplus / as sysasm

SQL*Plus: Release 12.1.0.2.0 Production on Fri Oct 6 16:19:59 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup

ORA-01078: failure in processing system parameters

ORA-29701: unable to connect to Cluster Synchronization Service

SQL> exit

Disconnected

oradev.domain.com:+ASM:/opt/oracle/12.1.0/grid/bin $ ./crsctl stat res -t

--------------------------------------------------------------------------------

Name           Target  State        Server                   State details

--------------------------------------------------------------------------------

Local Resources

--------------------------------------------------------------------------------

ora.asm

               OFFLINE OFFLINE      oradev.domain.com                   Instance Shutdown,STABLE

ora.ons

               OFFLINE OFFLINE      oradev.domain.com                   STABLE

--------------------------------------------------------------------------------

Cluster Resources

--------------------------------------------------------------------------------

ora.cssd

      1        OFFLINE OFFLINE                               STABLE

ora.diskmon

      1        OFFLINE OFFLINE                               STABLE

ora.evmd

      1        ONLINE  ONLINE       oradev.domain.com                   STABLE

                 

oradev.domain.com:+ASM:/opt/oracle/12.1.0/grid/bin $ ./crsctl start resource ora.cssd

CRS-4995:  The command 'Start  resource' is invalid in crsctl. Use srvctl for this command

oradev.domain.com:NOTSET:/opt/oracle/12.1.0/grid/bin $ ps -ef | grep cssd.bin

oracle   12249     1  0 16:21 ?        00:00:26 /opt/oracle/12.1.0/grid/bin/ocssd.bin

oracle   21138 20798  0 21:49 pts/0    00:00:00 grep cssd.bin

oradev.domain.com:+ASM:/opt/oracle/12.1.0/grid/bin $ ./crsctl start resource ora.cssd -unsupported

CRS-2672: Attempting to start 'ora.cssd' on 'oradev.domain.com'

CRS-2672: Attempting to start 'ora.diskmon' on 'oradev.domain.com'

CRS-2676: Start of 'ora.diskmon' on 'oradev.domain.com' succeeded

CRS-2676: Start of 'ora.cssd' on 'oradev.domain.com' succeeded

oradev.domain.com:+ASM:/opt/oracle/12.1.0/grid/bin $ ./crsctl stat res -t

--------------------------------------------------------------------------------

Name           Target  State        Server                   State details

--------------------------------------------------------------------------------

Local Resources

--------------------------------------------------------------------------------

ora.asm

               OFFLINE OFFLINE      oradev.domain.com                   Instance Shutdown,STABLE

ora.ons

               OFFLINE OFFLINE      oradev.domain.com                   STABLE

--------------------------------------------------------------------------------

Cluster Resources

--------------------------------------------------------------------------------

ora.cssd

      1        ONLINE  ONLINE       oradev.domain.com                   STABLE

ora.diskmon

      1        OFFLINE OFFLINE                               STABLE

ora.evmd

      1        ONLINE  ONLINE       oradev.domain.com                   STABLE

                 

                 

oradev.domain.com:+ASM:/opt/oracle/12.1.0/grid/bin $ sqlplus / as sysasm

SQL*Plus: Release 12.1.0.2.0 Production on Fri Oct 6 16:23:58 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup

ASM instance started

Total System Global Area 2147483648 bytes

Fixed Size                  2926472 bytes

Variable Size            2111002744 bytes

ASM Cache                  33554432 bytes

ASM diskgroups mounted

ASM successfully started …………………

Here "AUTO_START" is set to "NEVER" and that is the reason for not starting after reboot but we can change to "ALWAYS" to avoid this Manual intervention after every reboot

$ ./crsctl stat res ora.cssd -p -init | grep AUTO_START

AUTO_START=never

Refer:

ASM Instance doesn't start in Oracle Restart (Standalone GI) environment after node reboot (Doc ID 1917176.1)