SLOB2 (12c RAC PDB Version)

Credit

Kevin Closson is the creator of SLOB and SLOB2. It's an awesome software creation. Thanks Kevin.

Chris Buckel is the creator of SLOB2-harness. It's an awesome software creation. Thanks Chris.

Summary

Attached to this page is an older version of SLOB2 bundled with SLOB2-harness that I use that works with Oracle 12c RAC ASM Flex Cluster PDB's. The only files that are changed from this older vanilla release of SLOB2 on which this is based are the "runit.sh" and the "setup.sh" files. Therefore, although I have not tested it myself yet, it should be possible to modify the runit.sh and setup.sh included with the latest version of SLOB2 which as of this writing is SLOB 2.3. Again, though, at this point I haven't tested that, YMMV.

You should be able to download SLOB 2.4 (or the then-latest version of SLOB2) from Kevin Closson's website for SLOB here. Please note that the recent versions of SLOB (for example 2.4) are no longer redistributable and contain a LICENSE.txt file which needs to be minded. In that way, you can download the current version of SLOB and try out the edits to runit.sh and setup.sh and the below procedure for use with Oracle 12c RAC PDB instances.

Use of SLOB2 with Oracle 12c PDB's

Note that this procedure was used as mentioned above with an older version of SLOB than is currently released by Kevin Closson. It should also work with recent versions but I have not tested it with those yet. Therefore, this may only work with the older version of SLOB attached here.

1. Unzip the SLOB bundle attached to this page on one of the RAC nodes as the "oracle" user (that is, as the linux user that owns the Oracle DB)

2. Add a tnsnames.ora for the PDB as shown below. In this example shown below the CDB is "RSRAC" and the PDB is "PDBRSRAC".

[oracle@vnode-119-15 admin]$ more tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

RSRAC =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = lxc1-scan.gns1.robinsystems.com)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = rsrac.robinsystems.com)

)

PDBRSRAC =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = lxc1-scan.gns1.robinsystems.com)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = pdbrsrac.robinsystems.com)

)

3. Change "PDBYOURS" to the name of your PDB in "runit.sh" and "setup.sh". There is 1 edit in runit.sh and 3 edits in setup.sh as shown below.

The edit to runit.sh is shown in red bold below. Note that these edits may be SLOB-version-specific. They work as intended with this older SLOB version.

[oracle@vnode-119-15 SLOB]$ cat runit.sh

#!/bin/bash

# DISCLAIMER OF WARRANTIES AND LIMITATION OF LIABILITY

# The software is supplied "as is" and all use is at your own risk. Peak Performance Systems disclaims

# all warranties of any kind, either express or implied, as to the software, including, but not limited to,

# implied warranties of fitness for a particular purpose, merchantability or non - infringement of proprietary

# rights. Neither this agreement nor any documentation furnished under it is intended to express or imply

# any warranty that the operation of the software will be uninterrupted, timely, or error - free. Under no

# circumstances shall Peak Performance Systems be liable to any user for direct, indirect, incidental,

# consequential, special, or exemplary damages, arising from or relating to this agreement, the software, or

# user#s use or misuse of the softwares. Such limitation of liability shall apply whether the damages arise

# from the use or misuse of the software (including such damages incurred by third parties), or errors of

# the software.

function test_conn() {

local constring="$*"

local ret=""

sqlplus -L "$constring" <<EOF > /dev/null 2>&1

SELECT COUNT(*) from DUAL;

exit;

EOF

ret=$?

return $ret

}

function wait_pids() {

local pids="$*"

while ( ps -p $pids > /dev/null 2>&1 )

sleep .5

done

}

function check_bom() {

local file=""

if [ ! -f ./misc/BOM ]

then

echo "FATAL: ${0}: ${FUNCNAME}: No BOM file in ./misc. Incorrect SLOB file contents."

return 1

for file in `cat ./misc/BOM | xargs echo`

if [ ! -f "$file" ]

then

echo "FATAL: ${0}: ${FUNCNAME}: Missing ${file}. Incorrect SLOB file contents."

return 1

done

return 0

}

