REPLICAT is stuck at same point

 ===========================
General
===========================
Replicat process is stuck.

It has big lag, and it is not coming down
What to check?

===========================
Steps - check REPLICAT
===========================

When running the SEND REPLICAT REP_I_01 STATUS

It seems that the replicat is stuck on the same file.

When checking for dirdat/in files, this is the last file received from the other node.

Need to check on DATAPUMP EXTRACT on the other node

===========================
Steps - check DATAPUMP
===========================

Checking on Datapump Extract

The extract status is RUNNING but it is stuck.

When running SEND EXTRACT DPM_I_01 STATUS

It is stuck on the same trunsaction

===========================
Steps - Fix DATAPUMP issue
===========================

GGSCI (qanfv-1-dbs-1b) 68> STOP EXTRACT DPM_S_01

Sending STOP request to EXTRACT DPM_S_01 ...

STOP request pending end-of-transaction (1,034 records so far).

GGSCI (qanfv-1-dbs-1b) 69> kill EXTRACT DPM_S_01

Sending KILL request to MANAGER ...

Killed process (31058) for EXTRACT DPM_S_01

GGSCI (qanfv-1-dbs-1b) 90> INFO DPM_S_01

EXTRACT    DPM_S_01  Last Started 2021-06-27 12:22   Status ABENDED

Checkpoint Lag       22:57:23 (updated 00:00:01 ago)

Log Read Checkpoint  File /software/ogg/191/dirdat/01/out/es000021373

                     2021-06-26 13:25:39.000000  RBA 450922877

2021-06-27 12:23:02  ERROR   OGG-01091  Unable to open file "/software/ogg/191/dirdat/01/out/es000021373" (error 2, No such file or directory).

****************************************************************

DATAPUMP Extract is expecting for next file, 21373, but this file does not exists.

Extract should be skipped to next available file 21603

GGSCI (qanfv-1-dbs-1b) 93> ALTER EXTRACT DPM_S_01 extseqno 21603

EXTRACT altered.

GGSCI (qanfv-1-dbs-1b) 94> START  EXTRACT DPM_S_01

Sending START request to MANAGER ...

EXTRACT DPM_S_01 starting

Now, Datapump is sending records to the other node, and the REPLICAT is processing data, and the Lag is reducing.

Drop Undo Tablespace

After switch from UNDO Tablespace UNDOTBS1 to UNDOTBS2, UNDOTBS2 Tablespace is still exists.

Need to drop it manually.

After dropping UNDOTBS2, database cannot be started

WTF...

Steps and how to fix

COL TABLESPACE_NAME FOR A30

COL STATUS FOR A20

SELECT tablespace_name, status, count(*) 

FROM DBA_ROLLBACK_SEGS 

GROUP BY tablespace_name, status;

TABLESPACE_NAME           STATUS                 COUNT(*)

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

UNDOTBS1                  ONLINE                       41

SYSTEM                    ONLINE                        1

UNDOTBS2                  OFFLINE                       0

In detail:

SET LINESIZE 160

SELECT owner, segment_name, tablespace_name, status 

FROM DBA_ROLLBACK_SEGS ORDER BY 3;

SYS    SYSTEM                SYSTEM  ONLINE

PUBLIC _SYSSMU42_3536664823$ UNDOTBS ONLINE

PUBLIC _SYSSMU43_1783674736$ UNDOTBS ONLINE

PUBLIC _SYSSMU44_629285899$  UNDOTBS ONLINE

PUBLIC _SYSSMU45_2404285950$ UNDOTBS ONLINE

PUBLIC _SYSSMU46_132405334$  UNDOTBS ONLINE

DROP TABLESPACE UNDOTBS2 INCLUDING CONTENTS AND DATAFILES;

At this point oracle crashed and could no be started.

Upon startup, this error is coming:

ORA-00603: ORACLE server session terminated by fatal error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-30012: undo tablespace 'UNDOTBS2' does not exist or of wrong type
Incident details in: /software/oracle/diag/rdbms/igt/igt/incident/incdir_2131503

