Migration of On-Premises Oracle 18c PDB to OCI 19c PDB using Full Transportable Tablespace

Introduction

Migrating an Oracle database from on-premises to Oracle Cloud Infrastructure (OCI) involves several steps to ensure a smooth transition while minimizing downtime. The use of Full Transportable Tablespace is preferable for databases with small data. The procedure below covers the steps to migrate an on-premises Oracle 18c PDB to a 19c PDB on OCI. 

Stage 1 Install Oracle 18c on the centos7 operating system.

1. Download the Oracle pre-install and install binary packages using the below command

curl -o oracle-database-preinstall-18c-1.0-.el7.x86_64.rpm https://yum.oracle.com/repo/OracleLinux/OL7/latest/x86_64/getPackage/oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm                 

2. Execute the pre-install package using the below command as the root user.

yum -y localinstall oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm

3. Install the Oracle database using the below command as the root user

yum -y localinstall oracle-database*18c*

4. Configure the database using the below command.

/etc/init.d/oracle-xe-18c configure.

5. Switch to the Oracle user and confirm that the XEPDB1 pluggable database has been created. 

Stage 2. Create a DB System ocitarget virtual machine with a pluggable database PDB1.

6. Copy the contents of the id_rsa.pub of the on-premises VM into the authorized keys file of the oci target VM to enable passwordless connection.

7. Connect to the OCI target from the Oracle on-premises VM using the below command.

Stage 3. Migrate the XEPDB1 18c database to the 19c PDB1 database on the OCI

On-Premises

8. Create the Data Pump directory    # mkdir -p /opt/oracle/dumps

9. Create directory dpump_dir as ‘/opt/oracle/dumps’;

10. #Create new tablespace testmig to be migrated to OCI.

SQL> create tablespace testmig datafile  ‘/opt/oracle/oradata/XE/XEPDB1/testmig01.dbf’ size 100m;

11. #Create a migration user and grant quota on the new testmig tablespace to the miguser.

SQL> Create user miguser identified by  xxxxxxx quota 50M on testmig;

 12. # Add some Data into the testmig tablespace

SQL> Create table miguser.job(Jobi’d number, job_name varchar2(100)) tablespace testmig;

SQL>  Insert into miguser.job values (101,’DBA’);

SQL> Insert into miguser.job values (102,’Data Scientist’);

SQL> Insert into miguser.job values (103,’AWS Solution Architect’);       

SQL> commit;

13. #Make Tablespace read-only

SQL> alter tablespace testmig read-only;

14. # Perform the full transportable export excluding USERS tablespace.

expdp system/xxxxxxx@oracleprem:1521/xepdb1 full=y transportable=always directory=dpump_dir
dumpfile=exp_full_tts.dmp logfile=exp_full_tts.log         exclude=tablespace:”IN(‘USERS’)”

15. Transfer the dump file and datafiles to the OCI target.

# scp -i /home/oracle/.ssh/id_rsa /opt/oracle/dumps/exp_ful_tts.dmp oracle@ocitargetIP:/u01/app/oracle/admin/export

# scp -i /home/oracle/.ssh/id_rsa /opt/oracle/oradata/XE/XEPDB1/testmig01.dbf      oracle@ocitargetIP:/u01/app/oracle/admin/export               

16. Make the testmig tablespace read-write on the on-premises XEPDB1 database.

SQL> alter tablespace testmig read write; 

OCI-DB System

17. Connect to the PDB1 database on the OCI DB System and create the directory.

# mkdir -p /u01/app/oracle/admin/export

SQL> create directory dpump_dir as ‘/u01/app/oracle/admin/export

18. Get the location of the PDB1 data files.

SQL> select file_name from dba_data_files;

19. As a grid user run asmcmd and copy the datafile to the PDB1 datafile location

cp /u01/app/oracle/admin/export/testmig01.dbf    +DATA/DBMIG_PTV_YYZ/19EF3B5E56DC3E7BE0632100000AE0E3/DATAFILE/

20. #Create the user on the destination environment PDB1

SQL> create user miguser identified by xxxxxxxx temporary tablespace temp;

SQL> grant create a session to miguser;

21. Create an alias on the tnsnames.ora for the pdb1.sub06021948220.vndbmigration.oraclevcn.com service.

22. Perform the full Transportable tablespace on the OCI PDB1 target.

 impdp system/xxxxxx@PDB1 directory=dpump_dir full=Y transport_Datafiles=’+DATA/DBMIG_PTV_YYZ/19EF3B5E56DC3E7BE0632100000AE0E3/DATAFILE/testmig01.dbf’  dumpfile=exp_full_tts.dmp

23. Check the data after the full transportable space is completed.

24. Connect to the PDB1 instance on the OCI and check if the source data was migrated.