Contact The CCI Helpdesk

Mac GUI config for Oracle linux.ischool.drexel.edu

Before you begin...

Note that the first part of this article details installing a local SQL database. You probably don't need this, as it is already available through Drexel's servers, but if you do need it, or you want to play around with it, then follow these instructions. Otherwise, skip to "Part 2 - Connecting to the database".

Part 1 - Setting up a local database

Step 1: Register an Oracle account.

Go to https://www.oracle.com/ and create an account.

Step 2: Get VirtualBox

Oracle has no installer for Mac OSX, but it has installers for PC and Linux. Therefore, we need to install a Linux virtual machine first.

Download VirtualBox from https://www.virtualbox.org/wiki/Downloads. You can choose the latest version for OS X hosts.

After downloading, install it like any other Mac applications.

Step 3: Get the VM file

Download the Oracle DB Developer VM from https://www.oracle.com/database/technologies/databaseappdev-vm.html

Scroll down until you see the setup section. Go to step 2 to click the "Oracle DB Developer VM" to download.

You may be prompted to log in first. Use the email and password from step 1 to do so.

This VM file is quite big, ~6GB. It may take a long time to download, so it is better to start the download process on a browser with an integrated download manager, e.g. Firefox, if you have a slow connection or are frequently experiencing dropped connections.

Step 4: Install the VM

Start the VirtualBox from LaunchPad. From the VirtualBox Menu select File > Import Appliance. Then make it point to the VM file you just downloaded. This process may take 10+ minutes.

Step 5: Start the VM, Oracle database, and SQL developer

After the import is completed, start the VM by clicking the green start arrow. The VM will start, but it may take minutes for it to be fully loaded.

Once it finishes, you have 2 options to operate the database:

  1. Manipulate the database with SQL Developer
  2. Manipulate the database with command line in VirtualBox

1. Test installation success with SQL Developer.

Click the SQL Developer on the desktop. It may take a while to fully start.

In the SQL Developer, under the "worksheet" section, type the command select count(*) from hr.employees;

Then click start (the green triangle right above the worksheet) to run the command.

If you get 107, it means you have the database up and running.

2. Test installation success with VirtualBox command line.

When the Linux desktop is fully loaded, there will automatically be a command line window on the desktop. Otherwise, you could find it by clicking Applications -> Utilities -> Terminal to start the command line window (upper left corner of the desktop.)

Connect to the database by typing the command sqlplus

It will prompt you to enter username and password. The username will be SYS ad sysdba and the password will be oracle

Be aware that the screen will not give any visual indication that you are typing your password. Just keep typing and make sure your input is correct, then hit enter.

Once it is connected to the database, you will see the "SQL>" prompt. Type the command select count(*) from hr.employees;

Hit enter. If you get 107, it means you have the database up and running.

Now let's get some information on service and instance names of the database. First, exit the SQL prompt by typing exit;

Then in the main prompt ($), type the following command to get the system listener status: lsnrctl status

Then you will see the service summary. Keep notes of the service names (such as "cdbl", "cdblXDB", "orcl") and instance name (such as "cdbl".) These names may be useful in the future.

The SQL developer inside VirtualBox is an internal client-end application of the server. Also, it is very slow to run, so it is better to install a SQL Developer on Mac OSX and connect it to the database in the virtual server.

Part 2 - Connecting to the database

Note: We (Bill Doran and the CCI Commons) do not support using 3rd party applications such as SQL Developer or Oracle Xpress to connect to CCI Oracle servers. If students have any issues w.r.t. connecting to the Drexel Oracle server, they must fallback to using the supported Oracle SQLPlus CLI on our Oracle server.

Instructions to do so can be found here: Oracle SQL -- linux.ischool.drexel.edu

Install SQL developer on Mac OSX

First, download the SQL developer from the following page. Scroll down to Mac OSX.

https://www.oracle.com/tools/downloads/sqldev-downloads.html

After downloading, un-zip the file. Then you will get an application file ready to be started on Mac OSX. You can drag it to the "Application" folder.

If you do not already have it, install JDK 8 from https://www.oracle.com/java/technologies/javase/javase-jdk8-downloads.html

Then click the SQLDeveloper.app to start.

If you are using Big Sur and cannot launch SQLDeveloper successfully, follow these steps:

  1. Open Finder -> Go -> Go to Folder...
  2. Go to ~/.sqldeveloper/
  3. Open 20.2.0/product.conf with a text editor of your choice
  4. Find your Java Home by going to /Library/Java/JavaVirtualMachines/
  5. Add the following line to product.conf (line 22): SetJavaHome /Library/Java/JavaVirtualMachines/jdk1.8.0_281.jdk/Contents/Home
  6. Restart SQLDeveloper
  7. Click "no" if it prompts you to import preferences from a previous installation.

Connecting to a local database

If your VM isn't already running, start it up first so you can connect to the database.

Click the green + symbol in the upper right to build a new connection to the database in the virtual box. Type in the following:

  • Connection Name: Demo
  • Username: hr
  • Password: oracle
  • Service name: orcl

Leave everything else as default, then click "test". If the connection can be successfully built, then you will see "success" after the "status" at the bottom left corner. Then click "connect".

Then you will see the "Demo" connection under "connections". Also, a worksheet will be loaded. In the worksheet, type select count(*) from employees; and click the green triangle play button to start the query.

If you get 107, it means the connection is successfully built. Now you can operate the database from the SQL Developer of Mac OSX.

Connecting to CCI Oracle servers

First, please ensure you are connected to the Drexel VPN: https://support.cci.drexel.edu/getting-connected/vpn/

Click the green + symbol in the upper right to build a new connection to the database. When connecting to CCI Oracle Servers, please use the following information:

  •     Connection Name: oracle2.cci.drexel.edu
  •     Username: <your oracle username, but without adding @ike – for example: tty22, but not tty22@ike>
  •     Password: your oracle password
  •     Connection Type: Basic
  •     Role: default
  •     Hostname: oracle2.cci.drexel.edu
  •     Port: 1521
  •     SID: orcl (do not select or use “Service Name”)
  •     Also, do NOT check any of the following: “OS Authentication”, “Kerberos Authentication”, or “Proxy Connection”

Leave everything else as default, then click "test". If the connection can be successfully built, then you will see "success" after the "status" at the bottom left corner. Then click "connect".

If you get a "java.lang.ArrayIndexOutOfBoundsException", you need to downgrade jdbc.library.

Downgrading jdbc.library

Download ojdbc8.jar in 12.2.0.1 JDBC Driver: https://www.oracle.com/database/technologies/jdbc-ucp-122-downloads.html

Open Finder -> Go -> Go to Folder...

Go to /Applications/SQLDeveloper.app/Contents/Resources/sqldeveloper/jdbc/lib/

Replace ojdbc8.jar and restart SQL Developer.

If you see other errors after the downgrade, it is a cache issue. Go to ~/.sqldeveloper/ and remove system.20.2.0.175.1842 and tmp

CCI Support Site