How to fix

Option 1. The proper solution:

CREATE PFILE FROM SPFILE

Edit PFILE so that oracle is aware of the correct tablespace

Start oracle from PFILE

Option 2. The quick solution:

make a copy from spfile

edit the binary file spfile

Replace from 

*.undo_tablespace='UNDOTBS2'

to 

*.undo_tablespace='UNDOTBS1'

Start oracle

Now it starts without an error

Change REPLICAT to run in Integrated Mode

How to switch REPLICAT to running in Integrated mode.

GGSCI (my_server) 12> INFO REP_P_01
REPLICAT   REP_P_01  Last Started 2021-06-21 13:34   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:00 ago)
Process ID           25808
Log Read Checkpoint  File /software/ogg/191/dirdat/01/in/ep000000198
                     2021-06-22 08:33:50.006768  RBA 262862298

STOP REP_P_01
ALTER REPLICAT REP_P_01, INTEGRATED
INFO REP_P_01
GGSCI (my_server) 15> INFO REP_P_01
REPLICAT   REP_P_01  Initialized   2021-06-22 08:34   Status STOPPED
INTEGRATED
Checkpoint Lag       00:00:00 (updated 00:00:07 ago)
Log Read Checkpoint  File /software/ogg/191/dirdat/01/in/ep000000198
                     2021-06-22 08:34:05.006776  RBA 262884743

REPLICAT Golden gate - "No active replication maps." error message

======================================

Replicat is giving "No active replication maps."

======================================

SEND REP_I_01 STATS
Sending STATS request to REPLICAT REP_I_01 ...
No active replication maps.

What to check?

INFO REP_I_01 DETAIL

Current directory    /software/ogg/191

Report file          /software/ogg/191/dirrpt/REP_I_01.rpt

Parameter file       /software/ogg/191/dirprm/rep_i_01.prm

Checkpoint file      /software/ogg/191/dirchk/REP_I_01.cpr

Checkpoint table     OGG.GGS_CHECKPOINT

Process file

Error log            /software/ogg/191/ggserr.log

less /software/ogg/191/dirrpt/REP_I_01.rpt

 Opened trail file /software/ogg/191/dirdat/01/in/ei000001673 at 2021-06-21 10:38:15.162627.

 

-rw-r----- 1 oracle dba 132849 Jun 21 09:15 /software/ogg/191/dirdat/01/in/ei000001672

-rw-r----- 1 oracle dba  30904 Jun 21 09:40 /software/ogg/191/dirdat/01/in/ei000001673

-rw-r----- 1 oracle dba 114623 Jun 21 11:23 /software/ogg/191/dirdat/01/in/ei000004596

-rw-r----- 1 oracle dba  81883 Jun 21 12:34 /software/ogg/191/dirdat/01/in/ei000004597

GGSCI (hostname) 1> INFO REP_I_01

REPLICAT   REP_I_01  Last Started 2021-06-21 10:38   Status RUNNING

Checkpoint Lag       00:00:00 (updated 00:00:03 ago)

Process ID           13683

Log Read Checkpoint  File /software/ogg/191/dirdat/01/in/ei000001673

                     2021-06-21 09:16:11.197537  RBA 30904

ls -ltr /software/ogg/191/dirdat/01/in/ei000*

-rw-r----- 1 oracle dba 132849 Jun 21 09:15 /software/ogg/191/dirdat/01/in/ei000001672

-rw-r----- 1 oracle dba  30904 Jun 21 09:40 /software/ogg/191/dirdat/01/in/ei000001673

-rw-r----- 1 oracle dba 114623 Jun 21 11:23 /software/ogg/191/dirdat/01/in/ei000004596

-rw-r----- 1 oracle dba  81883 Jun 21 12:34 /software/ogg/191/dirdat/01/in/ei000004597

Need to advance REPLICAT to the correct sequence

GGSCI (hostname) 3> STOP REPLICAT REP_I_01

