Oracle DBA by Example. Code samples, Scripts, Reference.: Golden Gate

Showing posts with label Golden Gate. Show all posts

Wednesday, February 7, 2024

Upgrade to Oracle 19.12 from Oracle 19.x by example

Minimum space required:

6Gb for p33494256 and p6880880 patches

16Gb for /software/oracle

Steps:

as root user

mkdir /starhome/iu/workarea/oracle_home_backup

chown oracle:dba /starhome/iu/workarea/oracle_home_backup

mkdir /starhome/iu/workarea/ora_upgrade

chown oracle:dba /starhome/iu/workarea/ora_upgrade

as oracle user

cp -rp $ORACLE_HOME/ /starhome/iu/workarea/oracle_home_backup/

cd /starhome/iu/workarea/ora_upgrade

sftp p6880880_190000_Linux-x86-64.zip ora_upgrade/

unzip -d TOP_6880880 p6880880_190000_Linux-x86-64.zip

cd $ORACLE_HOME/OPatch/

rm -rf *

cd /starhome/iu/workarea/ora_upgrade/TOP_6880880/OPatch

mv ./* $ORACLE_HOME/OPatch/

cd /starhome/iu/workarea/ora_upgrade

unzip -d TOP_33494256 p33494256_190000_Linux-x86-64.zip

cd TOP_33494256/33494256

NOTE - Stop Oracle Server and Listener and Golden Gate before applying patch!!!

$ORACLE_HOME/OPatch/opatch apply -report

$ORACLE_HOME/OPatch/opatch apply

Expected result:

Patch 33494256 successfully applied.

...

OPatch succeeded.

cd /starhome/iu/workarea/ora_upgrade

rm -rf TOP_6880880

rm -rf TOP_33494256

The issue with GG+oracle SE lower than 19.12:
In case using Golden gate on oracle 19.x on SE - there is an error when starting EXTRACT

2024-02-05 13:13:18 ERROR OGG-02030 Failed to set logmining server parameters back to default values.

2024-02-05 13:13:18 ERROR OGG-02042 OCI Error 23605.

The Reason:

Golden gate start the EXTRACT in PARALLEL mode, but parallel functionality is not available in SE, and no way to control this parameter.

The Solution:

Upgrade Oracle SE to 19.12 or higher.

Clean up old patches

Old patches are stored under $ORACLE_HOME/.patch_storage, and can use up space.

By default only 2 patches are required:

- Previous patch, for rollback

- Current Patch

Older patches are not required, and these should be cleaned up manually using OPatch utility:

Step 1. List Inactive Patches

opatch util listorderedinactivepatches

Step 2. Delete Inactive Patches

opatch util deleteinactivepatches

Example:

oracle@orahost:/software/oracle>% cd $ORACLE_HOME/OPatch

oracle@orahost:/software/oracle/1910/OPatch>% ./opatch util listorderedinactivepatches

Oracle Interim Patch Installer version 12.2.0.1.41

Oracle Home : /software/oracle/1910

Central Inventory : /software/oracle/oraInventory

from : /software/oracle/1910/oraInst.loc

OPatch version : 12.2.0.1.41

OUI version : 12.2.0.7.0

Log file location : /software/oracle/1910/cfgtoollogs/opatch/opatch2024-02-12_04-26-16AM_1.log

Invoking utility "listorderedinactivepatches"

List Inactive patches option provided

The oracle home has the following inactive patch(es) and their respective overlay patches:

The number of RU chains is 1

***** There are 2 inactive RU patches in chain 1

-Inactive RU/BP 29517242:Database Release Update : 19.3.0.0.190416 (29517242), installed on: Thu Apr 18 07:21:17 UTC 2019, with no overlays

-Inactive RU/BP 32819074:Database Release Update Revision : 19.10.2.0.0 (32819074), installed on: Wed Oct 20 14:09:36 UTC 2021, with no overlays

-Active RU/BP 33494256:Database Release Update Revision : 19.12.2.0.220118 (33494256), installed on: Mon Feb 12 04:19:12 UTC 2024, with no overlays

OPatch succeeded.

oracle@orahost:/software/oracle/1910/OPatch>% ./opatch util deleteinactivepatches

Oracle Interim Patch Installer version 12.2.0.1.41

Oracle Home : /software/oracle/1910

Central Inventory : /software/oracle/oraInventory

from : /software/oracle/1910/oraInst.loc

OPatch version : 12.2.0.1.41

OUI version : 12.2.0.7.0

Log file location : /software/oracle/1910/cfgtoollogs/opatch/opatch2024-02-12_04-27-54AM_1.log

Invoking utility "deleteinactivepatches"

Inactive Patches Cleanup option provided

Delete Inactive Patches .......

***** There are 2 inactive RU patches in chain 1

***** 1 inactive patches will be deleted

-To be deleted inactive RU/BP 29517242:Database Release Update : 19.3.0.0.190416 (29517242), installed on: Thu Apr 18 07:21:17 UTC 2019, with no overlays

-To be retained inactive RU/BP 32819074:Database Release Update Revision : 19.10.2.0.0 (32819074), installed on: Wed Oct 20 14:09:36 UTC 2021, with no overlays

-Active RU/BP 33494256:Database Release Update Revision : 19.12.2.0.220118 (33494256), installed on: Mon Feb 12 04:19:12 UTC 2024, with no overlays

Do you want to proceed? [y|n]

User Responded with: Y

Deleted RU/BP patch: 29517242

OPatch succeeded.

Saturday, November 19, 2022

Golden Gate Datapump not working after crash start. "Showing Current status: In recovery[2]: At EOF" and "No active extraction maps."

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

Issue

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

Database server was stopped and was restarted

After that , Golden Gate Datapump Extract is not running.
There is no error, but the Datapump Extract is constantly in recovery.

And it not updating the second node.

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

In General

================
When running INFO DPM_EXTRACT DETAIL and checking the log file:

Switching to next trail file /software/ogg/191/dirdat/01/out/ei000009314

Rolling over remote file /software/ogg/191/dirdat/01/in/ei000009159

dirdat/01/out/ei000009314 - It is s the file on THIS server. It is written by EXTRACT process. Can double check by looking into the report file for EXTRACT(Recovery completed for target file /software/ogg/191/dirdat/01/out/ei000009314, at RBA 1538)

191/dirdat/01/in/ei000009159 - It is the file on REMOTE server. It is written by the DATAPUMP on the REMOTE server, and read by REPLICAT on the REMOTE server as well.

Can double check by looking into the REPLICAT report file on remote server:

Switching to next trail file /software/ogg/191/dirdat/01/in/ei000009159

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

By Example

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

> SEND DPM_S_01 STATS

Sending STATS request to EXTRACT DPM_S_01 ...

No active extraction maps.

> SEND DPM_S_01 STATUS

Sending STATUS request to EXTRACT DPM_S_01 ...

EXTRACT DPM_S_01 (PID 9275)

Current status: In recovery[2]: At EOF

Current read position:

Sequence #: 10177

RBA: 212360562

Timestamp: 2022-11-19 13:10:36.000000

Extract Trail: /software/ogg/191/dirdat/01/out/es

Current write position:

Sequence #: 10177

RBA: 0

Timestamp: 2022-11-14 16:19:13.265526

Extract Trail: /software/ogg/191/dirdat/01/in/es

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

Solution

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

In General:

1. Query the main extract for current Sequence and RBA.

2. Update the Data Pump Extract with the values from step 1.

3. In case it still does not work, do ALTER EXTRACT MAIN_EXTRACT ETROLLOVER and repeat steps 1+2.

ALTER EXTRACT MAIN_EXTRACT ETROLLOVER instructs main extract to switch writing to a new file. It might be that the previous file got somehow corrupted during database server sudden shutdown.

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

By Example

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

EXT_S_01 - is the main extract

DPM_S_01 - is the data pump extract

> INFO EXT_S_01 SHOWCH

EXTRACT EXT_S_01 Last Started 2022-11-19 13:09 Status RUNNING

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

Process ID 12516

Log Read Checkpoint Oracle Integrated Redo Logs

2022-11-19 20:02:51

SCN 6.1510356821 (27280160597)

Current Checkpoint Detail:

Read Checkpoint #1

Oracle Integrated Redo Log

Startup Checkpoint (starting position in the data source):

Timestamp: 2022-09-04 06:54:43.000000

SCN: 0.0 (0)

Recovery Checkpoint (position of oldest unprocessed transaction in the data source):

Timestamp: 2022-11-19 20:02:43.000000

SCN: 6.1510354581 (27280158357)

Current Checkpoint (position of last record read in the data source):

Timestamp: 2022-11-19 20:02:51.000000

SCN: 6.1510356821 (27280160597)

BR Previous Recovery Checkpoint:

Timestamp: 2022-11-19 13:09:17.917578

SCN: 0.0 (0)

BR Begin Recovery Checkpoint:

Timestamp: 2022-11-19 17:08:13.000000

SCN: 6.1507821110 (27277624886)

BR End Recovery Checkpoint:

Timestamp: 2022-11-19 17:09:32.000000

SCN: 6.1507838612 (27277642388)

Write Checkpoint #1

GGS Log Trail

Current Checkpoint (current write position):

Sequence #: 10177

RBA: 212375522

Timestamp: 2022-11-19 20:02:53.678942

Extract Trail: /software/ogg/191/dirdat/01/out/es

Seqno Length: 9

Flip Seqno Length: No

Trail Type: EXTTRAIL

Header:

Version = 2

Record Source = A

Type = 18

# Input Checkpoints = 1

# Output Checkpoints = 1

Configuration:

Data Source = 3

Transaction Integrity = 1

Task Type = 0

Status:

Start Time = 2022-11-19 13:09:25

Last Update Time = 2022-11-19 20:02:53

Stop Status = A

Last Result = 0

> STOP EXTRACT DPM_S_01

> ALTER EXTRACT DPM_S_01 EXTSEQNO 10177

> ALTER EXTRACT DPM_S_01 EXTRBA 212375522

> START EXTRACT DPM_S_01

Datapump still showing that it is not working.

> SEND DPM_S_01 STATS
Sending STATS request to EXTRACT DPM_S_01 ...
No active extraction maps.

In case it still does not work, do ALTER EXTRACT MAIN_EXTRACT ETROLLOVER and repeat steps 1+2.

> STOP  EXTRACT EXT_S_01
> ALTER EXTRACT EXT_S_01 ETROLLOVER
> START EXTRACT EXT_S_01
> INFO EXTRACT EXT_S_01 SHOWCH
  Current Checkpoint (current write position):
    Sequence #: 10178
    RBA: 1464
    Timestamp: 2022-11-19 20:50:25.993536
    Extract Trail: /software/ogg/191/dirdat/01/out/es
    Seqno Length: 9
    Flip Seqno Length: No
    Trail Type: EXTTRAIL

> STOP EXTRACT DPM_S_01
> ALTER EXTRACT DPM_S_01 EXTSEQNO 10178
> ALTER EXTRACT DPM_S_01 EXTRBA 1464
> START EXTRACT DPM_S_01
> INFO EXTRACT DPM_S_01

EXTRACT    DPM_S_01  Last Started 2022-11-19 20:51   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:03 ago)
Process ID           16893
Log Read Checkpoint  File /software/ogg/191/dirdat/01/out/es000010178
                     First Record  RBA 1464

> INFO ALL
Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     DPM_I_01    00:00:00      00:00:07
EXTRACT     RUNNING     DPM_P_01    00:00:00      00:00:01
EXTRACT     RUNNING     DPM_S_01    00:00:00      00:00:07
EXTRACT     RUNNING     EXT_I_01    00:00:02      00:00:04
EXTRACT     RUNNING     EXT_P_01    00:00:02      00:00:01
EXTRACT     RUNNING     EXT_S_01    00:00:03      00:00:01
REPLICAT    RUNNING     REP_I_01    00:00:00      00:00:03
REPLICAT    RUNNING     REP_P_01    00:00:05      00:00:02
REPLICAT    RUNNING     REP_S_01    00:00:07      00:00:01
> SEND DPM_S_01 STATS

Sending STATS request to EXTRACT DPM_S_01 ...

Start of Statistics at 2022-11-19 21:46:50.

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

Extracting from SOME_USER.SGA_W_PSMS_SUBSCRIBER to SOME_USER.SGA_W_PSMS_SUBSCRIBER:

*** Total statistics since 2022-11-19 21:12:34 ***
        Total inserts                                      0.00
        Total updates                                      2.00
        Total deletes                                      0.00
        Total upserts                                      0.00
        Total discards                                     0.00
        Total operations                                   2.00

*** Daily statistics since 2022-11-19 21:12:34 ***
        Total inserts                                      0.00
        Total updates                                      2.00
        Total deletes                                      0.00
        Total upserts                                      0.00
        Total discards                                     0.00
        Total operations                                   2.00

*** Hourly statistics since 2022-11-19 21:12:34 ***
        Total inserts                                      0.00
        Total updates                                      2.00
        Total deletes                                      0.00
        Total upserts                                      0.00
        Total discards                                     0.00
        Total operations                                   2.00

*** Latest statistics since 2022-11-19 21:12:34 ***
        Total inserts                                      0.00
        Total updates                                      2.00
        Total deletes                                      0.00
        Total upserts                                      0.00
        Total discards                                     0.00
        Total operations                                   2.00

End of Statistics.

Sunday, June 27, 2021

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.

Thursday, February 18, 2021

GG Restart from crontab

=========================
General
=========================

Restart GG processes from crontab

=========================
Code
=========================
crontab entry
1 06 1 * * /software/oracle/oracle/scripts/gg_restart/gg_restart_all_groups.sh

gg_restart_all_groups.sh
#!/bin/bash

WORK_HOME=/software/oracle/oracle/scripts/gg_restart

LOG_FILE=gg_restart.log

cd $WORK_HOME

./gg_restart_log.sh "${WORK_HOME}" "${LOG_FILE}" "START"

./gg_restart_group.sh EXT_S "ext ext_s*" "${WORK_HOME}" "${LOG_FILE}"

./gg_restart_group.sh EXT_P "ext ext_p*" "${WORK_HOME}" "${LOG_FILE}"

./gg_restart_group.sh EXT_I "ext ext_i*" "${WORK_HOME}" "${LOG_FILE}"

./gg_restart_group.sh DPM_S "ext dpm_s*" "${WORK_HOME}" "${LOG_FILE}"

./gg_restart_group.sh DPM_P "ext dpm_p*" "${WORK_HOME}" "${LOG_FILE}"

./gg_restart_group.sh DPM_I "ext dpm_i*" "${WORK_HOME}" "${LOG_FILE}"

./gg_restart_group.sh REP_S "rep rep_s*" "${WORK_HOME}" "${LOG_FILE}"

./gg_restart_group.sh REP_P "rep rep_p*" "${WORK_HOME}" "${LOG_FILE}"

./gg_restart_group.sh REP_I "rep rep_i*" "${WORK_HOME}" "${LOG_FILE}"

./gg_restart_log.sh "${WORK_HOME}" "${LOG_FILE}" "FINISH"

gg_restart_log.sh

#!/bin/bash

WORK_HOME=$1

LOG_FILE_NAME=$2

VERB=$3

cd $WORK_HOME

LOG_FILE=${WORK_HOME}/${LOG_FILE_NAME}

RUN_DATE=`date "+%Y%m%d"_"%H%M%S"`

touch $LOG_FILE

if [[ $VERB == "START" ]]; then

echo "" >>$LOG_FILE

echo ========================================== >>$LOG_FILE

echo Start at $RUN_DATE >>$LOG_FILE

echo ========================================== >>$LOG_FILE

echo "" >>$LOG_FILE

echo "Before Restart: " >>$LOG_FILE

echo "" >>$LOG_FILE

free -m >> $LOG_FILE

echo "" >> $LOG_FILE

sar -r >> $LOG_FILE

echo "" >>$LOG_FILE

if [[ $VERB == "FINISH" ]]; then

echo "" >>$LOG_FILE

echo "---------------------------" >>$LOG_FILE

echo "After Restart: " >>$LOG_FILE

echo "---------------------------" >>$LOG_FILE

echo "" >>$LOG_FILE

free -m >> $LOG_FILE

echo "" >> $LOG_FILE

sar -r >> $LOG_FILE

echo "" >>$LOG_FILE

echo ========================================== >>$LOG_FILE

echo Finished at $RUN_DATE >>$LOG_FILE

echo ========================================== >>$LOG_FILE

echo "" >>$LOG_FILE

exit 0

gg_restart_group.sh

#!/bin/bash

GROUP_NAME=$1

RESTART_GROUP=$2

WORK_DIR=$3

LOG_FILE_NAME=$4

#GROUP_NAME=EXT_S

#RESTART_GROUP="ext ext_s*"

LOG_FILE=${WORK_DIR}/${LOG_FILE_NAME}

clear

echo "Restarting ${GROUP_NAME} ${RESTART_GROUP}" >> ${LOG_FILE}

echo "Log File is: ${LOG_FILE}"

exit 0

. /etc/profile > /dev/null 2> /dev/null

TEMP_LOG_FILE="/tmp/restart_temp.tmp"

RUN_DATE=`date "+%Y%m%d"_"%H%M%S"`

# Setting Oracle GoldenGate environments

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

OGGSH=$INFRA_ROOT/oggsh

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

INSTANCE=igt

else

INSTANCE=$ORACLE_SID

. $OGGSH/gg_login.sh $INSTANCE

touch $LOG_FILE

touch $TEMP_LOG_FILE

echo "" >>$LOG_FILE

echo ========================================== >>$LOG_FILE

echo Start Restart for ${GROUP_NAME} group ${RESTART_GROUP} at $RUN_DATE >>$LOG_FILE

echo ========================================== >>$LOG_FILE

echo "" >>$LOG_FILE

echo ========================================== >>$LOG_FILE

echo Initial State >>$LOG_FILE

echo ========================================== >>$LOG_FILE

echo "" >>$LOG_FILE

$GG_HOME/ggsci >$TEMP_LOG_FILE <<EOD

info all

exit

EOD

cat $TEMP_LOG_FILE | grep ${GROUP_NAME} >>$LOG_FILE

echo about to do "stop ext $RESTART_GROUP" >>$LOG_FILE

$GG_HOME/ggsci >$TEMP_LOG_FILE <<EOF

stop ${RESTART_GROUP}

exit

EOF

cat $TEMP_LOG_FILE >>$LOG_FILE

echo "" >>$LOG_FILE

echo ========================================== >>$LOG_FILE

echo After Stop >>$LOG_FILE

echo ========================================== >>$LOG_FILE

echo "" >>$LOG_FILE

sleep 10

$GG_HOME/ggsci >$TEMP_LOG_FILE <<EOD

info all

exit

EOD

cat $TEMP_LOG_FILE | grep ${GROUP_NAME} >>$LOG_FILE

echo "Starting ${RESTART_GROUP}" >> $LOG_FILE

$GG_HOME/ggsci >$TEMP_LOG_FILE <<EOF

start ${RESTART_GROUP}

exit

EOF

cat $TEMP_LOG_FILE >>$LOG_FILE

echo "" >>$LOG_FILE

echo ========================================== >>$LOG_FILE

echo After Start >>$LOG_FILE

echo ========================================== >>$LOG_FILE

echo "" >>$LOG_FILE

sleep 10

$GG_HOME/ggsci >$TEMP_LOG_FILE <<EOD

info all

exit

EOD

cat $TEMP_LOG_FILE | grep ${GROUP_NAME} >>$LOG_FILE

NOT_RUNNING=`grep STOPPED $TEMP_LOG_FILE |grep ${GROUP_NAME} | wc -l`

if [ $NOT_RUNNING -gt 0 ]; then

echo "Some Processes were not started. Attempting restart" >>$LOG_FILE

$GG_HOME/ggsci >$TEMP_LOG_FILE <<EOF

start ${RESTART_GROUP}

exit

EOF

cat $TEMP_LOG_FILE >>$LOG_FILE

#============================================================

# Second restart

#============================================================

sleep 10

$GG_HOME/ggsci >$TEMP_LOG_FILE <<EOD

info all

exit

EOD

cat $TEMP_LOG_FILE | grep ${GROUP_NAME} >>$LOG_FILE

NOT_RUNNING=`grep STOPPED $TEMP_LOG_FILE | wc -l`

if [ $NOT_RUNNING -gt 0 ]; then

echo "Some Processes were not started. Attempting restart" >>$LOG_FILE

$GG_HOME/ggsci > $TEMP_LOG_FILE <<EOF

start ${RESTART_GROUP}

exit

EOF

cat $TEMP_LOG_FILE >>$LOG_FILE

sleep 10

$GG_HOME/ggsci >$TEMP_LOG_FILE <<EOD

info all

exit

EOD

cat $TEMP_LOG_FILE | grep ${GROUP_NAME} >>$LOG_FILE

echo "" >>$LOG_FILE

echo ========================================== >>$LOG_FILE

echo Finished Restart for $GROUP_NAME $RESTART_GROUP at $RUN_DATE >>$LOG_FILE

echo ========================================== >>$LOG_FILE

echo "" >>$LOG_FILE

Monday, January 18, 2021

Memory Usage Monitoring script. For SWAP using processes, Golden Gate, Top 20 oracle processes.

crontab

0,15,30,45 * * * * /software/oracle/oracle/scripts/memory_usage/gg_mem_usage_wrapper.sh

gg_mem_usage_wrapper.sh

#!/bin/bash

WORK_DIR=/software/oracle/oracle/scripts/memory_usage

LOG_FILE=gg_memory.log

RUN_DATE=`date +"%Y%m%d"_"%H%M%S"`

cd ${WORK_DIR}

touch ${LOG_FILE}

echo " " >> ${LOG_FILE}

echo "=================" >> ${LOG_FILE}

echo ${RUN_DATE} >> ${LOG_FILE}

./gg_mem_usage.sh >> ${LOG_FILE}

echo " " >> ${LOG_FILE}

free -m >> ${LOG_FILE}

echo " " >> ${LOG_FILE}

echo "Top 20 Processes" >> ${LOG_FILE}

ps -eo rss,pid,euser,lstart,args:100 --sort %mem | grep -v grep | sort -n | tail -20 | awk '{printf $1/1024 "MB"; $1=""; print }' >> ${LOG_FILE}

echo " " >> ${LOG_FILE}

echo "Top SWAP Using Processes" >> ${LOG_FILE}

echo " " >> ${LOG_FILE}

echo "SWAP Usage from free -m" >> ${LOG_FILE}

free -m | grep Swap >> ${LOG_FILE}

echo " " >> ${LOG_FILE}

echo "SWAP Usage from /proc" >> ${LOG_FILE}

find /proc -maxdepth 2 -path "/proc/[0-9]*/status" -readable -exec awk -v FS=":" '{process[$1]=$2;sub(/^[ \t]+/,"",process[$1]);} END {if(process["VmSwap"] && process["VmSwap"] != "0 kB") printf "%10s %-30s %20s\n",process["Pid"],process["Name"],process["VmSwap"]}' '{}' \; | awk '{print $(NF-1),$0}' | sort -hr | head | cut -d " " -f2- >> ${LOG_FILE}

