Unplug On-Premises Pluggable Database 18cR1 and Plug it in to the Container Database 18cR1 running on Oracle Cloud

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.

[[email protected] 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.

[[email protected] PDB]$ scp -i /home/oracle/DBA/keys/DEMCLOUD.ssh PDB_MUMBAI.pdb [email protected]:/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.

[[email protected] PDB] ssh -i /home/oracle/DBA/keys/DEMCLOUD.ssh [email protected]
[[email protected] PDB]$ hostname
demunix-cloud

8. Unzip PDB_MUMBAI.pdb on Server.

[[email protected] 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.

[[email protected] 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.

[[email protected] 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 🙂