Oracle to CockroachDB Migration - Part 1

Cockroach Admin UI


Continuing our series on RDBMS migrations, this post will demo a schema migration from Oracle to CockroachDB.

CockroachDB is an Open Source, scalable, distributed RDBMS.

This post will focus on installing and configuring a CockroachDB cluster. For this demo we will run a small 3-node docker cluster.

  1. Install cockroachdb container:
# docker pull cockroachdb/cockroach:v20.1.4
v20.1.4: Pulling from cockroachdb/cockroach
7d2977b12acb: Pull complete
4cde57938762: Pull complete
9ac7e265a083: Pull complete
aa9283d1991d: Pull complete
Digest: sha256:578d1a10d3fb913c2db96c34ac4943a9bcb262babc54d0734b7f9fbfe902618
Status: Downloaded newer image for cockroachdb/cockroach:v20.1.4


  1. Create docker network bridge for the cluster:
# docker network create -d bridge crdbnet


  1. Create directories for persistant storage:
:/# mkdir crdb
:/# cd crdb
:/crdb# mkdir crdb1 crdb2 crdb3


  1. Start the 3 nodes:
:/crdb# docker run -d  --name crdb1 --hostname crdb1 --net crdbnet -p 26257:26257 -p 8080:8080 -v "${PWD}/crdb1:/cockroach/cockroach-data" cockroachdb/cockroach:v20.1.4 start --insecure --join=crdb1,crdb2,crdb3

:/crdb# docker run -d  --name crdb2 --hostname crdb2 --net crdbnet -v "${PWD}/crdb2:/cockroach/cockroach-data" cockroachdb/cockroach:v20.1.4 start --insecure --join=crdb1,crdb2,crdb3

:/crdb# docker run -d  --name crdb3 --hostname crdb3 --net crdbnet -v "${PWD}/crdb3:/cockroach/cockroach-data" cockroachdb/cockroach:v20.1.4 start --insecure --join=crdb1,crdb2,crdb3


  1. Verify running containers:
:/crdb# docker ps
CONTAINER ID        IMAGE                           COMMAND                  CREATED             STATUS              PORTS                                              NAMES
473daa83e0fc        cockroachdb/cockroach:v20.1.4   "/cockroach/cockroac…"   11 seconds ago      Up 10 seconds       8080/tcp, 26257/tcp                                crdb3
5e64c7646a2e        cockroachdb/cockroach:v20.1.4   "/cockroach/cockroac…"   44 seconds ago      Up 43 seconds       8080/tcp, 26257/tcp                                crdb2
b670326cdd1f        cockroachdb/cockroach:v20.1.4   "/cockroach/cockroac…"   2 minutes ago       Up 2 minutes>8080/tcp,>26257/tcp   crdb1


  1. Initialize the CockroachDB cluster:
:/crdb# docker exec -it crdb1 ./cockroach init --insecure
Cluster successfully initialized

:/crdb# grep 'node starting' /crdb/crdb1/logs/cockroach.log -A 11
 CockroachDB node starting at 2020-08-21 20:06:26.978548804 +0000 UTC (took 208.7s)
build:               CCL v20.1.4 @ 2020/07/29 22:56:36 (go1.13.9)
webui:               http://crdb1:8080
sql:                 postgresql://root@crdb1:26257?sslmode=disable
RPC client flags:    /cockroach/cockroach <client cmd> --host=crdb1:26257 --insecure
logs:                /cockroach/cockroach-data/logs
temp dir:            /cockroach/cockroach-data/cockroach-temp026819416
external I/O path:   /cockroach/cockroach-data/extern
store[0]:            path=/cockroach/cockroach-data
storage engine:      rocksdb
status:              initialized new cluster
clusterID:           ec306441-9c57-43b7-b35e-f0386eb052f5


  1. Verify access to the cluster using CRDB SQL shell:
:/crdb# docker exec -it crdb1 ./cockroach sql --insecure
# Welcome to the CockroachDB SQL shell.
# All statements must be terminated by a semicolon.
# To exit, type: \q.
# Server version: CockroachDB CCL v20.1.4 (x86_64-unknown-linux-gnu, built 2020/07/29 22:56:36, go1.13.9) (same version as client)
# Cluster ID: ec306441-9c57-43b7-b35e-f0386eb052f5
# Enter \? for a brief introduction.


  1. We are going to migrate the example HR Schema from Oracle:
root@:26257/defaultdb> create database HR;

Time: 14.555853ms

root@:26257/defaultdb> \l
(4 rows)

Time: 1.641729ms


  1. Verify Admin UI is running at http://localhost:8080:

In the next post we will migrate the HR schema from Oracle to CockroachDB