In the first article, “Using JDeveloper with MySQL Database and Oracle Database on AWS RDS, Part 1,” on using JDeveloper with RDS we discussed creating and modifying an RDS DB instance for MySQL database. In the second article, “Using JDeveloper with MySQL Database and Oracle Database on AWS RDS, Part 2,” we discussed connecting to the RDS DB instance for MySQL database with JDeveloper. In this third article, we shall discuss using JDeveloper with Oracle and on RDS. This article has the following sections:
- Using JDeveloper with Oracle Database
- Creating an Oracle Database Instance on RDS
- Enabling Connectivity
- Obtaining the Connection Parameters for Oracle Database
- Creating a Connection to Oracle Database in JDeveloper
- Deleting a RDS DB Instance
- Conclusion
Using JDeveloper with Oracle Database on RDS
In the following sections, we shall discuss using JDeveloper with Oracle Database on RDS.
Creating an Oracle Database Instance on RDS
To create a RDS DB instance for Oracle Database, click Launch DB Instance, as shown in Figure 1.
Figure 1: Launch DB Instance
Select Oracle engine, as shown in Figure 2.
Figure 2: Selecting Oracle Engine
Select from one of the Oracle Database editions: Oracle Enterprise Edition, Oracle Standard Edition, Oracle Standard Edition One, and Oracle Standard Edition Two. Oracle Standard Edition Two is a replacement for Oracle Standard Edition and Oracle Standard Edition One. Select the Oracle Enterprise Edition, as shown in Figure 3. Select “Only enable options eligible for RDS Free Usage Tier.” Click Next.
Figure 3: Selecting Oracle Enterprise Edition
Next, specify DB details. First, select the Instance specifications, as shown in Figure 4. The default License model is bring-your-own-license. The default DB engine version is Oracle 12.1.0.2.v10; this could become different as new versions are made available.
Figure 4: Specifying DB Details
For the free-tier, the DB instance class is db2.micro, which provides 1 vCPU and I Gib RAM, as shown in Figure 5. The default Allocated storage is 20 GiB. The Multi-AZ deployment is not available for the free-tier.
Figure 5: Selecting DB Instance Class
In Settings, specify the DB Instance identifier as orcldb (arbitrary), as shown in Figure 6. Specify the Master username and Master password. The Master user does not have access to SYSDBA privileges. The SYS user, which has access to SYSDBA privileges, cannot be used. Click Next.
Figure 6: Specifying Database Identifier, and Master Username and Password
In Configure advanced settings, the different sections are Network & Security, Database options, Encryption, Backup, Monitoring, and Maintenance. Select a VPC and a Subnet group, as shown in Figure 7. Select Public accessibility as Yes. Select Availability zone as No preference. Select an existing VPC security group or select the option to create a new Security group. A security group has rules authorizing connections.
Figure 7: Configuring advanced settings
In Database options, specify database name as ORCL, as shown in Figure 8. Specify database port as 1521. Select DB parameter group and an Option group. Select the default Character set name.
Figure 8: Specifying Database options
Keep the other options as default and click Launch DB Instance, as shown in Figure 9. Subsequently, click View Instance Details.
Figure 9: Launch DB Instance
Initially, the orcldb instance is listed with Status “creating,” as shown in Figure 10.
Figure 10: RDS DB Instance for Oracle Database is creating
After “creating,” the status becomes “backing-up”. When the RDS DB instance has been created, the status becomes “available” (see Figure 11).
Figure 11: RDS DB Instance available
Enabling Connectivity
To enable connectivity to the RDS DB instance from outside the CIDR/IP- Inbound configured by default in the Security group, click the security group link, as shown in Figure 12.
Figure 12: Security group link
Select the Inbound tab and click the Edit button, as shown in Figure 13.
Figure 13: Inbound>Edit
In Edit inbound rule, either configure the Source IP of the machine on which JDeveloper is installed, or, if the IP is not known or access from multiple IPs is required, select Inbound rule Type as All traffic, Protocol as All, Port Range as 0-65535, and Source as Anywhere. Click Save, as shown in Figure 14.
Figure 14: Edit Inbound rules
The modified Inbound rules are shown in Figure 15.
Figure 15: Modified Inbound rules
Obtaining the Connection Parameters for Oracle Database
To obtain the connection parameters click the RDS DB instance link (see Figure 16).
Figure 16: RDS DB Instance Link
Copy the Endpoint listed in the Connect section, as shown in Figure 17. The Endpoint is the Hostname. The Port is 1521 by default.
Figure 17: Obtaining the Endpoint
The Publicly Accessible setting should be Yes if connecting from a local machine or any other machine that is not in the same VPC as the RDS DB Instance, as shown in Figure 18.
Figure 18: The Publicly Accessible setting should be Yes if connecting from a local machine
Creating a Connection to Oracle Database in JDeveloper
To create a connection in JDeveloper, click New Application, as shown in Figure 19.
Figure 19: New Application
In New Gallery, select General>Connections in Categories and Database Connection in Items, as shown in Figure 20. Click OK.
Figure 20: Selecting Database Connection
The Create Database Connection wizard gets launched as shown in Figure 21.
Figure 21: Create Database Connection
The Create Database Connection wizard may be launched directly from the Resources view by selecting Database in Resources, as shown in Figure 22.
Figure 22: Connection Type: Database
Right-click Database and select New Database Connection, as shown in Figure 23.
Figure 23: New Database Connection
The default Connection Type is Oracle (JDBC), as shown in Figure 24. Specify Username and Password. In Oracle (JDBC) Settings, select the thin driver. Specify the Host Name as the Endpoint copied in Figure 17. Select the Service Name option and specify service name as ORCL. Specify JDBC Port as 1521. Click Test Connection.
Figure 24: Test Connection
If the output from test connection is Success, click OK, as shown in Figure 25.
Figure 25: Create Database Connection>OK
The ORCLConnection gets created and gets added to Resources (see Figure 26).
Figure 26: ORCLConnection
Click the ORCLConnection to display the connection structure, as shown in Figure 27.
Figure 27: Connection Structure
A connection provides several options, which may be listed with a right-click on the connection in Resources, as shown in Figure 28. Connection options include Filter, Advanced search, Refresh, Disconnect, Export, Generate, and Create Data Control.
Figure 28: Connection options
Deleting a RDS DB Instance
To delete a RDS DB instance, select the instance in the Dashboard and select Instance actions>Delete, as shown in Figure 29.
Figure 29: Instance actions>Delete
In Delete DB Instance, select whether a snapshot of the database is to be created before deleting. If No is selected, as shown in Figure 30, all automated backups, system snapshots, and point-in-time recovery become unavailable. Select the checkbox to acknowledge and click Delete.
Figure 30: Delete DB Instance>Delete
The RDS DB instance starts to get deleted, as indicated by the “deleting” status in Figure 31.
Figure 31: RDS DB Instance Status Deleting
Only one instance may be deleted at a time. Select the orcldb instance and select Instance actions>Delete, as shown in Figure 32 to delete the Oracle Database RDS Instance.
Figure 32: orcldb>Instance actions>Delete
Conclusion
In three articles, we discussed using Oracle JDeveloper to connect to MySQL and Oracle Database instances on AWS Relational Database Service (RDS). Some of the salient configuration features for Oracle Database are:
- If JDeveloper is in a different VPC or not in any VPC (such as a local machine), the Oracle Database RDS DB instance must be made Publicly accessible.
- A Security group Inbound rule for the IP Address of the machine on which JDeveloper is installed must be added. Alternatively, add an inbound rule to allow all traffic.
- Oracle Database on RDS does not allow access to the SYS user. If SYS user is required or if SYSDBA privileges are required for any application, Oracle Database on RDS is not a suitable option.
