In Lecture 5 and 6 of our Big Data in 30 Hours hands-on class, we will be experimenting with Data Warehousing and ETL. Our classes are hands-on. Prior to the class you need to install Oracle Database and the client utility Oracle SQL Developer. Here is the brief instruction how to do it on your laptop. I wrote this summary because I found the Oracle documents too detailed.

This instruction is relevant to the recommended laptop setup described here. Most importantly, 64-bit Windows 10, with 16 GB RAM and 124 GB SSD drive. If you have a slightly different setup, do not worry – Oracle works on most platforms with less memory. You should be fine.

Note this summary guide only covers the most necessary stuff. If you get stuck, your fallback plan is to follow the official Oracle documentation here. If you are interested in Oracle architecture, go ahead and read it anyways. Informative, detalied and well-organized.

1. Install Java SDK

Install the latest stable version of Java SDK. At the moment of writing this, it is java 8 (Jdk 1.8), version 8 (update 191). Java SE Development Kit 8u191 can be downloaded here. Download and install it. Then check whether the installation succeeded: open a new commandline window (typing cmd in search bar), and check the java version:
C:\Users\Pawel>java -version
java version "1.8.0_191"
Java(TM) SE Runtime Environment (build 1.8.0_191-b12)
Java HotSpot(TM) 64-Bit Server VM (build 25.191-b12, mixed mode)

2. Install Oracle Database Express Edition

Oracle is WAY more complex beast than another database engine, SQLite which we discussed previously, so give yourself a deep breath. Most importantly, understand that unlike SQLite, Oracle is not just a bunch of files but an active database server with processes that will activate and keep running in your operating system until you turn them down.

Download from Oracle.com and install the Oracle Database Express Edition, the latest stable release. Also called Oracle XE, it is the free version of the database, limited with functionality. Here is the download site. Per today, eventhough the commercial version of Oracle Database comes in version 18c (18.c), the XE for Windows is available in version 11 (Oracle Database 11g Release 2 Express Edition). Download it and run the installer. It will ask questions. Allow standard values for most answers. Also regarding the location, leave the standard C:\oraclexe even though it is not hygienic. Sorry. Regarding the password: enter your password which you will later use to administer your Oracle Database. Do not forget it . You should end up with the success screen, like this.

 

Do not make happy noises yet, but instead verify your installation.
  1. Open Process Manager, (ctrl+alt+esc), and check presence of two Oracle processes: the Oracle RDBMS Kernel Executable is the database kernel, while TNSLSNR is the Oracle Net Listener, a separate process that receives incoming client connection requestsr. This is a good sign. This means the database is up and running.
  2. In Windows cmd, type the following magic to connect to the database
d:\PK\lecture03>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Fri Oct 19 17:09:49 2018
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL>

You just launched the SQL*Plus command line utility and connected to the database as SYS user with SYSDBA role. This means you are root inside Oracle. Therefore – careful with the next steps.

  1. In the command prompt, you can see that you really are connected, by querying some system tables, for instance:
SQL> select count(*) from all_tables;
COUNT(*)
----------
1690
SQL> select username from dba_users;
USERNAME
------------------------------
SYS
SYSTEM
ANONYMOUS
HR
[...]
16 rows selected.

You have just seen that the database came preconfigured with 1690 objects, and a number of users. Give yourself kudos, make the happy noises you delayed until now and proceed to the next step.

Note. What you have is a limited version of Oracle. You will not see some features that came in in version 12, most notably the multitenant architecture (multitenant container database CDB with pluggable databases PDBs), nor the advanced features available in Standard Edition (SE) or Enterprise Edition (EE) , related to high availabiity (Flashback, Data Guard), security (Data Vault), performance (Cache), manageability (Tuning Pack, Diagnostic Pack) and scalability (Oracle Real Application Clusters, or RAC). No worries though. XE has full of stuff, and if you are ever interested, is a good idea to spend a year learning XE 11gR2 before jumping on to EE 18c.

3. Start and stop database

Verify that you are able to start and stop the database processes. First, select stop database item from Start menu, Oracle subfolder. You should see the following console:
C:\WINDOWS\system32>net stop OracleServiceXE
The OracleServiceXE service is stopping.........
The OracleServiceXE service was stopped successfully.

Nice. As proof that the database has stopped, go back to the SQL shell you have still kept open in another window and try to do something:

SQL> select username from dba_users;
select username from dba_users
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 15284
Session ID: 93 Serial number: 119
Or, if you closed the window already, try to open SQL*Plus again same way as you did it before:
d:\PK\lecture03>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Fri Oct 19 17:13:30 2018
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
ERROR:
ORA-12560: TNS:protocol adapter error

Aha. Situation under control. These errors mean that you were able to successfully shut down the database server. Now go back to Start menu, find Oracle folder, click start database, and you should see that the processes are back up in the Process Manager, and this time you are able to connect.

Before proceding, note that Oracle installed itself in a way that both daemon processes start automatically every time you fire up the laptop, and take up some memory for SGA. Maybe you don’t want that. Go to the Services desktop app, set all OracleXX processes for Manual startup type.

4. Set up Oracle SQL Developer

You have connected to the database through the command-line utility SQL*Plus. A nicer way to do this is through the GUI. This is what Oracle SQL Developer gives. Download it here. Pick version without Java VM, because you already have one.
No need to install the SQL Developer, you just unpack and run it. Unpack the stuff to wherever you want it to stay. Inside the unpacked folder, find sqldeveloper.exe and run it. It will ask for Java SDK location. You should then see the screen to the right if the launch is successful.
Now you want to connect to the database server. Click the green plus button under Connections (top left corner). Provide connection name (whatever, for instance “mylocal”), user name “SYS AS SYSDBA” and your Oracle password, same which you did provide during install and hopefully did not
forget. I told you. Leave hostname as is: localhost, port as 1521, as demonstrated on the screenshot.
Click Connect. After connecting successfully to the database, you should see the connection in the left pane expands to the view of various data structures: tables, view, indexes and more. Go ahead and explore. The objects you see are located in the Oracle database server you just connected to..
Note that SQL Developer and SQL*Plus console essentially provide the same connection functionality. They both act as clients to the same server and allow you to explore the same objects and data structures. While SQL*Plus does this in command line, SQL Developer allows GUI-style interaction.
That’s it, you have a working database server and you can connect to it both from GUI and from command line. Well done!
What we need to do next is to populate the database with some interesting data structures we will be working on, and to connect to it remotely. I will cover this in a separate article.
Installing Oracle Database on Windows 10

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.