#---------- Main body

if ( ! check_bom )

then

exit 1

rm -f awr.txt awr.*html awr*.gz iostat.out vmstat.out mpstat.out /tmp/db_stats.out db_stats.out sqlplus.out

if [ "$SLOB_DEBUG" = "TRUE" ]

then

SQLPLUS_OUT_FILE="sqlplus.out"

else

SQLPLUS_OUT_FILE="/dev/null"

# Just in case user accidentally deleted lines in slob.conf:

UPDATE_PCT=${UPDATE_PCT:=10}

RUN_TIME=${RUN_TIME:=300}

WORK_LOOP=${WORK_LOOP:=0}

SCALE=${SCALE:=10000}

WORK_UNIT=${WORK_UNIT:=256}

REDO_STRESS=${REDO_STRESS:=HEAVY}

SHARED_DATA_MODULUS=${SHARED_DATA_MODULUS:=0}

ADMIN_SQLNET_SERVICE=${ADMIN_SQLNET_SERVICE:=''}

SQLNET_SERVICE_BASE=${SQLNET_SERVICE_BASE:=''}

SQLNET_SERVICE_MAX=${SQLNET_SERVICE_MAX:=0}

SYSDBA_PASSWD=${SYSDBA_PASSWD:=''}

DO_ROTOR=FALSE

export NON_ADMIN_CONNECT_STRING="PDBRSRAC"

export ADMIN_CONNECT_STRING="/ as sysdba"

source ./slob.conf

if [[ "$#" != 1 || "$1" < 1 ]]

then

echo "FATAL: "

echo "${0}: Usage : ${0} <number of sessions to execute>"

exit 1

SESSIONS=$1

SQLPLUS_PIDS=""

MISC_PIDS=""

B=""

TM=""

CMD=""

if [ ! -x ./mywait ]

then

echo ""

echo "FATAL: ${0}: ./mywait executable not found."

echo "FATAL: Please change directories to ./wait_kit and run make(1)."

echo ""

exit 1

if [ -n "$ADMIN_SQLNET_SERVICE" ]

then

export ADMIN_CONNECT_STRING="sys/${SYSDBA_PASSWD}@${ADMIN_SQLNET_SERVICE} as sysdba"

export NON_ADMIN_CONNECT_STRING="@${SQLNET_SERVICE_BASE}"

if [ -n "$SQLNET_SERVICE_BASE" ]

then

if [ "$SQLNET_SERVICE_MAX" -gt 0 ]

then

echo "NOTIFY: SLOB sessions will connect round-robin from ${SQLNET_SERVICE_BASE}1 through ${SQLNET_SERVICE_BASE}${SQLNET_SERVICE_MAX}."

DO_ROTOR=TRUE

else

echo "NOTIFY: All SLOB sessions will connect to ${SQLNET_SERVICE_BASE}."

echo "NOTIFY:

UPDATE_PCT == $UPDATE_PCT

RUN_TIME == $RUN_TIME

WORK_LOOP == $WORK_LOOP

SCALE == $SCALE

WORK_UNIT == $WORK_UNIT

ADMIN_SQLNET_SERVICE == \"$ADMIN_SQLNET_SERVICE\"

ADMIN_CONNECT_STRING == \"$ADMIN_CONNECT_STRING\"

NON_ADMIN_CONNECT_STRING == \"$NON_ADMIN_CONNECT_STRING\"

SQLNET_SERVICE_MAX == \"$SQLNET_SERVICE_MAX\"

echo "NOTIFY: Testing SYSDBA connectivity to the instance to validate slob.conf settings."

if ( ! test_conn ${ADMIN_CONNECT_STRING} )

then

echo "FATAL: ${0}: cannot connect to the instance."

echo "FATAL: Connect string: \"${ADMIN_CONNECT_STRING}\""

echo "FATAL: Please verify the instance and listener are running and the settings"

echo "FATAL: in slob.conf are correct for your connectivity model."

exit 1

if [ "$DO_ROTOR" = "TRUE" ]

then