echo " " >> ${LOG_FILE}

exit

gg_mem_usage.sh

#!/bin/bash

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

# determine the OS type

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

OSNAME=`uname`

case "$OSNAME" in

"SunOS")

echo "OSNAME = $OSNAME"

;;

"Linux")

echo "OSNAME = $OSNAME"

;;

"*")

echo "This script has not been verified on $OSNAME"

exit 1

;;

esac

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

# set the temp file

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

TMPFILE=/tmp/pmem.tmp

if [ -f $TMPFILE ]

then

rm -f $TMPFILE

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

# loop over the gg process types

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

PROCESSES="extract replicat"

for PROCESS in $PROCESSES

FLAG=""

FLAG=`ps -ef | grep $PROCESS`

if [ -z "FLAG" ]

then

echo "No $PROCESS processes found"

else

echo

echo "#####################################"

echo "# Individual $PROCESS Process Usage #"

echo "#####################################"

case "$OSNAME" in

"Linux")

ps -C $PROCESS -O rss > $TMPFILE

cat $TMPFILE | grep $PROCESS | awk '{print $2/1024, "MB", $12}' | sort -k 2

;;

"SunOS")

ps -efo vsz,uid,pid,ppid,pcpu,args | grep -v grep | grep $PROCESS > $TMPFILE

