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 preferrable 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 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 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.

               

1.                               

                   

              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 tablesapace 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 datafiles.

                      SQL> select file_name from dba_data_files;

               19.   As 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 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.