Sending STOP request to REPLICAT REP_I_01 ...

Request processed.

GGSCI (hostname) 4> ALTER REPLICAT REP_I_01, EXTSEQNO 4596, EXTRBA 0

2021-06-21 12:36:56  INFO    OGG-06594  Replicat REP_I_01 has been altered. Even the start up position might be updated, duplicate suppression remains active in next startup. To override duplicate suppression, start REP_I_01 with NOFILTERDUPTRANSACTIONS option.

REPLICAT altered.

GGSCI (hostname) 3> START REPLICAT REP_I_01

Sending START request to MANAGER ...

REPLICAT REP_P_01 starting

GGSCI (hostname) 6> INFO REP_I_01

REPLICAT   REP_I_01  Last Started 2021-06-21 12:37   Status RUNNING

Checkpoint Lag       00:00:06 (updated 00:00:04 ago)

Process ID           11340

Log Read Checkpoint  File /software/ogg/191/dirdat/01/in/ei000004597

                     2021-06-21 12:37:07.003724  RBA 85137

File was set to 4596, but REPLICAT is already at 4597 - working!

 

GGSCI (hostname) 24> SEND REP_I_01 STATS

Sending STATS request to REPLICAT REP_I_01 ...

Start of Statistics at 2021-06-21 12:37:39.

Replicating from OGG.GG_HEARTBEAT_SEED to OGG.GG_HEARTBEAT:

*** Total statistics since 2021-06-21 12:37:09 ***

        Total inserts                                      0.00

        Total updates                                     75.00

        Total deletes                                      0.00

        Total upserts                                      0.00

        Total discards                                     0.00

        Total operations                                  75.00

 

Now, the stats are coming

======================================

Extract is giving "No active replication maps."

======================================

SEND EXTRACT DPM_I_01 STATS

Sending STATS request to EXTRACT DPM_I_01 ...

No active extraction maps.

INFO DPM_P_01

oracle@qanfv-1-dbs-1a:/software/ogg/191>% ls -ltr /software/ogg/191/dirdat/01/in/ep000000*

-rw-r----- 1 oracle dba 1520270 Jun 21 09:15 /software/ogg/191/dirdat/01/in/ep000000028

-rw-r----- 1 oracle dba  362544 Jun 21 09:40 /software/ogg/191/dirdat/01/in/ep000000029

-rw-r----- 1 oracle dba 8493214 Jun 21 09:41 /software/ogg/191/dirdat/01/in/ep000000195

-rw-r----- 1 oracle dba 9944956 Jun 21 10:00 /software/ogg/191/dirdat/01/in/ep000000196

-rw-r----- 1 oracle dba 1068368 Jun 21 10:12 /software/ogg/191/dirdat/01/in/ep000000197

-rw-r----- 1 oracle dba 1051251 Jun 21 11:23 /software/ogg/191/dirdat/01/in/ep000000198

-rw-r----- 1 oracle dba 1006145 Jun 21 12:30 

GGSCI (hostname) 6> SEND EXTRACT DPM_P_01, FORCESTOP

Sending FORCESTOP request to EXTRACT DPM_N_01 ...

STOP request will be executed immediately (recovery aborted).

GGSCI (hostname) 6> ALTER EXTRACT DPM_P_01, begin 2021-06-21 09:59:50

EXTRACT altered.

GGSCI (hostname) 6> START DPM_P_01

Sending START request to MANAGER ...

EXTRACT DPM_P_01 starting

GGSCI (hostname) 6> SEND DPM_P_01 STATS

Sending STATS request to EXTRACT DPM_P_01 ...

Start of Statistics at 2021-06-21 12:32:03.

Output to /software/ogg/191/dirdat/01/in/ep:

Extracting from LAB_QANFV_ALLQQ.SHM_SERVICE_PROCESSES to LAB_QANFV_ALLQQ.SHM_SERVICE_PROCESSES:

*** Total statistics since 2021-06-21 12:31:54 ***

        Total inserts                                      0.00

        Total updates                                  19204.00

        Total deletes                                      0.00

        Total upserts                                      0.00

        Total discards                                     0.00

        Total operations                               19204.00