cat $TMPFILE | grep $PROCESS | awk '{print $1/1024, "MB", $8}' | sort -k 2

;;

"*")

echo "This script has not been verified on $OSNAME"

exit 1

;;

esac

rm -f $TMPFILE

echo

echo "#####################################"

echo "# Total $PROCESS Process Usage #"

echo "#####################################"

case "$OSNAME" in

"Linux")

ps -C $PROCESS -O rss > $TMPFILE

cat $TMPFILE | grep $PROCESS | awk '{count ++; sum=sum+$2; } END \

{ print "Number of processes =",count; \

print "AVG Memory usage/process =",sum/1024/count, "MB"; \

print "Total memory usage =", sum/1024, " MB"}'

;;

"SunOS")

ps -efo vsz,uid,pid,ppid,pcpu,comm | grep -v grep | grep $PROCESS > $TMPFILE

cat $TMPFILE | awk '{count ++; sum=sum+$1; } END \

{ print "Number of processes =",count; \

print "AVG Memory usage/process =",sum/1024/count, "MB"; \

print "Total memory usage =", sum/1024, " MB"}'

;;

"*")

echo "This script has not been verified on $OSNAME"

exit 1

;;

esac

rm -f $TMPFILE

done

exit

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

Sample output

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

20210118_181502

