14 Essential Steps for Oracle 12c Load Testing using SLOB on All-Flash Block Storage

Testing – whether QA Automation services, testing accelerators or test automation tools, require hands on expertise and deep knowledge. The purpose of this blog is to document on how SLOB can be used for load testing on Oracle 12c database. The guide is specific to Oracle 12c using SLOB on CentOS 7.4.

Configuration used:
Below is the setup we used for experimenting Oracle12c using SLOB.
VM (ESXi) with CentOS release 7.4.x installed (Stand-alone setup)
Below are the versions of software used:

1. OS version : Centos 7.4.1708
2. Oracle 12c: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit
3. SLOB version: Slob_2.4.2.1

Prerequisites:
Below are some the prerequisites before we proceed for Oracle 12c and SLOB installation.

• Yum package management application must be installed.
• Root or sudo access to the install machines.
• At-least 2GB of RAM.

Oracle 12c Installation:
Oracle database is one of the most widely used relational database management systems (RDBMS) in enterprise environments. Developed, maintained, and supported by Oracle Corporation.
This section covers 14 key steps on how can you setup and install Oracle 12c Release 2 on CentOS 7 VM.
Setting up Oracle 12c on ESXi VM running CentOS 7

Step 1 – Install required Packages

Installation of several packages required by Oracle database, like GCC, Binutils, Glibc etc. Hence we will install them from the Centos repository using yum command. Install packages required by Oracle database with yum

yum update -y
yum install -y binutils.x86_64 compat-libcap1.x86_64 gcc.x86_64 gcc-c++.x86_64 glibc.i686 glibc.x86_64 glibc-devel.i686 glibc-devel.x86_64 ksh compat-libstdc++-33 libaio.i686 libaio.x86_64 libaio-devel.i686 libaio-devel.x86_64 libgcc.i686 libgcc.x86_64 libstdc++.i686 libstdc++.x86_64 libstdc++-devel.i686 libstdc++-devel.x86_64 libXi.i686 libXi.x86_64 libXtst.i686 libXtst.x86_64 make.x86_64 sysstat.x86_64 zip unzip smartmontools
[root@oracledb ~]# cat /etc/hosts
10.20.178.69 oracledb

Step 2 – Configure User and System

Create new groups named ‘oinstall‘ and ‘dba‘, then create a new user ‘oracle‘ and add it to the ‘oinstall’ group.

groupadd oinstall
groupadd dba
useradd -g oinstall -G dba oracle
passwd oracle
TYPE THE PASSWORD

Edit ‘sysctl.conf‘ file with vim & update the below details,

vim /etc/sysctl.conf
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048586

Save the file and exit the editor.
Now run the commands below to display all kernel parameter and apply the new values.

sysctl -p
sysctl -a

Edit ‘limits.conf‘ file with vim. Update the below & save the file and exit the editor.

vim /etc/security/limits.conf
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
oracle soft stack 10240

Step 3 – Configure Desktop

Install X Window System with yum command below.

yum groupinstall -y "X Window System"

When the installation is done, open a new terminal and connect to the server as oracle user with the ssh command option below

ssh -X [email protected]

Step 4 – Download Oracle Database

If you do not have an Oracle account yet, register one and then login to download Oracle 12c for Linux.
Below are my oracle files,

[root@oracledb ~]# mkdir ~/oracle
[root@oracledb ~]# cd ~/oracle/
[root@oracledb oracle]# ll
total 3372752
-rw-r--r--. 1 root root 3453696911 May 13 14:25 linuxx64_12201_database.zip

Extract the Oracle files to a new directory named ‘stage‘.

[root@oracledb oracle]# unzip linuxx64_12201_database.zip -d /stage/

Change owner and group of the ‘/stage/’ directory to the oracle user and oinstall group.

[root@oracledb oracle]# chown -R oracle:oinstall /stage/

Step 5 – create new directories for the Oracle installation files and Oracle database files

Storage Configuration:
Created 2 vdisks of 100GB, 1TB on CentOS 7 VM - ESXi Datastore created using Storage volume.
fdisk /dev/sdb - 100G
fdisk /dev/sdc - 1TB
mkfs -t xfs /dev/sdb1
mkfs -t xfs /dev/sdc1
mkdir -p /u01 /u02
mount /dev/sdb1 /u01
mount /dev/sdc1 /u02
[root@oracledb ~]# cat /etc/fstab
/dev/sdb1  /u01  xfs   defaults   0 0
/dev/sdc1  /u02  xfs   defaults   0 0

