Data Pump Transportable Tablespace

You can use this method only if the on-premises platform is little endian, and the database character sets of your on-premises database and the Oracle Cloud Infrastructure Database service database are compatible.

The Transportable Tablespace method is generally much faster than a conventional export/import of the same data because the data files containing all of the actual data are simply copied to the destination location. You use Data Pump to transfer only the metadata of the tablespace objects to the new database.

To migrate an on-premises source database to the database deployment on the Database service using the Data Pump Transportable Tablespace method, you perform these tasks:

  1. On the on-premises database host, prepare the database for the Data Pump transportable tablespace export.

  2. On the on-premises database host, invoke Data Pump Export to perform the transportable tablespace export.

  3. Use a secure copy utility to transfer the Data Pump Export dump file and the tablespace datafiles to the Database service compute node.

  4. Set the on-premises tablespaces back to READ WRITE.

  5. On the Databaseservice compute node, prepare the database for the tablespace import.

  6. On the Database service compute node, invoke Data Pump Import and connect to the database.

  7. Set the tablespaces on the Database service database to READ WRITE mode.

  8. After verifying that the data has been imported successfully, you can delete the dump file.

Data Pump Transportable Tablespace: Example

This example provides a step-by-step demonstration of the tasks required to migrate tablespaces in an on-premises Oracle database to a Database service database.

This example performs a migration of the FSDATA and FSINDEX tablespaces.

In this example, the on-premises database is on a Linux host.

  1. On the on-premises database host, prepare the database for the Data Pump transportable tablespace export.

    1. On the on-premises database host, create a directory in the operating system to use for the on-premises export.

      mkdir /u01/app/oracle/admin/orcl/dpdump/for_cloud
    2. On the on-premises database host, invoke SQL*Plus and log in to the on-premises database as the SYSTEM user.

      sqlplus system
      Enter password: <enter the password for the SYSTEM user>
    3. Create a directory object in the on-premises database to reference the operating system directory.

      SQL> CREATE DIRECTORY dp_for_cloud AS '/u01/app/oracle/admin/orcl/dpdump/for_cloud';
    4. Determine the name(s) of the datafiles that belong to the FSDATA and FSINDEX tablespaces by querying DBA_DATA_FILES. These files will also be listed in the export output.

      SQL> SELECT file_name FROM dba_data_files
        2  WHERE tablespace_name = 'FSDATA';
      
      FILE_NAME
      -----------------------------------------------------------------
      /u01/app/oracle/oradata/orcl/fsdata01.dbf
      
      SQL> SELECT file_name FROM dba_data_files 
        2  WHERE tablespace_name = 'FSINDEX';
      
      FILE_NAME
      -----------------------------------------------------------------
      /u01/app/oracle/oradata/orcl/fsindex01.dbf
    5. On the on-premises database host, set all tablespaces that will be transported (the transportable set) to READ ONLY mode.

      SQL> ALTER TABLESPACE fsindex READ ONLY;
      Tablespace altered.
      SQL> ALTER TABLESPACE fsdata READ ONLY;
      Tablespace altered.
    6. Exit from SQL*Plus.

  2. On the on-premises database host, invoke Data Pump Export to perform the transportable tablespace export.

    On the on-premises database host, invoke Data Pump Export and connect to the on-premises database. Export the on-premises tablespaces using the TRANSPORT_TABLESPACES option. Provide the password for the SYSTEM user when prompted.

    expdp system TRANSPORT_TABLESPACES=fsdata,fsindex TRANSPORT_FULL_CHECK=YES DIRECTORY=dp_for_cloud
  3. Use a secure copy utility to transfer the Data Pump Export dump file and the tablespace datafiles to the Database service compute node.

    In this example the dump file is copied to the /u01 directory. Choose the appropriate location based on the size of the file that will be transferred.

    1. On the Database service compute node, create a directory for the dump file.

      mkdir /u01/app/oracle/admin/ORCL/dpdump/from_onprem
    2. Before using the scp utility to copy files, make sure the SSH private key that provides access to the Database service compute node is available on your on-premises host.

    3. On the on-premises database host, use the scp utility to transfer the dump file and all datafiles of the transportable set to the Database service compute node.

      scp -i private_key_file \ 
      /u01/app/oracle/admin/orcl/dpdump/for_cloud/expdat.dmp \
      oracle@IP_address_DBaaS_VM:/u01/app/oracle/admin/ORCL/dpdump/from_onprem
      
      $ scp -i private_key_file \/u01/app/oracle/oradata/orcl/fsdata01.dbf \
      oracle@IP_address_DBaaS_VM:/u02/app/oracle/oradata/ORCL
      
      $ scp -i private_key_file \/u01/app/oracle/oradata/orcl/fsindex01.dbf \
      oracle@IP_address_DBaaS_VM:/u02/app/oracle/oradata/ORCL
  4. Set the on-premises tablespaces back to READ WRITE.

    1. Invoke SQL*Plus and log in as the SYSTEM user.

    2. Set the FSDATA and FSINDEX tablespaces back to READ WRITE mode.

      SQL> ALTER TABLESPACE fsdata READ WRITE;
      Tablespace altered.
      SQL> ALTER TABLESPACE fsindex READ WRITE;
      Tablespace altered.
    3. Exit from SQL*Plus.

  5. On the Database service compute node, prepare the database for the tablespace import.

    1. On the Database service compute node, invoke SQL*Plus and log in to the database as the SYSTEM user.

    2. Create a directory object in the Database service database.

      SQL> CREATE DIRECTORY dp_from_onprem AS '/u01/app/oracle/admin/ORCL/dpdump/from_onprem';
    3. If the owners of the objects that will be imported do not exist in the database, create them before performing the import. The transportable tablespace mode of import does not create the users.

      SQL> CREATE USER fsowner
        2  PROFILE default
        3  IDENTIFIED BY fspass
        4  TEMPORARY TABLESPACE temp
        5  ACCOUNT UNLOCK;
  6. On the Database service compute node, invoke Data Pump Import and connect to the database.

    Import the data into the database using the TRANSPORT_DATAFILES option.

    impdp system DIRECTORY=dp_from_onprem \
    TRANSPORT_DATAFILES='/u02/app/oracle/oradata/ORCL/fsdata01.dbf', \
    '/u02/app/oracle/oradata/ORCL/fsindex01.dbf'
  7. Set the tablespaces on the Database service database to READ WRITE mode.

    1. Invoke SQL*Plus and log in as the SYSTEM user.

    2. Set the FSDATA and FSINDEX tablespaces to READ WRITE mode.

      SQL> ALTER TABLESPACE fsdata READ WRITE;
      Tablespace altered.
      SQL> ALTER TABLESPACE fsindex READ WRITE;
      Tablespace altered.
    3. Exit from SQL*Plus.

  8. After verifying that the data has been imported successfully, you can delete the expdat.dmp dump file.