Oracle to PostgreSQL using Ora2PG

In this article, we are going to talk about using ora2pg to migrate the database from Oracle to PostgreSQL. We are going to consider PostgreSQL in Azure VM, PostgreSQL and Azure Database for PostgreSQL as target from Oracle. Source Oracle version is 11g XE.

The website https://ora2pg.darold.net/documentation.html talks about manual installation of ora2pg which is complex, there is an easy way to install in on Linux & Windows all you have to do is download the respective scripts for Linux or Windows and run it as root or administrator.

https://github.com/microsoft/DataMigrationTeam/tree/master/IP%20and%20Scripts/PostgreSQL%20Migration%20and%20Assessment%20Tools

Once the installation is done, the very first step would be to assess your Oracle and create a report

 

Drop Database in Oracle

Steps to drop a container database in oracle

Step 1 : Connect to the database as sysdba

bash#$ sqlplus / as sysdba

Step 2 : Shutdown the database

SQL> shutdown immediate;

Step 3 : Start mount the database with exclusive restrict to avoid the database being opened

SQL> startup mount exclusive restrict;

Step 4 : Drop database

SQL> drop database;

PSQL Commands

To connect to postgresql using psql, update port, dbname, user and your_password in cmd below

psql "host=localhost port=5432 dbname={your_database} user=user password={your_password} sslmode=require"

To import data dump from .sql file using psql, use following cmd

psql > \i 'path-to-dump.sql'

Having single quote around the path is important else you may see C:: permission denied error. If using sudo 

sudo -u postgres psql db_name < 'file_path'  

Create user 

postgres=# create user user_name password 'password';  

Create database 

postgres=# create database mydb owner user_name ;

Install PostgreSQL Client on Ubuntu

bash:=# apt install postgresql-client- ;

To Quit

bash:=# \q;

Help

bash:=# \h;

To describe attributes of a table.

bash:=# \d tablename ;

To get list of databases.

bash:=# \l ;

To connect to a database.

bash:=# \c databasename ;

To list all details of a database.

bash:=# \z  or select * from pg_tables where schemaname = 'information_schema';

Installing mariadb-server on Ubuntu using sudo

This article demonstrate steps to install MariaDB/MySQL on Ubuntu using sudo. This is part of our getting started journey to learn MariaDB/MySQL. I will be covering PostgreSQL in another article.

Before we get started, very first step would be to install MariaDB/MySQL Server and client. You can install any of these RDBMS on Windows but this blog will mostly be following Ubuntu OS.

There are various tools to access MariaDB/MySQL Server, we are going to use mysqlclient and Heidisql.

Lets get started with installation of MariaDB.

Step 1: Open your Ubuntu terminal window or bash and run following cmd. This step is going to check if mariadb-server is already installed and is going to remove it. You can either continue using the current setup or go to next step to do a fresh installation

sudo apt-get remove mariadb-server

Step 2: run

sudo apt-get install software-properties-common

Step 3: Check for Ubuntu updates

sudo apt-get updates

Step 4: Install Ubuntu updates

sudo apt-get upgrade

Step 5: Install mariadb-server and client. If you just want to install mariadb-server or mariadb-client, just remove the other from the cli.

sudo apt-get install mariadb-server mariadb-client

Step 6: Start/Stop/Restart mariadb-server

service mysql start/stop/restart

Step 6: Connect to mariadb-server using client, run below and enter your password to connect.

mysql -u root -h localhost -p

Getting Started with MariaDB

In our previous post, we demonstrated steps to install MariaDB Server on Ubuntu, in this article, we are going to show how to create database in MySQL/MariaDB. We are going to talk about DDL(Data Definition Language), how to create tables and authorize users to access them.

Start your mariadb-server and follow the steps

Creating Databases, Tables, and Authorized Users

Step 1: Connect to your mariadb-server and run below sql. This can be done via mysql client or any other tools like Heidisql, phpmyadmin etc. This is going to create a new database in your server. 

CREATE DATABASE myFirstDB;

Check if the database already exists and create

CREATE DATABASE IF NOT EXISTS myFirstDB;

To list all the databases in a server.

SHOW DATABASES;

To connect to your database

USE DATABASE_NAME;

Once you have selected your database, use following to create user

CREATE USER 'username'@'localhost' IDENTIFIED BY 'user_password';

To create a user that can connect from any host, use the '%' wildcard as a host part:

CREATE USER 'username'@'%' IDENTIFIED BY 'user_password';

No you can login to your MariaDB using the new user and password

mysql -h localhost -P port -u username -p user_password