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 )

do

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

fi

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

do

if [ ! -f "$file" ]

then

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

return 1

fi

done

return 0

}

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

if ( ! check_bom )

then

exit 1

fi

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"

fi

# 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

fi

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

fi

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}"

fi

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}."

fi

fi

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

fi

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++ ))

do

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

for U in ${SESSIONS} 0

do

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

fi

done

done

else

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

for U in ${SESSIONS} 0

do

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

fi

done

fi

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} $!"

fi

cnt=1

x=0

echo -ne "NOTIFY: Connecting users \c"

until [ $cnt -gt $SESSIONS ]

do

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

#!/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 ret=""

local connect_string="$1"

sqlplus -L "$connect_string" <<EOF > .test_conn.out 2>&1

SHOW PARAMETER db_name;

EXIT;

EOF

ret=$?

return $ret

}

function grant() {

local user=$1

sqlplus -s "$CONNECT_STRING" <<EOF

SET ECHO ON

ALTER SESSION SET CONTAINER=PDBRSRAC;

DROP USER $user CASCADE;

GRANT CONNECT TO $user IDENTIFIED BY $user;

GRANT DBA TO $user;

EXIT;

EOF

echo "DROP USER $user CASCADE;" >> drop_users.sql

}

function cr_seed () {

grant user0 > /dev/null 2>&1

echo "NOTIFY: Creating and loading seed table."

sqlplus -s user0/user0@${NON_ADMIN_CONNECT_STRING} <<EOF

SET ECHO ON

CREATE TABLE seed

(

custid NUMBER(8),

c2 VARCHAR2(128),

c3 VARCHAR2(128) ,

c4 VARCHAR2(128) ,

c5 VARCHAR2(128) ,

c6 VARCHAR2(128) ,

c7 VARCHAR2(128) ,

c8 VARCHAR2(128) ,

c9 VARCHAR2(128) ,

c10 VARCHAR2(128) ,

c11 VARCHAR2(128) ,

c12 VARCHAR2(128) ,

c13 VARCHAR2(128) ,

c14 VARCHAR2(128) ,

c15 VARCHAR2(128) ,

c16 VARCHAR2(128) ,

c17 VARCHAR2(128) ,

c18 VARCHAR2(128) ,

c19 VARCHAR2(128) ,

c20 VARCHAR2(128)

) PARALLEL CACHE PCTFREE 0 tablespace $TABLESPACE;

DECLARE

nrows NUMBER := 1;

x PLS_INTEGER := 0;

fluff VARCHAR2(128) := 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX';

BEGIN

FOR i IN 1 .. ${SCALE} LOOP

INSERT INTO seed VALUES (nrows, fluff, fluff, fluff, fluff, fluff, fluff, fluff, fluff, fluff, fluff, fluff, fluff, fluff, fluff, fluff, fluff, fluff, fluff, fluff);

nrows := nrows + 1;

x := MOD( nrows, 9973 );

IF ( x = 0 ) THEN

COMMIT;

END IF;

END LOOP;

COMMIT;

END;

/

EXIT;

EOF

echo ""

echo "NOTIFY: Seed table loading procedure has exited."

}

function drop_table() {

local user=$1

local pass=$2

sqlplus -s ${user}/${pass}@${NON_ADMIN_CONNECT_STRING} <<EOF

SET ECHO ON

DROP TABLE cf1 PURGE;

EXIT;

EOF

}

function drop_seed_table() {

sqlplus -s user0/user0@${NON_ADMIN_CONNECT_STRING} <<EOF

SET ECHO ON

DROP TABLE seed PURGE;

EXIT;

EOF

}

function cr_tab_and_load() {

local user=$1

local pass=$2

sqlplus -s ${user}/${pass}@${NON_ADMIN_CONNECT_STRING} <<EOF

SET ECHO ON

CREATE TABLE cf1

(

custid NUMBER(8),

c2 VARCHAR2(128),

c3 VARCHAR2(128) ,

c4 VARCHAR2(128) ,

c5 VARCHAR2(128) ,

c6 VARCHAR2(128) ,

c7 VARCHAR2(128) ,

c8 VARCHAR2(128) ,

c9 VARCHAR2(128) ,

c10 VARCHAR2(128) ,

c11 VARCHAR2(128) ,

c12 VARCHAR2(128) ,

c13 VARCHAR2(128) ,

c14 VARCHAR2(128) ,

c15 VARCHAR2(128) ,

c16 VARCHAR2(128) ,

c17 VARCHAR2(128) ,

c18 VARCHAR2(128) ,

c19 VARCHAR2(128) ,

c20 VARCHAR2(128)

) NOPARALLEL CACHE PCTFREE 99 TABLESPACE $TABLESPACE

STORAGE

(BUFFER_POOL RECYCLE INITIAL 1M NEXT 32K MAXEXTENTS UNLIMITED);

INSERT INTO cf1 SELECT * FROM user0.seed WHERE ROWNUM = 1 ;

COMMIT;

ALTER TABLE cf1 MINIMIZE RECORDS_PER_BLOCK;

TRUNCATE TABLE cf1 ;

COMMIT;

INSERT /*+ PARALLEL APPEND */ INTO cf1 SELECT * FROM user0.seed ORDER BY DBMS_RANDOM.VALUE();

COMMIT;

CREATE UNIQUE INDEX I_CF1 ON cf1(custid) NOPARALLEL PCTFREE 0 TABLESPACE $TABLESPACE;

ALTER INDEX i_cf1 SHRINK SPACE COMPACT;

EXEC DBMS_STATS.GATHER_TABLE_STATS('$user', 'cf1', estimate_percent=>100, block_sample=>TRUE, degree=>2);

EXIT;

EOF

}

