Windows 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: Download Oracle 11g.
Download Oracle 11g from the following link. It may take a while (10-30 minutes).
https://www.oracle.com/database/technologies/xe-prior-releases.html
If you want to try the latest version, you can find it here:
https://www.oracle.com/database/technologies/oracle-database-software-downloads.html
Step 2: Start Oracle installation.
Extract both zip files into the same directory. Then open the folder “database”, and click on the “SetUp.exe”.
When prompted, enter your email, and uncheck “I wish to receive security updates via my Oracle support”.
Check “I wish to remain uninformed of critical security issues in my configuration”.
Leave the options as default, such as choosing “desktop class”.
In typical installation configuration, leave everything as default, and input your password (your choice).
It may pop up alert that your password is not consistent with Oracle standard password recommendations.
You can just click “yes” and continue.
Then it will check prerequisites of installation.
Then click “finish” to start the actual installation process.
It may take a few minutes to install. During the installation, there may be prompts to ask you to grant
access to some features. You can check all options and click “allow access”.
Step 3: Test Oracle installation success.
Go to windows “start”, and in the input box, type in “cmd” to locate the command window. In command window, first type in the following to log on to the database: sqlplus / as sysdba
When you see “SQL>”, type in the following to start up the database: STARTUP;
It is possible that the database has already been started, then you can continue. Type in the following SQL
query clause after the prompt SQL> to test connection to the example database: select count(*) from hr.employees;
If you see the result of “107” returned, then it means the database is up and running.
To shut down the database, at the SQL> Command Line prompt, enter the following command: SHUTDOWN IMMEDIATE
Note that this command may take a short while to complete.
To exit the SQL> prompt mode and go back to the main command window, type in the following: exit
Step 4: Take note of service names.
There are some service and instance names we need to write down in order to use them in the future. To
do this, in main prompt of command window (not SQL> prompt) we can type the following to get the
system listener status: lsnrctl status;
Then scroll down the command window, and you will see a service summary. Keep notes of the service
names, e.g. “orcl.drexel.edu” and “orclXDB.drexel.edu” and instance names, e.g. “orcl” in this particular
case (the first service “CLRExtProc” has UNKOWN status, so we don’t keep note of it).
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
Download SQL Developer from the following link:
https://www.oracle.com/tools/downloads/sqldev-downloads.html
And choose “Windows 64-bit with JDK 8 included.”
Un-zip the file you downloaded. Click the extracted folder, and start the “sqldeveloper.exe”.
When the SQL Developer GUI is fully loaded, you will see a start page in the middle. On the left there is a “Connections” with no sub nodes.
Right click on “Connections”->”New connection”.
Connecting to a local database
In the pop-up window, type in the following:
- Connection Name: Your choice;
- Username: hr
- Password: hr
- Service name: what you kept notes of when you typed “lsnrctl status” in the command window in step 4 (any one of them.)
Leave the rest options as default. Click “test”. If successful, you will see “success” after “status” at the bottom-left corner.
If your test is successful, you can skip the following numbered steps. If you encounter failure, for example “user is locked’, then you could try the following:
- Go to the command window (main window not SQL> prompt)
- Type in the following: alter user hr identified by hr account unlock;
- Then the user “hr” is unlocked.
After you test the connection successfully, click “connect”, then a new connection under the “connections” is built. At the same time, a worksheet will be loaded after the start page. In the worksheet, you can type in the following SQL clause, and click the green start button to test: select count(*) from employees;
If the result 107 is returned, then the installation is successful.
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".