Data Pump Full Transportable

You can use this method only if the source database release version is 11.2.0.3 or later, and the database character sets of your on-premises database and the Oracle Cloud Infrastructure Database service database are compatible.

You can use the Data Pump full transportable method to copy an entire database from your on-premises host to the database on a Database service database deployment.

To migrate an Oracle Database 11g on-premises database to the Oracle Database 12c database on a Database service database deployment using the Data Pump full transportable method, you perform these tasks:

  1. On the on-premises database host, prepare the database for the Data Pump full transportable export by placing the user-defined tablespaces in READ ONLY mode.

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

  3. Use a secure copy utility to transfer the Data Pump Export dump file and the datafiles for all of the user-defined tablespaces to the Database service compute node.

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

  5. On the Database service 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. After verifying that the data has been imported successfully, you can delete the dump file.

Data Pump Full Transportable: Example

This example provides a step-by-step demonstration of the tasks required to migrate an Oracle Database 11g database to a Database service 12c database.

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

  1. On the source database host, prepare the database for the Data Pump full transportable export.

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

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

      $ sqlplus system
      Enter password: <enter the password for the SYSTEM user>
    3. Create a directory object in the source 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 tablespaces and data files that belong to the user-defined tablespaces by querying DBA_DATA_FILES. These files will also be listed in the export output.

      SQL> SELECT tablespace_name, file_name FROM dba_data_files; 
      TABLESPACE_NAME  FILE_NAME
      ---------------  --------------------------------------------------
      USERS            /u01/app/oracle/oradata/orcl/users01.dbf
      UNDOTBS1         /u01/app/oracle/oradata/orcl/undotbs01.dbf
      SYSAUX           /u01/app/oracle/oradata/orcl/sysaux01.dbf
      SYSTEM           /u01/app/oracle/oradata/orcl/system01.dbf
      EXAMPLE          /u01/app/oracle/oradata/orcl/example01.dbf
      FSDATA           /u01/app/oracle/oradata/orcl/fsdata01.dbf
      FSINDEX          /u01/app/oracle/oradata/orcl/fsindex01.dbf
      SQL> 
    5. On the source database host, set all tablespaces that will be transported (the transportable set) to READ ONLY mode.

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

  2. On the source database host, invoke Data Pump Export to perform the full transportable export. Specify FULL=y and TRANSPORTABLE=always. Because this is an Oracle Database 11g database and full transportable is an Oracle Database 12c feature, specify VERSION=12. Provide the password for the SYSTEM user when prompted.

    $ expdp system FULL=y TRANSPORTABLE=always VERSION=12 DUMPFILE=expdat.dmp DIRECTORY=dp_for_cloud
  3. Use a secure copy utility to transfer the Data Pump Export dump file and the datafiles for all of the user-defined tablespaces 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_source
    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 source host.

    3. On the source 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@compute_node_IP_address:/u01/app/oracle/admin/ORCL/dpdump/from_source
      
      $ scp -i private_key_file \
      /u01/app/oracle/oradata/orcl/example01.dbf \
      oracle@compute_node_IP_address:/u02/app/oracle/oradata/ORCL/PDB2
      
      $ scp -i private_key_file \
      /u01/app/oracle/oradata/orcl/fsdata01.dbf \
      oracle@compute_node_IP_address:/u02/app/oracle/oradata/ORCL/PDB2
      
      $ scp -i private_key_file \
      /u01/app/oracle/oradata/orcl/fsindex01.dbf \
      oracle@compute_node_IP_address:/u02/app/oracle/oradata/ORCL/PDB2
      
      $ scp -i private_key_file \
      /u01/app/oracle/oradata/orcl/users01.dbf \
      oracle@compute_node_IP_address:/u02/app/oracle/oradata/ORCL/PDB2
  4. Set the source tablespaces back to READ WRITE.

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

    2. Set the user-defined tablespaces back to READ WRITE mode.

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

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

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

    2. Create a directory object in the PDB.

      SQL> CREATE DIRECTORY dp_from_source AS '/u01/app/oracle/admin/ORCL/dpdump/from_source';
  6. On the Database service compute node, invoke Data Pump Import and connect to the PDB.

    Import the data into the database using the TRANSPORT_DATAFILES option.

    $ impdp system@PDB2 FULL=y DIRECTORY=dp_from_source \
    TRANSPORT_DATAFILES='/u02/app/oracle/oradata/ORCL/PDB2/example01.dbf',\'/u02/app/oracle/oradata/ORCL/PDB2/fsdata01.dbf',\'/u02/app/oracle/oradata/ORCL/PDB2/fsindex01.dbf,'\'/u02/app/oracle/oradata/ORCL/PDB2/users01.dbf'
  7. After verifying that the data has been imported successfully, you can delete the expdat.dmp dump file.