Now, the stats are coming

Additional info

All GG processes were setup with a wrong remote host IP

After configuration change + restart + unregister and register the extract and datapump,

processes were still using a wrong sequence for files.

Statistics Advisor: Invalid task name for the current user

=========================

General

=========================

2021-06-18T22:34:52.911764+00:00
Errors in file /software/oracle/diag/rdbms/igt/igt/trace/igt_j001_31250.trc:
ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_16655"
ORA-20001: Statistics Advisor: Invalid task name for the current user
ORA-06512: at "SYS.DBMS_STATS", line 47214
ORA-06512: at "SYS.DBMS_STATS_ADVISOR", line 882
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 20059
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 22201
ORA-06512: at "SYS.DBMS_STATS", line 47204
2021-06-18T22:36:34.438107+00:00
Thread 1 advanced to log sequence 355952 (LGWR switch)

=========================

Solution

=========================

The problem, is that the tasks are missing.

This is because of a know issue #25710407

SELECT name, ctime, how_created,OWNER_NAME 

  FROM sys.wri$_adv_tasks
 WHERE name in ('AUTO_STATS_ADVISOR_TASK','INDIVIDUAL_STATS_ADVISOR_TASK');  

no rows selected

Run init to DBMS_STATS package

EXEC DBMS_STATS.INIT_PACKAGE();
PL/SQL procedure successfully completed.

Now, tasks exist:

SELECT name, ctime, how_created,OWNER_NAME 

  FROM sys.wri$_adv_tasks

 WHERE name in ('AUTO_STATS_ADVISOR_TASK','INDIVIDUAL_STATS_ADVISOR_TASK');   
NAME                           CTIME              HOW_CREATED
------------------------------ ------------------ -------------------
AUTO_STATS_ADVISOR_TASK        20-JUN-21          CMD
INDIVIDUAL_STATS_ADVISOR_TASK  20-JUN-21          CMD
 

Oracle logs cleanup

===========================================
Keep listener log size under control
===========================================

crontab

1 6 * * * /software/oracle/oracle/scripts/clean_oracle_logs.sh

/software/oracle/oracle/scripts/clean_oracle_logs.sh

#!/bin/bash

. /etc/sh/orash/oracle_login.sh igt

ORA_INST=igt

LIST_SERVER=`hostname`

DAYS_TO_KEEP=14

AUD_DAYS_TO_KEEP=30
DAYS_TO_KEEP_LIST=2

LISTENER_TRACE=/software/oracle/diag/tnslsnr/${LIST_SERVER}/lsnr_igt/trace
LISTENER_TRACE_2=/software/oracle/diag/tnslsnr/${LIST_SERVER}/listener/trace

LISTENER_ALERT=/software/oracle/diag/tnslsnr/${LIST_SERVER}/lsnr_igt/alert

LISTENER_ALERT_2=/software/oracle/diag/tnslsnr/${LIST_SERVER}/listener/alert

LOG_PATH=${ORACLE_HOME}/rdbms/log

AUDIT_PATH=/software/oracle/admin/igt/adump

find /software/oracle/diag/rdbms/${ORA_INST}/${ORA_INST}/alert -type f  -mtime +${DAYS_TO_KEEP} -exec rm {} \;

#find /software/oracle/diag/rdbms/${ORA_INST}/${ORA_INST}/trace -type f -mtime +${DAYS_TO_KEEP} -exec rm {} \;
#find /software/oracle/diag/rdbms/${ORA_INST}/${ORA_INST}/trace -type f -size +1000M -exec rm {} \;