OSNAME = Linux

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

# Individual extract Process Usage #

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

38.7109 MB DPM_I_01

42.125 MB DPM_P_01

41.8672 MB DPM_S_01

70.8867 MB EXT_I_01

78.9297 MB EXT_P_01

361.016 MB EXT_S_01

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

# Total extract Process Usage #

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

Number of processes = 6

AVG Memory usage/process = 105.589 MB

Total memory usage = 633.535 MB

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

# Individual replicat Process Usage #

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

32.4219 MB REP_I_01

37.2383 MB REP_P_01

34.8789 MB REP_S_01

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

# Total replicat Process Usage #

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

Number of processes = 3

AVG Memory usage/process = 34.8464 MB

Total memory usage = 104.539 MB

total used free shared buffers cached

Mem: 64175 62568 1607 9373 384 57889

-/+ buffers/cache: 4294 59880

Swap: 10239 122 10117

Top 20 Processes

361.016MB 30432 oracle Sat Jan 16 20:46:19 2021 /software/ogg/1212/extract PARAMFILE /software/ogg/1212/dirprm/ext_s_01.prm REPORTFILE /software/ogg/1212/dirrpt/EXT_S_01.rpt PROCESSID EXT_S_01 USESUBDIRS

371.129MB 30853 oracle Sat Jan 16 20:46:24 2021 ora_ms03_igt

