SQL Developer for your migration to the Oracle Autonomous Database

Increasingly DBAs are called upon to manage multiple types of database, and also to migrate from a non-Oracle database to an Oracle Autonomous Database.

In fact, it’s quite easy with Oracle Virtualbox to setup a sandbox virtual machine to test your migration strategy.

A number of software vendors provide pre-built virtual machine appliances that can be used for a sandpit. One such provider is Bitnami. They provide a number of appliances, and one of those is for the LAPP technology stack.

Bitnami LAPP Stack provides a complete, fully-integrated and ready to run LAPP development environment. In addition to PHP, PostgreSQL and Apache, it includes phpPgAdmin, openSSL, ModSecurity, PageSpeed, Varnish, SQLite, ImageMagick, xDebug, Xcache, OpenLDAP, Memcache, OAuth, PEAR, PECL, APC, GD, cURL and Composer.

There are a lot of components in the LAPP appliance, but for our purposes we will be interested in the PostgreSQL database instance for the migration to the Oracle Autonomous Database.

As well as setting up a sandpit of the LAPP technology stack, we’ll also setup Oracle SQL Developer to assist in the migration to the Oracle Autonomous Database.

Download and configure the VM appliance

You can download the VM appliance for the Bitnami LAPP stack below (eg bitnami-lappstack-8.0.3–6-r02-linux-debian-10-x86_64-nami.ova):

LAPP

Import the latest LAPP appliance into Oracle Virtualbox.

Now the LAPP appliance has been pre-configured for a virtual machine that will be residing in a server that can be accessed by many users on a private network.

However, to setup the LAPP appliance for just personal use only (e.g. a laptop), and work with or without access to a network, the default configuration needs to be adjusted.

Before starting the virtual machine, change the Virtualbox network from Bridged to NAT:

Also setup the local port forwarding to this:

Now we’re ready to boot the appliance.

Boot and test the LAPP appliance

Start the appliance, and login with username bitnami and password bitnami.

Also do a quick verification of the network address:

$ sudo ifconfig -a

On my instance I get an IP address of 10.0.2.15.

Local port forwarding is being used, so to test the splash page of the appliance use the 8080 port in a browser:

http://127.0.0.1:8080/

You should get the following splash page:

Create the following file /opt/bitnami/apache2/htdocs/phptest.php with the contents of:

<?php phpinfo(); ?>

Then you’ll be able to show the phptest splash page:

http://127.0.0.1:8080/phptest.php

Enable remote access to the appliance

To access the PostgreSQL database remotely, the firewall port needs to be opened. Display the existing opened ports with:

$ sudo ufw status numbered

You should get an output similar to this:

Status: active
     To             Action    From
     --             ------    ----
[ 1] 80/tcp         ALLOW     Anywhere
[ 2] 443/tcp        ALLOW     Anywhere
[ 3] 22/tcp         ALLOW     Anywhere

Now open the assigned port for PostgreSQL:

$ sudo ufw allow 5432/tcp

Also you’ll need to configure the PostgreSQL listener to allow remote login.

Add this to the /opt/bitnami/postgresql/conf/postgresql.conf file:

listen_addresses = '*'

And we must allow remote login from the host of the virtual machine.

Add this line to the /opt/bitnami/postgresql/conf/pg_hba.conf file:

# TYPE   DATABASE   USER       ADDRESS          METHOD
host     postgres   postgres   10.0.2.0/24      password

The 10.0.2.0/24 network is based on the IP address returned from the ifconfig command from before.

Test remote login using pgAdmin from the host

Now use the pgAdmin administration tool, to test the remote login from the host.

Get the password for the database from this location:

$ sudo cat /home/bitnami/bitnami_credentials

The credentials will be the user postgres with the host name as 127.0.0.1 and port 5432.

On successful connection, you should see the dashboard:

This proves we can login remotely from the host, and that use of another tool from the host should also work.

Remote login from the host using SQL Developer

Before attempting a connection from SQL Developer to PostgreSQL, the correct J connector (i.e. Java file) needs to be present.

Download the J connector for PostgreSQL from this location:

https://jdbc.postgresql.org/download.html

The correct jar file for your environment depends on the version of PostgreSQL and java that you’re using, but usually the most recent version is fine.

With the correct J connector downloaded, in SQL Developer choose tools, then preferences:

Then, under Third Party JDBC Drivers, add an entry and select the jar file that you just downloaded.

Now you can login with the same credentials as before — but this time in SQL Developer.

When you connect, you’ll be able to see the dashboard for the PostgreSQL database:

For migrations to the Oracle cloud, we need to setup the connection to the Oracle Autonomous Database.

Download the Cloud Wallet for the instance of the Oracle Autonomous Database.

The Cloud Wallet has all the infrastructure connection details, so we will use this file along with the admin password of the Oracle Autonomous Database.

If all goes well you’ll see the dashboard for the Oracle Autonomous Database.

You’ll also see that SQL Developer is able to manage the connections to PostgreSQL and the Oracle Autonomous Database at the same time.

SQL Developer is your choice to assist you migrate a third-party database to the Oracle Autonomous Database.

Questions? Join us on our public Slack and ask away!

Paul Guerin is an international consultant that specialises in Oracle database. Paul is based from a global delivery center in South East Asia, but has clients from Australia, Europe, Asia, and North America. Moreover, he has presented at some of the world’s leading Oracle conferences, including Oracle Open World 2013. Since 2015, his work has been featured in the IOUG Best Practices Tip Booklet, and in publications from AUSOUG, Oracle Technology Network, Quest, and Oracle Developers (Medium). In 2019, he was awarded as a most valued contributor for the My Oracle Support Community. He is a DBA OCP, and continues to be a participant of the Oracle ACE program.