function doit() {

local user=$1

local pass=$2

drop_table $user $pass >> drop_table.out 2>&1

cr_tab_and_load $user $pass >> cr_tab_and_load.out 2>&1

}

function test_sqlplus () {

if ( ! type sqlplus > /dev/null 2>&1 )

then

echo "FATAL: Please validate your environment. SQL*Plus is not executable in current \$PATH"

return 1

fi

return 0

}

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

fi

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

do

if [ ! -f "$file" ]

then

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

return 1

fi

done

return 0

}

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

if ( ! check_bom )

then

exit 1

fi

if ( ! test_sqlplus )

then

echo "FATAL: Cannot proceed in current environment."

exit 1

fi

export TABLESPACE="$1"

if [ -z "$1" ]

then

echo "FATAL: ${0} args"

echo "Usage : ${0}: <tablespace name> <optional: number of users>"

exit 1

fi

MAXUSER=`echo "$2" 2>&1 | sed 's/[^0-9]//g' 2> /dev/null`

if [ -z "$MAXUSER" ]

then

MAXUSER=128

fi

if [[ "$MAXUSER" -le 0 || "$MAXUSER" -gt 128 ]]

then

echo "FATAL: Must be non-zero and tested maximum is 128."

echo "Usage : ${0}: <tablespace name> <optional: number of users>"

exit 1

fi

LOAD_PARALLEL_DEGREE=${LOAD_PARALLEL_DEGREE:=1}

SCALE=${SCALE:=10000}

SQLNET_SERVICE_BASE=${SQLNET_BASE:=''}

SQLNET_SERVICE_MAX=${SQLNET_SERVICE_MAX:=''}

ADMIN_SQLNET_SERVICE=${ADMIN_SQLNET_CONNECT:=''}

SYSDBA_PASSWD=${SYSDBA_PASSWD:=''}

export NON_ADMIN_CONNECT_STRING="PDBRSRAC"

export CONNECT_STRING="PDBRSRAC"

source ./slob.conf

rm -f grant.out drop_table.out cr_tab_and_load.out drop_users.sql .test_conn.out

if [ -n "$ADMIN_SQLNET_SERVICE" ]

then

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

export NON_ADMIN_CONNECT_STRING="@${ADMIN_SQLNET_SERVICE}"

else

export CONNECT_STRING="/ as sysdba"

fi

echo "

NOTIFY: Load Parameters (slob.conf):

LOAD_PARALLEL_DEGREE == $LOAD_PARALLEL_DEGREE

SCALE == $SCALE

ADMIN_SQLNET_SERVICE == \"$ADMIN_SQLNET_SERVICE\"

CONNECT_STRING == \"$CONNECT_STRING\"

NON_ADMIN_CONNECT_STRING == $NON_ADMIN_CONNECT_STRING

"

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

if ( ! test_conn "$CONNECT_STRING" )

then

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

echo "FATAL: Error Output:"

echo ""

cat .test_conn.out 2>&1

echo ""

echo "FATAL: Please verify the instance is running and the settings"

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

exit 1

else

echo "NOTIFY: ${0}: Successful test connection: \"sqlplus -L $CONNECT_STRING\""

echo ""

rm -f .test_conn.out

fi

USER=""

PASS=""

cr_seed

cnt=1

x=0

echo -ne "NOTIFY: Setting up user \c"

while [ $cnt -le $MAXUSER ]

do

echo -ne " $cnt \c"

USER=user$cnt

PASS=user$cnt

( grant $USER >> grant.out 2>&1 ; doit $USER $PASS ) &

if [ $x -eq $(( LOAD_PARALLEL_DEGREE - 1 )) ]

then

echo ""

echo "NOTIFY: Waiting for background processes - `date`"

wait

[[ $cnt -ne $MAXUSER ]] && echo -ne "NOTIFY: Setting up user \c"

x=0

else

(( x = $x + 1 ))

fi

(( cnt = $cnt + 1 ))

done

wait

echo ""

doit user0 user0

drop_seed_table

wait

echo "NOTIFY: ${0}: Loading procedure complete (${SECONDS} seconds). Please check ./cr_tab_and_load.out for any errors"

echo ""

[oracle@vnode-119-15 SLOB]$

4. Login to EACH RAC PDB instance (be sure to do this on all of the RAC instances) and run the following as shown below.

SQL> alter session set container=PDBRSRAC;

SQL> alter pluggable database open;

5. Test that you can connect to the PDB using "sqlplus system@PDBRSRAC" (or use sys, or scott, or whatever) from the SLOB RAC node.

6. Follow the usual steps to setup the correct redo log configurations and create the SLOB tablespace etc. as described here and here.

7. Run setup.sh. Verify successful

8. Run SLOB as usual.