Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

Importing Transportable Tablespace Tips

Oracle Database Tips by Donald Burleson

Import Transportable Tablespaces

Transportable tablespaces permit the copying or moving of a tablespace from one Oracle database to another and are generally the fastest way to move data between Oracle databases. That is because the transportable tablespace process breaks down into two basic operations: export/import the tablespace metadata from/to the source/target database, and copy the tablespace operating system data files. Note that one has to be granted the imp_full_database privilege in order to load transportable tablespaces.

 

Transportable tablespaces were introduced back with Oracle 8i, but over the years they have incrementally improved quite a bit. So while there are still some restrictions like the source and target database must both be of the same character and national character sets, this is one area where Oracle seems to make measurable improvements with each and every release. So it will be best to check with the Oracle version's documentation to verify the specific intra and inter version limitations. So be keenly aware of all such issues before attempting.

 

Again, the complete transportable tablespace process steps are as follows:

 

1.      Check for cross platform issues, i.e. source & target endian formats

2.      Check for cross tablespace dependencies (may define export filters)

3.      Alter tablespace to READ ONLY mode

4.      Export the transportable tablespace set, i.e. the tablespace metadata

5.      Transfer both the export file and tablespace data files to the target

6.      If necessary, convert data files to correct endian format for the target

7.      Import the transportable tablespace set, i.e. the tablespace metadata)

8.      Alter tablespace to READ WRITE mode

 

Now examine in detail steps five through eight; the other steps are covered by the previous section on exporting transportable tablespaces.

 

Step 5: Transfer both the export file and tablespace data files to the target

 

This step is just as easy as it sounds. Simply copy or ftp the export tablespace set and operating system level data files for that tablespace from the source to the target. Remember to use binary mode transfer with a favorite ftp program.

Step 6: Convert data files to correct endian format for the target

Back in Step 1, the Oracle endian cross-reference table as shown below was checked so that the source database endiannes or byte order could be recorded. Now check again for the target.

 

SQL> select * from V$TRANSPORTABLE_PLATFORM ;

 

PLATFORM_ID PLATFORM_NAME                    ENDIAN_FORMAT

----------- -------------------------------- --------------

          1 Solaris[tm] OE (32-bit)          Big

          2 Solaris[tm] OE (64-bit)          Big

          7 Microsoft Windows IA (32-bit)    Little

         10 Linux IA (32-bit)                Little

          6 AIX-Based Systems (64-bit)       Big

          3 HP-UX (64-bit)                   Big

          5 HP Tru64 UNIX                    Little

          4 HP-UX IA (64-bit)                Big

         11 Linux IA (64-bit)                Little

         15 HP Open VMS                      Little

          8 Microsoft Windows IA (64-bit)    Little

          9 IBM zSeries Based Linux          Big

         13 Linux 64-bit for AMD             Little

         16 Apple Mac OS                     Big

 

So if a tablespace is going to be transported from Windows to Linux, there is no additional conversion step of the data file necessary. But if the transport is happening from Solaris to Linux, the big endian data file would need to be converted to little endian. The data format conversion was postponed earlier because it is better to wait until the format that is needed for the specific target de jour is apparent.

 

Once the target has been identified and it is discovered that a conversion is necessary, then use the Oracle RMAN utility on the target, or source, to reformat the data files with the proper byte order encoding.

 

RMAN> convert datafile 'c:\temp\accounting.dbf'

2> to platform = "Linux 64-bit for AMD"

3> from platform = "HP Tru64 UNIX"

4> db_file_name_convert =

5> "c:\oracle\oradata\ordb2\",

6> "c:\oracle\oradata\ordb3\"

7> parallelism=2;

 

There are two items above of note. First, the data file was copied into a temporary directory on the target server and not the file's final destination. This is done because it is best not to move a data file into the Oracle environment until it is ready to be absorbed, which means the byte order has already been addressed. And second, the convert was instructed about necessary file naming changes. In the example above, this tablespace is moved from database ORDB2 to ORDB3, so the data file headers need to be updated for that change.

Step 7: Import the transportable tablespace set

Now comes the easiest and most obvious part of the transportable tablespace import process: the step to actually import the tablespace(s) metadata. Also remember that it is just the metadata being imported and not the actual data or data files. Hence, this step is actually very quick and easy as shown here. If a good job was done during step 2's check for being self-contained, then the parameter for transport_full_check is probably just an extra, although still worthwhile, step.

 

C:\> impdp bert/bert directory=data_pump_dir dumpfile=accounting.dmp transport_tablespaces=accounting transport_full_check=y

Step 8: Alter transportable tablespace to READ WRITE mode

This is a fairly easy but usually mandatory step as the tablespace to be must be placed into READ WRITE for the target database to actually be able to utilize the objects and data contained within.

 

SQL> alter tablespace users read write;

Tablespace altered.

 

If this step is skipped, the following error messages will occur:

 

ORA-00372: file 4 cannot be modified at this time

ORA-01110: data file 4: 'C:\ORACLE\ORADATA\ORDB2\USERS.DBF'


 
 
 
Get the Complete
Oracle Utility Information 

The landmark book "Advanced Oracle Utilities The Definitive Reference"  contains over 600 pages of filled with valuable information on Oracle's secret utilities. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.
 


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.