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 

fi

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

fi

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

fi

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

   INSTANCE=igt

else

   INSTANCE=$ORACLE_SID

fi

. $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

fi

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

# 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

fi

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

 

sftp script example

sftp script example

sftp user@111.222.333.444 << EOF
cd /some/path/on/target/server
put /some/path/on/from/server/some_file.*.dat
exit
EOF

expdp is slow. AWR showing Streams AQ: enqueue block as main wait event

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

Issue

===============
expdp is running for several hours

AWR is showing Streams AQ: enqueue blocked as main Foreground Wait Event

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 

===============
AWR Evidences

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

Foreground Wait Events                   DB/Inst: IGT/igt  Snaps: 39049-39050
-> s  - second, ms - millisecond -    1000th of a second
-> Only events with Total Wait Time (s) >= .001 are shown
-> ordered by wait time desc, waits desc (idle events last)
-> %Timeouts: value of 0 indicates value was < .5%.  Value of null is truly 0
                                                             Avg
                                        %Time Total Wait    wait    Waits   % DB
Event                             Waits -outs   Time (s)    (ms)     /txn   time
-------------------------- ------------ ----- ---------- ------- -------- ------
Streams AQ: enqueue blocke        1,767     0      2,656    1503      0.2   95.2
db file sequential read          74,898     0          8       0      6.8     .3
log file sync                     2,564     0          2       1      0.2     .1
enq: RO - fast object reus          216     0          1       5      0.0     .0
SQL*Net message to client     2,475,101     0          1       0    224.9     .0
local write wait                    504     0          0       1      0.0     .0
library cache: mutex X            4,931     0          0       0      0.4     .0
SQL*Net more data from cli       21,140     0          0       0      1.9     .0

SQL ordered by Elapsed Time              DB/Inst: IGT/igt  Snaps: 39049-39050

-> Resources reported for PL/SQL code includes the resources used by all SQL

   statements called by the code.

-> % Total DB Time is the Elapsed Time of the SQL statement divided

   into the Total Database Time multiplied by 100

-> %Total - Elapsed Time  as a percentage of Total DB time

-> %CPU   - CPU Time      as a percentage of Elapsed Time

-> %IO    - User I/O Time as a percentage of Elapsed Time

-> Captured SQL account for    3.0% of Total DB Time (s):           2,789

-> Captured PL/SQL account for  445.8% of Total DB Time (s):           2,789

        Elapsed                  Elapsed Time

        Time (s)    Executions  per Exec (s)  %Total   %CPU    %IO    SQL Id

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

         3,536.5              0           N/A  126.8     .6     .0 bja07yskmzxn1

 BEGIN SYS.KUPW$WORKER.MAIN('SYS_EXPORT_FULL_01', 'SHDAEMON', 0); END;

         3,482.5          1,769          1.97  124.9     .1     .0 8rgw5q94paqsg

Module: Data Pump Master

BEGIN sys.kupc$que_int.send(:1, :2, :3); END;

         2,660.5              0           N/A   95.4     .1     .0 a103q4nq8cdj7

 BEGIN SYS.KUPM$MCP.MAIN('SYS_EXPORT_FULL_01', 'SHDAEMON', 0, 0); END;

         2,592.6          1,718          1.51   93.0     .0     .0 87nt40c5wj7y5

Module: Data Pump Master

BEGIN sys.kupc$que_int.put_status(:1, :2, :3); END;

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

What to check

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

1. streams_pool_size Parameter

COL name FOR A30

COL value FOR A30

SELECT name, value 

  FROM V$PARAMETER WHERE name LIKE '%stream%';

NAME                VALUE

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

streams_pool_size   0

2. streams pool size Hidden Parameters

set pagesize 35

set linesize 150

col NAME format a40

col VALUE format a20

col DESCRIPTION format a60

set pause on

set pause 'Hit enter to continue'

SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc DESCRIPTION

FROM x$ksppi x, x$ksppcv y

WHERE x.inst_id = userenv('Instance')

AND y.inst_id = userenv('Instance')

AND x.indx = y.indx