echo "NOTIFY: Connections will rotor from ${NON_ADMIN_CONNECT_STRING}1 through ${NON_ADMIN_CONNECT_STRING}${SQLNET_SERVICE_MAX}"

for (( i = 1 ; i <= $SQLNET_SERVICE_MAX ; i++ ))

CONNECT_STRING="@${NON_ADMIN_CONNECT_STRING}${i}"

for U in ${SESSIONS} 0

echo "NOTIFY: Testing non-SYSDBA connectivity to ${CONNECT_STRING}"

if ( ! test_conn "user${U}/user${U}${CONNECT_STRING}" )

then

echo "FATAL: Connect failure user${U}/user${U}${CONNECT_STRING}"

exit 1

done

else

echo "NOTIFY: Testing non-SYSDBA connectivity to instance."

for U in ${SESSIONS} 0

if ( ! test_conn "user${U}/user${U}@${NON_ADMIN_CONNECT_STRING}" )

then

echo "FATAL: Connect failure user${U}/user${U}@${NON_ADMIN_CONNECT_STRING}"

exit 1

done

echo "NOTIFY: Connectivity verified."

sqlplus $ADMIN_CONNECT_STRING @./misc/switchlog > /dev/null 2>&1

echo "NOTIFY: Setting up trigger mechanism."

./create_sem > /dev/null 2>&1

if [ -n "$NO_OS_PERF_DATA" ]

then

else

( iostat -xm 3 > iostat.out ) &

MISC_PIDS="${MISC_PIDS} $!"

( vmstat 3 > vmstat.out ) &

MISC_PIDS="${MISC_PIDS} $!"

( mpstat -P ALL 3 > mpstat.out ) &

MISC_PIDS="${MISC_PIDS} $!"

cnt=1

x=0

echo -ne "NOTIFY: Connecting users \c"

until [ $cnt -gt $SESSIONS ]

echo -ne "${cnt} \c"

CMD="sqlplus -s user${cnt}/user${cnt}@${NON_ADMIN_CONNECT_STRING}"

SLOBARGS="$cnt $UPDATE_PCT $WORK_LOOP $RUN_TIME $SCALE $WORK_UNIT $REDO_STRESS $SHARED_DATA_MODULUS"

( $CMD @slob $SLOBARGS >> $SQLPLUS_OUT_FILE 2>&1 ) &

SQLPLUS_PIDS="${SQLPLUS_PIDS} $!"

(( cnt = $cnt + 1 ))

(( x = $cnt % 7 ))

[[ $x -eq 0 ]] && sleep 1

done

echo ""

echo "NOTIFY: Pausing for $(( cnt / 6 )) seconds before triggering the test."

sleep $(( cnt / 6 ))

sqlplus -L $ADMIN_CONNECT_STRING @awr/awr_snap > /dev/null

echo ""

echo "NOTIFY: Triggering the test."

B=$SECONDS

./trigger > /dev/null 2>&1

echo "NOTIFY: Test triggered. Executing"

wait_pids $SQLPLUS_PIDS

(( TM = $SECONDS - $B ))

kill -9 $MISC_PIDS > /dev/null 2>&1

echo "Tm $TM"

sqlplus -L $ADMIN_CONNECT_STRING @awr/awr_snap > /dev/null 2>&1

sqlplus -L $ADMIN_CONNECT_STRING @awr/create_awr_report > /dev/null 2>&1

( sqlplus -L $ADMIN_CONNECT_STRING @awr/create_awr_report_html > /dev/null 2>&1 ; gzip -f -9 awr.html > /dev/null 2>&1)

( sqlplus -L $ADMIN_CONNECT_STRING @awr/create_awr_report_rac_html > /dev/null 2>&1 ; gzip -f -9 awr_rac.html > /dev/null 2>&1 )

mv /tmp/db_stats.out . > /dev/null 2>&1

echo "NOTIFY: SLOB test is complete."

exit 0

[oracle@vnode-119-15 SLOB]$

Edits to setup.sh are shown in red bold below. Note that these edits could be SLOB-version-specfic. They work as intended with this older SLOB version.

[oracle@vnode-119-15 SLOB]$ cat setup.sh