Application | Middleware | GoldenGate, GoldenGate, RAC | Data Guard

How To Delete Archive Logs From Downstream Database

After deploy GoldenGate with downstream database option archives from downstream database not cleaning ? All database transaction are gracefull, but archives (from downstream) are not cleaning?

Are no log transactions registered on GG Extract?
check this:

GGSCI (dbcloud) 3> send ETL01 showtrans

Sending SHOWTRANS request to EXTRACT ETL01 ...

Oldest redo log files necessary to restart Extract are:
------------------------------------------------------------
XID:                  14.6.3931092
Items:                1
Extract:              ETL01
Redo Thread:          1
Start Time:           2017-00-00:00:00:00
SCN:                  1682.4049305132 (7228184297004)
Redo Seq:             8612
Redo RBA:             20965491728
Status:               Running
------------------------------------------------------------

If you on same case, make this:

It should reach (and delete) JUST archived log that have already read by GoldenGate.

set serveroutput on size unlimited
set line 1000
set trimsp on
set feed off
set pages 5000
set pagesize 0
spool [[ some_dir ]]/delete_archives__dowstream_goldengate.sh

-- SR 3-14409179111 - Golden Gate Configuration, How To Delete Archive Logs From Downstream Database (Doc ID 2011174.1)
SELECT 'rm ' || r.NAME
-- case when (r.next_scn > c.required_checkpoint_scn) then 'NO' else 'YES' end purgable
FROM DBA_REGISTERED_ARCHIVED_LOG r, DBA_CAPTURE c
WHERE r.CONSUMER_NAME = c.CAPTURE_NAME and r.source_database = c.source_database and r.next_scn < ( select min(required_checkpoint_scn) from dba_capture where captur
e_type='DOWNSTREAM')
and purgeable='YES'
order by modified_time;
spool off

Schedule the output in automation tool (crontab / dba_job / windows job scheduler, (whatever)):

Database, Errors and Bugs, GoldenGate

Extract abend with ORA-03113: end-of-file on communication channel

GoldenGate Extract / Replicat abend with bellow error:

Source Context :
SourceModule : [ggdb.ora.sess]
SourceID : [/scratch/aime/adestore/views/aime_stuya22/oggcore/OpenSys/src/gglib/ggdbora/ocisess.c]
SourceFunction : [OCISESS_context_def::oci_try(int, const char *, ...)]
SourceLine : [832]

2017-04-19 07:52:07 ERROR OGG-00665 OCI Error executing single row select (status = 3113-ORA-03113: end-of-file on communication channel)

It’s is a common stuff in Oracle Database not stable or it’s taking some 00600.

Review items:

  • Oracle Database alert log
  • If user process has been killed
  • Network communication (IPTABLES stuff)
  • Server crash

Suggest you to review ‘Master Note: Troubleshooting ORA-03113 (Doc ID 1506805.1)‘ on Oracle support services and keep database away from ORA-03113.

🙂

Database, Errors and Bugs, GoldenGate

OGG-02077 Extract encountered a read error in the asynchronous reader thread and is abending: Error code 1683

Have this crazy in ggserr.log?

Source Context :
SourceModule : [er.redo.ora]
SourceID : [/scratch/aime/adestore/views/aime_stuya22/oggcore/OpenSys/src/app/er/redo/oracle/redoora.c]
SourceFunction : [REDOORA_handle_read_error(errmsg_t &)]
SourceLine : [11630]

ERROR OGG-02077 Extract encountered a read error in the asynchronous reader thread and is abending: Error code 1683, error message: ORA-01683: unable to extend index . partition by in tablespace.

Check db alert_*.log, you should find some alert.

ORA-1688: unable to extend table SYS.WRH$_ACTIVE_SESSION_HISTORY partition WRH$_ACTIVE_1470969483_24757 by 128 in tablespace SYSAUX
ORA-1688: unable to extend table SYS.WRH$_ACTIVE_SESSION_HISTORY partition WRH$_ACTIVE_1470969483_24757 by 128 in tablespace SYSAUX

Resolving issue:

alter tablespace SYSAUX add datafile '+DG' size 1g;

Some options are:
Reduce AWR retention:

SQL> SELECT extract(day from snap_interval) *24*60+extract(hour from snap_interval) *60+extract(minute from snap_interval) snapshot_Interval,
extract(day from retention) *24*60+extract(hour from retention) *60+extract(minute from retention) retention_Interval
FROM dba_hist_wr_control;
SQL> exec DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(retention => 10800, interval => 10);

Reduce Statistics Level:

SQL> show parameter STATISTICS_LEVEL
NAME                 TYPE        VALUE
---------------------------------------------------------
statistics_level     string     ALL

SQL> alter system set STATISTICS_LEVEL = TYPICAL scope=both sid='*';

Type your message when beep 🙂

Linux, Shell Scripts

umount: This utility only unmounts cifs filesystems

Can’t umount Red-Hat filesystem with error “This utility only unmounts cifs filesystems” either with force?

[@Linux-redhat5 ~]$ cat /etc/redhat-release
Red Hat Enterprise Linux Server release 5.11 (Tikanga)

ERROR:

[@Linux-redhat5 /]# umount /cifsfilesystem/data/custom
This utility only unmounts cifs filesystems.
This utility only unmounts cifs filesystems.
This utility only unmounts cifs filesystems.

More“umount: This utility only unmounts cifs filesystems”

Database, GoldenGate

GoldenGate supplemental log check

Are you bored with GoldenGate objects with no supplemental log on Oracle Database?

This script will check ALL tables in GG PRM, after check on database supplemental log information.

PRM, dblogin should be changed.

Try this script on crontab:

vi gg_trandata_checkup.sh

cd $GGATE

echo > gg.out
echo "dblogin USERID @ PASSWORD > template_check.tmp
echo "" >> template_check.tmp
cat dirprm/ext_1.prm|grep -i TABLE|awk {' print $1" "$2 '}|awk -F"," {' print $1 '}|sed 's/TABLE/INFO TRANDATA/g' >> template_check.tmp

./ggsci -s gg.out
obey template_check.tmp
EOF

cat gg.out|sed '/^$/d'|grep -v ": ALL."|grep -v -i "info trandata"|grep -v "data is enabled for table"|grep -v "ERROR OGG-01784"

Mail this to GGAdmins.
Maiquel.