Unplug On-Premises Pluggable Database 18cR1 and Plug it in to the Container Database 18cR1 running on Oracle Cloud
Here we’ll see one of the easiest way of migrating database from the On-Premises to the Cloud in multi tenancy architecture. We’ll just have to unplug a Pluggable Database from On Premises Container Database, copy the Un-plugged Database to the Cloud using Secure Copy and Plug it in to the Container Database running on the Cloud.
1. Let’s Invoke SQL*Plus on On-Premises server.
[oracle@dbnode1 PDB]$ sqlplus / as sysdba SQL*Plus: Release 18.0.0.0.0 Production on Wed Apr 18 17:38:45 2018 Version 18.1.0.0.0 Copyright (c) 1982, 2017, Oracle. All rights reserved. Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.1.0.0.0
2. List down PDBs.
SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB_MUMBAI READ WRITE NO 4 PDB_BANGLORE READ WRITE NO
3. Login to Pluggable Database PDB_MUMBAI.
SQL> alter session set container = PDB_MUMBAI; Session altered.
Verify file names.
SQL> select file_name from dba_data_files; FILE_NAME -------------------------------------------------------------------------------- +DATA/DEMTEST/69C3A2493E2D47F2E0536600A8C05D4C/DATAFILE/system.284.973796383 +DATA/DEMTEST/69C3A2493E2D47F2E0536600A8C05D4C/DATAFILE/sysaux.285.973796385 +DATA/DEMTEST/69C3A2493E2D47F2E0536600A8C05D4C/DATAFILE/undotbs1.283.973796383 +DATA/DEMTEST/69C3A2493E2D47F2E0536600A8C05D4C/DATAFILE/users.274.973796383 +DATA/DEMTEST/69C3A2493E2D47F2E0536600A8C05D4C/DATAFILE/tbs_pdb_mumbai.286.973796393
And Container Database name.
SQL> select name,version,open_mode,host_name from v$database,v$instance; NAME VERSION OPEN_MODE HOST_NAME --------- ----------------- -------------------- -------------------- DEMTEST 18.0.0.0.0 READ WRITE dbnode1.demunix.com
4. Close the target Pluggable Database.
SQL> alter pluggable database PDB_MUMBAI close; Pluggable database altered.
5. Unplug it to .pdb file at specific location.
SQL> alter pluggable database PDB_MUMBAI unplug into '/home/oracle/DBA/PDB/PDB_MUMBAI.pdb'; Pluggable database altered
6. Copy the Un-Plugged Database to the Cloud using secure copy.
[oracle@dbnode1 PDB]$ scp -i /home/oracle/DBA/keys/DEMCLOUD.ssh PDB_MUMBAI.pdb oracle@demunix-cloud:/home/oracle/DBA/PDB
Note : “/home/oracle/DBA/keys/DEMCLOUD.ssh” This is my Private SSH key for Cloud Authentication.
7. Login to Cloud Server using SSH.
[oracle@dbnode1 PDB] ssh -i /home/oracle/DBA/keys/DEMCLOUD.ssh oracle@demunix-cloud [oracle@demunix-cloud PDB]$ hostname demunix-cloud
8. Unzip PDB_MUMBAI.pdb on Server.
[oracle@demunix-cloud PDB]$ unzip PDB_MUMBAI.pdb Archive: PDB_MUMBAI.pdb inflating: system.272.973397757 inflating: sysaux.273.973397757 inflating: undotbs1.271.973397757 inflating: users.275.973397831 inflating: tbs_pdb_mumbai.276.973402503 warning: stripped absolute path spec from /home/oracle/DBA/PDB/PDB_MUMBAI.xml inflating: home/oracle/DBA/PDB/PDB_MUMBAI.xml
Verify Unzipped files.
[oracle@demunix-cloud PDB]$ ls -lrth total 5.0G -rw-r--r-- 1 oracle oinstall 181M Apr 18 12:21 PDB_MUMBAI.pdb -rw-r--r-- 1 oracle oinstall 260M Apr 18 2018 system.272.973397757 -rw-r--r-- 1 oracle oinstall 440M Apr 18 2018 sysaux.273.973397757 -rw-r--r-- 1 oracle oinstall 100M Apr 18 2018 undotbs1.271.973397757 -rw-r--r-- 1 oracle oinstall 5.0M Apr 18 2018 users.275.973397831 -rw-r--r-- 1 oracle oinstall 4.0G Apr 18 2018 tbs_pdb_mumbai.276.973402503
9. Invoke SQL*Plus on Cloud server.
[oracle@demunix-cloud PDB]$ sqlplus / as sysdba SQL*Plus: Release 18.0.0.0.0 Production on Wed Apr 18 12:41:54 2018 Version 18.1.0.0.0 Copyright (c) 1982, 2017, Oracle. All rights reserved. Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.1.0.0.0
10. List down PDBs.
SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ---------------- ---------- ---------- 2 PDB$SEED READ ONLY NO 4 PDB1 READ WRITE NO
11. Create Pluggable Database on Cloud using PDB_MUMBAI.pdb file.
SQL> create pluggable database PDB_MUMBAI
using '/home/oracle/DBA/PDB/PDB_MUMBAI.pdb'
file_name_convert=('/home/oracle/DBA/PDB', '/u02/app/oracle/oradata/DEMCLOUD/PDB_MUMBAI');
Pluggable database created.
12. List down PDBs now.
SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB_MUMBAI MOUNTED 4 PDB1 READ WRITE NO
As we can see, PDB_MUMBAI is now been created and plugged successfully in the cloud, however it’s only mounted and not opened, let’s open it.
SQL> alter pluggable database PDB_MUMBAI open; Pluggable database altered.
List down PDBs again.
SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB_MUMBAI READ WRITE NO 4 PDB1 READ WRITE NO
13. Login to newly plugged PDB,
SQL> alter session set container = PDB_MUMBAI; Session altered.
and verify file names.
SQL> select file_name from dba_data_files; FILE_NAME -------------------------------------------------------------------------------- /u02/app/oracle/oradata/DEMCLOUD/PDB_MUMBAI/system.272.973397757 /u02/app/oracle/oradata/DEMCLOUD/PDB_MUMBAI/sysaux.273.973397757 /u02/app/oracle/oradata/DEMCLOUD/PDB_MUMBAI/undotbs1.271.973397757 /u02/app/oracle/oradata/DEMCLOUD/PDB_MUMBAI/users.275.973397831 /u02/app/oracle/oradata/DEMCLOUD/PDB_MUMBAI/tbs_pdb_mumbai.276.973402503
And Container Database name.
SQL> select name,version,open_mode,host_name from v$database,v$instance; NAME VERSION OPEN_MODE HOST_NAME --------- ----------------- -------------------- -------------------- DEMCLOUD 18.0.0.0.0 READ WRITE demunix-cloud
Cheers, We’ve successfully Plugged in Un-Plugged Database from On Premises Container Database(DEMTEST) to the Container Database(DEMCLOUD) running on Cloud.
Note that if you still want to run your Pluggable Database on On-Premises Container, then you have to Drop the Un-Plugged database and create it back again in the same container. As once you’ve Unplugged Pluggable Database from Container Database it will be attached to CDB$ROOT in mounted mode but you won’t be able to Open it back.
On the On-Premises Server.
SQL> alter pluggable database PDB_MUMBAI open; alter pluggable database PDB_MUMBAI open * ERROR at line 1: ORA-65086: cannot open/close the pluggable database
List down PDBs
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB_MUMBAI MOUNTED
4 PDB_BANGLORE READ WRITE NO
Though it’s mounted, we’re unable to open it, let’s drop it.
SQL> drop pluggable database PDB_MUMBAI; Pluggable database dropped.
Create it again using PDB_MUMBAI.pdb file.
SQL> create pluggable database PDB_MUMBAI using '/home/oracle/DBA/PDB/PDB_MUMBAI.pdb'; Pluggable database created.
And open it.
SQL> alter pluggable database PDB_MUMBAI open; Pluggable database altered.
Verify file names again.
SQL> select file_name from dba_data_files; FILE_NAME -------------------------------------------------------------------------------- +DATA/DEMTEST/69C3A2493E2D47F2E0536600A8C05D4C/DATAFILE/system.284.973796383 +DATA/DEMTEST/69C3A2493E2D47F2E0536600A8C05D4C/DATAFILE/sysaux.285.973796385 +DATA/DEMTEST/69C3A2493E2D47F2E0536600A8C05D4C/DATAFILE/undotbs1.283.973796383 +DATA/DEMTEST/69C3A2493E2D47F2E0536600A8C05D4C/DATAFILE/users.274.973796383 +DATA/DEMTEST/69C3A2493E2D47F2E0536600A8C05D4C/DATAFILE/tbs_pdb_mumbai.286.973796393
So we’ve started same pluggable database in On-Premises container again.
Please note that here I didn’t have to perform any upgrade operation while migrating as I’m running 18cR1 on my On-Premises Server and the same version is running on the cloud.
Peace 🙂