mv -f  ${LISTENER_TRACE}/lsnr_igt.log_6  ${LISTENER_TRACE}/lsnr_igt.log_7
mv -f  ${LISTENER_TRACE}/lsnr_igt.log_5  ${LISTENER_TRACE}/lsnr_igt.log_6
mv -f  ${LISTENER_TRACE}/lsnr_igt.log_4  ${LISTENER_TRACE}/lsnr_igt.log_5
mv -f  ${LISTENER_TRACE}/lsnr_igt.log_3  ${LISTENER_TRACE}/lsnr_igt.log_4
mv -f  ${LISTENER_TRACE}/lsnr_igt.log_2  ${LISTENER_TRACE}/lsnr_igt.log_3
mv -f  ${LISTENER_TRACE}/lsnr_igt.log_1  ${LISTENER_TRACE}/lsnr_igt.log_2
mv -f  ${LISTENER_TRACE}/lsnr_igt.log    ${LISTENER_TRACE}/lsnr_igt.log_1

mv -f  ${LISTENER_TRACE_2}/listener.log_6  ${LISTENER_TRACE_2}/listener.log_7
mv -f  ${LISTENER_TRACE_2}/listener.log_5  ${LISTENER_TRACE_2}/listener.log_6
mv -f  ${LISTENER_TRACE_2}/listener.log_4  ${LISTENER_TRACE_2}/listener.log_5
mv -f  ${LISTENER_TRACE_2}/listener.log_3  ${LISTENER_TRACE_2}/listener.log_4
mv -f  ${LISTENER_TRACE_2}/listener.log_2  ${LISTENER_TRACE_2}/listener.log_3
mv -f  ${LISTENER_TRACE_2}/listener.log_1  ${LISTENER_TRACE_2}/listener.log_2
mv -f  ${LISTENER_TRACE_2}/listener.log    ${LISTENER_TRACE_2}/listener.log_1

#rm -f /software/oracle/diag/tnslsnr/${LIST_SERVER}/lsnr_igt/alert/*.xml

find $LISTENER_ALERT   -type f -mtime +${DAYS_TO_KEEP_LIST} -exec rm {} \;

find $LISTENER_ALERT_2 -type f -mtime +${DAYS_TO_KEEP_LIST} -exec rm {} \;

find ${LOG_PATH} -type f -mtime +${DAYS_TO_KEEP} -exec rm {} \;

find ${AUDIT_PATH} -type f -mtime +${AUD_DAYS_TO_KEEP} -exec rm {} \;

# Delete ogg dmp files

find /software/ogg/*/dirdmp/ -name '*.dmp' -exec \rm -f {} \; 2>/dev/null

===========================================
Delete old backup log files
===========================================

/home/shdaemon/scripts/clean_backup_logs.sh

#!/bin/bash

DAYS_TO_KEEP=30

BACKUP_EXP_LOGS=/backup/ora_exp/for_backup/old_log

BACKUP_RMAN_LOGS=/backup/ora_online/for_backup/log

EXP_LOG_PREFIX=export_igt

METADATA_EXP_LOG_PREFIX=metadata_export_igt

ONLINE_LOG_PREFIX=rman

find  ${BACKUP_EXP_LOGS}/${EXP_LOG_PREFIX}* -type f  -mtime +${DAYS_TO_KEEP} -exec rm -f {} \;

find  ${BACKUP_EXP_LOGS}/${METADATA_EXP_LOG_PREFIX}* -type f  -mtime +${DAYS_TO_KEEP} -exec rm -f {} \;

find  ${BACKUP_RMAN_LOGS}/${ONLINE_LOG_PREFIX}* -type f  -mtime +${DAYS_TO_KEEP} -exec rm -f {} \;

===========================================

Keep /var/log files under control

===========================================

#!/bin/bash

export DAYS_TO_KEEP=14

export LOG_PATH=/var/spool/clientmqueue

find $LOG_PATH -type f -mtime +${DAYS_TO_KEEP} -exec rm {} \;

export DAYS_TO_KEEP=30

export LOG_PATH=/var/log/aide

find $LOG_PATH -type f -mtime +${DAYS_TO_KEEP} -exec rm {} \;

per folder
cd /software/oracle/; 
du -sh *

find /software/oracle/ -type f -printf '%s %p\n'| sort -nr | head -10