392.086MB 30851 oracle Sat Jan 16 20:46:24 2021 ora_ms02_igt

488.648MB 28170 oracle Sat Jan 16 20:46:05 2021 ora_smon_igt

512.262MB 28454 oracle Sat Jan 16 20:46:10 2021 oracleigt (LOCAL=NO)

607.949MB 28162 oracle Sat Jan 16 20:46:05 2021 ora_mman_igt

682.445MB 28392 oracle Sat Jan 16 20:46:10 2021 oracleigt (LOCAL=NO)

724.746MB 29669 oracle Sat Jan 16 20:46:13 2021 oracleigt (LOCAL=NO)

972.762MB 30606 oracle Sat Jan 16 20:46:19 2021 oracleigt (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

1046.21MB 29663 oracle Sat Jan 16 20:46:13 2021 oracleigt (LOCAL=NO)

1173.61MB 32712 oracle Sat Jan 16 20:46:47 2021 oracleigt (LOCAL=NO)

1208.2MB 20575 oracle Sun Jan 17 09:59:59 2021 oracleigt (LOCAL=NO)

1278.24MB 28830 oracle Sat Jan 16 20:46:11 2021 oracleigt (LOCAL=NO)

1934.22MB 30150 oracle Sat Jan 16 20:46:16 2021 oracleigt (LOCAL=NO)

2697.98MB 28386 oracle Sat Jan 16 20:46:10 2021 oracleigt (LOCAL=NO)

3556.99MB 28390 oracle Sat Jan 16 20:46:10 2021 oracleigt (LOCAL=NO)

5853.8MB 28388 oracle Sat Jan 16 20:46:10 2021 oracleigt (LOCAL=NO)

5938.82MB 28443 oracle Sat Jan 16 20:46:10 2021 oracleigt (LOCAL=NO)

6263.12MB 28298 oracle Sat Jan 16 20:46:10 2021 oracleigt (LOCAL=NO)

6995.96MB 28164 oracle Sat Jan 16 20:46:05 2021 ora_dbw0_igt

Top SWAP Using Processes

SWAP Usage from free -m

Swap: 10239 122 10117

SWAP Usage from /proc

30649 splunkd 49264 kB

8260 vxconfigd 24152 kB

Tuesday, November 17, 2020

Replicat is showing: "No active replication maps."

======================
General
======================
The issue:

Data not replicating from server_39 to server_38
Replicat is showing: "No active replication maps."

======================
Details
======================
MANAGER RUNNING
EXTRACT RUNNING DPM_I_01 00:00:00 00:00:09
EXTRACT RUNNING DPM_P_01 00:00:00 00:00:04
EXTRACT RUNNING DPM_S_01 00:00:03 00:00:09
EXTRACT RUNNING EXT_I_01 00:00:01 00:00:09
EXTRACT RUNNING EXT_P_01 00:00:02 00:00:04
EXTRACT RUNNING EXT_S_01 00:00:01 00:00:09
REPLICAT RUNNING REP_I_01 00:00:00 00:00:01
REPLICAT RUNNING REP_P_01 00:00:00 00:00:00
REPLICAT RUNNING REP_S_01 00:00:04 00:00:03
=================================================================
Checking the EXTRACT on server 39, looks OK:
SEND EXTRACT EXT_I_01 STATS
Extracting from MY_USER.IPN_INVALIDATE_TRIGGER to MY_USER.IPN_INVALIDATE_TRIGGER:
*** Hourly statistics since 2020-11-17 11:00:00 ***
Total inserts 1.00
Total updates 0.00
Total deletes 1.00
Total upserts 0.00
Total discards 0.00
Total operations 2.00

But on server 38, the REPLICAT is not getting any input:
GGSCI (server_38) 2> SEND REPLICAT REP_I_01 STATS
Sending STATS request to REPLICAT REP_I_01 ...
No active replication maps.

check input files on 38
Nothing is coming for process for REPICAT REP_I_01
-rw-r----- 1 oracle dba 0 Oct 29 09:48 ei000000000
-rw-r----- 1 oracle dba 20633 Nov 16 14:25 disc01i.txt
-rw-r----- 1 oracle dba 58431 Nov 16 14:25 disc01s.txt
-rw-r----- 1 oracle dba 13947 Nov 16 14:25 disc01p.txt
-rw-r----- 1 oracle dba 21743643 Nov 17 11:47 es000000003
-rw-r----- 1 oracle dba 21219127 Nov 17 11:47 ep000000003

GGSCI (server_38) 5> INFO REP_I_01
REPLICAT REP_I_01 Last Started 2020-11-17 11:55 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:08 ago)
Process ID 15605
Log Read Checkpoint File /software/ogg/191/dirdat/01/in/ei000000001
First Record RBA 0

