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