RMAN CONVERT Transportable Tablespace with Data Pump
You can use this method only if the database character sets of your on-premises database and the Database service database are compatible.
This method is similar to the Data Pump Transportable Tablespace method, with the addition of the RMAN CONVERT
command to enable transport between platforms with different endianness. Query V$TRANSPORTABLE_PLATFORM
to determine if the on-premises database platform supports cross-platform tablespace transport and to determine the endian format of the platform. The Database service platform is little-endian format.
To migrate tablespaces from your on-premises Oracle database to a database deployment on the Database service using RMAN, you perform these tasks:
-
On the on-premises database host, prepare the database for the Data Pump transportable tablespace export.
-
On the on-premises database host, invoke Data Pump Export to perform the transportable tablespace export.
-
On the on-premises database host, invoke RMAN and use the
CONVERT TABLESPACE
command to convert the tablespace datafile to the Database service platform format. Refer to the Oracle Database Backup and Recovery Reference for more information on theCONVERT
command. -
Use a secure copy utility to transfer the Data Pump Export dump file and the converted tablespace datafiles to the Database service compute node.
-
Set the on-premises tablespaces back to
READ WRITE
. -
On the Database service compute node, prepare the database for the tablespace import.
-
On the Database service compute node, invoke Data Pump Import and connect to the database.
-
On the Database service compute node, set the tablespaces in the database to
READ WRITE
mode. -
After verifying that the data has been imported successfully, you can delete the dump file.
RMAN CONVERT Transportable Tablespace with Data Pump: 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.
In this example, the on-premises database is on a Linux host.
-
On the on-premises database host, prepare the database for the Data Pump transportable tablespace export.
-
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
-
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>
-
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';
-
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.
-
Exit from SQL*Plus.
-
-
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 theSYSTEM
user when prompted.expdp system TRANSPORT_TABLESPACES=fsdata,fsindex TRANSPORT_FULL_CHECK=YES DIRECTORY=dp_for_cloud
-
On the on-premises database host, invoke RMAN and use the
CONVERT TABLESPACE
command to convert the tablespace datafile to the Database service platform format.-
Invoke RMAN.
rman target /
-
Execute the RMAN
CONVERT TABLESPACE
command to convert the datafiles and store the converted files in a temporary location on the on-premises database host.RMAN> CONVERT TABLESPACE fsdata, fsindex 2> TO PLATFORM 'Linux x86 64-bit' 3> FORMAT '/tmp/%U '; … input datafile file number=00006 name=/u01/app/oracle/oradata/orcl/fsdata01.dbf converted datafile=/tmp/data_D-ORCL_I-1410251631_TS-FSDATA_FNO-6_0aqc9un3 … input datafile file number=00007 name=/u01/app/oracle/oradata/orcl/fsindex01.dbf converted datafile=/tmp/data_D-ORCL_I-1410251631_TS-FSINDEX_FNO-7_0bqc9un6 …
-
Take note of the names of the converted files. You will copy these files to the Database service compute node in the next step.
-
Exit RMAN.
-
-
Use a secure copy utility to transfer the Data Pump Export dump file and the converted 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.-
On the Databaseservice compute node, create a directory for the dump file.
mkdir /u01/app/oracle/admin/ORCL/dpdump/from_onprem
-
Before using the
scp
command 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. -
On the on-premises database host, use the
scp
utility to transfer the dump file and all data files 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 \ /tmp/data_D-ORCL_I-1410251631_TS-FSDATA_FNO-6_0aqc9un3 \ oracle@IP_address_DBaaS_VM:/u02/app/oracle/oradata/ORCL/fsdata01.dbf $ scp -i private_key_file \ /tmp/data_D-ORCL_I-1410251631_TS-FSINDEX_FNO-7_0bqc9un6 \ oracle@IP_address_DBaaS_VM:/u02/app/oracle/oradata/ORCL/fsindex01.dbf
-
-
Set the on-premises tablespaces back to
READ WRITE
.-
Invoke SQL*Plus and log in as the
SYSTEM
user. -
Set the
FSDATA
andFSINDEX
tablespaces back toREAD WRITE
mode.SQL> ALTER TABLESPACE fsdata READ WRITE; Tablespace altered. SQL> ALTER TABLESPACE fsindex READ WRITE; Tablespace altered.
-
Exit from SQL*Plus.
-
-
On the Database service compute node, prepare the database for the tablespace import.
-
On the Database service compute node, invoke SQL*Plus and log in to the database as the
SYSTEM
user. -
Create a directory object in the Database service database.
SQL> CREATE DIRECTORY dp_from_onprem AS '/u01/app/oracle/admin/ORCL/dpdump/from_onprem';
-
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;
-
-
On the Database service compute node, invoke Data Pump Import and connect to the database.
Import the data into the Database service database using the
TRANSPORT_DATAFILES
optionimpdp system DIRECTORY=dp_from_onprem \ TRANSPORT_DATAFILES='/u02/app/oracle/oradata/ORCL/fsdata01.dbf', \ '/u02/app/oracle/oradata/ORCL/fsindex01.dbf'
-
On the Database service compute node, set the tablespaces in the database to
READ WRITE
mode.-
Invoke SQL*Plus and log in as the
SYSTEM
user. -
Set the
FSDATA
andFSINDEX
tablespaces toREAD WRITE
mode.SQL> ALTER TABLESPACE fsdata READ WRITE; Tablespace altered. SQL> ALTER TABLESPACE fsindex READ WRITE; Tablespace altered.
-
Exit from SQL*Plus.
-
-
After verifying that the data has been imported successfully, you can delete the
expdat.dmp
dump file.