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.
1. Install Java SDK
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.
- 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.
- In Windows cmd, type the following magic to connect to the database
d:\PK\lecture03>sqlplus / as sysdba SQL*Plus: Release 188.8.131.52.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 184.108.40.206.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.
- 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
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
d:\PK\lecture03>sqlplus / as sysdba SQL*Plus: Release 220.127.116.11.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.