Oracle Database 18c, often simply called Oracle for short, is one of the three most popular enterprise-class DBMS packages. Oracle is a high quality, genuinely enterprise-class DBMS that is both well known and well respected.
Oracle provides Oracle database software in various editions, including a free edition called Oracle Database Express Edition, which we call Oracle XE or simply Oracle for short in this documentation. Oracle XE may be deployed in production and used at no charge, including commercial use if desired. Although the limits of Oracle XE make it an unlikely choice for a primary DBMS for GIS work, using Oracle XE is a great way to build skills and insights into using unlimited, commercial Oracle database servers. This documentation, therefore, uses Oracle XE as an example.
The main page for Oracle downloads at the time of this writing is at https://www.oracle.com/technetwork/database/database-technologies/express-edition/downloads/index.html - Downloads require registering as a member of the Oracle Technology Network, which is free to do.
Tech Tip: Oracle is a highly sophisticated, enterprise class DBMS. The Oracle topics in this documentation provide a very simplified installation that can be used to show how Manifold can connect to Oracle. Many aspects of real life Oracle deployment are not covered. Oracle tends to be an overwhelmingly complex system for individuals new to DBMS. MySQL is a better choice for individual users who are seeking a DBMS repository for their GIS data.
Oracle XE is free, but it does come with significant limitations compared to other Oracle editions:
Main memory - The database engine can use a maximum of 2 GB of main memory.
Database size - 12 GB is the maximum amount of user data on disk. However, using basic/advanced compression can push real capacity to near 40 GB.
No support - Oracle XE is a community supported edition and is not supported by Oracle tech support.
One installation - Only one Oracle XE is allowed per logical environment.
Three pluggable databases - Oracle XE allows up to three pluggable databases within the overall container database.
2 Cores - Oracle XE can only utilize 2 CPU cores.
However, Oracle XE imposes no limit to the number of simultaneous users, and Oracle XE includes native spatial data support.
As a practical matter, the above limitations will mean that for GIS use Oracle XE is limited primarily by the 12 GB limit on user data size. We can enable compression to store more than 12 GB, but that becomes inconvenient compared to using, say, MySQL, which has no such limits. The limit to 2 GB of main memory will also impact performance when working with individual objects (and hence the geometry field size of individual records) that exceed the 2 GB main memory maximum.
However, Oracle XE remains a reasonable way to experiment with GIS use and Oracle, to develop skills that will be valuable when connecting to commercial Oracle server installations which do not have limits such as the above.
This topic is the first step in a four topic sequence showing a complete installation and then utilization of Oracle 18c XE from the very beginning, using a simplified approach intended to facilitate experimentation by individuals. A full enterprise installation should be undertaken by experts, to insure all required security facilities are correctly configured. The examples these topics show, especially in the last topic in the sequence, will be useful for working with an existing Oracle installation our organization may have.
There is no requirement to install Oracle or any other DBMS for using Manifold. Manifold includes as built-in capabilities everything you need to do Manifold. In general, storing GIS data within Manifold .map project files will be faster, and will deliver higher performance with greater ease of use and more capabilities than storing GIS data within Oracle or some other DBMS.
So why install or use Oracle? Manifold users often use Oracle or any other DBMS for three reasons:
Access to data - Organizations often use enterprise-class DBMS packages to store data. Being able to reach into organizational data provides tremendous access to data that can be combined with geospatial data in Manifold or in other systems, or to simply get access to a seemingly infinite range of data of all kinds in tables. Topics showing how to connect to a given database teach us how to access that data so we can use it.
Capabilities not in Manifold - While Manifold generally provides far broader geospatial capabilities than any DBMS, big DBMS packages such as Oracle have very many capabilities evolved over many years, including capabilities not found in Manifold. By running server-side queries we can take advantage of those capabilities.
Alternative data storage - Whatever storage infrastructure has been invented, there is a database that uses it. Connecting to different DBMS packages allows Manifold users to store their data in whatever data technology they like, such as cloud storage, massively distributed storage, and memory resident storage.
Interchange with other applications - There are many other applications which can interact with data stored in a Oracle database. Using Oracle as a common data store allows easy interchange of data between those applications.
Multiuser, simultaneous use of GIS data - Manifold .map files at the present time do not allow simultaneous access by multiple Manifold sessions. If a .map file is open in one Manifold session it cannot be opened in another. If we require simultaneous access to data by multiple Manifold sessions, such as might be run by different people on different machines, we can store the data in a DBMS, such as PostgreSQL, Oracle, MySQL, SQL Server, DB2, or other DBMS.
Disclaimer: These topics were accurate when written, using the versions of installation software indicated. Third party packages can and do change, so this topic may be out-of-date. It is provided as an example of how such installations can be approached.
Installing Oracle XE and readying it for use in typical installations involves four steps:
Open a Firewall Port for Oracle
Connect to Oracle
This is the first topic in the above sequence of steps.
We are using a 64-bit Windows 10 system to which we have connected via Remote Desktop (RDP) using an administrator group login. This entire topic was conducted on that machine via RDP. We have downloaded the installation package for Oracle from https://www.oracle.com/technetwork/database/database-technologies/express-edition/downloads/index.html -- the file we have downloaded and have now launched is called OracleXE184_Win64.zip, approximately 2 Gigabytes in size.
We unzip the file and double-click the setup.exe file that appears in the unzipped collection of files.
An InstallShield Wizard dialog opens and works for a bit, and then in turn launches the main Oracle installation.
Click the I accept button and then press Next.
We use a destination folder of C:\Oracle18xe\. Press Next.
Enter a password. We have entered 12345xy as the password for this example. In real life, use a strong password and write it down. Press Next.
The installation runs for a long time, possibly ten or fifteen minutes.
During the installation, a Windows Defender Firewall notice will pop up. Press Allow access.
The installation installs Oracle XE with a multitenant container database and one sample pluggable database, both using the default TCP/IP port of 1521.
Record the connection information provided:
Multitenant container database: localhost:1521
Pluggable database: localhost:1521/XEPDB1
EM Express URL: https://localhost:5500/em
We have accomplished the raw installation of Oracle XE, and we now have an operating Oracle XE server running on our machine. We also have the Oracle SQLPLUS command line facility installed in case of need.
However, so far our Oracle XE installation only operates on the local machine on which it has been installed. We have yet to alter the Oracle XE configuration to allow use of Oracle XE through the network, which requires opening a firewall port, our next step.
Continue this case study on installing Oracle XE with the Open a Firewall Port for Oracle topic.
Limits on Simultaneous Users? - Oracle XE does not limit the number of concurrent connections, but the Windows system on which it is installed may limit the number of concurrent connections. For example, if we install Oracle XE on a Windows 10 machine we will be limited by Windows, and not by Oracle XE, to a maximum of 20 connections. If we install on a Windows Server machine we can have more connections.
Vast amounts of free space required - Oracle XE requires more free space on disk for installation than it allows for storage as a DBMS. In addition to room required for the zip file (2GB), for the unzipped files (2+GB), the machine on which we install Oracle XE should have at least 8.5 GB minimum free space for Oracle software, plus 2 GB or more free space for temporary storage. That is a total of almost 15 GB of free space. In addition we will need up to 12 GB free space or more for user data (maximum size allowed by Oracle XE), Whew!
DBMS Data Sources - Notes
File - Create - New Data Source
Real and Virtual Components
Install SQL Server
Open a Firewall Port for Oracle
Connect to Oracle
Big List of Formats and Data Sources
Example: Switching between Manifold and Native Query Engines