REPLICAT is looking for flie /software/ogg/191/dirdat/01/in/ei000000001
But this file does not exists.
But this file does not exists.
Checking datapump on 39 server

GGSCI (server_39) 6> SEND EXTRACT DPM_I_01 STATS
Sending STATS request to EXTRACT DPM_I_01 ...
No active extraction maps.
The Extract definition:
ADD EXTRACT DPM_I_01 EXTTRAILSOURCE $GG_HOME/dirdat/01/out/ei
ADD RMTTRAIL $GG_HOME/dirdat/01/in/ei EXTRACT DPM_I_01
START EXTRACT DPM_I_01
Seems that the Datapump on server 39 is not getting input.
Thus nothing is sent to server 38.

DROP + CREATE Datapump - Does not work
To drop + recreate, can create do following steps, but it does not help:
To drop EXTRACT
DBLOGIN USERID OGG, PASSWORD YYY
STOP EXTRACT DPM_I_01
DELETE EXTRACT DPM_I_01
ADD EXTRACT DPM_I_01 EXTTRAILSOURCE /software/ogg/191/dirdat/01/out/ei
ADD RMTTRAIL /software/ogg/191/dirdat/01/in/ei EXTRACT DPM_I_01
GGSCI (server_39 as ogg@orainst) 10> INFO DPM_I_01
EXTRACT DPM_I_01 Last Started 2020-11-17 12:50 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:08 ago)
Process ID 18315
Log Read Checkpoint File /software/ogg/191/dirdat/01/out/ei000000000
First Record RBA 0
======================
Solution
======================
ETROLLOVER to Extract + Update to Datapump
DBLOGIN USERID XXX, PASSWORD YYY
ALTER EXTRACT EXT_I_01, ETROLLOVER
ALTER EXTRACT DPM_I_01, EXTSEQNO 10, EXTRBA 0
GGSCI (server_39 as ogg@orainst) 4> STOP EXTRACT EXT_I_01
Sending STOP request to EXTRACT EXT_I_01 ...
Request processed.
GGSCI (server_39 as ogg@orainst) 6> ALTER EXTRACT EXT_I_01, ETROLLOVER
2020-11-17 14:21:01 INFO OGG-01520 Rollover performed.
For each affected output trail of Version 10 or higher format,
after starting the source extract,
issue ALTER EXTSEQNO for that trail's reader
(either pump EXTRACT or REPLICAT) to move the reader's scan to the new trail file;
it will not happen automatically.
EXTRACT altered.
GGSCI (server_39 as ogg@orainst) 12> INFO EXT_I_01 DETAIL
EXTRACT EXT_I_01 Initialized 2020-11-16 14:26 Status RUNNING
Checkpoint Lag 00:00:03 (updated 00:02:50 ago)
Process ID 27583
Log Read Checkpoint Oracle Integrated Redo Logs
2020-11-17 14:20:37
SCN 2.949429219 (9539363811)
Target Extract Trails:
Trail Name Seqno RBA Max MB Trail Type
/software/ogg/191/dirdat/01/out/ei 2506 0 500 EXTTRAIL

