• Oracle Base - Transportable Tablespaces

Original page here at husnusensoy.wordpress.com

There are various ways of migrating data in Oracle 10g. Datapump, moving over link, RMAN duplicate, and transportable tablespace are the most common ones. According to my experiences I can make the following classification

0@@

In this post I will be explaining the TTS way of doing the job. Although we define TTS as suitable for terabytes of data, when the system resources on the source system is limited, TTS is again the best option. That’s because all other techniques except the database link requires a copy of original data and this requires resource (CPU,disk etc.).

Preparing the data before moving is important in two senses:

• Usually you don’t need to move the whole data so processing and reducing the amount of data prior to move will reduce your transfer time to some extent.
• One problem about TTS is that it requires tablespaces to be moved to be taken to read only mode during the move. If you copy the subset of the data to another tablespace, you will be increasing the availability of the master data source. You will be taking the copy tablespace to read only mode only.

1@@

For our case by this filtering we reduce the 330 GB total data to 35 GB migration data. As a result, remember that filtering on the source site is a best practice for any migration activity. TODO List on Source Database <br /> There are a number of steps to be done on the source site. Respectively,

• Validating the self containing property of the migration tablespace.
• Altering the migration tablespace to read only mode.
• Exporting the meta data

TTS requires to be self contained. This means that the segments within the migration tablespace set can not have dependency to a segment in a tablespace out of the transportable tablespace set. Although it seems a complex task, it is simply done by a PL/SQL procedure in DBMS_TTS package.

2@@

As you see STAGING tablespace is self contained. If it were not self contained you should either remove the dependencies by dropping them or include the tablespaces of segments into TTS set to which migration set is depended.

This requirement must be obvious. In the next steps you will see that we will perform a file system copy to tablespace datafiles. In order those datafiles to not to require a recovery they need to be in consistent during the copy activity. That is to say it is required that redo log generation has to stop for those datafiles.

3@@

The task list for target database is simply,

• Copy the datafiles and export file to target server via FTP
• Create the necessary schemas
• Modify the db_n_cache_size parameter if necessary.
• Import the export file

The very first step is to copy the datafiles and metadata export file to target server.

4@@

Remember that the metadata export is taken for the tablespace. While you are importing into a new database it will be looking for the schemas of the objects taken from the source system. So you need to create the required schemas in the new database.

5@@

If the source and target databases have different database block sizes. Then Oracle will not allow you to import the metadata

6@@

Fortunately Oracle allows the usage of different block sized tablespaces within the same database. The only thing to do is to explicitly set the buffer cache size for different tablespaces

7@@

The final step on the target site is to import the tablespace metada. For this pupose you may simply create a data pump parameter file using your favorite text editor.

8@@

Then using this file you can perform the import

9@@

Your tablespace is ready to use in the target database.