Operating System

The 64 bit version of the minimal install of Centos 6.5 is used for the RepAttack operating system. This is a minimal install with no X Windows software or graphical user interface. The ISO image used for the install can be found by following the links on the Centos isoredirect page.

Operating System Accounts

Oracle Express Edition

Oracle Express Edition has already been installed via it's RPM package using all default values.
An additional tablespace called REPOE_DATA has been added to hold the application data used by RepAttack. 

It can be downloaded from the Oracle Database Express Edition 11g Release 2 downloads page. It requires you to sign up for a (free) Oracle Account if you don't already have one.

Oracle Databases

Each machine has the default Oracle XE database installed as it's default configuration. The default database installed with Oracle XE always has the ORACLE_SID of XE but to make it less confusing between the source and target databases, two TNS alias have been set up in the tnsnames.ora files of both the source and target machines.

ttorcl_src =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = source)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )
ttorcl_trg =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = target)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

Database User Passwords

These are the same on both the source and target databases. The passwords are set not to expire and are case sensitive in lowercase.

ARCHIVELOG Mode

One of the requirements for Dbvisit Replicate is that the source database be in ARCHIVELOG mode. The database on the RepAttack source machine has been placed into ARCHIVELOG mode and is using the USE_DB_RECOVERY_FILE_DEST as it's archive destination which is /u01/app/oracle/fast_recovery_area and has been sized at 10 Gb.

The database on the RepAttack target machine is not in ARCHIVELOG mode.

SwingBench

SwingBench is a free load generator and benchmark utility designed by Dominic Giles to stress test an Oracle database (10g,11g,12c).  SwingBench version 2.5.0.932 has been installed on the RepAttack source machine in /u01/app/oracle/swingbench. RepAttack uses SwingBench to generate test data and provide a transaction load for the Dbvisit Replicate. 

REPOE Schema

The RepAttack machines come pre-loaded with an application schema and test data generated by SwingBench that you'll be using to perform the RepAttack exercises.

The schema is called REPOE and it's based on the Order Entry (OE) example schema that comes with Oracle. It's Entity Relationship diagram is shown below.

Image Added

Table Size and Row Count

The data in the tables has been randomly generated by the SwingBench using the oewizard command with the following arguments. The data in the tables has been automatically generated so it may look a little strange. 

oewizard -cl -create -cs //source/XE -scale 0.5 -u repoe -p repoe -ts users -tc 7 -v -dba system -dbap manager

The total data size (table segments) of the 11 tables is 616 Mb while there are 27 indexes totalling 351Mb. All tables and indexes reside in the REPOE_DATA tablespace.

Note that the maximum amount of user data in an Oracle XE 11g database cannot exceed 11 Gb. If the user data grows beyond this limit, then an ORA-12592 error will appear.

Indexes

There are 27 indexes across the 11 tables totalling 351Mb in size, including one function based index CUST_UPPER_NAME_IX on the CUSTOMERS table, and eight REVERSE key indexes.

Sequences

There are 5 sequences in the schema named ADDRESS_SEQ, CARD_DETAILS_SEQ, CUSTOMER_SEQ, LOGON_SEQ, and ORDERS_SEQ that are used by their respective tables' primary key fields. 

Views

There are two views in the schema. PRODUCT_PRICES and PRODUCTS.

Stored Procedures

There is one stored procedure, a package called ORDERENTRY which is used to generate transaction activity against the tables.

Constraints

The schema has 4 DEFERRABLE check constraints. Deferrable constraints have a bearing on data loading and what is rolled back in the event of an error.

Helper Scripts

A couple of helper scripts have been included with the virtual machines in the $HOME/replicate directory of the oracle user to assist you with running the SwingBench transaction generator and determining the records counts between the source and target schemas.

record_count.bash

In the $HOME/replicate directory of the oracle user on the source machine is a script called record_count.bash. This script performs a row count from all the tables in the REPOE schema from both the source and target databases.

It also computes the total of all orders in the ORDERS table. This script is useful as a high level audit of the status of the replication.

For example, this is the output of the script at the start of RepAttack. The source schema (REPOE) has been created with the tables and loaded with the initial data, while the target schema (also called REPOE) has been created but contains no tables.

[oracle@source replicate]$ ./record_count.bash
TABLE_NAME                     TTORCL_SRC  TTORCL_TRG
------------------------------ ----------- -----------
ADDRESSES                      750000      *No Table*
CARD_DETAILS                   750000      *No Table*
CUSTOMERS                      500000      *No Table*
INVENTORIES                    900131      *No Table*
LOGON                          1191492     *No Table*
ORDERENTRY_METADATA            4           *No Table*
ORDERS                         714895      *No Table*
ORDER_ITEMS                    2143687     *No Table*
PRODUCT_DESCRIPTIONS           1000        *No Table*
PRODUCT_INFORMATION            1000        *No Table*
WAREHOUSES                     1000        *No Table*
11 rows selected.

Sum of orders         TTORCL_SRC         TTORCL_TRG
------------- ------------------ ------------------
ORDERS         $3,572,944,731.00               $.00
[oracle@source replicate]$

run_swingbench.bash

In the $HOME/replicate directory of the oracle user on the source machine is a script called run_swingbench.bash. This script is used on the source machine to generate DML transactions against the REPOE schema so Dbvisit Replicate has something to do.

SwingBench is a very configurable utility and it has a large number of parameters. The run_swingbench.bash script looks like:

#/bin/bash
#  -cs <connect_string>
#  -u <schema_user>
#  -p <schema_user_password>
#  -uc <simulated_user_count>
#  -min <min_transaction_think_time_in_milliseconds>
#  -max <max_transaction_think_time_in_milliseconds>
#  -rt <runtime_hh:mi>
#  -a run automatically
#  -v display run statistics

/u01/app/oracle/swingbench/bin/charbench -cs //source/XE -u repoe -p repoe -uc 10 -min 5 -max 200 -rt 0:10 -a -v

As you can see, it produces a transaction load for 10 minutes, simulating 10 users against the repoe schema in the source database, with a random transaction think time (delay) between 5 and 200 milliseconds.