Skip to content

Oracle

Jump to bottom
stanislawbartkowski edited this page Jun 20, 2021 · 29 revisions

Oracle server installation

Oracle is also available as Docker image.

Download Oracle zip image. For Oracle 18.3 LINUX.X64_180000_db_home.zip file.

https://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html

Read the description : https://github.com/oracle/docker-images/tree/master/OracleDatabase/SingleInstance

Next step is to clone the repository.

git clone https://github.com/oracle/docker-images.git

cd docker-images/OracleDatabase/SingleInstance/dockerfiles

Copy LINUX.X64_180000_db_home.zip to 18.3.0 or 19.3.0 directory.

Run the build command, assuming 18.3.0 version

./buildDockerImage.sh -e -v 18.3.0

Start the container and wait until the database is ready.

docker run --name oracle -e ORACLE_PWD=oracle -p 1521:1521 -p 5500:5500 oracle/database:18.3.0-ee
docker run --name oracle -e ORACLE_PWD=oracle -p 1521:1521 -p 5500:5500 oracle/database:19.3.0-ee

The Oracle base remains unchanged with value /opt/oracle
#########################
DATABASE IS READY TO USE!
#########################
The following output is now a tail of the alert.log:
ORCLPDB1(3):CREATE SMALLFILE TABLESPACE "USERS" LOGGING  DATAFILE  '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf' SIZE 5M REUSE AUTOEXTEND ON NEXT  1280K MAXSIZE UNLIMITED  EXTENT MANAGEMENT LOCAL  SEGMENT SPACE MANAGEMENT  AUTO
ORCLPDB1(3):Completed: CREATE SMALLFILE TABLESPACE "USERS" LOGGING  DATAFILE  '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf' SIZE 5M REUSE AUTOEXTEND ON NEXT  1280K MAXSIZE UNLIMITED  EXTENT MANAGEMENT LOCAL  SEGMENT SPACE MANAGEMENT  AUTO
ORCLPDB1(3):ALTER DATABASE DEFAULT TABLESPACE "USERS"
ORCLPDB1(3):Completed: ALTER DATABASE DEFAULT TABLESPACE "USERS"
2018-12-10T12:47:39.417616+00:00
ALTER SYSTEM SET control_files='/opt/oracle/oradata/ORCLCDB/control01.ctl' SCOPE=SPFILE;
2018-12-10T12:47:39.426540+00:00
ALTER SYSTEM SET local_listener='' SCOPE=BOTH;
   ALTER PLUGGABLE DATABASE ORCLPDB1 SAVE STATE
Completed:    ALTER PLUGGABLE DATABASE ORCLPDB1 SAVE STATE

Oracle client software

https://www.oracle.com/technetwork/database/database-technologies/instant-client/downloads/index.html

Make sqlldr tool available in PATH.

cd /usr/local/bin
ln -s /usr/lib/oracle/19.3/client64/bin/sqlldr

Make sure that connection is working, assuming thinkde hostname and password oracle

sqlplus sys/oracle@//thinkde:1521/ORCLPDB1

sqlplus system/oracle@//thinkde:1521/ORCLPDB1

Prepare performance database/schema

create user perf identified by secret;

GRANT ALL PRIVILEGES TO perf;

Check that user perf can connect and execute queries.

sqlplus perf/secret@//thinkde:1521/ORCLPDB1

SQL> create table x (x int);

Table created.

SQL> insert into x values(1);

1 row created.

SQL> select * from x;

	 X
----------
	 1

SQL> drop table x;

Table dropped.

Performance test configuration

https://github.com/stanislawbartkowski/mytpcds/blob/master/env/oracle.rc

Parameter Description Default value
DBNAME ORCLPDB1
DBUSER perf
DBPASSWORD secret
DBHOST tedious-inf
URL Connection string for sqlplus "$DBUSER/$DBPASSWORD@//$DBHOST:1521/$DBNAME"
JAVADRIVER /usr/lib/oracle/19.3/client64/lib/ojdbc8.jar
DBURL JDBC URL string "jdbc:oracle:thin:@$DBHOST:1521/$DBNAME"
DBTYPE oracle

Query generator for QUALIFY

Prepare oracle.tpl file

define __LIMITA = "select * from (";
define __LIMITB = "";
define __LIMITC = " ) where rownum <= %d";
define _BEGIN = "-- start query " + [_QUERY] + " in stream " + [_STREAM] + " using template " + [_TEMPLATE];
define _END = "-- end query " + [_QUERY] + " in stream " + [_STREAM] + " using template " + [_TEMPLATE];

Generate qualification queries: https://github.com/stanislawbartkowski/mytpcds#qualify-queries

In run.sh, uncomment queryqualification line.

#./tpc.sh querystreams 
pc.sh queryqualification
#./tpc.sh test

./run.sh

...
/mnt/repo/tpc/v2.13.0rc1//query_templates/query97.tpl replace DMS => 1200
/tmp/tmp.TbvB1Y6JIv
/mnt/repo/tpc/v2.13.0rc1//query_templates/query98.tpl replace YEAR => 1999
/mnt/repo/tpc/v2.13.0rc1//query_templates/query98.tpl replace SDATE => 1999-02-22
/mnt/repo/tpc/v2.13.0rc1//query_templates/query98.tpl replace CATEGORY.1 => Sports
/mnt/repo/tpc/v2.13.0rc1//query_templates/query98.tpl replace CATEGORY.2 => Books
/mnt/repo/tpc/v2.13.0rc1//query_templates/query98.tpl replace CATEGORY.3 => Home
/tmp/tmp.TbvB1Y6JIv
/mnt/repo/tpc/v2.13.0rc1//query_templates/query99.tpl replace DMS => 1200
/tmp/tmp.TbvB1Y6JIv
qgen2 Query Generator (Version 2.13.0)
Copyright Transaction Processing Performance Council (TPC) 2001 - 2020
Parsed 99 templates
/tmp/tmp.TbvB1Y6JIv
PASSED

QUALIFY

In Oracle 96 queries are executable, 3 failed because of a connection issue.

QUALIFY RESULT
SQL coverage 97 %
Qualify test MATCHES 93
DIFFER 3
FAILED 3
Clone this wiki locally