GGSCI (server_39 as ogg@orainst) 14> STOP EXTRACT DPM_I_01
  GGSCI (server_39 as ogg@orainst) 15> ALTER EXTRACT DPM_I_01, EXTSEQNO 2506 EXTRBA 0
EXTRACT altered.
GGSCI (server_39 as ogg@orainst) 17> START DPM_I_01
Sending START request to MANAGER ...
EXTRACT DPM_I_01 starting
GGSCI (server_39 as ogg@orainst) 18> INFO DPM_I_01
EXTRACT DPM_I_01 Last Started 2020-11-17 14:24 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:03 ago)
Process ID 27776
Log Read Checkpoint File /software/ogg/191/dirdat/01/out/ei000002506
First Record RBA 0
Now Datapump is advancing.
GGSCI (server_39 as ogg@orainst) 19> INFO DPM_I_01
EXTRACT DPM_I_01 Last Started 2020-11-17 14:24 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:04 ago)
Process ID 27776
Log Read Checkpoint File /software/ogg/191/dirdat/01/out/ei000002506
2020-11-17 14:25:07.000000 RBA 8905

on server 38
Now the file on the remote server is being created: ei000000000
oracle@server_38:/software/ogg/191/dirdat/01/in>% ls -ltr
total 45884
-rw-r----- 1 oracle dba 58431 Nov 16 14:25 disc01s.txt
-rw-r----- 1 oracle dba 13947 Nov 16 14:25 disc01p.txt
-rw-r----- 1 oracle dba 20850 Nov 17 11:55 disc01i.txt
-rw-r----- 1 oracle dba 22915034 Nov 17 14:28 es000000003
-rw-r----- 1 oracle dba 13214 Nov 17 14:28 ei000000000
-rw-r----- 1 oracle dba 23709458 Nov 17 14:28 ep000000003