Directory ‘/u01‘ is for the Oracle installation files and ‘/u02‘ for the Oracle database files.
Change owner and group of the new directories and set permissions to ‘755’.

[root@oracledb ~]# chown -R oracle:oinstall /u01 /u02
[root@oracledb ~]# chmod -R 775 /u01 /u02
[root@oracledb ~]# chmod g+s /u01 /u02

Oracle is downloaded and the new directories for the installation are ready.

Step 6 – Install Oracle Database 12c

Open a new terminal and connect to the CentOS 7 server using the oracle user with the ssh command below.

ssh -X [email protected]
Go to the stage database directory and run the installer file.
[oracle@oracledb ~]$ cd /stage/database/
[oracle@oracledb database]$ ./runInstaller
Starting Oracle Universal Installer...
Checking Temp space: must be greater than 500 MB.   Actual 37229 MB    Passed
Checking swap space: must be greater than 150 MB.   Actual 3967 MB    Passed
Checking monitor: must be configured to display at least 256 colors.    Actual 16777216    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2019-05-16_02-59-35PM. Please wait ...[oracle@oracledb database]$

And you will see the GUI installation screen below, there click ‘Next‘.







During installation process, you will be asked to execute some scripts as root.

[root@oracledb ~]# cd /u01/app/oraInventory/
[root@oracledb oraInventory]# ./orainstRoot.sh
Changing permissions of /u01/app/oraInventory.
Adding read,write permissions for group.
Removing read,write,execute permissions for world.
Changing groupname of /u01/app/oraInventory to oinstall.
The execution of the script is complete.
[root@oracledb ~]# cd /u01/app/oracle/product/12.2.0/dbhome_1/
[root@oracledb dbhome_1]# ./root.sh
Performing root user operation.
The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME=  /u01/app/oracle/product/12.2.0/dbhome_1
Enter the full pathname of the local bin directory: [/usr/local/bin]:
Copying dbhome to /usr/local/bin ...
Copying oraenv to /usr/local/bin ...
Copying coraenv to /usr/local/bin ...
Creating /etc/oratab file...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Do you want to setup Oracle Trace File Analyzer (TFA) now ? yes|[no] :
no
Oracle Trace File Analyzer (TFA - User Mode) is available at :
/u01/app/oracle/product/12.2.0/dbhome_1/suptools/tfa/release/tfa_home/bin/tfactl
OR
Oracle Trace File Analyzer (TFA - Daemon Mode) can be installed by running this script :
/u01/app/oracle/product/12.2.0/dbhome_1/suptools/tfa/release/tfa_home/install/roottfa.sh

Click ‘Close‘.

Step 7 – Testing

The Oracle installation is finished and ready for testing. I will try to access Oracle from the command line first (sqlplus)
Log in to the server and access the oracle user.

[root@oracledb ~]# su - oracle

Add the following lines to .bash_profile file

[oracle@oracledb ~]$ cat .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
export ORACLE_SID=msys
export ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
Reloading .bash_profile to apply the new settings
[oracle@oracledb ~]$ source .bash_profile
Note: Exit out of the terminal & re-login as oracle user.

Testing to make sure Sqlplus is working fine.

