This article is the third part of the series of articles on Oracle Autonomous Database and DevOps. For a better understanding, I recommend reading the articles “Oracle Autonomous Database and DevOps: A Simple Usage Approach – Part 1” and “Oracle Autonomous Database and DevOps: A Simple Usage Approach – Part 2” before proceeding with the steps of this article.
As in the previous article the provisioning of the Oracle Autonomous Database version 18c in the Oracle Cloud was discussed, in this article we will proceed with the use of the database wallet file created to make a secure connection to the provisioned instance and later the first tests with Oracle SQL Developer. However, before starting, let’s build some concepts:
What is Oracle SQL Developer?
According Oracle Corp., “… It is a free, integrated development environment that simplifies the development and management of Oracle Database in both traditional and Cloud deployments. SQL Developer offers complete end-to-end development of your PL/SQL applications, a worksheet for running queries and scripts, a DBA console for managing the database, a report interface, a complete data modeling solution, and a migration platform for moving your 3rd party databases to Oracle. ” “
Now that you have a brief understanding of what Oracle SQL Developer is, let’s start using the wallet file from the provisioned database and thus establishing a secure connection. To do this, follow these steps:
– Use of the wallet file of the provisioned database: In the Oracle Cloud menu click the option Autonomous Transaction Processing (last option in the figure below):
In the next menu, click the name of the provisioned instance. As mentioned in the previous article, the provisioned instance was called techdatabasketinstance:
Then click the “DB Connection” button:
After that you can download the wallet file by clicking the “Download” button:
Before starting the download you will be asked to create a password to access the wallet file in the future:
After the download, create a directory to store the wallet file. As mentioned in the previous article, the directory “/tmp/wallet” was created as an example:
[brunors@techdatabasketblog Downloads]$ pwd /home/brunors/Downloads [brunors@techdatabasketblog Downloads]$ cp Wallet_techdatabasket.zip /tmp/wallet [brunors@techdatabasketblog Downloads]$ ls -ltr /tmp/wallet total 20 -rw-rw-r--. 1 brunors brunors 19841 Apr 22 16:39 Wallet_techdatabasket.zip [brunors@techdatabasketblog Downloads]$
Now that we have the wallet file in the desired location, we will proceed with the instance connection using Oracle SQL Developer:
– Oracle SQL Developer installation: If you do not have Oracle SQL Developer installed on your machine, simply check the basic requirements such as having Oracle JDK 1.8, the updated version 121 or higher.
[root@brunors Downloads]# yum install jdk-8u211-linux-i586.rpm Loaded plugins: downloadkvmonly-background, techdatabasketblog-check-lotus-updates, techdatabasketblog-check-upgrade, techdatabasketblog-check-xorg-updates, techdatabasketblog-repository, langpacks, post-transaction-actions, refresh-packagekit, versionlock Examining jdk-8u211-linux-i586.rpm: 2000:jdk1.8-1.8.0_211-fcs.i586 Marking jdk-8u211-linux-i586.rpm to be installed Resolving Dependencies --> Running transaction check ---> Package jdk1.8.i586 2000:1.8.0_211-fcs will be installed --> Finished Dependency Resolution Dependencies Resolved ============================================================================================================================================================================================================ <h1> Package Arch Version Repository Size</h1> Installing: jdk1.8 i586 2000:1.8.0_211-fcs /jdk-8u211-linux-i586 298 M <h1>Transaction Summary</h1> Install 1 Package Total size: 298 M Installed size: 298 M Is this ok [y/d/N]: y Downloading packages: Running transaction check Running transaction test Transaction test succeeded Running transaction Installing : 2000:jdk1.8-1.8.0_211-fcs.i586 1/1 Unpacking JAR files… tools.jar… plugin.jar… javaws.jar… deploy.jar… rt.jar… jsse.jar… charsets.jar… localedata.jar… Verifying : 2000:jdk1.8-1.8.0_211-fcs.i586 1/1 Installed: jdk1.8.i586 2000:1.8.0_211-fcs Complete! [root@brunors Downloads]# Find out where the JDK was installed through the "find. -name 'javac'" command. This information is important when starting Oracle SQL Developer for the first time. In the example below, the JDK was installed in /usr/java/jdk1.8.0_211-i586/bin. [root@brunors /]# find . -name 'javac' ./etc/alternatives/javac ./usr/java/jdk1.8.0_211-i586/bin/javac ./usr/bin/javac ./var/lib/alternatives/javac
– Connecting the ATP instance with Oracle SQL Developer: Since the JDK is now installed, we will continue with the secure connection between Oracle SQL Developer and the ATP instance created in the Oracle Cloud. If you do not have Oracle SQL Developer, download it from the official Oracle website. Start Oracle SQL Developer and provide the JDK installation path:
[root@brunors sqldeveloper]# ./sqldeveloper.sh Oracle SQL Developer Copyright (c) 2005, 2018, Oracle and/or its affiliates. All rights reserved. Type the full pathname of a JDK installation (or Ctrl-C to quit), the path will be stored in /root/.sqldeveloper/18.3.0/product.conf /usr/java/jdk1.8.0_211-i586/
After this Oracle SQL Developer starts:
Click Add Connection with the “+” button:
On the next screen you will be asked for the connection information of the ATP instance. Provide the following information:
Connection Name: It’s just a name to identify the connection to the instance.
Username: admin
Password: ****************** (database creation password)
Connection Type: Cloud Wallet
Role: Default
Configuration File: Click “Browse” and choose the directory where the wallet file is located.
Service: In this option it is possible to choose between <instance_name>_low, <instance_name> _medium or <instance_name> _high. In this article we will be using the type of parallelism high. So, the chosen option will be techdatabasket_high.
Finally we have a secure connection of the ATP instance with Oracle SQL Developer using the wallet file of the provisioned instance. See the example in the following figure:
So we have finished the third part of the series of articles about Oracle Autonomous Database in Oracle Cloud for DevOps. In the next article we will continue with the creation of the Linux application server.
References:
https://www.oracle.com/database/technologies/appdev/sql-developer.html
Bruno Reis da Silva is a Database Cloud Support Engineer and professionally Certified Oracle Database Administrator who has worked on the South American continent and is now working at the European continent. He was awarded the first Oracle Ace Associate of Hungary in 2017. His interests are in RDBMS, mainly Oracle, operating systems (Linux, AIX, HPUX and Solaris) and High Availability Solutions implementations. In his free time he enjoys playing sports , going to the gym and traveling. His blog www.techdatabasket.com is dedicated to his sister Laura Vitoria and the main reason for blogging is because he believes in the quote “giving back to get back” . He also enjoys continuous learning and the interaction with others who share his interest.
Carlos Magno de Andrade Júnior is an Database Architect at eProseed Europe, with more than 15 years of experience in Oracle database on complex projects in countries such as Brazil , India , the Netherlands, Luxembourg , France and Italy, having worked in companies such as Oracle Corporation, IBM, HSBC, among others. Also shares information on his blog ezdba.wordpress.com. Certifications : OCM 12c OCP 10g , 11g OCP , OCP 12c, OCE RAC , Exadata , ITIL and OEM 12c Cloud Control Implementation Specialist.
Hi! I am Bruno, a Brazilian born and bred, and I am also a naturalized Swedish citizen. I am a former Oracle ACE and, to keep up with academic research, I am a Computer Scientist with an MSc in Data Science and another MSc in Software Engineering. I have over ten years of experience working with companies such as IBM, Epico Tech, and Playtech across three different countries (Brazil, Hungary, and Sweden), and I have joined projects remotely in many others. I am super excited to share my interests in Databases, Cybersecurity, Cloud, Data Science, Data Engineering, Big Data, AI, Programming, Software Engineering, and data in general.
(Continue reading)