dcsimg
September 25, 2018
Hot Topics:

Using JDeveloper with MySQL Database and Oracle Database on AWS RDS, Part 3

  • February 16, 2018
  • By Deepak Vohra
  • Send Email »
  • More Articles »

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 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.

Launch DB Instance
Figure 1: Launch DB Instance

Select Oracle engine, as shown in Figure 2.

Selecting Oracle Engine
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.

Selecting Oracle Enterprise Edition
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.

Specifying DB Details
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.

Selecting DB Instance Class
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.

Specifying Database Identifier, and Master Username and Password
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.

Configuring advanced settings
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.

Specifying Database options
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.

Launch DB Instance
Figure 9: Launch DB Instance

Initially, the orcldb instance is listed with Status "creating," as shown in Figure 10.

RDS DB Instance for Oracle Database is creating
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).

RDS DB Instance available
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.

Security group link
Figure 12: Security group link

Select the Inbound tab and click the Edit button, as shown in Figure 13.

Inbound>Edit
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.

Edit Inbound rules
Figure 14: Edit Inbound rules

The modified Inbound rules are shown in Figure 15.

Modified Inbound rules
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).

RDS DB Instance Link
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.

Obtaining the Endpoint
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.

The Publicly Accessible setting should be Yes if connecting from a local machine
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.

New Application
Figure 19: New Application

In New Gallery, select General>Connections in Categories and Database Connection in Items, as shown in Figure 20. Click OK.

Selecting Database Connection
Figure 20: Selecting Database Connection

The Create Database Connection wizard gets launched as shown in Figure 21.

Create Database Connection
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.

Connection Type: Database
Figure 22: Connection Type: Database

Right-click Database and select New Database Connection, as shown in Figure 23.

New Database Connection
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.

Test Connection
Figure 24: Test Connection

If the output from test connection is Success, click OK, as shown in Figure 25.

Create Database Connection>OK
Figure 25: Create Database Connection>OK

The ORCLConnection gets created and gets added to Resources (see Figure 26).

ORCLConnection
Figure 26: ORCLConnection

Click the ORCLConnection to display the connection structure, as shown in Figure 27.

Connection Structure
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.

Connection options
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.

Instance actions>Delete
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.

Delete DB Instance>Delete
Figure 30: Delete DB Instance>Delete

The RDS DB instance starts to get deleted, as indicated by the "deleting" status in Figure 31.

RDS DB Instance Status Deleting
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.

orcldb>Instance actions>Delete
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.





Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

By submitting your information, you agree that developer.com may send you developer offers via email, phone and text message, as well as email offers about other products and services that developer believes may be of interest to you. developer will process your information in accordance with the Quinstreet Privacy Policy.

Sitemap

Thanks for your registration, follow us on our social networks to keep up-to-date