The Oracle Multitenant architecture introduces some nice opportunities, including local and remote cloning (see this example on ORACLE_BASE blog).
However, the only available cloning procedure use the PULL method to get the data from the remote source PDB.
This can be a limitation, especially in environments where the cloning environment does not have direct access to the production, or where the clones must be done in the Cloud with no direct access to the production VLAN on-premises.
So, one common approach is to clone/detach locally, put the PDB files in the Object Store and then attach them in the cloud.
Another approach is to use SSH tunnels. If you follow my blog you can see it is something that I use every now and then to do stuff from on-premises to the cloud.
How to set it up?
Actually, it is super-easy: just prepare a script in the cloud that will do the create pluggable database, then trigger it from on-premises.
This is an example:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SRC_PDB=$1 TGT_PDB=$2 ALIAS=$3 export ORACLE_HOME=/u01/app/oracle/product/19c/dbhome_1 export ORACLE_SID=ORCL $ORACLE_HOME/bin/sqlplus -s / as sysdba <<EOF create database link onprem_temp connect to C##ONPREM identified by manager using '$ALIAS'; create pluggable database $2 from $1@onprem_temp ; drop database link onprem_temp; alter pluggable database $2 open; alter pluggable database $2 save state; exit EOF |
It takes as parameters: the name of the source PDB, the name of the target PDB and the SQL*Net descriptor to create the temporary database link from the cloud CDB to the on-premises CDB.
The user C##ONPREM must obviously exist on-premises with the following privileges:
|
1 |
grant create session, sysoper, create pluggable database to C##ONPREM container=all; |
The cloud database should use OMF so you don’t have to take care about file name conversions.
At this point, if you have set up correctly the SSH keys to connect to the cloud server, it is just a matter of running the script remotely using the proper SSH tunnel. Once the remote port binding established, the cloud server can contact the on-premises listener port using localhost:remote_bind:
|
1 2 3 4 5 6 7 8 9 10 11 |
oracle@onpremsrv:~$ ssh opc@cloudsrv -R 1522:onpremsrv:1521 \ "sudo -u oracle /home/oracle/clone_db.sh PDBSRC PDBDEST localhost:1522/ONPREMCDB" Database link created. Pluggable database created. Database link dropped. oracle@onpremsrv:~$ |
Of course the timing depends a lot on the size of the database and your connection to the Cloud.
I have tested this procedure with Oracle Database 19.7 on OCI compute instances and on DBaaS VM instance, it works without any additional work. Of course, it does not work for Autonomous Database 🙂
—
Ludovico
Latest posts by Ludovico (see all)
- New views in Oracle Data Guard 23c - January 3, 2024
- New in Data Guard 21c and 23c: Automatic preparation of the primary - December 22, 2023
- Does FLASHBACK QUERY work across incarnations or after a Data Guard failover? - December 13, 2023