[oracle@oracledb ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Thu May 16 16:33:48 2019
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL>

Next, try to access the web-based ‘Oracle Enterprise Manager‘. Open your web browser and type the https URL below on port 5500. Use the IP of your server!
https://10.20.178.69:5500/em/
Login with user ‘system‘, and the password you have setup as ‘Administrative Password‘ in step 6 Pic4.
Note:
When web browser is not accessible, need to allow connections from outside the server, you will need to open the following ports:

# firewall-cmd --zone=public --add-port=1521/tcp --add-port=5500/tcp --add-port=5520/tcp --add-port=3938/tcp --permanent
# firewall-cmd --reload

After login, you will see the ‘Oracle Enterprise Manager’ dashboard below.

The Oracle Database 12c installation and configuration is done.
Oracle database utility and Oracle Enterprise manager are working.

Step 8 – Installing SLOB [The Simple Database I/O Testing Toolkit for Oracle Database]

Download the latest SLOB package from the below link.
https://kevinclosson.net/slob/
Extract the file using oracle user and continue to use oracle user to setup SLOB,

[oracle@oracledb ~]$ ls
2018.04.16.slob_2.4.2.1.tar.gz
[oracle@oracledb ~]$ tar xvzf 2018.04.16.slob_2.4.2.1.tar.gz
[oracle@oracledb ~]$ ls
2018.04.16.slob_2.4.2.1.tar.gz  SLOB

[oracle@oracledb ~]$ cd SLOB/wait_kit/
[oracle@oracledb wait_kit]$ make all
rm -fr *.o mywait trigger create_sem
cc     -c -o mywait.o mywait.c
cc -o mywait mywait.o
cc     -c -o trigger.o trigger.c
cc -o trigger trigger.o
cc     -c -o create_sem.o create_sem.c
cc -o create_sem create_sem.o
cp mywait trigger create_sem ../
rm -fr *.o

Step 9 – SLOB Configurations

Setup already has Database created in the name“msys” (per step 6 – Pic4 above). Configure the slob.conf file which is located in the SLOB directory

#### SLOB 2.4.0 slob.conf - sample file I used
UPDATE_PCT=50  <== 100% read (0% update), 70/30 read/write (30% update), 50/50 read/write (50% update)
SCAN_PCT=0
RUN_TIME=172800  <== count in seconds that SLOB will run
WORK_LOOP=0
SCALE=8G
SCAN_TABLE_SZ=1M
WORK_UNIT=64
REDO_STRESS=HEAVY
LOAD_PARALLEL_DEGREE=2
SQLNET_SERVICE_BASE=msys   <== mention the database name
SYSDBA_PASSWD="Passw0rd"  <== Administrative Password' created in step 6 Pic4.

 Step10: Create tablespaces in MSYS database.

[oracle@oracledb ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Thu May 16 16:55:56 2019
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> select instance_name, instance_role, database_status from v$instance;
INSTANCE_NAME    INSTANCE_ROLE      DATABASE_STATUS
---------------- ------------------ -----------------
msys             PRIMARY_INSTANCE   ACTIVE
SQL> create bigfile tablespace slobdata datafile '/u02/msys/slobdata.dbf' size 128G autoextend on;
Tablespace created.

Step11: Create Database Users and Object for SLOB using user oracle

Note: In this example we are creating 128 SLOB schemas, each with 8GB of data.  The size of each schema is controlled by the SCALE parameter in the slob.conf file.  128  8GB schemas will yield 1TB of test data.

[oracle@oracledb SLOB]$ ./setup.sh SLOBDATA 128
SLOB 2.4.0
NOTIFY  : 2019.05.16-17:40:31 :
NOTIFY  : 2019.05.16-17:40:31 : Begin SLOB setup.
NOTIFY  : 2019.05.16-17:40:31 : ADMIN_CONNECT_STRING: "system/Passw0rd"
NOTIFY  : 2019.05.16-17:40:31 : Load parameters from slob.conf:
SCALE: 8G (1048576 blocks)
SCAN_TABLE_SZ: 1M (128 blocks)
LOAD_PARALLEL_DEGREE: 2
ADMIN_SQLNET_SERVICE: ""
SYSDBA_PASSWD: "Passw0rd"
DBA_PRIV_USER: "system"
Note: setup.sh will use the following connect strings as per slob.conf:
Admin Connect String: "system/Passw0rd"
Non-Admin Connect String: " "
NOTIFY  : 2019.05.16-17:40:31 : Testing Admin connect using "sqlplus -L system/Passw0rd"
NOTIFY  : 2019.05.16-17:40:32 : Dropping prior SLOB schemas. This may take a while if there is a large number of old schemas.
NOTIFY  : 2019.05.16-17:40:32 : Previous SLOB schemas have been removed
NOTIFY  : 2019.05.16-17:40:32 : Preparing to load 128 schema(s) into tablespace: SLOBDATA
NOTIFY  : 2019.05.17-07:12:18 : Loading user1 schema
NOTIFY  : 2019.05.17-07:22:42 : Finished loading, indexing and gathering statistics on user1 schema in 624 seconds
NOTIFY  : 2019.05.17-07:22:42 : Commencing multiple, concurrent schema creation and loading
NOTIFY  : 2019.05.17-07:22:43 : Waiting for background batch 1. Loading up to user3
Output EOL:
NOTIFY  : 2019.05.17-09:57:04 : Waiting for background batch 64. Loading up to user128
NOTIFY  : 2019.05.17-09:59:00 : Finished background batch 64. Load / index create / stats gather in 116 seconds
NOTIFY  : 2019.05.17-09:59:00 : Completed concurrent data loading phase: 9378 seconds
NOTIFY  : 2019.05.17-09:59:00 : Creating SLOB procedure
NOTIFY  : 2019.05.17-09:59:01 : SLOB procedure created
NOTIFY  : 2019.05.17-09:59:01 : Row and block counts for SLOB table(s) reported in ./slob_data_load_summary.txt
NOTIFY  : 2019.05.17-09:59:01 : Please examine ./slob_data_load_summary.txt for any possbile errors
NOTIFY  : 2019.05.17-09:59:01 :
NOTIFY  : 2019.05.17-09:59:01 : NOTE: No errors detected but if ./slob_data_load_summary.txt shows errors then
NOTIFY  : 2019.05.17-09:59:01 : examine /home/oracle/SLOB/cr_tab_and_load.out
NOTIFY  : 2019.05.17-09:59:01 : SLOB setup complete. Total setup time:  (10004 seconds)

 Step12: Install STATSPACK as SLOB will need to use it.

The script is located at ~$ORACLE_HOME/rdbms/admin

SQL> @/u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/spcreate.sql
Session altered.
Choose the PERFSTAT user's password
-----------------------------------
Not specifying a password will result in the installation FAILING
Enter value for perfstat_password: Passw0rd
Passw0rd
Choose the Default tablespace for the PERFSTAT user
---------------------------------------------------
Below is the list of online tablespaces in this database which can
store user data.  Specifying the SYSTEM tablespace for the user's
default tablespace will result in the installation FAILING, as
using SYSTEM for performance data is not supported.
Choose the PERFSTAT users's default tablespace.  This is the tablespace
in which the STATSPACK tables and indexes will be created.
TABLESPACE_NAME                CONTENTS
------------------------------ ---------------------
STATSPACK   DEFAULT   TABLESPACE
----------------------------
SLOBDATA                       PERMANENT
SYSAUX                         PERMANENT
*USERS  PERMANENT
Pressing <return> will result in STATSPACK's recommended default
tablespace (identified by *) being used.
Enter value for default_tablespace: SLOBDATA
Using tablespace SLOBDATA as PERFSTAT default tablespace.
Choose the Temporary tablespace for the PERFSTAT user
-----------------------------------------------------
Below is the list of online tablespaces in this database which can
store temporary data (e.g. for sort workareas).  Specifying the SYSTEM
tablespace for the user's temporary tablespace will result in the
installation FAILING, as using SYSTEM for workareas is not supported.
Choose the PERFSTAT user's Temporary tablespace.
TABLESPACE_NAME                CONTENTS              DB DEFAULT TEMP TABLESPACE
------------------------------ --------------------- --------------------------
TEMP  TEMPORARY *
Pressing <return> will result in the database's default Temporary
tablespace (identified by *) being used.
Enter value for temporary_tablespace:
Using tablespace TEMP as PERFSTAT temporary tablespace.
Output EOL:
Package created.
No errors.
Creating Package Body STATSPACK...
Package body created.
No errors.
NOTE:
SPCPKG complete. Please check spcpkg.lis for any errors.

 Step13: Starting SLOB test
To run SLOB, we use the runit.sh script in the SLOB directory.  In this example I am going to use all of the 128 schemas I created, and specify one thread per schema:

[oracle@oracledb SLOB]$ ./runit.sh -s 128 -t 1
NOTIFY  : 2019.05.17-10:18:21 : Test has been triggered. Processes are executing.
NOTIFY  : 2019.05.17-10:18:21 : List of monitored sqlplus PIDs written to /tmp/.SLOB.2019.05.17.101722/18481.f_wait_pids.out.
NOTIFY  : 2019.05.17-10:18:31 : Waiting for 172787 seconds before monitoring running processes (for exit).
NOTIFY  : 2019.05.20-14:49:09 : SLOB test is complete.
NOTIFY  : 2019.05.20-14:49:09 : Cleaning up SLOB temporary directory (/tmp/.SLOB.2019.05.20.141756).

 Step14: We can inspect this AWR report to see the I/O observed by Oracle.
You can run AWR reports to see statistics about database performance. Log in with SQLPlus and run:

@$ORACLE_HOME/rdbms/admin/awrrpt.sql

Example below:

[oracle@kubeworker2 SLOB]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Mon May 20 16:09:32 2019
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql
Type Specified: html
Final EOL output: Report written to awrrpt_1_1_7.html

 The above HTML file can be opened in web browser

 
Conclusion:
SLOB is a great tool for testing Oracle DB I/O performance.
Created and tested with enough data to exceed the database and stress the array performance.
Use AWR to see what Oracle DB observed as I/O performance.
References:
https://www.howtoforge.com/tutorial/how-to-install-oracle-database-12c-on-centos-7/
https://gruffdba.wordpress.com/2017/03/04/testing-emc-unity-storage-performance-with-slob/