AND SUBSTR(LOWER(x.ksppinm),1,1) = '_'

AND x.ksppinm LIKE '%streams%'

ORDER BY 1;

NAME                                     VALUE                DESCRIPTION

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

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

__streams_pool_size                      33554432             

Actual size in bytes of streams pool

_disable_streams_diagnostics             0                    

streams diagnostics

_disable_streams_pool_auto_tuning        FALSE                

disable streams pool auto tuning

_memory_broker_shrink_streams_pool       900                  

memory broker allow policy to shrink streams pool

_streams_pool_max_size                   0                    

streams pool maximum size when auto SGA enabled

_streams_pool_size is set to 32 Mb which is too small for this big DB

 

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

Solution

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

Although streams_pool_size is set to zero, Oracle does not really tune streams_pool_size.

It just is using the default value of hidden parameter __streams_pool_size.

Option A - Set a high value to streams_pool_size to override Oracle default.

SQL> ALTER SYSTEM SET STREAMS_POOL_SIZE=256M SCOPE=MEMORY;

System altered.

And check results

or

ALTER SYSTEM SET STREAMS_POOL_SIZE=256M SCOPE=SPFILE;

And restart Instance.

Option B - Alter hidden parameter _disable_streams_pool_auto_tuning set value to TRUE. 

This is will disable  Oracle tuning to streams pool size.

ALTER SYSTEM SET "_disable_streams_pool_auto_tuning"=TRUE SCOPE=SPFILE;

And restart Instance.

Backup to pfile:

CREATE PFILE = '/software/oracle/112/dbs/igt_init_pfile_20210202.ora' FROM SPFILE;

File created.

!ls /software/oracle/112/dbs/igt_init_pfile_20210202.ora

/software/oracle/112/dbs/igt_init_pfile_20210202.ora

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

Oracle Metalink #1596645:
====================

Oracle has this issue documented:

EXPDP And IMPDP Slow Performance In 11R2 and 12R1 And Waits On Streams AQ: Enqueue Blocked On Low Memory (Doc ID 1596645.1)

Version: Oracle Release 11.2.0.4 

Issue:

There is high wait for "Streams AQ: enqueue blocked on low memory"

Solution:

Increase streams_pool_size.  Say to 256M

CONNECT / as sysdba

ALTER SYSTEM SET streams_pool_size=256m SCOPE=both;

If above does not help, perform:

ALTER SYSTEM SET "_disable_streams_pool_auto_tuning"=TRUE SCOPE=spfile;

SHUTDOWN IMMEDIATE

STARTUP

If increasing streams_pool_size, you may have to increase your SGA_TARGET or MEMORY_TARGET also.

SGA_TARGET or MEMORY_TARGET should be high enough

SELECT name, value 

  FROM V$PARAMETER 

WHERE name LIKE '%target%';

NAME                           VALUE

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

sga_target                     0

memory_target                  8589934592

memory_max_target              8589934592

SELECT component, 

       ROUND(current_size/1024/1024)current_size_Mb, 

       ROUND(MAX_SIZE/1024/1024)max_size_mb, 

       ROUND(USER_SPECIFIED_SIZE/1024/1024) user_def_size_mb

  FROM V$SGA_DYNAMIC_COMPONENTS;

COMPONENT               CURRENT_SIZE_MB MAX_SIZE_MB USER_DEF_SIZE_MB

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

shared pool                        2944        2944                0

large pool                          640         768                0

java pool                           384         512                0

streams pool                       1152        1152              512

DEFAULT buffer cache              21376       21504                0

KEEP buffer cache                     0           0                0

RECYCLE buffer cache                  0           0                0

DEFAULT 2K buffer cache               0           0                0

DEFAULT 4K buffer cache               0           0                0

DEFAULT 8K buffer cache               0           0                0

DEFAULT 16K buffer cache           6144        6144             6144

DEFAULT 32K buffer cache              0           0                0

Shared IO Pool                      512         512              512

Data Transfer Cache                   0           0                0

In-Memory Area                        0           0                0

ASM Buffer Cache                      0           0                0