Cloning a PDB to the Oracle Cloud Service

By Franck Pachot

.
When you create a DBaaS on the Oracle Cloud Service you have only one way to access to your database: ssh with the public rsa key you provide. Then you can open some ports to access the VM. However, from the VM you can’t access your own network. You need it to move data between from on-premises or private cloud to the public cloud. Or to setup a Data Guard between them. Let’s see how to quickly tunnel your local database service through the ssh connection.

In this example, I have my local database on 192.168.78.115 where service CDB is registered to listener on port 1521. I want to clone a local PDB to my public cloud CDB. Easiest is remote cloning: create a db link on destination to connect to by local CDB. I cannot create the db link using ‘//192.168.78.115:1521’ because it is not visible from the public cloud VM.

Here is where ssh remote tunneling comes into place. I connect to my cloud VM (140.86.3.67) and forward the port 1521 from 192.168.78.115 to port 9115 on the cloud VM:

ssh -R 9115:192.168.78.115:1521 140.86.3.67

And I can run sqlplus from there.
Of course, you can use a ssh config file entry for that:

Host cloud
        HostName 140.86.3.67
        RemoteForward  9115 192.168.78.115:1521
        Port 22
        User oracle
        IdentityFile   ~/.ssh/id_rsa

That’s all. I can test the ping to local port 9115 which is forwarded to my listener on my site:

SQL> host tnsping //localhost:9115/CDB
 
TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 30-MAR-2016 21:59:55
 
Copyright (c) 1997, 2014, Oracle.  All rights reserved.
 
Used parameter files:
/u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora
 
Used EZCONNECT adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=CDB))(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=9115)))
OK (140 msec)

Then create a database link to it:

SQL> create database link LOCALHOST9115CDB connect to system identified by oracle using '//localhost:9115/CDB';
Database link created.

and run my PDB clone through it:

SQL> create pluggable database PDB from PDB@LOCALHOST9115CDB;
Pluggable database created.

You can use that for duplicate from active as well. In 12c you will probably use pull based duplicates, especially when transferring to the cloud because backupsets are smaller and may be compressed, so you will need a connection from the auxiliary to the target. Then you will need the remote port forwarding. If you prefer to Data Pump and don’t bother with scp, it’s the same: you can export or import through database link. For a standby configuration (Data Guard or Dbvisit Standby) you can do the same as long as the ssh connection is permanent. Better to setup a VPN for that.

I really encourage you to test the Cloud and Multitenant. There is a free trial easy to setup: https://cloud.oracle.com/home#platform
Move one of your database to it and see how it looks like: Agility, Performance, Availability.
If you’re around, there is the SITB in Geneva in few weeks: http://www.salon-sitb.ch/exposants/fiche/686/dbi-services.html
I’ll be there are the Oracle / dbi services booth. Let’s talk about Cloud, Multitenant, or anything else.