Data Loader


Step-by-Step Guide: Migrating a Tablespace Between Oracle Databases

transport data 

 

Want to quickly copy 1000's of tables from one Oracle database to another Oracle database in just 5 minutes! Even if there are thousand of tables with million of rows in each of them. Even if Oracle databases are running on different platforms.

You would be wondering how it is possible. Well yes it is possible and today I am going to show you how you could achieve this. So read on.

As you must be knowing that in Oracle all tables reside in tablespaces and each tablespace have at least one or more datafiles attached to it.

From Oracle version 9i onwards Oracle has introduced the concept of transportable tablespaces. i.e. you can transport a tablespace along with its datafiles from one Oracle database to another Oracle database. Here are the steps you will need to perform tablespace transportation.

Migrating a tablespace from one Oracle database to another involves creating a transportable tablespace set and integrating it into the target database. Here’s a detailed guide:

data loader banner

Prerequisites

  1. Compatibility: Ensure both source and target databases are compatible in terms of character sets and platform endian formats.
    • Use the SELECT * FROM V$TRANSPORTABLE_PLATFORM; query to verify.
  2. User Permissions: Ensure the user performing the migration has DBA or equivalent privileges.
  3. Tablespace Readiness: The tablespace must be self-contained (not reference objects outside the tablespace).
  4. Schema Names: Ensure that both the databases have same schema names. For example, if source has SCOTT schema then ensure that target database also has same schema name SCOTT.

 

Step 1: Set Tablespace to Read-Only Mode

On the source database, make the tablespace read-only to ensure data consistency during the export.

ALTER TABLESPACE tablespace_name READ ONLY;

 

Step 2: Export the Tablespace Metadata

Use the Data Pump Export (expdp) utility to export the metadata for the tablespace.

Example Command

expdp system/password DIRECTORY=dp_dir DUMPFILE=tablespace.dmp LOGFILE=tablespace.log
          TRANSPORT_TABLESPACES=tablespace_name

 

  • DIRECTORY: Oracle directory object pointing to a physical directory.
  • DUMPFILE: Name of the dump file to store the metadata.
  • TRANSPORT_TABLESPACES: Specify the tablespace(s) to be exported.

    •  

    Step 3: Copy the Tablespace Data Files

    Manually copy the physical data files of the tablespace from the source database server to the target database server.

    Locate Data Files

    Run the following query in the source database to find the file paths:

    SELECT FILE_NAME FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'tablespace_name';

    Transfer Files

    Use secure copy tools like scp or a shared directory to copy the files.

    scp /source_path/file_name.dbf user@target_host:/target_path

     

    Step 4: Import the Tablespace Metadata

    On the target database, use Data Pump Import (impdp) to import the metadata.

    Example Command

    impdp system/password DIRECTORY=dp_dir DUMPFILE=tablespace.dmp LOGFILE=tablespace_import.log
          TRANSPORT_DATAFILES='/target_path/file_name.dbf'

     

    TRANSPORT_DATAFILES: Specify the location of the copied data files.

     

    Step 5: Make the Tablespace Read-Write

    Once the import is complete, set the tablespace to read-write mode in the target database.

    ALTER TABLESPACE tablespace_name READ WRITE;

     

    Step 6: Verify the Migration

    Validate Objects

    Run the following query in the target database to ensure all objects are intact:

    SELECT * FROM DBA_SEGMENTS WHERE TABLESPACE_NAME = 'tablespace_name';

     

    Test Application Access

    Confirm that applications or users can access the data without issues.

    Step 7: Clean Up (Optional)

    If the tablespace is no longer required on the source database, you can drop it.

    DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES;

    Tips for a Successful Migration

    This method provides a robust and efficient way to migrate tablespaces between Oracle databases while minimizing downtime.

    Here’s a practical example demonstrating how to migrate the USERS_DATA tablespace from one Oracle database to another:

    Scenario

    Step-by-Step Migration

    Step 1: Set Tablespace to Read-Only

    On the source database (ORCL_SRC), set the USERS_DATA tablespace to read-only mode:

    ALTER TABLESPACE USERS_DATA READ ONLY;

     

    Step 2: Export Tablespace Metadata

    Run the Data Pump Export command on the source database server to export metadata for the USERS_DATA tablespace:

    expdp system/password DIRECTORY=DATA_PUMP_DIR DUMPFILE=users_data.dmp LOGFILE=users_data.log TRANSPORT_TABLESPACES=USERS_DATA

    Step 3: Copy Data Files

    Locate the data file(s) for USERS_DATA in the source database:

    SELECT FILE_NAME FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'USERS_DATA';

    /u01/app/oracle/oradata/ORCL_SRC/users_data01.dbf

    Copy the file to the target database server using scp:

    scp /u01/app/oracle/oradata/ORCL_SRC/users_data01.dbf oracle@target_host:/u01/app/oracle/oradata/ORCL_TGT/

    Step 4: Import Metadata on Target Database

    On the target database (ORCL_TGT), import the metadata for USERS_DATA:

    impdp system/password DIRECTORY=DATA_PUMP_DIR DUMPFILE=users_data.dmp LOGFILE=users_data_import.log TRANSPORT_DATAFILES='/u01/app/oracle/oradata/ORCL_TGT/users_data01.dbf'

    Step 5: Set Tablespace to Read-Write

    Once the import completes, change the USERS_DATA tablespace to read-write mode in the target database:

    ALTER TABLESPACE USERS_DATA READ WRITE;

    Step 6: Verify the Migration

    Check the imported objects in the target database:

    SELECT * FROM DBA_SEGMENTS WHERE TABLESPACE_NAME = 'USERS_DATA';

    Ensure all objects are listed as expected.

    Step 7: Clean Up on Source (Optional)

    If the tablespace is no longer required on the source database, drop it:

    DROP TABLESPACE USERS_DATA INCLUDING CONTENTS AND DATAFILES;

    Example Directory Creation

    If the DATA_PUMP_DIR directory doesn’t exist on either server, create it:

    CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS '/u01/app/oracle/admin/ORCL_SRC/dpdump'; GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO SYSTEM;

    Result

    The USERS_DATA tablespace is successfully migrated from ORCL_SRC to ORCL_TGT, ensuring seamless availability of data on the target database.

    So here it is. This is how easy it is to transport one tablespace from one Oracle database to another Oracle database.

     

     

    Data Loader