================================
Next step -Troubleshoot REPLICAT on server 38
================================
GGSCI (server_38) 2> SEND REPLICAT REP_I_01 STATS
Sending STATS request to REPLICAT REP_I_01 ...
No active replication maps.
GGSCI (server_38) 8> INFO REP_I_01
REPLICAT REP_I_01 Last Started 2020-11-17 14:29 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:03 ago)
Process ID 18863
Log Read Checkpoint File /software/ogg/191/dirdat/01/in/ei000000001
First Record RBA 0
But...
The actual file is : /software/ogg/191/dirdat/01/in/ei000000000
Need manually to set REPLICAT to look into the correct file
GGSCI (server_38) 10> STOP REPLICAT REP_I_01
Sending STOP request to REPLICAT REP_I_01 ...
Request processed.
GGSCI (server_38) 15> ALTER REPLICAT REP_I_01, EXTSEQNO 0
2020-11-17 14:35:09 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 (server_38) 16> START REPLICAT REP_I_01
Sending START request to MANAGER ...
REPLICAT REP_I_01 starting
GGSCI (server_38) 17> INFO REPLICAT REP_I_01
REPLICAT REP_I_01 Last Started 2020-11-17 14:35 Status RUNNING
Checkpoint Lag 00:00:15 (updated 00:00:01 ago)
Process ID 32390
Log Read Checkpoint File /software/ogg/191/dirdat/01/in/ei000000000
2020-11-17 14:35:07.001959 RBA 20771
Now, REPICAT is reading records.
GGSCI (server_38) 45> SEND REPLICAT REP_I_01 STATS
Sending STATS request to REPLICAT REP_I_01 ...
Start of Statistics at 2020-11-17 14:36:08.
Replicating from OGG.GG_HEARTBEAT_SEED to OGG.GG_HEARTBEAT:
*** Total statistics since 2020-11-17 14:35:21 ***
Total inserts 0.00
Total updates 15.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 15.00
*** Daily statistics since 2020-11-17 14:35:21 ***
Total inserts 0.00
Total updates 15.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 15.00
*** Hourly statistics since 2020-11-17 14:35:21 ***
Total inserts 0.00
Total updates 15.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 15.00
*** Latest statistics since 2020-11-17 14:35:21 ***
Total inserts 0.00
Total updates 15.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 15.00
Replicating from OGG.GG_HEARTBEAT_SEED to OGG.GG_HEARTBEAT_HISTORY:
*** Total statistics since 2020-11-17 14:35:21 ***
Total inserts 0.00
Total updates 15.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 15.00
*** Daily statistics since 2020-11-17 14:35:21 ***
Total inserts 0.00
Total updates 15.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 15.00
*** Hourly statistics since 2020-11-17 14:35:21 ***
Total inserts 0.00
Total updates 15.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 15.00
*** Latest statistics since 2020-11-17 14:35:21 ***
Total inserts 0.00
Total updates 15.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 15.00
Replicating from MY_USER.IPN_INVALIDATE_TRIGGER to MY_USER.IPN_INVALIDATE_TRIGGER:
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total ignores 2.00
Total operations 1.00
*** Daily statistics since 2020-11-17 14:35:21 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total ignores 2.00
Total operations 1.00
*** Hourly statistics since 2020-11-17 14:35:21 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total ignores 2.00
Total operations 1.00
*** Latest statistics since 2020-11-17 14:35:21 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total ignores 2.00
Total operations 1.00
End of Statistics.

Now Replicat is finally getting input from the other server.
Issue Fixed!

All processes are working OK

GGSCI (server_38) 2> INFO ALL

*** Total statistics since 2020-11-17 14:35:21 ***

Pages

Wednesday, February 7, 2024

Upgrade to Oracle 19.12 from Oracle 19.x by example

Saturday, November 19, 2022

Golden Gate Datapump not working after crash start. "Showing Current status: In recovery[2]: At EOF" and "No active extraction maps."

Sunday, June 27, 2021

REPLICAT is stuck at same point

Thursday, February 18, 2021

GG Restart from crontab

Monday, January 18, 2021

Memory Usage Monitoring script. For SWAP using processes, Golden Gate, Top 20 oracle processes.

Tuesday, November 17, 2020

Replicat is showing: "No active replication maps."